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

奔跑的犀牛先生

已于 2023-03-22 17:40:26 修改

阅读量1.7k

点赞数

分类专栏： VBA 文章标签： VBA

于 2023-03-09 15:06:02 首次发布

本文链接：https://blog.csdn.net/xuemanqianshan/article/details/129421691

版权

VBA 专栏收录该内容

184 篇文章

订阅专栏

问题和目标

问题：是想统计一个excel 文件里，多个sheet里的内容
但是整个目标可以细化为不同的分支需求

有的统计需求是，每个表只单表统计，只是进行批量操作。比如例子里累加到每个sheet的指定位置：单元格D2
有的需求是，多个表得某些行列累加等。比如例子里累加到，特定表“sheet1”的第6列

造出来得文件

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 Object
    For Each j In ThisWorkbook.Worksheets
        i = 2
        j.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 + 1
        Loop
    Next
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个原因

第1：其实无论哪个方法，因为EXCEL作为一个数据文件，本身输出区可能（只是可能，不是必然）是已经被其他数据污染过的，所以进行整列/或整个区域的输出区清理是很有必要的。

（比如你这次输出200行数据，下次只输出150行数据，那么上次的151-200行数据就可能污染第2次的结果，看起来像是第2次输出的）

第2，第1种方法的关键问题在于这：累加统计时，是以当前EXCEL的单元格的现有值为基础的，所以不清除老的数据，必然出错。

而第2种方法，因为累计统计 a=a+ add的时候，是用的变量a ，变量每次即生即灭，是不会存在,EXCEL这种，事先就存在数据的问题的

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

Sub t1()
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")
Dim j As Object
    For Each j In ThisWorkbook.Worksheets
        For i = 2 To 99   '写死99这种这个很不好，需要线判断最大行数
            sh1.Cells(i, 6) = j.Cells(i, 2) + sh1.Cells(i, 6)
        Next
    Next
End Sub


Sub t11()
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")
Dim j As Object
    For Each j In ThisWorkbook.Worksheets
        i = 2
        Do While j.Cells(i, 2) <> ""
            sh1.Cells(i, 6) = j.Cells(i, 2) + sh1.Cells(i, 6)
            i = i + 1
        Loop
    Next
End Sub


Sub t12()
Dim j As Object
    For Each j In ThisWorkbook.Worksheets
        i = 2
        Do While j.Cells(i, 2) <> ""
            j.Cells(2, 4) = j.Cells(i, 2) + j.Cells(2, 4)
            i = i + 1
        Loop
    Next
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.Count
    h = 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 + 1
        Loop
        b.Cells(2, 4) = h                       'h本身做了重置，因此输出单元格Cells(2, 4) 不需要再重置
    Next

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


End 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 Object
    For j = 1 To ThisWorkbook.Worksheets.Count
    h = 0  '每个表分表统计
    i = 2  '每个表都从第2行开始，重置i行数
        Set b = Worksheets(j)
        Do While b.Cells(i, 2) <> ""
           h = h + b.Cells(i, 2)
           i = i + 1
        Loop
        b.Cells(2, 4) = h
    Next

End Sub



Sub t21()
Dim i, j
Dim b As Object
Dim sh1 As Object
Set sh1 = ThisWorkbook.Worksheets("sheet1")

    For j = 1 To ThisWorkbook.Worksheets.Count
        i = 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 + 1
        Loop
    Next

End Sub