第3章　制作自己的数据地图（上）

在接下来的3节里，我们将介绍3种制作填色型数据地图的方法。这3种做法各有其技术特点和表现效果，可适用于不同分析场景下的需求。

3.1　分档填色经典做法

最常见的数据地图形式是分档填色型热力地图（HeatMap）。由于国内目前尚无权威译名，本书把HeatMap翻译为热力地图，作为一种可视化技术，有时也直接称做HeatMap。分档填色型热力地图是将各区域按数值分为几个层级，不同层级的图形填充不同的颜色。

作图思路

将指标数据的范围划分为几个分档区间，分别准备不同的填充色。将各指标数值与设定的分档区间进行匹配查找，得到相应的填充颜色，然后使用宏按此颜色填充对应的地图图形。

作图步骤

1．按图3-1准备数据地图模型。

图3-1　分档填色热力地图模型　范例文件名：3.1分档填色经典做法.xls（或xlsm）

图中B～C列为各省的指标数据，其余部分后面再讲解。

2．设置分档阈值和图例。

假设要将地图按数据大小分为5档进行填色。

在I列输入各分档区间的下限数值，如第2档的数值区间为0.2～0.4，就在I12单元格输入0.2；在J列输入color1～5字样。I11:J20区域将作为颜色分档的查找表，可将其名称定义为color_table，便于引用。为单元格或单元格区域命名的方法是：选中区域后，在Excel左上角的名称框中输入相应的名称，回车即完成命名。

在F列填入由浅到深的颜色，代表数值由小变大，并分别命名为color1～5。颜色的变化梯度应与分档间距成等比例，以反映数量变化。如何科学合理地设置地图颜色，可参见附录A.1的内容。

在G列填入分档区间的文字标签。本例中使用公式自动转换，如：

G11: =CONCATENATE(I11,"~",I12)

单元格区域F11:G20将在后面被引用为地图的图例。

3．查找各省颜色号。

经过以上设置后，在D列使用公式为各省查找颜色号，如：

D11: =VLOOKUP(C11,color_table,2,1)

注意VLOOKUP的最后一个参数值为“1”，即使用模糊查找模式，意思就是根据C11的值，在color_table区域（即I11:J20）查找“不大于C11的最大值”，并返回相应行第2列的值。将此单元格中的公式向下拖拽复制至整个D列。

以图3-1中的数据为例，C12北京的数据为58.0％，那么color_table 中不大于58.0％的最大数值为0.4，D12的公式将返回color_table中0.4所在的第3行的第2列，也就是color3。而color3作为名称指向单元格F13，所以就用F13的颜色去填充北京的图形。

4．插入地图图形。

将2.5节准备好的已命名的地图图形复制到模型中，本例中放置在U11:AB32区域（图3-1中未显示，请参阅范例文件）。请确保各省图形的命名与B列的省份名称一致，如选中湖北的图形，名称框中应显示“湖北”。若需调整地图的大小，可先将各省图形组合，然后按住Shfit键调整至合适大小，再取消组合为独立的图形。

5．编写填色的宏代码。

通过录制宏^[1]获得关键代码，修改、编写如图3-2所示的宏代码。

图3-2　分档填色的宏代码

这段宏其实很简单，关键代码只有一句话，就是对各省的图形使用D列颜色栏的值作为名称所指向的单元格的填充色进行填充。

使用窗体控件或矩形框绘制一个按钮，输入文本“填色”，通过鼠标右键为其指定宏为fill_color。

6．单击按钮测试功能。

现在单击按钮运行宏，你会发现，根据其数据与分档阈值的匹配情况，每个省的图形被填充了相应的颜色。检查数据看所填色是否正确，以确认模型无误。

7．整合完善地图。

为完善地图，对区域F11:G20进行拍照^[2]，将拍照图片放置到地图的左下角，作为图例；再对各省图形所在区域U11:AB32进行拍照，得到一个“整合的”地图图形，放置到L11:S32处。导出地图时，只需要复制这个拍照对象就可以了。

