ЛАБОРАТОРНАЯ РАБОТА № 7
Тема: Составление годовых отчётов. Составление консолидированных отчётов
Цель: закрепить основные навыки при работе с электронными таблицами, научить созданию консолидированных отчётов.
Время выполнения: два академических часа.
Задания:
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»закупила для своих подразделений мониторы и принтеры. Общие результаты покупки представлены в двух таблицах:
| Наименование товара | Тип | Модель | Цена | Количество | Общая стоимость |
| Монитор | 17” | Viewsonic E70 | |||
| Монитор | 17” | Viewsonic E71 | |||
| Монитор | 19” | Viewsonic E95 | |||
| Монитор | 19” | Samsung 900IFT |
| Наименование товара | Тип | Модель | Цена | Количество | Общая стоимость |
| Принтер | Лазерный | Epson EPL-5800L | |||
| Принтер | Лазерный | Epson EPL-N1600 | |||
| Принтер | Лазерный | HP LJ-1200 | |||
| Принтер | Лазерный | HP LJ-1220 | |||
| Принтер | Струйный | Epson Stilus C20SX | |||
| Принтер | Струйный | Epson Stilus C40UX | |||
| Принтер | Струйный | HP DJ-930S | |||
| Принтер | Струйный | HP DJ-959S |
Перенесите данные каждой таблицы на отдельный лист (значения в графе Общая стоимость определите по формуле).
На третьем листе этой же рабочей книги составьте отчёт о закупке техники, в котором должно быть указано общее количество и общая стоимость всех принтеров.
Представьте результаты выполнения заданий для проверки преподавателю
