相册

VBA也是交互式图表实现的重要方法之一，借助VBA不但可以很方便地处理使用定义名称、筛选、数据透视表无法完成的图表制作困扰，同时还可实现某些特殊效果的交互图表。

交互辅助

图14.4-1　的交互式图表没有使用任何我们已经熟知的触发器，触发采用鼠标点选数据区域的方式进行，在该案例中数据以列的方式被绘制到图表中，实际的触发过程利用工作表的Worksheet_SelectionChange事件驱动Excel强制重算来完成。

图14.4-1　使用VBA工作表事件驱动的即点即显交互图表

这个图表的制作过程使用到了条件格式、定义名称和VBA，此处的条件格式是为了高亮显示用户鼠标选取的列，并未参与图表的数据交互。以下是详细制作方法：

1．选中原始数据区的C5:F16单元格区域，设置条件格式如图14.4-2所示。

图14.4-2　单元格数据区域

2．分别定义“ChartData”、“Item”两个定义名称，公式详见图14.4-1案例左下方。

3．选中原始数据区的B4:C16单元格区域，制作柱形图，并美化为图14.4-1案例右上角图表样式。

4．修改图表柱形系列的SERIES公式为图14.4-1案例右下方箭头所指样式。

5．按下键盘Alt＋F11组合键启动VBE界面，在对应工作表宏代码编辑区，键入以下VBA代码段：

---

程序代码：14.4-1　工作表事件驱动公式重算

---

动态可视

汉斯·罗斯林（Hans Rosling）在著名的TED大会演讲人口和世界各国经济时，展示了一个非常有趣且强大的图表：运动图（Motion Chart），图14.4-3即是该类型图表的一个演绎案例。这个交互式图表展示了1959年至2006年间全球平均气温与二氧化碳含量、太阳黑子数3者间的关系，使用了12个复选框和1个滚动条工作表控件来分层展示数据，复选框控制显示月份，滚动条驱动图表显示当前年度。

图14.4-3　使用VBA驱动的运动图（Motion Chart）［数据来源：NASA.gov美国国家航空和航天管理局网站］

这个案例的制作过程，VBA只是响应自动展示的播放按钮，除此之外并未参与图表的其他交互过程，其他交互过程完全依赖定义名称和工作表控件的配合。以下是详细制作方法：

1．额外增加“月”和“年”两个辅助列，相应的函数公式见图14.4-4箭头所指。

图14.4-4　图14.4-3案例实现第一步数据整理

2．参照如图14.4-3图表右侧样式，依次添加12个复选框工作表控件，并分别指定其对应单元格链接为R5～R16的单个单元格。

3．添加如图14.4-3所示的滚动条工作表控件，设置如图14.4-5所示的对话框参数。

图14.4-5　图14.4-3案例滚动条设置

4．依次添加如下方所示的36个定义名称：

5．生成气泡图，添加12个图表系列，依次按顺序将自第5行数据的“月”数据指定给系列的名称；定义名称Xdata指定给系列的X值数据，定义名称Ydata指定给系列的Y值数据，定义名称Ydata指定给系列的气泡大小，定义名称的序号与系列序号一个个对应。美化为图14.4-3的图表样式，图表的SERIES公式类似于：

6．在单元格K27输入公式：，通过“＝”与图表标题相关联，并美化为图14.4-3的图表右上角所示样式。

7．添加按钮工作表控件，并同下方的工作表VBA代码相关联。

---

程序代码：14.4-2　CommandButton1_Click播放按钮单击事件

---

注：该VBA程序中并未使用类似Application.Wait(Now+TimeValue(“0:00:01”))的代码，是因为使用Sleep函数更加流畅和自然。

这个案例的实现过程其实不算复杂，但36个定义名称的编辑和设定，以及图表赋值的过程，操作步骤虽然简单却没有效率。以下是针对这3个作业的VBA宏代码段，供读者参考：

---

程序代码：14.4-3　NameWrite 36个定义名称编写

---

---

程序代码：14.4-4　NameSet 36个定义名称设定

---

---

程序代码：14.4-5　ChartSeries图表系列与定义名称关联

---

动态区间

