Дашборд что это excel

Дашборд как интерактивная альтернатива табличным отчетам

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

В этой статье мы рассмотрим дашборд: что это, кто его применяет и как правильно внедрить эту систему в компанию.

Содержание

Что такое дашборд

Дашборд — это интерактивная панель с важной информацией, сгруппированной на одном экране. В переводе с английского dashboard — это приборная панель.

Дашбордом являются: показатели скорости и расхода бензина в автомобиле, отчет продаж в CRM, описание задач сотрудника в программе планирования.

В бизнесе дашбордом называют системы, которые не только показывают данные, но и способны анализировать их. В этой статье мы рассмотрим дашборды, которые помогут сделать бизнес-процессы в компании проще и структурированней.

Например, нужно подсчитать ROI. Вы можете собрать отчет в Excel, а можете визуализировать его в дашборде — и сервис автоматически просчитает итоговый показатель.

Пример анализа ROI в системе Calltouch. Источник

Дашборд можно применять в любой сфере бизнеса:

Зачем нужен дашборд

Дашборд — это по сути инфографика по отчетам, которая показывает актуальную и объективную информацию по тем или иным сведениям. С его помощью в одну систему подтягивают показатели из систем аналитики, таблиц, CRM, социальных сетей и баз данных. Благодаря этой сведенной информации можно улучшить бизнес-процессы в компании и прогнозировать ее развитие.

Цели дашборда:

Таким образом, суть дашборда — создать простую систему из сложных и разрозненных отчетов и данных.

Если вы еще не используете дашборды в своем бизнесе, скорее всего, тратите более 50% рабочего времени на заполнение таблиц и отчетов и не успеваете при этом выполнять основные задачи.

Пример из жизни. Компания по производству мебели взяла на работу логиста. Его работа заключается в том, чтобы контролировать процесс доставки товара и быть посредником между водителем и клиентом. До того, как внедрили CRM-систему, которая автоматически подтягивала данные о водителях и машинах, логист тратил 80% времени на заполнение данных по каждому клиенту. Из-за этого случались частые ошибки, а как следствие негатив со стороны покупателей, ведь в случае опоздания машины логист не предупреждала клиентов. Далее процесс влиял на весь бизнес: частые возвраты, игнорирование звонков, итог — убытки до 300 000 рублей в месяц.

Чем дашборд отличается от отчета

Аналитики утверждают, что любой дашборд — это отчет, но не каждый отчет — это дашборд. В этом разделе рассмотрим отличия дашборда от отчета и выясним, почему их путают.

Некоторые руководители не понимают, в чем разница отчетов и дашбордов — ведь это такие же данные, только красиво оформленные. Поэтому и происходит путаница.

Так в чем же разница:

Ниже представлена таблица отличий дашборда от отчета:

Преимущества дашборда

А теперь давайте рассмотрим основные преимущества дашборда, которые значительно упрощают ведение отчетности в компании.

Автоматизация и оптимизация данных

Настройка отчетов помогает каждый день видеть готовые расчеты основных показателей. А если нужно изменить график или добавить дополнительные данные — вы с легкостью можете вставить модуль, в котором будут актуальные показатели.

Анализ данных в реальном времени

Руководителям не нужно постоянно спрашивать у вас отчеты, все это они могут отслеживать в дашборде в режиме реального времени: данные о посетителях сайта, бюджет на рекламные кампании, количество клиентов и тому подобное.

Пример. Настройка автоматического подтягивания бюджетов по рекламным кампаниям в Google Data Studio позволила маркетологу приезжать на работу на час позже, а также сберегло нервы генерального директора и руководителя команды. В 8:00 часов утра они видели расход за предыдущий день и могли быстро скорректировать задачи сотрудников.

Основные показатели находятся на одном экране

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

Из чего состоит дашборд

Дашборд делится на несколько типов отчетов:

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

Хедер

Шапка дашборда, или название отчета. Любой человек, увидевший дашборд, должен сразу понять, какие данные в нем. Здесь также размещают логотип компании или ее название.

Пример доски данных по финансовым метрикам. В левом верхнем углу — название дашборда, а под ним — логотип компании. На самой панели отражены основные показатели и динамика по каждому департаменту.

Дашборд по финансовым метрикам. Источник

Модули дашборда

Модули дашборда — это наполнение отчета: графики, диаграммы, таблицы, показатели, карты и многое другое.

График

Отражает динамику показателя: рост или снижение.

Пример отображения графиков в дашборде — с 2012 года наблюдается рост ВВП. В этом отчете вы можете видеть реальные показатели до 2020 года и прогноз до 2024 года.

Дашборд ВВП Китая — демо-отчет в сервисе Stimulsoft

Диаграмма

Диаграмма подходит для представления изменений показателей по разным категориям.

На представленном выше скриншоте рядом с графиком вы можете увидеть комбинированную диаграмму — «Доля отрасли в ВВП». Она включает график и гистограмму, отражает динамику и сравнение данных.

А в этом примере есть несколько видов диаграмм:

Ниже — другие виды диаграмм:

Таблица

В таблице можно собрать любые данные в формате названий и числовых показателей.

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

Дашборд мониторинга конкурентов. Источник

Карта

Карта показывает важные участки продаж или, как на примере ниже, количество заражений коронавирусом, выздоровлений и смертей в мире.

Дашборд в Yandex DataLens. Источник

Индикатор, или ключевые показатели

Ключевые показатели отображают числовое значение с возможностью отслеживать его прирост или снижение.

А здесь приведен пример дашборда с показателями посещаемости: количество пользователей, переходов, просмотров страниц и доля возвратов.

Дашборд по посещаемости

Футер

В подвале дашборда приводят выводы и краткую информацию по значениям данных, если какие-то показатели указаны сокращенно. Это необязательная часть, но если вы отправляете отчет руководителю или клиенту, лучше сделать расшифровки ключевых значений.

Виды дашбордов

Этот раздел посвящен трем главным видам дашбордов, которые используются на практике.

Дашборд для управления процессами

Его используют для отслеживания бизнес-процессов: коммерческие задачи и цели. Очень удобен для руководителей и аналитиков, так как показывает всю динамику, помогает оптимизировать процессы и избежать убытков.

Пример дашборда в Excel «Бюджет доходов и расходов». В нем показана чистая и валовая прибыль, выручка и расходы, а также динамика и сравнение этих показателей.

Дашборд в Excel. Источник

Дашборд для мониторинга рекламных кампаний

Дашборд используется маркетологами, директологами, SMM-специалистами и таргетологами для отслеживания показателей рекламных кампаний. Обычно используют «Яндекс.Метрику», Google Analytics. Для сквозной аналитики подключают Roistat, K50 и другие сервисы.

На примере ниже показан дашборд в «Яндекс.Метрике». В нем использованы стандартные модули, установленные по умолчанию:

Дашборд для отслеживания состояния

Отчет, отслеживающий какой-либо процесс в компании: отдел продаж, доставки, бухгалтерии или HR. Сюда можно, например, отнести трекеры задач для сотрудников.

Ниже показан пример дашборда для отдела кадров — перемещение сотрудников между отделами и их текучесть.

Дашборд для отдела кадров. Источник

Откройте для себя чат-бота

Выстраивайте автоворонки продаж и отвечайте на вопросы пользователей с помощью чат-бота в Facebook, VK и Telegram.

Как работать с дашбордами

Вы увидели несколько примеров дашбордов в нашей статье, но как работать с ними и как применить знания на практике? Давайте разберемся в том, как правильно собрать свой первый дашборд.

Указывайте только важные показатели

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

Создавайте простую и понятную структуру

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

Создавайте модули таким образом, чтобы каждый элемент был пропорционален и гармоничен с остальными.

Сравнивайте показатели

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

Добавьте настройку фильтров и модулей

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

На примере ниже показан модуль «Посетители» в дашборде «Яндекс.Метрика» и его варианты настройки.

Пример модуля в дашборде

Выбирайте наилучшую визуализацию

В случае с дашбордами, созданными через Excel, нет такого большого выбора для визуализации, какой предоставляют, к примеру, Power Bi или Tableau — о них поговорим в следующих разделах. Когда создадите отчет, попробуйте сделать цветовой акцент на основных показателях или колонках в диаграммах так, чтобы любой пользователь понимал, куда нужно посмотреть и на что обратить внимание.

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

Придерживайтесь минимализма

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

Задавайте вопросы

Если вы видите в показателях резкие изменения — узнайте, в чем причина. Если дашборд не отвечает на ваш вопрос, изучите составляющие модуля, где была ошибка. Откройте источники данных дашборда и просмотрите значения.

Как создать дашборд

