[發問] 關於SUMPRODUCT函數...

andy8426

各位前輩...
我有份資料需要做條件判斷,sheet4內每次的資料都是萬筆起跳

但現在遇到的問題,SUMPRODUCT的公式套用在將近4000個儲存格,
所以每當資料有所變動,就會重新讀取一次時間非常的久...

是否有方法能使以上的判斷只做一次讀取??

或是將 =SUMPRODUCT((Sheet4!$B:$B=$C$2)*(Sheet4!$C:$C=F2))轉變為VBA??

附上檔案請求各位前輩指點. (檔案只有部分格式)

Bodhidharma

回復 1# andy8426

公式→重算選項→手動，可以等資料輸入完成後再行計算

至於公式效率方面，sumproduct函數應該已經滿有效率的
可能要從你資料的呈現方式下手，從你的檔案中可以看出一些規律
但是因為不完整，所以也無法幫忙

或許你可以說明一下你整個資料的邏輯，以及想要達到什麼樣的效果 (如：是否一定要套在4000個儲存格？這4000個儲存格是如何安排？有何規律？)
這樣才比較有辦法給你意見

andy8426

回復 2# Bodhidharma

感謝提醒~

目前公式調成手動,操作上方便許多.

資料是運算在G欄中16個儲存格~
規律由上而下  都是相同的儲存格~
所以公式會套用在3808個儲存格(238個項目*固定的16個條件)

SUMPRODUCT((Sheet4!$B:$B=$C$2)*(Sheet4!$C:$C=F2))

Sheet4內是要統計的資料  
C2  與 F2  是 條件  

有試著錄制VBA與參考另篇「用VBA來執行SUMPRODUCT多條件統計」
但因為權限不足關系無法取得檔案參考.

目前除了先將公式調成手動外,也正在想如何優化成VBA
解決完運算的問題才有辦法將其餘前置動作整合在裡面....
還請前輩指教.

Bodhidharma

回復 3# andy8426

如果一定要將資料結果運算到那238*16個cell中，那大概真的要用VBA才會比較有效率了
畢竟直接用sumproduct的話，每個儲存格都要算一次，所有資料都得算個3808次
但是用VBA的話，可以直接將所有的資料掃過一遍，然後丟到[238][16]的陣列之中即可
你可以把你寫的程式PO在程式區，再請大家給意見

(話說我還沒有空學VBA，以上純粹就程式設計的概念發言)

Hsieh

回復 3# andy8426

1. Sub ex()
   
2. Dim a As Range, s&
   
3. Set dic = CreateObject("Scripting.Dictionary")
   
4. With Sheets("Sheet4")
   
5.   For Each a In .Range(.[B1], .[B1].End(xlDown))
   
6.      dic(a & a.Offset(, 1)) = dic(a & a.Offset(, 1)) + 1
   
7.   Next
   
8. End With
   
9. With Sheets("Sheet3")
   
10.   For Each a In .Range(.[F2], .[F2].End(xlDown))
    
11.   s = dic(a.Offset(, -3).MergeArea(1) & a)
    
12.      a.Offset(, 1) = s
    
13.   Next
    
14. End With
    
15. End Sub

複製代碼

andy8426

回復 5# Hsieh

非常感謝版主指導~   

另外想問一下...
Dim a As Range, s&
那個s&代表是甚麼?

a.Offset(, -3).MergeArea(1)
這段看不懂...

kimbal

回復 6# andy8426

> 那個s&代表是甚麼?
相等於 dim s as Long
>a.Offset(, -3).MergeArea(1)

找出 a(F欄) 格子前3欄 (即C欄)下合併格子的資料
例如在格子F3時 , 就會找出 C2 的資料 (B1-1)

andy8426

回復 7# kimbal


  感謝版主說明~~
經過這一課學到很多.

freeffly

回復 5# Hsieh


    可以處理合併儲存格的統計
   學習了
   嘗試解讀不過還不知道統計的方式
   看來我對於字典還需要多努力

ML089

回復 1# andy8426

4000多筆使用公式應該還不會太慢

建議1，不要用擴大範圍
=SUMPRODUCT((Sheet4!$B:$B=$C$2)*(Sheet4!$C:$C=F2))
B:B C:C 這種擴大範圍在2007版以上為1048576列，增加太多無效格的計算很浪費時間
你的示範例才72筆查詢資料，計算公式才65筆，只要按F9就感覺他跑得氣呼呼
將公式改為
=SUMPRODUCT((Sheet4!$B1:$B72=$C$2)*(Sheet4!$C1:$C72=F2))
馬上就改善很多

建議2，資料庫更大時，要排序整理讓查詢加速
如Sheet4 B欄應該排序，才能定位出查詢小範圍(動態查詢範圍)，讓計算比對工作量縮小
這部分比較複雜先提示一下，等晚上回來有空再說