Створіть діаграму теплової карти в Excel
В Excel діаграма теплової карти виглядає як таблиця, яка є візуальним зображенням, що показує порівняльний вигляд набору даних. Якщо на вашому аркуші є великий набір даних, вам дійсно важко з першого погляду визначити нижчі або вищі значення, але на тепловій карті значення комірки відображається іншим кольоровим малюнком, щоб ми могли бачити більші дані або менші дані швидко і легко, як показано на знімку екрана.
- Створіть просту діаграму теплової карти за допомогою умовного форматування
- Створіть динамічну діаграму теплової карти в Excel
- Приклад 1: Створіть динамічну теплову карту за допомогою смуги прокрутки
- Приклад 2: Створіть динамічну теплову карту за допомогою радіокнопки
- Приклад 3: Створіть динамічну теплову карту за допомогою прапорця
- Завантажте зразок файлу Heat Map Chart
- Відео: Створення діаграми теплової карти в Excel
Створіть просту діаграму теплової карти за допомогою умовного форматування
У Excel немає утиліти з прямою діаграмою теплових карт, але з потужною Умовне форматування функцію, ви можете швидко створити теплову карту, будь ласка, виконайте такі дії:
1. Виберіть діапазон даних, до якого потрібно застосувати Умовне форматування.
2. А потім клацніть Головна > Умовне форматування > Кольорові ваги, а потім виберіть потрібний вам стиль із розкритого розкривного списку праворуч (у цьому випадку я виберу Зелена - Жовта - Червона кольорова шкала) див. скріншот:
3. Тепер створена теплова карта, яка виділяє клітинки на основі їх значень, зелений колір - найвищі значення, а червоний колір - найнижчі, а решта значень відображають колір градієнта між зеленим та червоним. Дивіться знімок екрана:
4. Якщо ви хочете приховати цифри і залишити лише кольори, виберіть діапазон даних і натисніть Ctrl + 1 ключі, щоб відкрити Формат ячеек діалогове вікно.
5, в Формат ячеек у діалоговому вікні під Номер вкладка, клацніть виготовлений на замовлення опція зліва Категорія , а потім введіть ;;; в тип текстове поле, див. знімок екрана:
6. Потім натисніть кнопку OK і всі цифри приховані, як показано нижче:
примітки: Щоб виділити клітинки іншими кольорами, які вам подобаються, виберіть діапазон даних і натисніть Головна > Умовне форматування > Управління правилами йти до Менеджер правил умовного форматування діалогове вікно.
Потім двічі клацніть існуюче правило, щоб відкрити Змінити правило форматування діалогове вікно, а потім скиньте правило до своїх потреб, див. знімок екрана:
Створіть динамічну діаграму теплової карти в Excel
Приклад 1: Створіть динамічну теплову карту за допомогою смуги прокрутки
Якщо на вашому аркуші є декілька даних стовпців, але ви хочете відобразити їх у обмеженому просторі, у цьому випадку ви можете вставити смугу прокрутки на аркуш, щоб зробити теплову карту динамічно, як показано нижче.
Щоб створити цей тип динамічної діаграми теплової карти, виконайте такі дії:
1. Вставте новий аркуш, а потім скопіюйте перші місяці стовпців з вихідного аркуша на цей новий аркуш.
2. Потім натисніть кнопку Розробник > Insert > Смуга прокрутки, див. скріншот:
3. Потім перетягніть мишу, щоб намалювати смугу прокрутки під скопійованими даними, клацніть правою кнопкою миші смужку прокрутки та виберіть Контроль формату, див. скріншот:
4, в Формат об'єкта у діалоговому вікні під Контроль на вкладці, встановіть мінімальне значення, максимальне значення, поступову зміну, зміну сторінки та пов’язану комірку на основі вашого діапазону даних, як показано нижче:
5. Потім натисніть OK щоб закрити це діалогове вікно.
6. Тепер у клітинку B1 цього нового аркуша введіть таку формулу та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати перший результат:
примітки: У наведеній вище формулі, data1! $ B $ 1: $ I $ 13 - оригінальний аркуш із діапазоном даних, за винятком заголовка рядка (місяці), $ I $ 1 - комірка, з якою пов’язана смужка прокрутки, $ B $ 1: B1 - це клітинка, де виводиться формула.
7. Потім перетягніть цю комірку формули до решти комірок, якщо ви хочете показати на робочому аркуші лише 3 роки, перетягніть формулу з B1 на D13, див. Знімок екрана:
8. А потім застосуйте Кольорова шкала в Умовне форматування функція для нового діапазону даних для створення теплової карти, тепер, коли ви перетягнете смугу прокрутки, теплова карта буде переміщуватися динамічно, див. знімок екрана:
Приклад 2: Створіть динамічну теплову карту за допомогою радіокнопки
Ви також можете створити динамічну теплову карту за допомогою перемикачів, виберіть один перемикач, щоб виділити найбільші значення n, а інший перемикач виділить найменші значення n, як показано нижче:
Щоб скласти динамічну теплову карту цього типу, виконайте наступне:
1. Клацання Розробник > Insert > Кнопка опцій (контроль форми), потім перетягніть мишу, щоб намалювати дві перемикачі, та відредагуйте текст відповідно до ваших потреб, див. знімок екрана:
 |
 |
 |