图14.4-6的案例常见于互联网中，尤其是股票数据的展示中常见，但遗憾的是在Excel中要直接实现这样的效果需要使用两个滚动条来实现，要达到同等效果需要相对复杂的VBA编程，且演示的效果非常不友好。为此笔者编写了一个区间滑块控件来实现该效果，同时最低限度地使用VBA编程来实现同样的效果。

图14.4-6　范围区域滚动条制作的股票图

该控件在使用前需进行RegSvr32注册，注册后即可在工作表中调用该窗体控件。需注意：该控件不支持打印和打印预览，否则控件将不可使用，需关闭后重新打开；Windows 7中注册需要使用管理员权限，注册后可能无法直接通过Excel的控件工具箱来调用；某些没有及时打上补丁的Excel 2007/2010版本不支持，在Excel 2003工作表中可直接使用，所有版本都可以打开运行对应版本的本节案例工作簿。以下是结合该控件的案例制作说明：

1．分别定义以下6个定义名称：

2．设置图14.4-6右侧S4～S9单元格的样式。

3．在窗体控件的［其他控件］中查找控件，并绘制到单元格中。Windows 7下若注册后，若找不到该控件，可在设计模式下复制粘贴本案例中的控件对象到你需要的工作簿中。在Excel2010中若出现：运行时错误’1004’，可鼠标点击［确定］后，以Excel2007和2010的新格式保存该文档，退出，再次打开，控件就被绘制到工作表中了。

4．选中该控件，在VBE工作表宏代码编辑区编写如下代码：

---

程序代码：14.4-5　ctlRangeSlider1_Scroll区间滑块的滑动事件

---

5．选中原始数据，制作股票图，并美化为案例图14.4-6的图表样式。

6．依次修改图表系列的SERIES公式如下：

7．选中图表，并设置图表图表区背景为无，然后选中I4:P5单元格区域，合并单元格并设置公式为：

注：该案例和其他交互式图表相同，坐标轴均使用固定刻度设定。

学习思考

---

不同版本的Excel ，视是否有及时更新补丁和Windows作业系统影响，在这个案例中的交互体验完全不同，有些版本来得顺滑，有些则会出现卡顿和闪屏。

---

图表事件

图14.4-7案例模仿自《经济学人》杂志网站，这个案例中使用到了热力数据地图，窗体控件为切换按钮，且利用了图表事件来参与交互式体验，使单击图表系列变为触发图表数据变化的触发器。

图14.4-7　模仿《经济学人》杂志网站制作的交互热力数据地图

由于图14.4-7案例右侧的条形图使用了排序模式，实现过程使用到了辅助数据区来进行数据的排序作业。以下是详细的制作说明：

1．将图表和原始数据所在工作表分离，原始数据放置到Chart Data工作表中，包括了GDP、人均GDP、人口、出口4个大类，依次对应地图上方的4个切换按钮，每个大类又有3个小项，分别和图表系列相关，如图14.4-8所示。

图14.4-8　图14.4-7案例的原始数据［数据来源自：《经济学人》杂志网站和网络］

2．参考案例11.4－1的实现过程，制作XY散点数据地图，地图数据放置在Map Data工作表中。

3．在图表右侧的AD4:AI38单元格区域中分别设置如图14.4-9所示的辅助数据列来进行对原始数据区数据的排序，各列单元格详细公式设置如图14.4-9箭头所指。

图14.4-9　图14.4-7案例的辅助数据区域设定

4．选中辅助数据AF4:AE38，在地图右侧制作条形图表，并美化图表为图14.4-7案例右侧的条形图样式。

5．定义“Chart2Data”、“MapXData”、“MapYData”3个的定义名称，公式依次如下：

6．选中条形图，添加新图表系列“辅助”，并修改图表系列的SERIES公式如下：

7．选中XY散点地图，添加新图表系列“辅助”，并修改图表系列的SERIES公式如下：

8．设置XY散点地图图表区和绘图区为透明样式，合并C6:I7单元格区域，并设置公式为：

9．分别合并C18:F18、C19:F19、C20:F20、C21:F21单元格区域，然后设置C18:F21单元格区域，如图14.4-7案例左下方红色方框的样式，并依次设置公式为：

