Работа со сводными таблицами

Создание сводного отчёта

При создании сводного отчёта необходимо выполнить следующие требования к исходным данным в электронной таблице:

• Первой строкой в каждом столбце должен быть заголовок.

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

• В каждом столбце должен быть только один тип данных.

• Если в исходных данных содержатся промежуточные и общие итоги, перед созданием отчёта удалите их.

Пример создания сводного отчёта

Сначала необходимо заполнить исходные данные.

Исходными данными в этом примере служит список продаж по отдельным продавцам.

 Заголовки столбцов: «Страна», «Продавец», «Сумма заказа», «Дата заказа».

  Поля строк отображают данные по вертикали, по одной единице на строку.

  Поля столбцов отображают данные по горизонтали, по одной единице на столбец.

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

  Элементы данных, в которых отображаются и суммируются числовые данные.

Перетащим поля «Страна», «Продавец», «Сумма заказа» получим таблицу.

1. Изменение порядка сортировки

   • щёлкнем Сводная таблица на панели инструментов Сводные таблицы

   • выберем команду Сортировка и десятка лучших

1. Форматирование данных

   • щёлкнем любую ячейку в области данных

   • нажмём Параметры поля на панели инструментов Сводные таблицы

   • щёлкнем Формат и выберем денежный формат

1. Добавление другого поля

   • в области списка полей выберем поле «Дата заказа»

   • щёлкнем на кнопке Добавить в область строк

   • перетащим поле «Дата заказа» левее поля «Продавец»

Теперь оба поля Дата заказа и Продавец имеют в отчёте построчную ориентацию. Но результаты продаж отсортированы по дням с итогом за день.

1. Создание квартальной группы

   • переименуем поле Дата заказа в Квартал: для этого щёлкнем дважды поле Дата заказа

   • в появившемся окне введём новое имя Кв-л

   • правой кнопкой мыши щёлкнем поле с новым названием Кв-л, выберем Группа и структура

   • щёлкнем Группировать

   • в появившемся окне выберём Кварталы

Результатом будет новое отображение — сравнение поквартальных результатов продаж для каждого продавца.

1. Найдём количество продаж, которое было сделано каждым продавцом

Перетащим поле Сумма заказа в область данных ещё раз и ему автоматически будет присвоено имя Сумма по полю Сумма заказа 2.

В результате появится новый заголовок поля Данные.

Изменим способ вычисления с функции «Сумма» на функцию «Количество», для этого:

• щёлкнем в одной из ячеек с именем Сумма по полю Сумма заказа 2

• щёлкнем кнопку Параметры поля на панели инструментов Сводные таблицы

• выберем функцию Количество в списке Операция

• щёлкнем ОК.

1. Форматирование отчета сводной таблицы

Отформатируем отчет таким образом, что его будет проще читать. Для этого:

• поместим в область строк поле «Номер заказа»

• переименуем ячейку «Сумма по полю Сумма заказа» в «Заказ руб.»

• щёлкнем кнопку Формат отчета на панели инструментов Сводные таблицы

• выберём один из форматов сводной таблицы в диалоговом окне Автоформат.

• ОК.

1. Отображение числовых данных в виде процента от общего количества продаж

Чтобы изменить функцию Количество на Долю от общей суммы, нужно щёлкнуть в одной из ячеек поля Количество. После этого:

• щёлкнем кнопку Параметры поля на панели инструментов Сводные таблицы

• в списке Операция изменим функцию суммирования Количество на Сумма

• щёлкнем Дополнительно и в раскрывающемся списке Дополнительные вычисления выберем Доля от общей суммы.

1. Вычисление процента премии

Для того чтобы определить, составляет премия 12 процентов или 7, введём формулу в виде вычисляемого поля.

Для создания вычисляемого поля:

• щёлкнем команду Сводная таблица на панели инструментов Сводные таблицы

• выберем Формулы и затем щёлкнем Вычисляемое поле

• в диалоговом окне Вставка вычисляемого поля введём формулу в поле Формула, используя поля из списка Поля.

=ЕСЛИ(`Сумма заказа` 2000000;12%;7%)

• в поле Имя изменим имя на Процент премии и щёлкнем на кнопке Добавить, ОК.

Оставим в отчёте четыре поля, изображённых на рисунке, остальные скроем. Для этого:

• щёлкнем по названию поля правой кнопкой мыши

• и выберем Скрыть.

1. Вычисление размера премии

Вычислим размер премии для каждого продавца за квартал:

• щёлкнем Сводная таблица на панели инструментов Сводные таблицы

• выберем Формулы и затем щёлкнем команду Вычисляемое поле

• в диалоговом окне Вставка вычисляемого поля введём формулу, умножающую суммы заказов на процент премии:

• =`Сумма заказа`*`Процент премии`

1. Сложение данных из отчета в ячейке за пределами отчета

Посчитаем премию у Вороновой за все кварталы:

• щёлкнем в ячейке за пределами отчета

• введём =СУММ(E5:E7).

1. Обновление данных

• изменим данные в исходной таблице у Батурина

• перейдём к листу сводной таблицы.

• нажмём кнопку Обновить данные на панели инструментов Сводные таблицы.

Результат сводного отчёта по шести продавцам

Страна

Товар

Дата

Объём продаж

Австрия

Горячекатанный прокат

12.02.2008

10523

Австрия

Холоднокатанный прокат

13.05.2008

14523

Австрия

Слябы

14.02.2009

212890

Австрия

Холоднокатанный прокат

15.10.2008

313345

Австрия

Слябы

16.07.2009

42334

Италия

Слябы

17.01.2008

35587

Италия

Горячекатанный прокат

18.04.2009

12356

Италия

Слябы

19.12.2008

119234

Италия

Слябы

20.01.2009

556124

Италия

Горячекатанный прокат

21.09.2008

98567

Италия

Холоднокатанный прокат

22.06.2009

234543

Турция

Горячекатанный прокат

23.11.2008

108187

Турция

Слябы

24.03.2009

153675

Турция

Холоднокатанный прокат

25.08.2008

543167

Турция

Горячекатанный прокат

26.02.2009

67823

Турция

Слябы

27.11.2008

456565

Турция

Горячекатанный прокат

28.07.2009

765234