2. Вставивши перемикачі, клацніть правою кнопкою миші перший і виберіть Контроль формату, В Контроль формату у діалоговому вікні під Контроль на вкладці виберіть клітинку, яка розташована до перемикача, див. знімок екрана:
3. Клацання OK , щоб закрити діалогове вікно, а потім повторіть вищевказаний крок (крок 2), щоб також зв'язати другу перемикач із тією ж коміркою (комірка M1).
4. А потім вам слід застосувати умовне форматування для діапазону даних, виберіть діапазон даних та натисніть Головна > Умовне форматування > Нове правило, див. скріншот:
5, в Нове правило форматування діалогове вікно, виберіть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила і введіть цю формулу: =IF($M$1=1,IF(B2>=LARGE($B$2:$I$13,15),TRUE,FALSE)) в Форматувати значення, де ця формула відповідає дійсності текстове поле, а потім клацніть сформований , щоб вибрати колір. Дивіться знімок екрана:
6. Клацання OK , це виділить 15 найбільших значень червоним кольором, коли ви виберете перший перемикач.
7. Для виділення найменших 15 значень, будь ласка, залиште вибрані дані та перейдіть до Нове правило форматування діалогове вікно, а потім введіть цю формулу: =IF($M$1=2,IF(B2<=SMALL($B$2:$I$13,15),TRUE,FALSE)) в Форматувати значення, де ця формула відповідає дійсності текстове поле та натисніть сформований , щоб вибрати інший потрібний вам колір. Дивіться знімок екрана:
примітки: У наведених формулах, $ M $ 1 комірка, пов’язана з перемикачами, $ B $ 2: $ I $ 13 - діапазон даних, до якого потрібно застосувати умовне форматування, B2 - це перша комірка діапазону даних, число 15 це конкретне число, яке ви хочете виділити.
8. Клацання OK щоб закрити діалогове вікно, тепер, при виборі першого перемикача, будуть виділені найбільші 15 значень, а при виборі другого перемикача, найменші 15 значень будуть виділені, як показано нижче:
Приклад 3: Створіть динамічну теплову карту за допомогою прапорця
У цьому розділі я представив динамічну діаграму теплової карти за допомогою прапорця, який може допомогти вам показати або приховати теплову карту відповідно до ваших потреб. Якщо встановити прапорець, відображатиметься теплова карта, якщо зняти прапорець, вона буде відразу прихована, див. Демонстрацію нижче:
1. По-перше, слід перетворити діапазон даних у формат таблиці, що може допомогти вам автоматично застосувати умовне форматування під час вставки нового рядка даних. Виберіть діапазон даних, а потім натисніть Ctrl + T, клавіші разом, щоб відкрити Створити таблицю діалогове вікно, див. знімок екрана:
2. Клацання OK , щоб закрити діалогове вікно, а потім клацніть Розробник > Insert > Прапорець (контроль форми), потім перетягніть мишку, щоб намалювати прапорець і відредагувати текст відповідно до ваших потреб, як показано на скріншотах нижче:
 |
|
 |
3. Потім клацніть правою кнопкою миші прапорець та виберіть Контроль формату, В Формат об'єкта у діалоговому вікні під Контроль на вкладці, виберіть клітинку, яка розташована біля прапорця, див. знімок екрана:
4. Клацання OK щоб закрити діалогове вікно, виберіть діапазон даних, який потрібно створити, і натисніть кнопку Головна > Умовне форматування > Нове правило йти до Нове правило форматування діалогове вікно.
5, в Нове правило форматування діалоговому вікні, виконайте такі дії:
- Select Форматувати всі комірки на основі їх значень опція від Виберіть тип правила вікно списку;
- Вибирати 3-кольорова шкала від Формат стилю випадаючий список;
- Select Formula в тип коробки під мінімальний, Середина та Максимальний випадаючі списки окремо;
- А потім введіть наступні формули до трьох значення текстові поля:
- мінімум: = ПІСЛЯ ($ M $ 1 = TRUE, MIN ($ B $ 2: $ I $ 13), FALSE)
- Середня точка: = ЯКЩО ($ M $ 1 = TRUE, СЕРЕДНІЙ ($ B $ 2: $ I $ 13), FALSE)
- максимум: = ЯКЩО ($ M $ 1 = TRUE, MAX ($ B $ 2: $ I $ 13), FALSE)
- Потім вкажіть кольори виділення з Колір розділ за вашими потребами.
примітки: У наведених формулах, $ M $ 1 - комірка, яка пов’язана з прапорцем, $ B $ 2: $ I $ 13 - діапазон даних, до якого потрібно застосувати умовне форматування.
6. Після закінчення налаштувань натисніть OK , щоб закрити діалогове вікно, тепер, коли ви встановите прапорець, відображатиметься теплова карта, інакше вона буде прихована. Дивіться демонстрацію нижче:
Завантажте зразок файлу Heat Map Chart
Відео: Створення діаграми теплової карти в Excel
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
