В Excel кроме основной команды Консолидация существует несколько других способов для объединения данных. На новый лист с именем Упр.12-а скопируйте таблицу исходных данных, расположенную на листе Лаб.5 (см. рис. 5.14) из книги Лабораторные работы.xlsх,данные за январь–март обнулите, оставив итоговые формулы.
Задание 2. Консолидация данных при помощи команд Копировать и Специальная вставка.
Ваши действия:
1. Скопируйте числовые данные за январь – март, расположенные на листе Отд.1,с помощью команды Копировать в Буфер Обмена.
2. Перейдите на лист Упр.12-а и установите курсор в ячейке В3, затем выполните команду Специальная вставка из контекстного меню, вызванного по правой клавише мыши. Появится диалоговое окно Специальная вставка (рис.12.5):
- В поле Вставить выберите – значения;
- В поле Операция выберите – сложить.
3. Повторите выполнение пунктов 1-2 с данными, расположенными на листах Отд.2 и Отд.3.
Изменения, внесенные на листах с исходными данными никак не влияют на итоговые данные, так как операция сложения данных с разных листов происходит в памяти машины, а готовый результат помещается в ячейки итогового листа.
Рис.12.5. Диалоговое окно Специальная вставка
Задание 3. Консолидация данных при помощи трехмерных ссылок.
Трехмерные ссылки используются при выполнении действий с данными из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. При этом в формулу включаются все листы, хранящиеся между начальным и конечным именами листов, указанными в ссылке.
Ваши действия:
1. Сделайте копию листа Упр.12-а в книге Упражнения.xlsx, он автоматически получит имя Упр.12-а(2).
2. Выделите диапазон ячеек B3:D11 и удалите из них данные.
3. При выделенном диапазоне выполните команду СУММ().
4. Выделите группу листов с данными: Отд.1, Отд.2, Отд.3 следующим образом: сначала выделите лист Отд.1, затем при нажатой клавише Shift выделите Отд.3.
5. Укажите ячейку B3.
6. Для завершения трехмерной ссылки воспользуйтесь комбинацией клавиш Ctrl + Enter. Эта клавиатурная комбинация позволит распространить полученную формулу по всему выделенному диапазону.
=СУММ(Отд.1!:Отд.3!В3)
Теперь по этой трехмерной ссылке будут сложены все ячейки В3 и их сумма сохранена на листе Упр.12-а(2).
Любое изменение данных на листах Отд.1, Отд.2 и Отд.3 приводят к изменению данных на итоговом листе.
Задание 4. Консолидация данных при помощи команды Копировать и Вставить связь, расположенной в диалоговом меню Специальная вставка.
Ваши действия:
1. Сделайте копию листа Упр.12-а в этой же книге, он автоматически получит название Упр.12-а(3).
2. Измените названия столбцов: вместо Январь, Февраль и Март соответственно Отдел № 1, Отдел № 2 и Отдел № 3, а вместо 1 кв. – Январь.
3. Выделите диапазон ячеек B3:D11 и удалите из них данные.
4. Скопируйте значения столбца Январь листа Отд.1 и поместите в ячейки В3:В11 на лист Упр.12-а(3) с помощью команды Специальная вставка, Вставить связь, вызванной из контекстного меню.
Формула примет вид: Отд1!В3.
5. Скопируйте значения столбца Январь листа Отд.2 и поместите в ячейки С3:С11 Упр.12-а(3) таким же образом.
6. Аналогично поступите с январскими данными листа Отд.3.
Если ввести изменения в январские данные на листах Отд.1, Отд.2 и Отд.3, то благодаря команде Вставить связь эти данные будут отображена на итоговом листе Упр.12-а(3).
Задание 5. Консолидация данных при помощи команды Данные, Консолидация.
Ваши действия:
1. Сделайте копию листа Упр.12-а в этой же книге, он автоматически получит название Упр.12-а(4).
2. Выделите диапазон ячеек B3:D11 и удалите из них данные.
3. Выделите ячейку А2.
4. Выберите команду Консолидация.
5. Убедитесь, что установлены переключатели: Подписи верхней строки, Значения левого столбца, Создавать связи с исходными данными.
6. Установите курсор в поле Ссылка.
7. Выделите область А2:Е12 листа Отд.1, затем команду Добавить.
8. Добавьте таким же образом диапазоны данных листов Отд.2 и Отд.3.
При этом методе объединения данных изменения, внесенные в исходные данные также будут отображены в таблице консолидации.