第5章　让数据地图动起来

前面我们所介绍的各种数据地图做法，都是生成一幅静态的地图图片，用于打印或投影。如果说静态地图可以帮助管理者在阅读中洞察商机，那么动态地图则可以帮助管理者在交互操作中“动”察商机。交互式数据地图能以更高的效率分析更多的数据，是数据分析和演示的有力武器。

本章将介绍两种交互式数据地图的做法，一种是通过列表框等选择器切换地图中的数据，另一种是把地图构建为一个选择器来驱动图表。

5.1　动态图表技术准备

制作交互式地图首先需要掌握动态图表的制作技术。没有接触过动态图表的人会以为它很神奇，需要高深的编程技术。殊不知在Excel中制作动态图表其实很容易，并不需要编写任何代码，就可以构造一个具有程序界面的动态图表。

Excel中制作动态图表的方法很多，这里介绍一种最为简单和易于理解的方法，称为辅助序列法。

图5-1是一个简单的动态图表模型示例，包括源数据、选择器、辅助数据、动态图表及KPI等4部分。

图5-1　一个简单的动态图表模型　范例文件名：5.1简单动态图表模型示例.xls

这个动态图表模型的原理和机制是这样的：

当用户通过列表框选择时，选择结果会记录在M3单元格；第20行的辅助数据区按M3的选择结果，使用查找函数从位于第23～30行的源数据区查找并返回相应的行；而上面的图表及KPI是以第20行的辅助数据区为数据源的，因此用户的选择变化时图表及KPI就会变化，从而产生动态交互效果。

1．制作选择器。

动态图表的关键点在于选择器，选择器的制作一般使用一种叫作控件的对象。Excel有两类控件，2003版本中叫窗体控件和控件工具箱控件（参见图5-2），2007版本后称为表单控件和ActiveX控件（参见图5-3）^[1]。ActiveX控件一般适合于技术开发人员使用，对数据分析人员推荐使用表单控件。

图5-2　Excel 2003版的窗体控件和控件工具箱

图5-3　Excel 2007以上版本的表单控件和ActiveX控件

用鼠标在表单控件中选取列表框（鼠标悬停时屏幕会提示控件类型），在工作表上M4处画出一个控件，双击，或单击右键→设置控件格式进入设置控件格式对话框，如图5-4所示。

图5-4　设置窗体控件的控制选项

将数据源区域指定为国家名所在的A23:A30区域，单元格链接指定为M3，确定。现在，列表框已经可以选择，试着操作，你会发现选择结果以序号形式记录在M3单元格中。如选择“中国”则M3为1，选择“美国”则M3为8。

这就是一个选择器的功能。另外，利用单元格的数据有效性也可以制作一个简易的选择器，读者可以查阅相关资料了解。

2．准备作图数据。

在第20行准备作图数据，称为辅助数据区。在单元格A20中输入公式：

A20: =INDEX(A23:A30,$M$3)

向右复制到N20。这一行的公式根据M3的用户选择结果从数据源区查找返回对应的行。

在制作动态图表时，准备作图数据所经常使用的函数有：INDEX(+MATCH)、OFFSET(+COUNTA)、VLOOKUP、CHOOSE等，读者可查阅相关资料了解并熟练掌握。

3．制作图表。

以A19:L20为数据源做图表，格式化至理想样式。本例中还引用了L20、N20等位置的数据作为KPI用大字号显示，有些仪表板的风格。

4．测试动态图表。

至此，动态图表模型已经完成，现在可以检查并确认其正确性。通过列表框选择国家，图5-1所示左侧的图表和KPI显示内容都会自动切换。而如果你把数据源区、作图数据区、选择结果都隐藏起来的话，用户看到的就是一个完美的分析界面了。

隐藏辅助数据的方法很多，可以将数据源区域放置在另外的工作表，可以用图表或控件覆盖住数据区，也可以将数据区字体颜色设置为与单元格背景同色而隐藏。总之，给用户一个干净、清晰的分析界面。

掌握了动态图表技术之后，就为制作动态数据地图奠定了基础。其原理是一样的，就是通过选择器来切换“供应”给地图的数据。

5.2　切换时间维度的动态数据地图

