Так будет выглядеть готовый результат построения дашборда в Excel с возможностью интерактивного взаимодействия отчета с пользователем.
Чтобы создать свой такой же или подобный визуальный отчет в виде дашборда следует выполнить ряд последовательных действий в Excel.
В первую очередь создадим новую книгу с 3-ма листами:
Сначала создадим табличку с входящими данными на листе «Данные» так как показано ниже на рисунке:
После чего на листе «Дашборд» создадим первый управляющий элемент – выпадающий список. В данном случае рационально использовать поле со списком, так как оно имеет больше настроек. Конечно можно было бы воспользоваться стандартным выпадающим списком в Excel выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных: Список».
Но мы так делать не будем, так как он неудобен из-за своей боковой полосы прокрутки, которая появляется уже при 10-ти значений. А у нас в выпадающем списке должны отображаться 12 месяцев. Поэтому выберите другой инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком».
Сводные таблицы Excel с нуля до профи за полчаса + Дэшборды! | 1-ое Видео курса «Сводные Таблицы»
После выбора инструмента рисуем прямоугольник размером на полторы ячейки и получился выпадающий список.
Теперь нам необходимо его настроить. Щелкаем правой кнопкой мышки по выпадающему списку и из появившегося контекстного меню выбираем опцию: «Формат объекта». После чего появилось окно «Формат элементов управления», которое следует заполнить параметрами так как показано ниже на рисунке:
Как видно из параметров данный выпадающий список в данном примере настраивается 3-мя параметрами на вкладке «Элемент управления»:
- Список отображает значения из диапазона первого столбца ячеек таблицы входящих данных ссылаясь в первом поле «Формировать список по диапазону:» по адресу Данные!$A$2:$A$13.
- Второе поле «Связь с ячейкой:» позволяет указать ячейку куда будут возвращаться порядковые номера значений выпадающего списка. В данном случае они передаются в ячейку по адресу Обработка!$A$1. Например, если будет выбрано значение из нашего списка – «Март» тогда в ячейку A1 на листе «Обработка» передается число 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-53 в ячейку B6, чтобы быстро перемещается по шкале.
Для добавления нового блока с визуализацией желаемой метрики по показателям текущей недели выберите 1 из 9-ти блоков кликнув по нему левой кнопкой мышки:
В результате появится окно с миниатюрами иконками типа графика метрики:
Выберите желаемый график кликнув по нему мышкой. В результате на дашборде появится блок с выбранным графиком.
Внимание! Чтобы изменить в этом же блоке график с целью отображать в нем другой тип визуализации метрики его следует сначала удалить и лишь потом добавлять новый.
Для удаления графика из блока дашборда следует кликнуть по нему мышкой для вызова окна с миниатюрами визуализаций метрик и в этом же окне выбрать кнопку с красным крестиком в нижнем правом углу:
Так же удалить сразу все графики и диаграммы с дашборда можно воспользовавшись кнопкой сброса, которая находится над шкалой учетных периодов:
Переходим к следующему элементу управления конструктором:
В верху напротив первого блока находится кнопка-переключатель размера отображения графика в этом блоке: большой (4-х кратное увеличение) и малый – стандартный размер.
Источник: exceltut.ru
Управленческие отчеты в Excel: примеры
Подготовили подборку разработанных нами когда-то интерактивных управленческих отчетов в Excel для экономистов и финансистов. Эти отчеты (дашборды) можно скачать, заполнять данные и вносить изменения, а еще разобраться с тем, как они построены и «прокачать» свои навыки в Excel (но не обещаем, что это будет легко ))
Скачивайте файлы — это бесплатно. А если вы хотите научиться строить такие отчеты самостоятельно, приходите к нам на курсы .
Анализ бюджета доходов и расходов (БДР) по месяцам
Отчет показывает динамику изменения основных показателей БДР по месяцам: чистая прибыль, валовая прибыль, расходы и выручка, а также позволяет сравнить план и факт.
Переключение рисунков с помощью срезов и сводных таблиц
С помощью сводных таблиц, срезов и связанных рисунков, сделали для вас отчет, в котором можно переключать изображения и данные в диаграммах.
Если вы хотите научиться консолидировать бюджеты с использованием Power Query и выполнять сложные расчеты на основе больших массивов данных с помощью DAX-формул, приходите к нам на курсы « Бюджетирование с Business Intelligence »
Dashboard в Excel с кроссфильтрацией
Подготовил для вас Dashboard с надстройкой Power Pivot. Он замаскирован под Power BI с кроссфильтрацией, подсветкой элементов, переключением показателей и другими полезными штуками. Подробнее >>
Диаграммы с переключением для БДР
Предлагаю вам с одной стороны компактный, а с другой – универсальный вариант визуализации данных БДР (бюджета доходов и расходов).
Анализ ключевых показателей и EVA
В отчете проиллюстрированы изменение ключевых показателей, ликвидности, рентабельности собственного капитала и факторы, влияющие на изменение EVA.
Панель мониторинга 13 месяцев в году
Как показывать данные, чтобы было понятно, запоминалось и чтобы был план факт, прошлый месяц и тот же месяц прошлого года? Для этого нужно построить диаграмму за 13 месяцев.
Анализ бюджета доходов и расходов (БДР) за период
Отчет поможет проанализировать бюджет доходов и расходов за период, сравнить план и факт, провести факторный анализ прибыли и сравнить продажи с безубыточной выручкой.
Источник: finalytics.pro
Cоздание дашборда в Excel
Дашборд – это умный отчет в режиме реального времени. Главное условие создания дашборда – наличие данных, которые можно анализировать. Благодаря такой инфопанели руководители и менеджеры имеют возможность отслеживать наиболее важные показатели в режиме реального времени и быстро реагировать. Конечно, не каждому бизнесу нужно отчитываться каждую секунду, в большинстве случаев это еженедельная или ежемесячная периодичность.
Для создания дашборда не нужно быть профессиональным аналитиком, достаточно иметь знания, которые позволят вам определять самые важные показатели из большого массива данных и отображать их на итоговой панели.
Золотые правила дашборда
- Упрощайте подаваемую информацию, дашборд должен быть простым и понятным. Нет необходимости перегружать его избыточными данными
- Используйте сравнение (например, изменение показателя за разные периоды, или сравнение плана и факта)
- Используйте различные варианты визуализации данных. Таким образом, данные будут восприниматься проще и быстрее
Зачем нужен дашборд?
Чаще всего дашборды используются для визуализации бизнес-процессов, для отслеживания ключевых показателей эффективности, для сбора информации о состоянии веб-сайтов, для мониторинга производства. Список можно продолжать ещё, мы перечислили наиболее яркие примеры.
Данные есть в любой сфере деятельности. А если есть данные, то их можно и нужно проанализировать!
Интерактивный дашборд в Excel
В качестве примера мы решили рассмотреть достаточно актуальный, простой и общедоступный кейс – мониторинг продаж.
Создавать интерактивный дашборд мы будем в Microsoft Excel. Excel хоть и не предназначен для такого рода отчетов, но все-таки он обладает всеми необходимыми функциями для создания качественной инфопанели. Правда, крупным компаниям придется найти что-то более узкоспециализированное и функциональное. Потому как, анализировать большой поток данных и постоянно его обновлять в Excel — затруднительно.
Для простейшего дашборда мы использовали стандартные виджеты – круговую диаграмму для категорий, карточки с показателями, столбчатую диаграмму для сравнения показателей, таблицу с рейтингом, а также задействовали фильтрацию данных, для возможности ежемесячного просмотра отчета.
В этом видео мы хотим показать вам, как легко визуализировать данные. Важно использовать правильные инструменты и следовать основным статистическим принципам
Скачать файлы из этой статьи
Заключение
Источник: birdyx.ru