Инструкция как сделать в Excel. Все ключевые показатели сотрудников отдела продаж на одном экране.
Это случай из моего опыта. В компании работают 38 менеджеров по продажам, и нам необходимо видеть общую картину их показателей. Отчет сразу формирует рейтинг менеджеров по итоговой сумме продаж за год. Рейтинг свой у каждого филиала. Показан пример мониторинга восьми показателей для 11 сотрудников из 2 филиалов с января по октябрь:
Согласитесь, отобразить восемь показателей в диаграмме нереально! А отчет в виде таблицы без выводов и визуализации динамики не удобен для РОПа.
Что показывает дашборд отдела продаж?
Сортировки:
- Список сотрудников отсортированных по результативности (сумма продаж с начала года) внутри филиала.
- Список филиалов по убыванию результативности
Блок деньги:
- Динамика суммы продаж по месяцам (январь - октябрь)
- Сумма продаж за месяц
- Красная \ зеленая стрелка - сравнения итога за мес с предыдущим месяцем.
- Общий итог за год. (голубая линейная диаграмма)
Блок сделки:
- Динамика выигранных сделок за месяц (зеленые столбики)
- Динамика проигранных сделок за месяц (красные столбики)
- Кол-во выигранных и проигранных сделок за последний месяц (цифрой).
- Итого проигранных \ выигранных сделок за год (линейная диаграмма)
Спарклайны
Спарклайны (от англ. sparklines) – это маленькие графики, которые используются для визуализации изменений данных во времени прямо внутри ячейки. Они компактные и не содержат осей координат, подписей или других элементов, как обычные графики. Спарклайны позволяют быстро оценить динамику изменения показателя без необходимости привлекать внимание к деталям.
Когда нужно отслеживать четыре и более параметра на одном графике, спарклайны становятся особенно удобными. В таком случае это уже не просто график, а полноценный дашборд, подходящий для мониторинга динамики в отделе продаж, где трудится более десяти человек.
Какие инструменты Excel были использованы?
На примере данного дашборда рассмотрим два самых распространенных вида спарклайнов:
Спарклайн-график 📈 - колебание линии всегда эффективно при представлении общей динамики, представим с помощью мини-графиков динамику выручки по закрытым сделкам менеджера за период с января по октябрь.
Спарклайн-гистограмма 📊 - столбцы хорошо показывают абсолютные значения, если важно подчеркнуть именно размер показателя, а не его динамику во времени. Отметим с помощью столбов количество выигранных/проигранных сделок менеджером.
Кроме того, сегодня мы обсудим применение определенных инструментов условного форматирования, а именно:
Заливка по типу "Гистограмма" 🟦🟦⬜ - этот инструмент позволит заполнить ячейку с максимальной суммой выручки за год среди менеджеров цветом целиком, тогда как остальные ячейки будут окрашены пропорционально меньше, что наглядно покажет разницу в суммах.
Применение значков роста/падения в зависимости от данных ▲▼ - значки помогут интуитивно понять положительную или отрицательную динамику финансовых результатов последнего месяца.
Скачать шаблон
Скачать готовый шаблон в Excel, в который нужно только подставить свои цифры.
Пошаговая ИНСТРУКЦИЯ
Поставьте закладку в браузере - обязательно когда-нибудь пригодиться ☺️.
Пройдем все этапы создания шаг за шагом. Сначала создаем стандартный график.
Чтобы потренироваться скачайте учебный файл:
Подготавливаем поле
1. Создадим свободное пространство для творчества. Используем клавиши Ctrl+X и Ctrl+V, чтобы перенести числовые данные.
2. Удалим ненужные линии сетки, скрыв сетку построения таблицы.
Спарклайн-график
3. Добавим первый спарклайн, чтобы показать динамику сумм сделок каждого менеджера. Спарклайны позволяют отслеживать изменения множества параметров одновременно, сохраняя возможность сравнения.
4. Увеличим ширину столбца со спарклайнами для лучшего восприятия.
5. Объединяем по две ячейки рядом с каждым менеджером, увеличивая высоту спарклайнов без влияния на соседние ячейки.
6-7. На вкладке "Спарклайн" добавим последнюю точку для выделения итогового результата. Утолщим линию спарклайна для лучшей видимости и установим цвет последней точки такой же, как у линии, для гармоничного вида.
Последнее значение
8. Внесем сумму сделок за последний период в ячейку рядом со спарклайном, чтобы подчеркнуть значение последней точки.
9. Отметим, выросла или снизилась эта сумма по сравнению с предыдущим периодом. Вставим формулу в столбец F: =ЕСЛИ(X5<W5;-1;1). Она покажет, был ли рост финансовых показателей менеджера за последний месяц.
10. Заменим цифры на стрелки с помощью условного форматирования.
Общая сумма за год
11. Выведем общую годовую сумму. Для удобства сравнения добавим гистограмму через условное форматирование, где максимальная ячейка будет залита полностью, а остальные – пропорционально меньше.
12-13. Изменим цвет гистограммы на #C0E6F5, чтобы она не сливалась с текстом. Сделаем шрифт полужирным и увеличим ширину столбца для лучшей читаемости.
Спарклайн - гистограмма
14-15. Добавим спарклайны-гистограммы для отображения количества выигрышей и проигрышей. Вместо одного спарклайна используем два, так как они покажут динамику изменений в каждом периоде. Сначала создадим спарклайн для выигрышей, выбрав цвет #63C384.
16-17. Затем добавим спарклайн для проигрышей, используя контрастный цвет #C00000.
18. Введем значения выигрышей и проигрышей за последний период, аналогично сделкам.
Сумма выигрышей/проигрышей за год
19-20. Посчитаем общее количество выигрышей за год формулой =СУММ(AA5:AJ5) и применим зеленую гистограмму. Цвет шрифта сделаем белым, начертание — полужирным.
21-22. Затем рассчитаем количество проигрышей формулой =СУММ(AA6:AJ6)*-1 и применим розовую гистограмму (#D6007B). Текст также оформим белым полужирным шрифтом.
23. Добавим заголовки для таблицы. Ячейки первого уровня объединим и заполним светло-серым фоном, разделив блоки "Суммы закрытых сделок" и "Количество сделок".
24-25. Заголовки для столбцов добавим с нижней границей, чтобы выделить их и отделить от данных. Границу также проведем между данными по филиалам.
Результат
Мы превратили простую таблицу в информативный дашборд, используя цветовой код: красный — плохо, зеленый — хорошо, деньги — голубой. Этот инструмент идеально подходит для отчетности перед руководством, а также для анализа РОПа и коммерческого директора. Визуализация с помощью цветовых заливок и спарклайнов помогает наглядно сравнить и оценить динамику показателей менеджеров.
Данный дашборд информативный, но его недостаток - отсутствие оценки план \ факт. Руководитель может оценить менеджеров между собой, но не понимает насколько хороши эти показатели в целом. Дашборд менеджеры отдела продаж с план\фактом покажу в следующих статьях. Подписывайтесь на канал 📌.
Полезное
Скачать готовый шаблон в Excel, в который нужно только подставить свои цифры.
Поставьте закладку в браузере на инструкцию - обязательно когда-нибудь пригодиться ☺️.
Более подробно визуализация количества выигранных/проигранных тендеров представлена в статье Как показать позитивные и негативные данные на одном графике?
Еще много инструкций на моем сайте Магия диаграмм. Закажите мою новую книгу "Магия диаграмм". Погрузитесь в мир визуализации еще глубже.