Дашборд: что это такое и для чего нужен, инструкция

Создание дашбордов в Excel шаг за шагом

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

Чтобы создать свой такой же или подобный визуальный отчет в виде дашборда следует выполнить ряд последовательных действий в Excel.

В первую очередь создадим новую книгу с 3-ма листами:

  1. Дашборд.
  2. Данные.
  3. Обработка.

Сначала создадим табличку с входящими данными на листе «Данные» так как показано ниже на рисунке:

После чего на листе «Дашборд» создадим первый управляющий элемент – выпадающий список. В данном случае рационально использовать поле со списком, так как оно имеет больше настроек. Конечно можно было бы воспользоваться стандартным выпадающим списком в Excel выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных: Список». Но мы так делать не будем, так как он неудобен из-за своей боковой полосы прокрутки, которая появляется уже при 10-ти значений. А у нас в выпадающем списке должны отображаться 12 месяцев. Поэтому выберите другой инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком».

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

Теперь нам необходимо его настроить. Щелкаем правой кнопкой мышки по выпадающему списку и из появившегося контекстного меню выбираем опцию: «Формат объекта». После чего появилось окно «Формат элементов управления», которое следует заполнить параметрами так как показано ниже на рисунке:

Как видно из параметров данный выпадающий список в данном примере настраивается 3-мя параметрами на вкладке «Элемент управления»:

  1. Список отображает значения из диапазона первого столбца ячеек таблицы входящих данных ссылаясь в первом поле «Формировать список по диапазону:» по адресу Данные!$A$2:$A$13.
  2. Второе поле «Связь с ячейкой:» позволяет указать ячейку куда будут возвращаться порядковые номера значений выпадающего списка. В данном случае они передаются в ячейку по адресу Обработка!$A$1. Например, если будет выбрано значение из нашего списка – «Март» тогда в ячейку A1 на листе «Обработка» передается число 3 для дальнейшей обработки.
  3. «Количество строк списка:» – числовой параметр позволяет нам отображать выпадающий список без полосы прокрутки. Указав число 12, мы увеличили его размер на 12 записей, чего нельзя сделать с обычным выпадающим списком из проверки данных.

Готовый желаемый результат выглядит так:

Далее начинаем упорно работать с 3-тим листом «Обработка». На данном листе обрабатываются и подготавливаются все данные для вывода на дашборд. Будем двигаться с верху вниз. Сначала подготовим данные для верхних подписей. Для этого создаем табличку выборки показателей при условии полученного номера месяца, переданного выпадающим списком на лист «Обработка» в ячейку A1. В ячейке A2 определяем название месяца на основе полученного числа в ячейке A1, по формуле:

Делаем выборку из входящей таблицы на листе «Данные» для всех показателей с помощью функции =ВПР() скопировав формулу во все остальные ячейки:

Данные для верхних подписей показателей – подготовлены!

Единиц за период

Для начала на основе исходных данных листа Финансы создайте сводную таблицу на листе Фин_промежут (рис. 3). Затем на том же листе вставьте гистограмму и срез по кварталам. Уберите лишние элементы, добавьте подписи данных. Уменьшите количество цифр в подписях (подробнее см. Принцип Эдварда Тафти минимизации количества элементов диаграммы, Срезы сводных таблиц, Пользовательский формат числа в Excel раздел Некоторые дополнительные возможности форматирования). Вставьте новый лист. Назовите его Фин_панель. Переместите на него диаграмму. Обратите внимание: заголовок диаграммы не набран, а является ссылкой на ячейку А1.

Рис. 3. Сводная диаграмма Единиц за период

Набор отчетов

Прежде чем вы начнете строить что-либо в Excel, подумайте о своей аудитории. Кто будет использовать панель? Что они захотят на ней увидеть? Не полагайтесь лишь на свою интуицию. Если есть возможность, спросите у пользователей. Их точка зрения важнее вашей. Не делайте в Excel что-то оригинальное, о чем вы недавно прочли, и считаете, что это круто. Вы можете подумать, что создадите имидж эксперта. Но, если вы не решите проблемы пользователя, ваши усилия будут бесполезны. И именно такой имидж может за вами закрепиться.

Панели, как правило, не существуют изолированно; они являются частью набора отчетов. Один дашборд не ответит на все вопросы. Возможно, нужна еще одна панель с более глубоким уровнем детализации. А затем еще и отчет, который раскроет нюансы, не отраженные на дашборде.