一幅静态的数据地图只能反映一个时期或时间点的数据分布特点，如果有多个时期的数据怎么办呢？这时就需要能对数据地图动态切换时间周期。还记得3.1节的热力地图吗，现在我们要给它增加一个切换时间维度的功能，让用户可以分析多个时间周期的数据分布。

作图思路

提供一个选择器，让用户可以选择时间维度。根据用户的选择，将所选当期的数据“传递”给地图模型，模型则根据设定的分档阈值进行填色并刷新地图。

作图步骤

1．构建动态地图模型。

在3.1节分档填色地图模型的基础上，进行改造之后如图5-5所示。

图5-5　可以切换时间维度的动态数据地图模型　范例文件名：5.2切换时间维度的动态数据地图.xls（或.xlsm）

由于有多个时间周期，数据量比较大，我们用专门的data工作表来存放原始数据。本例中，data工作表B11:O44区域存放了各省2001～2012年共12年的数据，也可以是1～12月共1年的数据。

map工作表为模型的地图生成和演示界面，各部分的设置与3.1节基本类似，后续步骤下文再讲解。

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

设置分档阈值和图例的方法同3.1节。由于数据有12个周期，我们要根据所有期间数据的取值范围来设置分档阈值，这样每一年的地图分档填色才有共同的参照标准。为更精确地反映数据，本例中设置了9个分档。

3．制作时间选择器。

现在制作一个选择器以供用户选择时间周期。考虑到向右具有时间变化的象征意义，我们在地图下方绘制一个滚动条窗体控件，作为年份选择器。设置最小值为2001，最大值为2012，步长为1，选择结果链接存放到单元格C9。若是12个月的数据，最小值和最大值可以分别设为1和12。

4．准备所选当期的数据。

C11:C41作为存放所选择当期数据的临时站点，根据C9的选择结果，使用公式从data工作表的数据源区查找并返回相应年份各省的数据。以C11为例，其公式为：

C11: =VLOOKUP(B11,data!$B$11:$O$44,map!$C$9-2000+2,0)

5．将绘制地图的宏赋给滚动条。

窗体控件有一个好处是可以指定宏，每次控件被选择时，就会触发宏动作。还是利用3.1节中的地图填色宏fill_color，将其指定给滚动条。

这样，当调整滚动条时，记录选择结果的C9单元格会变化，C列就会变化为所选当期的数据。而滚动条同时会触发绘制地图的宏，以C列的当期数据参照统一的图例阈值来刷新地图的填充色！这就是动态地图的效果。

6．制作一个自动播放的按钮。

如进一步完善的话，我们可以提供一个可以自动切换年份的播放按钮。绘制一个按钮，为其编写如图5-6所示的宏代码。

图5-6　自动播放的宏

这个简单的宏循环所做的事情，就是让C9的值从2001变化到2012，每次刷新一次地图，并停顿1秒。

我们在地图上方放置一个大字号的文本框，引用C9的当前年份，作为地图的年份提示。

现在，当你单击播放按钮时，地图的颜色，也就是所对应的数据，将自动从2001年变化到2012年，而你就可以专心观察这种变化趋势。当看到时间慢慢变换，地图的颜色随之慢慢变化，反映出各省业务指标变化的特征，是不是非常惬意呢？

这里为讲解方便，将有关辅助数据与地图放置在一起。在制作自己的报告时，你可以将地图图形和选择器放置在一个单独的工作表，把其他工作表都隐藏起来，或者直接将有关辅助数据的列隐藏起来，以便给用户一个干净的分析界面。

懒人直接用

在随书范例包中找到并打开“5.2切换时间维度的动态数据地图.xls（或.xlsm）”文件，启用宏。

●在data工作表的B11:O44区域输入数据。如需修改数据周期，请按前述步骤调整模型中相应的地方。

●在map工作表设置统一的图例和分档阈值。

●在map工作表通过滚动条或播放按钮动态演示和分析数据。

5.3　切换指标数据的动态数据地图

