Воронка продаж в Microsoft Excel
Думаю, многие из вас уже не один и не два раза слышали или сталкивались с диаграммой Воронка Продаж - классической визуализацией работы с клиентами в любом бизнесе, связанном с продажами.
Всех поступивших к нам в этом, например, месяце 250 потенциальных клиентов (лидов) мы берём в оборот и сначала с ними связываемся (контакт). Тем, кого мы сумели заинтересовать высылаем нашу презентацию, а затем коммерческое предложение (КП). Ну, и какая-то часть из них в итоге (ура!) соглашается на сделку. Естественно, от этапа к этапу мы теряем часть потенциальных покупателей - кто-то в процессе передумает, кому-то не понравится наши цены, кого-то не "дожмут" наши менеджеры и т.д. И вот этот отсев критически важно отслеживать в любом бизнесе, чтобы понимать свои слабые места и докручивать скрипты продаж, коммуникацию с клиентами и т.д.
В Microsoft Excel есть несколько способов построить подобную визуализацию - от встроенных и простых до навороченных динамических.
Вариант 1. SmartArt без масштаба
Этот вариант хоть и не отображает размеры каждого слоя в воронке точно по числовым данным, т.е. не является полноценной, по факту, диаграммой, но точно достоин хотя бы краткого упоминания. Суть этого подхода в использовании графического объекта из коллекции SmartArt, который мы слегка "допиливаем" под задачу.
- Выбираем команду Вставка - SmartArt (Insert - SmartArt), а затем находим и вставляем на лист пирамиду со слоями носом вниз.
- Добавляем нужное количество слоев в текстовой панели со списком слева и дизайн на вкладке Конструктор (Design).
- Возможности настройки объектов SmartArt весьма скромные, так что лучше будет превратить нашу воронку в отдельные автофигуры. Щёлкаем по внешней рамке вставленной диаграммы правой кнопкой мыши и выбираем команду Преобразовать в фигуры (Convert to shapes).
- Задаём размеры каждого слоя (тут удобно держать Ctrl при для симметричного изменения ширины)
- Выделив каждую трапецию-слой, в строке формул можно ввести знак "равно", а затем щёлкнуть по ячейке, содержимое которой должно быть подписью слоя. Дополнительно можно сделать отдельный столбец со склейкой названия этапа и числового значения. При изменении числовых значений в будущем - подписи будут меняться и в воронке (но не ширина слоёв, конечно).
В общем и целом получается хоть и "на глазок", но достаточно красиво и для некоторых случаев, где не нужна хирургическая точность (типа презентаций) - вполне сойдет.
Вариант 2. Встроенная диаграмма воронка продаж
Если у вас Excel 2016 или новее, то построить такую диаграмму предельно просто - достаточно выделить таблицу со значениями каждого этапа и выбрать на вкладке Вставка (Insert) в группе диаграммы готовый тип Воронка (Funnel):
Вариант 3. Воронка продаж для старых версий Excel
Если вы счастливый обладатель старых версий Excel и встроенного готового типа такой диаграммы у вас нет, то можно легко её имитировать с помощью линейчатой диаграммы (горизонтальные столбики) с накоплением. Для этого нам потребуется добавить к нашей исходной таблице дополнительный столбец для расчета "пустышек" - прозрачных столбиков, выполняющих роль отступов для второго ряда, собственно, данных. Формула при этом может быть такой:
Т.е. мы берем первый, самый широкий слой, накидываем на него по 25% с каждой стороны (т.е. умножаем на 1.5), а затем вычитаем из получившегося значения размер очередного расположенного ниже слоя и делим это на 2 (т.к. воронка и отступы у нас должны идти симметрично).
После этого можно строить обычную линейчатую диаграмму с накоплением, обесцвечивать пустышки-отступы и разворачивать всю диаграмму на 180:
Вариант 4. Динамическая воронка продаж на сводной таблице
К сожалению даже в последних версиях Excel нет возможности построить воронку продаж по сводной таблице, т.е. сделать её воронку динамической. Однако это ограничение вполне можно обойти, если использовать связку Power Query + Power Pivot и немного напрячься :)
Предположим, что в качестве исходных данных нам досталась вот такая "умная" динамическая таблица, где в каждой строке фиксируется количество поступивших лидов, количество совершенных звонков (контактов), высланных презентаций и т.д. для каждого конкретного менеджера в определённый день:
Естественно, строить по ней воронку ещё рано - сначала нужно агрегировать эти данные, подсчитав общие итоги по менеджерам и этапам с помощью сводной таблицы. Но чтобы построить полноценную сводную, сначала нужно преобразовать наши данные в удобный для анализа вид, т.е. выполнить нормализацию. Для этого загрузим нашу таблицу в Power Query с помощью кнопки Из таблицы / диапазона на вкладке Данные (Data - From table / range), а затем щёлкнем правой кнопкой мыши по заголовку первого столбца Дата и выберем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot other columns).
В итоге получим нашу таблицу уже в "плоском" нормализованном виде, состоящем всего из трёх столбцов (последние два можно сразу переименовать в Этап и Количество):
Переименуем наш запрос в Sales (это будет имя будущей таблицы) и выберем на вкладке Главная команды Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to...), а затем в открывшемся окне вариант Только создать подключение (Only create connection) и включаем флажок Добавить эти данные в модель данных (Add this data to Data Model):
После нажатия на ОК наша таблица загружается в Модель Данных Power Pivot. Убедиться в этом можно нажав на вкладе Power Pivot кнопку Управление (Manage) и открыв окно надстройки Power Pivot:
Теперь можно построить не простую "классическую" сводную по данным с листа Excel, а сводную уже по Модели Данных из Power Pivot. Такая сводная предоставляет гораздо больше возможностей - в частности, даёт возможность создавать в ней меры - сложные вычисления на языке DAX. Для построения сводной выбираем в окне Power Pivot команды Сводная таблица - Сводная таблица на вкладке Главная (Home - Pivot table).
Теперь нам нужно будет сделать 2 меры - одну для суммирования по столбцу Количество и ещё одну для вычисления размера пустышек-отступов, как мы уже делали в предыдущем способе. Для создания мер выбираем на вкладке Power Pivot команды Меры - Создать меру (Measures - Create measure) и в открывшемся окне вводим имя меры, её формулу на языке DAX и числовой формат:
Аналогично создаём и вторую меру для пустышек:
Теперь можно закинуть в созданную сводную поле Этап в область строк, а обе созданные меры Результат и Пустышки в область значений:
Останется лишь построить сводную диаграмму по нашей сводной таблице, используя команду Сводная диаграмма на вкладке Анализ сводной таблицы (Pivot table analyze - Pivot Chart), и "допилить" её внешний вид, как мы делали это в предыдущем способе (сделать ряд пустышек прозрачным, развернуть ось Y в обратной последовательности и т.д.)
Дополнительно можно также добавить к нашей сводной срез по менеджерам для удобной фильтрации - кнопкой Вставить срез на вкладке Анализ сводной таблицы (Pivot table analyze - Insert slicer):
При любых изменениях исходных данных в будущем достаточно будет лишь нажать на кнопку Обновить всё на вкладке Данные (Data - Refresh all) - и наша динамическая воронка пересчитается полностью автоматически.
Ссылки по теме
- Преимущества построения сводной таблицы по Модели данных Power Pivot
- Диаграмма-торнадо в Microsoft Excel
- План-факт анализ в сводной таблице на Power Pivot
Подобную статью, датированную 2013 годом.
Возникает вопрос: "Зачм передирать друг у друга однотипные вещи"?
Или по принципу "Повторение - мать учения"?
Там только 1-й способ (самый примитивный) из четырёх(!), которые разбираю тут я.
Зачем такое писать?