Упражнение 1. Консолидация данных

ЛАБОРАТОРНАЯ РАБОТА №4

ТЕХНОЛОГИЯ КОНСОЛИДАЦИИ ТАБЛИЧНЫХ ДАННЫХ.

СВОДНЫЕ ТАБЛИЦЫ

Цель и содержание работы: знакомство с механизмом консолидации данных; научиться создавать и обрабатывать сводные таблицы.

Теоретическое обоснование

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

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

Существуют следующие варианты консолидации данных:

- с помощью формул, где используются ссылки;

- по расположению данных для одинаково организованных областей-источников;

- по категориям для различающихся по своей структуре области данных;

- консолидация внешних данных.

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

- все области на одном листе - в ссылках указывается адрес блока ячеек, например D1:C8;

- области на разных листах - в ссылках указывается название листа, диапазон, например лист1!D1:лист2!С8;

- области в разных книгах, на разных листах — в ссылках указывается название книги, название листа, диапазон, например [книга1]лист1!D1: [книга2]лист2!С8.

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

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

Методика и порядок выполнения работы:

Упражнение 1. Консолидация данных

1. Создать три таблицы, содержащие сведения о поставляемых товарах, по образцу, приведенному на рис.1. Для каждого месяца первого квартала на отдельном листе книги Имя_9_1 создается собственная таблица с названием Поставки товаров в месяце, где в месяце - в январе, в феврале, в марте. При создании таблиц пользоваться режимом группового заполнения листов или копирования данных.

2. Переменная часть таблиц (столбцы Объем и Дата) должна соответствовать данным, приведенным на рис.1. Переименовать листы, дав им соответствующие имена (Янв, Фев, Мар).

Рисунок 1 - Таблица сведений о поставляемых товарах

3. Вставить новый лист, дав ему имя Конс_данные. Скопировать в него заголовок таблицы и откорректировать его соответствующим образом (рис. 2). Установить указатель активной ячейки в первую свободную ячейку А3.

Рисунок 2 - Диалоговое окно «Консолидация»

4. Вызвать диалоговое окно Консолидация посредством меню Данные-Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформировать их полный список, состоящий из трех записей, как представлено на рис. 2.

5. В поле Функция оставить функцию вычисления суммы и указать, что в качестве имен (названий строк) будут выбираться данные из первого столбца (А) консолидируемой области. Указать на необходимость создания динамической связи с исходными данными.

6. Выполнить консолидацию. Сравнить полученные результаты с приведенными на рис. 3.

Рисунок 3 - Результаты выполненной консолидации данных

7. Просмотреть созданную структуру, последовательно показывая или скрывая уровни этой структуры. Открыть второй (внутренний) уровень для поставщиков из С.Петербурга и Череповца. Сравнить полученный результат с представленным на рис. 4.

8. Пользуясь меню Сервис ► Зависимости, проследить влияющие ячейки для ячеек С7, С10, С38. Убедиться в правильности полученных результатов.

9. Меняя данные в ячейках листов Янв, Фев, Мар, проследить за автоматическим пересчетом общей итоговой суммы (ячейка С38) и частичных сумм в ячейках С10, С26 и т.д.

10. Сохранить созданную книгу с четырьмя листами под именем Имя_9_1.

Рисунок 4 - Результат созданной             Рисунок 5 - Образец таблицы

структуры                                                                                

Выполнить консолидацию данных из разных книг.

1. Открыть новую книгу и создать в ней одну таблицу, имеющую аналогичную предыдущим структуру и содержащую данные за второй квартал. Образец такой таблицы на рис. 5. Назвать лист с таблицей 2кварт. Сохранить созданную книгу под именем Имя_9_2.

2. Свернуть окно рабочей книги.

3. На новом листе книги Имя_9_1 выполнить консолидацию четырех диапазонов ячеек - трех из листов Янв, Фев, Мар книги Имя_7_1, а четвертого из соответствующего диапазона книги Имя_9_2 листа 2кварт. Обратить внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверить результат и сравнить его с тем, что представлен на рис. 6.

Рисунок 6 - Результат выполнения консолидации