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

Рис.6. Диапазон назначения для примера 8

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

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

='[Филиал1.xls]Лист1'!$А$1:$С$6

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

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

='[Филиал2.xls]Лист1'!$А$1:$С$6

6. В списке Функция выбрать тип консолидации. Для рассматриваемого примера – функция Сумма.

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

Рис.7.Окно Консолидация для примера 8

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

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

Результат представлен на рис.8.

Рис.8.Результат консолидации данных примера 8

1.1.4 Консолидация данных с использованием ссылок

Пример 9. Подготовить отчет по результатам деятельности филиалов предприятия за 5 лет с 1995 года включительно. Данные за каждый год представлены в отдельной книге. Структура годовых отчетов однотипна. В качестве примера на рис.11 приведен отчет работы предприятия за 1995 год.

Для консолидации данных по ссылке необходимо:

1.Скопировать или задать надписи для данных консолидации (рис.12).

2.Указать ячейку, в которую следует поместить данные консолидации. Выделить ячейку В2 на Лист1 книги "Отчет за 5 лет".

Рис.11. Исходные данные для примера 9

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

='[Год1995.xls]Лист1'!$B$2:$D$2

4. Повторить шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты консолидации данных.

Рис.12.Результат консолидации данных примера 9

1.1.5 Ручная консолидация рабочих листов

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

Пример 10. Дополнить отчет, полученный в примере 9 данными за 2000 год. Структура годового отчета не изменилась.

Для решения задачи:

1) Выделите диапазон с данными за 2000 год и скопируйте его с помощью команды Правка, Копировать.

2) Выделите диапазон назначения. Для рассматриваемого примера это диапазон ячеек G2-G5, расположенный на Лист1 книги "Отчет за 5 лет".