Как создать тепловую карту в Excel: статическая и динамическая

Это пошаговое руководство проведет вас через процесс создания тепловой карты в Excel с практическими примерами.

Microsoft Excel предназначен для представления данных в виде таблиц. Но в некоторых случаях визуальные эффекты намного легче понять и усвоить. Как вы, наверное, знаете, в Excel есть ряд встроенных функций для создания графиков. К сожалению, тепловой карты на борту нет. К счастью, есть быстрый и простой способ создать тепловую карту в Excel с условным форматированием.

Что такое тепловая карта в Excel?

Тепловая карта (также известная как тепловая карта) — это визуальная интерпретация числовых данных, где разные значения представлены разными цветами. Как правило, используются цветовые схемы от теплого к холодному, поэтому данные представлены в виде горячих и холодных точек.

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

Вот несколько типичных примеров:

  • Тепловая карта температуры воздуха — используется для визуализации данных о температуре воздуха в определенном регионе.
  • Географическая тепловая карта — отображает некоторые числовые данные по географической области с использованием различных оттенков.
  • Тепловая карта управления рисками — наглядно и лаконично показывает различные риски и их влияние.

В Excel тепловая карта используется для отображения отдельных ячеек разными цветовыми кодами в зависимости от их значений.

Например, на тепловой карте ниже вы можете с первого взгляда определить самые влажные (выделены зеленым) и самые засушливые (выделены красным) регионы и десятилетия:
Тепловая карта в Excel

Как создать тепловую карту в Excel

Если вы думали о том, чтобы раскрасить каждую ячейку в зависимости от ее значения вручную, откажитесь от этой идеи, поскольку это было бы ненужной тратой времени. Во-первых, потребуется много усилий, чтобы применить соответствующий цветовой оттенок в соответствии с рангом значения. И, во-вторых, вам придется переделывать цветовое кодирование каждый раз, когда значения меняются. Условное форматирование Excel эффективно преодолевает оба препятствия.

Чтобы сделать тепловую карту в Excel, мы будем использовать цветовую шкалу условного форматирования. Вот шаги для выполнения:

  1. Выберите свой набор данных. В нашем случае это B3:M5.
    Источник данных
  2. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Цветовые шкалы, а затем щелкните нужную цветовую шкалу. Когда вы наведете указатель мыши на определенную цветовую шкалу, Excel покажет вам предварительный просмотр в реальном времени прямо в вашем наборе данных.

    Для этого примера мы выбрали Красный — Желтый — Зеленый цветовая гамма:
    Создание тепловой карты в Excel

    В результате у вас будут высокие значения, выделенные красным цветом, средние — желтым, а низкие — зеленым. Цвета будут корректироваться автоматически при изменении значений ячеек.
    Тепловая карта Excel с градиентной цветовой шкалой

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

Создайте тепловую карту с пользовательской цветовой шкалой

При применении предустановленной цветовой шкалы она отображает самые низкие, средние и самые высокие значения предопределенными цветами (в нашем случае зеленым, желтым и красным). Все остальные значения получают разные оттенки трех основных цветов.

Если вы хотите выделить все ячейки меньше/больше заданного числа определенным цветом независимо от их значений, то вместо использования встроенной цветовой шкалы создайте свою собственную. Вот как это сделать:

  1. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Цветовые шкалы > Дополнительные правила.
    Дополнительные правила
  2. в Новое правило форматирования диалоговом окне выполните следующие действия:
    • Выберите 3-цветную шкалу из Стиль формата выпадающий список.
    • За Минимум и/или Максимум значение, выберите Число в Тип раскрывающемся списке и введите нужные значения в соответствующие поля.
    • За Серединавы можете установить либо Число или же Процентиль (обычно 50%).
    • Назначьте цвет каждому из трех значений.

    Для этого примера мы настроили следующие параметры:
    Создание тепловой карты с пользовательской цветовой шкалой

    На этой пользовательской тепловой карте все температуры ниже 45 °F выделены одним и тем же оттенком зеленого, а все температуры выше 70 °F — одним и тем же оттенком красного:
    Пользовательская тепловая карта

