дашборд в excel что это
Дашборд — что это и почему он будет вам полезен или современный способ сделать тайное явным
Наверное, мало кто из нас задумывался, что практически с рождения пользовался дашбордами. Мы получали некую информацию, анализировали, принимали решение или даже испытывали какие-то эмоции благодаря им. Да-да, градусник, измеряющий температуру, когда вы болели; часы; стрелка спидометра, перевалившая за 200 км/час (ну, это может быть не у всех) — все эти приборы по сути являются дашбордами или их элементом. Но мне бы хотелось рассказать об интерактивных аналитических дашбордах. И, самое главное — показать, что в наше время такие дашборды могут быть полезны каждому человеку, а не только крупным банкам или корпорациям.
Если у вас есть данные — не важно, домохозяйка вы с пачкой чеков от закупок продуктов, спортсмен с данными о пробежках из Strava или кто-либо ещё — вы сможете представить это наглядно, оценить важные показатели, в результате чего принимать более оптимальные решения.
Вы не используете дашборды и думаете, что вам это не нужно? Мнение может поменяться, а кругозор расширится, так как далее: что такое дашборды, какие цели достигаются с помощью них, ключевые понятия и сферы использования, существующие инструменты, множество ссылок на актуальные ресурсы по теме, а также реальный пример, как из обычных на первый взгляд данных, можно извлечь интересные знания…
Возникновение и значение термина
Историческая справка
Большинство специалистов ведут отсчёт истории визуализации данных с графиков движения небесных тел (кто-то может поспорить, указав на древние наскальные рисунки). Так, Howard Funkhouser обнаружил диаграмму движения небесных тел, датированную 10’ым веком, которая считается одним из первых графических изображений данных.
Michael Friendly разделяет историю визуализации данных на несколько периодов:
до 17 века – ранние карты и диаграммы;
1600-1699 – измерения, теории, идеи;
1700-1799 – новые графические формы;
1800-1850 – начала современной графики;
1850-1900 – золотая эпоха статистической графики;
1900-1950 – современные тёмные времена;
1950-1975 – перерождение визуализации информации;
1975-наше время – высокоточная, интерактивная и динамическая визуализация.
Возрождение визуализации информации в 50’ые годы возможно связано с окончанием Второй Мировой Войны, когда особенно обострилась необходимость улучшать экономическое состояние стран-участников. В те годы также зародились такие термины как анализ данных и Business Intelligence (BI). В современном понимании понятие анализа данных в 1961 году дал известный статистик Джон Тьюки, которому мы благодарны ещё и за введение слова “бит” (binary digit). В своих работах он также указывал на необходимость визуализации статистической информации.
Термин Business Intelligence впервые использовал в своей статье 1958 года сотрудник корпорации IBM Ханс Питер Лан. В нынешнем употреблении, как “концепции и методы для улучшения принятия бизнес-решений и бизнес-анализа”, это понятие сформировалось при развитии систем поддержки принятия решений в середине 80х, тогда же и возникли первые идеи цифровых дашбордов.
Манифест пользователей дашбордов
Дашборд — это интерфейс между аналитическим движком и тем, кто выступает в роли пользователя-аналитика. Таким образом, к дашборду применимы все принципы построения интерфейсов, методы улучшения UX и повышения Usability.
О принципах визуализации данных
Как говорит известный эксперт по дашбордам Стефан Фью: “лучшее программное обеспечение для анализа данных то, при использовании которого вы забываете о нём самом — это настолько естественное продолжение вашего мышления, что вы можете использовать его, не думая о механике процесса”. Он сформулировал принципы интерактивной визуализации, как для разработчиков инструментов, так и для создателей дашбордов [это вольный перевод, прочтите оригинал]:
1) упрощайте — уловите суть и покажите проще;
2) сравнивайте — покажите необходимые визуализации рядом;
3) сопровождайте — облегчите доступ к важным данным;
4) исследуйте — позвольте посмотреть и найти новые знания;
5) смотрите иначе — дайте разные представления одних и тех же данных, что породит различные идеи;
6) спрашивайте “почему?” — больше, чем «что происходит», важно дать понять «почему это происходит», как возник тот или иной результат действий;
7) будьте скептиками — дайте возможность задавать больше вопросов и сразу получать ответ на них;
8) откликайтесь — не просто отвечайте на вопросы, предоставьте средства, чтобы делиться знаниями.
Где звучит выражение “визуализация данных”, обязательно упоминается Эдвард Тафти — гуру визуализации, которого The New York Times называет “Леонардо Да Винчи данных”, а Bloomberg «Галилео графики». Он придумал искрографики (sparklines) и написал несколько популярных книг по визуализации. Наиболее известны два его фундаментальных принципа визуализации, очень важных для дашбордов:
1) высокое соотношение графики для данных к общему занимаемому ею месту (data-ink ratio) — увеличьте количество полезного изображения на занимаемом визуализацией пространстве, т.е. максимизируйте отображение основной информации, тратьте больше “чернил” на данные;
2) отсутствие графического мусора (chartjunk) — исключайте не важные графические элементы, не отвлекайте от данных лишним дизайном.
За счёт чего достигается наглядность?
Аналитические данные показываются разными виджетами от таблицы и диаграммы до стрелочных индикаторов. Некоторые инструменты позволяют программисту самому реализовать требуемую визуализацию вплоть до анимаций, видео или произвольной инфографики. Сами аналитические данные представляют из себя сгруппированные и агрегированные исходные данные. Есть возможность применить фильтры и сортировки на разных уровнях, отсекать данные по топовым значениям, создавать вычисляемые поля практически любой сложности.
Основные понятия аналитических дашбордов
1) группировка — способ объединения схожих данных (по какому-то общему признаку, например по первой букве слова или имени человека);
2) агрегация (сумма, минимум, максимум, количество и т.д.) — способ отображения колонки фактов из исходной базы данных (например уникальное количество посетителей сайта, или сумма расходов на продукты);
3) сортировка — упорядочивание уже сгруппированных данных по заданному признаку (кроме алфавита, можно отсортировать фамилии менеджеров по их наибольшим продажам за месяц и т.п.);
4) фильтрация — исключение данных по заданному признаку или сложной формуле;
5) вычисляемая колонка — способ получения новых данных и знаний с использованием методов работы с датами, строками, математических функций (например отображение имени и фамилии, вычисление возраста согласно дате рождения и текущей дате);
6) топовые (лучшие) значения — способ отобразить указанное количество максимальных или минимальных значений данной группировки (например, возраст трёх самых молодых сотрудников крупной компании, или пять менеджеров, обеспечивающих максимальные продажи);
7) виджеты (таблицы, диаграммы, карты и т.п.) — собственно способ визуализации вышеуказанных понятий.
Любые ли данные можно анализировать?
Дашборды, как правило, позволяют подключаться к обширному списку источников данных, начиная от Excel-файла и заканчивая многомиллионными источниками больших данных BigData или веб-сервисам социальных сетей.
Часто заранее подготавливается специализированный для аналитики источник, именуемый Data Warehouse или многомерный OLAP-куб. Это делается в случаях, когда запрос данных к исходному источнику требует большого количества времени или запрос перегружает сервер, а это недопустимо.
Какие средства существуют
Excel… да, именно MS Excel для многих являлся (да и является!) основным средством анализа данных. MS Office достаточно недорогой продукт (а в России до недавнего времени активного пиратства — для многих бесплатный), он доступен даже студенту и в общем-то предоставляет базовый набор средств анализа и возможностей по написанию своих собственных скриптов.
Желающим внедрить в свой рабочий процесс весь спектр функциональности дашбордов (и других инструментов BI) достаточно посмотреть на Gartner Magic Quadrant:
чтобы выбрать продукт от одного из лидеров рынка, которые предоставляют мощные средства для анализа данных, как правило как конечные решения с довольно высокой стоимостью.
Исследовательская компания Gartner является наиболее авторитетным изданием, публикующим анализ какого-либо сегмента рынка, — им можно доверять.
Также многие из них — QlikSense, Tableau Public, Sisense, MicroSoft Power BI и т.п., — предоставляют публичные сервисы или бесплатные версии для создания дашбордов, обычно с серьёзными ограничениями для использования в реальном бизнесе, но достаточно мощных для персональных/некоммерческих целей.
Отдельно следует сказать про компании, желающие внедрить аналитические панели непосредственно в свои внутренние программные продукты. Для таких компаний более предпочтительно приобретать готовый набор библиотек (контролов) для создания дашбордов. Более того, многие компании, возможно и ваша, уже имеют такой набор в составе пакетов для своих офисных приложений и даже не осознают, что в считанные минуты могут внедрить у себя мощный инструмент бизнес-анализа. Библиотеки компонентов предназначены именно для внедрения в собственный программный продукт компаний согласно платформе (Desktop/Web). Кроме меньшей стоимости, они имеют гибкие условия лицензирования, т.к. приобретаются на одного разработчика и могут впоследствии использоваться неограниченным количеством конечных пользователей. Существует множество open source библиотек (которые можно найти, например, на GitHub), однако они обычно позволяют решать лишь базовые задачи анализа данных.
Как понять, что данный производитель дашборда вам подходит
Обычно каждый производитель средств BI имеет демонстрационные версии дашбордов. Посмотрев демонстрационные версии на сайте производителя, можно первоначально оценить, насколько они покрывают требуемый функционал.
1) продажи — в каком регионе продаётся лучше тот или иной продукт, какой из филиалов или какой из менеджеров компании даёт лучший результат, как изменились продажи по сравнению с прошлым годом и т.п.;
2) финансы — котировки акций, курсы валют… как влияет курс нефти на курс доллара всем известно, а вот какое влияние на курс той или иной акции оказывает например беспорядки в Сирии, можно наглядно увидеть, сделав соответствующий аналитический дашборд;
3) отдел кадров — специалисты могут на одном графике оценить как влияет количество курсов повышения квалификации или уровень зарплаты на текучесть кадров;
4) здравоохранение — распространение заболеваний по регионам, влияние погоды на распространение вирусов, влияние ужесточения политики продаж алкоголя и табака на продолжительность жизни т.п.;
5) промышленность (лёгкая, тяжёлая, электроника и т.п.) — отображение перспективных регионов по видам промышленности, прирост добычи газа или нефти после установки нового оборудования и т.п.
Список можно продолжать ещё, но обычно эти базовые демо есть у всех, что позволит вам сравнить уже на сайте производителя, а также возможно увидеть решение схожих вашим задач.
Пример построения и использования дашборда
Ещё раз подчеркну — данные есть в любой сфере деятельности. А если есть данные, то их можно проанализировать. Используя дашборды, можно извлечь новые знания и улучшить показатели или даже просто ежедневно смотреть на эти данные “под другим углом”, благодаря наглядному представлению и интерактивной аналитике.
В качестве примера я решил рассмотреть что-нибудь достаточно актуальное, простое и общедоступное, при этом не совсем банальное. По стечению обстоятельств, не так давно мне были интересны подробности избрания президентов США, в частности кто был самым молодым и кто самым возрастным президентом. Я столкнулся со списком американских президентов, который и решил использовать как источник данных для моего примера (я лишь дополнил его датами жизни и местом рождения президента).
Для простейшего дашборда я использовал несколько стандартных виджетов: карта по штатам; круговая диаграмма по партийной принадлежности; таблица по президентам со ссылками на Wikipedia и отметками по условиям с цветовой раскраской по срокам президентства; древовидная диаграмма для количества выходцев из определённых штатов и карточки для некоторых краевых показателей. Некоторые виджеты я сделал интерактивными — на них можно кликнуть и тем самым отфильтровать другие согласно выбранному показателю. Если поменять исходную таблицу данных, например внести данные о новом президенте — все показатели пересчитаются автоматически.
Здесь вы можете попробовать пример вживую, поменять или создать свои дашборды на данной таблице данных, сохранить их во временной сессии или скачать результирующий xml дашборда (также существует отдельный режим просмотра или вы можете скачать проект):
Вот уже более семи лет разрабатывая продукты компании DevExpress для Business Intelligence, я решил выступить также пользователем и использовать набор компонентов для создания дашбордов DevExpress Web Dashboard, для чего я:
1) скачал пакет Universal;
2) создал ASP.NET MVC приложение, используя готовый пример как основу;
3) подключился к базе данных, в моём случае Excel-таблице с данными о президентах (я реализовал свой вариант хранилища дашбордов, чтобы вы могли сохранять их во временной сессии);
4) запустил получившееся приложение и создал новый дашборд;
5) сделал вычисляемые поля (возраст вступления в должность в годах и сроки президентства в днях, вспомогательные для условного форматирования);
6) создал виджеты:
— карту по штатам, раскрасив Штаты по количеству рождённых там президентов;
— таблицу и добавил условное форматирование: цветовое по времени правления, жирный шрифт для фамилий ныне живущих президентов, иконки для дат рождения/смерти равных Дню Независимости;
— круговую диаграмму по партийной принадлежности и включил интерактивную фильтрацию: по клику на сегменте, карта и таблица показывают отфильтрованные данные;
— древовидную диаграмму также по Штатам и по количеству рождённых там президентов, я специально хотел показать отличия в визуализации одних и тех же данных;
— карточки для отображения важных показателей, используя топовые значения.
Заключение
Последние годы, в силу развития цифровых и программных технологий во всех областях человеческой жизнедеятельности, дашборды находят своё применение в новых сферах, такие как, например, социальные сети, приложения для фитнеса и т.п. Уже сегодня smart-часы показывают дашборды, не удивительно, если завтра на вашем холодильнике будет показан интерактивный дашборд с отметкой, кто чаще всего открывает холодильник и рекомендациями по употреблению продуктов разным членам семьи.
Как создать Дашборд в Excel
Знаю, что хорошо заходят посты, имеющие определённое количество текста/картинок. Тем не менее, создание дэшбордов в Excel – эта такая тема, которую уместить в несколько картинок и листов текста ну очень сложно! А поскольку поделиться еще одним из вариантов создания дэшбордов всё-таки очень хочется, решил поделиться в этом посте информацией в формате видео:
Что рассмотрено в видео:
• как создавать интерактивные дэшборды в Excel со сводными и не только диаграммами на основе сводных таблиц
• оптимальная структура рабочей книги для дэшборда
• подход в создании дэшбордов для их долгосрочного использования и развития (разработка отдельных деталей дэшборда на отдельных листах)
MS, Libreoffice & Google docs
469 постов 12.8K подписчика
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Дашборд в Эксель, это звучит ужасно:(
lastRow = Cells(Rows.Count, iColumn).End(xlUp).Row
For iRow = 4 To lastRow
strValue = Cells(iRow, iColumn)
Cells(iRow, iColumn) = strValue
Спасибо! Очень познавательно, подписалась.
@VBA.Excel, делаю графики по вашему видео. У меня почему то при перетягивании поля с датой в строки сводной таблицы не поялвяются года. Только месяца. Можно их как-то руками чтоли добавить?
Отличная подача информации.
Расскажи пожалуйста, как ты подходишь к записи роликов?
Написания сценария, потом запись или сразу запись?
запись видео потом озвучка или все за раз?
Спасибо! Весьма познавательно.
Ну, с другой стороны с собаками не спят, хотя, нет, это все равно хуже.
Покажите мне в экселе, пжст, то, что служит для их ПРАВИЛЬНОГО ВВОДА, а потом уже и о выводе поговорим (нет)
А можно вас попросить написать в названии поста (в скобочках), что такое этот дашборд?
Ну, с другой стороны с собаками не спят, хотя, нет, это все равно хуже.
Самые яркие воспоминания из детства
Старшему сыну 10 лет, спрашиваю:
— какое твое самое яркое воспоминание из прошлого.
— (вспоминал минуту) помню как по дороге из садика мы сидели на пенёчке
— ну да, как в сказке, сидели отдыхали, болтали.
Спрашиваю это же у младшего, ему 6:
— помню как приезжал трактор убирать снег, и ты попросил его меня покатать.
Да уж. Аквапарки, аттракционы, крутые игрушки, дедморозы на НГ, а самые яркие у них трактор и пенёчек.
Решил вспомнить самое яркое из своего детства. В общественной бане потерял фигурку водолаза, разревелся, искали всей баней. Не нашли.
Детский рассудок
Мечта сбылась
Недавно разбирал древние раритеты, нашел свой школьный аттестат, детские рисунки. И эту тетрадь. Хорошая тетрадь, сейчас таких нет, вырвал аккуратно первый лист. Тетрадь отличная.
Здесь прекрасно всё.
Аж чуть не прослезился.
Вспомнилось, ночь пакет 100руб, запах прокуренного зала, мышки с шариками, пень 4й, Варкрафт 3, Старкрафт.
Лица гор без национальности
Мерч представителей ЛГБН сообщества
Про тех кто хочет много зарабатывать, а вокруг одни 3,14расы
Оказалось ещё и пьет как не в себя, нашел склад пушнины в цеху. Дал пиздюлей.
Начинаем большой заказ. Сварщик работает. Почти не пьёт. Но при этом пораньше уходит, всегда дела какие-то, пару дней проеба. Получает 25к за 10 дней. Исчезает. Через его жену ищу. А она говорит: уехал вахтой в другой город, а хуле у вас работать за 15 дней 5к всего. А нам ипотеку платить надо было! А он сутками ебашит, дома не ночует!
Короче где-то он бухал походу, блядовал, жене сунул 5к, а я в итоге пидорас.
Марк Мур. Дашборды в 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 листа Фин_панель. Можете скрыть листы с исходными данными и промежуточными вычислениями, оставив только две панели.