Как сделать дашборд в excel
Как сделать dashboard в excel?
Доброго времени суток друзья!
В этой статье я хочу затронуть такую тему как создать дашборд в Excel, он же «dashboard». В статье я не могу детализировать и описать всё возможное о дашбоарде, так как это тема не только цикла статей, а целого мини курса, который я сделаю в ближайшее время. Но всё же сейчас я хочу сделать небольшой обзор, который сможет вас ознакомить с такой возможностью, для чего она нужна и как ее использовать, в своей работе.
Такое явление как дашборд в Excel используют в структуре отчётов для их лучшей визуализации и подведения общего итога. Как вы знаете, любая отчётность имеет разную структуру и схему отображения, но у всех есть общая черта, это наличие на начале составления первичных данных и в конце создается итоговый отчёт, который включает в себя все данные, тренды, показатели на уровне высшего менеджмента и помещается на одной странице, несмотря на то что первичные данные были жутко огромные.
Могу, с сожалением, сказать, что во многих предприятиях отчётность заканчивается на самых нижних уровнях, что не позволяет получать классной визуализированной отчётности по самым нужным показателям. 
Несмотря на то, что есть множество программ, которые специализируются на создании дашбоардов, тем не менее, долгое время Excel был за бортом этих тенденций и работ. Хотя это и странно, так как Excel просто идеален, для создания дашбоардов. Но в защиту Excel могу сказать, что основная проблема была не в программе, а в пользователях, а точнее в их уровне знаний. Люди просто очень привыкли к созданию стандартных отчётов, поэтому игнорируют возможность выйти на новый уровень в работе с отчётностью с высоким качеством. Посмотрите на примеры созданных в Excel дашбоардов, и вы сразу сможете оценить высокое качество и уровень таких типов отчётов, а также громадное отличие от стандартного типа отчётов.
Рассмотрим в чём же преимущества дашбоардов, более подробнее:
Создать, нужный вам дашборд в Excel возможно различными способами и задействовать разнообразнейшие инструменты Excel, об этом, я поговорю немного попозже. Но могу сказать, что начинать всё нужно с эскиза и макета вашего дашбоарда. Вам просто нужно взять в руки листик бумаги, ручку и нарисовать что же именно вы, или от вас, хотите увидеть в вашем отчёте, как и где эти данные будут размещены и в каком виде. Только после того как вы тщательно проработаете все модули вашего дашбоарда на бумаге, вы сможете перейти к его созданию в программе Excel. 
Вот, в принципе, я кратко описал, что собой представляет дашборд в Excel, для чего он служит, его преимущества и с чего нужно начинать его создание. Я понимаю, что это очень краткий обзор, но, тем не менее, вам для начала нужно понять подойдет ли вам такой тип отчёта или же всё же классические отчёты, типа создание умных или сводных таблиц, вам привычнее.
Собственно, хочу у вас спросить, стоит ли развивать эту тему сильнее? Написать цикл статей? Создать видео курс? Написать книгу? Или ничего не делать по этой теме или сделать всё перечисленное? Мне важно знать ваше мнение, напишите, пожалуйста, в комментариях, ваше мнение по этому вопросу!
До новых встреч на страницах сайта!
Бедняк лучше наслаждается розой на своем окне, чем богач своими обширными садами
П. Буаст
PowerView – это надстройка, входящая в состав Excel 2013. Вместе с PowerPivot надстройка PowerView позволяет создавать визуальные отчеты для ваших моделей данных в Excel.
Чтобы активировать надстройку, щелкните по кнопке PowerView, находящейся во вкладке Вставка в группе Отчеты.
Excel откроет новый лист PowerView (со сгенерированным названием PowerView1). К тому же, в списке вкладок появиться новая – PowerView, а в правой части экрана вы увидите панель Поля PowerView.
Чтобы отобразить данные в отчете PowerView, необходимо выбрать элементы, находящиеся в панели Поля PowerView. По умолчанию, информация будет представлена в виде маленькой сводной таблицы. Внешний вид визуализации можно изменить перейдя по вкладке Конструирование в группу Представление переключателя со следующими опциями:
На рисунке сверху показан пример одного из видов визуальных отчетов, которые вы можете сделать с помощью надстройки PowerView. На карте отображены данные по продажам в виде кругов различных диаметров, которые показывают относительные продажи по регионам. Если вы наведете мышкой по одной из кругов, Excel отобразит текстовое поле с названием региона и значением продаж по нему.
Excel PowerView: пример создания простейшего дашборда
Осталось добавить представление карты, чтобы мы смогли проанализировать значения в зависимости от региона. Снимаем выделения со всех диаграмм, в панели справа ставим маркеры напротив полей Город, Категория и Сумма.
Появившуюся на листе PowerView таблицу превращаем в карту, для этого переходим по вкладке Конструирование в группу Представление переключателя, щелкаем по иконке Карта. В зависимости от скорости интернета и мощности вашего компьютера, вы некоторое время будете наблюдать чудную картину, как Excel раскидывает полученные данные по регионам страны.
Теперь осталось дать название нашей работе, щелкнув на поле сверху листа, раскидать полученные представления по листу и наш дашборд готов.
Стоит отметить, что все представления, помещенные на лист PowerView, связаны между собой. Если вы щелкните по любой колонке диаграммы Сумма на Город, находящейся в левом верхнем углу, колонки, соответствующие другим городам, окрасятся в серый цвет, а две оставшиеся диаграммы отобразят информацию, касающуюся только выбранного города. То же самое произойдет, если вы щелкните по любому представлению данных других диаграмм.
Скачать файл с примером дашборда в PowerView в Excel
В цикле «Dashboards: как это делают профи» я собираюсь обсуждать наиболее удачные приёмы отображения и подачи информации при помощи Excel. Под термином «dashboard» в бизнесе сейчас понимается некое средство визуализации ключевых бизнес индикаторов, позволяющее быстро получить представление о состоянии какого-либо аспекта деятельности предприятия или процесса (например, текущее финансовое состояние, тенденции продаж, эффективность производства, либо просто визуализация каких-либо данных). За неимением краткого и устоявшегося русскоязычного аналога данного термина придётся использовать англоязычный термин.
Часто предполагается, что dashboard должен состоять из одной страницы, на которую выведено только самое необходимое и значимое. В связи с этим, особую важность приобретают знания, как красиво, лаконично и эффективно отобразить имеющиеся данные. Также следует понимать, что dashboard должен содержать не столько сырые исходные данные, а в основном — статистически обработанные данные, которые характеризуют суть анализируемых процессов, их качество и структуру.
Даже, если в ваши обязанности не входит разработка dashboard-ов, то эта тема всё равно будет очень полезной, потому что любая хорошо сделанная работа должна столь же хорошо быть презентована руководству и коллегам. Не важно, что вы сделали: съездили ли в командировку, выполнили ли план продаж, завершили проект, — оформите ваши результаты, подайте их достойно. Иначе с вероятностью 90% ваши усилия не будут замечены и адекватно оценены. Если же вам пока нечем хвастаться, то используйте эти советы для выработки собственного стиля, который будет безошибочно узнаваем коллегами и клиентами.
Что мы изучим в этой статье?
Учебный пример
Качаем! Не забываем разрешать редактирование и включать макросы.
Фигуры и WordArt
Это довольно простенький совет, но про эту возможность многие забывают. Excel (а также и Word, и PowerPoint) располагает большим количеством векторных фигур, при помощи которых можно нарисовать всё что угодно. У меня есть друг, который прекрасно рисовал в MS Office инженерные чертежи. Получалось очень недурно. Кроме этого многие фигуры могут иметь текстовые метки, которые можно привязать через именованный диапазон к ячейкам рабочего листа. А учитывая огромное количество эффектов оформления как самих фигур, так и текста внутри них, это становится незаменимым инструментом для придания dashboard визуальной привлекательности. Экспериментируйте — вам понравится!
Для вставки на лист фигуры выберите в ленте Вставка и в разделе Иллюстрации найдите кнопку Фигуры. Для вставки WordArt — на этой же ленте в разделе Текст нажмите кнопку Добавить объект WordArt. В принципе, никакой особой разницы между, скажем, фигурой Надпись и объектом WordArt нет. Это всё одно и то же, начиная с MS Office 2007.
Чтобы связать подпись фигуры с ячейкой, ячейку надо объявить в виде именованного диапазона и присвоить его фигуре в строке формул.
Сортировка формулами
Тут я должен сказать, что эти сокровенные знания, которыми я хочу с вами поделиться, я подсмотрел у «великого шамана Excel» (так он себя называет). У Jean MarcVoyer (Канада) действительно есть чему поучиться. Я едва смог декомпозировать то, что сейчас рассказываю вам. Но любое знание это на 95% заимствование, поэтому не будем расстраиваться, что мы не первооткрыватели. В современном мире проблема не в том, чтобы узнать что-то принципиально новое, а в том, чтобы освоить то знание, которое доступно.
Итак — сортировка формулами. Смотрим лист AutoSort учебного примера. У нас есть ячейка A3 в качестве индикатора типа сортировки: 1 — сортировка по имени продукта, 2 — сортировка по убыванию продаж. Исходная таблица, которую мы будем сортировать находится в диапазоне K3:L22 и объявлена именованным диапазоном rngOriginal. Отсортированная формулами таблица располагается в ячейках C3:D22. В столбцах F:I располагаются вспомогательные формулы.
Назначение столбцов и обсуждение их формул
Мини-таблица
На листе SortOnTheFly вы видите мини-таблицу в E7:G11, внутри которой при помощи полосы прокрутки (клавиатура не работает) мы можем просматривать отсортированную таблицу из C3:D22 листа AutoSort. В этом нам помогают ячейка A8, связанная с полосой прокрутки, и диапазон N6:P10, который, собственно, вычисляет те данные, которые надо показывать в мини-таблице.
Данные в таблицу N6:P10 выбираются (на примере ячейки O6) при помощи формулы =СМЕЩ($C$2;$N6;0) или =OFFSET($C$2;$N6;0). Ячейка N6 зависит от ячейки А8 (позиция полосы прокрутки).
Сортировка наведением мыши
Мы подошли к жемчужине нашего обзора. Описываемый трюк легко реализовать, но о нём практически никто не знает. Мы научились с вами сортировать нашу учебную таблицу путём изменения ячейки A3. Теперь мы хотим заставить эту ячейку меняться, когда мы только подводим мышку к значку сортировки! Никакие стандартные события рабочего листа тут не помогут, это именно трюк, основанный на одном малоизвестном свойстве функции рабочего листа ГИПЕРССЫЛКА.
Снимите защиту с листа SortOnTheFly и посмотрите на формулы в ячейках F6 и H6 — там, где расположены символы сортировки. Вы увидите =ЕСЛИОШИБКА(ГИПЕРССЫЛКА(highlightSeries(F6));»6″) или =IFERROR(HYPERLINK(highlightSeries(F6));»6″)
Оказывается формула ГИПЕРССЫЛКА обладает одной замечательной особенностью — она проверяет то, что ей передали в параметрах, когда вы наводите мышь на ячейку, в которою эта формула введена. А в качестве ссылки у нас там highlightSeries(F6). highlightSeries — это короткая VBA процедура, которая выглядит следующим образом:
Так вот ГИПЕРССЫЛКА вызывает нашу процедуру в момент наведения мыши! Процедура в зависимости от переданного адреса устанавливает константу в A3 =1 или =2. А дальше мы знаем 🙂
«Если вы мне скажете, что это плохо, вы мой кровный враг на всю жизнь!»
(С) Проф. Преображенский
Осталось прокомментировать, как сделаны значки сортировки. Был использован шрифт Webdings и условное форматирование:
Конструктор дашбордов и визуальных отчетов скачать в Excel
Вашему вниманию предлагается простой дашборд-конструктор версия 1.0, который можно бесплатно скачать по ссылке в конце статьи. Используя этот шаблон для конструирования визуализации по Вашим исходным данным, Вы имеете возможность выводить на панель только важную информацию для Вас. В Вашем распоряжении библиотека из 13-ти графиков и диаграмм.
Дашборд-конструктор из диаграмм и графиков Excel
Я сконструировал такой конструктор где можно в несколько кликов мышкой набросать отчет по одним из самых популярных метрик для анализа эффективности продаж по неделям. Рассмотрим функционал конструктора и правила его использования. Но сначала шаблон конструктора дашборда необходимо заполнить исходными данными.
Заполнение исходных данных
Входящие данные для последующей обработки и визуализации необходимо заполнить в таблице на листе «Data»:
Название каждого столбца говорит за статистические показатели, которыми должны быть заполнены их ячейки:
Дата – порядковая дата каждого дня 2024-го года. Все данные будут разбиты конструктором на недели.
Остатки на складах – количество товара на остатках по состояния на конец текущего дня. Остатки уже рассчитаны после сложения количества оприходованного товара и вычитания продаж товаров в штуках по состоянию на текущий день.
Поставка – количество оприходованного товара.
Продажа – общее количество проданного товара за день.
Мужчинам – сколько товара было продано мужчинам.
Женщинам – сколько среди покупателей было женщин.
Продажи по категориям товаров (A B C) – сегментирование продаж: сколько было продано штук в каждой категории (без возвратов).
Кросс-продажи – сколько было сделано апселлов по парам категорий AB, AC и BC. Таким образом здесь указывается статистика как взаимодействуют между собой категории товаров благодаря разным техникам кросс-продаж, которые помогают влиять на выбор потребителя. Подробнее о кросс продажах описано ниже в описании диаграммы «Взаимодействие кросс-продаж».
Расходы без кредита – сумма расходов, в которой не учитываются привлечение кредитных средств. Расходы на погашение кредитных средств желательно вести отдельно, чтобы всегда реально оценивать возможности фирмы в период финансового кризиса. Подробнее можно узнать в описании столбчатой гистограммы «Объем продаж» данного шаблона дашборда.
Кредит – расходы на погашение и обслуживание кредитных вложений.
Прибыль – чистая прибыль (общие доходы минус общие расходы).
Бюджет – сумма бюджета заложенного на покрытие расходов на текущий день.
В конструкторе дашбордов используется 13 элементов для визуализации метрик. Если Вам необходимо использовать их все, тогда необходимо заполнить все столбцы на листе «Data».
Механизм обработки данных для дашборда
Весь механизм считывания и обработки исходных данных находится на листе с формулами «Processing»:
Здесь много нужно описывать, но лучше скачать готовый файл примера и посмотреть все формулы на этом листе. Каждый блок формул подписан также как названия диаграммы или графика, который к нему относится.
Интерфейс конструктора дашбордов в Excel
На главном листе «DASHBOARD» находятся элементы управления конструктором, дашбордом и блоками диаграмм с графиками.
В левой части находится панель управления для выбора номера текущей недели, которую следует отобразить на дашборде:
Переключатся между неделями можно двумя способами:
Для добавления нового блока с визуализацией желаемой метрики по показателям текущей недели выберите 1 из 9-ти блоков кликнув по нему левой кнопкой мышки:
В результате появится окно с миниатюрами иконками типа графика метрики:
Выберите желаемый график кликнув по нему мышкой. В результате на дашборде появится блок с выбранным графиком.
Внимание! Чтобы изменить в этом же блоке график с целью отображать в нем другой тип визуализации метрики его следует сначала удалить и лишь потом добавлять новый.
Для удаления графика из блока дашборда следует кликнуть по нему мышкой для вызова окна с миниатюрами визуализаций метрик и в этом же окне выбрать кнопку с красным крестиком в нижнем правом углу:
Так же удалить сразу все графики и диаграммы с дашборда можно воспользовавшись кнопкой сброса, которая находится над шкалой учетных периодов:
Переходим к следующему элементу управления конструктором:
В верху напротив первого блока находится кнопка-переключатель размера отображения графика в этом блоке: большой (4-х кратное увеличение) и малый – стандартный размер.
Это все что Вам нужно для конструирования своих дашбордов с различными еженедельными отчетами по своим исходным данным. Далее рассмотрим всю библиотеку графиков подробнее.
Описание библиотеки конструктора дашборда из диаграмм и графиков
Первый график «Объем продаж» представляет собой столбчатую гистограмму в которой отображаются суммы продаж, разделенные на доли их составляющий частей:
Данный график не содержит вертикальной оси Y, так как его основана задача показать в какой день недели был наибольший или наименьший объем продаж. А также какую долю составляют каждая часть объема по отдельности в тот или иной день.
Важной особенностью представления данных метрик является отдельное экспонирование показателей по кредитным ресурсам. В современном бизнесе следует тщательно следить за показателями деятельности предприятия в двух плоскостях: с привлечением кредитных ресурсов и без. Таким образом можно реально оценивать возможности выживания бизнеса в период финансовых кризисов. Ведь если современный бизнес не будет вообще использовать дополнительные кредитные ресурсы, тогда будет проигрывать конкурентам, которые эффективно используют займы для форсирования роста объема финансового и товарного оборота.
С другой стороны, если придет финансовый кризис, тогда резко подорожают кредитные ресурсы, а для многих фирм они будут даже недоступны, что может повлечь за собой печальные последствия. Поэтому следует постоянно следить за уровнем независимости бизнеса от кредитов.
Данный график имеет возможность отображать долю отрицательной прибыли, ведь бывают дни, когда расходы превышают доходы. Отрицательная разница между доходами и расходами отображается долей красного цвета на гистограмме. Они сигнализируют о том, что владельцу бизнеса следует срочно поменять стратегию.
Следующий график «Доходы и расходы». Отображает соотношение расходов к доходам в процентах за недельный учетный период времени:
Линейчатая гистограмма «Рейтинг продаж по дням» отображает рейтинг самых успешных дней в продажах для текущей недели и соотношение остальных дней к топ-дню флагману. Показатели автоматически сортируются по убыванию:
При обращении клиента к магазину за покупкой определенного товара продавцы предлагают дополнительные сопутствующие товары. Если клиент купил вместе с основным дополнительно предложенный товар – в результате произошла кросс-продажа. На данной диаграмме можно заметить долю всех сделок за целую неделю с активными кросс-продажами (сильное взаимодействие продавцов и товаров с клиентами), пассивными (слабое взаимодействие) или без кросс-продаж (нет взаимодействий).
Здесь ясно видно отображение активности продаж отдельно по каждой группе товаров в определенные дни текущей недели. Одна из любимых визуализаций данных не требующая много описания, а одним лишь своим внешним видом передает много смысловой информации без слов.
Полосатые синие горы – это фактические остатки на текущий конец дня недели. Кирпичные столбы – дни и объемы поставки товаров. Желтая кривая – объем ежедневных продаж на протяжении недели.
Зеленым цветом уровень бюджета, а красным – доля превышения расходами суммы установленного бюджета для этой недели. На протяжении года ежедневные или еженедельные и ежемесячные бюджеты могут существенно изменятся в зависимости от сезонности бизнеса. Ежедневные бюджеты указываются в ячейках последнего столбца на листе «Data».
В данном примере максимально допустимая доля 12%. Данное значение устанавливается на листе «Processing» в ячейке I40.
Используя данную библиотеку из 13 элементов визуализации с помощью конструктора можно составлять свои собственные дашборды для еженедельных отчетов планировки:
При необходимости вы можете самостоятельно заменить диаграммы на ваши собственные. Только создавайте их в группах и соблюдайте последовательности имен групп. Для управления группами и всеми фигурами на листе жмите ALT+F10. Если вам потребуется больше диаграмм и графиков чем 13, тогда редактируйте макросы конструктора дашбордов в редакторе Visual Basic – ALT+F11.