Использование справочной системы

СОДЕРЖАНИЕ

ПЕРВЫЙ УРОК............................................................................................4

Первое знакомство.................................................................................................. 4

Вычисления в таблицах данных.......................................................................... 7

Элементарная сортировка данных..................................................................... 9 Графическое представление данных таблиц................................................. 10 Задания для самостоятельной работы............................................................. 11

ВТОРОЙ УРОК...........................................................................................12 Работа с диаграммами......................................................................................... 12

Использование рисунков в диаграммах......................................................... 17 Задания для самостоятельной работы............................................................. 22

ТРЕТИЙ УРОК...........................................................................................25

Работа со списками............................................................................................... 25

Сортировка........................................................................................................... 26

Использование фильтров.................................................................................... 34 Задания для самостоятельной работы............................................................. 41

ЧЕТВЕРТЫЙ УРОК...................................................................................43

Работа с формулами и функциями.................................................................... 43

Логические выражения....................................................................................... 45

Абсолютные и относительные ссылки............................................................. 47 Задания для самостоятельной работы............................................................. 49

ПЯТЫЙ УРОК............................................................................................50

Трехмерные ссылки............................................................................................. 50 Консолидация данных......................................................................................... 52 Задания для самостоятельной работы............................................................. 56

ШЕСТОЙ УРОК..........................................................................................59 Создание сводной таблицы................................................................................. 59

Функция «ЕСЛИ»................................................................................................. 60

Проведение анализа документа......................................................................... 63

Задания для самостоятельной работы:............................................................ 64

СЕДЬМОЙ УРОК.......................................................................................66

Шаблоны................................................................................................................ 66

Защита ячеек, листов и рабочих книг.............................................................. 68

Задания для самостоятельной работы:............................................................ 76

ВОСЬМОЙ УРОК.......................................................................................77 Таблицы подстановок.......................................................................................... 77 Анализ «что-если»................................................................................................ 80 Работа со сценариями.......................................................................................... 81 Поиск решений...................................................................................................... 85

Задания для самостоятельной работы............................................................. 89

ДЕВЯТЫЙ УРОК........................................................................................92

Базы данных в Excel............................................................................................. 92

Задание для самостоятельной работы.............................................................. 97

Первый урок

Первое знакомство

Запуск Microsoft Excel

Для запуска Excel необходимо щелкнуть кнопку Пуск на панели задач в группе меню Программы выбрать пункт Microsoft Excel.

При запуске Excel на экране появляется окно с пустой таблицей. При первом запуске этот документ имеет стандартное имя Книга1, которое указывается в строке заголовка.

Использование справочной системы

В любой момент времени вы можете получить помощь от справочной системы. Для работы с ней предназначен пункт меню со знаком вопроса. Так же можно воспользоваться горячей клавишей F1.

Общие сведения о книгах и листах Microsoft Excel

Книги. Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи.

Листы. Листы служат для организации и анализа данных. Можно вводить и изменять данные одновременно на нескольких листах, а также выполнять вычисления на основе данных из нескольких листов. При создании диаграммы ее можно поместить на лист с соответствующими данными или на отдельный лист диаграммы.

Вкладки листов. Имена листов отображаются на вкладках в нижней части окна книги. Для перехода с одного листа на другой следует выбрать соответствующую вкладку.

Элементы экрана

Строка заголовка находится в верхней части экрана и имеет обычный вид для программ, работающих под управлением Windows, дополнительно здесь установлена кнопка Файл, которая предназначена для вывода списка возможных действий с документом, включая открытие, сохранение и печать. Также на строке заголовка есть панель быстрого доступа.

 

Строка меню

Под строкой заголовка находится строка меню, в которой перечислены группы команд: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. Каждая группа объединяет набор команд, имеющих общую функциональную направленность.

Перемещение и прокрутка внутри листа

Для перехода между ячейками листа используется мышь или клавиши со стрелками. При переходе на ячейку она становится активной. Для перехода в другую область листа используются полосы прокрутки. Полоса прокрутки - полосы вдоль правой и нижней сторон окна. Чтобы прокрутить содержимое окна, перетаскивайте бегунок или нажимайте кнопки со стрелками.

Переход на другой лист книги

