Марк Мур. Дашборды в Excel

Рубрика: 7. Полезняшки Excel

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

Скачать заметку в формате Word или pdf, готовые примеры и пошаговые инструкции в формате Excel

Moore, Mark. Mastering Excel: Building Dashboards

Уровни Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выручка по регионам

Выделите ранее созданную сводную диаграмму скопируйте ее в буфер, и вставьте на свободное место на листе Фин_промежут. Располагайте сводные таблицы приблизительно в том же положении, что и диаграммы на панели. Так будет удобнее управлять ими. Измените настройки новой сводной таблицы (рис. 4). Обратите внимание: новая сводная ссылается на тот же срез, что и первая. Вставьте и отформатируйте диаграмму. Вырежьте ее и перенесите на лист Фин_панель. Вырежьте и перенесите на лист Фин_панель срез.

Рис. 4. Выручка по регионам

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

Рис. 5. Подключение среза к сводным таблицам

Выручка по продуктам

Для разнообразия этот отчет сделан не на основе сводной таблицы, а с помощью формул. В отчете будет выделен квартал, выбранный срезом (если он один). Для начала создадим уникальный список продуктов. Перейдите на лист Финансы. Выделите столбец Е. Скопируйте его. Вставьте в столбец I. Пройдите по меню Данные –> Удалить дубликаты (рис. 6).

Рис. 6. Создание уникального списка продуктов

Вырежьте список продуктов, и вставьте его на лист Фин_панель под левой диаграммой. Небольшая проблема: названия продуктов не вписываются в столбец B. Если же вы увеличите ширину столбца B, это изменит размер диаграммы. Решение: установить размер диаграммы неизменным. Выделите диаграмму. Кликните на диаграмме правой кнопкой мыши и выберите Формат области диаграммы. Перейдите на закладку Размер и свойства, и в области Свойства установите переключатель в позицию Не перемещать и не изменять размеры (рис. 7). Повторите эти действия и для второй диаграммы.

Рис. 7. Как сделать размер диаграммы не зависящим от размера ячеек

Формула в ячейке С21 содержит смешанные ссылки. Она подготовлена для копирования по диапазону С21:F26. Этой же цели служит и выбор ссылок на целые столбцы (Финансы!$G:$G), а не на столбцы Таблицы (Финансы[Сумма]). Последние, к сожалению, поддерживают только формат относительных ссылок.

Рис. 8. Формула СУММЕСЛИМН() отчета

Квартал, выбранный в срезе, автоматически выделяется в отчете. Эта сделано с использованием условного форматирования. Значение в срезе нельзя непосредственно использовать при форматировании. Однако срез изменяет поле Фильтры сводные таблицы, которое можно использовать для форматирования отчета (рис. 9).

Рис. 9. Условное форматирование на основе номера квартала

Вот, какой вид мы хотим придать отчету:

Рис. 10. Фрагмент отчета, выделенный условным форматированием

На самом деле здесь не один, а три условных формата для ячеек: D20, D21:D26, D27. Поэтому нужно создать три различных правила для ячеек С20:F20, С21:F26 и С27:F27. Во-первых, выберите ячейки C20:F20 (или иные ячейки с номерами кварталов). Пройдите по меню Главная –> Условное форматирование –> Создать правило –> Использовать формулу для определения форматируемых ячеек (позиция 1 на рис. 11). Введите формулу (2):

=C20=Фин_промежут!$I$1

Рис. 11. Формула условного форматирования для ячеек С20:F20

Как обычно, обратите внимание на формат ссылок. С20 – относительная; мы хотим, чтобы ссылка менялась при переходе к ячейке D20 и далее. Фин_промежут!$I$1 – абсолютная; мы хотим, чтобы значение одной из ячеек в диапазоне С20:F20 всегда сравнивалось с одной и той же ячейкой на листе Фин_промежут. Нажмите Формат (3), перейдите на закладку Граница, и выберите тип линии и тип границы (рис. 12). Перейдите на закладку Шрифт и выберите полужирный.

Рис. 12. Форматирование ячеек из диапазона С20:F20

Повторите манипуляции для диапазонов С21:F26 и С27:F27. Самое сложное во всем этом –формула. Если не догадались, то вот вам подсказка =C$20=Фин_промежут!$I$1. По этой формуле применение формата к диапазонам С21:F26 и С27:F27 основано на содержимом в ячейке С20. К сожалению, если выбрать на срезе более одного квартала, условное форматирование не работает.

