Board logo

標題: [發問] 關於SUMPRODUCT函數... [打印本頁]
作者: andy8426    時間: 2013-4-23 06:17     標題: 關於SUMPRODUCT函數...

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

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

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

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

附上檔案請求各位前輩指點. (檔案只有部分格式)
作者: Bodhidharma    時間: 2013-4-23 19:23

回復 1# andy8426

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

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

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

回復 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    時間: 2013-4-23 21:13

回復 3# andy8426

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

(話說我還沒有空學VBA，以上純粹就程式設計的概念發言)
作者: Hsieh    時間: 2013-4-23 23:05

回復 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    時間: 2013-4-24 02:54

回復 5# Hsieh

非常感謝版主指導~   

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

a.Offset(, -3).MergeArea(1)
這段看不懂...
作者: kimbal    時間: 2013-4-24 13:58

本帖最後由 Hsieh 於 2013-5-9 18:48 編輯

回復 6# andy8426

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

找出 a(F欄) 格子前3欄 (即C欄)下合併格子的資料
例如在格子F3時 , 就會找出 C2 的資料 (B1-1)
作者: andy8426    時間: 2013-4-25 02:49

回復 7# kimbal


  感謝版主說明~~
經過這一課學到很多.
作者: freeffly    時間: 2013-5-9 17:00

回復 5# Hsieh


    可以處理合併儲存格的統計
   學習了
   嘗試解讀不過還不知道統計的方式
   看來我對於字典還需要多努力
作者: ML089    時間: 2013-5-11 12:41

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

回復 3# andy8426

母親節太忙了，前面建議2沒有時間寫，先寫一個輔助欄改善方法
[attach]14951[/attach]

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
作者: andy8426    時間: 2013-5-14 01:26

回復 11# ML089

感謝指導~  

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

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



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)