5 полезных свойств сводных таблиц для бизнес анализа

Автор: Алексей Батурин.

бизнес анализИз данной статьи вы узнаете о 5 полезных свойствах сводных таблиц, которые помогу вам быстро и детально проанализировать ситуацию (на примере анализа клиентской базы). Вы узнаете:

1. Как сгруппировать данные;
2. Какие показатели можно рассчитать при сведении данных;
3. Как одновременно рассчитать несколько показателей по одному параметру при сведении данных;
4. Какие дополнительные возможности расчета при сведении данных вы можете использовать?
5. О возможности сортировки.

И на основании этого анализа мы затронем мощнейшую технику планирования мероприятий по стимулированию сбыта на рынках FMCG.

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

Установим курсор в левый верхний угол нашей таблицы, затем переходим в меню "Вставка" и нажимаем на кнопку "Сводная таблица":

Сводная таблица 

В диалоговом окне "Создание сводной таблицы" нажимаем "ОК":

Получили сводную таблицу на новом листе:

svodnaya tablica

Подробнее о создании сводных таблиц читайте в статье "Как сделать сводную таблицу и сгруппировать временные ряды?"

Скачать файл с примером!

1-е полезное свойство сводной таблицы для бизнес анализа — группировка данных

Итак, у нас есть отгрузки клиентам по дням, мы хотим понять, в каком диапазоне отгрузок у нас максимальные объемы продаж. Для этого нам надо сгруппировать отгрузки в диапазоны.

Перетаскиваем поле "Отгрузка сумма" в область сводной таблицы "Название строк" (зажимаем поле "Отгрузка_сумма" левой кнопкой мыши и перетаскиваем в раздел сводной "Название строк"):

 группировка в сводной таблице

Мы вывели все отгрузки в левый столбец сводной. Теперь устанавливаем курсор наши отгрузки (как на рисунке):

Группировка с помощью сводной

Заходим в меню Excel "Данные" и нажимаем на кнопку "Группировать"

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

В появившемся диалоговом окне устанавливаем шаг группировки "5000" (вы можете ввести любой) и нажимаем "ОК"

группировка в сводной

Получаем сгруппированные объемы продаж с заданным шагом:

Группировка данных

Для того, чтобы группировка выглядела красиво и воспринималось, нажимаем еще раз кнопку "Группировать" и руками ставим ровные значения, для значения "начиная с" — "-15 000" (ниже минимального значения, кратного 5000) "по" — "45 000" (больше максимальной группы, кратное 5000). 

Выставляем в сводной таблицы группы

Получаем сгруппированные данные по сумме отгрузке: 

      задаем шаг для групп в сводной таблице

 


2-е полезное свойство сводных таблиц для бизнес анализа —
возможность рассчитать различные сводные параметры по полям из исходной таблицы

Итак, отгрузки сгруппировали, теперь посмотрим - какие объемы продаж приходятся на каждый диапазон отгрузок. Для этого просуммируем отгрузки в сводной.

Зажимаем левой кнопкой мыши поле "Отгрузка_сумма" и перетаскиваем его в поле сводной "Значения":

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

Сводная по умолчанию рассчитала "Количество по полю Отгрузка_сумма", т.е. кол-во записей в нашей исходной таблице на листе "Данные". Т.к. таблица у нас содержит информацию по продажам клиентам по дням, то наш показатель "Количество по полю Отгрузка_сумма" — это количество отгрузок клиентам.

В итоге в сводной таблице мы видим количество отгрузок клиентам в разных диапазонах отгрузок:

группировка объемов продаж

 

Как нам из количества отгрузок получить сумму отгрузок?

Нажимаем левой кнопкой мыши на поле "Количество по полю Отгрузка сумма" в области сводной таблицы "Значения", и в раскрывшемся меню выбираем "Параметры полей значений…"

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

В раскрывшемся окне выбираем интересующую нас операцию сведения данных (Сумма, количество, среднее, максимум, минимум...). Выберем нужную нам операцию "сумма" и нажимаем "ОК". 

суммирование в сводной таблице

Получаем суммарный объём продаж для каждого диапазона отгрузок:

summa otgruzki itog

 Т.е. мы видим, какой объем продаж приходится на отгрузки в диапазоне от 0 до 5000 руб, от 5000 до 10 000 руб. и т.д. И видно, что максимальный объём отгрузок приходится на диапазон от нуля до 5000 руб.

Скачать файл с примером!


 

3 свойство – возможность для одного поля рассчитывать различные операции сведения данных

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

В область сводной таблицы "Значения" перетаскиваем еще 2 раза поле "Отгрузка_сумма"  и в параметрах поля значений для второго выбираем "количество" и для третьего поля выбираем "среднее".

Получаем для каждого диапазона отгрузок объем продаж, количество отгрузок и среднюю отгрузку:

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