上一节介绍的分档填色型动态地图，是切换同一个指标不同时期的数据，因此可以使用共同的分档阈值。有时候我们要在地图上切换显示多个不同类型的分析指标，由于指标的量纲单位不同，需要使用的分档阈值也不同，上一种做法就不合适了。这种情况下，我们可以利用3.2节的透明度做法，来制作这种切换指标类型的动态数据地图。

作图思路

同5.2节，提供一个选择器，让用户可以选择不同的分析指标。将不同的指标数据传递给地图模型，模型对每种指标都使用按比例计算的透明度来填充图形，就免去了分档阈值的设置。

作图步骤

1．构建动态地图模型。

还是利用3.2节透明度做法的地图模型，进行改造后如图5-7所示。同5.2节，我们将分析指标数据源放置在单独的工作表data中。

图5-7　可以切换指标数据的动态地图模型　范例文件名：5.3切换指标数据的动态数据地图.xls（或.xlsm）

工作表map为分析界面。C列为当前选择的指标数据，根据选择结果从data工作表的数据源区域查找引用过来。D列为根据指标最大及最小值按比例计算出的透明度。

2．制作指标选择器。

将data工作表中第10行的各指标名称，转置引用到map工作表的F列，用来作为列表框选择器的链接数据源。

注意这里的转置技巧，利用了转置函数和数组函数。选中F11:F20区域，在公式栏输入如下公式：

=TRANSPOSE(data!C10:L10)

输入完成后按Ctrl+Shift+Enter键，整个F11:F20区域都出现以上公式，并且公式前后出现一对花括号“{}”，表示公式被输入为数组公式。F11:F20就是对data工作表中C10:L10的转置引用。

用窗体控件绘制一个列表框，设置其数据源区域为F11:F20，链接单元格为F9。现在，单击选择列表框，选择结果将以序号形式记录在F9中。

3．准备所选指标的数据。

根据F9的选择结果，在C列输入公式查找各省当前所选择指标的对应数据。以C11为例，其公式为：

C11: =VLOOKUP(B11,data!$B$11:$L$44,$F$9+1,0)

公式根据所选择指标的序号，从源数据区域查找并返回各省对应的数值，即当前所选指标的数据。

4．修改刷新地图的宏。

现在编写刷新地图的宏代码。在切换不同的指标时，我们想让地图的基准色也发生变化。一方面可以提醒用户注意到指标的变化，另一方面可以让地图颜色的象征意义与指标相符，如对GDP可使用蓝色，对单位能耗则需使用红色。

在F列指标名称的右侧单元格即G列，填充不同的颜色，这个颜色将作为该指标的地图基准色。

仍利用3.2节的填色宏代码，修改后如图5-8所示。

图5-8　按指标基准色和透明度填充地图的宏

这段宏代码在对地图填色时，会使用当前所选指标对应的G列单元格的填充颜色作为地图的基准色。将此宏指定给列表框选择器，那么每次选择指标后都会触发此宏来刷新地图。

5．测试动态地图。

在地图上方绘制一个大字号的文本框，引用当前选中的指标名称，作为地图的指标名称提示。

现在，通过列表框选择不同的指标，地图就会刷新。不同的指标，地图的基准色是不一样的。

同5.3节，我们也增加一个自动切换指标的播放按钮，便于浏览观察各个指标之间的地域分布差异。自动播放宏可参见范例文件，这里不细述。

同样地，为了便于讲解，例中将有关辅助区域与地图放置在了一起，你可以将其隐藏，留给用户一个干净的分析界面。

采用透明度做法制作动态地图，优点是不必为不同的指标设置分档阈值和图例颜色，各指标统一采用按比例计算透明度办法，适用性强。3.4节的气泡地图做法也可以采用这种形式，做成动态地图，因为气泡的大小也是直接由具体数据大小决定的，不需要设置分档。这里就不具体介绍了，读者可以自行尝试。

懒人直接用

在随书范例包中找到并打开“5.3切换指标数据的动态数据地图.xls（或.xlsm）”文件，启用宏：

●在data工作表相应位置输入数据。

●在map工作表中即可选择指标通过地图分析数据了。

5.4　基于地图选择的动态图表（鼠标点击版）

