Доброго времени суток друзья!

     В этой статье я хочу затронуть такую тему как создать дашборд в 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 в табличном представлении. Данный вид отображения информации устанавливается по умолчанию.
• Линейчатая диаграмма отображает информацию в виде одной из выбранных линейчатых диаграмм.
• Гистограмма позволяет представить данные в виде гистограммы.
• Другая диаграмма представляет информацию в виде одной из трех доступных диаграмм: строчная, точечная и круговая.
• Карта представляет выбранные наборы данных в виде круговых диаграмм с географической привязкой к карте.

На рисунке сверху показан пример одного из видов визуальных отчетов, которые вы можете сделать с помощью надстройки PowerView. На карте отображены данные по продажам в виде кругов различных диаметров, которые показывают относительные продажи по регионам. Если вы наведете мышкой по одной из кругов, Excel отобразит текстовое поле с названием региона и значением продаж по нему.

Excel PowerView: пример создания простейшего дашборда

Чтобы продемонстрировать возможности надстройки PowerView, воспользуемся статистикой продаж компьютерной техники по регионам, которой мы уже пользовались при демонстрации работы надстройки GeoFlow (на самом деле PowerView работает практически с любыми видами данных, даже выгруженных из Azure Marketplace). Для этого выделите любую ячейку в таблице с данными и активируйте надстройку PowerView, как было описано ранее (Вставка -> Отчеты -> PowerView). Excel создаст новый лист и поместит в него сводную таблицу со всеми полями таблицы.

Уберите в панели Поля PowerView галочки с элементов Дата и Категория. На листе PowerView щелкните по заголовку поля таблицы Сумма, чтобы данные сортировались по убыванию. Измените внешний вид представления на гистограмму (Конструирование -> Представление переключателя –> Гистограмма –> Гистограмма с накоплением). У вас должно получиться вот такое Парето.

Чтобы добавить еще одно представление, снимите выделение с диаграммы, щелкнув мышкой в любом месте листа PowerView. Следующее представление будет отображать тренд продаж по датам, поэтому в панели Поля PowerView ставим маркеры напротив элементов Дата и Сумма. Меняем внешний вид, появившейся на листе PowerView, сводной таблицы на линейчатую диаграмму (Конструирование -> Представление переключателя -> Другая диаграмма–> Строка).

Осталось добавить представление карты, чтобы мы смогли проанализировать значения в зависимости от региона. Снимаем выделения со всех диаграмм, в панели справа ставим маркеры напротив полей Город, Категория и Сумма.

Появившуюся на листе PowerView таблицу превращаем в карту, для этого переходим по вкладке Конструирование в группу Представление переключателя, щелкаем по иконке Карта. В зависимости от скорости интернета и мощности вашего компьютера, вы некоторое время будете наблюдать чудную картину, как Excel раскидывает полученные данные по регионам страны.

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

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

Скачать файл с примером дашборда в PowerView в Excel

В цикле «Dashboards: как это делают профи» я собираюсь обсуждать наиболее удачные приёмы отображения и подачи информации при помощи Excel. Под термином «dashboard» в бизнесе сейчас понимается некое средство визуализации ключевых бизнес индикаторов, позволяющее быстро получить представление о состоянии какого-либо аспекта деятельности предприятия или процесса (например, текущее финансовое состояние, тенденции продаж, эффективность производства, либо просто визуализация каких-либо данных). За неимением краткого и устоявшегося русскоязычного аналога данного термина придётся использовать англоязычный термин.

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

Даже, если в ваши обязанности не входит разработка dashboard-ов, то эта тема всё равно будет очень полезной, потому что любая хорошо сделанная работа должна столь же хорошо быть презентована руководству и коллегам. Не важно, что вы сделали: съездили ли в командировку, выполнили ли план продаж, завершили проект, — оформите ваши результаты, подайте их достойно. Иначе с вероятностью 90% ваши усилия не будут замечены и адекватно оценены. Если же вам пока нечем хвастаться, то используйте эти советы для выработки собственного стиля, который будет безошибочно узнаваем коллегами и клиентами.

