Excel动态图表制作技巧

石晓玲　宋　越

（泰州职业技术学院信息技术学院　泰州　225300）1（泰州市人民检察院　泰州　225300）2



Excel动态图表制作技巧

石晓玲1宋越2

（泰州职业技术学院信息技术学院泰州225300）1
（泰州市人民检察院泰州225300）2

摘要本文重点介绍了创建Excel动态图表的三类方法，对制图过程中涉及的关键技术、技巧和注意点做了深入的探讨。

关键词Excel动态图表函数VBA

信息社会，从商业组织的市场分析、金融部门的成本控制、生产企业的质量管理，到各行各业的考勤统计和薪资计算，数据化管理已成为大数据时代的必然选择。Excel作为PC机普及以来使用最为广泛的软件之一，集成了最优秀的数据计算与分析功能。除此之外，Excel还能将枯燥乏味的数字报表转化为生动形象、有说服力的图表，使数据关系得到最直接的表达。

动态图表是利用Excel的函数、名称、空间等功能实现的交互展示图表［1］。与普通图表相比，动态图表突破了空间限制，能够展示出更多的数据信息；重新定义数据输入输出的方式，将静态的传统的图表以更灵活、交互、实时的方式展现出来；实现对静态图表的批量制作，大大节省制图的时间。

本文对excel动态图表的在制作中涉及的方法及技巧作出深入探讨。

一、利用公式和控件构建动态图表

使用公式和控件创建动态图表，需要四个控件即数据源、函数、控件、图表共同合作。其中数据源的形式决定了数据的组织方式并决定了动图的交互展示的维度；从数据源中提取建图数据常用的函数有VLOOKUP、INDEX、OFFSET、CHOOSE、MATCH等函数；为使图表具有交互性，需要在数据选择时设置有效性或在图表中插入进行数据切换和展示的触发组件，如单选按钮、复选框、滚动条等控件；图表是数据展现的载体，应根据数据的特点选择合适的图表类型来展现，如柱状图、折线图、饼状图、条形图、面积图、散点图、股价图、曲面图、圆环图、气泡图、雷达图等，不同的图表还可以根据布局和样式进行设置，也可以几种图表组合表示［2］。

1、利用VLOOKUP函数组织数据源创建动态图表

本方法的关键步骤是创建如图3所示的动态数据源表。首先，将B3单元格设置数据有效性为月份（定义名称“月份”为1月～12月），生成如图4所示的动态下拉列表。而图中的C6～C8中使用查找函数VLOOKUP返回当月的销售额，如“C6==VLOOKUP（$B$3，$A$20：$D$31，A6+1，FALSE）”，即可生成动态数据源。由动态的数据源，即可创建销售金额簇状柱形图如图2所示。

2、利用控件创建简单的动态图表

原始数据如图5所示：

动态查询效果图，如下图6所示：

创建思路：

（1）该动态图表使用了Activex控件-组合框控件控制月份的显示。在此需添加辅助月份信息$K$2：$K$7，设置LinkedCell到辅助单元格$K$1，最大值为6、最小值为1，与月份选择相对应。

（2）在编辑图表的数据源时，软件对数据源格式有限制，不允许直接插入公式。故须事先定义好两个名称“销售额”、“销售表月份”，作为“系列值”“系列名称”的数据源。

（3）在定义数据源名称时，应使用INDEX或OFFSET函数建立动态数据区域。如：“销售表月份=INDEX（销售表！$D$1：$I$1，，销售表！$K$1）”。

3、多控件联动创建复杂动态图表

控件之间的配合还可以实现相互联动，从而实现更灵活的查询。如对同样的数据源图5进行查询时，通过单选按钮可以决定组合框链接哪个选项列表，进而决定以哪个维度来展现数据。

创建思路：

（1）使用单选按钮实现按人员查询和按月份查询，并用

IF来定义名称“查询方式”，映射到辅助列：“查询方式=IF（销售表！$K$11=1，销售表！$L$2：$L$9，销售表！$K$2：$K $7）”，其中$K$11是单选按钮链接到的单元格，$L$2：$L$9 和$K$2：$K$7为辅助列，存放人员名单和月份信息。