Создать тепловую карту в Excel без цифр

Тепловая карта, которую вы создаете в Excel, основана на фактических значениях ячеек, и их удаление уничтожит тепловую карту. Чтобы скрыть значения ячеек, не удаляя их с листа, используйте настраиваемое числовое форматирование. Вот подробные шаги:

  1. Выберите тепловую карту.
  2. Нажмите Ctrl + 1, чтобы открыть Формат ячеек диалог.
  3. На Число вкладка, под КатегорияВыбрать Обычай.
  4. в Тип поле, введите 3 точки с запятой (;;;).
  5. Нажмите OK, чтобы применить пользовательский числовой формат.
    Скрытие номеров

Вот и все! Теперь ваша тепловая карта Excel отображает только цветовые коды без номеров:
Тепловая карта Excel без номеров

Тепловая карта Excel с квадратными ячейками

Еще одно улучшение, которое вы можете внести в свою тепловую карту, — это идеально квадратные ячейки. Ниже приведен самый быстрый способ сделать это без каких-либо скриптов или кодов VBA:

  1. Выровняйте заголовки столбцов по вертикали. Чтобы заголовки столбцов не обрезались, измените их выравнивание на вертикальное. Это можно сделать с помощью программы Ориентация кнопка на Дом вкладка, в Выравнивание группа:
    Выравнивание заголовков столбцов по вертикали

    Дополнительные сведения см. в разделе Как выровнять текст в Excel.

  2. Установить ширину столбца. Выберите все столбцы и перетащите край любого заголовка столбца, чтобы сделать его шире или уже. При этом появится всплывающая подсказка с точным количеством пикселей — запомните это число.
    Настройка ширины столбца
  3. Установить высоту строки. Выберите все строки и перетащите край любого заголовка строки на то же значение пикселей, что и у столбцов (в нашем случае 26 пикселей).
    Установка высоты строки

    Сделанный! Все ячейки вашей карты шляп теперь имеют квадратную форму:
    Тепловая карта Excel с идеально квадратными ячейками

Как сделать тепловую карту в сводной таблице Excel

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

Например, мы добавили продажи Луи в исходную таблицу, обновили сводную таблицу и увидели, что цифры Луи все еще находятся за пределами тепловой карты:
Тепловая карта не включает новые данные.

Как сделать тепловую карту сводной таблицы динамической

Чтобы заставить тепловую карту сводной таблицы Excel автоматически включать новые записи, выполните следующие действия:

  1. Выберите любую ячейку на вашей текущей тепловой карте.
  2. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Управление правилами…
  3. в Диспетчер правил условного форматированиявыберите правило и нажмите кнопку Изменить правило кнопка.
  4. в Изменить правило форматирования диалоговое окно, под Применить правило к, выберите третий вариант. В нашем случае это звучит так: Все ячейки со значениями «Сумма продаж» для «Торговый посредник» и «Продукт»..
  5. Нажмите OK дважды, чтобы закрыть оба диалоговых окна.
    Создание динамической тепловой карты сводной таблицы

Теперь ваша тепловая карта является динамической и будет автоматически обновляться по мере добавления новой информации в бэкэнд. Только не забудьте обновить сводную таблицу 🙂
Динамическая тепловая карта сводной таблицы

Как создать динамическую тепловую карту в Excel с флажком

