Excel Dashboards — Интерактивные элементы управления

Если у вас есть больше данных для отображения на приборной панели, которые не помещаются на одном экране, вы можете выбрать использование элементов управления Excel, которые входят в состав Excel Visual Basic. Наиболее часто используемые элементы управления — это полосы прокрутки, переключатели и флажки. Включив их в панель управления, вы можете сделать ее интерактивной и позволить пользователю просматривать различные аспекты данных с помощью возможных вариантов выбора.

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

Интерактивные элементы управления Excel будут доступны на вкладке РАЗРАБОТЧИК на ленте.

Если вы не нашли вкладку «РАЗРАБОТЧИК» на ленте, выполните следующие действия:

• Нажмите на Настроить ленту в окне параметров Excel.
• Выберите «Основные вкладки» в поле «Настройка ленты».
• Установите флажок Разработчик в списке главных вкладок.

• Нажмите ОК. Вы найдете вкладку РАЗРАБОТЧИК на ленте.

Полосы прокрутки в инструментальных панелях

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

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

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

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

Создание полосы прокрутки

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

• Вставьте полосу прокрутки.

  • Нажмите на вкладку РАЗРАБОТЧИК на ленте.

  • Нажмите «Вставить» в группе «Элементы управления».

  • Нажмите значок полосы прокрутки в разделе «Элементы управления форм» в раскрывающемся списке значков.

Вставьте полосу прокрутки.

Нажмите на вкладку РАЗРАБОТЧИК на ленте.

Нажмите «Вставить» в группе «Элементы управления».

Нажмите значок полосы прокрутки в разделе «Элементы управления форм» в раскрывающемся списке значков.

• Подведите курсор к столбцу I и потяните вниз, чтобы вставить вертикальную полосу прокрутки.

• Отрегулируйте высоту и ширину полосы прокрутки и выровняйте ее по таблице.

Подведите курсор к столбцу I и потяните вниз, чтобы вставить вертикальную полосу прокрутки.

Отрегулируйте высоту и ширину полосы прокрутки и выровняйте ее по таблице.

• Щелкните правой кнопкой мыши по полосе прокрутки.

• Нажмите на Контроль формата в раскрывающемся списке.

Щелкните правой кнопкой мыши по полосе прокрутки.

Нажмите на Контроль формата в раскрывающемся списке.

Откроется диалоговое окно управления форматами.

• Нажмите на вкладку «Управление».

• Введите следующее в появившиеся поля.

Нажмите на вкладку «Управление».

Введите следующее в появившиеся поля.

• Нажмите кнопку ОК. Полоса прокрутки готова к использованию. Вы выбрали ячейку O2 в качестве ссылки на ячейку для полосы прокрутки, которая принимает значения 0–36 при перемещении полосы прокрутки вверх и вниз. Далее необходимо создать копию данных в таблице со ссылкой на значение в ячейке O2.

• В ячейке K3 введите следующее —

  = OFFSET (Сводка [@ [S. №.]], $ O $ 2,0).

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

В ячейке K3 введите следующее —

= OFFSET (Сводка [@ [S. №.]], $ O $ 2,0).

• Нажмите кнопку Enter. Заполните ячейки в столбце, копируя формулу.

Нажмите кнопку Enter. Заполните ячейки в столбце, копируя формулу.

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

Заполните ячейки в других столбцах, копируя формулу.

Ваша динамическая и прокручиваемая таблица готова для копирования на панель инструментов.

• Переместите полосу прокрутки вниз.

Переместите полосу прокрутки вниз.

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

• Перетащите полосу прокрутки вниз.

Перетащите полосу прокрутки вниз.

Последние 12 строк данных отображаются, поскольку текущее значение равно 36 (как показано в ячейке O2), а 36 — это максимальное значение, которое вы установили в диалоговом окне «Управление формой».

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

Создание динамической и интерактивной целевой линии

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

Вы можете сделать следующее —

• Создайте столбчатую диаграмму, показывающую всю эту информацию.
• Создайте целевую линию по столбцам.
• Сделайте Целевую линию интерактивной с помощью полосы прокрутки.
• Сделайте Target Line динамически устанавливающей целевые значения в ваших данных.
• Выделите значения, которые соответствуют цели.

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

Выберите данные. Вставьте кластерную диаграмму столбца.

