iT邦幫忙

1

急求救 - IF 公式求優化

請問各位大能
下圖之公式是否有優化的可能?
之前沒什麼問題(7萬多筆),稍慢一些但約十分鐘就OK了,
但現9萬多筆資料卻直接半小時還不動,
我把所有的程式都關了,祇留EXCEL作業,結果還是相同,
已弄了3天,也問過電腦維修公司,電腦設備沒問題,
他們認為是 公式的問題.
因而請求各位幫忙,謝謝!

https://ithelp.ithome.com.tw/upload/images/20200715/20110195txMJrC4YT0.jpg

===============================================
109.07.16
目前已請電腦維護公司用其高效電腦幫我處理好資料了,謝謝各位!
但還是希望有大神能幫我改進公式,再次謝謝大家!

看更多先前的討論...收起先前的討論...
甲土豆 iT邦新手 5 級 ‧ 2020-07-15 16:36:27 檢舉
excel 處裡大數據,我只能說你很有勇氣
比較建議是使用 DB 加上 程式語言 去運算
最後運算結果,在寫到 excel 上
不然其實你每次開啟這張excel 都需要花一段時間><
fannyliu iT邦新手 5 級 ‧ 2020-07-15 16:41:44 檢舉
我不太懂公式,這是依前人所留作業,也快十年了,基本都是OK
公司又小小的,基本上不會為了這一家公司的版稅資料,
而找專人寫程式,因而逼壞了大媽我 ಥ_ಥ
KYC World iT邦新手 5 級 ‧ 2020-07-15 16:52:54 檢舉
9萬多筆 先分成三個檔,然後砍成3份SHEET,分三個同事幫 忙處理呢?
分3次也保證了電腦不會出包的問題。
japhenchen iT邦研究生 1 級 ‧ 2020-07-15 16:54:36 檢舉
九萬筆會不會已經超出系統的負荷?所以七萬行OK,九萬行OVER?

試試把資料刪到只剩七萬行試試,如果就OK,那除了換x64的office,加大記憶體,說真的我也沒好的建議了

除非,你們會用python或c#直接讀寫xlsx檔,不然........我還是沒有好的建議
fannyliu iT邦新手 5 級 ‧ 2020-07-15 18:41:39 檢舉
我不會python或c#,但謝謝你的建議!
japhenchen iT邦研究生 1 級 ‧ 2020-07-16 07:25:56 檢舉
考慮外包請人家寫吧,有$就好辦事,不然工作還是會繼續卡住
froce iT邦大師 1 級 ‧ 2020-07-16 07:30:12 檢舉
9萬筆...最簡單就是匯入access處理了
淺水員 iT邦研究生 5 級 ‧ 2020-07-16 13:34:21 檢舉
這條公式:IF(SUMPRODUCT((A2=$A$2:A2)*(B2=$B$2:B2)*(C2=$C$2:C2)*(D2=$D$2:D2)*1)=1,1+MAX($F$1:F1),"")
他應該是整條拉下來
所以時間複雜度至少是 n^2
2
淺水員
iT邦研究生 5 級 ‧ 2020-07-17 14:11:00
最佳解答

弄了一個網頁版的程式來處理
檔案在此
下載後解壓縮,裡面有個 single.html 檔案,用瀏覽器打開就可以用了
打開後,選擇作為資料的 excel 就會跑出結果了

執行時間測試(19萬筆資料 + 5年前的文書用筆電)

瀏覽器:chrome

工作 花費時間
js-xlsx 套件讀檔 33.3秒
計算處理 1.5秒

瀏覽器:firefox

工作 花費時間
js-xlsx 套件讀檔 53秒
計算處理 1.9秒
看更多先前的回應...收起先前的回應...
fannyliu iT邦新手 5 級 ‧ 2020-07-17 16:57:13 檢舉

先謝謝 淺水員 用心的幫忙
使用您 single.html 檔案 作業後出現以下狀況,
資料有些不符,詳如下圖.
https://ithelp.ithome.com.tw/upload/images/20200717/20110195tjs1a0xu4A.jpg

淺水員 iT邦研究生 5 級 ‧ 2020-07-17 17:25:05 檢舉