本做法最初的思路来源于Jorge Camoes博客上的一篇日志，见参考文献1。当我按照他的视频教程，摸索制作出中国地图的时候，简直兴奋不已。在博客发布后，更受到众多读者们的欢迎和喜爱，这鼓舞着我继续琢磨更多的地图做法。当然，现在这里介绍的做法较该视频已经做了很大的改进，更加简洁清晰，更加利于初学者理解和制作。

本做法涉及的知识点有：VLOOKUP函数模糊查找、定义名称、录制和编写宏、拍照等。当然，最重要的还是这种构建模型的精妙思路。

懒人直接用

现在，要使用分档填色数据地图的时候，只需要简单的3个步骤即可。在随书范例包中找到并打开“3.1分档填色经典做法.xls（或xlsm）”文件，启用宏。

●在C列输入各省的指标数值。为效率起见，建议使用VLOOKUP函数从其他地方查找引用过来。

●设置分档阈值和图例。在I列设置分档区间的下限，如有必要，更改F列（图例）的填充色。范例中预设了5种配色，需更多配色方案请参见附录A.1。

●单击填色按钮，刷新热力地图，OK。导出地图时复制L11:S32处的拍照图片即可。

范例文件的模型可以支持10个分档，不足10档的可以不填。不建议使用更多的分档。

如需在地图上增加区域名、指标值，可以在各省图形的上面绘制一个文本框，然后将其值链接到相应的单元格。一般不建议添加这些，地图上少放些内容，会显得更加清晰。

如需按华东、华南等大区域进行数据分析，可以将某几个省的图形组合起来之后再命名。这时候你的数据源也要按华东、华南等区域进行组织。简言之，将大区作为一个区。

3.2　透明度填色做法

上一节介绍的热力地图做法，是将地图分为有限的几档进行填色。其缺陷是有时候虽然区域之间数值差异较大，但填充色却是一样的，不能反映出区域数据之间的差别。商业杂志上有一种热力地图做法，对地图按指标数值的大小精确填色，也就是图形之间的颜色深浅差异与数值之间的差异是精确对应的，可以称做“无级变色”。本节介绍一种利用自选图形的透明度来制作这种“无级变色”热力地图的方法。

作图思路

对每个区域的图形填充一种颜色后，如果将其设置为不同的透明度，就会显示出不同深浅的效果，透明度越大，颜色越浅。而这个透明度参数可以用各区域指标数据与最大最小值的位置关系折算得出，这样就会得到一个精确、无级填色的热力地图。

作图步骤

1．准备数据地图模型如图3-3所示，本节以制作一幅世界地图为例进行讲解。

图3-3　透明度热力地图模型　范例文件名：3.2透明度填色做法.xls（或xlsm）

假设为B、E列为各国指标数据，D列为将要插入的地图图形的名称，与国家名缩写对应。

单元格I5、I6使用函数获取所有指标数据中的最大值和最小值。F列为根据指标数据和最大最小值的位置关系折算出的透明度，其公式为：

F5: =(N_max-E5)/(N_max-N_min)*90％

即最大值对应的透明度为0，最小值对应的透明度为90％。对此公式的理解，可参见范例文件中的图解。将F5单元格的公式向下复制到整个F列。

2．准备一个单元格K6，设置好其填充色。此填充色将作为整个地图的基准色。

3．将准备好的地图图形插入到Excel。注意其命名要与D列的图形名一一对应。

4．绘制一个矩形框，命名为My_legend，放在地图图形的合适位置，我们在后面将用其作为地图的图例。

5．编写填色宏代码。

为获得关键代码，对以下操作录制宏：

①选中某省图形→设置其填充色→设置其透明度为50％。

②选中图例图形→设置其填充色→设置其透明度从0％到90％。

根据获得的宏代码，修改、编写出如图3-4所示的代码。

图3-4　按透明度填色的宏代码

这段宏针对每个国家的图形，首先设置其填充色为单元格K6的填充色，然后设置其透明度为F列对应的百分比数值，这样每个图形的填充色就变得深浅不同了。对图例则是先设置为K6的填充色，然后设置出由浅到深的渐变效果。核心代码均通过录制宏得来，Excel 2007以上版本和2003版本可能有所差异，请按录制结果修改。

使用窗体控件或矩形框绘制一个按钮，显示文字为“填充颜色”，指定宏为本段代码fill_color。

6．单击测试模型。