Когда следует использовать панель, а когда отчет? Панель предназначена для быстрого визуального отображения фактов. Отчет содержит более подробные данные в структурированном формате. Отчет может содержать панель, а вот панель не может включать отчет.

Различают три типа панелей.

Стратегические – панели высокого уровня, используемые менеджерами для отслеживания ключевых показателей. Такие панели не содержат деталей, а их структура изменяется редко. Например, для торговой компании KPI могут включать объем и рентабельность продаж, размер дебиторской задолженности.

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

Операционные – панели, предоставляющие подробную информацию; часто в реальном времени. Они информируют пользователей о состоянии конкретного процесса и выявляют отклонения от нормы. Чтобы создать такие панели в Excel потребуется подключение к внешним данным.

Цели дашбордов

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

Следующая цель — составление иерархии данных. Довольно часто в компаниях необходимо сравнивать блоки информации. Характеристики могут быть относительными, что затрудняет их сравнение. Дашборд эффективно оптимизирует этот процесс.

Чтобы создать дашборд, нужно быть аналитиком?

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

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

Кто использует дашборды

Рассматриваемый нами инструмент используется разными специалистами в более узком спектре. Далее мы рассмотрим это детально.

Маркетологи

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

Отделы продаж

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

Руководители

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

Преимущества использования дашбордов

Преимущества такой отчетности довольно обширны и удивительны.

В целом, выделяется три фундаментальных преимущества использования дашборда.

  • Оптимизация и перевод большого количества данных на страницу с результатами. Таким образом, можно сократить ресурсы для визуализации.
  • Сравнение результатов. Благодаря чему, менеджеры могут предоставить более точные отчеты для своей организации. Для того чтобы сопоставить результаты отчетов, как правило, нужно несколько дней. На дашборде можно сделать все быстрее и компактней.
  • На дашборде выделяются основные показатели, которые необходимо увидеть руководителям. Практически все отчеты в дашборде — модульные, поэтому можно без проблем заменить таблицу или график на более важную.

А тема может быть совсем любая? Я думал, это только для бизнеса.

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

Панель Финансы

Вот что у нас должно получиться:

Рис. 2. Панель Финансы

Откройте Excel-файл Готовые примеры, и поэкспериментируйте с интерактивными элементами. Выберите квартал, и две диаграммы автоматически обновятся, а мини-отчет в левом нижнем углу изменит форматирование. Если вам интересно вникнуть в детали построения дашборда, откройте файл Пошаговые инструкции, и выполняйте действия, как описано далее.

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

Вставьте новый лист. Назовите его Фин_промежут. Построим элементы панели один за другим (см. рис. 2).

Дашборд-конструктор из диаграмм и графиков Excel

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

Заполнение исходных данных

Входящие данные для последующей обработки и визуализации необходимо заполнить в таблице на листе «Data»:

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

Дата – порядковая дата каждого дня 2024-го года. Все данные будут разбиты конструктором на недели.

Остатки на складах – количество товара на остатках по состояния на конец текущего дня. Остатки уже рассчитаны после сложения количества оприходованного товара и вычитания продаж товаров в штуках по состоянию на текущий день.

Поставка – количество оприходованного товара.

Продажа – общее количество проданного товара за день.

Мужчинам – сколько товара было продано мужчинам.

Женщинам – сколько среди покупателей было женщин.

Продажи по категориям товаров (A B C) – сегментирование продаж: сколько было продано штук в каждой категории (без возвратов).

Возвраты – сколько было возвратов в штуках.

Кросс-продажи – сколько было сделано апселлов по парам категорий AB, AC и BC. Таким образом здесь указывается статистика как взаимодействуют между собой категории товаров благодаря разным техникам кросс-продаж, которые помогают влиять на выбор потребителя. Подробнее о кросс продажах описано ниже в описании диаграммы «Взаимодействие кросс-продаж».

Продажа в $ – сумма проданного товара в долларах по состоянию на текущий день.

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

Кредит – расходы на погашение и обслуживание кредитных вложений.

Прибыль – чистая прибыль (общие доходы минус общие расходы).

Бюджет – сумма бюджета заложенного на покрытие расходов на текущий день.

В конструкторе дашбордов используется 13 элементов для визуализации метрик. Если Вам необходимо использовать их все, тогда необходимо заполнить все столбцы на листе «Data».

Очень похоже на инфографику. Это что, одно и то же?

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

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

Интерфейс конструктора дашбордов в Excel

