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

Модуль 2.15. Сводные таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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