现在单击按钮，你会发现地图图形已对应填色。检查并确认各区域填色的正确性。改变K6的填充色后再单击按钮，你会发现地图已按新的基准色重新绘制！

对地图图形所在区域拍照，得到一个整合的图形，方便导出时使用。

相比分档填色的热力地图，采用透明度原理制作热力地图的优点是精确的无级变色，且操作简单，无须设置分档阈值和图例，还可灵活快捷地设置地图的基准色。缺点是对地图图形的质量要求较高，边界必须严丝合缝，否则就会因为透明度的原因被读者看到缺陷。

本做法涉及的知识点有：自选图形的透明度、渐变，录制和编写宏，拍照等。

懒人直接用

在随书范例包中找到并打开“3.2透明度填色做法.xls（或.xlsm）”文件，启用宏。

●在E列填入你的指标数据。建议使用VLOOKUP函数从其他表格查找引用过来。

●如果需要，在K6单元格更改地图的基准色。这是一个灵活选项，便于选择与所分析指标意义相符合的颜色。

●单击填色按钮，刷新地图的填色。将更新后的拍照对象复制到外部应用。

3.3　条件格式填色做法

上一节介绍的透明度热力地图做法，其填色是无级变化的，但只支持一种颜色由浅到深的变化，不适合于反映数据范围从负数到正数变化的“二分”情况。本节介绍一种条件格式填色做法，其填色既是无级变化的，又可以灵活设置从一种颜色渐变到另一种颜色。

作图思路

在2007版本以后，Excel对条件格式进行了增强，提供了数据条、色阶、图标3种可视化形式。其中色阶其实就是一种HeatMap，通过单元格填充色的深浅反映其数值的大小。

本做法的思路就是利用色阶来制作数据地图：对各区域的指标数据设置色阶条件格式，将条件格式所产生的填色效果，对应填充到各区域的地图图形，即可获得一个条件格式的热力地图，其填充色与数据是精确对应的。

作图步骤

1．首先准备如图3-5所示的数据地图模型，本节以制作一份美国地图为例进行讲解。因为要利用到色阶功能，所以需要基于Excel 2007或2010版本。

图3-5　条件格式填色数据地图模型　范例文件名：3.3条件格式填色做法.xlsm

其中，C～D列为各区域的指标数值。

F～G列是为制作图例而准备的数据区域。其中G11、G20使用函数获取原数据区域的最大值和最小值，也可根据需要直接输入指定的值，以控制想要的颜色。G12:G19使用公式计算出10等分阈值，如：

G12: =($G$20-$G$11)/9+G11

G12:G19使用自定义数字格式“;;;”隐藏了数值的显示。F列直接引用G列的数据，同样使用自定义数字格式隐藏了数值的显示。

2．对数据源设置色阶条件格式。

选中D11:D63的源数据区域，并按住Ctrl键同时选中F11:F20的图例区域，单击菜单开始→条件格式→色阶，选择相应的色阶样式（如图3-6所示），即可在选中区域中产生HeatMap的填色效果。

图3-6　设置条件格式色阶

对色阶的样式，可以在菜单条件格式→管理规则中作进一步的自定义设置，如最小值、中间值、最大值及其相应的填充色，如图3-7所示。条件格式预览的效果就将是地图图形的最终填色效果。

图3-7　条件格式色阶可以自定义颜色

3．将条件格式复制为单元格格式。

要将条件格式生成的填充效果复制到地图图形上，首先要知道其填充色是什么。但经过反复测试，我还是没有找到获取色阶条件格式所生成的单元格填充色代码的方法，所以只好采用手工的方法。

我们先要将源数据D11:D63的条件格式填充色效果，复制到I列的相应位置，成为VBA可以读取的静态格式。这也是个难点，要将条件格式产生的填充效果复制为静态的单元格格式，直接复制粘贴或选择性粘贴都无法实现，需要使用一种特殊的操作方法，如图3-8所示。

图3-8　利用剪贴板窗格将条件格式粘贴为静态格式

①选中D11:D63区域，按Ctrl+C键复制。

②将鼠标定位到需要粘贴到的目标区域的第一个单元格，这里是I11。

③单击“剪贴板”功能组右下角的箭头，打开“剪贴板”任务窗格。