Что мы изучим в этой статье?

• Как можно визуализировать данные при помощи таких средств, как «фигуры» и объекты «WordArt».
• Автоматическая сортировка таблиц по факту изменения одной ячейки. То есть, если ячейка A3=1, то таблица сама (!), только при помощи формул сортируется по имени продукта, а если A3 изменяет своё значение на 2, то таблица пересортировывает себя по убыванию продаж.
• Рассмотрим очень интересный вариант получения мини-таблицы с полосой прокрутки, которая проецируется на большую таблицу на другом листе. Это крайне актуально в связи с тем, что места в любой dashboard всегда не хватает.
• И в качестве вишенки на торте, я покажу, как совместить сортировку и мини-таблицу, и обучить последнюю сортироваться, когда пользователь всего-лишь наводит указатель мыши на название колонки (ничего не нажимая!). Это выглядит очень эффектно и продвинуто.

Учебный пример

Качаем! Не забываем разрешать редактирование и включать макросы.

Фигуры и 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 располагаются вспомогательные формулы.

Назначение столбцов и обсуждение их формул

1. Столбец I с заголоком Row index не содержит формул, а просто пронумерован от 1 до 20. Он нам потребуется в дальнейших вычислениях.
2. Столбец H с заголовком Num for sort содержит значения, которые используются для сортировки. Чем больше значение в ячейке этого столбца — тем выше ячейка располагается в отсортированной таблице. К примеру, ячейка H4 содержит формулу:
   =ВЫБОР($A$3;21-$I4;L4)+0.00001*ЧСТРОК($I$3:$I4)
   или для англоязычной версии
   =CHOOSE($A$3;21-$I4;L4)+0.00001*ROWS($I$3:$I4)
   То есть, в зависимости от значения ячейки $A$3 (1 или 2), ячейка принимает значение 21-$I4 или L4. Кроме этого прибаляется небольшая дробная константа, зависящая от относительной строки ячейки (чем больше строка, тем больше константа. Это нужно на случай, если оригинальная таблица будет содержать одинаковые значения продаж.). То есть смысл формулы в том, чтобы значение ячейки было тем выше, чем выше должна располагаться соответствущая ячейка оригинальной таблицы после сортировки.
3. Столбец G с заголовком Needed order вычисляет последовательность, в которой должны отображаться строки оригинальной таблицы в соответствии с оценками, расчитанными в столбце H. Самое большое значение ячейки H получает индекс 1, следующее — 2, и так далее. Это реализуется при помощи полезной формулы =РАНГ($H3;$H$3:$H$22) или =RANK($H3;$H$3:$H$22).
   Приведем пример: если A3=2, то формула в G4 возвращает результат 11, что означает, что строка оригинальной таблицы K4:L4 (P2,504) должны во время сортировки быть на 11-й позиции.
4. Столбец F с заголовком Real sort вычисляет, какую строку оригинальной таблицы отсортированная таблица должна взять для соответствующей строки. Например, значение в F4 равно 4. Это означает, что отсортированная таблица C3:D22 в своей второй строке (C4:D4) должно взять данные из 4-й строки оригинальной таблицы — то есть K6:L6. Делается это (для ячейки в G4) при помощи формулы: =ПОИСКПОЗ($I4;$G$3:$G$22;0) или =MATCH($I4;$G$3:$G$22;0).
5. Отсортированная таблица в C:D извлекает данные из оригинальной при помощи простой формулы: =ИНДЕКС(rngOriginal;$F3;1) или =INDEX(rngOriginal;$F3;1). Второй столбец в качестве последнего параметра использует двойку.
   Надеюсь, ваш мозг не вскипел 🙂

Мини-таблица

На листе 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-гуру организации, в которой вы работаете!