Создайте целевую линию по столбцам

Измените тип диаграммы на комбинированный. Выберите тип диаграммы как Линия для целевого ряда и Кластерный столбец для остальной части ряда.

Создайте базовую таблицу для Целевой линии. Вы сделаете это динамическим позже.

Измените значения ряда данных для Целевой линии на столбец Целевой в приведенной выше таблице.

Нажмите кнопку ОК.

Измените цветовую схему для кластерного столбца. Измените целевую линию на зеленую пунктирную линию.

Сделайте линию цели интерактивной с помощью полосы прокрутки

• Вставьте полосу прокрутки и поместите ее под диаграмму и измените ее размер с января по июнь.

• Введите параметры полосы прокрутки в диалоговом окне «Управление форматом».

Вставьте полосу прокрутки и поместите ее под диаграмму и измените ее размер с января по июнь.

Введите параметры полосы прокрутки в диалоговом окне «Управление форматом».

• Создайте таблицу с двумя столбцами — Месяц и Цель.

• Введите значения на основе таблицы данных и ссылки на ячейку полосы прокрутки.

Создайте таблицу с двумя столбцами — Месяц и Цель.

Введите значения на основе таблицы данных и ссылки на ячейку полосы прокрутки.

Эта таблица отображает месяц и соответствующую цель в зависимости от положения полосы прокрутки.

Сделайте Target Line динамически устанавливающей целевые значения в ваших данных

Теперь вы готовы сделать вашу Целевую линию динамичной.

• Измените значения столбца Target в базовой таблице, которую вы создали для Target Line, набрав = $ G $ 12 во всех строках.

Измените значения столбца Target в базовой таблице, которую вы создали для Target Line, набрав = $ G $ 12 во всех строках.

Как вам известно, в ячейке G12 целевое значение отображается динамически.

Как вы можете заметить, целевая линия перемещается в зависимости от полосы прокрутки.

Выделите значения, которые соответствуют цели

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

• Добавьте столбцы в правую часть таблицы данных — «Восток-результаты», «Север-результаты», «Южные результаты» и «Запад-результаты».

• В ячейку H3 введите следующую формулу —

  = IF (D3> = $ G $ 12, D3, NA ())

Добавьте столбцы в правую часть таблицы данных — «Восток-результаты», «Север-результаты», «Южные результаты» и «Запад-результаты».

В ячейку H3 введите следующую формулу —

= IF (D3> = $ G $ 12, D3, NA ())

• Скопируйте формулу в другие ячейки таблицы. Изменить размер таблицы.

Скопируйте формулу в другие ячейки таблицы. Изменить размер таблицы.

Как вы можете заметить, значения в столбцах — Восток-Результаты, Север-Результаты, Юг-Результаты и Запад-Результаты динамически изменяются в зависимости от полосы прокрутки (т. Е. Целевого значения). Отображаются значения, которые больше или равны Target, а остальные значения просто # N / A.

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

• Нажмите на Изменить тип диаграммы.

• Сделайте Целевой ряд Линией, а остальную Кластерную Колонну.

• Для вновь добавленного ряда данных выберите Вторичная ось.

• Отформатируйте ряд данных таким образом, чтобы ряды «Восток», «Север», «Юг» и «Запад» имели оранжевый цвет заливки, а серии «Восток-результаты», «Результаты севера», «Юг-результаты» и «WestResults» — зеленый цвет заливки.

• Введите метку данных для целевой строки и сделайте ее динамической, указав в ячейке значение месяца в таблице динамических данных.

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

Нажмите на Изменить тип диаграммы.

Сделайте Целевой ряд Линией, а остальную Кластерную Колонну.

Для вновь добавленного ряда данных выберите Вторичная ось.

Отформатируйте ряд данных таким образом, чтобы ряды «Восток», «Север», «Юг» и «Запад» имели оранжевый цвет заливки, а серии «Восток-результаты», «Результаты севера», «Юг-результаты» и «WestResults» — зеленый цвет заливки.

Введите метку данных для целевой строки и сделайте ее динамической, указав в ячейке значение месяца в таблице динамических данных.

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

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

Кнопки Excel Option (Radio)

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

В Excel переключатели называются дополнительными кнопками.

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

