如何在Excel中创建热力图

如何在Excel中创建热力图

了解如何使用真实数据、简便方法和轻松步骤在 Excel 中创建热图。

Microsoft Excel 为数据科学家、分析师、数学家和统计学家提供了迄今为止已知的最佳数据可视化技术。这些热图是使用颜色渐变在 2D 平面上可视化数据的绝佳工具。

当数据中某些块或扇区的值增加时,某些颜色的强度也会相应增加。因此,您可以轻松找出哪个块包含最多的值。

例如,作为零售店主,您想了解在特定季节哪些产品销量更高。借助热图,您可以轻松地直观地识别产品,而不必用眼睛扫描包含多行数据的表格。

与我一起了解绘制 Excel 热图最流行和最实用的方法。

推荐:在Microsoft Excel中添加图表轴标签的7种方法

使用条件格式创建 Excel 热图

Excel条件格式的颜色标度单元格格式规则允许您立即创建热图,前提是您选择受支持的数据集。

您的数据必须包含可以清楚区分的值,例如收入、销售额、分数等。除了整数或分数值外,您还可以使用百分比、比率等。

如何在Excel中创建热力图
热图样本数据集

例如,上述数据集非常适合直观地展示各国在石油和天然气、粮食、服装、电信等指标方面的相互比较。

如果您有这样的数据集,请转到您的工作表并突出显示所有包含值的行和表。

色阶规则
色阶规则

单击条件格式下拉菜单。现在,将光标置于色阶菜单上,然后单击 12 组内可用的任意颜色组合。

Excel 将根据单元格和列中的值自动在目标数据集上生成热图。

色阶规则将加权平均系统应用于比较列的每个单元格。因此,阴影的强度会随着特定值的增加或减少而增加。

无数字的热图

格式化单元格
格式化单元格

如果您希望隐藏热图的值,请突出显示包含数值的单元格范围。

Ctrl+1调出“设置单元格格式”对话框。

从左侧导航面板导航到自定义数字格式类别。

单击“类型”字段并输入此代码:;;;。单击“确定”以应用数字格式修改。

从热图中删除数字
从热图中删除数字

Excel 将隐藏热图单元格上的数字。

使用自定义颜色标度创建 Excel 热图

如果内置的色阶规则无法将您的数据集准确可视化为热图,您可以自定义底层配置。这样,您的热图就会变得更加精确和易于理解。

通过个性化颜色标度规则,您还可以找出最低点、中值和最高点使用的颜色代码。您可以将颜色代码放在表格中作为图例,以便观众知道如何解释热图上不同色调的强度。

让我向您展示如何为您的数据集创建自定义颜色尺度规则

更多规则选项
更多规则选项

突出显示目标数据集并单击条件格式按钮。在上下文菜单中,将鼠标光标悬停在突出显示单元格规则上,然后单击溢出菜单底部的更多规则选项。

根据单元格的值格式化所有单元格
根据单元格的值格式化所有单元格

Excel 将显示“新建格式规则”对话框。在这里,单击“根据单元格值格式化所有单元格”选项,以在对话框底部调出“编辑规则描述”菜单。

在编辑规则描述中,您需要根据输入数据集自定义以下字段:

格式样式
格式样式
  • 格式样式:在2 色标尺3 色标尺选项之间进行选择。在 3 色标尺中,您可以自定义整个数据集的三个点(即最小值中点最大值 )的单元格填充颜色。因此,3 色标尺选项比 2 色标尺选项更能直观地显示数据集。
输入颜色标尺
输入颜色标尺
  • 类型:单击最小值中点最大值下方的类型下拉菜单,然后选择最低值百分位数最高值等选项。
在色标中选择颜色
在色标中选择颜色
  • 颜色:单击最小值中点最大值的颜色下拉菜单,选择哪种色调应表示哪种值范围。

完成自定义颜色标度规则的配置后,单击“确定”按钮将单元格格式样式应用到您的数据集。

使用自定义颜色标尺
使用自定义颜色标尺

最后,Excel 会为您创建热图。创建一个表格来指示颜色及其代表的数值范围。

推荐:如何在Excel中计算百分比变化

使用等高线图创建 Excel 热图

假设您正在分析地形数据集。您想在三维空间的热图中可视化数据。在这种情况下,您可以使用“更改图表类型”工具的“表面”类别中的“等高线图” 。

等高线图的示例数据集
等高线图的示例数据集

由于等高线图在XYZ维度上绘制数据,因此输入数据集不应包含超过三列数值。此外,输入数据集应同时具有列标题和行标题。上面显示了一个示例数据集。

您也可以包含超过三列。但是,在这种情况下,输出的等高线图会变得更加复杂。

推荐图表
推荐图表

选择输入数据集并单击插入选项卡。找到并单击推荐图表按钮。

所有图表
所有图表

现在您将看到“插入聊天”对话框。在那里,导航到“所有图表”选项卡。

表面轮廓图
表面轮廓图

单击左侧导航面板中的“Surface”类别。

等高线图
等高线图

在右侧,单击“轮廓”选项。单击“确定”创建图表。

切换行列
切换行列

生成图表后,您可以从“图表设计”选项卡对其进行自定义。例如,单击“切换行/列”按钮可在轴上交换数据。

自定义数据源
自定义数据源

或者,您可以单击“图表设计”选项卡上的“选择数据”按钮来自定义当前图表对象中包含的数据范围。

更复杂的等高线图
更复杂的等高线图

上面是一张由 8 个数据列创建的更广泛的轮廓图。

使用地图创建 Excel 热图

如果输入数据集包含地理数据,则可以使用 Excel 地图图表创建热图。由于数据绘制在数据集中包含的地理或位置的地图上,因此您的受众可以轻松掌握您想要传达的基本信息。

Excel 地图的示例数据集
Excel 地图的示例数据集

上面给出了适用于Excel 地图图表的示例数据集。您可以包括州名称和其中一个数据列,例如人口(百万),以创建热图,通过在美国 2D 地图上进行颜色编码来了解人口密度。

一旦为人口(百万)数据列创建了热图,您就可以使用其他数据,例如识字率(%)GDP(十亿美元)来制作两个单独的热图。

现在,您无需通过人口(百万)、识字率(%)和 GDP(十亿美元)来呈现美国各州的表格数据比较,而是可以通过热图来更好地吸引观众的注意力和理解。

Excel 地图图表
Excel 地图图表

创建地图图表非常简单。选择数据集,然后单击“插入”选项卡的“图表”块内的“地图”下拉菜单。

填充地图
填充地图

在“地图”下拉菜单中,选择“填充地图”选项。

Excel 将通过分析输入数据集中的地理位置自动选择地图并绘制热图。

Microsoft Excel 使用 Bing Maps API 创建热图。因此,您可能会收到允许 Excel 与 Bing Maps 共享数据集的提示。

默认情况下,Excel 使用顺序(2 色)选项来可视化图表数据系列。您可以将其更改为发散(3 色)以使热图更加全面。您的观众将看到用于为地图上的州着色的三个级别的数据点,例如最低值、中间值和最高值。

发散(3色)
发散(3色)

要修改系列选项请双击彩色地图以调出Excel 应用程序右侧的“格式化数据点”导航面板。

在那里,单击“系列颜色”下拉菜单并切换到“发散(3 色)”选项。

填充颜​​色下拉菜单
填充颜​​色下拉菜单

在这里,您可以通过单击“最小值”“中点”“最大值”字段的单元格填充颜色下拉菜单来自定义各州的颜色编码。

点击热图
点击热图

要绘制下一列数据,您无需从头开始。将第一张图表导出为图像后,单击地图一次。这应该会突出显示输入数据范围。

识字率(%)热图
识字率(%)热图

单击人口(百万)数据集的边缘并将其拖到下一个数据列。这应该会在同一张地图上绘制识字率(%)列的数据点。

格式化第二列的数据系列
格式化第二列的数据系列

要自定义此地图,请按前面的说明调出“格式化数据系列”控制台。

使用 3D 地图创建 Excel 热图

通常,您可能无法在 2D 热图中找到所需的确切数据洞察。在这种情况下,您可以使用 Excel 3D 地图。此功能仅在 Excel 2013 或更高版本中可用。

打开 3D 地图
打开 3D 地图

突出显示输入数据,最好是地理数据,然后单击“插入”选项卡的“旅游”块中的“3D 地图”下拉菜单。

现在,从打开的上下文菜单中单击打开 3D 地图选项。

Excel 3D 地图工具
Excel 3D 地图工具

Excel 3D 地图工具将打开。在那里,您将在 3D 地球仪上看到美国的空白地图。

热图
热图

单击“添加图层”按钮以显示“数据”面板。在那里,单击“热图”选项卡。

所在地州
所在地州

单击位置字段内的添加字段按钮。从下拉菜单中选择州。Excel 从输入数据集中捕获州的名称。

现在,单击“值”字段内的“添加字段”按钮,然后选择输入数据集中的任何一列数据,例如“人口(百万)”。Excel 3D 地图将创建一个基本的热图。

更改图层选项
更改图层选项

为了使此热点图更加美观,请单击图层选项下拉菜单并增加以下字段的值:

  • 色阶
  • 影响半径
颜色自定义
颜色自定义

如果您希望更改热图的阴影,请单击“颜色”部分中的“默认”下拉菜单并选择“自定义”。

您可以通过点击填充颜色下拉框来更改低点高点颜色。您还可以点击添加颜色按钮为热图中绘制的数据点添加中点。现在,您可以为热图添加第三种颜色。

截屏
截屏

要将热图导出到另一个应用程序(例如 Microsoft Word),您可以单击“主页”选项卡的“游览”块中的“捕获屏幕”按钮。

将 3D 地图复制到 Word

现在,转到目标 Word 文件并按Ctrl+V将复制的数据粘贴为图像。

推荐:Microsoft Excel中安装Solver求解的2种方法

使用 Excel VBA 创建 Excel 热图

如果您希望避免选择数据范围、查找热图工具并将其配置为完成的可视化的手动步骤,则可以使用Excel VBA。VBA 宏可以帮助您自动执行此任务。

下面是一个你可以使用的简单脚本:

VBA 脚本创建热图
VBA 脚本创建热图
Sub CreateHeatMap()
    ' Prompt the user to select a range of cells
    Dim rng As Range
    Set rng = Application.InputBox("Select a range", Type:=8)
    
    ' Prompt the user to choose between 2-color and 3-color heat map
    Dim colorScale As Integer
    colorScale = Application.InputBox("Enter 2 for 2-color heat map, or 3 for 3-color heat map", Type:=1)
    
    ' Apply the chosen color scale to the selected cells
    With rng.FormatConditions
        .Delete
        Select Case colorScale
            Case 2
                .AddColorScale(2)
                .Item(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
                .Item(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 255)
                .Item(1).ColorScaleCriteria(2).Type = xlConditionValueHighestValue
                .Item(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 0, 0)
            Case 3
                .AddColorScale(3)
                .Item(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
                .Item(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 255)
                .Item(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
                .Item(1).ColorScaleCriteria(2).Value = 50
                .Item(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 0)
                .Item(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
                .Item(1).ColorScaleCriteria(3).FormatColor.Color = RGB(255, 0, 0)
        End Select
    End With
End Sub

要使用上述脚本设置 VBA 宏,请按照本文中概述的说明进行操作:

创建热图宏
创建热图宏

如果宏已准备好,请按Alt+F8进入对话框。选择CreateHeatMap宏并点击Run

数据集输入框
数据集输入框

您必须在随后的提示中输入源数据集。

颜色标度输入框
颜色标度输入框

然后,您还可以选择热图的颜色编码系统。

使用 VBA 的 Excel 热图
使用 VBA 的 Excel 热图

最后,Excel 将源数据集重新格式化为热图。

结论

到目前为止,您已经探索了在 Excel 中创建热图的六种不同方法。如果您偶尔需要在小数据集中应用此技能,则可以使用基于条件格式的方法。

如果输入数据集包含地理和人口统计数据,请使用基于Excel 地图Excel 3D 地图的方法。

最后,如果您希望为大型数据集频繁创建热图,请使用基于Excel VBA的方法。

推荐:TikTok上的缓存是什么


发表评论