（2）使用组合框窗体控件，进行具体显示项的选择。如图7，在确认“按销售员查询”后，选择“徐琴”查询各月份销售数据。

（3）定义名称“销售数据源”、“横坐标”，对数据源的图表项系列、水平轴标签的设定。如“销售数据源=IF（$K $11=1，OFFSET（$C$1，$L$11，1，1，6），OFFSET（$C$1，1，$L$11，8，1））”，其中$L$11为组合框窗体控件链接到的单元格，实现数据源的动态选择。

在动态图表中，控件相当于“发射器”，在控件中做出选择时，控件就发出“信号”。函数则起着“定位器”的作用，根据控件的信号定位出作图需要引用的数据。其中，查找与引用类的函数的熟练使用是动图创建的基础。

二、使用切片器创建动态图表

Excel 2010版以后切片器的出现，使动态图表的制作变得简单轻松。对于清单类型的数据表，直接生成透视表（如图9所示），通过透视表变化出多个需要的表格或图表，最后插入切片器（如图10所示），用这个切片器驱动多个透视图。

特别指出，Excel 2010的切片器不支持表，需要使用透视表。对于非清单数据的二维表格制作透视表的步骤这里不做赘述。

三、使用VBA创建动态图表

VBA的全称是Visual Basic for Application，是基于Visual Basic for Windows发展而来的可视化编程工具。Exce VBA是指应用于Excel中、可扩展功能的VBA编程语言［3］。通过Exce VBA编程语言，能够实现Excel中所有工具的功能，创建图表也不例外。

对A1：D6区域的数据，使用VBA代码创建柱形图。当选择B1：D1中任一单元格时（如C1），图表显示如图12所示柱形图：

在工作表中选择A2：A6中的任一单元格（如A6），图图13表显示如所示柱形图：

由此实现图表的动态显示。行数据选择关键代码如图14所示，列数据选择类推。对于2010以上版本的Excel，包含VBA代码的文档不再允许被保存为.XLSX扩展名，而要保存为基于XML且启用宏的.XLSM格式。

VBA代码创建动态图表的方法具有良好的可扩展性，更利于在其他的平台上输出。

四、结语

1、无论哪种方法创建动态图表，都需要有：决定显示方式的控件或操作、定位数据源的公式或语句，才能在传统平淡的图表上加载神奇的动态效果。

2、插入表单控件、Activex控件及录制宏等操作需要首先启用功能区的开发工具选项卡。打开VBA窗口还可以使用快捷键操作。

3、图表的展示在PPT应用中比较频繁。Excel动态图表可以导出为Flash格式，插入PPT中进行动态演示。也可以将包含动态图表的Excel文件选择性链接入PPT（或者嵌入），在演示状态下点击该链接（或图标），即可进入excel进行图表的动态演示。

参考文献

［1］徐军泰著.Excel动态图表制作与职场数据沟通［M］.北京：机械工业出版社，2014.1.

［2］张伟等著.基于EXCEL图表的专题图制作技巧［J］.测绘与空间地理信息，2014.10.

［3］ExcelHome著. EXCEL2010实战技巧精粹［M］.北京：人民邮电出版社，2013.4.

石晓玲，女，1973.10，硕士，副教授。主要研究领域：计算机数据挖掘。

宋越，男，36，江苏泰州人，研究方向：计算机技术。

Skills on Making Dynamic Excel Chart

Shi Xiaoling1Song Yue2
（College of Information Technology，Taizhou Polytechnic College Taizhou 225300）1
（Taizhou Municipal People's Procuratorate Taizhou 225300）2

AbstractThis paper introduces how to create dynamic Excel charts by three kinds of methods，and makes a deep discussion on the techniques of drawing process.

KeywordsDynamic Excel charts Function VBA

中图分类号TP317.3

文献标识码B

文章编号160518-7283

作者简介