Материал: Задание11
Задание № 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.Меняя данные в ячейках листов "Янв", "Фев", "Мар", проследить за автоматическим пересчетом общей итоговой суммы (ячейка С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.
Рис.П.11.7
3.4.Закрыть книгу Имя_11_2. На новом листе книги Имя_11_1 повторно выполнить консолидацию, включив в итог данные соответствующей области из закрытой книги. Обратить внимание на структуру ссылки при задании области консолидации из закрытой книги. 3.5.Выполнить консолидацию данных из четырех таблиц (аналогично предыдущему пункту), но задав в качестве обработки данных вычисление среднего значения консолидируемых данных. Сравнить полученный результат с представленным на рис.П.11.7.
4.Добавить в книгу еще один лист, именовав его как "Конс_данные2", и выполнить на этом листе консолидацию данных, расположенных в таблицах листов "Янв", "Фев", "Мар" и "Апр" (структура таблицы листа "Апр" приведена на рис.П.11.8), обратив внимание на задание консолидируемой области для листа "Апр". 4.1.Проверить правильность структуры таблицы на листе "Конс_данные2", сравнив ее с представленной на рис.П.11.9.
Рис.П.11.8
5.Задание для самостоятельного выполнения (выполняется факультативно). 5.1.Решить рассмотренную в п. 4 задачу, выполнив консолидацию с определением максимального и минимального значений консолидируемых данных. 5.2.Выполнить консолидацию для таблиц листов "Янв", "Фев", "Мар" и "Апр", приняв в качестве исходных данных для консолидации диапазоны ячеек, включающие столбец "Дата" ($А$3:$С$11). Проанализировать полученный результат.
6.Предъявить результаты преподавателю.