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

Консолидация – это операция объединения данных из разных листов или рабочих книг в одну таблицу. Обычно консолидируют только однотипные записи.

Объединение данных из исходных листов в итоговом листе обеспечивается командой Данные – Консолидация. Исходные листы могут находиться в той же книге, в которой находится итоговый лист, а также в других книгах, в том числе, и в закрытых. В консолидации может участвовать до 255 источников.

Консолидация данных может производиться разными способами. Можно выполнить консолидацию с созданием связей с исходными листами. При этом все последующие изменения в них будут автоматически отображаться в итоговом листе. Это – динамическая консолидация. Статическая консолидация не предусматривает создания связей между исходными и итоговыми листами.

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

— с помощью формул, где используются ссылки;

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

— по категориям для различающихся по своей структуре областей данных;

— с помощью сводной таблицы;

— объединение внешних данных.

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

— все области на одном листе – в ссылках указывается адрес блока ячеек, например: D1.C8;

— области на разных листах – в ссылках указывается название листа, диапазон, например: лист1!D1:лист2!С8;

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

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

При консолидации по расположению MS Excel применяет итоговую функцию к ячейкам с одинаковыми адресами в каждом исходном листе. Консолидируемые данные во всех исходных листах должны иметь одинаковое расположение.

Пример. Имеются две таблицы на листах 1семестр, 2семестр (рис. 5.29) с идентичной структурой. На листе Среднее будет расположена итоговая таблица, которая должна содержать усредненные данные успеваемости для каждого студента по каждому предмету.

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

Активизировать итоговый лист и выделить конечную область, т.е. блок ячеек, куда будут помещены результаты консолидации (диапазон В2:Е4).

Выбрать команду Данные – Консолидация. В диалоговом окне Консолидация (рис. 5.30) в поле Функция выбрать Среднее.

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

Если исходные листы находятся в той же книге, для выделения диапазонов удобно использовать мышь. В указанном примере ссылка для первого исходного листа будет иметь вид: 1семестр!$B$2:$E$4.

После ввода ссылки необходимо нажать кнопку Добавить, при этом осуществляется перенос ссылки из поля Ссылка в поле Список диапазонов. После ввода ссылок для всех исходных листов нажать кнопку ОК. Результатом консолидации будет таблица, представленная на листе Среднее (рис. 5.31).

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

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

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

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

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

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

Пусть в условиях предыдущего примера списки студентов отличаются по их количеству в разных семестрах (рис. 5.32).

Для консолидации данных необходимо выполнить следующие действия:

Активизировать итоговый лист Среднее, в котором введены заголовки столбцов, и установить курсор в ячейку А2, являющуюся левой верхней границей конечной (итоговой) области.

Выбрать команду Данные – Консолидация и заполнить окно диалога Консолидация. В поле Функция необходимо выбрать значение Среднее и установить переключатель Значения левого столбца секции Использовать в качестве имен. В поле Ссылка нужно последовательно установить исходные диапазоны данных, включая все заголовки строк:

Дата добавления: 2014-12-27