Глава 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. Информационное окно об ошибке

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

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