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

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

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

Существуют следующие варианты консолидации:

− консолидация по расположению для одинаково организованных источников (фиксированное расположение);

− консолидация по категориям для различающихся по расположению данных;

− консолидация внешних данных.

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

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

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

Если консолидируются данные по категориям, указывается тип меток в верхней строке и левом столбце.

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

Изучая процесс консолидации, будем оперировать данными из таблиц, отображенных на рис.103 (а, б, в)

а)

б)

в)

Рисунок 103 - Данные об объемах продаж в филиалах: а) − Оренбургском, б) − Бузулукском, в) − Орском

Задание: Используя операцию консолидации вычислить суммарный объем продаж автомобилей по всем филиалам.

Технология работы:

1. В строке «Всего» для каждой таблицы просуммируйте данные.

2. На Листе4 активизируйте ячейку А4, которая послужит началом диапазона ячеек с итогами.

3. Активизируйте команду Консолидация меню Данные.

4. В одноименном окне Консолидация в поле Функция выберите функцию, которая будет использоваться при объединении данных (а нашем примере − Сумма).

5. Перейдите в поле Ссылка, чтобы задать координаты первого диапазона ячеек с данными, подлежащими консолидации.

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

7. Введите в поле Ссылка адреса всех консолидируемых диапазонов.

8. Установите опцию значения левого столбца в области использовать в качестве имен диалогового окна Консолидация. Таким образом вы зададите консолидацию по именам. При этом значения в строках с одинаковыми метками будут просуммированы.

9. Установите опцию создавать связи с исходными данными (рис.104), чтобы между исходными данными и результатами консолидации была установлена динамическая связь, обеспечивающая автоматическое обновление данных.

Рисунок 104 - Диалоговое окно Консолидация, в котором заданы параметры консолидации

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

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

Задания для самостоятельного выполнения:

1. Таблицу с консолидированными данными отформатировать, задать границы (рис.105).

2. Построить диаграмму на основе таблицы с консолидированными данными.

Рисунок 105 - Консолидированные данные

Вопросы для самоконтроля

1. Как формируются итоги в списках по заданным критериям?

2. Перечислите функции, которые могут использоваться при вычислении итогов?

3. Что такое консолидация таблиц?

4. Какие варианты консолидации вы знаете?

5. Укажите основные особенности каждого вида консолидации.

Дата добавления: 2014-10-31