Як створити інтерактивні діаграми з динамічними елементами в Excel

Цей підручник продемонструє, як створювати інтерактивні діаграми з динамічними елементами у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022.

Інтерактивна діаграма - це графік, який дозволяє користувачеві контролювати, які ряди даних слід ілюструвати на діаграмі, дозволяючи відображати або приховувати дані за допомогою елементів керування формою. Вони бувають різних форм і форм, починаючи від кнопок опцій і закінчуючи смугами прокрутки та випадаючими списками.

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

Отже, створення інтерактивних діаграм з динамічними елементами дозволяє вбити двох птахів одним каменем.

Для початку інтерактивні діаграми допоможуть акуратно відобразити величезну кількість даних лише одним графіком:

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

Тим не менш, цей підручник є курсовим курсом до передових методів побудови діаграм в Excel: ви навчитеся створювати інтерактивні діаграми як зі спадним списком, так і за допомогою смуги прокрутки, а також освоїте способи створення заголовка діаграми і діапазон даних діаграми динамічний.

Приклади даних

Щоб показати вам, що це таке, нам потрібні деякі необроблені дані для роботи. З цієї причини розглянемо таку таблицю, що ілюструє показники продажів книг у вигаданому онлайн -книгарні:

Як створити заголовок динамічної діаграми

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

Ви можете зробити заголовок динамічним, прив’язавши його до певної комірки на аркуші, щоб будь -які зміни значення у цій клітинці автоматично відображалися у заголовку діаграми.

І краса цього методу в тому, що для перетворення заголовка діаграми зі статичного на динамічний потрібно всього кілька простих кроків:

1. Двічі клацніть по заголовку діаграми.
2. Натисніть на Панель формул і введіть "=" в це.
3. Виберіть будь -яку клітинку, щоб призначити її новою назвою діаграми.

На перший погляд ідея здається досить примітивною. Але ми ледь навіть подряпали поверхню речей, що стали можливими, застосувавши цю просту техніку. Щоб дізнатися більше, повніше ознайомтеся з поглибленим підручником, що охоплює заголовки динамічних діаграм у Excel.

Як створити діапазон динамічних діаграм

Далі: налаштування динамічного діапазону діаграм. Знову ж таки, зазвичай, кожного разу, коли ви додаєте або видаляєте елементи з діапазону даних, що використовуються при побудові діаграми Excel, вихідні дані також слід коригувати вручну.

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

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

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

1. Виберіть весь діапазон даних (A2: G6).
2. Перейдіть до Вставити вкладка.
3. Натисніть кнопку «Таблиця”.

Якщо у ваших даних немає заголовків, зніміть прапорець біляУ моїй таблиці є заголовки”Коробка.

Потрапивши туди, діапазон даних буде перетворено в таблицю.

Тепер все, що вам потрібно зробити, це побудувати діаграму, використовуючи таблицю як вихідні дані діаграми:

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

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

Як створити інтерактивну діаграму зі спадним списком

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

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

Як приклад, подивіться на стовпчасту діаграму, показану нижче. Замість того, щоб показувати брудний графік, що містить всю таблицю даних, вибір цієї інтерактивної діаграми зі спадним списком дозволяє користувачеві збільшити показники продажів за певний рік у два кліки.

Просто виконайте докладні кроки, описані нижче, щоб дізнатися, як ви можете зробити те ж саме.

Крок №1: Покладіть основи.

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

По-перше, клацніть правою кнопкою миші на будь-якому порожньому місці стрічку і виберіть "Налаштуйте стрічку.”

В Параметри Excel діалогове вікно, перейдіть до Основні вкладки списку, перевірте "Розробник", І натисніть"В ПОРЯДКУ.”

The Розробник зараз має з’явитися вкладка стрічку. На цьому етапі виберіть будь-яку порожню клітинку для відображення випадаючого меню (В8). Це допоможе програмі Excel показати правильні дані залежно від того, який варіант вибрано зі спадного списку.

Крок 2: Додайте розкривний список.

Наш наступний крок-це додати випадаюче меню до аркуша:

1. Перейдіть до Розробник вкладка.
2. Натисніть «Вставити.”
3. Під "Контроль форми", Виберіть"Combo Box.”

Намалюйте це порожнє поле зі списком у будь -якому місці. Для ілюстрації ось як виглядає порожнє спадне меню:

Крок №3: Пов’яжіть розкривний список із осередками робочого аркуша.

Щоб меню імпортувало список років у таблиці даних, ви повинні зв’язати його з відповідним діапазоном даних на аркуші. Для цього клацніть правою кнопкою миші на поле зі списком і виберіть "Контроль формату.”

У діалоговому вікні виконайте наведені нижче дії.

1. За “Діапазон введення", Виділіть усі фактичні значення в таблиці даних ($ A $ 3: $ G $ 6).
2. За “Посилання на клітинку”, Виберіть порожню клітинку, призначену для зберігання випадаючого меню ($ B $ 8).
3. За “Випадаючі лінії", Введіть кількість точок даних у наборі даних (у нашому випадку чотири) і натисніть"В ПОРЯДКУ.”

Ця дія має призвести до того, що назви точок даних будуть додані до випадаючого списку, а значення допоміжної комірки (В8) характеризує вибраний пункт з меню як варіант 1, 2, 3 або 4:

Крок 4: Налаштуйте таблицю даних діаграми.

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

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

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

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