А сейчас перейдем к этапам создания дашборда и разберем системы, в которых можно это сделать.

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

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

Определите источники данных

У каждого отдела в компании есть свой источник данных: CRM-система, сервис аналитики, бухгалтерские отчеты в 1C, сервисы доставки и так далее. Выделите основные источники для каждого отдела и уточните, откуда еще берутся показатели. Возможно, нужно будет привязать внешние ресурсы: котировки валют, данные по конкурентам, отчетность по демографическим признакам в вашем городе и многое другое.

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

Выберите систему, где будете создавать дашборд

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

Google Sheets

Google Sheets — сервис с возможностью подключения пользователей и редактирования данных в режиме реального времени. Удобный и простой инструмент для командной работы.

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

Клоны Microsoft Office Excel:

Ниже показан пример дашборда для расчет месячного бюджета.

Пример дашборда в Google Sheets

Плюсы:

Минусы:

«Яндекс.Метрика»

«Яндекс.Метрика» — бесплатный онлайн-сервис для веб-аналитики от разработчиков «Яндекс».

С помощью сервиса «Яндекс.Метрика» можно отслеживать объем трафика, источники переходов, конверсии, поведение пользователей, получать данные по целевой аудитории, составлять отчеты по тем показателям, которые у вас в приоритете.

Особенности:

Плюсы:

Минусы:

Google Analytics

Google Analytics — бесплатная система веб-аналитики от Google, которая позволяет отслеживать данные по трафику на сайте или в приложении.

Особенности:

Пример дашборда по посещаемости и каналам перехода:

Дашборд, созданный в Google Analytics

Плюсы:

Минусы:

Google Data Studio

Google Data Studio — бесплатный инструмент визуализации от Google.

Особенности:

Плюсы:

Минусы:

Qlik — Bi-платформа с большим количеством возможностей. В сервисе есть две системы: для корпоративного пользования — QlikView, и для персонального исследования данных — Qlik Sense.

Особенности:

Ниже представлен пример динамики продаж в странах за разные промежутки времени:

Пример дашборда в системе Qlik Sense

Плюсы:

Минусы:

Tableau

Tableau — система, которая подходит для всестороннего анализа данных. Сервис имеет несколько приложений:

Особенности:

Плюсы:

Минусы:

Power Bi

Power Bi — инструмент от Microsoft для анализа любых данных. Построен на языке R. Для отслеживания всех процессов в компании потребуется помощь программиста.

Ниже представлена рабочая среда Power Bi:

Рабочая среда Power Bi Desktop

Особенности:

Вот пример того, как может выглядеть отчет в Power Bi. Интерактивный дашборд с использованием иконок. Здесь показан общий обзор бизнеса: план продаж, сделки и их стоимость.

Пример дашборда в Power Bi. Источник

Плюсы:

Минусы:

Owox Bi

Owox Bi — система, похожая на Power Bi, но позволяющая обойтись без помощи программиста.

Особенности:

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

Пример дашборда Owox Bi

Плюсы:

Минусы:

Примеры дашбордов

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

Дашборд помогает понять, как три фактора влияют друг на друга и какие проблемы могут возникнуть.

Дашборд по производству. Источник

Пример дашборда для отдела продаж. Здесь использованы диаграммы, таблицы и графики.

В разделе «Основные KPI» показано количество открытых и закрытых сделок, план продаж, а также основные маркетинговые показатели: конверсия, среднее время, средний чек.

Справа расположены блоки, которые отвечают на главный вопрос «Как улучшить бизнес-процесс». Для руководителя такой подход будет удобен тем, что он видит названия в виде вопросов и сразу может найти ответ, изучив показатели в модуле.

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

Пример дашборда. Источник

Заключение

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

Дашборд — это удобный и многофункциональный инструмент для отслеживания и анализа данных. Он подойдет любому бизнесу и значительно сократит время на создание отчетов. Автоматизация процессов поможет сфокусироваться на более важных задачах и целях в вашей компании!

А чтобы наблюдать за актуальными и точными данными по рассылкам — регистрируйтесь в SendPulse и подключайте email канал, web push, Viber и SMS рассылки и чат-боты в мессенджерах Telegram, Facebook Messenger и ВКонтакте!

Источник

Марк Мур. Дашборды в 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):

Рис. 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 листа Фин_панель. Можете скрыть листы с исходными данными и промежуточными вычислениями, оставив только две панели.

Источник