МЕТОДИЧЕСКИЕ УКАЗАНИЯ

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

Консолидация – это агрегирование (объединение) данных, представленных в исходных областях-источниках – таблицах, списках, блоках ячеек и др.

Консолидация данных (рис. 8.1 рис. 8.1) выполняется в соответствии с выбранной функцией обработки. Результат консолидации находятся в области- назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах их может быть до 2⁸= 256.

В Excel возможны следующие варианты консолидации данных:

• с помощью формул, где используются ссылки;
• по расположению данных для одинаково организованных областей-источников (фиксированное расположение);
• по категориям для различных по структуре области данных;
• с помощью сводной таблицы;
• консолидация внешних данных.

увеличить изображение
Рис. 8.1. Представление о консолидации данных

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

• все области на одном листе – в ссылках указывается адрес блока ячеек (например, D1:C8);
• области на разных листах – в ссылках указывается название листа и диапазон (например, лист1 !D1:лист2!С8, т.е. с ячейки D1 листа 1по ячейку С8 листа 2);
• области в разных книгах, на разных листах – в ссылках указывается название книги, название листа, диапазон, например [книга1]лист1! D1:[книга2]лист2!С8.

Консолидация данных по расположению

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

Пример 1. На разных листах рабочей книги по каждому товару хранятся сведения о показателях реализации товаров за конкретный период (рис. 8.2 рис. 8.2). Консолидируемая область выделена цветом.

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

Рис. 8.2. Области-источники при консолидации данных

по расположению

Выполняется команда Данные > Консолидация, выбирается вариант и задаются условия консолидации.

Пример 2. На рабочих листах представлена информация областей-источников в виде структуры на рисунке 8.3 рис. 8.3. Число строк и столбцов – переменное, состав показателей и виды товаров могут различаться или совпадать, при консолидации по категориям они собираются вместе. Цветом показана консолидируемая область источников.

увеличить изображение
Рис. 8.3. Области-источники при консолидации данных по категориям

Условия консолидации задаются в диалоговом окне Консолидация (рис. 8.4 рис. 8.4 ). В поле Функция выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка Добавить.

Ссылка может иметь любую из следующих форм:

Консолидация данных по категориям

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

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

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

Рис. 8.4. Диалоговое окно Консолидация для задания условий консолидации

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

Примечание. Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка переключателя Создавать связи с исходными данными.

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

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

ЗАДАНИЕ

1. Предварительно подготовить 2–е таблицы для консолидации по областям, например продажа товаров по кварталам в 1 и 2, по аналогии с примером (рис. 8.5 рис. 8.5). Построить итоговую таблицу консолидированную по областям.
2. Предварительно подготовить 2 таблицы для консолидации по категориям (см. рис. 8.3 рис. 8.3) – дополнить 2-ую таблицу (копию) новым столбцом и новой строкой. Построить итоговую таблицу, консолидированную по категориям.

Пояснения к выполнению

Консолидация по областям

1. Создать таблицу своего варианта ("например, Показатели в 1 квартале") на новом листе, переименовать лист в "Консолидация" (дважды щелкнуть мышью по имени и ввести новое имя).

2. Сделать копию таблицы на том же листе, изменить в ней данные. Эта таблица будет отражать, например, показатели во 2 квартале (рис. 8.5 рис. 8.5).

3. Выполнить консолидацию данных по расположению:

• установить курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A10;
• выполнить команду Данные > Консолидация;
• в диалоговом окне Консолидация выбрать из списка функцию Сумма и установить флажки подписи верхней строки и значения левого столбца;
• установить курсор в окне Ссылка и выделить блок ячеек А2:D7 (показатели в 1 квартале);
• нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
• установить курсор в окне Ссылка, удалить прежнюю запись и выделить блок ячеек F2:I7 (показатели во 2 квартале);
• нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
• нажать кнопку ОК и сравнить полученные результаты с рисунка8.5.

Консолидация по категориям

1. Добавить строку с товаром TV-тюнер с соответствующими числами в первую таблицу "Показатели в 1 квартале" (рис. 8.6 рис. 8.6).

2. Вставить новый столбец с именем % реализации и заполнить формулой Продано*100/ Получено. Чтобы выводилось 2 знака после запятой, в меню Формат > Ячейки, вкладка Число выбрать в поле Числовые форматы строку Числовой и установить Число десятичных знаков – 2;

увеличить изображение
Рис. 8.5. Пример консолидации данных по расположению

увеличить изображение
Рис. 8.6. Пример консолидации данных по категориям

3. Сделать консолидацию данных по категориям:

• установить курсор в первую ячейку области, где будет располагаться консолидиpoванная таблица, например в ячейку A11;
• выполнить команду Данные > Консолидация;
• в диалоговом окне Консолидация выбрать из списка функцию Сумма и установить флажки подписи верхней строки и значения левого столбцов;
• установить курсор в окне Ссылка и выделить блок ячеек A2:Е8 (показатели в 1 квартале);
• нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
• установить курсор в окне Ссылка, удалить предыдущую запись и выделить блок ячеек G2:J7 (показатели во 2 квартале);
• нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
• нажать кнопку <ОК> и сравнить результаты с рис. 8.6 рис. 8.6.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Что называется консолидацией?
2. Где располагается результат консолидации?
3. Что такое области-источники и где они могут располагаться?
4. Какие существуют варианты консолидации?
5. Из чего состоит область консолидации при объединении данных по расположению?
6. Из чего состоит область консолидации при объединении данных по категориям?