問(wèn)題和目標(biāo)

• 問(wèn)題： 是想統(tǒng)計(jì)一個(gè)excel 文件里，多個(gè)sheet里的內(nèi)容
• 但是整個(gè)目標(biāo)可以細(xì)化為不同的分支需求

有的統(tǒng)計(jì)需求是，每個(gè)表只單表統(tǒng)計(jì)，只是進(jìn)行批量操作。比如例子里累加到每個(gè)sheet的指定位置：單元格D2

有的需求是，多個(gè)表得某些行列累加等。比如例子里累加到，特定表“sheet1”的第6列

造出來(lái)得文件

?

?2 實(shí)現(xiàn)方法1 （可能只適合VBA+EXCEL，不太干凈的寫法）

2.1 基本思路

• 雙層循環(huán)
• 一層是循環(huán)各個(gè)sheet表
• 一層是在某個(gè)sheet表內(nèi)取數(shù)據(jù)，從頭取到尾（需要判斷下取第幾列，取到哪行為止）

Sub t100() Dim sh1 As Object Set sh1 = ThisWorkbook.Worksheets("sheet1") sh1.Columns(6).Clear '重置輸出區(qū) 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) '跨表對(duì)應(yīng)行累加統(tǒng)計(jì)j.Cells(2, 4) = j.Cells(i, 2) + j.Cells(2, 4) '單表多行累加i = i + 1LoopNext End Sub

2.2 用EXCEL表的單元格，當(dāng)變量來(lái)存儲(chǔ)數(shù)據(jù)

（其實(shí)我反思，EXCEL只應(yīng)用來(lái)顯示可能更好）

• 如果是累加一個(gè)sheet的數(shù)據(jù)，存在一個(gè)變量/1個(gè)單元格就行
• 如果是累加多個(gè)sheet的數(shù)據(jù)，需要存在多個(gè)excel單元格/ 1行/1列等

2.3 重點(diǎn)1

• 方法1直接把 j當(dāng)作了worksheets對(duì)象

? ? Dim j As Object
? ? For Each j In ThisWorkbook.Worksheets

2.4?方法1，需要重置輸出區(qū)

• 為什么呢？
• 因?yàn)镋XCEL不是變量，數(shù)組，是文件，是可以保存數(shù)據(jù)的
• 不像 程序里的變量，數(shù)組，程序開始運(yùn)行，創(chuàng)建--生---------程序運(yùn)行結(jié)束，銷毀--滅，
• 所以有可能上次運(yùn)行的數(shù)據(jù)（ 可以叫臟數(shù)據(jù)吧）還存著，會(huì)和新運(yùn)行的結(jié)果累積起來(lái)
• 所以就需要 先把EXCEL的輸出區(qū)域重置才行

總結(jié)：需要重置輸出區(qū)的2個(gè)原因

• 第1：其實(shí)無(wú)論哪個(gè)方法，因?yàn)镋XCEL作為一個(gè)數(shù)據(jù)文件，本身輸出區(qū)可能（只是可能，不是必然）是已經(jīng)被其他數(shù)據(jù)污染過(guò)的，所以進(jìn)行整列/或整個(gè)區(qū)域的 輸出區(qū)清理是很有必要的。

（比如你這次輸出200行數(shù)據(jù)，下次只輸出150行數(shù)據(jù)，那么上次的151-200行數(shù)據(jù)就可能污染第2次的結(jié)果，看起來(lái)像是第2次輸出的）

• 第2，第1種方法的關(guān)鍵問(wèn)題在于這：累加統(tǒng)計(jì)時(shí)，是以當(dāng)前EXCEL的單元格的現(xiàn)有值為基礎(chǔ)的，所以不清除老的數(shù)據(jù)，必然出錯(cuò)。

• 而第2種方法，因?yàn)槔塾?jì)統(tǒng)計(jì)? a=a+ add的時(shí)候，是用的變量a ，變量每次即生即滅，是不會(huì)存在,EXCEL這種，事先就存在數(shù)據(jù)的問(wèn)題的

2.5? 方法1得歷史改進(jìn)過(guò)程，沒(méi)什么用，有興趣得看看

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這種這個(gè)很不好，需要線判斷最大行數(shù)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： 運(yùn)算和存儲(chǔ)都在程序的變量里進(jìn)行，EXCEL只存儲(chǔ)和顯示最終結(jié)果

（不過(guò)也有可能有問(wèn)題，就是老數(shù)據(jù)的行數(shù)比新的多，導(dǎo)致這樣還是有臟數(shù)據(jù)，嘿嘿）

3.1 代碼寫法思路和方法1完全不同

