如何在Excel中创建热贴图 - 逐步指南

Excel中的热图是可视表示,可快速向您展示数据集的比较视图。

例如,在下面的数据集中,我可以轻松地点发现销售量低的月份(以红色突出显示)与其他月相比。

Excel中的热贴图 -  DataSet

在上面的数据集中,根据单元格中的值分配颜色。颜色刻度为绿色至黄色到红色,具有高值,获得绿色和低值获得红色。

在Excel中创建热图

虽然您可以通过手动颜色编码单元格在Excel中创建热贴图。但是,在值更改时,您必须重做。

您可以使用而不是手动工作条件格式基于该值突出显示单元格。这样,如果您更改单元格中的值,则单元格的颜色/格式将基于条件格式化的预先指定的规则自动更新热图。

In this tutorial, you’ll learn how to:

  • 使用条件格式快速在Excel中创建热图。
  • 在Excel中创建一个动态热图。
  • 在Excel枢轴表中创建一个热图。

让我们开始吧!

在Excel中创建热图Using Conditional Formatting

If you have a dataset in Excel, you can manually highlight data points and create a heat map.

但是,这将是静态热图,因为在更改单元中的值时颜色不会改变。

因此,条件格式化是正确的方法,因为当您更改其中的值时,它会在单元格更改中的颜色。

假设您有一个数据集,如下所示:

Excel中的热图 -  DataSet Plain

以下是使用此数据创建热图的步骤:

  • 选择数据集。在这个例子中,它将是B2:D13。Heat Map in Excel - Select Data set
  • Go to Home –> Conditional Formatting –> Color Scales. It shows various color combinations that can be used to highlight the data. The most common color scale is the first one where cells with high values are highlighted in green and low in red. Note that as you hover the mouse over these color scales, you can see the live preview in the data set.
    Heat Map in Excel - color scale

这将为您提供热图,如下所示:

Heat Map in Excel - result color scales

默认情况下,Excel将红色赋值为最低值和绿色到最高值,并且所有剩余值都会根据值获取颜色。因此,基于该值,有三种颜色的不同阴影存在渐变。

现在,如果不想要渐变,只想显示红色,黄色和绿色的东西。例如,您希望突出显示除了值的红色比700的所有值,而不管该值。因此,500和650都获得了相同的红颜色,因为它少于700。

去做这个:

  • 回到家 - >条件格式化 - >颜色尺度 - >更多选项。Excel中的热图 - 更多选项
  • 在“新建格式化规则”对话框中,从“格式样式”下拉格式中选择“3色标度”。Heat Map in Excel - 3 color scale
  • Now you can specify the minimum, midpoint, and the maximum value and assign the color to it. Since we want to highlight all the cells with a value below 700 in red, change the type to Number and value to 700.Excel的热图 - 最低700
  • 单击确定。

现在,您将获得如下所示的结果。请注意,700以下的所有值都获得相同的红色色调。

Excel的热图 - 最低700result

奖金提示:Want to show only the colors and not the values in the cells. To do this, select all the cells and press Control + 1. It will open the Format Cells dialog box. In the Number tab, select Custom and enter;;;;在右边的领域。

一个小心:虽然条件格式是一个很棒的工具,但不幸的是,它是易挥发的。这意味着每当工作表中有任何更改时,会重新计算条件格式。虽然在小数据集上的影响可能会忽略不计,但它可能会导致一个慢速Excel工作簿when working with large data sets.

在Excel中创建动态热图

由于条件格式依赖于更改值,条件格式重新计算和更改后立即取决于单元格中的值。

这使得可以进行动态热图。

让我们来看看在Excel中使用交互式控件创建热图的两个例子。

示例1:使用滚动条热图

Here is an example where the heat map changes as soon as you use thescroll bar改变一年。

Excel中的热图 - 滚动条演示

This type of dynamic heat maps can be used in dashboards where you have space constraints but still want the user to access the entire data set.

点击这里下载热情地图模板

How to create this dynamic heat map?

以下是用于创建此动态热图的完整数据集。

Excel中的热图 - 动态热图数据集

