excel 如何建立自己的自动化图表模板

Excel技巧 2023-01-08

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

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

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

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

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

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

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

需求分析

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

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

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

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

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

使用VBA来演练

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

数据排序

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

程序代码　片段：13.1-1a　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图表更新－重置函数公式

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

图表设置及数据引用更新

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

程序代码　片段：13.1-1c　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数据区进行测试。