10．分别设置AB5:AB9单元格区域的样式如图14.4-7案例最右侧单元格所示，在XY散点地图上方依次添加4个窗体切换按钮，并在设计模式下，依次修改Caption属性为“国民生产总值”、“人均国民生产总值”、“人口”、“出口”。最后在VBE的Test1工作表宏代码编辑区编写如下代码：

---

程序代码：14.4-6　Worksheet_Activate工作表激活事件

---

---

程序代码：14.4-7　ToggleButton1_Click　第1个切换按钮被按下

---

---

程序代码：14.4-8　ToggleButton2_Click　第2个切换按钮被按下

---

---

程序代码：14.4-9　ToggleButton3_Click　第3个切换按钮被按下

---

---

程序代码：14.4-10　ToggleButton4_Click　第4个切换按钮被按下

---

---

程序代码：14.4-11　XYChartUpdate　按钮切换时XY散点地图热力颜色更新

---

11．在VBE界面，新建一个类模块“Class1”，编写如下代码：

---

程序代码：14.4-12　Class1类模块代码

---

12．在VBE界面，新建一个模块“模块1”，编写如下代码：

---

程序代码：14.4-13　模块1的ClassClose　释放图表事件的关联

---

---

程序代码：14.4-14　模块1的ClassOpen　建立图表事件的关联

---

13．在VBE界面的ThisWorkBook工作簿打开和关闭事件中，编写如下代码：

---

程序代码：14.4-15　Workbook_BeforeClose　工作簿关闭事件

---

程序代码：14.4-16　Workbook_Open　工作簿打开事件

---

当Excel图表绘制在图表工作簿中时，可在VBE的图表工作表中直接调用编写Excel图表事件代码，若是图表嵌入在Excel的非图表工作表中，则必须以类模块的方法来调用这些事件，图14.4-7的实现就是使用类模块的一个案例。而这类事件在日常图表制作中使用相对较少，只有在较为复杂的交互式图表中才有可能使用到。以下是Excel支持的图表事件列表，如表14.4-10所示。

表14.4-10　Excel支持的图表事件

注：笔者确认SeriesChange事件在Excel 2007/2010中亦不适用，但在Excel 2007/2010的帮助文档中并未列出该事件不被支持。

小技巧

---

关于图表名称

涉及VBA编程的图表，一个好的建议是使用英文字母来命名。毕竟Excel底层基于英文，使用英文的兼容性强过汉字。修改图表的名称请使用选择对象按钮，然后鼠标选取图表，在公式编辑栏前的输入框中修改。

14.4-2文件夹下包含了笔者开发的RangeSlider.ocx控件，在使用该控件前请单击“注册RangeSlider.bat”批处理文件注册，卸载请使用“卸载RangeSlider.bat”批处理文件，Windows 7需管理员权限。本章案例目录的Next.ico和Pause.ico图标对应图14.4-3案例的播放按钮，其案例文档为14.4-1。

Excel数据透视表是强大的数据整合处理引擎，可非常方便和灵活地对数据进行汇总、加工、处理。架构在数据透视表基础上的数据透视图，具有灵活的数据查询功能，是Excel中最为直接的交互式图表。可借助数据透视表，结合定义名称，使用常规图表来制作交互图表。

传统应用

在实际作业中，我们往往需要处理未经加工处理的杂乱数据。图14.3-1的表格是一个混合了多种产品、多状态、多属性的进货与销售数据表，共使用了761条记录，其次该数据表的数据日期分布很零散，需按年按季度来汇总统计。这样的数据若使用图表表达，就必须借助如图14.3-2所示的数据透视表功能。否则，使用一般方法来处理数据将非常费事，且很难快速制作出有价值的图表。

图14.3-1　数据透视图案例原始数据

图14.3-2　原始数据整理后的数据透视表

在使用原始数据来生成图14.3-2的数据透视表时，该透视表的布局如图14.3-3和图14.3-4所示。当生成透视表后，需选中透视表的“发生日期”字段，单击鼠标右键，在弹出菜单中：Excel 2003选“组及显示明细数据”> “组合”；Excel 2007/2010选“创建组”。在弹出的对话框（如图14.3-5所示）中，点选“季度”与“年”，Excel 2007/2010另需在“年”字段的设置“布局和打印”中勾选“以表格形式显示项目标签”。

图14.3-3　Excel 2003数据透视表布局

