Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Создать диаграмму тепловой карты в Excel

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


Создайте простую диаграмму тепловой карты с условным форматированием

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

1. Выберите диапазон данных, к которому вы хотите применить Условное форматирование.

2, Затем нажмите Главная > Условное форматирование > Цветовые шкалы, а затем выберите нужный стиль из раскрывающегося справа раскрывающегося списка (в данном случае я выберу Зеленый - желтый - красный цветовая шкала) см. снимок экрана:

3. Теперь создается тепловая карта, которая выделяет ячейки на основе их значений, зеленый цвет представляет самые высокие значения, а красный цвет представляет самые низкие значения, а остальные значения показывают цвет градиента между зеленым и красным. Смотрите скриншот:

4. Если вы хотите скрыть числа и оставить только цвета, выберите диапазон данных и нажмите Ctrl + 1 , чтобы открыть Формат ячеек диалоговое окно.

5. В Формат ячеек диалоговое окно под Число вкладку нажмите На заказ вариант слева Категория список, а затем введите ;;; в Тип текстовое поле, см. снимок экрана:

6, Затем нажмите OK кнопку, и все числа скрыты, как показано на скриншоте ниже:

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

Затем дважды щелкните существующее правило, чтобы открыть Изменить правило форматирования диалоговое окно, а затем сбросьте правило в соответствии с вашими потребностями, см. снимок экрана:


Создать динамическую диаграмму тепловой карты в Excel

Пример 1. Создание динамической тепловой карты с помощью полосы прокрутки

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

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

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

2, Затем нажмите разработчик > Вставить > Полоса прокрутки, см. снимок экрана:

3. Затем перетащите мышь, чтобы нарисовать полосу прокрутки под скопированными данными, щелкните полосу прокрутки правой кнопкой мыши и выберите Управление форматом, см. снимок экрана:

4. В Форматировать объект диалоговое окно под Control На вкладке установите минимальное значение, максимальное значение, инкрементное изменение, изменение страницы и связанную ячейку на основе вашего диапазона данных, как показано ниже:

5. Затем нажмите OK чтобы закрыть это диалоговое окно.

6. Теперь в ячейке B1 этого нового листа введите следующую формулу и нажмите Enter ключ для получения первого результата:

=INDEX(data1!$B$1:$I$13,ROW(),$I$1+COLUMNS($B$1:B1)-1)

Внимание: В приведенной выше формуле data1! $ B $ 1: $ I $ 13 - исходный лист с диапазоном данных без заголовка строки (месяцы), $ I $ 1 это ячейка, с которой связана полоса прокрутки, $ B $ 1: B1 это ячейка, в которую вы выводите формулу.

7. Затем перетащите эту ячейку формулы в остальные ячейки, если вы хотите, чтобы на листе отображалось только 3 года, перетащите формулу из B1 в D13, см. Снимок экрана:

8. А затем примените Цветовая шкала Условное форматирование в новый диапазон данных для создания тепловой карты, теперь, когда вы перетаскиваете полосу прокрутки, тепловая карта будет перемещаться динамически, см. снимок экрана:


Пример 2: Создание динамической тепловой карты с помощью радиокнопок

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

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

1. Нажмите разработчик > Вставить > Кнопка выбора (элемент управления формой), затем перетащите мышь, чтобы нарисовать две переключатели, и отредактируйте текст по своему усмотрению, см. снимок экрана:

2. После вставки переключателей щелкните правой кнопкой мыши первую и выберите Управление форматом, В Управление форматом диалоговое окно под Control вкладку, выберите ячейку, которая привязана к переключателю, см. снимок экрана:

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 ячейка связана с переключателями, 2 доллара: 13 иракских долларов это диапазон данных, к которому вы хотите применить условное форматирование, B2 - первая ячейка диапазона данных, число 15 - это конкретный номер, который вы хотите выделить.

8. Нажмите OK чтобы закрыть диалоговое окно, теперь при выборе первого переключателя будут выделены 15 наибольших значений, а при выборе второго переключателя будут выделены 15 наименьших значений, как показано ниже:


Пример 3. Создайте динамическую тепловую карту с помощью флажка.

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

1. Во-первых, вы должны преобразовать диапазон данных в формат таблицы, который поможет вам автоматически применить условное форматирование при вставке новой строки данных. Выберите диапазон данных и нажмите Ctrl + T, , чтобы открыть Создать таблицу диалоговое окно, см. снимок экрана:

2. Нажмите OK чтобы закрыть диалоговое окно, а затем щелкните разработчик > Вставить > Флажок (контроль формы), затем перетащите мышь, чтобы установить флажок и отредактировать текст в соответствии с вашими потребностями, как показано ниже:

3. Затем щелкните правой кнопкой мыши флажок и выберите Управление форматом, В Форматировать объект диалоговое окно под Control вкладку, выберите ячейку, которая выровнена с флажком, см. снимок экрана:

4. Нажмите OK чтобы закрыть диалоговое окно, затем выберите диапазон данных, который вы хотите создать тепловую карту, и нажмите Главная > Условное форматирование > Новое правило , чтобы перейти к Новое правило форматирования диалоговое окно.

5. В Новое правило форматирования диалоговом окне выполните следующие действия:

  • Выберите Отформатируйте все ячейки на основе их значений из файла Выберите тип правила список;
  • Выберите 3-х цветная шкала из Стиль формата раскрывающийся список;
  • Выберите Формула в Тип коробки под минимальный, средняя точка и максимальная выпадающие списки отдельно;
  • Затем введите следующие формулы в три Значение текстовые поля:
  • Минимум: = ЕСЛИ ($ M $ 1 = ИСТИНА, МИН ($ B $ 2: $ I $ 13), ЛОЖЬ)
  • Середина: = ЕСЛИ ($ M $ 1 = ИСТИНА, СРЕДНЕЕ ($ B $ 2: $ I $ 13), ЛОЖЬ)
  • Максимум: = ЕСЛИ ($ M $ 1 = ИСТИНА, МАКС ($ B $ 2: $ I $ 13), ЛОЖЬ)
  • Затем укажите цвета выделения из цвет раздел для ваших нужд.

Внимание: В приведенных выше формулах $ M $ 1 это ячейка, которая связана с флажком, 2 доллара: 13 иракских долларов - это диапазон данных, к которому вы хотите применить условное форматирование.

6. После завершения настройки нажмите OK кнопку, чтобы закрыть диалоговое окно, теперь, когда вы поставите галочку, тепловая карта будет отображаться, в противном случае она будет скрыта. См. Демонстрацию ниже:


Скачать образец файла тепловой карты


Видео: создание диаграммы тепловой карты в Excel


Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее ...) и экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    S. Wakasugi · 4 months ago
    sorry,I cancel.