Теплова карта в Excel - це візуальне зображення, яке швидко показує порівняльний вигляд набору даних.
Наприклад, у наведеному нижче наборі даних я можу легко визначити, які це місяці, коли продажі були низькими (виділено червоним) порівняно з іншими місяцями.
У наведеному вище наборі кольорів призначається на основі значення в комірці. Колірна гамма від зеленого до жовтого до червоного з високими значеннями набуває зеленого кольору, а низькі - з червоним.
Створення теплової карти в Excel
Хоча ви можете створити теплову карту в Excel, вручну колірним кодуванням комірок. Однак вам доведеться повторити це, коли зміниться значення.
Замість ручної роботи можна використовувати умовне форматування для виділення клітинок на основі значення. Таким чином, у разі зміни значень у комірках колір/формат комірки автоматично оновлюватиме теплову карту на основі заздалегідь заданих правил у умовному форматуванні.
У цьому уроці ви дізнаєтесь, як:
- Швидко створіть теплову карту в Excel за допомогою умовного форматування.
- Створіть динамічну теплову карту в Excel.
- Створіть теплову карту в зведених таблицях Excel.
Давайте розпочнемо!
Створення теплової карти в Excel за допомогою умовного форматування
Якщо у вас є набір даних в Excel, ви можете вручну виділити точки даних та створити теплову карту.
Однак це буде статичною тепловою картою, оскільки колір не зміниться, якщо ви зміните значення в комірці.
Отже, умовне форматування - це правильний шлях, оскільки він змінює колір клітинки, коли ви змінюєте значення в ній.
Припустимо, у вас є набір даних, як показано нижче:
Ось кроки для створення теплової карти з використанням цих даних:
- Виберіть набір даних. У цьому прикладі це буде В2: D13.
- Перейдіть додому -> Умовне форматування -> Кольорові гами. Він показує різні поєднання кольорів, які можна використовувати для виділення даних. Найпоширеніша кольорова гама - перша, де клітинки з високими значеннями виділені зеленим, а низькі - червоним. Зауважте, що під час наведення курсору миші на ці кольорові шкали ви зможете побачити поточний перегляд у наборі даних.
Це дасть вам теплову карту, як показано нижче:
За замовчуванням Excel призначає червоний колір найнижчому значенню, а зелений - найвищому, а всі інші значення отримують колір на основі значення. Отже, існує градієнт з різними відтінками трьох кольорів на основі значення.
Що робити, якщо ви не хочете градієнта і хочете показувати лише червоний, жовтий та зелений. Наприклад, ви хочете виділити червоним кольором усі значення, менші ніж 700, незалежно від значення. Отже, і 500, і 650 набувають однакового червоного кольору, оскільки це менше 700.
Зробити це:
- Перейдіть додому -> Умовне форматування -> Кольорові шкали -> Додаткові параметри.
- У діалоговому вікні «Нове правило форматування» виберіть «Триколірна шкала» зі спадного меню «Стиль форматування».
- Тепер ви можете вказати мінімальне, середнє значення та максимальне значення та призначити йому колір. Оскільки ми хочемо виділити всі клітинки зі значенням нижче 700 червоним кольором, змініть тип на Number і значення на 700.
- Натисніть OK.
Тепер ви отримаєте результат, як показано нижче. Зверніть увагу, що всі значення нижче 700 мають однаковий відтінок червоного кольору.
Слово застереження: Хоча умовне форматування - чудовий інструмент, на жаль, воно мінливе. Це означає, що при будь -яких змінах на аркуші умовне форматування перераховується. Хоча вплив на невеликі набори даних може бути незначним, це може призвести до уповільнення роботи книги Excel при роботі з великими наборами даних.
Створення динамічної теплової карти в Excel
Оскільки умовне форматування залежить від значення в комірці, як тільки ви змінюєте значення, умовне форматування перераховується та змінюється.
Це дає можливість скласти динамічну теплову карту.
Давайте розглянемо два приклади створення теплових карт за допомогою інтерактивних елементів керування в Excel.
Приклад 1: Теплова карта за допомогою смуги прокрутки
Ось приклад, коли теплова карта змінюється, як тільки ви використовуєте смугу прокрутки, щоб змінити рік.
Цей тип динамічних теплових карт можна використовувати на приладових дошках, де у вас обмежені місця, але все одно потрібно, щоб користувач мав доступ до всього набору даних.
Натисніть тут, щоб завантажити шаблон теплової карти
Як створити цю динамічну теплову карту?
Ось повний набір даних, який використовується для створення цієї динамічної теплової карти.
Ось кроки:
- На новому аркуші (або на тому самому аркуші) введіть назви місяців (просто скопіюйте та вставте його з вихідних даних).
- Перейдіть до Розробник -> Елементи управління -> Вставка -> Панель прокрутки. Тепер натисніть будь -де на аркуші, і він вставить смугу прокрутки. (натисніть тут, якщо ви не можете знайти вкладку розробника).
- Клацніть правою кнопкою миші на смузі прокрутки та клацніть на елементі керування форматом.
- У діалоговому вікні Формат керування внесіть такі зміни:
- Мінімальне значення: 1
- Максимальне значення 5
- Посилання на клітинку: Sheet1! $ J $ 1 (Ви можете натиснути на піктограму праворуч, а потім вручну вибрати клітинку, яку потрібно зв’язати з смугою прокрутки).
- Натисніть OK.
- У клітинку В1 введіть формулу: = ІНДЕКС (Аркуш1! $ В $ 1: $ H $ 13, ROW (), Аркуш1! $ J $ 1+СТОЛКИ (Аркуш2! $ B $ 1: B1) -1)
- Змініть розмір і розмістіть смугу прокручування внизу набору даних.
Тепер, коли ви змінюєте смугу прокрутки, значення у Sheet1! $ J $ 1 змінюється, а оскільки формули пов’язані з цією коміркою, вона оновлюється, щоб відображати правильні значення.
Крім того, оскільки умовне форматування є мінливим, як тільки значення змінюється, воно також оновлюється.
Дивіться відео - Динамічна теплова карта в Excel
Приклад 2: Створення динамічної теплової карти в Excel за допомогою кнопок радіо
Ось ще один приклад, де можна змінити теплову карту, вибравши перемикач:
У цьому прикладі ви можете виділити 10 значень зверху/знизу на основі вибору радіо/кнопки вибору.
Натисніть тут, щоб завантажити шаблон теплової карти
Створення теплової карти у зведеній таблиці Excel
Умовне форматування у зведених таблицях працює так само, як і з будь -якими звичайними даними.
Але вам потрібно знати дещо важливе.
Дозвольте мені взяти приклад і показати вам.
Припустимо, у вас є зведена таблиця, як показано нижче:
Щоб створити теплову карту в цій зведеній таблиці Excel:
- Виділіть клітинки (B5: D14).
- Перейдіть додому -> Умовне форматування -> Кольорові гами та виберіть колірну шкалу, яку потрібно застосувати.
Це миттєво створить теплову карту у зведеній таблиці.
Проблема цього методу полягає в тому, що якщо ви додасте нові дані в бекенд і оновите цю зведену таблицю, умовне форматування не буде застосовано до нових даних.
Наприклад, коли я додав нові дані у внутрішній частині, відкоригував вихідні дані та оновив зведену таблицю, ви можете побачити, що до неї не застосовується умовне форматування.
Це відбувається, коли ми застосували умовне форматування лише до комірок B5: D14.
Якщо ви хочете, щоб ця теплова карта була динамічною, щоб вона оновлювалася при додаванні нових даних, виконайте такі дії:
- Виділіть клітинки (B5: D14).
- Перейдіть додому -> Умовне форматування -> Кольорові гами та виберіть колірну шкалу, яку потрібно застосувати.
- Знову перейдіть додому -> Умовне форматування -> Керувати правилами.
- У Менеджері правил умовного форматування натисніть кнопку Змінити.
- У діалоговому вікні Редагувати правило форматування виберіть третій варіант: усі клітинки, що показують значення "Продажі" для "Дата" та "Клієнт".
Тепер умовне форматування буде оновлюватися при зміні даних бекенда.
Примітка: Умовне форматування зникає, якщо ви змінюєте поля рядка/стовпця. Наприклад, якщо видалити поле «Дата» та застосувати його знову, умовне форматування буде втрачено.
