2020年12月7日 星期一

VBA:SUMPRODUCT 的美與好

 不知道大家有無處理大量資料的痛,尤其是使用SUMPRODUCT的時候,當有很多儲存格都使用時,開始維護新資料就會有明顯卡頓,小編簡單舉例自己同事,維護的一份簡單進銷存資料從剛開始的1000筆成長到目前的8萬多筆,然後有80個SUMPRODUCT,等於每打一筆資料EXCEL要重新計算64萬次(我可愛的同事都等到要砸鍋了),當然,這部分也是可以透過關閉EXCEL內部的自動計算功能,來避免這個問題發生,但關了經常忘了在重新打開~~這又是另外一個問題了。

先來簡單幫SUMPRODUCT做介紹,使用前若是可以,可以針對資料所在行別,透過"名稱管理員"或是"定義名稱"做定義,這樣就省去每次資料有異動拉儲存格的動作。

接著開始說明SUMPRODUCT;

主要語法結構大致上是這樣:

SUMPRODUCT(資料1=判斷1,資料2=判斷2,資料N=判斷N),

多條件時,透過*號將條件串在一起(資料1=判斷1)*(資料2=判斷2);白話一點意思就是當資料1滿足判斷1並且(AND的意思)資料2也等於判斷2時,才算成立,等等歐這樣還不夠!!((資料1=判斷1)*(資料2=判斷2),資料1),小編在這補上資料1表示當條件成立時加總資料1的資料;延伸一下,((資料1=判斷1)*1*(資料2=判斷2))這樣勒!!差在那,會變成計算資料1的個數歐,大概簡單說到這裡。

為了改進SUMPRODUCT的運算缺點,小編配合VBA語法,以儲存格物件的Formula屬性結合SUMPRODUCT語法做一點小改善,所以小編做了一個按鈕,把SUMPRODUCT用公式方式寫入Formula屬性中。

範例:

圖1.原始資料

圖2.SUMPRODUCT彙整結果頁面

VBA CODE:
簡單說明:雙引號間的資料透過&符號串在一起,然後透過等號寫入Formula ;紅色字體的d為變數,透過d = "C" & I,I為迴圈的步進值,用賴配合"C",做變數的設定,例如"C" & 1則變數d為"C1",以此類推"C" & 10,則為"C10"。在變數d=C10情況下,"= SUMPRODUCT((Item = " & & ") * (日期 >= A2) * (日期 <= B2),入庫)" 則在Formula 會成為SUMPRODUCT((Item = C10) * (日期 >= A2) * (日期 <= B2),入庫)這樣!

Sheet1.Range("D" & I).Formula = "= SUMPRODUCT((Item = " & d & ") * (日期 >= A2) * (日期 <= B2),入庫)" 

步驟:
1.先定義"名稱"
2.寫一個函數版的SUMPRODUCT,測試OK後,在參考小編的CODE,做一個按鈕,貼上CODE後,依樣畫葫蘆參考自己寫的SUMPRODUCT函數做修改。
   依樣畫葫蘆(1):
CODE中的d = "C" & I;這邊是你要拿ITEM跟誰比較的對象設定,比較對象"C"行組合數字來匹配儲存格,所以每一列比對的對象,都是不同的ITEM號碼歐。小編的I是從數字2開始編歐( I = 2),然後透過While迴圈檢查C行有無資料歐,所以有改到"C"則有2處要修改歐(分別是: d = "C" & I、    While Sheet1.Range("C" & I) <> "") 
圖3.SUMPRODUCT彙整比對對象(C行;紅框)

   依樣畫葫蘆(2):
參考小編的CODE,修改 * (AND)條件,或是>、<、=、<=、>=等邏輯與判斷條件。
   依樣畫葫蘆(3):
參考小編的CODE,把名稱改掉;例如ITEM、日期、入庫。
    依樣畫葫蘆(4):
Sheet1.Range("D" & I).Formula :"D" 用來控制寫在哪一行,這邊有D、E、F行,可以單獨一個或多個自行視需要做修改即可,但這邊的修改請一併修改 Sheet1.Range("D2:F" & I).Copy顏色字體標示的部分,例如,單行"D",則改為"D2:D",多行C、D、E則"C2:E",以此類推。
    依樣畫葫蘆(5):
Sheet1.Range("D2").PasteSpecial xlPasteValuesAndNumberFormats;顏色字體標示的部分,類似前步驟,但不管單一或多儲存格,僅要寫第一個位置的儲存格即可。

解說:有人可能會想,不就是自動寫入SUMPRODUCT函數,還不是一樣每次輸入資料會卡頓!!! 小編最後程式碼倒數第二行,這行功能為貼上資料,所以寫的函數公式會被自動蓋掉,自然沒卡頓問題。

Sheet1.Range("D2").PasteSpecial xlPasteValuesAndNumberFormats '這一行會僅留下結果,去除公式







沒有留言:

張貼留言

指數變化(2024.06.07)

 指數變化(2024.06.07) 上周焦點:      5/9當周焦點                          事實:fed與非農就業,引起投資市場對降息有加速,但肯定的是,fed說不加息了。                        美國密西根大學信心指數 再次...