Как рассчитать воронку продаж в excel

Создание воронки продаж в Excel

Вариант 1. Использование встроенного графика "Воронка"

Этим способом могут воспользоваться те, у кого установлен Excel 2016 версии (или оформлена подписка Office 365). Также у пользователей вашим графиком должна быть версия не ниже 2016 года. Иначе вместо воронки они увидят пустое пространство. Если есть сомнения — переходите к следующему способу.

Первое, что нужно сделать — подготовить сходные данные. К примеру возьмем продажи квартир, коттеджей.

Данные для построения воронки продаж

Выделим диапазон A3:B7 перейдем на вкладку Вставка -> Рекомендуемые диаграммы -> Воронка. Сохраняем выбор нажав кнопку OK.

Вставка диаграммы Воронка

Воронка продаж готова, осталось по своему вкусу отформатировать ее.

Вариант 2. Использование гистограммы для создания воронки

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

В качестве примера буем использовать те же данные, что и в способе 1. Однако добавим дополнительно столбец Отступ (чуть позже будет понятно для чего).

Исходные данные для построения воронки

Выберем диапазон A3:C7 и вставим линейчатую диаграмму с накоплением (Вставка -> Все диаграммы).

Вставка линейчатой диаграммы

Перевернем воронку на 180 градусов. Для этого выберите ось Y и кликните по ней правой кнопкой мыши. Выберите команду Формат оси.

Формат оси Y в Excel

В группе Положение оси отметьте пункт Обратный порядок категорий . Кстати ось X нам не понадобится, можно смело выделить ее и удалить. С легендой можно поступить аналогично.

На данном этапе уже прорисовывается воронка, однако, для большей красоты, не хватает отступов от оси Y. Давайте их добавим. Для этого в столбце Отступ "протянем" формулу =(МАКС($C$4:$C$7)-C4)/2.

Теперь сделаем бесцветным отступы. Для этого выделим ряд Отступ (зеленый). Щелчком правой кнопки мыши откроем контекстное меню и выберем Формат ряда данных.

Формат ряда данныхВоронка продаж

Воронка готова. Можно дополнительно увеличить ширину этапов воронки и сократить зазор между ними. Откроем окно формата ряда данных со значениями воронки (аналогично формату ряда Отступ). Перейдем в Параметры ряда и уменьшим Боковой зазор (к примеру до 20%)

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

В этой статье мы по шагам разберем как построить диаграмму воронки продаж в Excel.

  1. Что такое воронка продаж?
  2. Зачем использовать диаграмму воронки продаж?
  3. 5 шагов построения воронки продаж в Excel
  4. 1. Подготовка данных. Построение линейчатой гистограммы с накоплением
  5. 2. Настройка обратного порядка категорий
  6. 3. Настройка бокового зазора гистограммы
  7. 4. Замена цвета “отступа” на прозрачный
  8. 5. Подготовка диаграммы для презентации

Что такое воронка продаж?

Это диаграмма отражающая количество покупателей на каждом из этапов процесса продаж в вашей компании.

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

С ее помощью вы можете оценить эффективность работы вашего отдела продаж на каждом из этапов взаимодействия с клиентами. Например, ваши менеджеры совершили 1000 звонков, которые позволили назначить 800 встреч. По результатам этих встреч фактически удалось провести презентацию 400 клиентам. Из тех клиентов, которым была проведена презентация, совершили покупку 150 клиентов. Разложив эти данные на диаграмме воронки продаж вы сможете оценить на каком из этапов эффективность работы менеджеров наиболее низкая.

5 шагов построения воронки продаж в Excel

1. Подготовка данных. Построение линейчатой гистограммы с накоплением

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

  • Наименование этапа воронки
  • Столбец с формулой для расчета отступа и смещения диаграммы в центр
  • Данные количества покупателей на каждом из этапов воронки

Итак, представим, что у нас есть таблица с данными о количестве покупателей на каждом из этапов воронки продаж:

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

Для расчета отступа в линейчатой диаграмме добавим, слева от столбца с количеством покупателей, новый столбец “Отступ”. В этой колонке мы в каждую строку вставим формулу, которая рассчитывает отступ для гистограммы. В ее основе используется функция МАКС:

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

  • Она определяет максимальное значение в диапазоне данных столбца с количеством покупателей;
  • Вычитает из полученного числа значение этапа, для которого рассчитывается отступ;
  • Делит полученное число на два для “центровки” графика.

Как только наша таблица готова – мы выделяем диапазон с данными и создаем линейчатую гистограмму с накоплением:

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

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

2. Настройка обратного порядка категорий

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

  • Кликнем правой кнопкой мыши по заголовкам на диаграмме и выберем пункт “Формат оси”:

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

  • В меню справа, на вкладке “Формат оси”, в разделе “Параметры оси” поставим “галочку” в пункте “Обратный порядок категорий”:

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

После этого, категории на диаграмме будут отсортированы в обратном порядке.

3. Настройка бокового зазора гистограммы

Теперь, для придания гистограмме вида воронки нам нужно изменить ширину рядов гистограммы. Для этого:

  • Кликнем по любому ряду диаграммы правой кнопкой мыши и выберем пункт выпадающего меню “Формат ряда данных”
  • В меню справа, в разделе “Параметры ряда”, в пункте “Боковой зазор” поставим число “0%”:

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

4. Замена цвета “отступа” на прозрачный

Наша диаграмма должна визуально быть похожа на воронку. Для этого нам нужно поменять цвет для линий “отступа” на прозрачный. Проделаем следующие действия:

  • Кликнем правой кнопкой мыши по любой линии отступа на диаграмме и выберем пункт “Формат ряда данных” из выпадающего меню:

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

  • В меню справа, в разделе “Границы и заливка”, в подразделе “Заливка” выберем пункт “Нет заливки”:

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

5. Подготовка диаграммы для презентации

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

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

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Ссылка на основную публикацию