Как создавать интерактивные диаграммы с динамическими элементами в Excel

В этом руководстве будет показано, как создавать интерактивные диаграммы с динамическими элементами во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

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

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

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

Начнем с того, что интерактивные диаграммы помогают аккуратно отображать огромные объемы данных с помощью всего лишь одного графика:

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

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

Образец данных

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

Как создать заголовок динамической диаграммы

Давайте сначала займемся простой частью: сделаем заголовок вашей диаграммы динамичным.

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

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

1. Дважды щелкните заголовок диаграммы.
2. Щелкните значок Панель формул и введите «=" внутрь.
3. Выберите любую ячейку, чтобы назначить ее новым заголовком диаграммы.

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

Как создать динамический диапазон диаграммы

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

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

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

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

1. Выберите весь диапазон данных (A2: G6).
2. Перейдите в Вставлять таб.
3. Щелкните значок «Стол" кнопка.

Если у ваших данных нет заголовков, снимите флажок «В моей таблице есть заголовки" коробка.

Оказавшись там, диапазон данных будет преобразован в таблицу.

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

1. Выделите таблицу (A2: G6).
2. Перейдите к Вставлять таб.
3. Создайте любую двумерную диаграмму, например, столбчатую диаграмму с кластерами (Вставить столбец или линейчатую диаграмму> Кластерная линейка).

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

Как создать интерактивную диаграмму с раскрывающимся списком

Теперь, когда вы сделали первые шаги в области расширенных инструментов визуализации данных в Excel, пора перейти к самому сложному: интерактивным диаграммам Excel.

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

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

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

Шаг № 1: Заложите основу.

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

Сначала щелкните правой кнопкой мыши любое пустое место в лента и выберите «Настройте ленту.”

в Параметры Excel диалоговом окне перейдите к Основные вкладки список, отметьте «РазработчикПоле "и нажмите"OK.”

В Разработчик вкладка должна теперь отображаться на лента. На этом этапе выберите любую пустую ячейку для отображения вывода раскрывающегося меню (B8). Это поможет Excel отображать правильные данные в зависимости от того, какой вариант выбран в раскрывающемся списке.

Шаг № 2: Добавьте раскрывающийся список.

Наш следующий шаг - добавить раскрывающееся меню на рабочий лист:

1. Перейти к Разработчик таб.
2. Нажмите «Вставлять.”
3. Под "Элементы управления формой," Выбрать "Поле со списком.”

Нарисуйте это пустое поле со списком в любом месте. Для иллюстрации вот как выглядит пустое раскрывающееся меню:

Шаг № 3: Свяжите раскрывающийся список с ячейками рабочего листа.

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

В диалоговом окне сделайте следующее:

1. Для "Диапазон ввода, »Выделите все фактические значения в таблице данных (3 доллара США: 6 долларов США G).
2. Для "Сотовая ссылка, »Выберите пустую ячейку, предназначенную для хранения вывода раскрывающегося меню ($ 8 млрд).
3. Для "Выпадающие строки, "Введите количество точек данных в вашем наборе данных (в нашем случае четыре) и нажмите"OK.”

Это действие должно привести к добавлению имен точек данных в раскрывающийся список, в то время как значение вспомогательной ячейки (B8) характеризует текущий выбранный элемент из меню как вариант 1, 2, 3 или 4:

Шаг № 4: Настройте таблицу данных диаграммы.

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

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

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

Но как убедиться, что данные извлекаются правильно? Вот здесь и вступает в игру функция ИНДЕКС. Введите следующую формулу в A11 и скопируйте его в G11:

1	= ИНДЕКС (A3: A6; $ B $ 8)

Обычно формула фильтрует по столбцу Год (столбец А) и возвращает только значение в строке, номер которой соответствует текущему выбранному элементу в раскрывающемся списке.

Шаг № 5: Настройте диаграмму на основе данных динамической диаграммы.

Вы прошли через самую сложную часть, поэтому все, что осталось сделать, это создать двухмерную диаграмму на основе вспомогательной таблицы (выделите вспомогательную таблицу [A10: G11]> Вставить> создать кластерную столбчатую диаграмму).

В качестве последнего штриха, если вы хотите, чтобы раскрывающееся меню располагалось в верхней части диаграммы, щелкните правой кнопкой мыши поле со списком, нажмите «порядок, "И выберите"На передний план. » (Или щелкните правой кнопкой мыши график диаграммы, выберите «порядок, "И выберите"Отправить на задний план.”)

Вот и все! Здесь у вас есть интерактивная столбчатая диаграмма, связанная с раскрывающимся меню.

