6. Практическая Консолидация данных. Лабораторная работа Консолидация данных
 Скачать 451.24 Kb.
|
|
Лабораторная работа Консолидация данных Цель занятия: Изучение технологии связей между файлами и консолидации данных в MS EXCEL. Для изучения технологии работы, смотреть видео урок http://www.youtube.com/watch?v=8ouvhYlsza4&feature=player_detailpage – консолидация данных в Excel Задание 1: Задать связи между файлами. Порядок работы: Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу. Создайте таблицу «Отчет о продажах 1 квартал» по образцу:  И проведите расчеты Прибыли: Прибыль = Доходы - Расходы. Сохраните файл под именем «1 квартал». Создайте таблицу «Отчет о продажах 2 квартал» по образцу в виде нового файла:  Для этого создайте новый документ и скопируйте таблицу отчета о продаже первый квартал, после чего исправьте заголовок и исходные данные. Обратите внимание, как изменился расчет Прибыли. Сохраните файл под именем «2 квартал». Создайте таблицу «Отчет о продажах за полугодие» по образцу в виде нового файла:  Для этого создайте новый документ и скопируйте таблицу отчета о продаже первый квартал, после чего исправьте заголовок таблицы и в колонке В удалите все значения исходных данных. Сохраните файл под именем «Полугодие». Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал».  Задание 2: Обновить связи между файлами. Порядок выполнения: Закройте файл «Полугодие» предыдущего задания. Измените значения «Доходы» в файлах первого и второго квартала, увеличив их на 100. Сохраните изменения и закройте файлы. Откройте файл «Полугодие» Одновременно с открытием файла появится окно с предложением обновить связи. Для обновления нажмите кнопку Да. Проследите, как изменились данные файла полугодие (величина «Доходы» должна увеличиться на 200 р. И принять значение 887,18 р.). В случае, когда вы отказываетесь от автоматического обновления, вам приходится выполнить это действие вручную. Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его. Вновь откройте файлы первого и второго кварталов и измените исходные данные Доходов, увеличив значения на 100 р. Сохраните изменения и закройте файлы. Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи в меню Правка выберите команду Связи, появится окно. В окне перечислены все файлы, данные из которых используются в активном файле «Полугодие». Задание 3: Консолидация данных для подведения итогов по таблицам данных сходной структуры. Краткая справка. В Excel существует удобный инструмент для подведения итогов по таблицам данных сходной структуры, расположенных на разных листах или разных рабочих книгах, - Консолидация данных. При этом одна и та же операция (суммирование, вычисление среднего и др.) выполняется по всем ячейкам нескольких прямоугольных таблиц, и все формулы EXCEL строит автоматически. Порядок работы: Откройте все три файла задания 2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку В3. Выполните команду Данные/Консолидация. В появившемся окне Консолидация выберите функцию – «Сумма». В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек В3:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек В3:В5 и опять нажмите кнопку Добавить.  В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. Далее нажмите кнопку ОК, произойдет консолидированное суммирование данных за первый и второй кварталы. Вид таблицы после консолидации приведен на рисунке:  Задание 4: Консолидация данных для подведения итогов по таблицам неоднородной структуры. Порядок работы: Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу:  Произведите расчеты и сохраните файл с именем 3 квартал. Создайте новую электронную книгу. Наберите отчет по отделам за четвертый квартал по образцу:  Произведите расчеты и сохраните файл с именем «4 квартал». Создайте новую электронную книгу. Наберите название таблицы «Полугодовой отчет о продажах по отделам». Установите курсор на ячейку А3 и проведите консолидацию за третий и четвертый кварталы по заготовкам таблиц. Для этого выполните команду Данные/Консолидация. В появившемся окне консолидации данных сделайте ссылки на диапазон ячеек А3:Е6 файла «3 квартал» и A3:D6 файла «4 квартал». Обратите внимание, что интервал ячеек включает имена столбцов и строк таблицы. В окне Консолидация активируйте опции (поставьте галочку): Подписи верхней строки; Значения левого столбца; Создавать связи с исходными данными (результаты будут не константами, а формулами). После нажатия кнопки ОК произойдет консолидации. Сохраните все файлы в папке вашей группы. Обратите внимание, что все данные корректно сгруппированы по их заголовкам (по отделам). В левой части экрана появятся так называемые кнопки управления контуром (иерархической структурой). С их помощью можно скрывать или показывать исходные данные.  Самостоятельная работа Создайте три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.1. Для каждого месяца первого квартала на отдельном листе книги Имя_10_1 создается собственная таблица с названием "Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март.  2. Создайте таблицы "Отгрузка (Январь)", "Отгрузка (Февраль)" и "Отгрузка (Март)"по образцу, приведенному на рис.2, пользуясь режимом группового заполнения, и дайте листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.  Создайте новую таблицу "Суммарный доход за три месяца", в которой будут сведены итоговые значения выручки за все кварталы за счет организации "трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.4. В создаваемой таблице запишите две формулы для получения одного и того же значения, но в одной из них запишите формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую "объемную" формулу. Примеры записи таких формул приведены на рис.4 непосредственно под ячейками В4, В7 и выделены курсивом.  Рис.4 Задание 2 1. Создайте три таблицы, содержащие сведения о поставляемых товарах, по образцу, приведенному на рис.11.1. Для каждого месяца первого квартала на отдельном листе книги Имя_11_1 создается собственная таблица с названием "Поставки товаров в месяце", где месяц - январь, февраль, март. При создании таблиц пользуйтесь режимом "группового заполнения листов" или копирования данных. 1.1. Переменная часть таблиц (столбцы "Объем" и "Дата") должна соответствовать данным, приведенным на рис.11.1. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).  Рис.11 2. Вставьте новый лист, дав ему имя "Конс_данные". Скопируйте в него заголовок таблицы и откорректируйте его соответствующим образом (рис.11.3). Установите курсор в первую свободную ячейку (А3).  Рис.11.2 2.1. Активизируйте диалоговое окно Консолидация с помощью команд Данные – Работа с данными - Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформируйте их полный список, состоящий из трех записей, как представлено на рис.11.2. 2.2. В поле Функция оставьте функцию вычисления суммы и укажите, что в качестве имен (названий строк) будут выбираться данные из левого столбца (А) консолидируемой области. Укажите на необходимость создания динамической связи с исходными данными.  Рис.11.3 2.3. Выполните консолидацию. Сравните полученные результаты с приведенными на рис.11.3. |