Упражнение 14 - Сводные таблицы

Инструмент Сводная таблица из меню Данные является чрезвычайно мощным и удобным средством анализа и трансформации данных. С его помощью можно сделать практически любой «разрез» таблицы, получить итоги по любым данным. Сводная таблица во многом объединяет возможности других обобщающих инструментов, таких как Итоги, Фильтр, Консолидация.

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

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

Для формирования сводных таблиц Excel предлагает использовать команду Сводная таблица, расположенную на вкладке Вставка в разделе команд Таблицы, позволяющую в наглядной форме сформировать нужную сводку, которую в дальнейшем можно неограниченно трансформировать. Особенность этой команды заключается в том, что она удовлетворительно работает только с однородными и хорошо структурированными данными. Поэтому преобразуйте таблицу, расположенную на листе упр.12-а (см. рис. 12.1) к следующему виду (рис. 14.1).

Точка	Наименование	Количество	Цена	Сумма
Булочная № 1	Городской	50	14,00 р.	700,00 р.
Булочная № 1	Ржаной	75	13,00 р.	975,00 р.
Булочная № 1	Лаваш	20	20,00 р.	400,00 р.
Булочная № 2	Городской	120	14,00 р.	1680,00 р.
Булочная № 2	Ржаной	100	13,00 р.	1300,00 р.
Булочная № 2	Лаваш	20	20,00 р.	400,00 р.
Булочная № 2	Калач	10	8,00 р.	80,00 р.
Чайная "Золотой самовар"	Выпечка	100	7,50 р.	750,00 р.
Чайная "Золотой самовар"	Ржаной	5	13,00 р.	65,00 р.
Чайная "Золотой самовар"	Лаваш	50	20,00 р.	1000,00 р.

Рис. 14.1. Исходные данные

Для построения сводной таблицы выполните следующие действия:

1. Сделайте активной любую ячейку таблицы с исходными данными.

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

3. Нажмите клавишу ОК.

Рис.14.2. Диалоговое окно Создание сводной таблицы

4. В книге Упражнения появится новый лист, на котором необходимо сформировать сводную таблицу. Для этого переместите с помощью мыши следующие поля, используя диалоговое окно Список полей сводной таблицы (рис.14.3):

Рис.14.3. Диалоговое окно Список полей сводной таблицы

• Точка – в область Фильтр отчета;

• Наименование – в область Название строк;

• Цена – в область Названия столбцов;

• Сумма – в область Значения;

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

Рис. 14.4. Сводная таблица – вариант 1

5. Выбирая элемента из списка по полю Точка, просмотрите данные по каждой точке отдельно.

6. Воспользуйтесь кнопкой Мастер сводных таблиц на панели инструментов Сводные таблицы: вы сразу попадете на третий шаг. Поменяйте местами два поля Точка и Наименование (рис.14.5).

Рис.14.5. Сводная таблица – вариант 2

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

8. Внесите в исходную таблицу поправку: измените количество какого-нибудь товара, например, городской батон в Булочной № 2.

9. Выделите столбец данных с ценой на этот товар в сводной таблице и выполните команду Обновить данные в контекстном меню.

Лабораторная работа 9. Использование сводных таблиц

На рабочем листе Лаб_9 книги Лабораторные работы.xlsx создайте список, содержащий данные за два года о реализации продукции некоторой компанией, выпускающей мороженое. Для каждого из семи сортов мороженого (сливочное, шоколадное, ореховое, фисташковое, ванильное, кофейное, земляничное) в списке содержится информация о его реализации по регионам в каждом квартале 2209 и 2010го годов. Реализация фиксируется (рис. 14.6) как в натуральном (шт.), так и стоимостном выражении (руб.).

Год	Квартал	Сорт	Регион	Объем продаж (шт.)	Объем продаж (руб.)
2002	1	Сливочное	Восток	29000	159 500,00 р.
2002	1	Сливочное	Север	38000	209 000,00 р.
2002	1	Сливочное	Юг	33000	181 500,00 р.
2002	1	Сливочное	Запад	45000	247 500,00 р.
2002	1	Шоколадное	Восток	114000	855 000,00 р.
2002	1	Шоколадное	Север	129000	967 500,00 р.
2002	1	Шоколадное	Юг	88000	660 000,00 р.
…	…	…	…	…	…

Рис. 14.6. Исходные данные

Цена за единицу продукции каждого сорта определите самостоятельно:

• на листе с именем Мороженое создайте таблицу (рис.14.7) с наименованиями всех семи сортов мороженого и ценой за единицу;

• присвойте полученным значениям соответствующие имена;

• используйте эту таблицу при расчете Общего объема продаж (руб.).

Рис.14.7. Вспомогательная таблица

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

Создайте сводную таблицу (рис. 14.8) и поместите ее на отдельном листе с именем св_табл.

Рис. 14.8. Сводная таблица

Задание 2. Модификация сводной таблицы.

1. Сделайте копию сводной таблицы - св_табл(2) в этой же книге.

10. Переместите заголовки из области столбцов в область строк и наоборот (рис.14.9).

Рис.14.9. Модифицированная сводная таблица – вариант 1

11. Сделайте копию сводной таблицы св_табл(3) в этой же книге и переместите в область Фильтра поле Год (рис. 14.10).

Рис.14.10. Модифицированная сводная таблица – вариант 2

12. Сделайте копию сводной таблицы св_табл(4) в этой же книге и поместите в область Фильтра поле Год и поле Квартал (рис.14.11).

Рис.14.11. Модифицированная сводная таблица – вариант 3

13. Сделайте копию сводной таблицы св_табл(5) в этой же книге и добавьте в область Данные поле Объем продаж (шт.).

Задание 3. Работа с данными в сводных таблицах.

1. Отсортируйте данные в св_таб(3) по полю Сорт (по возрастанию) и Регион (по убыванию).

14. На отдельном листе создайте диаграмму продаж ванильного мороженого по регионам на основе листа св_таб.

15. Оси подпишите следующим образом: Объем продаж и Кварталы (2002-2003гг.).

16. Название диаграммы: Продажа ванильного мороженого по регионам.

5