Информационные технологии

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

Связи между файлами и консолидация данных в Microsoft Excel.

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

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

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

2.  Создайте таблицу Отчет за 1 квартал по образцу (рис. 1).  Со­храните файл под именем Ф.И.О. I квартал.

Ячейка Прибыль рассчитывается по формуле: =Доходы – Расходы.  

3. Создайте таблицу Отчет за 2 квартал по образцу (рис. 1).  Со­храните файл под именем Ф.И.О. II квартал.

4. Создайте таблицу Отчет за полугодие по образцу по образцу (рис. 1). Сохраните файл под именем Ф.И.О. Полугодие.

Рис. 1. Исходные данные для Задания 1.

5. Рассчитайте полугодовые итоги, связав формулами файлы I квартал и 2 квартал.

Для связи файлов вы­полните следующие действия:

ü    откройте все три файла, расположите окна файлов так, чтобы они не перекрывали друг друга;

ü    в файле Полугодие рассчитайте ячейку Доходы по формуле:

= Доход за 1 квартал + Доход за 2 квартал

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

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

ü    аналогично рассчитайте полугодовые значения Расходы и При­быль.

6. Сохраните текущие резуль­таты расчетов. Резуль­таты работы представлены на рис. 2.

Рис. 2. Результаты расчетов.

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

Например: Содержимое ячейки Прибыль после закрытия файлов I полугодие и II полугодие примет вид: ='C:\Documents and Settings\user\[I квартал.xls]Лист1'!$B$3+'C:\Documents and Settings\user\[II квартал.xls]Лист1'!$B$3

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

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

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

3. Откройте файл Полугодие.  Одновременно с открытием файла появится окно с предложением обновить связи (рис. 3).  Для об­новления связей нажмите кнопку Обновить.  

Проследите, как изменились величина Доходы (должна увеличиться на 200 р.).

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

4.  Сохраните файл Полугодие и закройте его.

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

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

6. Откройте файл Полугодие.  Одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопку Не обновлять.  В меню Подключения выберите ко­манду Изменить связи (рис. 4).

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

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

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

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

Краткая справка:  Существует удобный инструмент для подведения итогов по таблицам сходной структуры, расположенных на разных листах или разных рабочих книгах — консолидация данных.

Одна и та же операция (суммирова­ние, вычисление среднего и др.) выполняется по всем ячейкам нескольких прямоугольных таблиц, а все формулы программа строит автоматически.

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

Установите курсор в ячейку ВЗ.

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

В строке Ссылка выделите в файле I квартал диапа­зон ячеек ВЗ:В5 и нажмите кнопку Добавить.

Затем выделите в фай­ле II квартал диапазон ячеек ВЗ:В5 и нажмите кнопку Добавитъ (рис. 5).

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

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

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

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

1. Со­здайте новую электронную книгу.

Наберите отчет по отделам за третий квартал по образцу рис. 6.

Рис. 6. Исходные данные для Задания 4.

Произведите расчеты и со­храните файл с именем Ф.И.О. III квартал.

2. Создайте новую электронную книгу.

Наберите отчет по отде­лам за четвертый квартал по образцу рис.7.

Рис. 7. Исходные данные для Задания 4.

Произведите рас­четы и сохраните файл с именем Ф.И.О. IV квартал.

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

Установите курсор на ячейку A3 и проведите консолидацию за третий и чет­вертый кварталы по заголовкам таблиц. Для этого:

ü     выполните команду Данные - Консолидация;

ü     в появившемся окне сделайте ссылки на диапазон ячеек АЗ:Е6 файла III  квартал и A3:D6 файла IV квартал (рис. 8);

ü     обратите вни­мание, что интервал ячеек включает имена столбцов и строк таб­лицы.

Рис. 8. Консолидация неоднородных таблиц.

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

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

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

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

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

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

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

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

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