Укажите ярлычок листа, содержащего нужные данные. Если ярлычок нужного листа не виден, то для его отображения используйте кнопки прокрутки листов. Затем укажите необходимый ярлычок.

Создание, закрытие и сохранение файлов

Создание книги

1. Нажимаем кнопку Файл и выбираем Создать.

2. Чтобы создать новую пустую книгу, выберите Новая книга.

Открытие книги

1. Нажимаем кнопку Файл и нажимаем Открыть или просто нажимаем кнопку Открыть на панели быстрого доступа.

2. В поле Папка укажите путь, где находится книга, которую требуется открыть. Также для удобства, при нажатии на кнопку Файл появляется вкладка Последние, где показаны последние ранее открывавшиеся документы.

3. Нажмите кнопку Открыть.

Сохранение книги

При первом сохранении книги указывается имя файла и место, куда он будет помещен. Во время каждого последующего сохранения книга будет обновляться в соответствии с внесенными в нее изменениями. Для этого можно просто нажать кнопку Сохранить

Сохранение новой книги

1. Выберите команду кнопка Файл /Сохранить как и в поле Тип файла выберитенеобходимый тип сохранения документа (например, Книга Excel).

2. В окне Сохранение документа укажите диск и папку, в которую будет помещена книга.

3. В поле Имя файла введите имя книги.

4. Нажмите кнопку Сохранить.

Закрытие книги

Выберите команду Кнопка Файл/ Закрыть.

Ввод данных

1. Укажите ячейку, в которую необходимо ввести данные.

2. Наберите данные и нажмите клавишу ENTER.

Очистка ячейки

1. Выделите ячейки, строки или столбцы, которые следует очистить.

2. В меню Главная в подменю Редактирование выберите команду Очистить , а затем — Все, Форматы, Содержимое, Примечания или Гиперссылки, л ибо нажмите кнопку Delete на клавиатуре, если нужно очистить все.

Вычисления в таблицах данных

Для чего нужны формулы?

Наша цель создать таблицу с данными и формулами, производящими вычисления.

Значения ячеек в столбце стоимость должны вычисляться как произведение цены и количества, значение итого вычисляется, как сумма значений ячеек, расположенных выше. Важно, что при изменении значений ячеек в столбцах цена и количество значения в ячеек в столбце стоимость изменяются автоматически. В Microsoft Excel 2010 можно использоватьформулы, которые интегрированы в систему. Для этого достаточно выполнить следующую команду Формулы/Библиотека функций и выбрать нужную вам функцию. Также можно вставлять функцию вручную.

Второй урок

Работа с диаграммами

Основные термины

Диаграмма представляет собой графический способ представления табличных данных. При этом используется следующая терминология.

• Серия данных – группа данных расположенная внутри одной строки таблицы.

• Имя серии – имя строки таблицы, содержимое которой образует данную серию.

• Легенда – набор всех имен серий данной таблицы.

• Категория – группа значений, расположенных в одном столбце таблицы.

Элементы диаграммы

Для осознанной работы с диаграммами необходимо знать название и назначение ее основных элементов.

1. Область диаграммы. Область размещения диаграммы и всех ее элементов.

2. Область построения диаграммы. На плоских диаграммах – ограниченная осями область, содержащая все ряды данных. На объемных диаграммах – ограниченная осями область, содержащая все ряды данных, имена категорий, подписи делений и названия осей.

3. Точки данных для ряда данных, отмеченные на диаграмме. Точки данных – отдельные значения, отображаемые на диаграмме в виде полос, столбцов, линий, секторов, точек или других объектов, называемых маркерами данных. Маркеры данных одного цвета образуют ряд данных. Ряд данных – Набор связанных между собой элементов данных, отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.

4. Ось категорий. Горизонтальная ось, вдоль нее строятся категории.

5. Ось значений. Вертикальная ось, вдоль нее строятся данные.

6. Легенда диаграммы. Рамка, в которой определяются узоры или цвета рядов или категорий данных на диаграмме.

7. Названия на диаграммах. Описательный текст, располагающийся вдоль осей или по центру в верхней части диаграммы.

8. Подписи данных. Подпись с дополнительными сведениями о маркере данных, предоставляющем одну точку данных или значение ячейки листа.

