分类：复杂故事

使用Excel创建仪表板报表，应该能够清晰地表达数据、结构、布局和格式。使用分组和面板将仪表板定制为多个层次，这将会减少仪表板部件间的相互干扰，帮助使用者对数据进行解读。同时使仪表板结构更加富于弹性，包含更多综合信息。

布局

1．位置：由于仪表板所含信息的多样性，使大多数使用者在浏览和使用仪表板报表时，只会寻找自己所感兴趣的内容。切勿使仪表板失去关注焦点，将核心仪表板元素置于首要位置，这个位置要符合使用者的阅读习惯，放置位置同时要考虑到使用者了解仪表盘分层信息时的流程顺序，因此眼球的动线设计在仪表板设计上显得更加重要。

2．反馈：需要清楚地告诉使用者：当前看到的信息关乎什么内容，并且状况如何。这些内容应该使用文字来进行必要的辅助说明，可以是图表部件的标题，或是Excel单元格的批注。当然也可以使用图表鼠标悬停时的详细数据点数值说明。

层次

1．分组：通过将同类同级的仪表板部件组织在一起，然后使用分组框的形式将不同的部件进行区隔，这种区隔就完成了部件的分组。这些不同的分组构建出了不同的表达层次，每个分组可能会是另外一个分组部件的细化，也可以会是另一个视角的数据解读。分组框可以使用单元格来完成，也可以借助Excel的分组框工作表控件来完成，但不建议使用自选图形。

2．面板：通过向仪表盘添加面板也可创建层。Excel提供的最佳面板集是工作表，每个工作表可以视为一个面板集，面板间的切换可以使用超链接的方式来完成。除此外也可以采用和图14.4-7案例相同的方法，使用切换按钮来实现，这样的实现需要使用VBA。整体而言，使用面板会使仪表板变得更加复杂，尤其是在Excel工作表间使用时需要特别谨慎。

样式

1．尺寸：仪表板的部件尺寸，同组或同层应该保持一致。这有利于使用者在视觉中将其归集为一类来处理。同组内的元素间隔尺寸应该保持一致，不同组间应该有较为明显的间隔。

2．格式：仪表板上的同组元素，外观设置保持同一格式化样式，同时确保这些元素彼此间的自然对齐，有助于使用者快速捕获展示信息中最为关键的那部分信息。

3．效果：图表部件中切勿使用过多的效果，尤其是Excel 2007/2010的各类渲染效果，因为视觉干扰的原因，会导致更为详尽的数据细节难以被人理解。例如：在折线图上使用平滑线效果，会使折线线条变得圆滑，但是这却给眼睛拾取折线上每个数据点的位置带来了困扰。

案例

图15.2-1的仪表板案例数据来自图15.1-4。案例中有6个如图15.1-8所示的仪表盘部件，分别对应6个主要产品，放置在仪表板的最上方，归集为一组，这部分以展示实时数据为主。使用两个图15.1-9所示的条形图部件，并依次放置在仪表板左下方，各自归集为一组，这两个图表部件又可以视为一组，因为它与当前仪表盘月份相对应。使用4个如图15.1-9所示的折线图，归集为一组并放置在仪表板右下方。为使仪表板版面保持简单清爽，所有部件与数据集完全隔离，分别使用单独工作表放置，本案例中，Excel工作簿的“Data”工作表专门用来放置数据集，“Test1”工作表用来放置仪表板。

图15.2-1　图15.1-4数据创建的仪表板布局

图15.2-1仪表板所使用的部件及其外观样式设置，在读者学习完本书第2部分和第3部分内容后实现起来并不复杂。在此案例中6个仪表盘的制作数据由于比较复杂，专门使用一个“Chart”工作表来放置，指针数据分别使用6组数据来一个个对应。条形图和折线图分别使用两个系列，其中一个系列为辅助系列，仅有一个数据点，目的是为了保证图表刻度的最大值始终保持一致，不受当前显示数据影响，减少使用者获取数据时的误判。

“在天气、气候不佳时，飞行员的直觉往往是错误的。因此，在飞行中，要相信我们的仪表板。”

——摘自台湾大学教授刘顺仁《财报就像一本故事书》

将正确的数据使用多个图表展示出来，然后像“堆砌”砖块一样放置在一起的图表集合不能称为仪表板，这类应用几乎没有实用性，更像是一个图形垃圾堆。