以下是步骤:

  • 在新纸张(或在同一表中)中,输入月份名称(只需将粘贴它从原始数据复制粘贴即可。
  • 转到开发人员 - >控件 - >插入 - >滚动条。现在单击工作表中的任意位置,它将插入滚动条。(如果找不到的话,请点击这里developer tab)。Excel中的热图 - 滚动条插入
  • 右键单击滚动条,然后单击格式控制。Heat Map in Excel - Format Controls
  • 在“格式控制”对话框中,进行以下更改:
    • 最小值:1
    • Maximum Value 5
    • Cell Link:Sheet1!$ J $ 1(您可以单击右侧的图标,然后手动选择要链接到滚动条的单元格)。Excel中的热图 - 格式控制详细信息
  • 单击确定。
  • 在Cell B1中,输入公式:=指数(表格1!$ B $ 1:$ H $ 13,ROW(),表1!$ j $ 1 +(表2!$ B $ 1:B1)-1)
  • 调整大小并将滚动条放置在数据集的底部。

现在,当您更改滚动条时,表格1中的值!$ j $ 1将更改,并且由于公式链接到此单元格,因此它将更新以显示正确的值。

Excel的动态热图 - 滚动条演示

此外,由于条件格式是易失性的,一旦值更改,它也会更新。

Watch Video – Dynamic Heat Map in Excel

示例2:使用单选按钮在Excel中创建动态热映射

以下是您可以通过制作单选按钮选择来更改热图的另一个示例:

使用单选按钮在Excel中自动更新热图

在此示例中,您可以基于以下突出显示顶部/底部10值radio/option button选择。

点击这里下载热情地图模板

在Excel枢轴表中创建热图

条件格式在枢轴表中与任何正常数据相同的方式。

But there is something important you need to know.

让我举个例子并告诉你。

假设您有一个枢轴表,如下所示:

Heat Map in Excel - Pivot Table Data

在此Excel枢轴表中创建热图:

  • 选择单元格(B5:D14)。
  • 转到首页 - >条件格式化 - >颜色尺度并选择要申请的颜色比例。Excel中的热图 - 条件格式化尺度

This would instantly create the heat map in the pivot table.

Excel中的热图 - 枢轴表条件格式应用

此方法的问题是,如果在后端中添加新数据并刷新此枢轴表,则不适用于新数据的条件格式。

例如,由于我在后端添加了新数据时,调整源数据并刷新了枢轴表,您可以看到不适用于其条件格式。

Excel热图 - 枢轴表条件格式不适用于

当我们将条件格式应用于单元格B5:D14时,会发生这种情况。

如果您希望此热量贴图是动态的,可以在添加新数据时更新,以下是步骤:

  • 选择单元格(B5:D14)。
  • 转到首页 - >条件格式化 - >颜色尺度并选择要申请的颜色比例。在Excel中创建热图 - 条件格式化尺度
  • Again go to Home –> Conditional Formatting –> Manage rules.在Excel  - 管理规则中创建热图
  • In the Conditional Formatting Rules Manager, click on the Edit button.在Excel中生成热图 - 编辑规则
  • 在“编辑格式规则”对话框中,选择第三种选项:显示“日期”和“客户”的“销售”值的所有单元格。

现在,当您更改后端数据时,条件格式将更新。

笔记:如果更改行/列字段,条件格式会消失。例如,如果删除日期字段并再次应用它,则会丢失条件格式。

You May Also Like the Following Excel Tutorials:

Excel电子书订阅

FREE EXCEL BOOK

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作

23关于“如何在Excel中创建热贴图的思考 - 逐步指导”

  1. 你好,
    非常乐于助人,你非常!如果我想从热贴图单元中删除数字,何时绘制颜色怎么办?

  2. 你好,

    我正试图在Excel中建立一个风险热图,我确实在努力奋斗,是您在Excel中的风险热图中有任何教程

  3. 谢谢你的帖子。我没有意识到Excel建立在热爱功能,非常聪明!

  4. 有人可以解释一下这段枢轴表热图给我吗?似乎它缺少步骤......

    “In the Edit Formatting Rule dialog box, select the third option: All cells showing ‘Sales’ values for ‘Date’ and ‘Customer’.”

  5. If columns in the table represent different entities like apple, milk, car, pollution (in first diagram of this webpage) which has different scale as well as units. How can excel be employed to develop their heat map with respect to months? How can Colors obtained be averaged out ? Like April has green, yellow, red for 2015,2016,2017. These averaged to yellow color. Reply sooner. Thank you

  6. 你好。谢谢你这个漂亮的信息。这真的很有帮助。
    我只是想加一件事。
    我刚刚尝试不在每个单元格中显示值。
    这必须只有3个;要键入自定义编号字段。
    而不是4的;;;;
    我只是想help whoever may be stuck with it.

  7. 我想使用热图来指示项目的状态。示例:在进度=绿色,未启动=黄色,升级=红色,完成=绿色。我该怎么做?

  8. 嗨,感谢热图信息。我如何在我选择的图像中显示每个间隔的热贴数据,即大脑的图片和4个头位置中的每一个的每秒值,然后将数千个间隔(秒)的文件作为电影播放?

  9. 嗨Sumit,您是否知道改变条件格式化长式公式的更好方法?使用箭头键滚动到TINY框中的右侧或左侧,从而引用在工作表中选择的单元格的额外代码。我知道点击并按住播放在框的EGDES上工作,但它非常耗时。

    • 你好..这是我所做的。单击有公式的框中时,按F2键。现在,当您使用箭头键时,它不会生成额外的小区引用。

  10. 嗨Sumit,您是否知道改变条件格式化长式公式的更好方法?使用箭头键滚动到TINY框中的右侧或左侧,从而引用在工作表中选择的单元格的额外代码。我知道点击并按住播放在框的EGDES上工作,但它非常耗时。

评论被关闭。