5.2. Консолидация по расположению.
При консолидации данных по расположению собирается информация из одинаково расположенных ячеек всех исходных листов. При консолидации по расположению Excel применяет итоговую функцию Сумма, Количество, Среднее, Максимум, Минимум, Произведение и т. п. к ячейкам с одинаковыми адресами во всех исходных листах. Для консолидации по расположению необходимо проделать следующие действия.
Выделить конечную область помещения консолидированных данных, поместив выше и левее данной области заголовки столбцов и строк исходных данных.
Командой Консолидация меню Данные открыть диалоговое окно Консолидация (см. рис. 5.1).
Рис. 5.1. Диалоговое окно консолидации листов
В открывшемся диалоговом окне выбрать итоговую функцию консолидации данных.
В поле Ссылка ввести все диапазоны консолидации по очереди, нажимая после указания каждого диапазона клавишу Добавить.
Использование мыши является наиболее удобным способом ввода ссылок, но если приходится ссылаться на листы других книг, можно для указания местонахождения файла использовать клавишу Обзор, а затем вручную добавлять ссылку на ячейку. Вводимая ссылка должна иметь следующий вид:
[Имя_файла]Имя_листа!Ссылка.
Исходные ссылки должны содержать только фактические значения, которые необходимо консолидировать, не включая заголовков столбцов и строк консолидируемых данных.
Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне, установить флажок Создавать связи с исходными данными.
Следует помнить, что если данный флажок установлен, изменить набор ячеек и диапазонов, входящих в консолидацию, невозможно. Данная функция доступна только при обновлении консолидации вручную.
Все поля в группе Использовать в качестве имен оставить пустыми.
5.3. Консолидация по категории.
При консолидации данных по категории Excel использует в качестве основы для объединения листов заголовки столбцов или строк, что требует одинаковых заголовков столбцов или строк у всех диапазонов консолидации. Консолидация по категории предоставляет большую свободу для организации исходных данных.
Например, усредним значения из трех списков, имеющих различное количество строк, но одинаковые столбцы (см. рис. 5.2).
Рис. 5.2. Три листа списков источников, консолидируемых по категории
Для того чтобы консолидировать списки по категории, необходимо проделать следующие действия.
Выделить конечную область консолидации. Удобней всего для задания конечной области выделить одну ячейку, ниже и правее которой будет создан список консолидации.
С помощью команды Консолидация меню Данные открыть диалоговое окно Консолидация (см. рис. 5.1).
В открывшемся диалоговом окне выбрать итоговую функцию консолидации данных.
Для заполнения Списка диапазонов нужно щелкнуть мышью в поле Ссылка, открыть лист, содержащий первый диапазон данных для консолидации, ввести имя этого диапазона и нажать кнопку Добавить. Произвести аналогичные действия для всех диапазонов.
Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне, установить флажок Создавать связи с исходными данными.
В группе Использовать в качестве имен установить флажки, соответствующие расположению подписей в исходных диапазонах: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно. Все подписи, не совпадающие с подписями в других исходных областях, в консолидированных данных будут расположены в отдельных строках или столбцах.
В примере, показанном на рисунке 5.2, в качестве имен будут использоваться значения левого столбца, т. к. все списки содержат одинаковые заголовки столбцов и различные заголовки строк. Скопировав заголовки столбцов в итоговый список консолидации данных, мы получим итоговый лист, изображенный на рисунке 5.3.
Рис. 5.3. Консолидация данных по категории
Итоговый лист содержит строки, которые соответствуют уникальным элементам строк исходных листов. В нашем примере консолидированный список имеет отдельную строку для каждого студента, упомянутого в исходных листах. Если несколько листов имеют одинаковые элементы строк, итоговый лист выполняет выбранную математическую операцию на соответствующих значениях в каждом столбце.