請問各位大能
下圖之公式是否有優化的可能?
之前沒什麼問題(7萬多筆),稍慢一些但約十分鐘就OK了,
但現9萬多筆資料卻直接半小時還不動,
我把所有的程式都關了,祇留EXCEL作業,結果還是相同,
已弄了3天,也問過電腦維修公司,電腦設備沒問題,
他們認為是 公式的問題.
因而請求各位幫忙,謝謝!
===============================================
109.07.16
目前已請電腦維護公司用其高效電腦幫我處理好資料了,謝謝各位!
但還是希望有大神能幫我改進公式,再次謝謝大家!
已邀請的邦友 {{ invite_list.length }}/5
弄了一個網頁版的程式來處理
檔案在此
下載後解壓縮，裡面有個 single.html 檔案，用瀏覽器打開就可以用了
打開後，選擇作為資料的 excel 就會跑出結果了
執行時間測試（19萬筆資料 + 5年前的文書用筆電）
瀏覽器：chrome
| 工作 | 花費時間 |
|---|---|
| js-xlsx 套件讀檔 | 33.3秒 |
| 計算處理 | 1.5秒 |
瀏覽器：firefox
| 工作 | 花費時間 |
|---|---|
| js-xlsx 套件讀檔 | 53秒 |
| 計算處理 | 1.9秒 |
先謝謝 淺水員 用心的幫忙
使用您 single.html 檔案 作業後出現以下狀況,
資料有些不符,詳如下圖.
這可能需要你把測試的檔案給我才有辦法確認問題在哪。
（先前你給的測試檔案是符合的）
喔，我看到你的著作人有使用到斜線，剛好我有用到那個當作分隔符號。
我改一下。
原本分隔符號我有用 / 所以發生問題
下面我把分隔符號改成 [@s#e!p] 了，你 excel 檔案應該不會用到這個字串吧。
新的檔案：song-1.0.1
PS. 如果還有其他問題可能就需要給我測試檔案了。
excel不是給你拿來處理數據的，是拿來運算的。
9萬多筆給excel。至少可以証明你的電腦等級的確不錯。居然沒有崩潰。
我大略看一下你的公式，也沒有什麼好簡化了。
最多只能從你的資料來源下手了。
以上的格式,已是我簡化後的資料了 ಥ_ಥ
基本上不清楚你的資料及整理後的情況。
如果你希望幫忙的話。
先給個基本範例資料出來。這樣才好知道怎麼去改。
(可以先不用10萬筆。10幾筆的test資料就好)
或是提供你的xls也行。(資料不需要是正確的，反正只是幫你換公式。但原公式要是對的，才能了解你打算做啥事。)
對了，不要9萬筆，我的電腦受不了@@"
您好,
以下是測試檔的連結,煩請幫忙了,謝謝!
https://drive.google.com/file/d/1aTa86LLgYE8IEn1XqhBqOPv5do1kd-_y/view?usp=sharing
弱弱的問一下~你的EXCEL檔案是XLS還是XLSX，如果是XLS也會有影響喔~改成XLSX會好一點
猜的 , 或許是資料有問題
總共有 6 條公式 , 就從最底下一筆(本期支付金額)公式去除 , 看看能不能跑出來 ;
若不能跑出來 , 在去除一筆(銷售地區)...以此類推
我個人感覺是你在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)看會不會好點。
或是以定義範圍名稱方式來寫上述欄位內容，隨著資料增刪只要更新範圍名稱所包含的儲存格範圍即可。
我是有設 名稱定義 的
我指的是公式中含有那些整欄的(A,B,C,D,F)。
整欄參考
在舊版 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 的虛擬記憶體。
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。
查看您的活頁簿，並檢查所使用的公式種類。 有些公式會佔用大量記憶體。 這包括下列陣列公式：
參照整欄的公式會導致 .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）。
陣列公式
陣列公式很強大。 但必須正確使用。 很重要的一點是不要對陣列新增更多儲存格，而不是必須具有的儲存格。 當陣列中的儲存格有需要計算的公式時，會針對該公式中所參照的所有儲存格進行計算。
謝謝你的說明
$F:$F)，轉換成有限個數(例如：$F$1:$F$90000)。
應該是excel的問題，之前剛寫自然語言的時候資料14萬筆也要跑半小時以上
但是前面有大大說到重點，就是excel不適合你作為你現在的工具，也許你可以考慮寫成json?
把那些公式全部刪掉
我的答案還是「樞紐分析」
先看結果
我設定的資料範圍如下
$A$2:$E$1048576 (應該夠大了吧)
資料我則是複製你的資料
複製成 344,962 列(應該夠多筆了吧)
產生上面那個結果的樞紐分析表
花了多少時間
....不到兩秒鐘
謝謝 海綿寶寶大神
我需要做成以下EXCEL表格,再加入一些固定欄位資料,做成另一份資料後,最終再轉進access,所以這份資料需很乾淨如下表所示。
我不太會樞紐分析表,所以不知要怎麼拉出以下的表格
樞紐分析表就只能解決兩個問題
1.取代原本那些 sumproduct, index/match ... 的函式
2.減少統計運算時間
至於「格式」
可能要自己手動輸入
再把樞紐分析表的「值」複製貼進來
謝謝您的建議
查已處理好的資料有9千多筆,這樣複製貼,我怕我老人眼花貼錯了,就慘了。
但還是非常感謝您的幫忙,謝謝!
你好, 這麼龐大的資料庫, 我看到的不僅是效率的問題 , 還有安全性的問題以及資料一致性的問題, 你在下方有提到銷售名稱大小寫不同的問題, 這當然可以在事後修正, 但你一次的報表有幾百筆, 恐怕你也會漏看, 建議你要正視規劃採用程式的方式來管理, 並且要做好多版次的備份, 以防萬一
多版次備份可以使用較大容量的 usb + 上傳到雲端硬碟
我個人的做法會是開一個 excel 負責新增資料庫及產生報表
我用你的問題, 自己練習寫了一個 excel vba 負責產生報表, 你可以參考看看, 如果你有需要完整範例再用站內訊息連絡
iThome鐵人賽
看影片追技術