ПР_Составление годовых отчётов. Составление консолидированных отчётов

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

Тема: Составление годовых отчётов. Составление консолидированных отчётов.

Цель: - закрепить основные навыки при работе с электронными таблицами, научить созданию консолидированных отчётов.

Вид работы: групповой

Время выполнения: 2 часа

Задания к практической работе

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

2.       Запустите программу MS Excel.

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

3.       На листе книге создайте диапазон:

4.       Закройте книгу, сохранив её в созданной Вами на рабочем столе папке, и присвойте ей название Филиал1.

5.       Откройте новую книгу и создайте в ней диапазон:

6.       Закройте книгу, сохранив её в созданной Вами на рабочем столе папке, и присвойте ей название Филиал2.

▲ Обратите внимание на то, что заголовки столбцов совпадают, заголовки строк нет.

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

▲ Если необходимо, чтобы поля были расположены в определенном порядке, следует включить в диапазон заголовки полей или строк. Заголовки должны быть написаны в точности так, как и на исходных листах книг Филиала1 и Филиала2. Если заголовки не вводить, то Excel создаст их автоматически.

 Выделите диапазон назначения (созданный Вами «чистый» диапазон)  Данные  Консолидация В поле ссылка укажите на исходный диапазон, введя [Филиал1.xls]Лист1!$А$1:$С$6 (диапазон первого филиала)

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

 Нажмите на кнопку Добавить (ссылка будет занесена в Список диапазонов)  Измените диапазон в поле ссылка на [Филиал2.xls]Лист1!$А$1:$С$6 (диапазон данных второго филиала)  Нажмите на кнопку Добавить В списке Функция выберите тип консолидации (для рассматриваемого примера – функция Сумма) Установите флажки в группе Использовать в качестве имен

▲ Можно установить как один флажок, так и оба Подписи верхней строки, Значения левого столбца.

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

 ОК.

8.       Результатом консолидации должно послужить следующее:

Контрольные задания

1.       Проработать последовательность операций по осуществлению консолидации данных.

2.       Фирма «Mechanics»закупила для своих подразделений мониторы и принтеры. Общие результаты покупки представлены в двух таблицах:

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

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