• 運(yùn)算和存儲(chǔ)都在程序的變量里進(jìn)行，EXCEL只存儲(chǔ)和顯示最終結(jié)果
• VBA 和像方法1那么干，還是因?yàn)槭莾?nèi)置在EXCEL里的吧
• 一般程序還是都把過(guò)程放在程序內(nèi)解決，
• EXCEL表只是存儲(chǔ)最終結(jié)果? & 顯示出來(lái)

Sub t200()'核心差異 '方法2，把j定義為 sheet的序號(hào)，而b作為worksheets對(duì)象，Set b = Worksheets(j)' '對(duì)應(yīng)方法1，直接把j當(dāng)作了worksheets對(duì)象'方法1，需要重置輸出區(qū) '方法2，因?yàn)槎际怯米兞恐修D(zhuǎn)的，單數(shù)據(jù)存1個(gè)變量里，多數(shù)據(jù)存在數(shù)組，因?yàn)樽兞孔隽酥刂?#xff0c;所以輸出區(qū)域就不做重置了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 '每個(gè)表分表統(tǒng)計(jì)i = 2 '每個(gè)表都從第2行開始，重置i行數(shù)'數(shù)組不需要重置？因?yàn)檫@個(gè)數(shù)組不需要循環(huán)，就是要一次性累加'每次運(yùn)行變量和數(shù)組都是消滅后重新生產(chǎn)的，不會(huì)像excel這種外部文件記錄了數(shù)據(jù)' 重置變量和數(shù)組是為了程序連續(xù)運(yùn)行期間問(wèn)題，就是為了，循環(huán)，下次循環(huán)沖頭再來(lái)Set b = Worksheets(j)Do While b.Cells(i, 2) <> ""ReDim Preserve arr1(2 To i) '因?yàn)閕在不同的表，無(wú)法確認(rèn)具體數(shù)值，有數(shù)據(jù)的行數(shù)都不同arr1(i) = b.Cells(i, 2) + arr1(i) '跨表對(duì)應(yīng)行累加統(tǒng)計(jì),因?yàn)槭嵌鄠€(gè)數(shù)據(jù)，需要用數(shù)組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的序號(hào)，而b作為worksheets對(duì)象，Set b = Worksheets(j)'
• 對(duì)應(yīng)方法1，直接把j當(dāng)作了worksheets對(duì)象

3.3 不需要重置EXCEL的? 存儲(chǔ)+顯示區(qū)

• 方法1，需要重置輸出區(qū)
• 因?yàn)榉椒?，把那些區(qū)域又做顯示，又做存儲(chǔ)就有了需要重置清除的問(wèn)題
• '方法2，因?yàn)槎际怯米兞恐修D(zhuǎn)的，單數(shù)據(jù)存1個(gè)變量里，多數(shù)據(jù)存在數(shù)組，因?yàn)樽兞孔隽酥刂?#xff0c;所以輸出區(qū)域就不做重置了

可以看到代碼里

EXCEL輸出區(qū)域，只是從 代碼里取變量 或數(shù)組內(nèi)容進(jìn)行顯示，和EXCEL本身區(qū)域的內(nèi)容沒(méi)關(guān)系，輸出后會(huì)直接覆蓋老數(shù)據(jù)

（不過(guò)也有可能有問(wèn)題，就是老數(shù)據(jù)的行數(shù)比新的多，導(dǎo)致這樣還是有臟數(shù)據(jù)，嘿嘿）

b.Cells(2, 4) = h ??

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

3.4 代碼內(nèi)部的重置， 這個(gè)主要和循環(huán)有關(guān)系

• 數(shù)組不需要重置？因?yàn)檫@個(gè)數(shù)組不需要循環(huán)，就是要一次性累加
• ?每次運(yùn)行變量和數(shù)組都是消滅后重新生產(chǎn)的，不會(huì)像excel這種外部文件記錄了數(shù)據(jù)
• ?重置變量和數(shù)組是為了程序連續(xù)運(yùn)行期間問(wèn)題，就是為了，循環(huán)，下次循環(huán)重頭再來(lái)

3.5 方法2的歷史代碼，沒(méi)啥用

Sub t2() Dim i, j, h Dim b As ObjectFor j = 1 To ThisWorkbook.Worksheets.Counth = 0 '每個(gè)表分表統(tǒng)計(jì)i = 2 '每個(gè)表都從第2行開始，重置i行數(shù)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 '每個(gè)表都從第2行開始，重置i行數(shù)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

總結(jié)

以上是生活随笔為你收集整理的VBA小模板，跨表统计的2种写法的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。