Лабораторная работа консолидация данных

Цель занятия: Изучение технологии связей между файлами и консолидации данных в MS EXCEL.

Для изучения технологии работы, смотреть видео урок http://www.youtube.com/watch?v=8ouvhYlsza4&feature=player_detailpage – консолидация данных в Excel

Задание 1:Задать связи между файлами.

Порядок работы:

1. Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу.

1. Создайте таблицу «Отчет о продажах 1 квартал» по образцу:

И проведите расчеты Прибыли: Прибыль = Доходы - Расходы. Сохраните файл под именем «1 квартал».

1. Создайте таблицу «Отчет о продажах 2 квартал» по образцу в виде нового файла:

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

Обратите внимание, как изменился расчет Прибыли. Сохраните файл под именем «2 квартал».

1. Создайте таблицу «Отчет о продажах за полугодие» по образцу в виде нового файла:

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

1. Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал».

Задание 2:Обновить связи между файлами.

Порядок выполнения:

1. Закройте файл «Полугодие» предыдущего задания.

1. Измените значения «Доходы» в файлах первого и второго квартала, увеличив их на 100. Сохраните изменения и закройте файлы.

1. Откройте файл «Полугодие» Одновременно с открытием файла появится окно с предложением обновить связи. Для обновления нажмите кнопку Да. Проследите, как изменились данные файла полугодие (величина «Доходы» должна увеличиться на 200 р. И принять значение 887,18 р.).

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

1. Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его.

1. Вновь откройте файлы первого и второго кварталов и измените исходные данные Доходов, увеличив значения на 100 р. Сохраните изменения и закройте файлы.

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

Задание 3:Консолидация данных для подведения итогов по таблицам данных сходной структуры.

Краткая справка. В Excel существует удобный инструмент для подведения итогов по таблицам данных сходной структуры, расположенных на разных листах или разных рабочих книгах, - Консолидация данных. При этом одна и та же операция (суммирование, вычисление среднего и др.) выполняется по всем ячейкам нескольких прямоугольных таблиц, и все формулы EXCEL строит автоматически.

Порядок работы:

1. Откройте все три файла задания 2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку В3.

1. Выполните команду Данные/Консолидация. В появившемся окне Консолидация выберите функцию – «Сумма».

В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек В3:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек В3:В5 и опять нажмите кнопку Добавить.

В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. Далее нажмите кнопку ОК, произойдет консолидированное суммирование данных за первый и второй кварталы.

Вид таблицы после консолидации приведен на рисунке:

Задание 4: Консолидация данных для подведения итогов по таблицам неоднородной структуры.

Порядок работы:

1. Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу:

Произведите расчеты и сохраните файл с именем 3 квартал.

1. Создайте новую электронную книгу. Наберите отчет по отделам за четвертый квартал по образцу:

Произведите расчеты и сохраните файл с именем «4 квартал».

1. Создайте новую электронную книгу. Наберите название таблицы «Полугодовой отчет о продажах по отделам». Установите курсор на ячейку А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.