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