返回列表 上一主題 發帖

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

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
1# 跳轉到 »
發表於 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欄應該排序，才能定位出查詢小範圍(動態查詢範圍)，讓計算比對工作量縮小
這部分比較複雜先提示一下，等晚上回來有空再說
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
2#
發表於 2013-5-11 17:10 | 顯示全部帖子
回復 3# andy8426

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

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
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 犯錯出懺悔心，才能清淨無煩惱。
返回列表 上一主題