④选择最上面的刚才复制的项目右侧的下拉框，单击“粘贴”。

这时复制完成，I列显示的内容和格式与D列完全一样，但动态的条件格式已经被转换为静态的单元格格式，后面我们通过VBA可以读出其填充色代码。

一个奇怪的事情是，录制以上操作所产生的宏，却无法正确运行，导致不能采用宏自动化，不知是何原因。这个问题欢迎读者来信指教。

关于这一步，也可以采用另外一种借道Word的变通方法来实现：

①选中D11:D63区域，按Ctrl+C键复制。

②新建一个Word文件，按Ctrl+V键粘贴。

③然后再从Word中复制回来，粘贴到I11:I63区域。

这样也可以实现将条件格式转换为静态单元格格式的目的。并且这个过程可以使用VBA自动化，如图3-9所示。在范例文件中，我们已将此宏赋给一个按钮复制，故此步骤只需单击该按钮即可。

图3-9　通过Word获得条件格式所产生的颜色

注意以上代码需要先在VBA工程窗口的菜单工具→引入窗口中勾选Microsoft Office Word的对象库之后才能运行。

4．为地图图形填色。

再把I列单元格的填充色复制到对应的地图图形就简单了，这与3.1节分档填色中的做法是一样的。

编写一小段宏，用I列的填充色去填充对应省份的地图图形。代码如图3-10所示，其实只有1行。

图3-10　按单元格底色填充地图图形的宏代码

将这个宏指定给一个矩形框，作为一个填色按钮。单击按钮，各省地图图形即被填充为对应的颜色，与D列的填充色一致。一个条件格式的HeatMap已经生成，请检查并确认填色正确。

5．整合地图图形及图例，便于导出。

将F11:G20拍照引用到U列处作为图例，再将U11:AB31拍照引用到L11处，作为最后完工的地图，以后导出地图只需要复制这个拍照对象即可。

本做法的优点是：

●色阶是无级渐变的，地图的填色精确对应源数据数值的大小。也省去了分档填色做法中设置分档阈值和图例的麻烦。

●可以充分利用Excel 2007色阶功能的特性，灵活设置出自己想要的各种HeatMap效果，如单色渐变、双色渐变，操作也很方便。尤其适合于数据范围从负数变化到正数的二分情况。

本做法涉及的知识点有：色阶条件格式，特殊效果的复制粘贴方法，录制并修改宏代码，拍照引用，自定义格式“;;;”等。

懒人直接用

在随书范例包中找到并打开“3.3条件格式填色做法.xlsm”文件，启用宏。

●在D11:D63列填入你的数据。为效率起见，建议使用VLOOKUP函数从其他区域查找引用过来。

●如果需要，更改D11:D63以及F11:F20区域的色阶条件格式为自己想要的效果。

●单击复制按钮，或按前文第3步描述的方法，将D列的条件格式复制为I列的单元格格式。

●单击填色按钮，生成地图。导出地图时复制拍照对象即可。

3.4　气泡式数据地图

气泡式数据地图也是一种常见的数据地图，它是在地图上相应位置显示相应的气泡，以气泡的大小代表各区域指标值的大小。气泡式数据地图可以避免区域之间面积差异悬殊时可能带来的视觉误导。图3-11显示了2009年金融危机中，3家公司在各地关闭零售门店的数量比较。

图3-11　气泡式数据地图　例图来源：《商业周刊》杂志

作图思路

相比前面介绍的热力地图，气泡地图的制作方法要简单得多。制作一个气泡图，气泡的大小代表数值的大小；绘图区用一个地图图片填充，衬在气泡图下面，调整气泡的位置与各区域对应。稍微麻烦一点的是如何确定气泡的位置，不过我们并不需要知道准确的经纬度数据，而是运用一个拽动数据点的操作技巧来完成它。

作图步骤

1．先组织作图数据如图3-12所示。

图3-12　气泡式数据地图模型　范例文件名：3.4气泡式数据地图.xls

D～E列为定位气泡位置的x、y辅助数据，可先用过渡数据代替。假设你有10个区域，（x，y）的数据可先暂时填为（1，1）、（2，2）…（10，10），本例中一直填到（34，34）。

