excel統計公式運算過久問題

excel 公式運算

msmplayv121068 2018-10-14 00:36:32 ‧ 6247 瀏覽

分享至

請問是否有辦法解決統計公式造成運算過久的問題，例如修改公式或使用巨集加速運算
因為只要篩選條件，一等就要5-10分鐘才會重新統計數據完成，實在是太久了
再來是數據來源的資料未來會每月向下新增，資料越多會等更久吧!!求解~~~~~~~~~~

說明：
【統計】活頁上方可篩選條件，以篩選所需年月及統計數據，另使用以下公式統計【數據來源】活頁數據
=SUMPRODUCT(數據來源!$D$6:$OC$1299*(數據來源!$OD$6:$OD$1299>=$D$1)(數據來源!$OD$6:$OD$1299<=$F$1)(數據來源!$C$6:$C$1299=E$4)(數據來源!$D$4:$OC$4=$D5)(數據來源!$D$5:$OC$5=$H$1))

【數據來源】活頁每月資料會向下堆疊，所以資料範圍會越來越大，例如未來可能到2000或5000

測試檔http://www.FunP.Net/1435

看更多先前的討論...收起先前的討論...

小魚 iT邦大師 1 級 ‧ 2018-10-14 09:22:21 檢舉

運算本來就是需要速度,
要優化這種可能要花錢請人家做吧,
能不能再優化就不確定了,
這要問專門做Excel的,
買台超級電腦應該運算速度會快很多...

erwinho iT邦新手 5 級 ‧ 2018-10-15 07:28:13 檢舉

多條件篩選如果是excel2007以後版本，改用sumifs函數吧！sumproduct真的相當耗資源。

msmplayv121068 iT邦新手 4 級 ‧ 2018-10-15 11:44:12 檢舉

@erwinho 請問公式該怎麼下呢？因為有3欄+2列資料需同時比對後再加總數據，所以不知該如何修改公式～～～

we684123 iT邦研究生 4 級 ‧ 2018-10-15 19:28:13 檢舉

寫 gs 或 vab 吧，你這是每個儲存格都計算超大量很恐怖啊...
用上述2個都比直接用excel公式硬上好很多，最好還是DB就直接處理了

既然其他單位只是參考沒有彙整，那你就直接把"統計"內算過OK的值複製到另一個EXCEL(沒公式的)，再給其他單位就好
每個月匯入新資料時先在其他分頁輸入好，在一次複製進去然後等結果吧

erwinho iT邦新手 5 級 ‧ 2018-10-15 23:17:40 檢舉

看起來是有兩個表, 還是需要範例檔才能正確回答。

msmplayv121068 iT邦新手 4 級 ‧ 2018-10-15 23:27:49 檢舉

@erwinho 測試檔裡面第一個活頁是公式，第二個活頁是系統撈出來每月累積的資料來源，所需資料已經放在同一個檔案裡面了

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

erwinho

iT邦新手 5 級 ‧ 2018-10-17 15:11:58

最佳解答

Sumproduct雖然好用但就是太耗運算, 最後用了sumifs + index + match來完成, 效率快了百倍千倍以上, 主要真的是sumproduct真的太慢了。
最後在E5公式如下, 完成後複製到所有儲存格。
=SUMIFS(INDEX(數據來源!$D$6:$OC$1299,,MATCH($D5,數據來源!$D$4:$OC$4,0)+IF($H$1="數量",0,1)),數據來源!$C$6:$C$1299,E$4,數據來源!$OD$6:$OD$1299,">="&$D$1,數據來源!$OD$6:$OD$1299,"<="&$F$1)