Дашборд CSAT расчет индекса удовлетворенности клиентов в Excel

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

Пример как сделать шаблон дашборда CSAT в Excel пошагово

Из CRM системы был экспортирован в файл Excel отчет по обслуживанию клиентов торговыми представителями за период 1,5 года (180 дней). В конце каждого месяца проводится оценка по пятибалльной системе удовлетворенности всех клиентов фирмы по индексу «C-Sat» (Customer Satisfaction Score). У каждой группы торговых представителей закреплен свой супервайзер. Как видно на таблице отчета у каждого торгового представителя свое количество клиентов и свой средний показатель C-Sat по клиентам. В последнем столбце с помощью формулы =D2*E2 выводиться общая оценка уровня обслуживания клиентов фирмы:

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

Чтобы получить год из исходной даты используется функция =ГОД(). А чтобы получить название месяца по каждой дате следует воспользоваться формулой:

=ТЕКСТ(A2;"MMMM")

Формула для расчета номера квартала по дате в Excel:

Теперь таблица для загрузки входящих данных показателей индекса удовлетворенности клиентов CSAT в шаблон дашборда – ГОТОВА!

Создание динамической ссылки в Excel для сводной таблицы

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

В Excel есть изящное решение для подобного рода проблем. Просто при создании сводной таблицы следует использовать не статическую ссылку на диапазон данных, а динамическую. Для реализации данной задачи в Excel используются именные диапазоны с формулами на основе функции =СМЕЩ().

Сначала необходимо создать имя диапазона ячеек, который будет автоматически охватывать все заполненные ячейки на листе в пределах заданной таблицы. Для этого выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен (CTRL+F3)»-«Создать». В появившемся диалоговом окне «Создание имени» в верхние поля ввода указываем имя диапазона «СводТабл», а в нижнее поле ввода прописываем формулу:

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

Создание динамической сводной таблицы для дашборда в Excel

Теперь создаем саму сводную таблицу, выберите инструмент: «ВСТАВКА»-«Таблицы»-«Сводная таблица». В появившемся диалоговом окне «Создание сводной таблицы» в разделе опций «Укажите, куда следует поместить отчет сводной таблицы:» отметьте опцию «На новый лист». А в разделе параметров «Выберите данные для анализа:» в поле ввода «Таблица или диапазон:» поместите курсор и нажмите клавишу F3 на клавиатуре. Появиться окно «Вставка имени» со списком именных диапазонов, выберите «СводТабл» (или просто пропишите в поле ввода название имени вручную) и нажмите кнопку ОК на всех открытых окнах:

Новый созданный «Лист1» со сводной таблицей переименовываем в «Обработка» и выполняем настройку полей следующим образом:

Создаем дополнительное поле. Сделав активной сводную таблицу щелкнув по ней левой кнопкой мышки появилось дополнительное меню. Из дополнительного меню выбираем инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Вычисления»-«Поля, элементы и наборы»-«Вычисляемое поле». В появившемся окне «Вставка вычисляемого поля» заполняем поля ввода «Имя:» и «Формула» так как показано ниже на рисунке:

В поле ввода «Имя:» находиться название нового поля для сводной таблицы, а ниже его формула – значения поля «Оценка» разделены на значения поля «Клиентов». После внесения параметров нажмите на большую верхнюю кнопку «Добавить», а затем ОК.
Определяем расположение полей. Из всех полей в данной сводной таблице нас интересуют только 2: «Торговый» (помещаем в раздел «СТРОКИ») и новое «CSAT» (помещаем в раздел «ЗНАЧЕНЯ»):

Изменяем формат отображения значений. Делаем двойной щелчок по ячейке B3 где находится заголовок столбца «Сумма по полю CSAT» и в окне «Параметры поля значений». После жмем на кнопку «Числовой формат», чтобы изменить параметр «Число десятичных знаков:» с 0 на 2. И нажимаем ОК на всех открытых окнах.

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

Создание элементов управления дашбордом в Excel

Благодаря данной сводной таблице у нас появилась возможность создать сразу несколько элементов управления интерактивным дашбордом – кнопки. Реализуем же данную возможность с помощью срезов, выбрав инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Фильтр»-«Вставить срез». Из списка полей создаем срезы только для «Супервайзер», «Месяц», «Год» и «Квартал»:

Удерживая клавишу CTRL на клавиатуре и поочередно кликая левой кнопкой мышки активируем все 4 созданных среза. Инструмент «Срез» также обладает дополнительным меню при его активации из него же выбираем инструмент чтобы создать стиль оформления: «ИНСТРУМЕНТЫ ДЛЯ СРЕЗА»-«ПАРАМЕТРЫ»-«Стили срезов»-«Создать стиль среза»:

В появившемся окне «Создание стиля среза» в поле ввода «Имя:» указываем желаемое название для пользовательского оформления, а в группе опций «Элемент среза:» с помощью кнопки «Формат» задаем желаемый стиль оформления для каждого элемента среза, например, как показано на рисунке выше.

