Способы объединения данных

Модуль 2.12. Объединение данных

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

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

Рассмотрим способ объединения данных с помощью команды Консолидация, расположенной на вкладке Данные в разделе команд Работа с данными.

Задание 1. Создание консолидирующнй таблицы.

Задача: Мини-пекарня реализует свою продукцию через три торговые точки: две булочные и одну чайную. Требуется создать сводку реализации товара за один день.

Решение: Используйте такую процедуру, как консолидация данных. Она позволяет создать таблицу-сводку по одной или нескольким категориям данных, используя один или несколько блоков данных.

1. Откройте книгу Упражнения.xlsx.

2. Добавьте в книгу новый лист с именем Упр.12-а.

3. Введите исходные данные (рис. 12.1).

4. Перед созданием консолидирующей таблицы необходимо скрыть столбец С, чтобы данные этого столбца не вошли в нее.

Рис. 12.1. Исходные данные для консолидации

5. Выделите ячейку, определяющую положение итоговой таблицы. Например, F1 или A22.

6. Выберите команду Консолидация.

7. В диалоговом окне Консолидация (рис. 12.2) в поле Функция укажите функцию Сумма.

Обратите внимание, что кроме суммирования в Excel существует еще другие способы подведения итогов. Откройте список для просмотра в поле Функция.

8. Установите курсор в поле Ссылка.

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

Рис. 12.2. Диалоговое окно Консолидация

9. Выделите первую исходную область A4:D7.

10. Выполните команду Добавить.

11. Повторите действия, описанные в пп. 5-7, для диапазонов A9:D13, A15:D18.

12. Установите переключатель Значения левого столбца.

13. Нажмите кнопку ОК. В результате появится результирующая таблица, показанная на рис. 12.3.

Рис. 12.3. Результирующая таблица

Обратите внимание, что строки Итого из исходной таблицы также участвуют в операции консолидации (см. рис. 12.1).

Переключатель Создавать связи с исходными данными нужно устанавливать в том случае, когда итоговая область размещена на другом рабочем листе. Если последняя находится на том же листе, что и исходные области, то установка этого флажка вызывает сообщение об ошибке (рис. 12.4).

Рис. 12.4. Информационное окно об ошибке

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

Самостоятельно измените данные в исходных таблицах и повторите консолидацию.

Способы объединения данных

На листе Упр.11-б создайте таблицу, взяв за основу структуру таблицы с листа Лаб_2 (см. рис. 5.14) из книги Лабораторные работы.xls,данные за январь–март обнулите, оставив итоговые формулы.

Задание 2. Консолидация данных при помощи команд Копировать и Специальная вставка.

1. Скопируйте исходные данные за январь – март, расположенные на листе Отд_1 с помощью команды Копировать в ячейки с теми же адресами на лист Упр.11-б.

2. Сложите значения столбцов январь – март листа Отд_2 со зна­­чениями соответствующих ячеек на листе Упр.11-б с помощью команды Специальная вставка из меню Правка.

3. Таким же образом добавьте значения с листа Отд_3.

Задание 3. Консолидация данных при помощи Формулы Внешних Ссылок.

1. Сделайте копию листа Упр.11-б в этой же книге, он автоматически получит название Упр.11-б (2).

2. Выделите диапазон ячеек B3:D11 и удалите из них данные.

3. Выполните команду Автосумма.

4. Выделите все листы с данными: Отд_1, Отд_2, Отд_3 следующим образом:

5. Выделите лист Отд_1, затем при нажатой клавише Shift выделите Отд_3.

6. Укажите ячейку B3.

7. Для завершения формулы внешних ссылок воспользуйтесь комбинацией клавиш Ctrl + Enter. Вы получите формулу:

=СУММ(Отд_1!:Отд_3!В3).

Задание 4. Консолидация данных при помощи команды Копировать и Вставить связь.

1. Сделайте копию листа Упр.11-б в этой же книге, он автоматически получит название Упр.11-б (3).

2. Выделите диапазон ячеек B3:D11 и удалите из них данные.

3. Измените названия столбцов: вместо Январь, Февраль и Март соответственно Отдел № 1, Отдел № 2 и Отдел № 3, а вместо 1 кв. – Январь.

4. Скопируйте значения столбца Январь листа Отд_1 и поместите в ячейки В3:В11 на лист Упр.11-б (3) с помощью команды Специальная вставка, Вставить связь.

5. Скопируйте значения столбца Январь листа Отд_2 и поместите в ячейки С3:С11 Упр.11-б (3) таким же образом.

6. Аналогично поступите с январскими данными на листе Отд_3.

Задание 5. Консолидация данных при помощи команды Данные, Консолидация.

1. Сделайте копию листа Упр.11-б в этой же книге, он автоматически получит название Упр.11-б (4).

2. Выделите диапазон ячеек B3:D11 и удалите из них данные.

3. Выделите ячейку А2.

4. Выберите команду Консолидация.

5. Убедитесь, что установлены переключатели: Подписи верхней строки, Значения левого столбца, Создавать связи с исходными данными.

6. Установите курсор в поле Ссылка.

7. Выделите область А2:Е12 листа Отд_1, затем команду Добавить.

8. Добавьте таким же образом диапазоны данных листов Отд_2 и Отд_3.