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

Сводные таблицы – одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.

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

­ в списке или базе данных MS Excel - если данные берутся с одного рабочего листа;

­ во внешнем источнике данных - если данные берутся из внешней базы данных;

­ в нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;

­ в другой сводной таблице - если сводная таблица создается на основании данных другой сводной таблицы.

В этом же диалоговом окне указывается вид создаваемого отчета - сводная таблица или сводная диаграмма (со сводной таблицей).

Рисунок 7 – Первое диалоговое окно Мастер сводных таблиц

На втором шаге построения сводной таблицы в поле Диапазон окна Мастер сводных таблиц (рисунок 8) необходимо указать диапазон, на основании которого строится сводная таблица.

Рисунок 8 – Второе диалоговое окно Мастер сводных таблиц

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

Рисунок 9 – Третье диалоговое окно Мастер сводных таблиц

Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.

Поля БД, на основании которой строится сводная таблица, представлены в окне создания макета в виде кнопок с названием этих полей. Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.

В окне имеются четыре области:

­ Строка - для использования данных поля, расположенного в этой области, в качестве заголовка строки;

­ Столбец - для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;

­ Данные - для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы;

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

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

Допустимыми операциями подведения итогов являются: сумма, количество значений, среднее арифметическое, максимальное и минимальное значение, произведение, количество чисел, несмешанное и смешанное отклонение, несмешанная и смешанная дисперсия. В нашем примере в качестве допустимой операции выбрана сумма.

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

Кнопка Параметры… третьего диалогового окна Мастер сводных таблиц позволяет задать некоторые параметры, определяющие вид сводной таблицы. Например, в поле Имя можно задать название таблицы. По умолчанию сводные таблицы называются Сводная таблица 1, Сводная таблица 2 и т.д. Устанавливая флажок Общие итоги по столбцам или флажок Общие итоги по строкам можно подвести итоги по столбцам или строкам в сводной таблице. Установка флажка Автоформат позволяет пользоваться средствами автоформата MS Excel. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.

Задание 5. Создайте сводную диаграмму и таблицу для расчета прибыли по каждому товару на основе таблицы с первого листа рабочей книги Excel.

На первом шаге построения выбираем - сводная диаграмма (со сводной таблицей).

На втором шаге построения диапазон – A1:I18(всю таблицу).

На третьем шаге устанавливаем переключатель – новый лист. Нажимаем на кнопку Макет…. В рассматриваемом примере создается сводная таблица, состоящая из строк с Наименованием товара и столбцов с Названием города из которого этот товар доставлен. В область Данные перемещена кнопка Прибыль. Для вычисления выбрана операция Сумма (рисунок 11).

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

Нажмем кнопку ОК и выйдем из диалогового окна Макет и перейдем на третий шаг построения сводной таблицы.

После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная диаграмма и таблица, представленная на рисунке 12 и 13 соответственно.

Рисунок 12 – Сводная диаграмма подсчета прибыли по каждому товару

Рисунок 13 – Сводная таблица подсчета прибыли по каждому товару