iT邦幫忙

1

EXCEL檔案開啟速度慢!

事情是這樣;
使用者的EXCEL檔案內約有50萬筆資料,裡面含有大量的公式、參考不同的Sheet。
之前電腦用的是傳統硬碟開啟檔案約要20~30分鐘,換了固態硬碟、DDR-4 RAM加到24GB,開啟時間縮短到約10分鐘。

但使用者一直強調她們在之前的公司,相同資料量、相同作法的EXCEL都是秒開。
我實在找不到讓約有50萬筆資料及有大量公式的EXCEL檔秒開的方法,特地到此尋找神人提示解法!

在此先感謝神人開示!

看更多先前的討論...收起先前的討論...
sclin2k iT邦新手 4 級 ‧ 2020-10-29 22:00:16 檢舉
補充一下:
請使用者再在做完資料運算、分析後另存成純數值的資料。
但使用者堅定的回答:不可能!
slime iT邦大師 1 級 ‧ 2020-10-30 08:16:59 檢舉
檔案在本機還是網路磁碟?
kagaya iT邦新手 5 級 ‧ 2020-10-30 08:43:22 檢舉
開完不用關 就行了
misadm iT邦高手 10 級 ‧ 2020-10-30 08:49:56 檢舉
> 但使用者一直強調她們在之前的公司,相同資料量、相同作法的EXCEL都是秒開...

使用者的『之前』往往不具任何可信度,請堅決跟使用者說明,如果可能請使用者提出證據,否則空口白話,誰都會說。
ks1217 iT邦研究生 4 級 ‧ 2020-10-30 09:59:18 檢舉
曾經處理過EXCEL檔開啟要等很久的 0-100%, 檢查發現, 使用者非Excel表的創建者,

每次新增資料就是用複製貼上的, 導致兩三千筆欄位做相同的格式化條件(檢驗同一個欄位...) , 所以Excel每次開都要去跑一下格式化條件,導致開啟變慢,
另外還發現因為複製貼上導致資料下方超多空白欄位, 經過清除空白欄位後, 5x MB的Excel表瘦身成8MB. 開Excel就變快了, 供您參考
有大量公式 => 開啟時會復算,所以你的問題應該是卡在 CPU 不夠快
基本上建議事換高階CPU,以50萬筆的資料量來說,要找工作站等級的CPU
8C16T 是基本要求,如果可以實體雙CPU更好,最好是兩顆 8C16T
接著是記憶體,你沒講檔案大小,基本上這種規模的檔案至少在 2-4GB左右
記憶體需求大概也要檔案大小的 5~6倍才夠,如果有大量運算,還要再增加
所以記憶體拉到 64~128GB是有必要的,就說了,這種大小要工作站等級才能處理,一般PC很難,除非你的規格可以達到工作站等級,不然很難秒開
還有 2GB 的檔案要秒開,應該用了RAID 讀取技術吧,例如8顆SATA3 SSD 做 RAID 10,就能秒開了,或是放到 PCIE SSD 上面,然後千萬不要買一樓說的消費級SSD,要買企業用的,一顆價格大概是消費級的4倍以上,才能有比較穩定的效能
裡面可能有連接外部工作表或資料連結,只有一個EXCEL檔複製出來卻沒有把那些外部資料一起處理,就有可能會因為連接錯誤而造成遲頓吧
sclin2k iT邦新手 4 級 ‧ 2020-10-30 13:46:16 檢舉
謝謝大家的回應;
檔案是在網路硬碟裡,檔案讀取的速度應該是沒問題的(也有將檔案複製到本機上測試過)。
另外,這個檔案是由DB裡抓出使用者要求的基礎資料,大小約150MB,後續再依自己的需求設定公式、分析。
至於電腦硬體是HP ProOne 400 G4, CPU i5-8500 3.00GHz,固態硬碟,記憶體是8GB+16GB,開檔時查看工作管理員,CPU、RAM和硬碟也沒有拉高使用率的情況。
ckp6250 iT邦研究生 3 級 ‧ 2020-11-01 14:52:39 檢舉
我感覺到好像用錯工具了,
雖說 excel 可以處理這麼大量的資料,但,一個表放50萬筆,怎麼操作啊?
0
richardsuma
iT邦大師 1 級 ‧ 2020-10-29 22:40:32