Годовая выручка по регионам

Эта диаграмма просто отображает вклад каждого региона в годовую выручку. Поскольку данные основаны на полном годе, эта диаграмма не взаимодействует со срезом. Из-за того, что диаграмма использует данные за весь год, вы не можете скопировать одну из сводных диаграмм Фин_промежут. Любая копия будет использовать тот же кеш, что не позволит вам для одной сводной таблицы выбрать квартал, а для другой – все кварталы. Поэтому вы должны создать новую сводную на основе тех же исходных данных с листа Финансы. Вставьте круговую диаграмму, отформатируйте ее, а затем перенесите на лист Фин_панель (рис. 13).

Рис. 13. Годовая выручка

Навигация

Расположите на панели Финансы синюю кнопку для перехода на панель Продажи. Для этого перейдите на лист Фин_панель. Пройдите по меню Вставка –> Фигуры. Нажмите на фигуру по вашему выбору (я использовал закругленный прямоугольник, рис. 14). Нажмите и перетащите фигуру на лист. Придайте ей желаемый размер.

Рис. 14. Прямоугольник для кнопки перехода

Щелкните на фигуре правой кнопкой мыши. Выберите Изменить текст. Введите На панель Продажи. Отформатируйте текст, разместите его по центру кнопки. Создайте лист Панель_Продажи (если вы собираетесь создать ссылку на него, он должен существовать!). Щелкните правой кнопкой мыши на кнопке, выберите Гиперссылка. В появившемся окне выберите Место в документе, а затем укажите ячейку A1 на листе Панель_продаж (рис. 15).

Рис. 15. Настройка гиперссылки

Сделайте лист Фин_панель визуально более интересным. Больше похожим на лист бумаги, а не на лист Excel. Перейдите на вкладку Вид и снимите галочки с опций Сетка и Заголовки. То, что у вас приблизительно должно получиться изображено в начале заметки на рис. 2.

Панель Продажи

Панель Продажи будет основана на данных с листа Продажи. Чтобы не повторяться, на ней мы отработаем новые функции. Вот, что у вас должно получиться:

Рис. 16. Панель Продажи

Создайте в Excel новый лист, и назовите его Прод_промежут. На нем вы разместите сводные таблицы и иные элементы бизнес-логики. Лист Панель_Продажи уже существует. Вы его создали, когда занимались кнопкой навигации.

Начнем с отчета Продажи по кварталам. Визуально он похож на аналогичный отчет на панели Финансы, но будет реализован с помощью иной техники. Постройте табличку продаж по кварталам на листе Прод_промежут (рис. 17). Строки представляют собой уникальный список продуктов. Формулы в ячейках С3:F8 основаны на функции СУММЕСЛИМН (рис. 17).

Рис. 17. Продажи по кварталам

Для отражения этой таблицы на панели Продажи воспользуемся инструментом Камера (подробнее см. Марк Мур. Динамические диаграммы, раздел Инструмент Камера). Поместите Камеру на панель быстрого доступа. Выберите диапазон В1:F8 на листе Прод_промежут, кликните на Камере, перейдите на лист Панель_Продажи, кликните в любом месте. Появится изображение выделенного диапазона. Прелесть этого изображения заключается в том, что оно изменяется в соответствии с любыми изменениями оригинальной области: чисел, формата, рисунков, сетки… Обратите внимание, если выделить изображение, в строке формул отобразиться ссылка на оригинальную область (рис. 18). Изображение можно перемещать по листу, как единое целое; изменять его размеры. При этом ширина изображения не зависит от ширины столбцов.

Рис. 18. Изображение на листе Панель_Продажи

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

Продажи по категориям

Круговая диаграмма будет показывать данные для одного квартала, выбранного с помощью переключателя (подробнее о последнем см. Марк Мур. Динамические диаграммы, раздел Инструмент Переключатель, Option Button). Создайте таблицу на листе Прод_промежут. В ячейке В12 будет храниться значение, соответствующее выбору Переключателя. Формула в ячейке С12 =»Q»&B12, преобразует выбор Переключателя в номер квартала. В ячейке С15 используется формула: =СУММЕСЛИМН(Продажи!$F:$F;Продажи!$E:$E;»Sales»; Продажи!$D:$D;$C$12;Продажи!$C:$C;$B15)