Далее для срезов «Год», «Квартал» и «Месяц» в параметрах разбиваем группу кнопок на 2 ряда: «ИНСТРУМЕНТЫ ДЛЯ СРЕЗА»-«ПАРАМЕТРЫ»-«Кнопки»-«Столбцы» – 2:

Также для всех срезов устанавливаем одинаковый параметр «Размер»-«Ширина» – 4,4см.

Снова выделяем все срезы кликая по ним мышкой и удерживая клавишу CTRL, а потом переносим (CTRL+X, CTRL+V) их на главный лист «Дашборд».

Как сделать стеклянный график в Excel

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

Перейдите на лист «Обработка», выделите диапазон ячеек со сводной таблицей A3:B14 и скопируйте ее (CTRL+C, CTRL+V) в ячейку D3.

В сводной таблице для стеклянной капсулы будут добавлены новые поля, поэтому сразу выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Вычисления»-«Поля, элементы и наборы»-«Вычисляемое поле»:

Как видно выше на рисунке, в появившемся окне «Вставка вычисляемого поля» создаем новое поле «Капсула» с формулой =5-CSAT (так как у нас 5-ти бальная система оценки) и нажимаем на кнопку добавить.

Аналогичным образом создаем еще одно новое поле «Колпак», но в качестве формулы просто значение =0,25:

Теперь выполняем настройку отображения полей второй сводной таблицы, размещая их в такой же последовательности как показано ниже на рисунке:

Обратите внимание на то, что одно и то же поле «Колпак» добавляется 2 раза на сводную таблицу (в начале и в конце).

Теперь переходим непосредственно к созданию стеклянного графика Excel в виде горизонтальной капсулы.

Выделите диапазон ячеек со значениями второй сводной таблицы D4:G4 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Линейчатые диаграммы»-«Объемная линейчатая с накоплением»:

Так как мы строим график по значениям сводной таблицы у него свое специфическое дополнительное меню настроек. Из него выберите инструмент: «РАБОТА СО СВОДНЫМИ ДИАГРАММАМИ»-«АНАЛИЗИРОВАТЬ»-«Показать или скрыть»-«Кнопки полей»-«Скрыть все». А затем нажав на кнопку плюс возле графика снимите все галочки со всех опций как на рисунке:

Осталось сделать 3 главные настройки дизайна стеклянного графика типа – Капсула в Excel:

Двойной щелчок левой кнопкой мышки по фону графика для ввода параметров: «Формат области построения диаграммы»-«ПАРАМЕТРЫ ОБЛАСТИ ПОСТРОЕНИЯ»-«ПОВОРОТ ОБЪЕМНОЙ ФИГУРЫ»-«Вращение вокруг оси X» – 15 градусов и «Вращение вокруг оси Y» – 0 градусов.
Двойной щелчок левой кнопкой по любому ряду данных, чтобы изменить 3D фигуру: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Фронтальный зазор» – 0%, «Боковой зазор» – 0%, «Фигура» – Цилиндр.
Двойной щелчок левой кнопкой мышки по каждому ряду данных, чтобы каждому задать свой цвет: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Сплошная заливка»-«Цвет»:

Ряд1: Белый фон оттенок 25%;
Ряд2: Темно-сизый;
Ряд3: Темно-сизый и Прозрачность 80%;
Ряд4: Белый фон оттенок 25%.

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

С помощью стандартных инструментов настройки цвета и размера шрифта на вкладке «ГЛАВНАЯ» оформляем число подписи данных.

Стеклянный график-капсула – ГОТОВ! Его мы также переносим на главный лист «Дашборд».

Объемная линейчатая 3D диаграмма в Excel для дашборда

По аналогичному принципу строиться линейчатый объемный график для показателей супервайзеров. Но сначала нужно подготовить и обработать входящие данные. Для этого снова копируем диапазон ячеек со сводной таблицей A3:B14 на листе «Обработка» и вставляем копию по адресу D6. В этой (третьей) сводной таблице выполняем настройку полей так как показано ниже на рисунке:

Как видно на рисунке также следует изменить число десятичных знаков после запятой до 1, таким же способом как было описано выше во второй сводной таблице.

Входящие данные по супервайзерам подготовлены! Теперь на их основе построим линейчатый объемный график. Выделяем диапазон ячеек D7:F9 и выбираем инструмент: «ВСТАВКА»-«Диаграммы»-«Линейчатые диаграммы»-«Объемная линейчатая с накоплением»:

линейчатый объемный график супервайзеров.

Как и в предыдущем графике скрываем все лишнее, кроме основной вертикальной оси:

Настройку положения 3D фигуры, цветов и подписей рядов данных выполняем по тому же принципу, как и в предыдущем графике капсулы:

Линейчатый график – ГОТОВ! Помещаем его также на главный лист «Дашборд».

График оценок по количеству звезд в Excel

