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

andy8426

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

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

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

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

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

andy8426

回復 11# ML089

感謝指導~  

正常Sheet4資料通常都是大於10萬筆...  所以才會使用B:B   C:C
而Sheet3會有3808個計算.
使用SUMPRODUCT會跑很久.

目前使用板大教學的VBA修改後,已完成報表.
M兄提供的附件,目前小弟權限不足無法下載,4個簡單的公式我會在嘗試.^^

ML089

回復 3# andy8426

母親節太忙了，前面建議2沒有時間寫，先寫一個輔助欄改善方法
test1 SUMPRODUCT太慢.rar (17.77 KB)

下載次數: 4

2013-5-11 17:07

4個簡單公式，但比原公式快多了，雖然COUNTIF函數很慢，我不太喜歡使用他，但大家都很熟還是用吧

Sheet3 L1 =COUNTA(Sheet4!B:B)
Sheet3 J1 =IF(C2="",J1,C2)
Sheet3 H2 =COUNTIF(OFFSET(Sheet4!E$1,,,L$1),J2&"__"&F2)
Sheet4 E1 =B1&"__"&C1

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欄應該排序，才能定位出查詢小範圍(動態查詢範圍)，讓計算比對工作量縮小
這部分比較複雜先提示一下，等晚上回來有空再說

freeffly

回復 5# Hsieh


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

andy8426

回復 7# kimbal


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

kimbal

回復 6# andy8426

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

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

andy8426

回復 5# Hsieh

非常感謝版主指導~   

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

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

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

複製代碼

Bodhidharma

回復 3# andy8426

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

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