Як створити діаграми Excel (стовпець або стовпчик) із умовним форматуванням

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

Умовне форматування-це практика призначення спеціального форматування клітинкам Excel, кольору, шрифту тощо на основі зазначених критеріїв (умов). Ця функція допомагає аналізувати дані, знаходити статистично значущі значення та визначати закономірності в даному наборі даних.

Але, на жаль, Excel не пропонує вбудованих інструментів для застосування умовного форматування до діаграм Excel.

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

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

Починаємо

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

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

Крок №1: Підготовка даних діаграми.

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

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

Наприклад, на скріншоті вище ви можете побачити, що показники продажів за певний місяць повинні потрапляти в діапазон від 90 000 до 150 000 доларів, щоб вважатися задовільним.

Щоб створити правила, використовуйте три порожні рядки над набором даних:

• Ряд 1 (мітка): Використовуйте ці клітинки для позначення правил форматування.
• Рядок 2 (мінімальне значення): Значення в цьому рядку визначають нижню межу для кожної умови.
• Рядок 3 (макс. Значення): Так само ці значення встановлюють верхні межі.

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

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

1	= C1 & ": from" & TEXT (C2, "$#, ##") & "to" & TEXT (C3, "$#, ##")

Формула об'єднує динамічно виглядаючу динамічну мітку на основі раніше встановлених правил умовного форматування. Функція TEXT форматує значення у валюті. Але якщо ваш тип даних відрізняється, застосуйте замість цього цю формулу:

1	= C1 & ": from" & TEXT (C2, "#, ##") & "to" & TEXT (C3, "#, ##")

Або це, коли ви працюєте з відсотками:

1	= C1 & ": from" & TEXT (C2, "#%") & "to" & TEXT (C3, "#%")

Нарешті, знайдіть значення діаграми. Введіть наступну формулу C5, скопіюйте його до C12 і до відповідних клітинок у стовпець D:

1	= IF (AND (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ())

Формула порівнює задане фактичне значення в стовпець В проти заданих граничних значень за допомогою функцій IF та AND. Якщо значення потрапляє в діапазон, його буде скопійовано в цей стовпець. В іншому випадку формула повертає помилку #N/A, щоб запобігти багатократному нанесенню одного і того ж значення.

Крок 2: Налаштуйте стовпчасту діаграму.

Зібравши всі дані діаграми, налаштуйте просту стовпчасту діаграму або стовпчасту діаграму як альтернативу:

1. Виділіть усі дані діаграми, крім стовпців, що містять фактичні значення та правила, утримуючи натиснутою кнопку Ctrl ключ (A4: A12 та C4: D12).
2. Перейдіть до Вставити вкладка.
3. Виберіть "Вставити стовпчасту або стовпчасту діаграму.”
4. Виберіть "Кластерна колонка/кластерна панель.”

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

Крок №3: Змініть значення Overlap та Gap Width.

Перш ніж закрити цей посібник, усуньте проблему неправильно розміщених стовпців, спричинену невидимими порожніми значеннями (помилки #Н/Д), нанесеними на діаграму.

Клацніть правою кнопкою миші на будь-якому стовпці та виберіть "Форматувати серію даних»З контекстного меню, що спливає.

На панелі завдань змініть положення та ширину стовпців:

1. Перейдіть до Опції серії вкладка.
2. Змінити "Перекриття серії"До"100%.”
3. Встановіть Ширина зазору до “60%.”

Крок №4: Налаштуйте колірну схему.

Нарешті, додайте останні штрихи. Не закриваючи Форматувати серію даних панелі завдань, змініть колірну схему діаграми:

1. Перейдіть до Заповнити & Лінія вкладка.
2. Під "Заповнити, "Вибрати"Тверда заливка.”
3. Натисніть кнопку «Колір заливки” зелений з палітри кольорів.

Потрапивши туди, перефарбуйте інші ряди даних, змініть назву діаграми, і ваша повністю динамічна діаграма з умовним форматуванням готова до роботи!