图14.3-4　Excel 2007/2010数据透视表布局

图14.3-5　字段分组设置对话框

由于数据透视表在处理该案例的“数量”和“金额”两项求和项时，交互作业无法直接使用图表上的交互按钮来完成，必须借助“数据透视表字段列表”窗格来处理，因此这个案例中分别使用如图14.3-6所示的两个数据透视表来处理“数量”和“金额”两项求和项。

图14.3-6　原始数据整理后的最终数据透视表

注：两个数据表间要保留足够的间隔单元格，因为数据透视图的交互展示，是直接对数据透视表的操作，这种操作会导致单元格格式的变化，尤其是透视表间的间距太小时。

当完成数据透视表的布局后，制作数据透视图就变得非常简单。直接选中相应的数据透视表，然后制作簇状柱形图，依次可生成如图14.3-7所示的两个数据透视图，这两个图表的交互作业完全依赖于数据透视图的交互按钮。

图14.3-7　最终数据透视图

虽然此类数据透视图表制作起来非常简单，但可视化效果并不好。以下列举了一些数据透视图表的局限，供读者使用数据透视图表时参考：

1．按钮类元素所占的图表面积太大且无法移动位置，由于数据透视图的数据信息一般都比较大，因此展示效果给人较强的局促感。

2．数据透视图中图表元素的格式化设置，往往随交互按钮的触发操作而导致丢失，因为每次的数据交互，数据透视图都进行了再次完全重绘。

3．数据透视图对组合类图表的支持非常有限。有时在交互操作后，系列的图表类型会发生变化，尤其是在改变图表系列个数时。

数据的OLAP操作

数据透视表可以实现数据处理中经常被提及的OLAP的多维分析操作，包括钻取（Drill-down）、上卷（Roll-up）、切片（Slice）、切块（Dice）以及旋转（Pivot）。这是一组对普通人而言不知是何物的术语，为了更好地使大家理解这些概念，以下将以图14.3-1的数据表展开简单说明。

名词解释：

---

■　OLAP（On-line Analytical Processing，联机分析处理）：

基于大数据量、多指标基础上的各种数据整理与分析方式集合，简单理解就是：Excel中基于数据透视表的各类透视过程。对于复杂的大型数据集而言，Excel的处理能力不足应付时，可以使用微软专门基于SQL Server的工具集Microsoft SQL Server OLAP Services，该工具是为处理数据的OLAP应用而生。

❶数据立方体（Data Cube）：

其实就是需要分析的原始数据集合，此处图片只是示意，实际的数据维度远不止3维。此处可以理解为图14.3-1的数据表。

❷钻取（Drill-down）：

将汇总数据拆分成更细节的数据，比如图14.3-2的透视表在未进行分组时的日期状态。

❸旋转（Pivot）：

即互换维的位置，简单而言就是行列转换，Excel中的转置功能即属于此操作。如基于图14.3-2的数据表透视表将“发生日期”和“品种”两个字段进行相应的交换。

❹切块（Dice）：

选择特定区间数据或者某特定指标进行分析，如选择图14.3-1数据表中所有产品上半年的销售数量与销售金额。

❺切片（Slice）：

选取特定维度的数据进行分析，如只选择图14.3-1数据表中各季度产品A的销售数量数据。

❻上卷（Roll-up）：

钻取的逆操作，简单地理解就是Excel的分类汇总，如只将图14.3-1数据表中日期销售数量汇总为各年度数据。

---

这些概念也许显得晦涩和难懂，但其实在制作交互式图表的过程中，我们都有意或无意在使用这些操作方法，因为交互式图表依赖这样的数据操作。如果单纯使用函数公式来处理如此庞杂的数据，将变得不具可操作性，因此借助Excel的数据透视表功能就显得非常必要。随着Excel的推陈出新，Excel在不断加强数据透视表功能，在Excel 2010中，已经添加了切片器功能，该功能基于数据透视表和SQL应用。

与定义名称结合

图14.3-7　透视图表在操作上的不友好，导致我们必须要考虑一个非常现实的问题：如何借助常规图表结合数据透视表来制作交互式图表。有个非常令人困扰的问题是：如果图表引用的数据来源自Excel的数据透视表，该图表会被Excel强制变为数据透视图。

