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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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