Создание диаграмм

В Microsoft Excel 2010 можно построить диаграмму следующим способом: необходимо предварительно создать в документе таблицу данных, выделить ее, и после этого запускать вставку диаграмм. Диаграмма связана с данными, на основе которых она создана, и обновляется автоматически при изменении данных. Диаграмму можно создать на отдельном листе или поместить в качестве внедренного объекта на лист с данными. Создадим таблицу и диаграмму следующего вида.

1. Создайте новую книгу. Для этого нажмите на кнопку Файл, выполните команду: Создать / Новая книга и нажмите Создать.

2. Заполните таблицу, т.е. ячейки А1-G1, A2-G2.

3. Выделите таблицу и выполните команду Вставка/(Диаграммы) Гистограмма.

4. В списке гистограмм выбираем пункт Гистограмма с группировкой.

5. Выделите диаграмму, затем выполните команду: Макет/(Текущий фрагмент) Область диаграммы/Вертикальная ось (значений).

6. Далее выполните команду: Макет/(Текущий фрагмент)Формат выделенного объекта.

7. В появившемся окне Формат оси выберем вкладку Число.

8. Далее выбираем Числовые форматы: Процентный, число десятичных знаков: 2. Нажмите Закрыть.

9. Выбираем Горизонтальную ось (категорий), аналогично с пунктом 5.

10. Далее выполните команду: Макет/(Текущий фрагмент)Формат выделенного объекта.

11. В появившемся окне Формат оси выберем вкладку Выравнивание. В строке Направление текста в выпадающем списке выбираем пункт Повернуть весь текст на 270. Нажимаем Закрыть.

12. Выбираем Ряд1, аналогично с пунктом 5.

13. Далее выполните команду: Макет/(Подписи) Подписи данных.

14. В выпадающем списке выберите пункт Дополнительные параметры подписей данных.

15. В открывшемся окне, по аналогии с предыдущими пунктами, выбираем также Числовой формат: процентный, число десятичных знаков: 1.

16. Во вкладке Параметры подписи установите следующие параметры: Включить в подписи: значения и Положение подписи: у вершины, снаружи Нажимаем Закрыть.

17. Аналогично предыдущим пунктам 5 и 6 выберите Область построения. В открывшемся окне Формат области построения выбираем: Заливка/сплошная заливка. В строке Цвет выбираем нужный нам цвет. Нажимаем Закрыть.

18. При построении диаграммы справа у нас есть надпись: Ряд1. Это Легенда, уберем ее выполнив команду: Макет/(подписи)Легенда/Нет.

19. Расположите диаграмму под таблицей и установите ей соответствующий размер.

Изменение типа диаграммы.

1. Для того чтобы изменить тип диаграммы, щелкните на диаграмму.

2. Выполните команду Конструктор / (Тип) Изменить тип диаграммы / Объемная коническая.

Выбор цвета, границ и заливки допускается в маркерах данных, в области диаграммы, в области построения и в других элементах диаграммы.

Следующая процедура используется для изменения цветов, узора, ширины линии или типа рамки для маркеров данных, области диаграммы, области построения, сетки, осей и подписей делений на плоских и объемных диаграммах:

1. Щелкните левой кнопкой мыши на область диаграммы.