Excel 2003中，可以先使用图表向导来生成一个空白图表，然后使用数据拖曳方式添加系列，但该方法并不非常可靠，在不确定的状况下，该图表依然会被Excel自动设置为数据透视图。在Excel 2007和2010中则完全无望，拖曳功能也不再被支持。但并非没有办法来实现这样的操作，借助定义名称的方法，传统Excel图表和数据透视表可以被有机地结合在一起。图14.3-8所示的案例，就是图14.3-7的常规图表演绎，该图仅使用了一个图表来展示“数量”和“金额”两项求和项。

图14.3-8　数据透视表和定义名称结合的案例

这个图表的制作过程基于图14.3-7案例数据透视表，通过使用4个数据透视表来实现，这些透视表和图14.3-3的结构基本相同。制作方法说明如下：

1．参照图14.3-6数据透视表的生成过程，生成如图14.3-9所示的4个数据透视表。

图14.3-9　图14.3-8案例4个数据透视表

2．新增“类别”、“状态”两个辅助列，相关内容参照图14.3-8设置即可。再依次新增4个触发链接列“状态”、“类别”、“年度”、“分类”，除“分类”为5个单元格外，其余皆为1个单元格。

3．分别添加以下工作表控件：两个组合框、两个分组框、3个选项按钮、5个复选框。分组框并不参与触发作业，仅为视觉分组，排布和外观设定参照图14.3-8设置即可，数据源区域和单元格链接从上到下依次为：

1. 组合框1中数据源区域为$C$5:$C$6，单元格链接为$F$6；
2. 组合框2中数据源区域为$D$5:$D$6，单元格链接为$G$6；
3. 3个选项按钮的单元格链接为$H$6；
4. 复选框1的单元格链接为$I$6；
5. 复选框2的单元格链接为$I$7；
6. 复选框3的单元格链接为$I$8；
7. 复选框4的单元格链接为$I$9；
8. 复选框5的单元格链接为$I$10。

4．分别定义“AData”、“BData”、“CData”、“DData”、“EData”、“分类”、“刻度”7个定义名称，公式如下所示：

5．生成无数据的簇状柱形图，依次添加6个图表系列，这6个图表系列的SERIES公式依次为：

6．将系列6“刻度”的图表类型改为XY散点图，并将图表按照图14.3-8右侧图表样式美化即可。

图14.3-8案例图表是为了学习如何将数据透视表和定义名称结合，以及如何使用选项按钮和复选框工作表控件。该案例图表仅比直接使用数据透视图的可视化效果好，但并不是一个值得称道的图表。

小技巧

---

交互式图表动态设置数值坐标刻度，使其保持一致。

由于涉及“金额”和“数量”两个不同量纲，图表的数值坐标无法直接手工设定为统一刻度，图14.3-8的系列6“刻度”即是为保证刻度的一致性而设（图表中并未隐去，实际作业中可设置其为无标记点）。

注：本章所有的案例，均对图表的数值坐标进行了统一设置。

文档14.3-2是图14.3-8案例使用列表框工作表控件的复选选项实例，文档中获取复选数值使用了xlm4.0宏表函数。该文档仅限在Excel 2003中使用，在Excel 2007/2010中已不可使用，因为该功能已经不被支持，若需使用应借助VBA来处理。

定义名称是Excel图表和函数公式的一类高级应用，此类应用完全脱离工作表的单元格存储限制，是运行在Excel后台的一组逻辑计算或单元格区域引用。其可以被图表和其他函数直接调用，使用Excel定义名称制作交互式图表最为常见，也是交互式图表中最为灵活的一类应用。

动态更新

图14.2-1是图14.1-9完全定义名称的演绎，没有使用任何辅助单元格区域。这种方式的好处是可以更好地组织工作表的页面布局，并减少由于鼠标误操作带来的错误，但同时该方式也增加了制作难度，因为调试和修改这些定义名称并非易事。

图14.2-1　完全利用定义名称以行为单位制作的交互图表

图14.2-1案例动态选择图表的源数据区域以行为单位，图14.2-2的案例则是一个以列为单位的案例，这两个案例的唯一不同是OFFSET函数中：上（下）偏移的行数、左（右）偏移的列数，以及返回引用区域的行／列数的赋值。