在国外的财经网站上，我们经常会看到这样一种通过地图选择的交互式图表：当读者用鼠标在地图上选择某个地区，右侧的图表就会自动切换为该地区的数据。这种做法在地域选择的方式上提供了直观的地理位置信息，可方便地分析大量数据，交互性强，非常具有吸引力。

作图思路

这种基于地图选择的动态图表可以分解为地图选择器和动态图表两部分来实现。地图选择器负责在用户点击某个地区的图形时，触发一个宏过程，记录下用户选择的地区名，并突出显示所选地区图形的填充效果。动态图表模型则根据地图选择器所记录的地区名，相应地变换图表的数据源，从而产生动态交互的效果。

作图步骤

1．准备地图模型，如图5-9所示。

图5-9　基于地图选择的动态图表模型　范例文件名：5.4基于地图选择的动态图表（鼠标点击版）.xls（或.xlsm）

首先将准备好的地图图形放置到名为dashboard的工作表，注意，这些图形应已按各省名称命名。动态图表模型后续步骤讲解。

2．编写地图选择器的宏过程。

我们要编写一个宏过程，当用户单击某省图形后，将该省名称填入A1单元格，供后面的动态图表模型引用。同时要将该省图形填充为表示已被选中的高亮色，并将之前选中的图形还原为表示未选中的淡颜色。

单击菜单工具→宏→Visual Basic编辑器（或Alt+F11键），进入VBA编辑窗口。在左侧窗口中选择ThisWorkbook，在右侧窗口中输入如图5-10所示的代码。

图5-10　地图选择器的宏代码

其中，Application.Caller会返回此宏的调用者，也就是被点击的地图图形的名称，该值被记录在单元格A1。

3．为每个地区图形指定宏调用。

现在要将上述宏过程指定给每个省的地图图形。选中某省的图形，单击右键→指定宏，选择user_click。按此方法逐一为各省图形指定宏调用。

更加便捷的方法是使用如图5-11所示的宏代码，为各省图形批量指定宏调用。

图5-11　为地图图形批量设置宏调用

4．测试选择器。

现在可以测试选择器了。注意，测试之前，先要手动为单元格A1初始化（即输入一个值，如“湖北”）。然后，用鼠标逐一单击各省的地图图形，你会发现选择的结果会反映在单元格A1中，地图图形的颜色也会跟随变化。

至此，一个地图形式的选择器已经完成，它将是第6章综合地图模型的重要部件。

5．准备动态图表数据源。

假设各省的数据存放在工作表data1中，其中A列为省名，与地图图形的名称对应；C～N列为各省1～12月的指标数据，如图5-12所示。

图5-12　动态图表的数据组织

单元格A2引用工作表dashboard中A1的值，即当前被选中的省名。单元格C2:N2使用VLOOKUP函数从数据表格中查找返回当前选中省的对应数据。以单元格C2为例，其公式为：

C2: =VLOOKUP($A$2,$A$5:$N$36,COLUMN(C5),0)

将此公式向右复制到N2。这样，当用户通过前面的地图选择器选择某个省时，该省的数据就会被查找引用到第2行。

6．制作动态图表。

在工作表data1中以B2:N2为数据源制作图表，格式化至你喜欢的样式，然后将其复制粘贴到工作表dashboard中，放置在地图图形的右侧。

7．测试模型。

现在，用鼠标在地图上选择不同的省份，你会发现图表将自动跟随变化。检查并确认动态图表的数据引用正确。至此，一个基于地图选择的动态图表模型已经完成。

你可以继续使用第5～6步的方法，在其他工作表中准备数据，制作更多的图表，并且都放置到工作表dashboard中，与地图一起排列好，成为一个综合的动态仪表板，通过一个地图选择器同时控制多个图表、表格或者KPI显示。

本做法的主要技巧在于地图选择器的构造，其他与5.1节动态图表技术准备中的内容是一样的。

懒人直接用

在随书范例包中找到并打开“5.4基于地图选择的动态图表（鼠标点击版）.xls（或.xlsm）”文件，启用宏。

●在data1工作表中输入各省数据。

●在dashboard工作表中测试动态图表，检查无误。

若要反映的数据源不是时间序列，或时间序列的周期不是12，可按前述步骤修改相应的地方或图表类型。