Excel 版本一樣嗎?

不然將RAM加到 128GB,
硬碟換成 SAMSUNG 三星 970 EVO 1TB NVMe M.2 2280 PCIe 固態硬碟(MZ-V7E1T0BW)
讀取速度3,400MB/s
試試看!

sclin2k iT邦新手 4 級 ‧ 2020-10-30 13:49:03 檢舉

目前的硬體是HP ProOne 400 G4,CPU i5-8500 3.00GHz, 固態硬碟,記憶體上限是32GB
除非換電腦了,不然沒辦法加到128GB

1
海綿寶寶
iT邦大神 1 級 ‧ 2020-10-29 23:00:40

50萬筆資料

真厲害
從我認識 Excel 以來處理過的所有資料
加起來也沒有 50 萬筆
/images/emoticon/emoticon12.gif

針對問題回答
改善硬體我不會
就軟體方面
我唯二想得到的就是
1.把「自動運算」改成「手動運算」
2.先去檢查看看在 WorkSheet_Open 裡有沒有寫什麼 VBA,再去檢查所有的 VBA
/images/emoticon/emoticon13.gif

ckp6250 iT邦研究生 3 級 ‧ 2020-11-01 14:58:28 檢舉

我是有用excel做【會計帳冊】呈現,
一頁要佔66列,50萬列可以擺上7575頁,
不過,因為是由我會計程式中產出,資料很乾淨,
所以,開檔速度還可以。

0

"裡面含有大量的公式、參考不同的 Sheet"

我會看這個問題
大量的公式跟參考 sheet 都在本身 excel 檔案中?還是其他的 excel 檔?參考公式有包含運算當中嗎 (因單純參考資料跟重新運算取得更新會有很大時間差)?如果參照其他 excel 檔,參照了幾個層次?
如果層次超過 3 層 (亦即 A 原生,B 參考 A、C 再參考 B、D 再參考 C),那開檔案真的慢慢等、泡咖啡先 ......
如果使用者真要參照那麼多層次,建議他把所有參照資料放到一個集中檔,所有檔案均到那個集中檔讀取,最多 2 層
我不先否定使用者的說法,先了解整個檔案架構再坐適當判斷

sclin2k iT邦新手 4 級 ‧ 2020-10-30 13:51:21 檢舉

參考 sheet 都在本身檔案,但也有連結到外部檔案!

2
stephen3342
iT邦新手 4 級 ‧ 2020-10-30 09:22:54

多年前剛到這家公司時,使用者也有反應,Excel開啟要20多分鐘,以前都不會...等等
因該Excel檔會多人維護,推測為每位使用者的習慣不同,造成"不可見"物件有上萬個,
寫VBA將這些無用物件刪除後,即恢復正常的開啟速度
我們與您的情況並不完全相同,提供參考

0
hsiang11
iT邦好手 1 級 ‧ 2020-10-30 11:07:14

大多數office檔案會出問題的因素很複雜
有陳年的檔案經年累月 換過不少office版本 環境變遷了 檔案沒有隨著改變
有經過各使用者編輯 複製貼上的來源各有不同 就把一些鬼東西一起複製過來了
有微軟自己更新出包 出了奇怪的問題 最後還是要找出哪一隻更新出包 只能先移除等微軟解決
以前就有遇過連電話簿這種簡單的excel都可以當掉
複製了貼去新的excel照當
後來發現不用複製一筆一筆重打資料就沒事了

但是50萬筆 這種痛不是一般人可以想像的
你只能找有沒有辦法把資料匯出
先把資料單純化之後 再來找這個問題到底怎樣發生的

我要發表回答

立即登入回答