СВЯЗЬ МЕЖДУ ФАЙЛАМИ И КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL

Практическая работа 13

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

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

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

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

   1.запустить редактор электронных таблиц Microsoft Excel и создать новую электронную таблицу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Выполните команду Данные/Консолидация (рис 13.4 ). В появившемся окне Консолидация выберите функцию- Сумма.

В строке Ссылка сначала выделите в файле 1 квартал диапазон ячеек В3/В5 и опять нажмите на кнопку Добавить( см. рис. 13.4 ). В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации.

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

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

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

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

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

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

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

3. Создайте новую электронную книгу. Наберите название таблицы Полугодовой отчёт о продажах по отделам. Установите курсор на ячейке А3 и проведите консолидацию за третий и четвертый кварталы по заголовкам таблиц. Для этого выполните команду Данные/Консолидация. В появившемся окне консолидации данных сделайте ссылки на диапазон ячеек А3/Е6 файла 3 квартал и А3/D6 файла 4 квартал (рис. 13.8). Обратите внимание, что интервал ячеек включает имена столбцов и строк таблицы.

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

*подписи верхней строки

*значения левого столбца

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

После нажатия кнопки ОК произойдёт консолидация (рис. 13.9).

Сохраните все файлы в папке вашей группы.

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