Как создать интерактивную диаграмму с полосой прокрутки

По правде говоря, процесс добавления полосы прокрутки к вашим диаграммам Excel аналогичен шагам, описанным выше.

Но что, если вы хотите проанализировать динамику продаж каждой категории книг (столбцы), а не отображать годовые показатели продаж (строки)? Чтобы проиллюстрировать это, взгляните на интерактивную линейную диаграмму ниже, которая делает именно это:

Как тебе это удается? Что ж, здесь все становится немного сложнее. Однако, если вы просто придерживаетесь пошагового процесса, описанного ниже, следование инструкциям должно ощущаться как прогулка по парку, даже если вы новичок.

Шаг № 1: Заложите основу.

Прежде чем начать, добавьте Разработчик вкладка в лента, поскольку он содержит функцию, позволяющую рисовать полосу прокрутки (щелкните правой кнопкой мыши любое пустое место на ленте> настройте ленту> Основные вкладки> установите флажок «Разработчик»> ОК).

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

Шаг № 2: Нарисуйте полосу прокрутки.

Наш следующий шаг - вставить полосу прокрутки в рабочий лист:

1. Перейти к Разработчик таб.
2. Ударь "Вставлять" кнопка.
3. Под "Элементы управления формой," Выбрать "Полоса прокрутки.”

На этом этапе вы можете нарисовать полосу прокрутки где угодно. Мы переместим его на место позже.

Шаг № 2: Свяжите полосу прокрутки с данными рабочего листа.

Щелкните полосу прокрутки правой кнопкой мыши и выберите «Управление форматом.”

в Управление форматом диалоговом окне измените настройки полосы прокрутки:

• Текущая стоимость: Это значение определяет положение поля прокрутки по умолчанию на полосе прокрутки. Установите значение «1.”
• Минимальное значение: Этот параметр означает наименьшее значение полосы прокрутки. В нашем случае введите «1»В поле.
• Максимальное значение: Этот параметр определяет максимальное значение полосы прокрутки и должен соответствовать количеству точек данных, которые будут использоваться для построения интерактивной диаграммы. Поскольку таблица данных состоит из шести категорий книг, измените значение на «6.”
• Постепенное изменение: Это значение определяет, насколько плавно ползунок меняет свое положение при его перемещении. Всегда устанавливайте значение «1.”
• Изменение страницы: Это значение определяет, насколько плавно полоса прокрутки меняет свое положение, когда вы щелкаете область между полем прокрутки и стрелками. Всегда устанавливайте значение «1.”
• Сотовая ссылка: Это значение характеризует текущее положение ползунка. Просто выделите ячейку на листе, ранее назначенную для этой цели ($ 8 млрд.).

Шаг № 3: Создайте таблицу данных диаграммы.

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

Для этого начнем с копирования столбец Год как показано на скриншоте ниже (A2: A6).

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

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

1	= ИНДЕКС ($ B $ 2: $ G $ 2, $ B $ 8)

Теперь импортируйте показатели продаж, соответствующие текущей категории книги, введя эту формулу в B11 и скопируйте его (B11: B14):

1	= ИНДЕКС ($ B $ 3: $ G $ 6, ПОИСКПОЗ (A11, $ A $ 3: $ A $ 6,0), ПОИСКПОЗ ($ B $ 10, $ B $ 2: $ G $ 2, 0))

Комбинация функций ИНДЕКС и ПОИСКПОЗ помогает точно вернуть значение, номер строки и столбца которого соответствует заданным критериям.

Формула сначала фильтруется через столбец Год (3 австралийских доллара: 6 австралийских долларов) и выбирает строку, которая соответствует соответствующему году в вспомогательной таблице (A11). Затем формула таким же образом перебирает названия категорий книг ($ B $ 2: $ G $ 2). В результате формула возвращает правильное динамическое значение, которое корректируется при каждом перемещении ползунка.

Шаг №4: Создайте диаграмму на основе вспомогательной таблицы.

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

Выделите всю вспомогательную таблицу (A10: B14), перейдите в Вставлять вкладку и постройте любую двухмерную диаграмму, например, линейную диаграмму (Вставить линию или диаграмму с областями> Линия с маркерами).

ПРИМЕЧАНИЕ. Если вы хотите, чтобы полоса прокрутки отображалась в верхней части диаграммы, щелкните полосу прокрутки правой кнопкой мыши и выберите «порядок, "И выберите"Вперед. » (Или щелкните правой кнопкой мыши график диаграммы, выберите «порядок, "И выберите"Отправить на задний план.”)

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