Excel дашборд по обслуживанию клиентов — формулы и система расчетов [часть 2 из 4]

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

 

 

Напомню, что наша серия сотоит из четырех частей:

Данные для дашборда

Мы уже видели, как выглядят данные, которые будут использоваться в дашборде.

таблица данных

Давайте быстро пробежимся, какие данные содержат колонки:

  1. Звонок ID: Уникальный идентификатор каждого звонка.
  2. Дата/Время: Дата и время полученного звонка.
  3. Категория: Категория продукта, которую охватывает полученный звонок.
  4. Регион: Местность, актуальная для полученного звонка.
  5. Категория клиента: Тип клиента, находящегося на другом конце провода.
  6. Длительность: Длительность звонка в секундах.
  7. Решен: Отвечает на вопрос, решена ли, полученная по звонку, проблема.
  8. Уровень удовлетворенность – оценка решения вопроса от 1 до 5.
  9. Дополнительные продажи.
  10. Агент: Служащий, принявший звонок.

Расчеты, необходимые в дашборде

Все расчеты, производимые в Excel дашборде, находятся на рабочем листе Расчеты.

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

Переменные, управляющие дашбордом

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

  1. Стартовая дата: Находиться в ячейке R2, от этой даты начинается отчет в 4 недели
  2. Категория сравнения: Выбирается из выпадающего списка в дашборде и определяет варианты сравнения – продукты, категория клиента, регионы и агенты.
  3. Сравнение опции 1 с опцией 2: Тут мы выбираем два пункта одной категории, которые хотим сравнить.  Например: в категории продуктов хотим сравнить элементы Софт c Ноутбуками и т.д. Фактическое выделение определяется с помощью VBA и задаются с помощью двух именованных диапазонов valOption1 и valOption2.
  4. Тип диаграммы: Вид диаграммы, который мы хотим отобразить.

Ключевые переменные в дашборде

Основные имена

Прежде чем начать разбираться с расчетами, необходимо понять несколько, заданных нами, имен.

  1. Данные: Это таблица с данными, которая содержит все записи колл-центра. Таким образом, если вы напишите Данные[Регион], Excel сошлётся на все 14832 значения регионов со всех звонков, которые мы получили.
  2. спскВыбора: Данное имя ссылается на столбец таблицы Данные. Таким образом, если вы выберете в качестве категории сравнения Продукт, имя будет возвращать данные, соответствующие формуле Данные[Продукт].
  3. спскДатаЗвонков: Так как мы используем только определенный период времени для расчетов, я создал именованный диапазон, который ссылается на конкретный отрезок дат и времени. Диапазон реализуется с помощью формулы =ЦЕЛОЕ(Данные[Дата/Время]).

Помимо этих 3-х имен, были созданы еще 16 именованных диапазонов для упрощения основных расчетов.

Извлечение данных 4-х недель

На первом этапе расчетов нам необходимо извлечь актуальные данные для 4-х недельного периода, начинающиеся с даты, указанной в ячейке R2 (Дашборд!R2). Для этого я создал таблицу с 16 колонками. Первый столбец содержит дату, следующие 3 столбца – информацию о количестве звонков (Общее количество звонков за день, Количество звонков для выбранной опции сравнения 1 и 2), следующие 3 столбца – время разговора, далее – индекс решения, далее – уровень удовлетворенности и, наконец, последние 3 столбца содержат информацию о дополнительных продажах.

таблица с расчетами

Данная таблица находится в диапазоне Расчеты!B10:Q37.

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

Для расчета количества звонков для каждой даты можно использовать формулу СУММПРОИЗВ, например, =СУММПРОИЗВ(—(спскДатаЗвонков=выбранная_дата)), где выбранная_дата – это дата с первого столбца.

Количество звонков, приходящиеся на конкретный день для конкретной выбранной опции, рассчитывается по формуле =СУММПРОИЗВ((спскДатаЗвонков=выбранная_дата)*(спскВыбора=выбор)). Здесь, в качестве переменной выбор используется, выбранная пользователем, опция для сравнения.

Для остальных столбцов данные рассчитываются аналогичным образом.

Расчет итогов

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

итоги дашборда

Чтобы рассчитать общее количество звонков за выбранный период, мы воспользовались формулой суммирования: =СУММ(C10:C37), которая суммирует количество звонков за 28 дней. Средняя длительность звонка была посчитана как общая длительность звонков деленное на количество звонков.

Расчет распределения уровня удовлетворенности

Это интересная часть. Чтобы распределить оценки удовлетворенности от 1 до 5, мы воспользовались функцией СУММПРОИЗВ. Итоги расчетов отображены на рисунке ниже.

распределение уровня удовлетворенности

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

Имена, используемые в дашборде

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

имена в дашборде

Скачать дашборд сервиса по обслуживанию клиентов

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

Что дальше? Создание графиков и спарклайнов

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


3 комментариев

  1. Ренат, здравствуйте!
    Интересная вещь Вами создана. Идея — супер, реализация — супер. Спасибо!
    Пытаюсь разобрать на составляющие.
    Не могу определить где ограничивается период в 4 недели. К примеру, если необходимо сделать дашборд на просчет квартала или года.
    Предполагаю, что это rngSel1 и 2, но как их вытащить на формулы — не понимаю.
    Подскажите?

  2. Ренат,спасибо.
    Получилось. Надо еще отметить, что на данный период влияет ячейка конца периода, это Е6. Ее надо привязывать к окончанию диапазона дат. И тогда уже не 4 недельный период будет считаться, а любой.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *