Электронные таблицы могут содержать большие группы данных, которые требуют некоторого обобщения и анализа. Для этих целей применяются сводные таблицы, которые позволяют обобщить данные и просмотреть срезы данных по некоторым категориям: по товарным группам, по наименованиям товаров, по поставщикам, по датам, по потребителям, по Ф.И.О., по производителям и т. п.

    Большие группы данных, обобщенные по некоторым критериям и преобразованные в удобный для просмотра и анализа табличный вид, называют сводной таблицей. Начальными данными для сводных таблиц могут быть как группы ячеек Excel, так и другие сводные таблицы и базы данных из других файлов. На основе сводных таблиц легко строятся диаграммы. При смене начальных данных, сводные таблицы и соответствующие диаграммы не обновляются. В самой сводной таблице нельзя менять (вводить) данные.

Задание 1. Подготовьте начальные данные для построения сводной таблицы, или загрузите готовый файл общие_сводные.xls. Создайте по образцу сводную таблицу объема продаж по товарам и регионам (используя мастер сводных таблиц):

Рисунок 5.20 – Данные для сводной таблицы

В таблице приведены данные об объемах продажи телевизоров разных производителей по регионам за три месяца 2012 года.

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

Методические указания.

1. Выберите вкладку Вставка – Сводная таблица…. Будет запущен мастер.

3. На второму шаге выделите диапазон ячеек A2:D29. Нажмите Далее>.

Внимание!!! При выделении диапазона, захватываем заголовок таблицы, а нижнюю строку с итогами не берем.

4. На третьем шаге выбираем поля, необходимые для сводной таблицы:

Внимание!!! Если необходимо выбрать операцию для вычисляемого поля, щелкните на нем дважды. Откроется диалоговое окно Вычисление поля сводной таблицы. Выберите нужную операцию (рисунок 5.21).

Рисунок 5.21 - Диалоговое окно Вычисление поля сводной таблицы.

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

4. На третьем шаге выбираем местоположение таблицы. На вокладке Анализ нажмите на кнопку Действия - Переместить.

Рисунок 5.22 – Окно размещения сводной таблицы

Готовая сводная таблица легко реорганизуется простым перетягиванием названий столбцов. Например, поля Период и Регион можно поменять местами и т.п.

Задание 2. Проанализируйте срезы данных по месяцам, раскрыв поле со списком Период. Рассмотрите основные кнопки: панели инструментов Сводная таблица.

Вместе со сводной таблицей на экран выводится Панель инструментов Сводная таблица. Рассмотрим ее основные кнопки:  

- Формат отчета позволяет добавить сводной таблице "презентабельный" для печати вид;

- Мастер диаграмм позволяет автоматически построить диаграмму по сводной таблице;

- Отобразить детали позволяет детализировать сводную таблицу по некоторому параметру. Н вкладке Анализ в группе меню Активное поле для такой команды есть кнопка Детслизация.

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

- Параметры поля позволяет установить обобщенную операцию по выбранному полю. Например, выделите на сводной таблице поле Сумма по полю Объем продаж и щелкните по кнопке. В окне, которое появилось, вместо операции Сумма задайте операцию Минимум (или другую). Нажмите ОК, проанализируйте результат, а потом отмените результат кнопкой.

Задание 3. Для полученной сводной таблицы постройте объемную диаграмму.

Методические указания.

1. Щелкните по сводной таблице для ее выделения. На панели инструментов Сводная таблица нажмите кнопку  Мастер диаграмм. Будет построена гистограмма с накоплением.

2. Измените тип гистограммы: еще раз нажмите кнопку Мастер диаграмм (можно щелкнуть правой кнопкой мыши по самой гистограмме и из списка, который появился, выбрать Тип диаграммы, но тогда гистограмма будет построена на отдельном листе). В окне, которое появилось, выберите вид - объемная гистограмма и нажмите Далее.

3. В следующем окне Мастер диаграмм нажмите Далее.

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

Рисунок 5.23 - Гистограмма анализа продажи товаров по сводной таблице

Задача 3. Выполните фильтрацию данных в таблице с помощью автофильтра.

Методические указания.

1. Выделите любую ячейку начальной таблицы и выполните команды: Данные – фильтр.

2. Из открывающегося списка строки «Период» уберите флажки со всех периодов, кроме «март».

В результате имеем таблицу, которая содержит данные продаж в марте.

 Для восстановления всех строк начальной таблицы необходимо выполнить команды: Фильтр – Выделить все – Ок.

Внимание! Строки таблицы, которые отобраны при фильтрации можно редактировать, форматировать, создавать на их основе диаграммы, выводить на печать.