На главном листе «DASHBOARD» находятся элементы управления конструктором, дашбордом и блоками диаграмм с графиками.

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

Переключатся между неделями можно двумя способами:

  1. С помощью переключателя элемента управления «Счетчик» (стрелками вверх и вниз).
  2. Непосредственным вводом числового значения 1-53 в ячейку B6, чтобы быстро перемещается по шкале.

Для добавления нового блока с визуализацией желаемой метрики по показателям текущей недели выберите 1 из 9-ти блоков кликнув по нему левой кнопкой мышки:

В результате появится окно с миниатюрами иконками типа графика метрики:

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

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

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

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

Переходим к следующему элементу управления конструктором:

В верху напротив первого блока находится кнопка-переключатель размера отображения графика в этом блоке: большой (4-х кратное увеличение) и малый – стандартный размер.

Заставьте ваши данные говорить

 
Курс очень понравился тем, что теоретическая информация дана в кратком, сжатом виде, только самое нужное и важное, нет “воды”, а также наличием совместных практических заданий и самостоятельных работ. Информация не пропадает и забывается, а сразу применяется на практике и гораздо лучше усваивается.
 
Курс интересный и полезный. Полагаю, что приобретенные знания и навыки значительно сократят трудозатраты на подготовку отчетности для руководства в необходимых формах и разрезах.
 
Курс очень лаконичный, тема раскрыта полностью, метод обучения для меня самый оптимальный – вместо скучной теории полезная практика.
 
Отличный курс для тех, кто только приступил к созданию отчетов и владеет excel на “пользовательском” уровне, дает много инсайтов по созданию дашбордов. Формат позволяет заниматься в удобное время и если будете выполнять задания вовремя, то получите разбор вашего кейса от автора курса

Курс будет интересен тем людям, кто делает отчеты для руководителей и не очень знаком с продвинутыми функциями Excel, а также чистым “аналитикам”, которые считают, что “и так все ясно из таблицы”. Мне лично было удобно проходить темы в удобное для меня время, интересным показалось также видео с разбором кейсов предыдущих участников. Теперь хочу пойти на курс Power BI

 
Курс позволяет в короткие сроки получить знания о грамотной визуализации данных. Обучение ведется на конкретных примерах. Показанный алгоритм позволяет мгновенно применить знания на практике, в отличие от изучения литературы с последующим освоением в программе. Упражнения модуля “Интерактив и срезы в Excel” позволяют структурировать данные для разработки дашборда.

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

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

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

Теперь в ячейку G1 вводим число 0, а целый диапазон ячеек G2:P11 заполняем формулой:

Диапазон G2:P11 состоит из 100 ячеек (10×10) и 100 единиц – соответственно. В каждой ячейке формула, которая проверяет количество единиц в диапазоне. Если оно больше или равно числу (процентов) в ячейке F1 значит следует прекратить заполнять данный диапазон единицами. Как видно, пока-что формула не работает, так как ей не хватает значений в диапазоне H1:P1, к которым она также обращается. В этом диапазоне будут вычисляться итоговые суммы чисел для подсчета количества единиц из предыдущих столбцов с помощью формулы, которую копируем во все ячейки диапазона H1:P1:

Теперь как видно все работает и диапазон ячеек G2:P11 заполняется единицами по условию, в зависимости от числового значения в ячейке F1.

Вафельный график будет состоять из двух слоев динамического (переднего плана – желтый цвет) и статического (задний план – черный цвет). Мы составили динамически изменяемые данные для первого желтого графика. Нам нужно еще создать черный статический график, который послужит задним фоном. Для этого понадобится диапазон размером 10×10 ячеек которые просто статически заполнены единицами. Поэтому рядом заполняем диапазон ячеек R2:AA11 единицами и строим по ним статический по такому же принципу, как и предыдущий – динамический.