Делаем оценочный график по кварталам, но сначала подготавливаем и обрабатываем данные. Снова создаем копию первой сводной таблицы на листе «Обработка» из диапазона ячеек A3:B14 и вставляем ее в ячейку D12. Сразу же выполняем настройку полей для новой 4-ой сводной таблицы. Сначала добавляем новое поле «МаксБаллов»:

Теперь распределяем поля по следующей схеме:

По значениям новой сводной таблицы составляем график. Для этого выделите диапазон ячеек D13:F16 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Линейчатая с группировкой»:

Удаляем все признаки сводной диаграммы:

Изменяем максимальное значение на горизонтальной оси X. Делаем двойной щелчок по самой оси и изменяем параметр: «Формат оси»-«ПАРАМЕТРЫ ОСИ»-«Границы»-«Максимум»–5,0:

Далее удаляем саму горизонтальную ось X и легенду графика. После чего форматируем ряды данных: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Перекрытие рядов» - 100% и «Боковой зазор» - 50%:

Рядом возле графика создаем фигуру звездочки: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Пятиконечная звезда»:

Изменяем ей цвет заливки на оранжевый и создаем ее копию без заливки (только контур фигуры). Выберите фигуру, а потом инструмент: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Заливка фигуры»-«Нет заливки»:

Теперь копируем сначала фигуру без заливки CTRL+C выделяем оранжевые ряды графика одним кликом и вставляем фигуру прямо в сам график CTRL+V:

Настраиваем заливку рядов графика оценок: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Размножить в масштабе»:

Теперь нам нужно выделить ряды данных на нижнем слое и вставить в них фигуру звездочки с оранжевой заливкой. Не всегда есть возможность получить доступ «на прямую» курсором мышки ко всем слоям рядов графика оценок. Для переключения между группами рядов данных на оценочном графике выберите инструмент из выпадающего меню: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Ряд: Сумма по полю CSAT». После чего выполняем аналогичные действия для фигуры оранжевой звездочки с аналогичными настройками параметров заливки ряда:

настройки для фигуры оранжевой звездочки.

Звездочный оценочный график готов! Осталось лишь удалить линии фоновой сетки и перенести график на главный лист «Дашборд».

Гистограмма для шаблона дашборда в Excel

Пришло время создать гистограмму с накоплением для анализа оценок по торговым агентам. Для этого нам понадобиться первая сводная таблица, которой нужно только лишь добавить ранее созданное поле «МаксБаллов»:

А теперь строим гистограмму по диапазону значений первой сводной таблицы A4:C14 выбрав инструмент: «ВСТАВКА»-«Диаграммы»-«Гистограмма с группировкой» и сразу же скрываем все лишнее на графике как и в предыдущих примерах:

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

Добавляем метки подписи данных только для нижнего ряда с показателями CSAT, форматируем их шрифт и график готов:

Также стоит отметить: если мы хотим выделить ряды на нижнем слое нам следует воспользоваться опцией из выпадающего меню: «Формат ряда данных»-«Параметры ряда»-«Ряд: Сумма по полю CSAT».

Данный график также не забудем переместить на главный лист «Дашборд».

График корреляции количества клиентов с индексом CSAT в Excel

И на конец остался последний график корреляции для шаблона данного дашборда. На нем мы будем отображать корреляцию динамики изменений индекса удовлетворенности клиентов C-SAT и количества клиентов на протяжении полного периода времени по месяцам. Как и в предыдущих примерах начнем с подготовки входящих данных для графика. Делаем новую копию сводной таблицы A3:C14 и копируем ее в ячейку A20. После чего сразу же переходим к настройке полей сводной таблицы по следующей схеме:

Теперь выделяем любую ячейку в границах данной сводной таблицы и выбираем инструмент: «ВСТАВКА»-«Диаграммы»-«Гистограмма с группировкой»:

Убираем все элементы управления сводной диаграммой, а легенду графика перемещаем вниз, как показано выше на рисунке.

Одним кликом мышки выделяем оранжевые ряды данных и изменяем для них тип отображения выбрав инструмент: «РАБОТА СО СВОДНЫМИ ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Тип»-«Изменить тип диаграммы»:

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

Настраиваем цвет для гистограммы и тип отображаемых маркеров для графика как показано ниже на рисунке:

На этот раз добавляем метки подписей данных для обоих рядов графика, но поочередно так как для них будут применяться разные настройки:

Последний график корреляции оценки уровня удовлетворенности клиентов и динамики изменения их количества – ГОТОВ! Его также помещаем на главный лист «Дашборд» и гармонично комбинируем все элементы на этом листе в целостную композицию интерактивной визуализации данных:

Скачать дашборд анализа индекса CSAT в Excel

Шаблон отчета для дашборда CSAT полностью готов к использованию! Достаточно лишь разместить свои данные на листе «Отчет» и шаблон сам определит количество записей в таблице и автоматически обновит информацию на всех своих графиках.