F列为要显示的指标值，作为气泡图的z值，它将决定气泡的大小。

G列为准备显示的数据标签：省名+数值，例如G20：=C20&", "&F20。我们可以只对要显示标签的行输入公式。

2．生成气泡图图表。

选中D～F列的数据插入一个气泡图，设置X、Y轴刻度的最小值为0，最大值为35，设置以气泡的面积代表数值大小，缩放气泡大小为50％左右。这时得到的图表应该是从左下角到右上角一连串的气泡，如图3-13左图所示。

图3-13　调整气泡的位置使之与地图上的区域对准

准备一个与例图类似的地图图片，将其填充到图表的绘图区。理想的做法是淡色填充、白色框线，或者白色填充、淡色框线。

3．将各个气泡拖动到其对应的区域上面。

若使用Excel 2003版本，可以直接使用拖拽法。以北京为例，先选中整个气泡图数据系列，稍后再选中北京的数据点，当鼠标悬停在其上面时，会变成一个十字箭头。这时按住鼠标左键，拽住数据点沿水平方向拖动，再沿垂直方向拖动，直到将该数据点拖拽到地图上北京的位置所在。用同样的方法，将各区域的数据点逐一拖拽到相应的位置。

对于Excel 2007以上版本，由于其取消了对拖拽改变数据源大小的支持，只能通过调整D～E列单元格中x、y数值的大小来调整数据点的位置。这一点上可能不及Excel 2003方便。

现在图表的效果应该如图3-13右图所示，已经可以看到气泡地图了。

4．完成数据地图。

删除或隐藏坐标轴，进行一些格式化设置，就可以得到一个气泡式数据地图。

为防止气泡之间相互覆盖而看不见，商业杂志上经常让气泡呈半透明的效果。若使用Excel 2007以上版本，可以直接设置气泡的透明度为50％左右。

Excel 2003版本无法直接设置透明度，但可利用以下变通方法达到同样效果。用自选图形绘制一个圆圈，设置想要的填充色，透明度为50％左右，无色或淡色边框线，然后选中圆圈→Ctrl+C复制圆圈→选中气泡图数据序列→Ctrl+V粘贴，即将圆圈“贴入”到了数据系列的气泡上，各气泡变为半透明。这个“贴入”技巧有很多用途，建议读者熟练掌握。

5．为地图添加图例和/或数据标签。

气泡地图需要有图例，告诉读者多大的气泡代表多大的数值。将气泡图的数据源范围往下扩展4行，在D～G列填入合适的x、y值和指标值、标签值，使图表在中国地图左下角新增加4个代表图例的气泡。

要将G列的标签值显示在对应的气泡旁，需要使用散点图标签工具XY Chart Labeler^[3]，设置气泡图显示数据标签为G列。

最后完成的气泡地图如图3-12所示，是不是很巧妙呢？

本做法涉及的知识点有：气泡图，绘图区填充图片，拽动数据点改变源数据（仅适用于Excel 2003），数据点贴入图形，标签修改工具等。

懒人直接用

在随书范例包中找到并打开“3.4气泡式数据地图.xls”文件。

●在F列输入各省的指标值。为方便起见，可在另外的区域贴入数据表格，然后使用VLOOKUP函数查找引用到F列。

●若设置了图例，调整F41:G44的图例参数。

●将右侧的图表或包含该图表的拍照对象复制到自己的报告中使用。

3.5　热力气泡式数据地图

所谓热力气泡式数据地图，是在气泡式数据地图的基础上加入了HeatMap的特征，可以同时表现两个变量：气泡的大小代表一个变量的同时，气泡的颜色又代表另一个变量。一般来说，用气泡的大小代表绝对值变量（如销售额），用气泡的颜色代表相对变量（如同比增幅）。

作图思路

在3.4节气泡式数据地图的基础上，加入3.1节热力地图的做法，对气泡进行分档填色，即可得到一个热力气泡式地图。建议读者先掌握以上两种地图形式的制作方法后再阅读本节。

作图步骤

1．准备数据地图模型如图3-14所示。

图3-14　热力气泡式数据地图数据模型　范例文件名：3.5热力气泡式数据地图.xls（或xlsm）

