Практическая работа № 6.

Тема: СВЯЗИ МЕЖДУ ФАЙЛАМИ И КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL

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

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

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

• Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу.

• Создайте таблицу «Отчет о продажах 1 квартал» по образцу рис. 1. Введите исходные данные (Доходы и Расходы):

Доходы = 234,58 р.;

Расходы = 75,33 р.

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

• Создайте таблицу «Отчет о продажах 2 квартал» по образцу рис. 1 в виде нового файла. Для этого создайте новый документ (Файл/Создать) и скопируйте таблицу отчета о продаже за пер­вый квартал, после чего исправьте заголовок таблицы и измените исходные данные^-

Доходы = 452,6 р.;

Расходы = 185,8 р.

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

• Создайте таблицу «Отчет о продажах за полугодие» по образу рис. 1 в виде нового файла. Для этого создайте новый документ (Файл/Создать) и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и в колон­ке В удалите все

значения исходных данных и результаты расчетов. Сохраните файл под именем «Полугодие».

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

Краткая справка. Для связи формулами файлов Excel вы­полните действия:

3.2.1. откройте эти файлы (все три файла);

3.2.2. начните ввод формулы в файле-клиенте (в файле «Полугодие» введите формулу для расчета «Доход за полугодие»).

Формула для расчета:

Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.

Чтобы вставить в формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячей­кам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.

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

В ячейке В3 файла «Полугодие» формула для расчета полугодо­вого дохода имеет следующий вид:

= '[1 квартал-xls] Лист1'!$В$3 + '[2 квартал. xls]Лист1'!$В$3.

Аналогично рассчитайте полугодовые значения Расходов и При­были, используя данные файлов «1 квартал» и «2 квартал». Резуль­таты работы представлены на рис. 1. Сохраните текущие резуль­таты расчетов.

Примечание. Если файл-источник данных закрыт, в форму­ле, которая на него ссылается, будет указан весь путь для этого файла.

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

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

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

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

Доходы 1 квартала = 334,58 р.;

Доходы 2 квартала = 552,6 р.

Сохраните изменения и закройте файлы.

Рис. 2. Окно предложения обновления связи

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

на 200 р. и принять значение 887,18 р.).

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

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

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

Доходы 1 квартала = 434,58 р.;

Доходы 2 квартала = 652,6 р.

Сохраните изменения и закройте файлы.

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

Расположите его так, чтобы были видны данные файла «По­лугодие», выберите файл «1 квартал» и нажмите кнопку Обно­вить и проследите, как изменились данные файла «Полугодие». Аналогично выберите файл «2 квартал» и нажмите кнопку Обно­вить. Проследите, как вновь изменились данные файла «Полу­годие».

Примечание. При изменении данных в нескольких исход­ных файлах обновление связи производится для каждого файла.

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

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

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

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

3.2.3. Выполните команду Данные/Консолидация (рис. 4). В появив­шемся окне Консолидация выберите функцию — «Сумма».

Рис. 4. Консолидация данных

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

Рис. 5. Таблица «Полугодие» после консолидированного суммирования

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

Вид таблиц после консолидации данных приведен на рис. 5.

Дополнительные задания

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

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

- Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу (рис. 6). Произведите расчеты и со­храните файл с именем «3 квартал».

- Создайте новую электронную книгу. Наберите отчет по отде­лам за четвертый квартал по образцу (рис. 7). Произведите рас­четы и сохраните файл с именем «4 квартал».

Рис. 6. Исходные данные для третьего квартала Задания 4

- Создайте новую электронную книгу. Наберите название таб­лицы «Полугодовой отчет о продажах по отделам».

Рис. 7. Исходные данные для четвертого квартала Задания 4

Установите курсор на ячейку A3 и проведите консолидацию за третий и чет­вертый кварталы по заголовкам таблиц. Для этого выполните команду Данные/Консолидация. В появившемся окне консолида­ции данных сделайте ссылки на диапазон ячеек А3:Е6 файла «3 квартал» и A3:D6 файла «4 квартал» (рис. 8). Обратите вни­мание, что интервал ячеек включает имена столбцов и строк таб­лицы.

В окне Консолидация активизируйте опции (поставьте галочку):

3.4.1. подписи верхней строки;

3.4.2. значения левого столбца;

3.4.3. создавать связи с исходными данными (результаты будут не константами, а формулами).

После нажатия кнопки ОК произойдет консолидация (рис. 9). Сохраните все файлы в папке вашей группы.

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

Рис. 9. Результаты консолидации неоднородных таблиц

(иерархической структурой). С их помощью можно скрывать или показывать исходные данные.