【原】复杂的多工作表数据汇总，还得用VBA字典

快速浏览

实用案例

|日期控件||简单的收发存||收费管理系(Access改进版)|

|电子发票管理助手||电子发票登记系统(Access版)|

|文件合并||表格拆分||审计凭证抽查底稿|

|中医诊所收费系统(Excel版)||中医诊所收费系统(Access版)||银行对账单自动勾对|

|印章使用登记系统|

收费使用项目

|财务管理系统||工资薪金和年终奖个税筹划||新税法下工资表模版|

内容提要

• 多工作表数据汇总
• VBA字典应用

大家好，我是冷水泡茶。

今天在论坛上看到一个求助贴：[求助] 想将各工作表的数据汇总到一张表内 

具体需求：

各位大侠

麻烦编一个程序 

如附件“汇总 (希望效果)”sheet那样，将数据汇总到一张表格内 

注意 

1、“编号”列的项目不是每个都固定，希望自动识别到没有数据就停止 

2、有些产品"模号"不止4个，有些只有一个，希望根据工作表中的“模号”自动识别 

3、工作表不止案例中的数量，希望根据实际有几个自动汇总 

感激不尽。

数据表格：

1、明细表：表格的数据关系有点不太好理解，我们后面再说。

2、汇总表：

需求分析：

1、明细数据表分析，

（1）工作表名是以“#1-#2“这样的文本命名，也许这里的数字对应着工作表中H列的“模号”吧，如果所有明细表均以同样的格式命名，那么这个“#”号可以作为识别汇总对象的标志。

（2）表头C2单元格的一组字符串，叫Lot No，不知是什么意思，不管它。我们发现有些表中这个Lot No是相同的，当然也有不同。相同的Lot No中，H列的模号不同，也许这些Lot No相同的表，本就应该在一张表上。

（3）数据区域，“测试结果”，有两个数字，在汇总表中，对应一个Lot No。

2、汇总表分析，

（1）汇总表前面几列，跟明细表左边的结构一样，右边表头是Lot No，顺序排开，每个占两列。

（2）不管是汇总表，还是明细表，都是以“模号”为基础，展开所有对应明细，如果把“模号”移到第一列，表结构就比较清楚了。

基本思路：

1、我们通过嵌套字典，一次性把所有数据装入字典。

2、我们以模号CavNo为key，下设二级字典，以编号DimID为key:

3、我们再设两个三级字典，一以“基础数据”为key，Item为明细表E~G列数字，以“/”分隔连成的字符串，这个基础数据，每个编号对应的数据应该都一样，我们就取一次；二以LotNo为key，也就是明细表的C2单元格的字符串，Item为对应的I~J列的数字，以“/””分隔连成的字符串。

4、我们再循环字典，把数据按照汇总表的格式展开到一个数组temp。这里在处理表头LotNo的时候，有点复杂，我们没有用另外一个字典来生成不重复的LotNo，而是在循环字典的时候，遇到LotNo把它写到表头，这里还是需要用到一个字典dicPos，用来记录LotNo的表头位置。逻辑是这样的：我们以LotNo为Key，以表头位置j为Item，当我们循环到一个新的LotNo时，我们通过dicPos来判断它不存在，则把temp扩展两列，j也增加2，我们把LotNo写入表头j-1的位置，同时把LotNo存入字典，item为j，如果我们循环到一个已经处理过的LotNo，我们把对应的item赋值给j，把数据写入正确的位置。

5、把temp写入汇总表，设置表头LotNo单元格两列合并，设置数据区域划线。这里还有一个烦人的地方，汇总表中，C列与D列，表头是合并居中的，但数据单元格却没有合并居中，但是两个单元格相邻的地方是没有线条的，看上去就象是一个单元格，这里我们采用逐行、逐列设置外框线的办法，跳过第2列、第3列。

VBA代码

代码详见第二条推文。

后记

1、我们看到原表中有一些标色的单元格，可能是结果偏离标准超过公差范围，这些也可以通过VBA代码来设置，但由于没有明确的标准、要求，我们就不管它了。

2、在设置C、D列边框线的时候，我们采取的是逐行、逐列设置外边框，跳过第2、3列的方法，我们再想想，是不是可以先设置所有边框线，再循环第2列，设置右边框线为无，或者循环第3列，设置左边框为无？

3、在确定需要汇总的明细表时，我们是通过其B2单元格是否包含“Lot”字符来判断的。如果我们除了汇总表，就是明细表，我们也可能通过检查工作表名中是否包含“汇总”字样来确定。当然，我们也可以根据前文所提及的，工作表名中包含“#”的为需要汇总的明细表，但我觉得这个标准不是太严谨。

4、后来再去看那个贴子的时候，楼主又说明细表的数据位置有变化，询问如何修改代码（别人给他做的），我也只能呵呵了，这样真的是不太好，不管它了，我们也不改了。当然要改也很容易。

好，今天就到这里，我们下期再会！