图14.2-2　完全利用定义名称以列为单位制作的交互图表

图14.2-2的触发器此处使用的并非数据有效性，而是工作表控件，这种控件有别于窗体控件，工作表控件可以直接通过关联单元格实现触发驱动，而窗体控件则完全需要使用VBA代码来驱动。图14.2-3列出了在不同Excel版本中的这两种控件，❶是工作表控件，❷是窗体控件。Excel 2003调用这两个菜单，需要鼠标移至菜单栏空白处，鼠标右键分别勾选“窗体”和“控件工具箱”；Excel 2007和2010只需在Excel选项的自定义功能区中勾选开发工具即可。

图14.2-3　交互图表使用的控件触发器

图14.2-4是图14.2-2案例的组合框触发器属性对话框。调用该对话框只需鼠标右键选中控件，并在弹出菜单中选取“设置控件格式”即可。在该对话框中的数据源区域对应于显示在下拉选框中的项目，此处可使用定义名称；单元格链接对应于下拉选框响应鼠标选取项的数值序号存储单元格。工作表控件也可指定宏，在单击控件时可触发执行VBA宏代码。

图14.2-4　工作表控件组合框的属性对话框

当熟悉了定义名称和工作表控件，就可以将这两者结合起来使用，并在图表源数据中引用这些定义名称。此时图表就变得动态交互了，整个过程其实并不复杂，关键在于Excel函数公式的构建。

切片展示

大多数情况下，使用Excel制作交互式图表和切豆腐一样。图14.2-5案例的原始数据区就是一个具有代表意义的交互图表，鼠标每选取一项，图表上蓝色折线即会高亮标注此项代表的数据。此案例中所有数据都被按行放置到了图表中，以方便比较，但由于焦点只有一个，整个图表的呈现效果并不凌乱。

图14.2-5　数据切片展示的交互式图表

这个案例的制作并不复杂，制作过程说明如下：

1. 选择数据区，数据产生在行，生成折线图。
2. 将这12个系列依次设置线形为最细，颜色为淡灰色，数据标记为无线无填充。
3. 定义“名称”和“Y值”两个定义名称，公式详见图14.2-5左侧表格下方。
4. 额外添加一个系列，系列SERIES公式见图14.2-5右侧下方箭头所指位置。
5. 添加一个列表框工作表控件，数据源区域为“№”列数值，单元格链接为控件下方的H17单元格，选定类型为单选。

多层次

更多的情况是：我们所面对的数据，往往并非单纯的一个层次，可能的情况为多个层次组成。图14.2-6案例的原始数据就是由大类和小类两层结构组成的案例，该案例是某公司不同产品月度销售数据的动态展示图表，触发驱动采用了两个工作表组合框控件，来分别管理不同的层次结构。

图14.2-6　多层次定义名称交互图表

这个图表的制作过程并不复杂，但是相对较为繁杂。关键的部分是定义名称，首先图表系列的引用数据采用的是两层OFFSET函数来响应触发选择，其次是第2层触发使用的工作表组合框控件，其数据源区域为定义名称赋值引用，而非单元格引用。以下是详细制作步骤：

1. 选择数据区行表头和下方第一行单元格，数据产生在行生成柱形图，并美化为图14.2-6右上侧图表样式。
2. 将横坐标设置为分类坐标，并设置坐标格式为：［=39814］yyyy”-“m;”‘”m。
3. 定义“Data”和“DataList”两个定义名称，公式详见图14.2-6右侧最下方。
4. 修改图表系列SERIES公式为图14.2-6橙红色箭头所指位置。
5. 添加两个组合框工作表控件，第1个控件数据源区域为数据区下方的“分组”列数值，单元格链接为D19单元格；第2个控件数据源区域为定义名称“DataList”，单元格链接为D20单元格。

多视角

图14.2-6案例亦可能面临如下的使用情况，即除了表达横向比较外，也可能会有单个月份的产品纵向比较的需求。此时等于在图14.2-6案例基础上又增加了1个比较维度，其为“月份”和“具体月份”两个层次，且这个维度表达在图表上必须为横向比较的条形图表，图14.2-7案例右上角的图表即是该需求的实现。

图14.2-7　多层次多角度定义名称交互图标