本模型可直接在3.4节气泡式数据地图模型和3.1节分档填色数据地图模型的基础上整合而来。

前一部分做法与气泡式数据地图模型类似，D～E列为定位气泡位置的x、y辅助数据，F～G列为要表现的指标数值，如销售收入和同比增幅。H列预留为可能要显示的数值标签。

后一部分做法与分档填色数据地图模型类似，K列为一组渐变的图例颜色，命名为color1～10；N列为分档阈值；I列根据G列数值大小，从N7: O16区域查找返回相应的颜色号，查找公式为：

I6 ：=VLOOKUP(G6,$N$7:$O$16,2,1)

注意最后一个参数值为“1”，表示模糊查找，即返回小于查找值的最大数所在行的对应值。

2．生成气泡数据地图。

这一步与3.4节气泡式数据地图完全一样，请查阅该节相应内容。

3．使用宏填充气泡的颜色。

现在我们要根据各省I列的颜色号，对气泡填充相应的颜色，这是热力气泡式数据地图的主要特征，也是本做法的关键点。

Excel中插入的图表，默认的名称为“图表1”或“chart 1”之类的，比较好的习惯是先为图表命名一个有意义的名称，便于后续引用。所以我们先给气泡图命个名。

若使用2003版本，按住Ctrl键，用鼠标选中气泡图，在Excel左上角的名称框中输入“my_map_chart”，回车，即完成了气泡图的命名。

若使用2007以上版本，选中气泡图→图表工具选项卡→布局→属性→图表名称，输入“my_map_chart”，回车。显然2003版本的操作更为简便。

我们不知道设置气泡填充色的代码该如何写，但可以对以下操作录制宏：

选中气泡图数据系列→再选中某个数据点→设置一种填充色→设置透明度为50％。

根据得到的宏代码，修改、编写如图3-15所示的宏代码。

图3-15　设置气泡图数据点填充色的宏

这段宏代码将对气泡图的每个数据点，使用对应省I列的值作为名称所指向的K列对应单元格的颜色进行填充，并同时设置透明度，使之呈现半透明的效果。若使用2003版本，将不支持设置透明度，可省略此特征。

使用窗体控件或矩形框，绘制一个按钮，为其指定宏为fill_bubble_color。现在单击按钮，各个气泡即被自动填色，请检查所填颜色正确无误。

4．为地图添加图例和标签。

热力气泡式数据地图需要两个图例。一是代表数值大小的气泡，这与气泡式数据地图做法一样，使用往下延展的图例辅助数据。二是代表增幅大小的颜色，这与热力地图做法一样，对单元格区域K7:L16进行拍照，将拍照图片放置到地图的合适位置作为图例。

若需为气泡添加数值标签，可使用标签修改工具XY Chart Labeler^[4]设置显示为H列。

最后完成的地图如图3-16所示，与例图效果几无二致。

图3-16　热力气泡式数据地图

热力气泡式数据地图的优点是可以在地图上同时反映两个分析变量。

本做法涉及的知识点有：气泡图，VLOOKUP模糊查找，定义名称，命名图表，录制和编写宏，标签修改工具等。

懒人直接用

在随书范例包中找到并打开“3.5热力气泡式数据地图.xls（或.xlsm）”文件，启用宏。

●在F和G列输入你的指标数值，如销售额和同比增幅数据，这时会看到气泡图大小即时发生变化。同时输入第一个变量的图例辅助数据。

●在N列输入第二个变量的分档阈值，作为起止区间的下限。模型支持分10档。

●如有必要，在K列设置你想要的梯次填充色，即第二个变量的图例。

●单击按钮，即看到气泡图的填充色发生变化。将完成的地图复制到你的报告中。

轻松一刻

地理课上。

老师指着世界地图提问：“谁能告诉我美洲在哪里？”

Zeric走到地图前，指出了美洲在地图上的位置。

老师又说：“好，孩子们，告诉我，是谁发现了美洲呢？”

“Zeric！”孩子们异口同声地回答道。

注释

[1]　录制宏并获得代码的方法参见附录A.3。

[2]　拍照操作方法参见附录A.4。

[3]　散点图标签工具XY Chart Labeler，安装及使用方法参见附录A.5。

[4]　参见附录A.5。