Если вы не хотите, чтобы тепловая карта была там все время, вы можете скрыть и показать ее в соответствии с вашими потребностями. Чтобы создать динамическую тепловую карту с флажком, выполните следующие действия:

  1. Вставьте флажок. Рядом с вашим набором данных поставьте галочку (управление формой). Для этого нажмите кнопку Разработчик вкладка > Вставлять > Элементы управления формой > Флажок. Вот подробные шаги, чтобы добавить флажок в Excel.
  2. Свяжите флажок с ячейкой. Чтобы связать флажок с определенной ячейкой, щелкните флажок правой кнопкой мыши, щелкните Управление форматомпереключитесь на Контроль вкладку, введите адрес ячейки в поле Сотовая ссылка поле и нажмите кнопку ОК.

    В нашем случае флажок связан с ячейкой O2. При установленном флажке в связанной ячейке отображается ИСТИНА, в противном случае — ЛОЖЬ.
    Флажок и связанная ячейка

  3. Настройте условное форматирование. Выберите набор данных, нажмите Условное форматирование > Цветовые шкалы > Дополнительные правилаи настроить пользовательская цветовая шкала этим способом:
    • в Стиль формата В раскрывающемся списке выберите 3-цветная шкала.
    • Под Минимум, Середина а также Максимумвыберите Формула из Тип выпадающий список.
    • в Ценность поля, введите следующие формулы:

      Для минимума:

      =ЕСЛИ($O$2=ИСТИНА, МИН($B$3:$M$5), ЛОЖЬ)

      Для средней точки:

      =ЕСЛИ($O$2=ИСТИНА, СРЕДНЕЕ($B$3:$M$5), ЛОЖЬ)

      Для максимума:

      =ЕСЛИ($O$2=ИСТИНА, МАКС($B$3:$M$5), ЛОЖЬ)

      В этих формулах используются функции МИН, СРЗНАЧ и МАКС для определения наименьшего, среднего и наибольшего значений в наборе данных (B3:M5), когда связанная ячейка (O2) имеет значение ИСТИНА, т. е. когда установлен флажок.

    • в Цвет раскрывающихся списков выберите нужные цвета.
    • Нажмите кнопку ОК.
      Настройка пользовательской цветовой шкалы

    Теперь тепловая карта появляется только при установленном флажке и скрыта в остальное время.
    Динамическая тепловая карта с флажком

Кончик. Чтобы удалить значение ИСТИНА / ЛОЖЬ из представления, вы можете связать флажок с какой-либо ячейкой в ​​пустом столбце, а затем скрыть этот столбец.

Как сделать динамическую тепловую карту в Excel без цифр

Чтобы скрыть числа на динамической тепловой карте, вам нужно создать еще одно правило условного форматирования, которое применяет пользовательский формат чисел. Вот как:

  1. Создайте динамическую тепловую карту, как описано в приведенном выше примере.
  2. Выберите свой набор данных.
  3. На Дом вкладка, в Стили группа, нажмите Новое правило > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
  4. в Форматировать значения, где эта формула верна введите эту формулу:

    =ЕСЛИ($O$2=ИСТИНА, ИСТИНА, ЛОЖЬ)

    Где O2 — ваша связанная ячейка. Формула говорит применять правило только тогда, когда установлен флажок (O2 имеет значение TRUE).
    Создание правила условного форматирования для скрытия чисел

  5. Нажмите на Формат… кнопка.
  6. в Формат ячеек диалоговое окно, переключитесь на Число вкладка, выберите Обычай в Категория список, введите 3 точки с запятой (;;;) в Тип поле и нажмите кнопку ОК.
    Пользовательский формат номера, чтобы скрыть номера
  7. Нажмите OK, чтобы закрыть Новое правило форматирования диалоговое окно.

С этого момента установка флажка будет отображать тепловую карту и скрывать числа:
Динамическая тепловая карта без номеров

Чтобы переключаться между двумя разными типами тепловых карт (с числами и без них), вы можете вставить три переключателя. Затем настройте 3 отдельных правила условного форматирования: 1 правило для тепловой карты с числами и 2 правила для тепловой карты без чисел. Или вы можете создать общее правило цветовой шкалы для обоих типов, используя функцию ИЛИ (как это сделано в нашем образце рабочего листа ниже).

В результате вы получите вот такую ​​красивую динамическую тепловую карту:
Динамическая тепловая карта в Excel

Чтобы лучше понять, как это работает, вы можете скачать наш образец листа. Надеюсь, это поможет вам создать свой собственный удивительный шаблон тепловой карты Excel.

Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Тепловая карта в Excel — примеры (файл .xlsx)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *