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

В MS EXCEL

Задание 1.

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

Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Создайте таблицу «Отчет о продажах 1 квартал» по образцу. Введите исходные данные (Доходы и Расходы): Доходы = 234,58 р.; Расходы = 75,33 р. и проведите расчет Прибыли: Прибыль = Доходы – Расходы.  Сохраните файл под именем «1 квартал».

Создайте таблицу «Отчет о продажах 2 квартал» в виде нового файла. Для этого создайте новый документ  и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и измените исходные данные: Доходы = 452,6 руб.; Расходы = 125,8 руб.
Обратите внимание, как изменился расчет прибыли. Сохраните этот файл под именем «2 квартал».

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

Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал». Для связи файлов Excel формулами выполните действия: 1) откройте эти файлы (все три файла); начните в файле-клиенте ввод формулы (в файле «Полугодие» введите формулу для расчета «Доход за полугодие»). Формула для расчета:
Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.
Чтобы вставить в формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячейкам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.Полный адрес ячейки состоит из названия рабочей книги в квадратных скобках, имени листа, восклицательного знака и адреса ячейки на листе.В ячейке В3 файла «Полугодие» формула для расчета полугодового дохода имеет вид:

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

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

Рис. 7.1. Результат вычисления

Задание 2.

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

Закройте файл «Полугодие» предыдущего задания. Измените значение «Доходы» в файлах первого и второго квартала, увеличив значения на 100 руб.: Доходы 1 квартала = 334,58 р. Доходы 2 квартала = 552,6 р.
Сохраните изменения и закройте файлы.

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

Задание 3.

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

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

Откройте все три файла задания 2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку В3. Выполните команду Данные/Консолидация (рис. 7.2). В появившемся окне Консолидация выберите функцию – «Сумма».
В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек В3:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек В3:В5 и опять нажмите кнопку Добавить. В списке диапазонов будут находиться две области данных для консолидации: за первый и второй кварталы. Далее нажмите кнопку ОК, произойдет консолидированное суммирование данных за первый и второй кварталы.

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

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

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

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

Рис. 7.3. Исходные данные

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

Рис. 7.4. Исходные данные

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

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

Рис. 7.6. Итоговый вид таблиц

ПРАКТИЧЕСКАЯ РАБОТА №8