Сначала создадим черный статический график. Для этого выполним ряд последовательных действий:

  1. Выделите диапазон ячеек R2:AA11 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Линейчатая»-«Линейчатая с накоплением»
  2. Делаем двойной щелчок мышкой по оси X, чтобы изменить настройки: «Формат оси»-«ПАРАМЕТРЫ ОСИ»-«Границы»-«Максимум» – с 12 на 10.
  3. После чего удаляем саму ось X, затем ось Y, название, легенду, сетку – поочередно выделяя их и нажимая клавишу Delete на клавиатуре:
  4. Делаем двойной щелчок по любому ряду данных графика и делаем настройку: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Боковой зазор» – 5%.
  5. Рядом возле графика создаем фигуру в виде черного круга. Выбреете инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Овал». Удерживая зажатой клавишу SHIFT на клавиатуре нарисуйте круг.
  6. Получился синий круг поэтому меняем цвет на черный. Для этого сделайте активной фигуру круг щелкнув по ней левой кнопкой мышки и выберите инструмент из дополнительного меню: «ФОРМАТ»-«Стили фигур»-«Черная заливка»:
  7. Скопируйте черную фигуру круга нажав комбинацию клавиш CTRL+C, затем выделите один из рядов на диаграмме и вставьте ее нажав клавиши CTRL+V на клавиатуре.
  8. Измените размеры сторон диаграммы сделав их равными – 5 на 5 см. Щелкните по графику сделав его активным и вызвав его дополнительное меню: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Размер»:

Черный график для фона готов! Теперь создадим динамический желтый, но сначала следует временно изменить значение 50% на 100% в таблице входящих данных (или временно вместо формулы ввести 100% в ячейку F1). Иначе не получится создать линейный график с накоплением для диапазона ячеек G2:P11.

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

ВНИМАНИЕ: Не забудьте обратно поменять значение 100% на 50%!

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

Далее выделите два графика удерживая клавишу CTRL на клавиатуре и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Упорядочивание»-«Группировать», как показано выше на рисунке.

После чего наложите один на другой и переместите группу (вырезать, вставить) на главный лист «Дашборд»:

Для управления слоями наложения диаграмм используйте инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Упорядочение»-«Область выделения», как показано выше на рисунке.

Динамический вафельный график в Excel – готов!

Аналогичным образом создаем еще два вафельных графика для показателей: «Показатель качества» и «Производительность».

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

Планирование панели

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

В Excel нет формулы, которую можно использовать для быстрого создания панели. Панель – это сочетание различных элементов, объединенных для визуализации информации. Думайте о дашборде, как о конструкторе Лего. В Excel кубиками являются формулы (СУММ, СУММЕСЛИ, СУММЕСЛИМН, СМЕЩ, ВПР), диаграммы, сводные таблицы, элементы управления и др.

Мы построим две панели, основанные на финансовых данных и данных о продажах.

Механизм обработки данных для дашборда

Весь механизм считывания и обработки исходных данных находится на листе с формулами «Processing»:

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

Уровни Excel

Чтобы сделать модели Excel максимально гибкими, я предлагаю использовать концепцию уровней. Гибкость подразумевает:

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

Уровень данных – это данные, импортированные из Oracle, SAP, … или набранные в Excel. Каждый столбец должен иметь заголовок и содержать схожие данные. Например, если столбец имеет заголовок Имя, то он должен содержать только имена. Не вставляйте идентификатор. Добавьте еще один столбец для идентификатора. Нет смысла экономить столбцы. Их в Excel 16 000, так что используйте столько, сколько вам нужно.

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

А вот что нужно сделать с данными, так это оформить их в виде таблицы (рис. 1). Это позволит обращаться к данным, как к единому массиву. Если вы добавите новые строки, ссылки обновятся автоматически. И вы по-прежнему будет обращаться ко всем данным сразу. Чтобы превратить данные в таблицу встаньте на любую ячейку внутри данных нажмите Ctrl+T (английское).

Рис. 1. Данные; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Уровень отчета – это то, что все видят. Он содержит диаграммы, сводные таблицы, кнопки, переключатели и т.п. Он красиво оформлен, включает логотипы и т.д. Это уровень, который печатается и отображается в презентации.

Уровень бизнес-логики – это то, что связывает уровень отчета с уровнем данных. Это формулы и иные средства вычисления, которые извлекают данные из уровня данных и преобразуют их в информацию.

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

Источники


  • https://exceltable.com/shablony-skachat/dashbord-skachat-v-excel
  • https://baguzin.ru/wp/mark-mur-dashbordy-v-excel/
  • https://reklamaplanet.ru/biznes/dasbord
  • https://skillbox.ru/media/management/dashbord_chto_eto_i_zachem_nuzhno/
  • https://exceltable.com/shablony-skachat/konstruktor-dashbordov-otchetov-excel
  • https://alexkolokolov.com/express_online

Рейтинг
( Пока оценок нет )
Понравилась статья? Поделиться с друзьями:
Все об Экселе: формулы, полезные советы и решения
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: