如何在Excel中创建热力图
了解如何使用真实数据、简便方法和轻松步骤在 Excel 中创建热图。
Microsoft Excel 为数据科学家、分析师、数学家和统计学家提供了迄今为止已知的最佳数据可视化技术。这些热图是使用颜色渐变在 2D 平面上可视化数据的绝佳工具。
当数据中某些块或扇区的值增加时,某些颜色的强度也会相应增加。因此,您可以轻松找出哪个块包含最多的值。
例如,作为零售店主,您想了解在特定季节哪些产品销量更高。借助热图,您可以轻松地直观地识别产品,而不必用眼睛扫描包含多行数据的表格。
与我一起了解绘制 Excel 热图最流行和最实用的方法。
推荐:在Microsoft Excel中添加图表轴标签的7种方法
使用条件格式创建 Excel 热图
Excel条件格式的颜色标度单元格格式规则允许您立即创建热图,前提是您选择受支持的数据集。
您的数据必须包含可以清楚区分的值,例如收入、销售额、分数等。除了整数或分数值外,您还可以使用百分比、比率等。
例如,上述数据集非常适合直观地展示各国在石油和天然气、粮食、服装、电信等指标方面的相互比较。
如果您有这样的数据集,请转到您的工作表并突出显示所有包含值的行和表。
单击条件格式下拉菜单。现在,将光标置于色阶菜单上,然后单击 12 组内可用的任意颜色组合。
Excel 将根据单元格和列中的值自动在目标数据集上生成热图。
色阶规则将加权平均系统应用于比较列的每个单元格。因此,阴影的强度会随着特定值的增加或减少而增加。
无数字的热图
如果您希望隐藏热图的值,请突出显示包含数值的单元格范围。
按Ctrl+1调出“设置单元格格式”对话框。
从左侧导航面板导航到自定义数字格式类别。
单击“类型”字段并输入此代码:;;;
。单击“确定”以应用数字格式修改。
Excel 将隐藏热图单元格上的数字。
使用自定义颜色标度创建 Excel 热图
如果内置的色阶规则无法将您的数据集准确可视化为热图,您可以自定义底层配置。这样,您的热图就会变得更加精确和易于理解。
通过个性化颜色标度规则,您还可以找出最低点、中值和最高点使用的颜色代码。您可以将颜色代码放在表格中作为图例,以便观众知道如何解释热图上不同色调的强度。
让我向您展示如何为您的数据集创建自定义颜色尺度规则。
突出显示目标数据集并单击条件格式按钮。在上下文菜单中,将鼠标光标悬停在突出显示单元格规则上,然后单击溢出菜单底部的更多规则选项。
Excel 将显示“新建格式规则”对话框。在这里,单击“根据单元格值格式化所有单元格”选项,以在对话框底部调出“编辑规则描述”菜单。
在编辑规则描述中,您需要根据输入数据集自定义以下字段:
- 格式样式:在2 色标尺和3 色标尺选项之间进行选择。在 3 色标尺中,您可以自定义整个数据集的三个点(即最小值、中点和最大值 )的单元格填充颜色。因此,3 色标尺选项比 2 色标尺选项更能直观地显示数据集。
- 类型:单击最小值、中点和最大值下方的类型下拉菜单,然后选择最低值、百分位数和最高值等选项。
- 颜色:单击最小值、中点和最大值的颜色下拉菜单,选择哪种色调应表示哪种值范围。
完成自定义颜色标度规则的配置后,单击“确定”按钮将单元格格式样式应用到您的数据集。
最后,Excel 会为您创建热图。创建一个表格来指示颜色及其代表的数值范围。
推荐:如何在Excel中计算百分比变化
使用等高线图创建 Excel 热图
假设您正在分析地形数据集。您想在三维空间的热图中可视化数据。在这种情况下,您可以使用“更改图表类型”工具的“表面”类别中的“等高线图” 。
由于等高线图在X、Y和Z维度上绘制数据,因此输入数据集不应包含超过三列数值。此外,输入数据集应同时具有列标题和行标题。上面显示了一个示例数据集。
您也可以包含超过三列。但是,在这种情况下,输出的等高线图会变得更加复杂。
选择输入数据集并单击插入选项卡。找到并单击推荐图表按钮。
现在您将看到“插入聊天”对话框。在那里,导航到“所有图表”选项卡。
单击左侧导航面板中的“Surface”类别。
在右侧,单击“轮廓”选项。单击“确定”创建图表。
生成图表后,您可以从“图表设计”选项卡对其进行自定义。例如,单击“切换行/列”按钮可在轴上交换数据。
或者,您可以单击“图表设计”选项卡上的“选择数据”按钮来自定义当前图表对象中包含的数据范围。
上面是一张由 8 个数据列创建的更广泛的轮廓图。
使用地图创建 Excel 热图
如果输入数据集包含地理数据,则可以使用 Excel 地图图表创建热图。由于数据绘制在数据集中包含的地理或位置的地图上,因此您的受众可以轻松掌握您想要传达的基本信息。
上面给出了适用于Excel 地图图表的示例数据集。您可以包括州名称和其中一个数据列,例如人口(百万),以创建热图,通过在美国 2D 地图上进行颜色编码来了解人口密度。
一旦为人口(百万)数据列创建了热图,您就可以使用其他数据,例如识字率(%)和GDP(十亿美元)来制作两个单独的热图。
现在,您无需通过人口(百万)、识字率(%)和 GDP(十亿美元)来呈现美国各州的表格数据比较,而是可以通过热图来更好地吸引观众的注意力和理解。
创建地图图表非常简单。选择数据集,然后单击“插入”选项卡的“图表”块内的“地图”下拉菜单。
在“地图”下拉菜单中,选择“填充地图”选项。
Excel 将通过分析输入数据集中的地理位置自动选择地图并绘制热图。
Microsoft Excel 使用 Bing Maps API 创建热图。因此,您可能会收到允许 Excel 与 Bing Maps 共享数据集的提示。
默认情况下,Excel 使用顺序(2 色)选项来可视化图表数据系列。您可以将其更改为发散(3 色)以使热图更加全面。您的观众将看到用于为地图上的州着色的三个级别的数据点,例如最低值、中间值和最高值。
要修改系列选项,请双击彩色地图以调出Excel 应用程序右侧的“格式化数据点”导航面板。
在那里,单击“系列颜色”下拉菜单并切换到“发散(3 色)”选项。
在这里,您可以通过单击“最小值”、“中点”和“最大值”字段的单元格填充颜色下拉菜单来自定义各州的颜色编码。
要绘制下一列数据,您无需从头开始。将第一张图表导出为图像后,单击地图一次。这应该会突出显示输入数据范围。
单击人口(百万)数据集的边缘并将其拖到下一个数据列。这应该会在同一张地图上绘制识字率(%)列的数据点。
要自定义此地图,请按前面的说明调出“格式化数据系列”控制台。
使用 3D 地图创建 Excel 热图
通常,您可能无法在 2D 热图中找到所需的确切数据洞察。在这种情况下,您可以使用 Excel 3D 地图。此功能仅在 Excel 2013 或更高版本中可用。
突出显示输入数据,最好是地理数据,然后单击“插入”选项卡的“旅游”块中的“3D 地图”下拉菜单。
现在,从打开的上下文菜单中单击打开 3D 地图选项。
Excel 3D 地图工具将打开。在那里,您将在 3D 地球仪上看到美国的空白地图。
单击“添加图层”按钮以显示“数据”面板。在那里,单击“热图”选项卡。
单击位置字段内的添加字段按钮。从下拉菜单中选择州。Excel 从输入数据集中捕获州的名称。
现在,单击“值”字段内的“添加字段”按钮,然后选择输入数据集中的任何一列数据,例如“人口(百万)”。Excel 3D 地图将创建一个基本的热图。
为了使此热点图更加美观,请单击图层选项下拉菜单并增加以下字段的值:
- 色阶
- 影响半径
如果您希望更改热图的阴影,请单击“颜色”部分中的“默认”下拉菜单并选择“自定义”。
您可以通过点击填充颜色下拉框来更改低点和高点颜色。您还可以点击添加颜色按钮为热图中绘制的数据点添加中点。现在,您可以为热图添加第三种颜色。
要将热图导出到另一个应用程序(例如 Microsoft Word),您可以单击“主页”选项卡的“游览”块中的“捕获屏幕”按钮。
现在,转到目标 Word 文件并按Ctrl+V将复制的数据粘贴为图像。
推荐:Microsoft Excel中安装Solver求解的2种方法
使用 Excel VBA 创建 Excel 热图
如果您希望避免选择数据范围、查找热图工具并将其配置为完成的可视化的手动步骤,则可以使用Excel 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。
您必须在随后的提示中输入源数据集。
然后,您还可以选择热图的颜色编码系统。
最后,Excel 将源数据集重新格式化为热图。
结论
到目前为止,您已经探索了在 Excel 中创建热图的六种不同方法。如果您偶尔需要在小数据集中应用此技能,则可以使用基于条件格式的方法。
如果输入数据集包含地理和人口统计数据,请使用基于Excel 地图或Excel 3D 地图的方法。
最后,如果您希望为大型数据集频繁创建热图,请使用基于Excel VBA的方法。