iT邦幫忙

0

excel統計公式運算過久問題

https://ithelp.ithome.com.tw/upload/images/20181014/20109231V4yZTcXcF0.png

請問是否有辦法解決統計公式造成運算過久的問題,例如修改公式或使用巨集加速運算
因為只要篩選條件,一等就要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真的相當耗資源。
@erwinho 請問公式該怎麼下呢?因為有3欄+2列資料需同時比對後再加總數據,所以不知該如何修改公式~~~
we684123 iT邦新手 5 級 ‧ 2018-10-15 19:28:13 檢舉
寫 gs 或 vab 吧,你這是每個儲存格都計算超大量很恐怖啊...
用上述2個都比直接用excel公式硬上好很多,最好還是DB就直接處理了

既然其他單位只是參考沒有彙整,那你就直接把"統計"內算過OK的值複製到另一個EXCEL(沒公式的),再給其他單位就好
每個月匯入新資料時先在其他分頁輸入好,在一次複製進去然後等結果吧
erwinho iT邦新手 5 級 ‧ 2018-10-15 23:17:40 檢舉
看起來是有兩個表, 還是需要範例檔才能正確回答。
@erwinho 測試檔裡面第一個活頁是公式,第二個活頁是系統撈出來每月累積的資料來源,所需資料已經放在同一個檔案裡面了
0
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)

1
wilson1966
iT邦研究生 5 級 ‧ 2018-10-15 08:43:16

建議改用資料庫設計會比較快,即資料存在DB裡,再用SQL 去抓資料,速度會快一點。

@wilson1966 因為資料需由系統彙整到excel後,提供此檔案供其他單位使用,但外單位無法使用我們公司的系統,所以~~~~~~可能沒辦法使用到系統

0
蟹老闆
iT邦大師 1 級 ‧ 2018-10-17 11:17:55

試試看有沒有好一點

我要發表回答

立即登入回答