這可能需要你把測試的檔案給我才有辦法確認問題在哪。
(先前你給的測試檔案是符合的)

淺水員 iT邦研究生 5 級 ‧ 2020-07-17 17:27:56 檢舉

喔,我看到你的著作人有使用到斜線,剛好我有用到那個當作分隔符號。
我改一下。

淺水員 iT邦研究生 5 級 ‧ 2020-07-17 17:54:35 檢舉

原本分隔符號我有用 / 所以發生問題
下面我把分隔符號改成 [@s#e!p] 了,你 excel 檔案應該不會用到這個字串吧。
新的檔案:song-1.0.1

PS. 如果還有其他問題可能就需要給我測試檔案了。

fannyliu iT邦新手 5 級 ‧ 2020-07-17 18:54:26 檢舉

您真是太厲害了, 檔案總金額 合計 無誤
祇有在筆數上,您結算出來的多了一些,
我比對資料,發現是 當英文大小寫不同時,您的檔案會當成2筆資料
詳如下圖。
能否請您再次幫忙,將其2筆合併? 再次感謝您!
https://ithelp.ithome.com.tw/upload/images/20200717/20110195cwaxVEUVqy.jpg

淺水員 iT邦研究生 5 級 ‧ 2020-07-17 20:23:23 檢舉

原本我只是玩玩 js-xlsx 套件。
做好後,想說與其刪除不如多少幫人一下,這才貼出來。
其實這樣直接幫別人寫程式是不合規矩的,這裡畢竟是「技術問答」,而不是「發包區」。
原始碼我有附,你可以找人改。
(這程式不難,有些學生應該也會改或是直接幫你做新的)

fannyliu iT邦新手 5 級 ‧ 2020-07-18 01:02:50 檢舉

我理解,但還是非常的謝謝您的幫助.
解決了我的困擾,真的非常的感恩~/images/emoticon/emoticon41.gif

2
浩瀚星空
iT邦超人 1 級 ‧ 2020-07-15 16:42:04

excel不是給你拿來處理數據的,是拿來運算的。
9萬多筆給excel。至少可以証明你的電腦等級的確不錯。居然沒有崩潰。

我大略看一下你的公式,也沒有什麼好簡化了。
最多只能從你的資料來源下手了。

看更多先前的回應...收起先前的回應...
fannyliu iT邦新手 5 級 ‧ 2020-07-15 16:44:14 檢舉

以上的格式,已是我簡化後的資料了 ಥ_ಥ

基本上不清楚你的資料及整理後的情況。
如果你希望幫忙的話。

先給個基本範例資料出來。這樣才好知道怎麼去改。
(可以先不用10萬筆。10幾筆的test資料就好)

或是提供你的xls也行。(資料不需要是正確的,反正只是幫你換公式。但原公式要是對的,才能了解你打算做啥事。)
對了,不要9萬筆,我的電腦受不了@@"

fannyliu iT邦新手 5 級 ‧ 2020-07-15 18:18:56 檢舉

您好,
以下是測試檔的連結,煩請幫忙了,謝謝!
https://drive.google.com/file/d/1aTa86LLgYE8IEn1XqhBqOPv5do1kd-_y/view?usp=sharing

fannyliu iT邦新手 5 級 ‧ 2020-07-16 11:10:24 檢舉

抱歉,我沒有設好,新連結如下,謝謝!

回答於下方,答案還是「樞紐分析表」

1
evio0502
iT邦新手 3 級 ‧ 2020-07-15 17:27:32

弱弱的問一下~你的EXCEL檔案是XLS還是XLSX,如果是XLS也會有影響喔~改成XLSX會好一點

fannyliu iT邦新手 5 級 ‧ 2020-07-15 18:40:26 檢舉

收到~ 謝謝你的建議.

2
rogeryao
iT邦高手 1 級 ‧ 2020-07-15 17:51:43

猜的 , 或許是資料有問題
總共有 6 條公式 , 就從最底下一筆(本期支付金額)公式去除 , 看看能不能跑出來 ;
若不能跑出來 , 在去除一筆(銷售地區)...以此類推

fannyliu iT邦新手 5 級 ‧ 2020-07-15 18:42:39 檢舉

好的,謝謝建議!

rogeryao iT邦高手 1 級 ‧ 2020-07-16 00:15:49 檢舉

...

1
ccenjor
iT邦新手 3 級 ‧ 2020-07-15 18:55:09

我個人感覺是你在INDEX、MATCH、MAX函數中一直使用整欄來處理(A:A、B:B、C:C、D:D、F:F)這5個欄位每個1,048,576*5=5,242,880,5百萬筆在算,電腦沒當掉算不錯了。
建議可否改為A1:A100000、B1:B100000、C1:C100000、D1:D100000、F1:F100000)看會不會好點。
或是以定義範圍名稱方式來寫上述欄位內容,隨著資料增刪只要更新範圍名稱所包含的儲存格範圍即可。

