Консолидация по расположению

Консолидация с использованием трехмерных формул.

Тема 5. Консолидация данных

1. Консолидация с использованием трехмерных формул.

2. Консолидация по расположению.

3. Консолидация по категории.

4. Использование сводной таблицы для консолидации данных.

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

§ с использованием трехмерных формул;

§ по расположению;

§ по категории;

§ с использованием сводных таблиц.

Наиболее удобным методом консолидации является метод, заключающийся в создании трехмерных формул, использующих ссылки на ячейки в каждом диапазоне объединяемых данных. Трехмерными называются формулы, в которых используются ссылки на несколько листов.

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

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

Например, чтобы объединить данные в ячейках B4 начиная с листа Экзамены 1 по лист Экзамены 3, используется формула:

=СРЗНАЧ('Экзамены 1:Экзамены 3'!B4).

Если данные, которые требуется объединить, находятся в разных ячейках на разных листах, будет использоваться формула, подобная формуле:

=СРЗНАЧ('Экзамены 1'!B5;'Экзамены 2'!B4;'Экзамены 3'!C5;).

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

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

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

2. Командой Консолидация меню Данные открыть диалоговое окно Консолидация (см. рис. 5.1).

Рис. 5.1. Диалоговое окно консолидации листов

3. В открывшемся диалоговом окне выбрать итоговую функцию консолидации данных.

4. В поле Ссылка ввести все диапазоны консолидации по очереди, нажимая после указания каждого диапазона клавишу Добавить.

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

[Имя_файла]Имя_листа!Ссылка.

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

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

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

6. Все поля в группе Использовать в качестве имен оставить пустыми.

Дата добавления: 2014-11-29