和图表相关的自动化工具

也许VBA对于读者而言,还非常陌生。其实我们也可不必纠结在此,Excel本身就有自带分析加载项工具,这个工具是基于常用概率统计的工具,可以帮助我们提高某些方面工作的效率。在互联网上,存在着各类基于不同应用的第三方插件,这些插件在图表制作过程中也是非常好用的帮手。

Excel自带工具

分析工具库是在完整安装Microsoft Office后可用的Excel加载项程序。但是,要在Excel中使用它,需要先进行加载,加载请参阅图13.2-8。使用时,Excel 2003:工具>数据分析即可;Excel 2007/2010:数据选项卡>数据>数据分析即可。该工具中包含了日常统计数据分析的基本工具集,如图13.3-1所示。

图13.3-1 Excel数据分析工具

以直方图为例,如图13.3-2所示,可以快速帮助读者完成频率直方图和频率柏拉图的输出。虽然这个工具制作此类图表的过程简单直接,但输出图表外观却不为人称道,后期依然需要做一些必要的修饰。

图13.3-2 Excel自带直方图对话框

使用第三方工具

在前面,笔者提及了Andy pope对Excel 2007图形图案填充的一个扩展插件,该插件可以帮助使用者完成自选图形、面积类图表元素图案填充,其界面如图13.3-3所示。

Andy的PatternFills.xlam加载项

图13.3-3 Andy的PatternFills.xlam加载项

:这个功能在Excel 2003/2010中存在,而无须额外使用加载项来扩充。在Excel 2007中应对图表系列互补色的填充设置上,该方法也只是一个折中方法,并不是一个完美方案,因为填充被额外地加入了图案。

使用互连网搜索,我们可以快速获得与Excel相关的各类加载项。此类插件一般多见于国外网站,国内相对较少;有些免费,有些则收费。图13.3-4所示是搜索示意,根据实际应用,读者可能要扩充相应的搜索关键字。

图13.3-4 互联网Excel加载项搜索

著名的peltiertech.com个人博客站点提供了大量复杂图表的制作工具,包括瀑布图、不等宽面积、箱线、股票等多种样式,如图13.3-5所示,网站地址见http://peltiertech.com/Utility/。

peltiertech.com的图表加载项

图13.3-5 peltiertech.com的图表加载项

创建自己的自动化工具

经常要使用Excel来制作图表的读者可能常常会碰到:图表系列标签引用单元格,需要手工使用“=”分别和单元格建立关联,这样的工作如若频繁出现,会使人几近崩溃。

笔者在本书第二部分的案例中大量提及这样的方法,所以有必要创建一个工具来使这个过程变得简单和高效,本节内容就是针对该应用的一个VBA宏代码实践。

需求分析

要创建一个基于Excel图表的VBA自动化功能扩展,核心代码段的部分也许并不复杂。录制宏:在图表中选中任意系列的任意数据点标签,使用“=”和对应单元格建立关联,可获得如下代码段:

ActiveSheet.ChartObjects(“图表1”).Activate

ActiveChart.SeriesCollection(1).Points(1).DataLabel.Text=”=Sheet1!R5C7″

简化一下上述代码,甚至只需一行即可。但这样的代码段并不能完成一个图表的自动化功能扩展,一个完整的小工具应该具备以下特点:

1.良好的Excel版本兼容性

要考虑到该工具可能被读者使用到Excel的各个已发行版本中,目前,至少要保证代码在Excel 2003/2007/2010上运行正常。

2.容易被加载和卸载

应该使用标准的Excel加载项程序集,可以方便地被加载和卸载,且在Excel的工作簿运行界面中不可见,只运行在Excel的后台。应该有相应的菜单按钮来驱动程序执行,这些按钮应该在程序加载时出现,程序卸载时从界面上消失。

3.可以在Excel图表任意元素上使用

考虑到使用鼠标无法选取有些图表上的系列,故工具应该是:只要鼠标选中任意Excel图表,且不论是图表中的何种图表元素,该工具都可被调用,同时列出相应的图表系列列表,以供使用者进行相应系列的选择。且如果鼠标选中相应系列和对应的系列标签,应该可以直接智能指定对应的系列标签,减少使用者选取时间。

