Как создать Дашборд в Excel
Знаю, что хорошо заходят посты, имеющие определённое количество текста/картинок. Тем не менее, создание дэшбордов в Excel – эта такая тема, которую уместить в несколько картинок и листов текста ну очень сложно! А поскольку поделиться еще одним из вариантов создания дэшбордов всё-таки очень хочется, решил поделиться в этом посте информацией в формате видео:
Что рассмотрено в видео:
• как создавать интерактивные дэшборды в Excel со сводными и не только диаграммами на основе сводных таблиц
• оптимальная структура рабочей книги для дэшборда
• подход в создании дэшбордов для их долгосрочного использования и развития (разработка отдельных деталей дэшборда на отдельных листах)
MS, Libreoffice & Google docs
458 постов 12.7K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Дашборд в Эксель, это звучит ужасно:(
Отличная подача информации.
Расскажи пожалуйста, как ты подходишь к записи роликов?
Написания сценария, потом запись или сразу запись?
запись видео потом озвучка или все за раз?
Спасибо! Очень познавательно, подписалась.
@VBA.Excel, делаю графики по вашему видео. У меня почему то при перетягивании поля с датой в строки сводной таблицы не поялвяются года. Только месяца. Можно их как-то руками чтоли добавить?
lastRow = Cells(Rows.Count, iColumn).End(xlUp).Row
For iRow = 4 To lastRow
strValue = Cells(iRow, iColumn)
Cells(iRow, iColumn) = strValue
Спасибо! Весьма познавательно.
Ну, с другой стороны с собаками не спят, хотя, нет, это все равно хуже.
Покажите мне в экселе, пжст, то, что служит для их ПРАВИЛЬНОГО ВВОДА, а потом уже и о выводе поговорим (нет)
А можно вас попросить написать в названии поста (в скобочках), что такое этот дашборд?
Ну, с другой стороны с собаками не спят, хотя, нет, это все равно хуже.
— А ты почему не работаешь!?
Год назад
Ответ на пост «Куплю квартиру в Вашем доме. »
Герои среди нас
Домой, в Белгород, милиционер вернулся только через год. И как обещал до командировки, первым делом женился.
Пример как сделать простой дашборд в Excel скачать шаблон
Простой, но полезный дашборд в Excel для интерактивной визуализации данных отчета по показателям: уровень обслуживания, качество и производительность. С помощью этой управляемой панели отчета можно продемонстрировать, как визуализировать свой небольшой набор данных с помощью интерактивных элементов.
Создание дашбордов в Excel шаг за шагом
Так будет выглядеть готовый результат построения дашборда в Excel с возможностью интерактивного взаимодействия отчета с пользователем.
Чтобы создать свой такой же или подобный визуальный отчет в виде дашборда следует выполнить ряд последовательных действий в Excel.
В первую очередь создадим новую книгу с 3-ма листами:
Сначала создадим табличку с входящими данными на листе «Данные» так как показано ниже на рисунке:
После чего на листе «Дашборд» создадим первый управляющий элемент – выпадающий список. В данном случае рационально использовать поле со списком, так как оно имеет больше настроек. Конечно можно было бы воспользоваться стандартным выпадающим списком в Excel выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных: Список». Но мы так делать не будем, так как он неудобен из-за своей боковой полосы прокрутки, которая появляется уже при 10-ти значений. А у нас в выпадающем списке должны отображаться 12 месяцев. Поэтому выберите другой инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком».
После выбора инструмента рисуем прямоугольник размером на полторы ячейки и получился выпадающий список.
Теперь нам необходимо его настроить. Щелкаем правой кнопкой мышки по выпадающему списку и из появившегося контекстного меню выбираем опцию: «Формат объекта». После чего появилось окно «Формат элементов управления», которое следует заполнить параметрами так как показано ниже на рисунке:
Как видно из параметров данный выпадающий список в данном примере настраивается 3-мя параметрами на вкладке «Элемент управления»:
Готовый желаемый результат выглядит так:
Далее начинаем упорно работать с 3-тим листом «Обработка». На данном листе обрабатываются и подготавливаются все данные для вывода на дашборд. Будем двигаться с верху вниз. Сначала подготовим данные для верхних подписей. Для этого создаем табличку выборки показателей при условии полученного номера месяца, переданного выпадающим списком на лист «Обработка» в ячейку A1. В ячейке A2 определяем название месяца на основе полученного числа в ячейке A1, по формуле:
Делаем выборку из входящей таблицы на листе «Данные» для всех показателей с помощью функции =ВПР() скопировав формулу во все остальные ячейки:
Данные для верхних подписей показателей – подготовлены!
Как сделать вафельный график в Excel
Подготавливаем значения для графического вывода информации на верхних вафельных графиках дашборда. Сначала делаем данные для вафельной диаграммы по показателю «Уровень обслуживания».
В первую очередь нам необходимо преобразовать процентное значение в числовое сохраняя самое значение числа перед знаком %. Естественно для этого нужно умножить его на 100, но для предотвращения ошибок и простоты отображения на графике мы еще и округлим значение данного показателя до целого числа:
Теперь в ячейку G1 вводим число 0, а целый диапазон ячеек G2:P11 заполняем формулой:
=$F$1;»»;1)’ > 
Диапазон G2:P11 состоит из 100 ячеек (10×10) и 100 единиц – соответственно. В каждой ячейке формула, которая проверяет количество единиц в диапазоне. Если оно больше или равно числу (процентов) в ячейке F1 значит следует прекратить заполнять данный диапазон единицами. Как видно, пока-что формула не работает, так как ей не хватает значений в диапазоне H1:P1, к которым она также обращается. В этом диапазоне будут вычисляться итоговые суммы чисел для подсчета количества единиц из предыдущих столбцов с помощью формулы, которую копируем во все ячейки диапазона H1:P1:
Теперь как видно все работает и диапазон ячеек G2:P11 заполняется единицами по условию, в зависимости от числового значения в ячейке F1.
Вафельный график будет состоять из двух слоев динамического (переднего плана – желтый цвет) и статического (задний план – черный цвет). Мы составили динамически изменяемые данные для первого желтого графика. Нам нужно еще создать черный статический график, который послужит задним фоном. Для этого понадобится диапазон размером 10×10 ячеек которые просто статически заполнены единицами. Поэтому рядом заполняем диапазон ячеек R2:AA11 единицами и строим по ним статический по такому же принципу, как и предыдущий – динамический.
Сначала создадим черный статический график. Для этого выполним ряд последовательных действий:
Черный график для фона готов! Теперь создадим динамический желтый, но сначала следует временно изменить значение 50% на 100% в таблице входящих данных (или временно вместо формулы ввести 100% в ячейку F1). Иначе не получится создать линейный график с накоплением для диапазона ячеек G2:P11.
Для создания динамической вафельной диаграммы выполняем все выше перечисленные действия, только цвет фигуры круга должен быть желтый.
ВНИМАНИЕ: Не забудьте обратно поменять значение 100% на 50%!
Так же для динамического желтого графика следует убрать заливку фона области. Для этого делаем двойной щелчок мышкой по фоновой области и вносим настройки: «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Нет заливки».
Далее выделите два графика удерживая клавишу CTRL на клавиатуре и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Упорядочивание»-«Группировать», как показано выше на рисунке.
После чего наложите один на другой и переместите группу (вырезать, вставить) на главный лист «Дашборд»:
Для управления слоями наложения диаграмм используйте инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Упорядочение»-«Область выделения», как показано выше на рисунке.
Динамический вафельный график в Excel – готов!
Аналогичным образом создаем еще два вафельных графика для показателей: «Показатель качества» и «Производительность».
Полезный совет. При создании остальных двух вафельных графиков можно не создавать фоновую черную диаграмму, а просто скопировать ее из первой уже созданной вафельной диаграммы.
Готовый шаблон дашборда в Excel
Ниже под вафельными диаграммами у нас на дашборде расположены 3 переключателя для нижнего графика (под ними). Чтобы создать переключатели на листе «Дашборд» выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель». После щелкаем по нему правой кнопкой мышки и в контекстном меню выбираем опцию «Формат объекта»:
В появившемся диалоговом окне «Формат элемента управления» на вкладке «Элемент управления» в поле ввода «Связь с ячейкой:» указываем ссылку для вывода числовых значений в ячейку по адресу: Обработка!$A$6.
Копируем новый элемент управления – переключатель, 2 раза для создания его копий остальным показателям. Теперь при переключении переключателя на листе «Обработка» в ячейке A6 будут возвращаться числовые значения 1,2 и 3. В зависимости от выбранного пользователем переключателя.
Пришло время подготовить данные для нижнего динамически изменяемого графика при переключении. Но сначала на листе «Обработка» сделаем подготовку и обработку всех необходимых значений. Снова выполним ряд последовательных действий:
Данные подготовлены и обработанные!
Теперь выполним ряд действий для создания самого нижнего графика:
Можно еще сделать несколько шагов для оформления:
После всех оформлений переносим график на главный лист «Дашборд». Там же делаем все необходимые дополнительные оформления на свой вкус. После настраиваем расположение элементов и наслаждаемся готовым результатом:
В результате получился простой аналитический дашборд в Excel. Научившись делать такие простые дашборды для интерактивных и презентабельных отчетов с динамической визуализацией данных, постепенно можно переходить к более сложным.
Как сделать интерактивный дашборд в Excel средствами сводных диаграмм и срезов
Мастер-класс по работе с диаграммами. Уровень освоения: выше среднего. Результат будет примерно такой:
1. В наличии файл с данными
Необходимо проанализировать данные, оформив результат анализа в наглядный отчет:
Как заметно из картинки, разрезов 3: товар, магазин и менеджер. По ним и сделаем анализ.
2. Строим сводные таблицы по данным
Я построила 3 таблицы на одном листе. Здесь этого достаточно.
Таблиц должно быть столько, сколько будет диаграмм.
3. На основе каждой таблицы создаем диаграммы
Кнопкой Сводная диаграмма на вкладке Анализ (сводной таблицы). Или на вкладке Вставка.
Я сделала диаграммы 3-х разных типов, которые нагляднее всего отображают сведения. Диаграммы можно использовать и других типов, главное помнить, диаграмма не должна быть сложной, затруднять понимание информации. Только упрощать. Чтобы увидел — и сразу понял, что здесь и о чем здесь.
Заодно переименовала столбцы в первой сводной таблице, чтобы на диаграмме в легенде отображались корректные подписи.
Убрала кнопки полей с каждой диаграммы: вкладка Анализ (сводной диаграммы) — Кнопки полей — Скрыть все. Без них диаграммы смотрятся лучше. Да и функционально эти кнопки не понадобятся.
Марк Мур. Дашборды в 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 листа Фин_панель. Можете скрыть листы с исходными данными и промежуточными вычислениями, оставив только две панели.