2. При необходимости заливки фигуры выберите Формат/(Стили фигур)Заливка фигуры и укажите нужный вид во вкладках Градиентная или Текстура. (В нашем примере Формат/(Стили фигур)Заливка фигуры/Желтый цвет

3. При необходимости изменения вида диаграммы пользуйтесь вкладками Конструктор, Макет или Формат.

Третий урок

Работа со списками

Понятие списка

В Microsoft Excel 2010 под списками подразумеваются таблицы со связанными между собой данными. Имеется набор функций облегчающих обработку и анализ данных: сортировка, поиск информации, фильтрация данных и т.д. Списком может быть, например, справочник или ведомость. Для того чтобы таблица была списком необходимо, чтобы ее столбцы имели имена и содержали однотипные данные. Создадим документ со списком следующего вида.

1. Создайте новую книгу. Для этого выполните команду Файл / Создать / Новая книга и нажмите кнопку Создать.

2. Курсор мыши поместите в рабочую область программы. Наберите в ячейках А1-Е1 имена полей: фамилия, имя, отчество, адрес, телефон.

3. Выделите ячейки А1-Е1 и установите шрифт Times New Roman 14 пунктов, а затем выровняйте по центру.

4. Заполните все остальные ячейки текстом шрифта Arial 10 пунктов.

5. Установите внутренние и внешние границы для таблицы. Для этого выделите таблицу и выберите команду Главная / (ячейки) Формат / Формат ячеек. Затем выберите вкладку Граница и щелкните на кнопки: Внешние и Внутренние.

6. Отрегулируйте ширину столбцов по содержимому. Для этого выделите таблицу и выполните команду Главная / (ячейки) Формат /Автоподбор ширины столбца.

 

Таким образом, строки списка становятся записями, а заголовки столбцов – именами полей списка. Имена полей должны располагаться в верхней части списка и находиться в отдельных ячейках. Нужно помнить, что пустая строка указывает на окончание списка, поэтому не следует оставлять пустых строк между заголовками столбцов.

Сортировка

При вводе записей можно не обращать внимание на их порядок, т.к. в дальнейшем есть возможность сортировать списки. Отсортируем созданный только что список по полю Фамилия в порядке возрастания.

1. Для того чтобы программа Microsoft Excel 2010 обнаружила список нужно щелкнуть по любой ячейке, относящейся к списку.

2. Выполните команду Данные / (сортировка и фильтр) Сортировка. После чего программа сама выделит таблицу.

3. В окне Сортировка выберите следующие значения: сортировать по - Фамилия, затем Сортировка - з начения, в последнюю очередь Порядок - от А до Я. Нажмите кнопку ОК.

Для дальнейшего изучения приемов сортировки данных списка создадим документ следующего вида.

1. Выделите ячейки A-F. Выполните команду Главная / (Ячейки) Формат/ Ширина столбца. Задайте ширину столбца 13 см и нажмите кнопку ОК.

2. В первой строке выделите ячейки A-F.

3. На панели инструментов Главная/(Выравнивание) выполните команду Объединить и поместить в центре и напечатайте заголовок (размер шрифта 14 пунктов).

4. Зададим строкам 3 и 7 нужный вид. Для этого в строке 3 выделим ячейки A-E. На панели инструментов Главная/(Шрифт) выполните команду Границы и выберете нужные границы. Тоже самое проделайте для строки 7.

5. Напечатайте названия колонок. Для этого на панели инструментов Главная/(Шрифт) задайте тип шрифта Полужирный, формат абзаца По правому краю и напечатайте названия колонок.

6. Аналогично напечатайте названия строк.

7. Введите значения ячеек таблицы.

8. Вставьте функции в ячейки E4 - E6. Для этого выделите ячейку E4, выполните команду Формулы/(Библиотека функций)Вставить функцию, в диалоговом окне выберите функцию СУММ нажмите кнопку ОК, в окне Число1 задайте ссылки на ячейки B4: D4 и нажмите кнопку ОК. Аналогично просуммируйте строки 5-8.

9. Задайте значениям колонки Всего тип шрифта Полужирный.

10. Теперь расположите строки таблицы по количеству проданных телефонов в порядке убывания. Для этого выделите всю таблицу. Выполните команду Данные (Сортировка и фильтр), задайте в выпадающем списке установите флажок Мои данные содержат заголовки, затем Сортировать по значение Всего, сортировать по убыванию и нажмите кнопку ОК.

Получится документ следующего вида:

Несколько полезных советов в случае неправильной работы сортировки:

Перед сортировкой отобразите скрытые строки и столбцы При сортировке столбцов скрытые строки не перемещаются, а при сортировке строк не перемещаются скрытые столбцы. Перед сортировкой данных отобразите скрытые строки и столбцы.

Проверьте текущую настройку региональных параметров Порядки сортировки различны в зависимости от выбранного языка. Убедитесь в том, что в элементах панели управления Microsoft Windows Языки и стандарты или Язык и региональные стандарты параметры заданы верно. Для получения сведений об изменении региональных параметров см. соответствующий раздел справки Microsoft Windows.

Чтобы исключить из сортировки первую строку данных, которая содержит заголовки столбцов, на вкладке Главная в группе Правка щелкните элемент Сортировка и фильтр, выберите пункт Пользовательская сортировка и установите флажок Мои данные содержат заголовки.

Для включения в сортировку первой строки данных, которая не содержит заголовки столбцов, на вкладке Главная в группе Правка щелкните элемент Сортировка и фильтр, выберите пункт Пользовательская сортировка, а затем снимите флажок Мои данные содержат заголовки.

Использование фильтров

Общие сведения

При работе с большими списками, бывает удобнее отображать не весь список, а только его часть, называемую подмножеством списка. Для того чтобы выбрать записи удовлетворяющие определенным условиям и временно скрыть остальные записи используется фильтр. Фильтр используется, вводя условия фильтрации.

Чтобы установить фильтр, нужно нажать на любую ячейку списка с данными и выполнить команду Данные / (Сортировка и Фильтр) Фильтр. После этого вид списка изменится. У каждого столбца появится дополнительная кнопка, нажатие которой приводит к открытию перечня условий фильтрования соответствующего поля. Эти условия представляют собой список названий полей в соответствующем столбце, а также ряд общих условий: Все, первые 10, и т.д. Применив одно из условий мы изменим вид списка.

При использовании фильтра, все записи не входящие в подмножество, т.е. не удовлетворяющие условиям фильтра скрываются. В строке состояния программы отображается общее число записей и число записей, удовлетворяющее условию.

При фильтрации нумерация строк не меняется, а номера отфильтрованных записей выделяются другим цветом. Кнопка у названия столбца также изменяет свой цвет, показывая, что она используется в качестве условия для фильтра.

Чтобы отменить применение фильтра нужно выбрать пункт Все в выпадающем списке. Программа позволяет создавать довольно сложные фильтры, используя пункт условие. Появляющийся при этом диалог содержит несколько выпадающих списков. Левый верхний список содержит логические операции, такие как: равно, больше, меньше и т.д., так и условие для поиска данных: содержащих или не содержащих данный текст. Полей для операции два, и они объединяются по правилу И или ИЛИ. Правые списки содержат значения условий из списка фильтрации. После завершения форматирования условия нажмите кнопку ОК.

Чтобы завершить работу с автофильтром необходимо еще раз выполнить команду Данные/(Сортировка и Фильтр)Фильтр.

Фильтрация

Создадим таблицу следующего вида для изучения приемов фильтрации.

1. Заполните первую строку таблицы полужирным шрифтом Arial 12 пунктов.

2. Выделите таблицу и выполните команду Главная/(Ячейки)Формат/ Автоподбор ширины столбца.

3. Выделите таблицу и установите все границы. Для этого выполните команду: Главная/(Шрифт)/Границы/Все границы.

Выполнимфильтрацию для данного документа. Выберем из списка только тех студентов, фамилия которых начинается с буквы после В, т.е. Г; имена которых начинаются на В; отчество не начинается с буквы Р и не содержит букву Е и номер дома или квартиры которых не равен 30.

1. Установите курсор на ячейку списка, содержащую данные и выполните команду Данные/(Сортировка и фильтр)Фильтр.

2. Нажмите на значок в ячейке с именем поля Фамилия и выберите Текстовый фильтр/Настраиваемый фильтр. Выберите в левом верхнем поле диалога логическую функцию больше, и установите значение функции в правом верхнем поле: В, после чего нажмите ОК.

3. Нажмите на значок в ячейке с именем поля Отчество и выберите Текстовый фильтр/Настраиваемый фильтр. Выберите в левом верхнем поле диалога логическую функцию не начинается с, и установите значение функции в правом верхнем поле: Р; установите переключатель на И, в левом нижнем поле выберите: не содержит, и установите значение функции: Е, после чего нажмите ОК.

4. Нажмите на значок в ячейке с именем поля Адрес и выберите Текстовый фильтр/Настраиваемый фильтр. Выберите в левом верхнем поле диалога логическую функцию не содержит, и установите значение функции в правом верхнем поле: 30, после чего нажмите ОК. Получаем список следующего вида.

Графическое представление фильтрованных данных Создадим таблицу следующего вида.

1. В каждую ячейку введем данные.

2. В ячейку С10 введем формулу =сумм (С2:С9). Введем формулы в ячейки D10:H10. Для этого выделим ячейку С10 и, нажав левой кнопкой мыши на ее правый нижний угол, перемещаем мышь до H10.

3. Левой кнопкой мыши выделим все ячейки, с помощью правой кнопки мыши выберем Формат ячеек.

4. Во вкладке Граница выбираем внешние и внутренние.

5. Во вкладке Заливка выберем нужный цвет.

6. Во вкладке Шрифт выберем начертание – полужирный, размер 10.

7. Выделяем мышкой первые два столбца таблицы, выбираем в меню Данные/(Сортировка и фильтр)Фильтр. В ячейках город и агент появится кнопочка со стрелкой, обозначающей наличие списка.

8. Щелкнем по стрелочке в столбце Город. Появится список всех введенных нами городов. Выберем строку “Москва”, будут показаны только результаты работы московских агентов.

Создадим диаграмму для этой таблицы.

1. Выделим ячейки A1:H10.

2. Выберем команду Вставка/(Диаграмма)Гистограмма

3. Во всплывшем окошке выбираем Гистограмма с группировкой.

 

 

4. Во вкладке Макет/Подписи выберем Название диаграммы. Во всплывающем окошке выберем над диаграммой. Введем название: «количество проданных деталей».

5. Диаграмму можно растянуть, установив мышь в угол области диаграммы, нажать левой кнопкой, удерживая его, растянуть таким образом, чтобы его правая и левая граница совпадали с размером таблицы.

6. Если в таблице нажать кнопочку со стрелкой (в ячейке город), и выбрать только какой-нибудь один из городов, то в диаграмме будут результаты только тех городов, которые мы выбрали. Таким же образом можно выбрать только результаты покупок каких-нибудь отдельных клиентов.

7. Например, выберем только Московских клиентов, получим диаграмму следующего вида:

Вернув таблице исходный вид (чтобы все строки были видны), добавим примечания в 10 строку, в ячейках C10:H10.

1. Левой кнопкой мыши выделим ячейку С10,.

2. с помощью правой кнопки мыши выберем Вставить примечание и введем примечание.

3. Аналогично вставим примечания в остальные ячейки десятой строки.

Задания для самостоятельной работы.

1. Создайте документ следующего вида. Сделайте сортировку по Общему баллу.

2. Создайте документ со списком своей группы, заполнив реальными данными таблицу следующего вида:

Используя Фильтр оставьте только иногородних студентов.

3. Создайте список с формулами и диаграммой следующего вида.

Четвертый урок

Логические выражения

Создадим программу для проверки знания умножения следующего вида.

1. Выделим ячейки от A1:E12.

2. Правой кнопкой мыши выбираем Формат ячеек. Во вкладке Граница выбираем внешние и внутренние, тип линии и цвет.

3. Выделяем первую строку, правой кнопкой мыши выбираем Формат ячеек. Во вкладке Заливка выбираем нужный цвет, таким же образом выделяем те ячейки, которые должны быть одного цвета.

4. В ячейки B1 и A2:A11 можно вводить любые числа, а в ячейках B2:B11 вводим ответы. Это программа для самопроверки.

5. В ячейку C1 введем слово ”проверка”.

6. В ячейки C2 введем формулу =если(B2=B1*A2;”молодец”;”подумай”), таким же образом в ячейки C3:C11. Это формула проверяет правильность введенного ответа.

7. В ячейку D1 введем слово “ошибка”.

8. В ячейку D2 введем формулу =если(C2="подумай";1;0). Так же для ячеек D3:D11 нужно написать подобную формулу. Если в проверке было слово “подумай”, то в ошибке будет стоять единица. Это удобно чтобы подсчитать общее количество ошибок.

9. В ячейку D12 введем формулу, которое вычисляет общее количество ошибок: =сумм(D2:D11).

10. В ячейку E11 введем слово ”оценка”.

11. В ячейку E12 введем формулу, которая будет оценивать ваше знание: =еcли(D12>3;”плохо”;если(D12=0;”отлично”;”хорошо”)). Это формула для выставления оценки: если количество ошибок больше трех, то оценка “плохо”, если ошибок нет то “отлично”, в других случаях “хорошо”. Рассмотрим пример, в котором есть ошибки:

Пятый урок

Трехмерные ссылки

Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все ячейки, хранящиеся между начальным и конечным именами, указанными в ссылке. Трехмерные ссылки позволяют увидеть часть таблицы данного документа в другом документе, используются при анализе данных и консолидации данных.

Создадим таблицу следующего вида, и сохраним его под именем Книга23.

1. Выделим ячейки А1:G16 ( Запись А1:G16 означает, что мы выделяем прямоугольную область от A1 до G16).

2. Правой кнопкой мыши выбираем Формат ячеек.

3. Во вкладке граница выбираем Граница внешние и внутренние.

4. Выделяем 11-ую строчку (диапазон А11:G11), щелкаем правой кнопкой мыши, выбираем объединить ячейки.

(Или выбираем значок «объединить и поместить в центре».)

5. В ячейке B10 вводим формулу =сумм(B$2:B$9) (Знак $ нам нужен для того, чтобы при копировании у нас не изменялся номер строки. Чтобы получить знак $ перед цифрой нажимаем 2 раза F4). Копируем эту формулу в C10:G10. Для этого выделяем ячейку B10, в правом нижнем углу нажимаем крестик и копируем ячейку вправо.

6. В ячейку B12 вводим формулу, которая считает общее количество проданных деталей за 6 дней только в Москве

=СУММ(B2:G2)+СУММ(B4:G4)+СУММ(B6:G6)+СУММ(B8:G8).

(Для удобства, достаточно в ячейке выбрать формулу =СУММ и выделить диапазон, указанный в скобках.)

7. В ячейку B13 введем формулу =СУММ(B3:G3).

8. В ячейку B14 введем =СУММ(B5:G5). 9. В ячейку B15 введем =СУММ(B7:G7).

10. В ячейку B16 введем =СУММ(B9:G9).

11. В ячейках C12:G16 нужно убрать внутренние границы. Выделяем эти ячейки, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница убираем внутренние.

12. Все оставшиеся ячейки заполняем так, как указано в примере.

Создадим файл следующего вида и сохраним его под именем Книга5.

1. Выделяем ячейки A1:B6, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница выбираем внешние и внутренние.

2. Ячейки А1:А6, В1 заполняем так, как показано в примере.

3. В ячейку B2 введем формулу, которая ссылаясь на другой документ, присваивает то значение, которое мы хотим увидеть.

4. В ячейку B2 введем формулу =[Книга23.xlsx]Лист1!$B$12.

Для этого в ячейку вводим знак «=», затем переходим в файл Книга 23, нажимаем курсором на ячейку В12 и нажимаем Enter. Теперь мы видим, что в ячейке В12 появилось нужное нам значение. Аналогично вводим формулы в ячейки В3:В6.

5. В ячейку B3 введем формулу =[Книга23.xlsx]Лист1!$B$13.

6. В ячейку B4 введем формулу =[Книга23.xlsx]Лист1!$B$14.

7. В ячейку B5 введем формулу =[Книга23.xlsx]Лист1!$B$15. 8. В ячейку B6 введем формулу =[Книга23.xlsx]Лист1!$B$16.

Консолидация данных.

Консолидация данных это сбор и объединение данных из разных исходных областей. Исходные области могут располагаться на любом листе или книге, на других открытых листах или книгах. Существует несколько способов консолидации. Рассмотрим консолидацию с помощью трехмерных ссылок (трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов), что является наиболее предпочтительным способом. При использовании трехмерных ссылок отсутствуют ограничения по расположению данных в исходных областях. То есть сделаем так, чтобы в ячейках искомой таблицы были ссылки на значение ячеек другой таблицы другого документа.

Сначала создадим таблицу, данные из которой будем консолидировать.

1. Создать новую Книгу.

2. Ввести данные в ячейки

3. Сохранить Книгу под именем таблица.xlsx.

Теперь консолидируем данные о гольфе и теннисе по годам в следующей таблице.

Сначала сделаем пустую таблицу.

1. Введите имена строк и столбцов таблицы.