1	= ІНДЕКС (A3: A6, $ B $ 8)

В основному формула фільтрує стовпець Рік (стовпець А) і повертає виключно значення у рядку, номер якого відповідає вибраному в даний момент елементу у випадаючому списку.

Крок 5: Налаштуйте діаграму на основі даних динамічної діаграми.

Ви пройшли найважчу частину, тому вам залишається лише створити 2-D діаграму на основі допоміжної таблиці (виділіть допоміжну таблицю [A10: G11]> Вставити> створіть кластерну діаграму стовпців).

І, нарешті, якщо ви хочете, щоб випадаюче меню було розміщене поверх діаграми, клацніть правою кнопкою миші на поле зі списком, а потім клацніть «Замовлення", І виберіть"На передній план. ” (Крім того, клацніть правою кнопкою миші на діаграмі діаграми та натисніть "Замовлення", І виберіть"Надіслати назад.”)

Це воно! Тут у вас є інтерактивна діаграма зі стовпцями, пов’язана зі спадним меню.

Як створити інтерактивну діаграму за допомогою смуги прокрутки

По правді кажучи, процес додавання смуги прокрутки до ваших діаграм Excel подібний до кроків, описаних вище.

Але що, якщо ви хочете проаналізувати динаміку продажів кожної категорії книг (стовпці), а не відображати щорічні показники продажів (рядки)? Для ілюстрації подивіться на інтерактивну лінійну діаграму нижче, зробивши саме це:

Як вам це вдається? Ну, тут ситуація дещо ускладнюється. Однак, якщо ви просто дотримуєтесь покрокового процесу нижче, слідування інструкціям повинно виглядати як прогулянка парком, навіть якщо ви зовсім новачок.

Крок №1: Покладіть основи.

Перш ніж розпочати, додайте Розробник вкладку до стрічку, оскільки він містить функцію, яка дозволяє нам намалювати смугу прокрутки (клацніть правою кнопкою миші будь-яке порожнє місце на стрічці> налаштуйте стрічку> основні вкладки> встановіть прапорець «Розробник»> OK).

Щоб все працювало як по маслу, призначте порожню клітинку для відображення виводу смуги прокрутки, щоб Excel міг правильно розмістити поле прокрутки на смузі прокрутки (В8).

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

Наступний наш крок - вставити смугу прокрутки до аркуша:

1. Перейдіть до Розробник вкладка.
2. Натисніть кнопку "Вставити”.
3. Під "Контроль форми", Виберіть"Смуга прокрутки.”

На цьому етапі ви можете намалювати смугу прокрутки куди завгодно. Пізніше ми перемістимо його на місце.

Крок 2: Пов’яжіть смугу прокручування з даними робочого аркуша.

Клацніть правою кнопкою миші на смузі прокрутки та виберіть "Контроль формату.”

В Контроль формату діалогове вікно, змініть параметри смуги прокрутки:

• Поточне значення: Це значення визначає положення поля прокрутки за промовчанням на смузі прокрутки. Встановіть значення "1.”
• Мінімальне значення: Цей параметр позначає найменше значення смуги прокрутки. У нашому випадку введіть "1”У поле.
• Максимальне значення: Цей параметр визначає найвище значення смуги прокрутки і має відповідати кількості точок даних, які будуть використовуватися для побудови інтерактивної діаграми. Оскільки таблиця даних складається з шести категорій книг, змініть значення на “6.”
• Поступові зміни: Це значення визначає, наскільки плавно прокручування змінює своє положення під час переміщення. Завжди встановлюйте значення "1.”
• Зміна сторінки: Це значення визначає, наскільки плавно панель прокрутки змінює своє положення, коли ви натискаєте область між полем прокручування та стрілками. Завжди встановлюйте значення "1.”
• Посилання на мобільний телефон: Це значення характеризує поточне положення поля прокручування. Просто виділіть клітинку на робочому аркуші, раніше призначеному для цієї мети ($ B $ 8).

Крок №3: Створіть таблицю даних діаграми.

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

Для цього почніть з копіювання колонка Рік як показано на скріншоті нижче (А2: А6).

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

Введіть у клітинку таку формулу В10:

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

Тепер імпортуйте показники продажів, що відповідають поточній категорії книг, ввівши цю формулу В11 і скопіюйте його (В11: В14):

1	= INDEX ($ B $ 3: $ G $ 6, MATCH (A11, $ A $ 3: $ A $ 6,0), MATCH ($ B $ 10, $ B $ 2: $ G $ 2, 0))

Поєднання функцій INDEX та MATCH допомагає точно повернути значення номера рядка та стовпця, що відповідає зазначеним критеріям.

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

Крок 4: Створіть діаграму на основі допоміжної таблиці.

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

Виділіть всю таблицю помічників (A10: B14), перейдіть до Вставити вкладку та побудуйте будь-яку 2-D діаграму-наприклад, лінійну діаграму (Вставити діаграму лінії або області> Лінія з маркерами).

ПРИМІТКА. Якщо ви хочете, щоб смуга прокрутки відображалася у верхній частині діаграми, клацніть правою кнопкою миші на смузі прокрутки та виберіть "Замовлення"Та виберіть"Висувати. ” (Крім того, клацніть правою кнопкою миші на діаграмі діаграми та натисніть "Замовлення", І виберіть"Надіслати назад.”)

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