5.5　基于地图选择的动态图表（鼠标移动版）

5.4节介绍的鼠标点击版的基于地图选择的动态图表，每次切换区域都需要在地图上点击一下，点多了手就会有些不适。而我一直想模仿的一种效果是，鼠标在地图上移动，当悬停在某个区域上时，图表就自动切换为该区域的数据。

很多网友一起参与了这个讨论，提出了多种做法，包括图表事件法、工作表事件法、钩子法等。其中网友chrisfang的做法最为简洁^[2]，我在其基础之上进行了简化和完善，介绍给大家。

作图思路

在Excel中，可以通过API函数获取鼠标位置，然后获取处于这个位置上的对象。如果是地图图形，则将其名称记录下来，作为动态图表的输入参数。剩下的事情，就与5.4节一样了。

作图步骤

1．构建地图模型。

还是利用5.4节的模型文件，这里我们以广东省地图来讲解。如图5-13所示，在dashboard工作表中放置地图图形和动态图表，用户选择结果放在A1单元格中，将作为动态图表模型的输入参数。源数据及动态图表辅助数据均放在data1工作表中。

图5-13　基于地图选择的动态图表模型（鼠标移动版）　范例文件名：5.5基于地图选择的动态图表（鼠标移动版）.xls（或.xlsm）

2．编写地图选择器宏。

根据前述思路，编写如图5-14所示的宏代码。

图5-14　通过捕捉鼠标位置获知用户选择的宏代码

在这段宏代码中，先调用API函数GetCursorPos获取鼠标当前所在的位置坐标，然后利用RangeFromPoint函数获取这个坐标位置上的对象。当这个对象是地图图形时，将其名称记录于A1单元格，也就是用户当前选择的区域名。同时，要将该区域图形填充为表示已选中的亮色，并将之前选中的图形还原为表示未选中的淡色。这就是鼠标移动版地图选择器的功能。

与5.4节的模型相同，A1是动态模型的输入参数，作为图表数据源的辅助区域将自动刷新为与A1中区域名称对应的数据。

3．提供启用移动选择器的选项。

绘制一个启用鼠标移过切换区域的复选框，链接到单元格A2，并为其编写如图5-15所示的宏，即当用户选择复选框时，根据其选择结果，决定调用还是终止移动选择器。

图5-15　启用或者停用选择器的宏

4．测试模型。

现在，勾选启用鼠标移过切换区域复选框，当鼠标在地图上移动时，地图会突出显示当前悬停的区域，右侧的图表也会随之发生变化，显示为当前鼠标所悬停区域的数据。检查并确认数据正确。

这种操作非常轻灵，完美模仿了国外网站上的操作体验，模型效果也体现出一种数据的灵动之美。若取消勾选启用鼠标移过切换区域复选框，此功能消失。

此功能可单独作为选择器，也可与5.4节的鼠标点击版整合在一起，使用户在取消勾选启用鼠标移过切换区域复选框后，仍可使用单击方式来切换区域。

本做法所涉及的新增知识点，主要是API函数GetCursorPos和RangeFromPoint的结合运用，其他与5.4节类似。

懒人直接用

在随书范例包中找到并打开“5.5基于地图选择的动态图表（鼠标移动版）.xls（或.xlsm）文件，启用宏。

●在data1工作表输入数据。

●在dashboard工作表中勾选启用鼠标移过切换区域复选框，即可通过鼠标在地图上移动来切换区域，分析数据。

轻松一刻

一位地理学家，用世界各大洲来比喻女人一生的各个阶段。他说：

“13岁到18岁的女人好像非洲一般，是一块处女地带，尚未经过探索；

“18岁到30岁，像亚洲——热情而富有异国情调；

“30岁到40岁，像美洲——已完全被勘察，而且资源丰富；

“40岁到55岁，像欧洲——已消耗殆尽，但仍是有趣的；

“55岁之后，像澳洲——每一个人都知道它孤零零地躺在太平洋中，但没有人去关心照顾它。”

注释

[1]　默认情况下并不可见，需要先调出开发工具选项卡。参见附录A.3相关内容。

[2]　http://club.excelhome.net/viewthread.php?tid=546023，8楼