Лабораторная работа №
Тема. Анализ и обобщение данных в электронных таблицах Excel
Составление консолидированных отчетов.
Цель: 1) освоение операции построения итоговых таблиц (консолидации данных);
2) научиться строить сводные таблицы.
Форма отчета: 1) Запись в тетради темы работы;
2) краткий конспект;
3) сохраненный файл.
Ход работы:
Откройте в своей папке файл «Итоги», созданный на предыдущем занятии.
На Листе 1 выделите таблицу и выполните команду Правка/ Копировать;
Создайте новую книгу, выбрав на панели Стандартной значок или выполнив команду Файл / Создать / чистая книга;
На Листе 1 выполните команду Правка / Вставить;
Переименуйте Лист 1 в Принтеры;
Выделите строки, содержащие данные о покупке сканеров и поместите их в Буфер обмена (Правка - Вырезать), перейдите на Лист 2 и разместите на нем информацию о сканерах (Правка – Вставить);
Скопируйте и перенесите на Лист 2 заголовок таблицы с Листа Принтеры. В случае необходимости произведите форматирование таблицы;
Сохраните новый файл в своей папке под именем «Консолидация данных».
ТЕОРИЯ.
Данные одной или нескольких исходных областей можно обработать и отобразить в общей итоговой таблице. Такая операция называется консолидацией данных. Источники данных могут находиться на том же листе, что и итоговая таблица, на других листах той же книги или в других книгах.
ПРАКТИКА.
Задание 1.
Составим консолидированный отчет по закупкам принтеров и сканеров (данным, размещенным на разных листах рабочей книги).
Методические указания.
Выберите Лист 3 и переименуйте его в «Отчет»;
Создайте шапку новой таблицы, как показано на рисунке.
Выделите ячейку В7 («Товар») и в меню Данные выберите команду Консолидация. Откроется диалоговое окно Консолидация;
Убедитесь, что в поле Функция находится функция Сумма.
Выделите данные, подлежащие консолидации. Для этого:
- щелкните в поле ССЫЛКА, а затем на ярлычке листа «Принтеры»;
- выделите блок ячеек, содержащих информацию о приобретении принтеров (с заголовком). В случае необходимости переместите диалоговое окно Консолидация в сторону;
- щелкните по кнопке Добавить. Содержимое поля ССЫЛКА перенесется в окно Список диапазонов.
- щелкните на ярлычке листа «Сканеры»;
- выделите блок ячеек, содержащих информацию о приобретении сканеров (с заголовком);
- щелкните по кнопке Добавить. Обе таблицы занесены в Список диапазонов;
- в группе Использовать метки активизируйте опции в Верхней строке и В левом столбце;
- сравните вид своего окна Консолидация с приведенным на рисунке и щелкните по кнопке ОК.
Выполните обрамление таблицы;
Пустые столбцы C и D можно скрыть (выделите их и выполните команду Формат/Столбцы/Скрыть);
Окончательный вариант отчета должен иметь вид:
Сохраните изменения в файле «Консолидация данных».
Самостоятельно.
- вставьте в файл «Консолидация данных» еще два листа Лист 4 и Лист 5;
- Лист 4 переименуйте в «Модемы» и создайте на этом листе таблицу закупок модемов по приведенному ниже образцу.
Для вычисления «Сумм» введите формулу.
- - - переименуйте Лист 5 рабочей книг в «Отчет l» и создайте на этом листе консолидированный отчет о закупке техники (принтеров, сканеров, модемов). Используйте функцию СУММА и данные листов «Сканеры», «Принтеры», «Модемы»;
- скройте лишние столбцы, выполните обрамление таблицы;
- сохраните изменения в файле «Консолидация данных»
- предъявите результаты работы преподавателю. позволяет автоматически вычислять промежуточные итоги.
Контрольные вопросы:
Что такое консолидация данных?
Где могут находиться источники данных для выполнения консолидации?
Какие средства Excel позволяют осуществлять консолидацию данных?