五個「太多」，讓試算表變龜速的原因

太多筆資料

• 刪掉不必要的儲存格、行、欄，如果有空白的也建議刪一刪，讓試算表的負擔減輕一些。
• 切割（建議先做好備份，再做這個操作）：可以考慮把試算表分成幾片，把資料剪下貼到其他試算表，日後要再結合可以用 IMPORTRANGE 或 QUERY 等函式適度連接。
• 可以考慮用其他的資料庫軟體（BigQuery、MySQL...）。

太多條件式格式了

• 考慮縮小範圍使用！

太多需要試算表出力運算的函式

• RAND、RANDBETWEEN
• TODAY、NOW
• ARRAYFORMULA
• IMPORT 家族（IMPORTRANGE、IMPORTXML、IMPORTHTML 等）
• GOOGLEFINANCE、GOOGLETRANSLATE、DETECTLANGUAGE

• 動態運算：只要資料有更動，函式就會再更新一次。
• 呼叫試算表外面的服務：像是 GOOGLEFINANCE 會去找 Google 財經後端的資料、GOOGLETRANSLATE 和 DETECTLANGUAGE 也會去呼叫 Google 翻譯、請它回傳後端的資料。
• 可能會大範圍產出資料：IMPORT 家族和 ARRAYFORMULA 都有可能會一次性產出很多資料，這也會給試算表一些負擔。

• 重新審視一下這些函式，思考怎麼避免不必要的運算、減低運算的次數。
• 確定資料不會再一直變動更新時，就把計算範圍選取起來、用「僅貼上值」（或快捷鍵：Ctrl/⌘ + Shift + V）把資料變靜態吧！「僅貼上值」在「編輯」 「選擇性貼上」：

• 如果資料必須是動態的、還是有必要做這樣的運算的話，可以建立一個 IF 的開關機制，讓這些函式只在開關是「開」的時候執行。例如「IF 某儲存格等於『Go go!』，就執行這些函式」。（延伸閱讀：IF，如果 … 那就 … 不然 …）

=IF(A1 = "Go go!", IMPORTRANGE(....), "")

• 用 IMPORT 家族的函式時、盡量縮小範圍。如果真的得抽取大範圍的資料，或許可以用陣列並排的方式（大括號和分號）分段處理：

={IMPORTRANGE(第一段 IMPORTRANGE);

  IMPORTRANGE(第二段 IMPORTRANGE);

  ...;

  IMPORTRANGE(最後一段 IMPORTRANGE)} 

太多的參照

使用過多開放參照

什麼是開放參照？有這類參照的範圍看起來會像這樣：

=A:B  -- A 到 B 欄的所有內容

=2:2  -- 第二行的所有內容

=A2:B -- A2、B2 往下（A3 - A... 還有 B3 - B...）的所有內容

=A:B20 -- A20、B20 往下（A20 - A... 還有 B20 - B...）的所有內容

這麼一來，試算表就會需要不斷找到那個欄或行的最後一筆資料，再進行運算。範圍小小的倒是還好，但如果範圍很大的話，也有可能會拖到運算的速度。

解決方案

• 用封閉參照，定義清楚範圍的頭尾在哪：

=A2:B50

=C2:D100

=E5:F500

我們拿 VLOOKUP 和 QUERY 舉例：

=VLOOKUP(A2, 工作表!A:Z, 3, false) --> (X)

=VLOOKUP(A2, 工作表!A2:Z30, 3, false) --> (O)

=QUERY(工作表!A:Z, "SELECT ...") --> (X)

=QUERY(工作表!A2:Z30, "SELECT ...") --> (X)

如果用封閉參照，這麼一來 VLOOKUP 和 QUERY 就只會先從指定好的範圍去查找資料，就可以省下很多運算的時間了；換句話說，要做 VLOOKUP 或 QUERY 的表本身如果可以小巧玲瓏一點，就會幫大忙了！

不斷參照其他儲存格的資訊

這個也會讓試算表得花多點時間去運算、回找前一個儲存格的資料是什麼。下面的範例可能有點極端，不過我想說的「不斷參照」有點像是這樣：

解決方案：

• 就，別這樣寫吧 QQ
• 可以考慮用 ARRAYFORMULA 處理。
• 如果不需要做動態的運算，一樣可以用上面提到的「僅貼上值」來讓數字變成靜態即可。

太多跨工作表的參照了

像下面這樣，這些算式會跨越到其他工作表的資訊做運算：

='新工作表'!A50

=QUERY('新工作表'!A:B, "SELECT * ...")

=VLOOKUP(A1, '新工作表'!A2:D50, 2, false)

如果有太多這樣的參照，也會導致試算表要花很多時間去另外的表找到資料並做運算。

解決方案：

• 可以的話，盡量把需要的參照放在同一張工作表裡面。
• 如果需要跨工作表的參照，可以盡量把範圍放小一點，讓試算表可以快點找到資料。

太多資料透視表

• 試著用 QUERY 裡的 GROUP BY 和 PIVOT 計算
• 如果只是想要取得資料大致的狀況，利用「探索」功能，或是適度利用聚集函式（SUM()、AVERAGE()、COUNT()、MAX()、MIX()）即可。

• QUERY 函式大解析（五）：PIVOT 給你一雙透視眼
• 探索！你的省時省力好幫手
• 五分鐘一次搞懂聚集函數：SUM

如果你喜歡這次的文章，歡迎你透過這些方法支持我：

・按下愛心、按下儲存

・留言告訴我你的想法

・加入喜特先生的官方沙龍，即時看到我發布的教學

・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案

・追蹤喜特先生的 Facebook

・按這邊小額贊助我的創作！