• Создайте столбчатую диаграмму, показывающую всю эту информацию.
• Создайте целевую линию по столбцам.
• Сделайте линию цели интерактивной с помощью кнопок выбора.
• Сделайте Target Line динамически устанавливающей целевые значения в ваших данных.
• Выделите значения, которые соответствуют цели.

Шаги 1 и 2 такие же, как и в предыдущем случае. К концу второго шага у вас будет следующий график.

Сделайте линию цели интерактивной с помощью кнопок выбора

• Вставьте опциональную кнопку.

  • Нажмите на вкладку РАЗРАБОТЧИК на ленте.

  • Нажмите «Вставить» в группе «Элементы управления».

  • Нажмите значок кнопки «Параметры» в разделе «Элементы управления формой» в раскрывающемся списке значков.

Вставьте опциональную кнопку.

Нажмите на вкладку РАЗРАБОТЧИК на ленте.

Нажмите «Вставить» в группе «Элементы управления».

Нажмите значок кнопки «Параметры» в разделе «Элементы управления формой» в раскрывающемся списке значков.

Поместите его в верхний правый угол графика.

Щелкните правой кнопкой мыши на кнопке Option. Выберите опцию «Контроль формата» в раскрывающемся списке.

Введите параметры кнопки параметров в диалоговом окне «Формат объекта» на вкладке «Управление».

Ячейка F10 связана с кнопкой выбора. Сделайте 5 копий кнопки выбора по вертикали.

Как вы можете заметить, все кнопки выбора имеют одно и то же имя, называемое именами заголовков. Но внутренне Excel будет иметь разные имена для этих опциональных кнопок, которые вы можете посмотреть в поле «Имя». Кроме того, поскольку кнопка выбора 1 была установлена ​​для связи с ячейкой F10, все копии также ссылаются на одну и ту же ячейку.

Нажмите на любую из кнопок выбора.

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

Создайте таблицу с двумя столбцами — Месяц и Цель. Введите значения на основе таблицы данных и ссылки на ячейку полосы прокрутки.

Эта таблица отображает месяц и соответствующую цель на основе выбранной кнопки выбора.

Сделайте Target Line динамически устанавливающей целевые значения в ваших данных

Теперь вы готовы сделать вашу Целевую линию динамичной.

• Измените значения столбца Target в базовой таблице, которую вы создали для Target Line, набрав = $ G $ 12 во всех строках.

Измените значения столбца Target в базовой таблице, которую вы создали для Target Line, набрав = $ G $ 12 во всех строках.

Как вам известно, в ячейке G12 целевое значение отображается динамически.

Как вы можете видеть, целевая линия отображается на основе выбранной кнопки выбора.

Выделите значения, которые соответствуют цели

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

• Добавьте столбцы в правую часть таблицы данных — «Восток-результаты», «Север-результаты», «Южные результаты» и «Запад-результаты».

• В ячейку H3 введите следующую формулу —

  = IF (D3> = $ G $ 12, D3, NA ())

Добавьте столбцы в правую часть таблицы данных — «Восток-результаты», «Север-результаты», «Южные результаты» и «Запад-результаты».

В ячейку H3 введите следующую формулу —

= IF (D3> = $ G $ 12, D3, NA ())

• Скопируйте формулу в другие ячейки таблицы. Изменить размер таблицы.

Скопируйте формулу в другие ячейки таблицы. Изменить размер таблицы.

Как вы можете заметить, значения в столбцах — Восток-Результаты, Север-Результаты, Юг-Результаты и Запад-Результаты динамически изменяются в зависимости от полосы прокрутки (т. Е. Целевого значения). Отображаются значения, которые больше или равны Target, а остальные значения просто # N / A.

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

• Нажмите на Изменить тип диаграммы.

• Сделайте Целевой ряд Линией, а остальную Кластерную Колонну.

• Для вновь добавленного ряда данных выберите Вторичная ось.

• Отформатируйте ряд данных таким образом, чтобы ряды «Восток», «Север», «Юг» и «Запад» имели оранжевый цвет заливки, а серии «Восток-результаты», «Результаты севера», «Юг-результаты» и «WestResults» — зеленый цвет заливки.

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

Нажмите на Изменить тип диаграммы.

Сделайте Целевой ряд Линией, а остальную Кластерную Колонну.

