Консолидация данных. Другим способом получения итоговой информации является консолидация согласно выбранной функции обработки данных

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

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

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

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

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

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

Рис. 23

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

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

Рис. 24

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

Рис. 25

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

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

- ссылки на ячейки – источники и назначение на одном листе;

- ссылки на лист и ячейки – источники и назначение на разных листах;

- ссылки на книгу, лист и ячейки – источники и назначение в разных книгах;

- полный путь и все ссылки – источники и назначение в различных местах диска;

- имя поименованной области – область-источник поименована;

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

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

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

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

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

Оптимизация данных в электронных таблицах