这个图表的制作过程建立在图14.2-6案例实现基础上，额外增加了一个图表系列来实现纵向比较。由于是柱形和条形图的组合，因此该图表是一个主次坐标图表，在处理数值轴坐标时，这个案例使用了XY散点来模拟，否则效果不尽如人意。当在第一个下拉列表框中单击“月份”，则显示条形图表系列，其余选项为柱形系列，第2个下拉列表框亦跟着变更选项为月份列表。此处当显示条形系列时，为了将柱形系列隐藏，柱形系列的分类标志和数值指向了空单元格引用。以下是制作方法：

1．选择数据区行表头和下方第一行单元格，数据产生在行生成柱形图，并美化为图14.2-6右上侧图表样式。

2．将横坐标设置为分类坐标，并设置坐标格式为：［=39814］yyyy”-“m;”‘”m。

3．新增“月份”、“辅助X1”、“辅助Y1”、“辅助X2”、“辅助Y2”5个辅助列，涉及公式如下：

“辅助X1”：=0.5+7/5*(ROW()-19)

“辅助Y1”：=IF($B$19=4,25,7)

其他的参照图14.2-7设置即可。

4．分别定义“AItem”、“BItem”、“ItemList”、“AData”、“BData”、“Xdata”、“Ydata”、“XErrData”和“YErrData”9个定义名称，公式如下：

5．修改柱形图表系列SERIES公式为：

6．新增图表系列，并更改系列图表类型为条形图，修改图表系列SERIES公式详见图14.2-7橙红色箭头所指位置。

7．将新增图表系列的填充色如图14.2-7右上侧的图表样式美化。

8．再新增一个XY散点图表系列，用来模拟数值轴刻度。该图表系列的系列公式为：

具体使用数据见“辅助X1”、“辅助Y1”。“辅助X2”、“辅助Y2”两个辅助列分别用来设置XY散点误差线X的正误差值和误差线Y的负误差值，依次使用“XErrData”和“YErrData”两个定义名称。

9．添加两个组合框工作表控件，第1个控件数据源区域为数据区下方的“分组”列数值，单元格链接为B19单元格；第2个控件数据源区域为定义名称：“DataList”，单元格链接为B20单元格。

该案例的制作步骤相对繁杂，读者需要花些时间来理解定义名称和各个系列的设置。以上的过程仅是一个非常简单的描述，具体的实际操作，需要读者参照案例，反复演练学习。

提示

---

定义名称的命名字符需注意：

1）不能以数字开头，或单元格地址、以数字作为名称，如2Data或B3都不可以。字符中不可包含字母R、C、r、c，因为和单元格的R1C1引用样式冲突。名称中不能包含空格，但可用下划线或点号代替，其它非字符尽量避免使用。

2）字符最大不能超过255个字符。

3）勿使用保留字段，比如：Print_Titles和Print_Area。

实际工作中，对于周期性数据，我们有可能需要制作由触发器驱动的Excel图表，甚至是由多个数据的动态变化来构成一个小型图表应用系统，以此来动态展示数据。此时需要使用建立在单层次表达基础之上的多层次表达，以适应这种新需求，Excel的相关功能可以帮助我们实现这样的需求：

1. 图表可进行交互式的数据查询及呈现；
2. 图表可动态适应数据个数的变化；
3. 可动态看到数据的变化过程趋势。

通过Excel自动筛选功能及列表功能，或使用查询函数的辅助数据区域，是实现图表交互式数据查询最为简单和快捷的方法。

利用Excel定义名称封装的函数公式，在图表系列源数据中进行引用，是相对灵活和简便的数据交互查询和动态展现方法。将Excel工作表控件作为触发器来使用，交互过程更具良好的人机交互体验。

Excel数据透视表提供了强大的数据整合查询功能，利用数据透视表来制作的数据透视图在查询上可实现复杂的交互数据查询，配合Excel定义名称来使用在格式设置上更加灵活。

一个好的展示效果离不开多种Excel功能的整合使用，Excel的VBA也是其中之一，尤其是Excel图表具有多种基于VBA编程的事件响应过程，可供我们将图表变为触发器去参与交互过程。

筛选及列表

