Лабораторная работа №7. Построение сводных таблиц

Цель: отработка навыков построения и редактирования сводных таблиц.

7.1 Создание сводной таблицы

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

Для создания сводной таблицы необходимо выделить ячейку базы данных, перейти во вкладку Вставка и выполнить команду Сводная таблца (рис. 7.1). Дальнейшее построение сводной таблицы заключается в перетаскивании заголовков полей базы данных в одну их четырех областей макета. Рассмотрим их назначение.

Названия строк (столбцов). Значения полей, помещенных в эту область, используются в качестве заголовков строк (столбцов) в сводной таблице (рис. 7.2).

Значения. Значения полей, помещенных в эту область, используются для заполнения ячеек сводной таблицы итоговыми данными, например, сумма, количество, среднее и т.д. (рис. 7.2).

Рисунок 7.1 – Макет для построения сводной таблицы

Фильтр отчета. Значения полей, помещенных в эту область, используются для построения раскрывающегося списка. В области данных будут отображены итоговые данные, для выбранного значения из списка (рис. 7.2).

Объект, помещенный в поле Фильтр отчета

Объект, помещенный в поле Названия строк

Объект, помещенный в поле Значения

Рисунок 7.2 – Элементы сводной таблицы

7.2 Практическая часть

Задание 1. На основании базы данных “Менеджеры” построить сводную таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по месяцам в разрезе регионов:

Таблица 7.1 – База данных “Менеджеры”

Менеджер	Месяц	Продукция	Доход	Расход	Прибыль	Регион
Иванов	январь	мясо	100	50		Страны СНГ
Иванов	февраль	мясо	100	50		Россия
Иванов	февраль	мясо	100	50		Россия
Иванов	февраль	рыба	100	50		Россия
Иванов	март	молоко	200	20		Россия
Иванов	апрель	мясо	100	50		Россия
Иванов	апрель	мясо	100	50		Россия
Петров	январь	мясо	100	50		Страны СНГ
Петров	февраль	мясо	100	50		Страны СНГ
Петров	февраль	мясо	100	50		Страны СНГ
Петров	март	молоко	300	30		Страны СНГ
Петров	апрель	мясо	100	50		Страны СНГ
Петров	апрель	мясо	100	50		Страны СНГ
Сидоров	январь	рыба	100	50		Россия
Сидоров	март	молоко	150	100		Страны СНГ
Сидоров	май	рыба	100	50		Страны СНГ

Указания.

1. Рассчитайте значение поля «Прибыль», записав соответствующую формулу.

2. Сделайте текущей любую ячейку построенного списка.

3. Выполните команду вкладка Вставка/Сводная таблица.

4. Перетащите поля «Продукция» и «Менеджер» в область «Заголовки строк». При этом важен порядок перетаскивания – поле «Менеджер» будет вложенным по отношению к полю «Продукция». Затем в область «Заголовки столбцов» перетащите поле «Месяц» и в область «Фильтр отчета» – поле «Регион». В область “Значения” перетащите кнопку «Прибыль».

5. Укажите место размещения сводной таблицы на новый лист.

Построенная сводная таблица будет иметь следующий вид:

Рисунок 7.3 – Сводная таблица, показывающая объем прибыли, полученной от продажи разных видов продукции разными исполнителями по месяцам в разрезе регионов

Задание 2. На основании базовой таблицы построить сводную таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по кварталам в разрезе регионов;

Указания.

1. Скопируйте сводную таблицу задания 1 на другой лист или повторите процесс ее построения. Можно также создать копию листа со сводной таблицей.

2. Отметьте диапазон C4:E15.

3. Во вкладке Параметры выполните команду Группировать. В поле столбца появиться новое поле «Месяц 2» и в сводную таблицу добавится строка, в которой для выделенных трех столбцов присвоится название «Группа 1».

4. Выполните аналогичные действия для столбцов сводной таблицы за апрель и май месяцы. Для этих столбцов должно появиться название «Группа 2».

5. Удалите поле месяц. Для этого вызовите контекстное меню или перетащите его из области сводной таблицы.

6. Исправьте название «Месяц 2» на «Квартал», «Группа 1» - на «Первый», «Группа 2» - на «Второй». Полученная таблица должна иметь следующий вид:

Рисунок 7.4 – Сводная таблица с группировкой

Задание 3. Скопируйте первую сводную таблицу на новый лист. Последовательно удаляя поля “Менеджер”, “Месяц” и “Продукция” получите новые сводные таблицы.

Задание 4. На основании таблицы задания 1:

• построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по регионам;

• построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции по регионам;

• построить таблицу, показывающую средний объем прибыли полученной от продажи разных видов продукции;

• построить таблицу, показывающую объем прибыли по регионам;

• построить диаграмму изменения суммарной прибыли по регионам по месяцам (январь, февраль, март, апрель, май).