首页 > Excel专区 > Excel技巧 >

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

Excel技巧 2021-11-25 12:05:17

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图表更新-排序

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


排序的部分完全来自录制宏,然后修改排序区域获得,并不复杂,需注意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数据区进行测试。


标签: excel自动化图表模板

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.