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 = " & d & ") * (日期 >= 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 '這一行會僅留下結果，去除公式