Рис. 19. Продажи по категориям

Вставьте круговую диаграмму. Свяжите ее название с ячейкой В13, в которой введите формулу =»Продажи за «&C12. Отформатируйте диаграмму, вырежьте ее и вставьте на лист Панель_Продажи.

Добавьте четыре Переключателя. Переименуйте их в Q1, Q2, … Порядок создания и именования имеет значение! Вставьте элемент управления Группа. Переименуйте его – Выберите квартал. Убедитесь, что все переключатели находятся полностью внутри группы (рис. 20).

Рис. 20. Группа охватывает все Переключатели

Щелкните правой кнопкой мыши на любой Переключатель выберите опцию Формат объекта, перейдите на вкладку Элемент управления и установите связь с ячейкой C12 листа Прод_промежут. Поскольку все кнопки являются частью группы, установка одной ссылки на ячейку устанавливает их все. (Не устанавливайте связь с ячейкой для второго Переключателя. Это может сбить его работу.)

Продукты по кварталам

Мы создадим диаграмму, на которой пользователь сможет выбрать продукт и число кварталов. Продукты будут выбираться с помощью выпадающего списка; а кварталы – полосой прокрутки. Данные, на которых основана диаграмма, – это фрагмент данных Выручка по продуктам (см. рис. 8). Разница в том, что сейчас диаграмма отображает лишь один ряд. Вы используете ранее созданную таблицу, и выберете из нее нужные данные на основе значений, возвращаемых элементами управления Выпадающий список и Полоса прокрутки (рис. 21). В двух ячейках (I3 и I5) вы храните значения, выбранные в полосе прокрутки и раскрывающемся списке. Значение в ячейке I7 определяется формулой =ИНДЕКС(B3:B8;I5), а значения в диапазоне J8:M8, формулой =ВПР($I7;$B$3:$F$8;СТОЛБЕЦ()-8;ЛОЖЬ)

Рис. 21. Бизнес-логика линейного графика

Создайте элемент управления Поле со списком на листе Панель_Продажи. Сформируйте список по диапазону Прод_промежут!$B$3:$B$8. Установите ссылку на ячейку Прод_промежут!$I$5. Укажите количество строк в списке 6 (рис. 22).

Рис. 22. Формат Поля со списком

Создайте горизонтальную полосу прокрутки на листе Панель_Продажи (рис. 23).

Рис. 23. Формат горизонтальной полосы прокрутки

На листе Прод_промежут выделите диапазон I6:M7. Вставьте линейный график. Для того, чтобы график использовал только кварталы, указанные в ячейке I3, создайте именованный диапазон ДанныеГрафика (пробелы в имени не допускаются). В поле Диапазон введите формулу =СМЕЩ(Прод_промежут!$J$7;;;1;Прод_промежут!$I$3), как на рис. 24.

Рис. 24. Именованный диапазон

Измените данные для линейного графика, чтобы он использовал в качестве значений не диапазон I7:M7, а именованный диапазон. Для этого кликните на графике правой кнопкой мыши, откройте Выбрать данные. В окне Выбор источника данных выберите ряд и кликните Изменить. В поле Значение укажите имя диапазона; предварите его именем листа (рис. 25).

Рис. 25. Теперь график ссылается на именованный диапазон

Протестируйте график, вводя значения от 1 до 4 в ячейку J3. Вырежьте диаграмму и вставьте ее на лист Панель_продажи. Над полосой прокрутки добавьте Надпись с инструкциями для пользователя. То, что очевидно для вас, как разработчика, может не быть очевидным для других пользователей. Небольшой трюк. Обычно Надпись содержит текст, но можно вставить в нее и формулу. Пройдите по меню Вставка –> Надпись. Нарисуйте прямоугольник Надписи над полосой прокрутки. Дважды кликните на границе Надписи. В строку формул введите ссылку на ячейку I9 листа Прод_промежут. Введите в ячейку I9 текст с указаниями для пользователя.

Добавьте заголовок панели, вставьте кнопку На панель Финансы, свяжите ее гиперссылкой с ячейкой А1 листа Фин_панель. Можете скрыть листы с исходными данными и промежуточными вычислениями, оставив только две панели.


Прокомментировать