VBA小模板，跨表统计的2种写法

目标

• 1 统计一个excel 文件里，多个sheet里的内容
• 2 有的统计需求是，每个表只单表统计，只是进行批量操作
• 3 有的需求是，多个表得某些行列累加等

造出来得文件

 

 2 实现方法1 （可能只适合VBA+EXCEL，不太干净的写法）

2.1 基本思路

• 双层循环
• 一层是循环各个sheet表
• 一层是在某个sheet表内取数据，从头取到尾（需要判断下取第几列，取到哪行为止）

 

Sub t100()
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")
sh1.Columns(6).Clear                    '重置输出区
sh1.Cells(1, 6) = "跨表sum"Dim j As ObjectFor Each j In ThisWorkbook.Worksheetsi = 2j.Cells(2, 4).Clear             '重置输出单元格，避免污染Do While j.Cells(i, 2) <> ""sh1.Cells(i, 6) = j.Cells(i, 2) + sh1.Cells(i, 6)   '跨表对应行累加统计j.Cells(2, 4) = j.Cells(i, 2) + j.Cells(2, 4)       '单表多行累加i = i + 1LoopNext
End Sub

2.2 用EXCEL表的单元格，当变量来存储数据

（其实我反思，EXCEL只应用来显示可能更好）

• 如果是累加一个sheet的数据，存在一个变量/1个单元格就行
• 如果是累加多个sheet的数据，需要存在多个excel单元格/ 1行/1列等

2.3 重点1

• 方法1直接把 j当作了worksheets对象

    Dim j As Object
    For Each j In ThisWorkbook.Worksheets

2.4 方法1，需要重置输出区

• 为什么呢？
• 因为EXCEL不是变量，数组，是文件，是可以保存数据的
• 不像 程序里的变量，数组，程序开始运行，创建--生---------程序运行结束，销毁--灭，
• 所以有可能上次运行的数据（ 可以叫脏数据吧）还存着，会和新运行的结果累积起来
• 所以就需要 先把EXCEL的输出区域重置才行

2.5  方法1得历史改进过程，没什么用，有兴趣得看看

Sub t1()
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")
Dim j As ObjectFor Each j In ThisWorkbook.WorksheetsFor i = 2 To 99   '写死99这种这个很不好，需要线判断最大行数sh1.Cells(i, 6) = j.Cells(i, 2) + sh1.Cells(i, 6)NextNext
End SubSub t11()
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")
Dim j As ObjectFor Each j In ThisWorkbook.Worksheetsi = 2Do While j.Cells(i, 2) <> ""sh1.Cells(i, 6) = j.Cells(i, 2) + sh1.Cells(i, 6)i = i + 1LoopNext
End SubSub t12()
Dim j As ObjectFor Each j In ThisWorkbook.Worksheetsi = 2Do While j.Cells(i, 2) <> ""j.Cells(2, 4) = j.Cells(i, 2) + j.Cells(2, 4)i = i + 1LoopNext
End Sub

3  方法2： 运算和存储都在程序的变量里进行，EXCEL只存储和显示最终结果

3.1 代码写法思路和方法1完全不同

• 运算和存储都在程序的变量里进行，EXCEL只存储和显示最终结果
• VBA 和像方法1那么干，还是因为是内置在EXCEL里的吧
• 一般程序还是都把过程放在程序内解决，
• EXCEL表只是存储最终结果  & 显示出来

Sub t200()'核心差异
'方法2，把j定义为 sheet的序号，而b作为worksheets对象，Set b = Worksheets(j)'
'对应方法1，直接把j当作了worksheets对象'方法1，需要重置输出区
'方法2，因为都是用变量中转的，单数据存1个变量里，多数据存在数组，因为变量做了重置，所以输出区域就不做重置了Dim i, j, h
Dim b As Object
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")
Dim arr1()For j = 1 To ThisWorkbook.Worksheets.Counth = 0  '每个表分表统计i = 2  '每个表都从第2行开始，重置i行数'数组不需要重置？因为这个数组不需要循环，就是要一次性累加'每次运行变量和数组都是消灭后重新生产的，不会像excel这种外部文件记录了数据' 重置变量和数组是为了程序连续运行期间问题，就是为了，循环，下次循环冲头再来Set b = Worksheets(j)Do While b.Cells(i, 2) <> ""ReDim Preserve arr1(2 To i)           '因为i在不同的表，无法确认具体数值，有数据的行数都不同arr1(i) = b.Cells(i, 2) + arr1(i)       '跨表对应行累加统计,因为是多个数据，需要用数组h = h + b.Cells(i, 2)                  '单表多行累加i = i + 1Loopb.Cells(2, 4) = h                       'h本身做了重置，因此输出单元格Cells(2, 4) 不需要再重置NextFor i = LBound(arr1) To UBound(arr1)sh1.Cells(i, 6) = arr1(i)NextEnd Sub

3.2 定义sheet 不同

• 方法2，把j定义为 sheet的序号，而b作为worksheets对象，Set b = Worksheets(j)'
• 对应方法1，直接把j当作了worksheets对象

3.3 不需要重置EXCEL的  存储+显示区

• 方法1，需要重置输出区
• 因为方法1，把那些区域又做显示，又做存储就有了需要重置清除的问题
• '方法2，因为都是用变量中转的，单数据存1个变量里，多数据存在数组，因为变量做了重置，所以输出区域就不做重置了

可以看到代码里

EXCEL输出区域，只是从 代码里取变量 或数组内容进行显示，和EXCEL本身区域的内容没关系，输出后会直接覆盖老数据

（不过也有可能有问题，就是老数据的行数比新的多，导致这样还是有脏数据，嘿嘿）

b.Cells(2, 4) = h   

    For i = LBound(arr1) To UBound(arr1)
        sh1.Cells(i, 6) = arr1(i)
    Next

3.4 代码内部的重置， 这个主要和循环有关系

• 数组不需要重置？因为这个数组不需要循环，就是要一次性累加
•  每次运行变量和数组都是消灭后重新生产的，不会像excel这种外部文件记录了数据
•  重置变量和数组是为了程序连续运行期间问题，就是为了，循环，下次循环重头再来

3.5 方法2的历史代码，没啥用

Sub t2()
Dim i, j, h
Dim b As ObjectFor j = 1 To ThisWorkbook.Worksheets.Counth = 0  '每个表分表统计i = 2  '每个表都从第2行开始，重置i行数Set b = Worksheets(j)Do While b.Cells(i, 2) <> ""h = h + b.Cells(i, 2)i = i + 1Loopb.Cells(2, 4) = hNextEnd SubSub t21()
Dim i, j
Dim b As Object
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")For j = 1 To ThisWorkbook.Worksheets.Counti = 2  '每个表都从第2行开始，重置i行数Set b = Worksheets(j)Do While b.Cells(i, 2) <> ""sh1.Cells(i, 6) = b.Cells(i, 2) + sh1.Cells(i, 6)i = i + 1LoopNextEnd Sub