Задание № 11. Консолидация данных.

Цель: Знакомство с механизмом консолидации данных.

Темы: Консолидация «по положению». Консолидация «по категориям». Консолидация со связью.

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

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

Рис.11.1

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

Рис.11.2

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

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

Рис.11.3

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

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

Рис.11.4 Рис.11.5

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

2.6. Раскройте структуру для первых трех консолидированных данных и просмотрите формулы в столбце С (Формулы – Зависимости формул – Влияющие ячейки – Показать формулы). Верните отображение результатов вычислений.

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

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

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

3.2. Сверните окно рабочей книги.

Рис.11.6

3.3. На новом листе книги Имя_11_1 выполните консолидацию четырех диапазонов ячеек - трех из листов "Янв", "Фев", "Мар" книги Имя_11_1, а четвертого из соответствующего диапазона книги Имя_11_2 листа "2кварт". Пользуйтесь кнопкой Обзор диалогового окна Консолидация. Обратите внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверьте результат и сравните его с тем, что представлен на рис.11.6.

3.4. Закройте книгу Имя_11_2. Обратите внимание на структуру ссылки при задании области консолидации из закрытой книги.

3.5. Выполните консолидацию данных из четырех таблиц (аналогично пункту 3.3), но задав в качестве обработки данных вычисление среднего значения консолидируемых данных. Сравните полученный результат с представленным на рис.11.7.

4. Добавьте в книгу еще один лист, именовав его как "Конс_данные2", и выполните на этом листе консолидацию данных, расположенных в таблицах листов "Янв", "Фев", "Мар" и "Апр" (структура таблицы листа "Апр" приведена на рис.11.8), обратив внимание на задание консолидируемой области для листа "Апр".

Рис.11.7

4.1. Проверьте правильность структуры таблицы на листе "Конс_данные2", сравнив ее с представленной на рис.11.9.

Рис.11.8 Рис.11.9

5. Предъявите результаты преподавателю.