看更多先前的回應...收起先前的回應...
fannyliu iT邦新手 5 級 ‧ 2020-07-15 19:15:02 檢舉

我是有設 名稱定義 的

ccenjor iT邦新手 3 級 ‧ 2020-07-15 19:58:31 檢舉

我指的是公式中含有那些整欄的(A,B,C,D,F)。

ccenjor iT邦新手 3 級 ‧ 2020-07-15 20:04:05 檢舉

整欄參考
在舊版 Excel 中,使用大量整欄參考和多個工作表的活頁簿 (例如 =COUNTIF(Sheet2!A:A,Sheet3!A1)),在開啟或在刪除資料列時,可能會使用大量記憶體和 CPU。
Excel 2016 組建 16.0.8212.1000 能減少在這些情況下使用的記憶體和 CPU。
在具有 6 百萬個公式的活頁簿的測試範例中,使用整欄參考失敗,出現記憶體不足訊息,在 Excel 2013 LAA 與 Excel 2010 使用了 4 GB 的虛擬記憶體,但在 Excel 2016 僅使用了 2 GB 的虛擬記憶體。

ccenjor iT邦新手 3 級 ‧ 2020-07-15 20:09:09 檢舉

32 位元 Excel 的 LAA 記憶體改善
雖然 64 位元版本 Excel 中有大量記憶體的限制,32 位元版本則只有 2 GB 的虛擬記憶體。 有些客戶使用 32 位元版本,因為某些協力廠商增益集和控制項都無法在 64 位元版本中執行。
32 位元版本的 Excel 2013 和 Excel 2016 現在已啟用大量位址提示 (LAA)。 這會減少記憶體不足的錯誤訊息。
LAA 可在 Windows 64 位元版本上將可用虛擬記憶體的數量從 2 GB 加倍至 4 GB,並在 Windows 32 位元版本上將可用虛擬記憶體的數量從 2 GB 增加至 3 GB。

ccenjor iT邦新手 3 級 ‧ 2020-07-15 20:16:57 檢舉

查看您的活頁簿,並檢查所使用的公式種類。 有些公式會佔用大量記憶體。 這包括下列陣列公式:

  • 查找
  • INDIRECT
  • 補償
  • INDEX
  • 匹配
    使用它們是很好的方式。 不過,請留意您參考的範圍。

參照整欄的公式會導致 .xlsx 檔案中的效能不良。 格線大小從65536列增加至1048576列,而從256(IV)資料行增加到16384(XFD)欄。 建立公式的常見方式,雖然不是最佳作法,還是參考整欄。 如果您只參照舊版本中的一欄,則只包含65536儲存格。 在新版本中,您參照的是超過1000000欄。

假設您有下列 VLOOKUP:

=VLOOKUP(A1,$D:$M,2,FALSE)
在 Excel 2003 和更早版本中,此 VLOOKUP 參考的是一個包含655560儲存格的整列(10欄 x 65536 列)。 不過,使用新的較大格線時,相同的公式會參考幾乎10500000儲存格(10欄 x 1048576 列 = 10485760)。

ccenjor iT邦新手 3 級 ‧ 2020-07-15 20:18:45 檢舉

陣列公式
陣列公式很強大。 但必須正確使用。 很重要的一點是不要對陣列新增更多儲存格,而不是必須具有的儲存格。 當陣列中的儲存格有需要計算的公式時,會針對該公式中所參照的所有儲存格進行計算。

fannyliu iT邦新手 5 級 ‧ 2020-07-16 16:14:39 檢舉

