Як створити діаграму з інтерактивними підписами даних у Excel - ExcelGuide Як зробити це в Excel
Вітаю. Сьогодні Ви дізнаєтеся про чудову можливість в Excel, а саме як створити діаграму, а точніше гістограму, з динамічними підписами даних. Вивчивши цей урок, Ви зможете створювати діаграми, в яких буде можливість вибирати, яку інформацію показувати на ній, без перерахунку вихідних даних.
Дуже багато користувачів, думаю, і Ви теж, часто у своїй роботі використовують гістограми для візуалізації даних. Однак часто вони замість відповідей на питання, створюють ще більше їх. Наприклад:
- Яка загальна сума низки?
- Який відсоток у кожного сегмента у ряду?
- Яка відсоткова зміна порівняно з попереднім періодом?
Динамічні підписи даних на гістограмі
Наведена нижче діаграма намагається відповісти на частину з цих питань. Вона дозволяє користувачеві змінювати підписи даних за допомогою зрізів. Користувач може швидко побачити загальний дохід, відсоток від загального або відсоткову зміну кожного блоку в кожному ряду. Звичайно, Ви можете представити всі ці дані в одному підписі, але це зазвичай призводить до дуже захаращених графіків.
Як створити графік з динамічними підписами даних
Налаштування динамічних підписів даних не надто складне, але потребує небагато труднощів. Нам знадобляться такі компоненти цієї діаграми:
- Функція тексту
- Функція ВИБІР
- Одна зведена таблиця
- Один зріз
- Одна гістограма
Завантажте файл як приклад, щоб слідувати за моїми поясненнями. Увага: Цей файл працює правильно в Excel 2013 чи 2016.
Крок 1: Створіть гістограму із підсумковими даними
Крок 2: Розрахунок значень для підписів даних
Вихідні дані для гістограми виглядають як на скріншоті знизу. Нам потрібно буде розрахувати різні значення для підписів даних.
Я створив розділ на аркуші для кожного виміру: Сума, % від загального та % зміни. Це досить легко, і я не вдаватимуся до деталей кожного розрахунку.
Крок 3: Використовуйте формулу ТЕКСТ для формування підписів
Як правило, діаграма відображає підписи даних на основі основного джерела діаграми.
В Excel 2013 було введено нову можливість під назвою "Значення з осередків". Ця можливість дозволяє вказати діапазон, який ми хочемо використовувати для підпису даних.
Так як наші підписи даних змінюються між фінансовим ($) та процентним (%) форматом, нам потрібен спосіб, щоб також змінити форматування чисел на графіку. В іншому випадку Excel буде відображати десяткове число замість відсотків.
На щастя, ми можемо використовувати функцію тексту для цього. Функція ТЕКСТ дозволяє Вам взяти число та вказати формат, в якому Ви хотіли б, щоб воно відобразилося.
Функція ТЕКСТ насправді повертає текст у комірку, навіть якщо вона виглядає як число. Таким чином, ми можемо використовувати цю функцію як джерело наших підписів даних і гістограма відображатиме правильне форматування.
Крок 4: Використання функції ВИБІР
Використовуйте функцію ВИБІР, щоб визначити які саме підписи даних необхідно відобразити на діаграмі.
У нас є три розділи, які містять різні метрики. Далі ми створимо ще один розділ, який міститиме інформацію для підписів даних на гістограмі.
Нам потрібно буде використовувати функцію пошуку, щоб повернути правильну метрику на вибір зрізу. Функція ВИБІР відмінно підходить для цього.
Функція ВИБІР дозволяє нам вказати індекс (1,2,3.) і вона поверне те значення, яке відповідає індексу.
Зараз ми просто додамо комірку, яка містить номер індексу, і вказує на три метрики для кожного значення у формулі ВИБІР.
У результаті зріз управлятиме індексом.
Крок 5: Налаштування підписів даних
Наступним кроком є зміна підписів даних, щоб вони відображали значення з осередків, сформованих нами за допомогою формули ВИБІР. Як я говорив раніше, ми будемо використовувати функцію "Значення з осередків", яка є в Excel 2013 або 2016, щоб зробити це простіше. Вам потрібно вибрати на графіку ряд підписів, потім натиснути кнопку "Вибрати діапазон" в меню Параметри підпису.
Потім виберіть діапазон, який містить підписи даних для цього ряду.
Повторіть цей крок для кожного ряду на гістограмі.
Крок 6: Налаштування зведеної таблиці та зрізу
Останній крок дозволить зробити наші підписи даних інтерактивними. Ми зробимо це за допомогою зведеної таблиці та зрізу. Вихідні дані для зведеної таблиці - це таблиця малюнку нижче.
Ця таблиця містить три варіанти для різних підписів даних. Вона також включає номер індексу, на який будепосилатися формула ВИБІР (див. крок 4). Створіть зведену таблицю. Додайте Name, Index і Symbol у рядки зведеної таблиці.
Потім вставте зріз для поля Символ. Для цього натисніть на зведену таблицю, зайдіть в меню Конструктор і там натисніть кнопку Вставити зріз.
Зріз можна відформатувати, щоб відповідати оформленню Вашого графіка. Я змінив кількість стовпців до 3, щоб показати кнопки зрізу по горизонталі.
Останній крок полягає в тому, щоб у комірці С38 послатися на значення індексу зі зведеної таблиці для коректної роботи формули ВИБІР. Коли користувач натискає кнопку зрізу, в комірці С38 відобразиться індекс обраного елемента зведеної таблиці.
Формули ВИБІР автоматично відобразять підписи даних для вибраного зрізу.
Було зроблено багато кроків, щоб створити цю інтерактивну гістограму. На щастя, Ви можете завантажити приклад і модифікувати його для себе.
Ви можете переміщати зріз в області діаграми, а також додати інструкції, щоб користувач розумів, як це працює.