Для вновь добавленного ряда данных выберите Вторичная ось.

Отформатируйте ряд данных таким образом, чтобы ряды «Восток», «Север», «Юг» и «Запад» имели оранжевый цвет заливки, а серии «Восток-результаты», «Результаты севера», «Юг-результаты» и «WestResults» — зеленый цвет заливки.

• Добавьте динамическую метку данных в целевую строку со значением из ячейки $ G $ 12.

• Очистите вторичную ось, так как она не требуется.

• На вкладке «Вид» на ленте снимите флажок «Сетка».

• Измените параметр «Метка» на «Высокий» в параметрах «Ось форматирования». Это смещает метки вертикальной оси вправо, делая вашу метку целевой линии заметной.

Добавьте динамическую метку данных в целевую строку со значением из ячейки $ G $ 12.

Очистите вторичную ось, так как она не требуется.

На вкладке «Вид» на ленте снимите флажок «Сетка».

Измените параметр «Метка» на «Высокий» в параметрах «Ось форматирования». Это смещает метки вертикальной оси вправо, делая вашу метку целевой линии заметной.

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

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

Флажки Excel

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

Вы можете использовать флажки Excel в диаграммах, чтобы выбрать данные, на которые читатель хочет взглянуть. Например, в примере из предыдущего раздела вы создали столбчатую диаграмму, которая отображает данные 4 регионов — Восток, Север, Юг и Запад. Вы можете использовать флажки, чтобы выбрать регионы, для которых отображаются данные. Вы можете выбрать любое количество регионов одновременно.

Вы можете начать с последнего шага предыдущего раздела —

• Вставьте флажок.

  • Нажмите на вкладку РАЗРАБОТЧИК на ленте.

  • Нажмите «Вставить» в группе «Элементы управления».

  • Нажмите значок флажка в разделе «Элементы управления формой» в раскрывающемся списке значков.

Вставьте флажок.

Нажмите на вкладку РАЗРАБОТЧИК на ленте.

Нажмите «Вставить» в группе «Элементы управления».

Нажмите значок флажка в разделе «Элементы управления формой» в раскрывающемся списке значков.

• Поместите его в верхний левый угол графика.

• Измените имя флажка на восток.

Поместите его в верхний левый угол графика.

Измените имя флажка на восток.

• Щелкните правой кнопкой мыши по флажку. Нажмите на Контроль формата в раскрывающемся списке.

• Введите параметры флажка в диалоговом окне «Управление форматами» на вкладке «Управление».

Щелкните правой кнопкой мыши по флажку. Нажмите на Контроль формата в раскрывающемся списке.

Введите параметры флажка в диалоговом окне «Управление форматами» на вкладке «Управление».

• Нажмите кнопку ОК. Вы можете заметить, что в связанной ячейке C19, TRUE будет отображаться, если вы установите флажок, и FALSE будет отображаться, если вы снимите флажок.

• Скопируйте флажок и вставьте 3 раза по горизонтали.

• Измените имена на север, юг и запад.

Нажмите кнопку ОК. Вы можете заметить, что в связанной ячейке C19, TRUE будет отображаться, если вы установите флажок, и FALSE будет отображаться, если вы снимите флажок.

Скопируйте флажок и вставьте 3 раза по горизонтали.

Измените имена на север, юг и запад.

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

• Измените связанные ячейки для Севера, Юга и Запада на $ C $ 20, $ C $ 21 и $ C $ 22 соответственно.

Измените связанные ячейки для Севера, Юга и Запада на $ C $ 20, $ C $ 21 и $ C $ 22 соответственно.

Следующим шагом будет иметь только данные выбранных регионов в диаграмме.

• Создайте структуру таблицы следующим образом:

Создайте структуру таблицы следующим образом:

• Тип = IF ($ C $ 19, H3, NA ()) в ячейке C21.
• Тип = IF ($ D $ 19, I3, NA ()) в ячейке D21.
• Тип = IF ($ E $ 19, J3, NA ()) в ячейке E21.
• Тип = IF ($ F $ 19, K3, NA ()) в ячейке F21.
• Заполните другие строки в таблице.

• Добавьте столбец Target.

• Измените данные диаграммы на эту таблицу.

Добавьте столбец Target.

Измените данные диаграммы на эту таблицу.

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