数据查询是一个“展示需要、隐藏不需要”的筛选过程。Excel中可以被使用来进行图表可视化交互的查询功能有：自动筛选、列表和Excel函数。通过这些简单的功能应用，可使查询结果变为具有动态交互效果的图表展示。

自动筛选

Excel提供的筛选功能分为：自动筛选和高级筛选两种。自动筛选功能基于下拉列表式的触发机制来完成筛选作业，该功能其实是将不符合筛选条件要求的数据行进行了隐藏。图14.1-1的案例图表正是基于此类引用的一个典型案例，整个制作过程相当简单，仅是在柱形图表系列的源数据引用区域使用了自动筛选功能而已，图14.1-1左侧的下拉列表是该方法的筛选操作。

图14.1-1　利用自动筛选功能制作的交互图表

此方法需要勾选图表选项：只绘制可见单元格数据选项。自动筛选区请包含列标题，这样的好处是可以使首行不参与筛选。

列表

列表是自Excel 2003起，封装并强化了自动筛选的一个功能，该功能可动态适应数据选区。当数据选区中有新数据被加入后，列表区会自动扩充，当图表系列引用的源数据是该列表区，则图表系列的数据点个数会自动进行相应增加。这减少了反复操作图表、修改源数据引用区域的困扰。

创建一个列表区非常简单。无论Excel版本，仅仅只需选中我们需要的数据选区时，然后按下键盘Ctrl＋L组合键，根据提示勾选：表包含标题，单击“确认”按钮即可，如图14.1-2所示。

图14.1-2　创建列表对话框

Excel对于列表亦提供了相应的列表工具栏来进行管理，这使得列表变得更加易于操作。如图14.1-3所示为Excel 2003列表工具栏，图14.1-4所示则为Excel 2010列表选项卡。

图14.1-3　Excel 2003列表工具栏

图14.1-4　Excel 2010列表选项卡

熟练掌握列表的使用后，即可将图14.1-1的案例引用源数据区直接转换为如图14.1-5所示的列表，来进行相应的管理。该方式完全基于自动筛选功能，故图表选项同样需勾选“只绘制可见单元格数据”选项。

图14.1-5　利用列表功能制作的交互图表

辅助区域函数筛选

使用辅助单元格区域，同样可以利用Excel的查询函数来实现数据的检索和筛选。Excel提供了诸如OFFSET、MATCH、INDEX、CHOOSE等函数来进行相应的数据查找。

此处的触发器使用了数据有效性的序列功能，该功能可使Excel的单元格具有下拉选框。要使某个单元格具有数据有效性设置，只需按键盘Alt＋D＋L组合键即可弹出“数据有效性”对话框，在“设置”选项卡中，“允许”选序列，“来源”选择下拉列表引用单元格区域，如图14.1-6所示。

图14.1-6　数据有效性对话框

图14.1-7案例图表即是基于此类引用的一个典型案例，图表数据源引用辅助单元格区域的数值，当鼠标选取筛选触发单元格时，触发辅助单元格区域函数进行重算，来响应用户的交互作业。

图14.1-7　利用函数公式辅助区域制作的交互图表

提示

---

特别说明：

1）大部分的查找与引用函数要求查找源数据进行必要的排序。虽然使用数组公式也可完成排序，但这样的函数应用往往复杂，且投入与产出比不佳。

2）制作交互式图表的首要任务亦是将数据的排布变得有序，以方便制作图表和简化图表制作的难度。

---

图14.1-7案例图表每次筛选的结果均为3行，相当固定。当筛选的行数不确定时，则使用上述方法并不能有效解决这个问题。若使用辅助区域来处理不确定行数的筛选，出现的最大问题便是如图14.1-8所示的状况，图表将以“0”值方式呈现筛选结果中没有的数值，如果图表是线形类图表，在视觉中将会非常糟糕。

图14.1-8　利用函数公式辅助区域制作的步长不等交互图表

图14.1-8的视觉呈现效果并不理想，要解决这个问题只需使用定义名称的方法，来限制图表系列的引用区域行数即可，如图14.1-9所示。一般而言，建议图表使用工作表级的定义名称，这样引用较为方便，不易受工作簿名称限制，且复制工作表时较易进行移植。

图14.1-9　利用辅助函数公式区域与定义名称配合的交互图表