行动前了解清楚自己的目标，并以此来着手准备数据是成功的一半。在搞清楚应该需要些什么部件，并且这些部件该如何进行布局前，采用何种形式、如何让其易于理解则是贯穿其中的要点。

从检视使用者需求和习惯入手，在第一时间，简单直接地提供有价值的信息，这是仪表板的基础。人们喜欢有条理的表达，将信息归类并建立层级关系是仪表板的筋脉所在。

每个仪表部件不应该是孤立的信息孤岛，不同部件间应该存在必要的相互关系，通过交互的方式，在部件间建立彼此的联系，更利于信息的整合，使诉求信息的表达更完整。

不同的仪表板，表达信息的方式方法，侧重点各不相同。把握好表达的技巧会使表达变得流畅，并使表达更加有力。

多个有机整合在一起的图表，整体应该有一个核心诉求，围绕该诉求来构建信息是仪表板制作的前提，并且需要剔除掉不必要的冗余信息，集中显示重点信息，勿使辅助信息和附加内容成为信息噪音。目的只有一个：提供最直接且有价值的信息用来辅助行动，而不仅仅是用来展示一堆数据。

BI与仪表板

在企业销售、财务、市场、制造、人事等诸多管理环节中，我们都会有意或是无意使用到多个交互式图表集合的报表，其实这些图表都可归属到仪表板的范畴。此类仪表板大多为企业的日常决策、警戒、分析服务，使用者往往具有决策权。所以需要设计与用户层次和知识水平匹配的仪表盘。同时应该具有侧重点，针对用户角色，也可能需要针对业务逻辑来设定预警提示。

名词解释：

---

■　商业智能（Business Intelligence，BI）

将企业中现有的数据转化为知识，帮助企业做出明智商业经营决策的一组工具集合。其前端展示多借用图解可视化的仪表板来展示数据当前状态，注重数据的时效性。

■　仪表板（Dashboard）

最初来自监视当前数据状态的各类物理仪表面板，最为典型的应用是汽车和飞机的仪表面板，如图15.1-1所示。当前，仪表板已经成为一种综合信息展示手段，其概念已经被广泛应用到各领域的决策、分析与事务之中，如图15.1-2所示。狭义上主要用于展现基于定性的关键业绩指标KPI，广义上将多个仪表、图表、报表等内容整合在一个页面上进行显示的都算作仪表板。

图15.1-1　Dash 8飞机仪表面板

图15.1-2　一个典型的BI仪表板案例

---

仪表板旨在帮助我们获取数据集合的全局视图，在此基础上利用查询和分析、数据挖掘、联机分析处理OLAP等方法进行分析和处理，将最后成为辅助决策的信息使用图示化方法来呈现，为管理决策过程提供支持。从这些概念的初始提出，仪表板就和大数据量的分析与处理捆绑在一起，成为一种大数据量的分析处理解决方案，虽然BI仪表板的应用有大量专业软件可供选择，其实使用Excel同样可以完成这样的应用。

数据集合

数据集的数据必须充足，并提供多维度视角，可供多个不同诉求的图表所使用。仪表板所展示的数据集可以是来自现有Excel报表和文档，也可以是借助Excel外部数据工具获取到的网页数据，或是像SQL Server这类外部数据库中的数据，当然也包括其他类型文档中的数据。

数据评估

多数情况下，我们习惯使用现有数据，直接开始仪表板的制作过程，但我们却忽略了一个重要内容：到底是仪表板的具体功能重要，还是手头的数据重要？开始前梳理一下与之相关的以下问题很有价值：

1. 通过仪表板需要让使用者知道些什么？
2. 展示是针对整个企业范围，还是局部流程、单一产品或部门？
3. 展示报告的影响是长期还是短期？是宏观战略层次还是特定战术角度的内容？
4. 数据的时间范围是历史记录数据，还是单一时间的快照数据？是偏重实时数据的监控，还是基于当前数据的趋势预测？
5. 展示界面是使用单一的图表样式，还是使用多个不同的图表样式？
6. 展示是否仅使用汇总分析信息，还是包括更多细节层次的展示？
7. 是直接明确告知使用者数据的结论，还是由使用者自主去评估数据的状态？

数据准备