謝謝你的說明

1
paicheng0111
iT邦高手 1 級 ‧ 2020-07-15 22:50:25
  1. 換成64位元的office試試看。
  2. 公式中有參數參照整個欄位(例如:$F:$F),轉換成有限個數(例如:$F$1:$F$90000)。
fannyliu iT邦新手 5 級 ‧ 2020-07-16 16:15:25 檢舉

謝謝你的建議

1
e6319a5b
iT邦新手 5 級 ‧ 2020-07-16 08:45:16

應該是excel的問題,之前剛寫自然語言的時候資料14萬筆也要跑半小時以上
但是前面有大大說到重點,就是excel不適合你作為你現在的工具,也許你可以考慮寫成json?

fannyliu iT邦新手 5 級 ‧ 2020-07-16 11:03:13 檢舉

我不會寫程式,不懂json,但還是謝謝您的建議。

1
海綿寶寶
iT邦大神 1 級 ‧ 2020-07-16 14:00:04

把那些公式全部刪掉
我的答案還是「樞紐分析」

先看結果
https://ithelp.ithome.com.tw/upload/images/20200716/200017873lznleieEw.png

我設定的資料範圍如下
$A$2:$E$1048576 (應該夠大了吧)
https://ithelp.ithome.com.tw/upload/images/20200716/20001787V0ZqyMCfkG.png

資料我則是複製你的資料
複製成 344,962 列(應該夠多筆了吧)
https://ithelp.ithome.com.tw/upload/images/20200716/20001787iirt21ihpk.png

產生上面那個結果的樞紐分析表
花了多少時間
....不到兩秒鐘

看更多先前的回應...收起先前的回應...
fannyliu iT邦新手 5 級 ‧ 2020-07-16 16:13:35 檢舉

謝謝 海綿寶寶大神

我需要做成以下EXCEL表格,再加入一些固定欄位資料,做成另一份資料後,最終再轉進access,所以這份資料需很乾淨如下表所示。
我不太會樞紐分析表,所以不知要怎麼拉出以下的表格
https://ithelp.ithome.com.tw/upload/images/20200716/20110195x7ZLgCMP9n.jpg

樞紐分析表就只能解決兩個問題
1.取代原本那些 sumproduct, index/match ... 的函式
2.減少統計運算時間

至於「格式」
可能要自己手動輸入
再把樞紐分析表的「值」複製貼進來

fannyliu iT邦新手 5 級 ‧ 2020-07-17 10:54:10 檢舉

謝謝您的建議
查已處理好的資料有9千多筆,這樣複製貼,我怕我老人眼花貼錯了,就慘了。
但還是非常感謝您的幫忙,謝謝!

其實我根本沒幫上忙
/images/emoticon/emoticon25.gif

我試了一下
只能做到這樣

https://ithelp.ithome.com.tw/upload/images/20200717/20001787eMVIpo8EHA.png

fannyliu iT邦新手 5 級 ‧ 2020-07-17 17:02:10 檢舉

我作業上還是稍會有困擾,但還是謝謝您的用心,感恩~

0
galon
iT邦見習生 ‧ 2020-07-20 17:06:44

建議將MAX($F:$F)這種整欄的參照改用代數
例如在N2輸入=MAX($F:$F)
這樣減少每個計算的程序

0
goodnight
iT邦研究生 3 級 ‧ 2020-07-23 11:23:59

你好, 這麼龐大的資料庫, 我看到的不僅是效率的問題 , 還有安全性的問題以及資料一致性的問題, 你在下方有提到銷售名稱大小寫不同的問題, 這當然可以在事後修正, 但你一次的報表有幾百筆, 恐怕你也會漏看, 建議你要正視規劃採用程式的方式來管理, 並且要做好多版次的備份, 以防萬一

多版次備份可以使用較大容量的 usb + 上傳到雲端硬碟

我個人的做法會是開一個 excel 負責新增資料庫及產生報表
我用你的問題, 自己練習寫了一個 excel vba 負責產生報表, 你可以參考看看, 如果你有需要完整範例再用站內訊息連絡

https://ithelp.ithome.com.tw/upload/images/20200723/20001633V6fHBSsSjT.jpg

我要發表回答

立即登入回答