1.2Выполнение консолидации

1. Установить курсор в левую верхнюю ячейку диапазона, в который должны быть помещены консолидированные данные. Результат консолидации может располагаться на новом листе или на листе с любой из консолидируемых таблиц.

2. Меню Данные – Консолидация. При этом появляется диалоговое окно, в которое необходимо ввести следующие данные:

а) выбрать из списка итоговую операцию

б) ввести адрес диапазона, в котором расположен консолидируемый список

в) нажать кнопку для помещения адреса таблицы в список диапазонов

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

д) нажать для выполнения консолидации

Замечание 1: Если консолидируемые списки находятся в закрытых книгах, то для открытия книги и перехода к ней используется кнопка . При этом появляется диалоговое окно открытия книги.

Замечание 2: Адрес консолидируемого диапазона удобнее вводить указанием мыши, для этого перейти на нужный лист и обвести мышью нужный диапазон. Для занесения адреса в список диапазонов нажать кнопку . Ввод адреса и добавление его в список выполняется столько раз, сколько имеется консолидируемых списков. Если был введен неверный адрес диапазона, то для удаления его из списка нажать кнопку .

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

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

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

1.3Варианты консолидации

Вариант 1. Установлены оба флажка:

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

Пример 4. Вычислить суммарный объем и суммарные расходы на ремонт двух аудиторий 301 и 305 по каждому виду работ.

1. Подготовить данные для консолидации в следующем виде:

2. Перейти на новый лист и установить курсор в ячейку А1.

3. Меню Данные – Консолидация.

4. Заполнить диалоговое окно и получить результат:

Занести в ячейку А1 заголовок Вид работ. Замечание. Заголовки итоговой таблицы форматируются по умолчанию. При необходимости выполнить нужное форматирование.

Вариант 2. Не установлен ни один флажок:

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

Пример 5. Составить отчет о составе студентов по факультету.

Эту задачу можно решить двумя способами.

Способ 1. Выполним консолидацию по расположению. В исходные диапазоны включим заголовки строк и столбцов. Левой верхней ячейкой итоговой таблицы является ячейка А1 нового листа. Заполнение диалогового окна и результат консолидации:

пусто, т.к. выполняется консолидация по расположению, а в диапазоны включены заголовки строк и столбцов

Способ 2. Выполним консолидацию по категории с теми же исходными данными.

Получим следующую таблицу:

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

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

Вариант 3. Установлены следующие флажки:

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

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

Вариант 4. Установлены следующие флажки:

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

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

Контрольные вопросы

1. Понятие консолидации.

2. Виды консолидации, их отличие.

3. Выполнение консолидации.

4. Какие параметры диалогового окна Консолидация должны быть установлены, чтобы выполнить консолидацию по расположению для строк и столбцов?

5. Какие параметры диалогового окна Консолидация должны быть установлены, чтобы выполнить консолидацию по категориям для строк и столбцов?

6. Какие параметры диалогового окна Консолидация должны быть установлены, чтобы выполнить консолидацию по категориям для строк и по расположению для столбцов?

7. Какие параметры диалогового окна Консолидация должны быть установлены, чтобы выполнить консолидацию по категориям для столбцов и по расположению для строк?

8. Связывание с исходными данными. Ограничение?