Теперь мы видим, в каком диапазоне отгрузок максимальный объем продаж и максимальное количество отгрузок. В нашем примере это для диапазона от 0 до 5000 руб. и объём продаж и количество отгрузок максимально.

Скачать файл с примером!


4-е свойство сводных таблиц — возможность проводить дополнительные расчеты

Для наглядности анализа данных добавим еще 2 параметра — "Долю по объёма продаж по каждой группе"и "Долю количества отгрузок для каждой группы".

Для этого в поле сводной таблицы "Значения" перетащим еще 2 раза поле "Отгрузка сумма"

4 dop raszety

Причем для одного параметра в меню "Параметры поля значений" (как это сделать см. выше) мы выберем операцию "сумма", а для второго операцию "количество" .

Получаем таблицу следующего вида:

сведение и группировка дынных в сводной таблице

Теперь еще раз заходим в "Параметры полей значений" и входим во вкладку "Дополнительные вычисления":

дополнительные вычисления в сводной таблице

Выбираем в поле "Дополнительные вычисления" пункт "Доля от общей суммы"

дополнительные вычисления в сводной таблице

Получаем таблицу, в которой для каждого диапазона отгрузок клиентам мы видим объём продаж, количество отгрузок, среднюю отгрузку, долю объема продаж по каждой группе и долю количества отгрузок по каждой группе:

4 dolya obschey summy itog

Скачать файл с примером!


5 полезное свойство — сортировка

Теперь для наглядности от максимальной к минимальной группе по объёму продаж сделаем сортировку. Для этого установим курсор в поле с объёмом продаж по группам и нажмем на кнопку "сортировка от максимального к минимальному":

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

сортировка в сводной тиблице

Видно, что максимальная группа по объёму продаж и количеству отгрузок — это группа "от 0 до 5000 руб." средние продажи в этой группе составляют 1971 руб.

Обратите внимание! Средняя отгрузка по всем клиентам значительно отличается от 86% отгрузок. Причем отличается значительно

  • по всем группам средняя отгрузка равна 2 803 руб. (в строке общий итог).
  • А по 86% отгрузок 1 971 руб.

Это серьезная разница, и если мы будем стимулировать продажи опираясь на 86% отгрузок и среднюю по ним — 1 971 руб., то наши действия будут точней, а эффект гораздо выше, т.к. мы сможем заинтересовать максимальное количество клиентов. 

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

Скачать файл с примером!

Если есть вопросы, обращайтесь.

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

Комментарии   

#4 Алексей Батурин 08.03.2017 21:27
Цитирую оксана:
Здравствуйте! имеется сводная таблица-столбцы: отгрузка по неделям килограмм ; строки-торговые точки. Столбец общий итог-считает сумму отгрузок за 2 недели-а как посчитать разность отгрузок между двумя неделями? Заранее спасибо


Оксана, извиняюсь за задержку с ответом, вам надо войти во вкладку "Анализ" - при установки курсора на сводную таблицу она появится, далее найти пункт "Поля, элементы, наборы данных" и в нем "Вычисляемые поля" - создать вычисляемое поле!
Цитировать
#3 оксана 20.02.2017 13:30
Здравствуйте! имеется сводная таблица-столбцы : отгрузка по неделям килограмм ; строки-торговые точки. Столбец общий итог-считает сумму отгрузок за 2 недели-а как посчитать разность отгрузок между двумя неделями? Заранее спасибо
Цитировать
#2 +1 Алексей Батурин 04.02.2014 17:38
Цитирую Денис:
Добрый день, очень понравилась ваша статья и хотелось бы применять ее на практике для решения различных маркетинговых задач. Одна из таких задач, используя сводные таблицы сравнить свой продукт с продуктом конкурента. Например, электродвигатели по цене, мощности, производительности. Цель исследования выявить конкурентные преимущества и определить конкурирующие/аналогичные по характеристикам продукты среди нашего и конкурента ассортиментов. Как Вы думаете, можно провести корректный анализ?


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

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

Как сделать сводную описано здесь:
http://www.4analytics.ru/chto-vajno-znat-o/kak-sdelat-svodnuyu-tablicu-sgruppirovat-prodaji-po-godam-mesyacam-ocenit-dinamiku-i-sdelat-prognoz.html

потом свернуть данные из таблицы в сводную и анализировать их в нужных разрезах.
Цитировать
#1 Денис 04.02.2014 12:42
Добрый день, очень понравилась ваша статья и хотелось бы применять ее на практике для решения различных маркетинговых задач. Одна из таких задач, используя сводные таблицы сравнить свой продукт с продуктом конкурента. Например, электродвигател и по цене, мощности, производительно сти. Цель исследования выявить конкурентные преимущества и определить конкурирующие/а налогичные по характеристикам продукты среди нашего и конкурента ассортиментов. Как Вы думаете, можно провести корректный анализ?
Цитировать

Добавить комментарий