Реферат: Консолидация данных в Excel

Пример 1.

Необходимо записать в ячейку В2 первого листа(Лист1) значение ячейки D4 следующего листа (Лист2).

Для этого в ячейку В2 введите формулу:

='Лист2'!$D$4

Внешняя ссылка –это ссылка на диапазоны ячеек, расположенных в других книгах.

Синтаксис внешней ссылки:

='Путь\[Имя_рабочей_книги]Имя_листа'!Адрес_диапазона

Пример 2.

Необходимо в ячейку В2 первого листа (Лист1) записать значение ячейки А1 из листа 2кв1996 файла КВАРТАЛ2.xls, находящегося на диске Е: в папке USERS.

Для этого в ячейку В2 следует ввести следующую формулу:

='E:\USERS\[КВАРТАЛ2.xls]2кв1996'!$A$1

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

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

Пример 3. Исходные области и область назначения находятся на одном листе. Например, включить диапазон Бюджет (диапазону ячеек предварительно присвоено имя Бюджет).

Для этого вводим ссылку

=Бюджет

Пример 4. Исходные области и область назначения находятся на разных листах. В этом случае удобно использовать имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Бюджет», находящийся в рабочей книге на листе «Бухгалтерия», необходимо ввести ссылку

=Бухгалтерия!Бюджет

Пример 5. Исходные области и область назначения находятся в разных книгах. Используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «1996», находящейся в этой же папке, введите:

='[1996.xls]Дальний Восток'!Продажи

Пример 6. Исходные области и область назначения находятся в разных книгах разных каталогах диска. Используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет» на диске С:, введите:

='[C:\Бюджет\Отдел продаж.xls]Февраль'!Оборот

Если диапазонам назначены уникальные, не присвоенные автоматически имена, то в ссылке можно не указывать имена листов. Например '[1996.xls]'!Продажи или '[C:\Бюджет\Отдел продаж.xls]'!Оборот в примерах 5 и 6.

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

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

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

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

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

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