4.充分考虑可能的期望问题

读者可能期望标签的引用有单元格链接和直接赋值两种类型可供选择;需要标签有诸如字体及其颜色和大小、数字格式和引用单元格保持一致的选项;对于单元格为函数引用的应该具有处理零值和空值选项;对于单元格为函数引用的应该具有处理错误值(包括#NA)选项。

5.充分考虑操作的不确定性

读者可能在操作时会出现以下误操作:选取单元格区域多于一行或一列;选取的单元格个数少于当前的系列数据点个数;或者没有指定要修改的系列。

建立用户选项面板

基于本章13.2.1节的分析,考虑到编程逻辑,首先需要在VBE界面(按下键盘Alt+F11组合键即可打开)中新建一个窗体,然后依次添加两个Frame框架控件、1个ComboBox下拉列表框控件、4个CheckBox勾选控件、1个CommandButton按钮控件,整体效果如图13.2-1所示。

图13.2-1 工具属性界面

Frame框架控件仅是为了美化,在程序执行过程,不参与代码运行;ComboBox下拉列表框控件用来存储所有图表系列,并且显示将被设置修改标签的系列名称;4个CheckBox勾选控件用来分别对应13.2.1节分析的第4点提及的4个期望;CommandButton按钮用来激活系列标签的设置与更改。

这是一个较为简单的用户窗体设计,该设计可以完全无视控件间的逻辑关系,其实将其看作是一个选项面板更加贴切。考虑到大多数人的习惯,选项中的“单元格动态引用”被默认选中,其他具体的相关设置,请读者参看示例文档。

该窗体在初始化显示后,需要将当前激活图表的系列名称加载到ComboBox下拉列表框控件中。这需要使用VBA代码来循环遍历图表的每个系列,并添加到ComboBox下拉列表框的选项内容,这样当单击右侧下拉按钮时,即可将系列名称点选到显示框中。以下是写入窗体代码区的初始化VBA代码:


程序代码 片段:13.2-1a UserForm_Initialize窗体初始化-判断当前鼠标的选取对象


上述代码的主要功能是确认当前鼠标选取的焦点是否在图表系列或系列标签上。若是,则获取对应的系列群组序号,以及其在群组中的序号。目的是将当前选取的图表系列名称直接指定到ComboBox下拉列表框的显示框中,从而减少手工下拉点选。


程序代码 片段:13.2-1b UserForm_Initialize窗体初始化


:如果读者曾尝试使用录制宏获取代码,可能会产生“为何此处使用Excel图表系列的群组,而不使用Excel图表系列序号”的疑问。这是因为在实际操作中笔者发现,当图表中包含一个以上相同图表类型的系列,并被分别放置在主次坐标系时,VBA中使用Excel图表系列序号会使操控不起作用。

执行标签设置及修改作业

当选项面板设置好之后,接下来着手处理按下窗体[操作]按钮后的响应代码。这个部分是整个工具的核心,主要作用是执行标签设置及修改作业,因为这个部分要处理13.2.1节分析的各种不同状况,所以在整个小工具中也是代码结构最复杂的部分。以下是VBA代码的分步处理过程:

1.检查系列是否指定

该步骤检查ComboBox下拉列表显示框中是否已经指定了要修改的图表系列。若无,则弹出对话框提醒使用者进行相应的选取,否则直接执行后续代码。以下为详细代码:


程序代码 片段:13.2-2a CommandButton1_Click按钮响应-检查系列是否指定


上述代码的执行效果如图13.2-2所示。

图13.2-2 程序代码 片段:13.2-2a的执行效果

2.指定引用区域并检查引用是否为单列或单行

该步骤确定标签引用的单元格区域,并且检查使用者在选取单元格区域时是否有误操作,导致引用的区域大于1列或1行。若有误操作,则提示并重新进行选取。以下为详细代码:


程序代码 片段:13.2-2b CommandButton1_Click按钮响应-检查系列是否指定


上述代码的执行效果如图13.2-3所示。

图13.2-3 程序代码 片段:13.2-2b的执行效果

3.查找系列在群组中的位置

该步骤循环遍历图表系列群组,查找ComboBox下拉列表显示框中的系列,并确定其群组序号和在群组中的位置。以下为详细代码:


程序代码 片段:13.2-2c CommandButton1_Click按钮响应-查找系列在群组中的位置


4.判断单元格个数

该步骤判断引用单元格个数是否和要修改标签的数据点个数相符,若不相符则给出3个不同的解决方法来处理。以下为详细代码:


程序代码 片段:13.2-2d CommandButton1_Click按钮响应-判断引用单元格个数


上述代码的执行效果如图13.2-4所示。

图13.2-4 程序代码 片段:13.2-2d的执行效果

5.标签设置及修改

在完成上述各类可能问题的检查,及相应的处理后,以下代码根据使用者在选项窗口中勾选的内容,执行标签设置及修改,这是整个工具中最核心的部分。


程序代码 片段:13.2-2e CommandButton1_Click按钮响应-判断引用单元格个数


关联到程序

完成了核心代码的编写,接下来要考虑的是设计调用代码。调用代码其实就是将图13.2-1所示窗体激活,但前提是先检查鼠标的焦点是否在图表上。这个过程为全局过程,放置在VBA模块中,以下是相应代码:


程序代码:13.3 DataLableChange启动标签更改面板


建立菜单系统

代码编写的最后部分是建立菜单系统,这些菜单被用来触发这个标签更改工具的执行,菜单应该包含加载和卸载两个部分。


程序代码:13.4 AddCommandBars菜单设置


有些误操作或其他异常状况会使Excel崩溃,或者直接关闭,而菜单并未卸载干净。所以加载菜单前必须要确保Excel中并不含有这些工具栏及相应按钮,否则菜单就会被重复创建,并可能引发程序执行错误。

上述代码被关联到工作簿的打开和关闭事件中,当相应的事件被触发时,Excel根据需要来加载菜单或是卸载菜单,以下是相关代码:


程序代码:13.5 Workbook_Open和Workbook_BeforeClose加载与卸载事件


上述菜单基于Excel 2003创建,虽然Excel 2007/2010使用基于XML格式的Ribbon菜单,但菜单依旧可以很好地和这些版本兼容。执行效果如图13.2-5所示。

图13.2-5 标签更改工具的菜单效果

:如果读者使用Excel 2007/2010,[加载项]选项卡一般会自动出现,不需要用户额外进行添加。如果在Excel 2007/2010中看不到,请在“文件>选项>自定义功能区>主选项卡”中勾选“加载项”。

设置文档

作为一个VBA自动化功能扩展,应该将文档存储为后缀名为xla或xlam的加载项文档(加载项:指为Microsoft Office提供自定义命令或自定义功能的补充程序),此类文档可以被Excel加载项管理器管理。需要选中VBE窗口中的文档ThisWorkbook项,在其属性中将IsAddin设为True,使文档中工作表在Excel前台窗口不可见,如图13.2-6所示。

图13.2-6 文档ThisWorkbook属性设置

为了使文档的代码不被人为误修改,导致错误,可以设置工程的保护密码。设置请在VBE的工程资源管理器窗口中,选择文档VBAProject,单击鼠标右键>VBAProject属性>保护,如图13.2-7所示。

图13.2-7 文档ThisWorkbook属性设置

:该保护功能并不能保护你的代码不被他人看到,这个密码的防护等级非常低,可以非常轻易地将密码移除。

其他

完成上述设置后,即可以使用Excel加载项管理器来管理该工具了,如图13.2-8所示。当然也可以直接打开文档来加载,但是要将文档关闭就必须要将Excel整体结束才可以,不过这样的方法并没有任何效率可言。

图13.2-8 Excel加载项管理器

但在Excel的默认菜单中,并没有集成该管理器的激活按钮。在Excel 2003中,选择“工具”菜单>加载项即可;在Excel 2007/2010中,请在“文件>(Excel)选项>加载项>管理:Excel加载项”中单击[转到]按钮即可。:Excel 2007的文件指按钮。

本书13.1节的案例,如果需要使用加载项的方法来处理,亦可使用本节方法,不过前提是要充分考虑到各种不确定因素的处理,使之可以使用在多个不同版本,并适用不同的工作环境,操作上更加简单方便。

建立自己的自动化图表模板

VBA是一般读者不愿过多接触和学习的内容,因为这个部分总是给人高深莫测之感。若要摆脱Excel图表制作中那些低效的大量重复作业,学习VBA宏代码就是一个必须要面对的问题。

办公室人员可能需要每周、每月都重复制作相同类型的图表,虽然Excel提供了图表的自定义模板和工作簿模板,但有时直接使用这些方法只能减少部分重复作业,通过使用VBA则可以大大简化这样的操作,我们需要的仅仅是用鼠标轻轻点击一下相应按钮即可。

我们是否对Excel图表制作中简单机械的重复工作痛恨不已?这些繁复的劳作不但消耗作图时间,而且会使我们对Excel图表制作的兴趣消耗殆尽。好了,如果这已经是一个必须要认真对待的问题,那么使用VBA来定制适合自己的工具集就变得再实用不过了。

除了自己动手来编写相应的VBA宏代码,其实我们也可利用Excel自带的分析加载项来制作相应的图表。此外,借助使用第三方工具集也是一个非常不错的方法。

:虽然掌握VBA这个工具对于学习图表大有裨益,但并非必须。如果我们不打算对VBA进行更深入学习,本章的工具请直接拿来套用即可。

建立自己的自动化图表模板:面对周报、月报、年报中如出一辙的图表,我们还在日复一日、年复一年重复着这种燃烧激情和岁月的作业吗?如果回答是肯定的,我们是否想过,并且尝试去使用更加高效的方法,来实现这些简单、重复、低效的工作?如果没有,请不要再犹豫,马上行动起来,建立属于自己的自动化图表模板吧!

需求分析

Excel提供了自定义图表模板和工作簿模板,这样的操作虽然可以简化作业,但是在某些方面,依然不能有效地解决问题。如图13.1-1案例图表是非常典型的帕累托图表,如果仅是更新图表的数据,来重复使用这个图表,每次都必须要面对如下问题:

需重复使用的帕累托图

图13.1-1 需重复使用的帕累托图

  1. 将数据从大到小排列;
  2. 重新设置“辅助”列的函数公式;
  3. 修改两个系列的引用数据区域大小;
  4. 调整数值纵轴的最大刻度和“数值”列的合计相同,计算并设置相应的主要刻度单位。

上述4项中,尤其第4项,使用VBA基本是最佳的选择,除此尚无最佳方案。作为模板应该可以直接另存为副本,且不包含任何多余的作图辅助内容。

使用VBA来演练

通过13.1.1节的分析,要完成图13.1-1的案例的VBA自动化图表更新,我们将通过以下步骤来进行学习:

数据排序

一般而言,我们制作帕累托图表的首要工作就是将数据从大到小排列,在图13.1-1的案例中使用的是自动筛选功能,其实也可直接使用按钮来完成。以下是这个部分的宏代码:


程序代码 片段:13.1-1a ChartUpdate图表更新-排序

ChartUpdate图表更新-排序


排序的部分完全来自录制宏,然后修改排序区域获得,并不复杂,需注意Key1:=Range(“C6”),此处是为首要排序基准。唯一有难度的是xRow变量,在此使用了[B65536].End(3).Row来赋值,这是为了和Excel 2003兼容,在Excel 2007/2010中,可以使用[B1048576].End(3).Row

:下述代码中凡涉及xRow变量的部分,一律使用红色字进行了高亮标注,请读者参考录制获得的代码进一步修改即可。

重置函数公式

接下来由于受到数据的最大行影响,“辅助”列的函数公式所在的单元格,及公式中的单元格引用均会不同,因此重置“辅助”列的函数公式便是需要考虑的内容。录制宏:选中“辅助”列的函数公式所在第一个单元格,鼠标指向编辑栏结尾处,按下键盘Ctrl+Enter组合键,向下拖曳复制函数到最大行。最终修改后的宏代码如下所示。


程序代码 片段:13.1-1b ChartUpdate图表更新-重置函数公式

 ChartUpdate图表更新-重置函数公式


图表设置及数据引用更新

由于在VBA代码的执行中,需要反复调用图表对象,所以在此处图表的设置和数据引用更新一并进行讲解。录制并修改后的宏代码如下所示。


程序代码 片段:13.1-1c ChartUpdate图表更新-图表设置及数据引用更新

ChartUpdate图表更新-图表设置及数据引用更新


:在录制宏时,修改系列引用请使用[源数据]对话框,请勿直接修改SERIES公式,这会导致后期宏代码修改的难度增加。

后期整理

完成第3步操作后,就已经完成了13.1.1节分析的4个要点,但工作表上的数据表依然需要设置统一的外观样式,否则由于每次数据有多有少,这将严重影响美观。以下是录制并修改后的宏代码:


程序代码 片段:13.1-1d ChartUpdate图表更新-后期整理


复制工作表

接着新建一个工作簿,并将上述步骤处理的工作表复制进去,并删除表格上的辅助部分。以下是录制并修改后的宏代码:


程序代码 片段:13.1-2a SaveAs图表更新-复制工作表


保存新工作簿

最后是将新工作簿保存到指定位置,完成最终宏代码的编写。以下是录制并修改后的宏代码:


程序代码 片段:13.1-2b SaveAs图表更新-保存新工作簿


其他

最后是向工作表添加触发器,来触发VBA宏代码的执行,如图13.1-2所示。推荐的做法是使用工作表控件按钮,切勿使用窗体控件,窗体控件需要在工作表代码区编写相应代码,这会给新建工作表带来麻烦,因为使用VBA来删除这些代码相对较为复杂,这也是代码写在模块,而非工作表代码区的原因。

图13.1-2 使用工作表按钮驱动VBA宏代码

:为了保证代码的兼容性,此处使用的按钮名称被重命名为“按钮1”。方法是单击鼠标右键,选中按钮,在编辑栏左侧编辑框中修改,见。图13.1-2右侧的Test Data数据区为调试代码的数据,因为函数公式会影响排序结果,所以请直接选取必要数据区域,复制并“选择性粘贴:值”到Chart Data数据区进行测试。

图表的自动化:整理图表系列边框及标签位置

有时,有些图表的制作过程并不复杂,但格式化图表则需要大量的时间,这样的工作不但费时费力,枯燥且毫无乐趣可言。通过录制一小段宏,修改为制图的小帮手,不但充满乐趣,而且会使我们很有成就感,何乐而不为?程序代码6.4-3即是这样的一个例子。


程序代码:6.4-3 VBA整理图表系列边框及标签位置


单击“整理系列”按钮后的效果如图6.4-3所示,该节点图由48个堆积条形图和2个XY散点图系列构成,由于考虑到本书叙述的结构性问题,该图表的具体制作本节不进行深入的讲解,请对此有兴趣的读者根据数据和图表进行学习。

图6.4-3 VBA整理节点图系列边框和系列标签

示例文档


本书所附案例文档6.4为本节所述案例的具体实现。

案例中包含了本节所述的所有VBA代码,读者可参照案例文档进行演练学习。

图表的自动化:批量生成图表

有时我们需要根据数据的不同分类动态生成多个图表,这些图表具有一致的外观,唯一不同的仅仅是图表系列所引用的数据为不同单元格区域,使用手工一个一个去生成,或是通过复制修改的方法来获得这些图表不仅费时费力,而且容易出错,使用VBA来完成无疑是一个简单高效的方法。程序代码6.4-2所示是一个根据不同产品动态生成图表的案例,这个案例绝大部分代码的编写也是基于录制宏,尤其是图表格式设置部分。


程序代码:6.4-2 VBA批量生成图表


该程序实现了根据产品名称动态生成图表,并格式化的功能,同时该代码不受产品所占单元格数量多寡影响。如图6.4-2所示为单击“动态生成图表”按钮后所生成的5个不同产品的销售状况柱形图表。

VBA动态批量生成图表效果

图6.4-2 VBA动态批量生成图表效果

图表的自动化:数值刻度的最大/最小值设定

无论版本,Excel图表均默认提供了自动设置的数值轴刻度,但遗憾的是并未提供类似图表系列数据源的单元格引用功能。有些时候我们制作的图表往往需要手动来设置刻度的最大/最小值,这些手动设置的数值在图表系列引用的数据源数据变化后,大多数情况下必须要再次更改最大/最小值设定,这是一件非常烦琐的事。如何使数值轴刻度的最大/最小值设定和单元格关联,便成为一个非常有价值的问题,解决这个问题的答案便是VBA。

图6.1-1的案例中分割布局的辅助系列使用了数值1,并设置在了次数值纵轴,采用百分比堆积柱状图。如果不设置次数值纵轴,并使用簇状柱形图,则该辅助系列的引用数值就必须始终是主数值纵轴的当前最大值。面临的挑战首先是如何动态获得主数值纵轴的当前最大值;其次由于辅助系列的引用数值变化,主数值纵轴的当前最大值亦会自动发生变化;最后是这个主数值纵轴的刻度设定必须在图表系列引用数值发生变化时动态变化,而非静态。

录制宏

单击“录制宏”按钮,选中分割布局的辅助系列引用的单元格数值区域,在编辑栏进行赋值,录得以下代码段,获取该段代码的目的是后续将图表刻度最大值赋值给单元格。


程序代码:6.4-1a VBA动态图表刻度 录制宏 获取单元格赋值语句


单击“录制宏”按钮,激活图表并选中图表主要数值刻度,在坐标轴格式中设定刻度最大单位为固定模式,录得以下代码段,获取该段代码的目的是后续将单元格赋值给图表刻度最大值,以及获得图表的名称。


程序代码:6.4-1b VBA动态图表刻度 录制宏 设定图表主要数值刻度


整理代码

将上述两段录制宏整理为下列代码段,完成主代码。该代码段包括初始化代码段和主作用代码段两部分。


程序代码:6.4-1c VBA动态图表刻度 整理录制宏


关联到工作表事件中

完成上述代码后,要使图表和数据联动,实现无须借助人工参与的自动化,必须使代码和工作表事件建立关联关系。


程序代码:6.4-1d VBA动态图表刻度 与工作表事件关联


图表的自动化[以逸待劳]

虽然Excel图表功能具有非常灵活的高度自定义设置,但也存在诸多问题:

  • 数值刻度最大最小值无法动态引用单元格数值;
  • 无法一次生成相同类型、不同数据源区域的多个图表;
  • 一个个调整数据点标签位置费时费力……

其实,所有这些都可以通过使用VBA编程的自动化来解决。VBA是基于VB(Microsoft Visual Basic)的编程语言,其实这个编程语言学习起来远比其他任意一个编程语言简单,而且Excel提供了非常好的学习工具:录制宏,通过该工具我们可以快速构建属于自己的自动化宏过程。当然要将这个工具掌握到得心应手的地步,我们需要大量的学习和实践,有关该部分更为具体的内容请参阅讲解Excel VBA的书籍。

提示


这个部分的内容要求读者电脑上必须安装了Visual Basic for Applications。

如果没有安装请参考以下微软官方的说明:

http://msdn.microsoft.com/zh-cn/library/0s58cy7h(v=vs.90).aspx


录制宏功能按钮路径在:Excel 2003工具>宏>;Excel 2007/2010“开发工具”选项卡(如图6.4-1所示)>代码>录制宏。Excel 2003也可使用“Visual Basic”工具栏,Excel 2007/2010若没有“开发工具”选项卡,需在Excel 2007文件>Excel选项>常规>使用Excel时采用的首选项中,选中“在功能区中显示“开发工具”选项卡”;在Excel 2010中,文件>选项>自定义功能区中选中“开发工具”。

Excel 2010开发工具选项卡

图6.4-1 Excel 2010开发工具选项卡