Удаление и редактирование ссылок

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

Чтобы удалить ссылку из диапазонов консолидации, выделите ее в списке диапазонов и нажмите кнопку Удалить .

Чтобы отредактировать ссылку, выделите ее в диалоговом окне Консолидация в списке диапазонов. Она появится в поле Ссылка, где ее можно изменить. После внесения всех исправлений нажмите кнопку Добавить. Затем удалите старый вариант измененной ссылки.

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

Наличие связи между объектами. При наличии связи результаты будут обновляться при изменении данных, а в области назначения будет создана структура. Т.е. в диапазон консолидации между итоговых строк будет вставлена детальная информация, связанная внешними ссылками с исходными диапазонами. Причем, диапазон назначения будет структурирован. Верхним уровнем структуры будут итоговые данные, нижним — исходные (см. пример _8.).

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

По умолчанию используется функция Сумма.

Консолидация данных по физическому расположению

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

Пример 7.

Необходимо просуммировать данные об обороте товаров за первый и второй квартал 1996 года. Данные соответственно находятся на листе 1кв1996 ячейках D2:D5 файла Квартал1.xls и на листе 2кв1996 ячейках B3:B6 файла Квартал2.xls. В указанных диапазонах нет заголовков граф.

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

1. Выделить диапазон назначения, руководствуясь табл. 1.

Учитывайте только область данных. Текстовые заголовки в консолидации не участвуют. Для нашего примера это диапазон A2:A5 листа назначения (рис.3). Поскольку указан диапазон ячеек, то консолидируется только четыре значения по количеству ячеек в диапазоне (см.табл.1).

2. Выбрать команду Данные, Консолидация. Появится диалоговое окно Консолидация (рис. 1).

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

=[Квартал1.xls]1кв1996!$D$2:$D$5

Нажать кнопку Добавить, ссылка будет занесена в Список диапазонов (рис. 2).

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

=[Квартал2.xls]2кв1996!$В$3:$В$6

5. В списке Функция выбрать тип консолидации. По умолчанию используется функция сумма.

6. Сбросить оба флажка в группе Использовать в качестве имен, поскольку ячейки привязаны к своим позициям, а не к заголовкам.

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

8. Нажать кнопку ОК.

Результат консолидации представлен на рис.3.

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

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