Как создать воронку продаж в Excel

В этой статье мы поговорим о том, как создать диаграмму «Воронка продаж» в Excel. Такой график можно создать за 5 простых шагов из нескольких линейчатых диаграмм с накоплением.

Что такое диаграмма-воронка?

График «Воронка продаж» подобен линейчатой диаграмме, при этом ряд данных выравнивают по центру, чтобы создать форму воронки. В данном примере воронка продаж показывает, сколько клиентов имеется на каждом этапе продаж в заданный момент времени.

Зачем используют диаграмму «Воронка продаж»?

График-воронка даёт читателю наглядную картинку о стадиях процесса. В нашем случае мы продаем трубопроводы (см. рисунок ниже). На Этапе 1 (Stage 1), где происходит первый контакт с потенциальным клиентом (Initial Contact), мы имеем самое большое число клиентов, но лишь небольшая доля этих самых клиентов доходит до Этапа 5 (Stage 5), на котором происходит покупка.

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

Строим воронку продаж за 5 простых шагов

График-воронка может быть создан за 5 простых шагов из линейчатой диаграммы с накоплением.

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

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

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

Формула промежуточного ряда: Эта формула находит максимальное значение в указанном диапазоне, вычитает из него значение текущей строки и делит результат на 2.

=(МАКС($D$7:$D$11)-D8)/2
=(MAX($D$7:$D$11)-D8)/2

Когда данные будут готовы, выделяем их и создаём Линейчатую диаграмму с накоплением (Stacked Bar Chart).

Шаг 2: Устанавливаем обратный порядок категорий

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

1. Кликните правой кнопкой мыши по вертикальной оси (на которой перечислены имена категорий) и выберите Формат оси (Format Axis).
2. В разделе Параметры оси (Axis Options) отметьте галочкой опцию Обратный порядок категорий (Categories in reverse order).

Теперь категории располагаются в том же порядке, как они перечислены на листе.

Шаг 3: Устанавливаем боковой зазор равным 0%

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

1. Кликните правой кнопкой мыши по любой из полос диаграммы и выберите Формат ряда данных (Format Data Series).
2. В разделе Параметры ряда (Series options) установите Боковой зазор (Gap Width) равным 0%.

Шаг 4: Убираем заливку промежуточного ряда

Цвет заливки промежуточного ряда должен быть прозрачным. Это сделает его невидимым для читателя, а значит на виду останутся только данные воронки:

1. Кликните правой кнопкой мыши по любой полосе промежуточного ряда и выберите Формат ряда данных (Format Data Series).
2. В разделе Заливка (Fill) выберите Нет заливки (No fill).

Шаг 5: Подготавливаем диаграмму к презентации

Теперь можно настроить вид диаграммы и убрать всё лишнее. Excel добавляет множество элементов (сетку, легенду и прочие), которые в данном случае не нужны.

Вот что я изменил в настройках внешнего вида моей диаграммы:

1. Удалил легенду
2. Удалил вертикальные линии сетки
3. Удалил линию вертикальной оси
4. Удалил горизонтальную ось
5. Изменил цвет заливки и контура полос воронки
6. Изменил заголовок диаграммы
7. Добавил подписи данных и выровнял их по центру.

Где еще можно использовать воронку продаж?

В приведённом выше примере использованы результаты подсчёта клиентов на разных этапах продажи трубопровода. Кроме этого, график-воронку можно использовать чтобы показать:

• Число учащихся на каждом уровне обучения (студент, выпускник, кандидат наук и так далее)
• Число сотрудников на каждом уровне организации (подчинённый, менеджер, управляющий и так далее)
• Размер дебиторской задолженности по срокам давности (30, 60, 90 дней)
• Возрастное распределение клиентов / населения (20, 30, 40 лет и так далее)

Какие же ещё существуют области применения для диаграммы «Воронка продаж«? Пожалуйста, задавайте любые вопросы и оставляйте предложения ниже в комментариях. Спасибо!