上述问题的答案，需要对原始数据进行必要的整理，这些整理过程可能需要使用到数据透视表，或是使用Microsoft Query（如图15.1-3所示）来编写SQL语句代码。可以是每个图表关联一组数据，并置于多个不同的工作表中，也可以是多个图表关联一组数据，放在一个工作表中。

图15.1-3　Excel中的Microsoft Query

收集或创建数据集时，需要把重点聚焦在重要指标上，如性能指标、趋势和变化。日常被使用到仪表盘的数据大致分为量化的和非量化两种，可量化的数据大多为水平与垂直比较型数据，非量化的数据则为前10项不良、前5大客户、待解决的问题与任务等这类信息。制作仪表板前，需要将非量化的数据进行量化处理，比如使用排序序号来处理。

案例

图15.1-4左侧的数据集是某公司2000到2010年间6个主要产品的销售状态数据，包括了零售数量、批发数量、网购数量、平均单价4个维度的信息。

图15.1-4　仪表板案例数据集

该数据集以商业企业销售活动为例，当SWOT分析中的优势(Strengths)、劣势(Weaknesses)、机会(Opportunities)和威胁(Threats)出现变化时，销售战略或战术就可能需要迅速进行相应的调整及改变。本案例中数据范围限定在该公司6类主要销售商品上，涉及按月展示的多个年度数据。由于涉及多个数据维度，需要包括水平与垂直的详尽比较，原始数据为与时间刻度关联的数据，进行水平比较较为简单，但是使用垂直比较时，这个数据集则需要进行必要的排布变换，本案例中使用数据透视表来进行数据的变换，变换结果详见图15.1-4右侧的数据透视表。

注：图15.1-4中，右侧的数据透视表原始数据为左侧的数据集，所有字段排布在透视表布局的行中，如图15.1-5所示。当然，此处亦可使用筛选排序的方法实现。

图15.1-5　图15.1-4右侧的数据透视表布局

部件

构成仪表板的部件多为图表，除此之外仪表板还包括数值标记和交互部件。数值标记主要起诸如异常、正常等的定性反馈；交互部件使用在仪表板包含了交互作业的场景中，用来作为数据展示的触发器。

基本部件

a）基础图表：包括柱形与条形图及其堆积子类型、折线图、离散图等。

b）组合类图表：包括柱－线图、子弹图、箱线图等。

c）变形类图表：包括地图、仪表盘等。

d）迷你图或波形图：Excel 2010新增功能，如图15.1-6所示。

图15.1-6　Excel 2010的迷你图

知识扩展

---

推荐迷你图或波形图插件

Sparklines for Excel®：一个基于Excel自定义函数和类模块编写的插件工具，包含大量图表类型，功能强大。链接地址［注：该地址国内无法直接浏览］：

http://sparklines-excel.blogspot.com

---

e）表格也可视为部件，一般配合筛选和数据标记、迷你图一起使用。

数据标记

在Excel中，使用条件格式是数据状态标记的最佳解决方法。Excel 2003的条件格式非常简陋，但仍然是重要的标记方法。Excel 2007后，则可使用非常丰富的条件格式，尤其是图标集对于进行当前数据状态的标记最为方便，如图15.1-7所示。以下几类是主要的标记类型：

图15.1-7　Excel 2007/2010条件格式图标集

a）警告：使用类似红绿灯的效果来直观预警数据状态。

b）上升／下降：使用箭头来直观描述数据的趋势。

c）开启／关闭：特定数据的标注，强调超出警戒线的数据。

交互部件

这些部件一般为Excel的工作表控件和窗体控件。如下拉列表、按钮、复选框等。其目的往往是使用者参与到仪表板的数据展示中，帮助使用者将关注的数据局部进行放大处理，或是将多个细节串联起来进行判断。

案例

图15.1-4的数据集，根据15.1.2节的分析，使用仪表盘和条形图、折线图来展示数据。仪表盘用来展示实时数据，并配合使用条件格式来标记数据状态，如图15.1-8所示。条形图用来细化6个产品间的垂直比较，以及用来细化零售数量、批发数量、网购数量3个销售数量的水平比较；折线图则用来分别展示零售数量、批发数量、网购数量、平均单价的历史数据状态，这两类基础图表如图15.1-9所示：

图15.1-8　案例使用的仪表盘

图15.1-9　案例使用的其他图表部件

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　利用辅助函数公式区域与定义名称配合的交互图表