Глава 12. объединение данных
С помощью команды Консолидация вы можете объединять данные из нескольких исходных рабочих листов (до 255) в одном итоговом листе. При этом исходные листы могут располагаться в той же самой книге, где находится итоговый лист, или в других книгах. Все таблицы должны иметь абсолютно идентичную структуру. Соединение не является механическим. Итоговая таблица будет содержать только одну строку с ключевым полем, а числовые данные в ней будут суммами (или другими функциями) всех строк объединенного подмножества. Ключевым полем считается самое левое поле (колонка) таблицы или ее выделенной части. Консолидация может быть применена и к одной таблице. Для выполнения консолидации предварительная сортировка не нужна.
Консолидация данных
Рассмотрим способ объединения данных с помощью команды Консолидация, расположенной на вкладке Данные в разделе команд Работа с данными.
Задание 1. Создание консолидирующей таблицы.
Мини-пекарня реализует свою продукцию через три торговые точки: две булочные и одну чайную. Требуется создать сводку реализации товара за один день.
Ваши действия:
1. Откройте книгу Упражнения.xlsx.
2. Добавьте в книгу новый лист с именем Упр.12.
3. Введите исходные данные (рис. 12.1).
4. Перед созданием консолидирующей таблицы необходимо скрыть столбец С, чтобы данные этого столбца не вошли в нее.
Рис. 12.1. Исходные данные для консолидации
5. Выделите свободную ячейку, определяющую положение итоговой таблицы. Например, F1.
6. Выберите команду Данные, Работа с данными, Консолидация.
7. В диалоговом окне Консолидация (рис. 12.2) в поле Функция укажите функцию Сумма.
Обратите внимание, что кроме суммирования в Excel существует еще другие способы подведения итогов. Откройте список для просмотра в поле Функция.
8. Установите курсор в поле Ссылка.
Обратите внимание, что в этой строке находится кнопка, которая позволит свернуть окно до минимального размера, оставив лишь строку ввода. После выделения блока ячеек та же кнопка позволит развернуть окно до его первоначального размера.
Рис. 12.2. Диалоговое окно Консолидация
9. Выделите первую исходную область A4:D7.
10. Выполните команду Добавить.
11. Повторите действия, описанные в пп. 8-10, для диапазонов A9:D13, A15:D18.
12. Установите переключатель Значения левого столбца.
13. Нажмите кнопку ОК. В результате появится итоговая таблица, показанная на рис. 12.3.
Рис. 12.3. Консолидирующая таблица
Обратите внимание, что строки Итого из исходной таблицы также участвуют в операции консолидации (см. рис. 12.1).
Переключатель Создавать связи с исходными данными нужно устанавливать в том случае, когда итоговая область размещена на другом рабочем листе. Если последняя находится на том же листе, что и исходные области, то установка этого флажка вызывает сообщение об ошибке (рис. 12.4).
Рис. 12.4. Информационное окно об ошибке
Основной недостаток консолидации заключается в том, что в итоговой таблице не производится автоматический пересчет данных. Если вы измените данные в исходной таблице, то значения в итоговой таблице останутся старыми. Чтобы их обновить, необходимо запустить консолидацию еще раз и получить новую результирующую таблицу. Исходные данные в окне Консолидация сохраняются.
Самостоятельно измените данные в исходных таблицах и повторите консолидацию.
Способы объединения данных
В 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.
При этом методе объединения данных изменения, внесенные в исходные данные также будут отображены в таблице консолидации.
Самостоятельная работа 12. Использование консолидации
На листе Лаб.12 в книге Лабораторные работы.xlsx рассчитайте общую смету расходов по трем отделам Администрация, Исследования, Маркетинг, расположенных на листах с соответствующими именами.
- На листе Администрация создайте таблицу исходных данных (рис. 12.5).
- Структура таблиц на листах Исследования и Маркетинг аналогичны таблице листа Администрация.
- Данные на листах должны быть разными.
| Январь | Февраль | Март | Апрель | Май | Июнь | |
| Зарплата | 13000 р. | 13000 р. | 13000 р. | 13000 р. | 13000 р. | 13000 р. |
| Акции | 2000 р. | 2000 р. | 2000 р. | 2000 р. | 2000 р. | 2000 р. |
| Расходы | 50000 р. | 50000 р. | 50000 р. | 50000 р. | 50000 р. | 50000 р. |
| Реклама | 10000 р. | 10000 р. | 10000 р. | 10000 р. | 10000 р. | 10000 р. |
| Запасы | 24000 р. | 24000 р. | 24000 р. | 24000 р. | 24000 р. | 24000 р. |
| Итого | 99000 р. | 99000 р. | 99000 р. | 99000 р. | 99000 р. | 99000 р. |
Рис. 12.5. Исходные данные
1. Консолидация данных при помощи команд Копировать и Специальная вставка.
- На лист Лаб.12 скопируйте данные с листа Администрация.
- Добавьте к этим данным с помощью команды Специальная вставка, Сложить данные с листов Исследования и Маркетинг.
2. Консолидация данных при помощи трехмерных ссылок.
- Сделайте копию листа Лаб.12,данные удалите.
- Объедините данные за каждый месяц всех трех отделов при помощи формулы внешних ссылок.
3. Консолидация данных при помощи команд Копировать и Специальная вставка, Вставить связь.
- Сделайте копию листа Лаб.12,данные удалите.
- Рассчитайте общую сумму расходов по всем статьям для трех отделов за ЯНВАРЬ. Для этого видоизмените таблицу.
4. Консолидация данных при помощи командыКонсолидация.
- Сделайте копию листа Лаб.12,данные удалите.
- Рассчитайте общую смету расходов по трем отделам, используя командуКонсолидация.
Вопросы для самоконтроля
1. Что такое консолидация?
2. Перечислите способы консолидации.
3. Какой командой можно объединить данные?
4. На какой вкладке находится команда Консолидация?
5. Можно ли объединить данные разных листов?
6. Можно ли объединить данные из разных книг?
7. При каком способе объединения данных при изменении исходных данных результирующая таблица не меняется?
Тесты
1. На какой вкладке находится команда Консолидация?
A. Главная.
B. Вставка.
C. Формулы.
D. Данные.
2. Консолидация – это …
A. Объединение данных.
B. Перемещение данных.
C. Сложение данных.
D. Проверка данных.
3. Можно ли объединить данные разных листов?
A. Да.
B. Нет.
4. Можно ли объединить данные из разных книг?
A. Да.
B. Нет.
5. С помощью какой клавиши можно объединить листы в группу?
A. Shift.
B. Alt.
C. Ctrl.
D. Enter.
6. Какие математические операции можно использовать при выполнении команды Специальная вставка?
A. Сложить.
B. Умножить.
C. Вычесть.
D. Разделить.
7. В каком диалоговом окне находится команда Вставить связь?
A. Специальная вставка.
B. Консолидация.
C. Подбор параметра.
8. С помощью какой клавиатурной комбинацией клавиш можно распространить формулу по выделенному диапазону?
A. Ctrl + Enter.
B. Shift + Enter.
C. Alt + Enter.