為什麼昨天還好好的總表，今天整張都是 #REF!？IMPORTRANGE 出事的時候，畫面上常常只有一個錯誤碼，原因卻有好幾種。第一次授權怎麼按，在 IMPORTRANGE 入門 講過了，這篇從「公式沒寫錯，還是壞了」開始。
這篇引用的錯誤訊息都是 Sheets 顯示的原文；欄數對不上、空白列的小表，則是用同一份檔案裡的範圍來示意。來源檔改名、搬移、複製、刪除這幾種，行為依 Google 官方說明整理，改動正式的來源檔之前，建議先用測試檔試一次。
#REF! 的四種原因
遇到 #REF!，我的順序是：先點一下那個儲存格，讀跳出來的訊息；再把那條公式單獨貼到一張空白工作表的 A1，範圍縮成單一格（例如 "銷售!A1"）。單格都匯不進來，問題在授權或權限；單格可以、整段不行，問題就在範圍字串。
另一種 #REF! 是匯入的範圍被別的東西擋住，訊息是「系統未展開陣列結果，以免覆蓋 G2 中的資料。」（G2 會換成被擋住的儲存格），入門篇講過，這邊不重講。
還沒按「允許存取」
點儲存格會跳出卡片：「你必須連結這些試算表。目標試算表初次從新的來源試算表擷取資料時，你必須授予權限。」左邊是「瞭解詳情」，右邊是「允許存取」。意思是這份目標檔還沒有跟那份來源檔連上線，按右邊那顆就好。同一份來源檔的別的範圍，授權過一次就不用再按。
比較容易忽略的是：連線是這份目標檔跟這份來源檔之間的事。你把目標檔「建立副本」，或是在新的檔案裡寫同一條公式，都要再按一次。看到副本滿版 #REF! 先別慌，一個一個點開按。
來源檔沒有權限
按鈕沒出現，或按了之後還是 #REF!，訊息會是類似「您沒有該試算表的存取權限」。這個權限是看「按允許存取的那個人」，不是看事後打開總表的人，這點入門篇有畫圖。
這時我會先確認兩件事：
- 瀏覽器是不是登入了好幾個 Google 帳號，總表是用 A 帳號開著，來源檔卻只有 B 帳號有權限。
- 來源檔的擁有者是不是把你移掉了，或把檔案搬進了你沒有權限的資料夾。
另外，來源檔如果停用了下載、列印和複製選項，新寫的 IMPORTRANGE 公式就無法匯出資料（在那之前就設好的公式不受影響）。
修法只有一個，請來源檔的擁有者給你至少檢視權限，再回總表重新按。至於為了省事把來源改成「知道連結的任何人」，我個人不會這樣做，原因入門篇講過了。
工作表改名了
來源檔把「銷售」分頁改成「銷售_10月」，總表的公式還在找「銷售!A1:E」，就會變成 #REF!，訊息是「在匯入範圍中找不到範圍或工作表。」。好消息是名稱改回「銷售」，總表就恢復了，資料沒有壞。
最麻煩的是這個改名通常不是你做的，是分店的人順手改的，而你的總表是隔天才發現。修法有兩種：把範圍字串改成新名字，或像 多門市彙總實戰 那樣，把網址放在設定表，再多放一欄工作表名稱，公式寫成 IMPORTRANGE(設定!B2, 設定!C2&"!A2:E")。以後再改名，只要改設定表的一格。
在同一份檔案裡，INDIRECT("不存在!A1") 指向不存在的分頁，得到的也是 #REF!，不過這只是類比，真正的 IMPORTRANGE 行為還是要兩份檔案才看得到。
範圍字串寫錯
最常見的是全形：把 "銷售!A1:E" 打成 "銷售！A1:E"，驚嘆號是中文輸入法的全形。眼睛幾乎看不出來，結果是 #REF!，訊息跟工作表改名、打錯名稱是同一句「在匯入範圍中找不到範圍或工作表。」，所以光看訊息分不出是哪一種，要逐字對。還有分頁名稱多一個空白、冒號打成全形、結束欄寫成來源檔根本沒有的欄，都屬於這一類。
修法就是逐字對。我自己的習慣是把分頁名稱從來源檔直接複製貼上，不手打。
疊合時欄數不同
三家店用 {;} 疊起來，要求每一段的欄數一樣。假設台北店匯 2 欄（品項、杯數），台中店多加了一欄折扣，匯到 3 欄：
第一段 2 欄、第二段 3 欄，上下疊在一起
={A1:B3; D1:F3}會得到 #VALUE!，訊息是「ARRAY_LITERAL 中的陣列常值有一或多列缺少值。」，整張疊合都不見。修法是只取你要的那幾欄，讓每段欄數一致，第二段也從第 2 列開始，標題才不會重複：
第二段只取前兩欄，並跳過標題列
={A1:B3; D2:E3}來看看結果（這是同一份檔案裡 A1:B3 與 D1:F3 的模擬）：
| A | B | |
|---|---|---|
| 1 | 品項 | 杯數 |
| 2 | 蘋果紅茶 | 37 |
| 3 | 青茶 | 64 |
| 4 | 烏龍奶茶 | 112 |
| 5 | 蘋果綠茶 | 8 |
更麻煩的是欄數「剛好一樣」但位置變了。如果台中店不是在最後加折扣，而是插在品項跟杯數中間，順序變成品項、折扣、杯數，這時 D2:E3 一樣拿 2 欄，不報錯，杯數欄卻變成「九折」「無」這類文字：
| A | B | |
|---|---|---|
| 1 | 品項 | 杯數 |
| 2 | 蘋果紅茶 | 37 |
| 3 | 青茶 | 64 |
| 4 | 烏龍奶茶 | 九折 |
| 5 | 蘋果綠茶 | 無 |
這種靜悄悄的錯位比 #VALUE! 可怕。我在疊合表旁邊放一格檢查：
杯數欄的非數字儲存格有幾個，正常應該是 0
=COUNTA(B2:B5)-COUNT(B2:B5)正常時是 0，上面那張錯位的表會得到 2。檢查格紅了，就去找是哪家店動了欄位。欄位順序固定，要請各店遵守，這件事比公式重要。
開放式範圍帶進空白列
入門篇用 "銷售!A2:B" 這種不寫結束列的寫法，來源檔多一列總表就多一列，我很常用。但要小心，開放式範圍回傳的是來源工作表的全部列數，不是「到最後一列有資料」為止。來源工作表有 1000 列、只有 3 列資料，ROWS(IMPORTRANGE(..., "銷售!A1:B")) 照樣是 1000；寫成 "銷售!A1:B1000" 也一樣是 1000。內容是公式回傳空字串的列，也會照樣帶進來。
單獨放一格沒差，疊合的時候就麻煩了。兩段開放式範圍直接疊，結果近 2000 列，中間夾著一大段空白，下方有資料的話整個結果還會被擋成 #REF!，訊息是「系統未展開陣列結果，以免覆蓋 A51 中的資料。」（A51 會換成擋路的儲存格）。
為了看得清楚，下面用同一份檔案裡的範圍示意：假設台北店範圍是 A1:B6，其實只有 2 筆資料，後面 3 列是空的，接著疊台中店：
第一段範圍多帶了 3 列空白，後面才接第二段
={A1:B6; D2:E3}來看看結果，第 4 到 6 列是空白，台中店被擠到後面：
| A | B | |
|---|---|---|
| 1 | 品項 | 杯數 |
| 2 | 蘋果紅茶 | 37 |
| 3 | 青茶 | 64 |
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | 烏龍奶茶 | 112 |
| 8 | 蘋果綠茶 | 8 |
用位置取值的公式（像 INDEX）、算筆數的公式都會跟著錯。修法是在每一段 IMPORTRANGE 外面包一層 QUERY，把第 1 欄沒有東西的列丟掉，再疊合：
每一段各自用 QUERY 濾掉第 1 欄是空的列，再上下疊
={
QUERY(
IMPORTRANGE(設定!B2, "銷售!A1:B"),
"select Col1, Col2 where Col1 is not null"
);
QUERY(
IMPORTRANGE(設定!B3, "銷售!A2:B"),
"select Col1, Col2 where Col1 is not null"
)
}| A | B | |
|---|---|---|
| 1 | 品項 | 杯數 |
| 2 | 蘋果紅茶 | 37 |
| 3 | 青茶 | 64 |
| 4 | 烏龍奶茶 | 112 |
| 5 | 蘋果綠茶 | 8 |
標題加 2 + 2 列資料，剛好 5 列，沒有空白。改用 FILTER 濾掉第 1 欄是空的列也行。如果來源的空白是公式回傳的空字串，條件也可以寫成 Col1 <> ''。
公式越來越多的維護成本
每多一條 IMPORTRANGE，就多一個要授權的連線、一個可能因為改名或權限而壞掉的點、一條要被找到的公式。我自己的警訊是：有人問「這份總表到底連了哪些檔案」，而我一時答不出來。
找公式的時候，可以用尋找及取代（Windows 按 Ctrl + H，Mac 按 Cmd + Shift + H，在 Chrome），搜尋 IMPORTRANGE(，範圍選所有工作表，記得勾選連公式內容一起搜。
整理的方向有三個，做法在 多門市彙總實戰 有完整例子：
- 一個來源檔只匯一次，放進專門的「匯入」工作表，其他公式都從那張取值。
- 網址和分頁名稱放設定表，不寫死在公式裡。
- 每一個匯入區塊旁邊放一格檢查（像上面的非數字計數），壞了的時候一眼看出是哪一家。
疊合公式還有一個壞處：它是一條公式，只要其中一段 #REF!，整張疊合表就會變成 #VALUE!（訊息是「ARRAY_LITERAL 中的陣列常值有一或多列缺少值。」，不是 #REF!），一家店的檔案出問題，全部門市的報表都看不到。要判斷是哪一段壞了，我會把每一段各自貼到空白格單獨看。如果店數多、又常有人動檔案，可以考慮每家店一個匯入區塊，再從區塊疊起來。這是換來容易除錯、付出多一層工作表的代價，拆檔的取捨在 三層架構進階 有講。
來源檔改名、搬移與複製
這幾種動作的結果不一樣。以下依官方說明整理，動手改正式的來源檔之前，建議先用兩份測試檔試一次：
- 來源檔改檔名：公式是用檔案 ID 找檔案，所以不受影響。
- 來源檔搬到別的資料夾：ID 沒變，照常運作。但資料夾的共用設定可能跟著變，如果你因此失去檢視權限，就變成前面「來源檔沒有權限」的
#REF!。 - 來源檔分頁改名：前面講過，會壞（
#REF!），改回原名就恢復。 - 來源檔被永久刪除：沒東西可讀了，公式就取不到資料。
- 來源檔「建立副本」：這個最會害人。
每個月把上個月的分店檔「建立副本」當作新月份檔，是很多人的習慣。副本有自己的新 ID，總部公式還連著舊檔，不會報錯，資料只是停在上個月。換新檔要記得去設定表換網址，換完還要再按一次「允許存取」。
來源檔的擁有者離職或帳號被停用，也是常被忽略的一環，交接時一起檢查，清單在 協作與權限 有。
核取方塊控制顯示，不代表切斷連線
方格子核取方塊舊文曾把外包 IF 的匯入寫成「取消勾選就中斷、不再執行」。這個說法需要限縮：畫面變空白，只能證明目前回傳的顯示分支，不能單憑它推論 IMPORTRANGE 的快取、背景更新或外部請求都停止。
如果要用核取方塊當報表的顯示入口，先用少量合成資料、已授權的來源檔測試：勾選時看指定範圍是否正確，取消時看顯示分支，再修改來源、重新載入與切換回來核對。即使這幾步畫面都符合預期，也不代表網路請求已經完全停止。
它也不是權限開關。取消勾選不會撤銷既有匯入授權、來源分享或既存資料副本；要停止提供資料，應依需求處理分享／連線與保存副本，不能只藏住一塊輸出。核取方塊的值與進度條可看核取方塊教學。
資料延遲
來源檔改完，總表會跟著更新，但中間有延遲，不保證同一秒。依官方說明，來源檔有任何更新，所有開啟中的目的檔都會更新；目的檔剛開啟、或過去 5 分鐘內開過時，會重新載入 IMPORTRANGE；檔案開著的期間，每小時會自動檢查一次更新。在同一個檔案裡按瀏覽器重新整理，不會觸發 IMPORTRANGE 重新載入。店員剛登記完，我馬上去對帳，看到舊數字，不是公式壞了，隔一陣子再看就好。
要注意兩件事：幾份檔案之間一層接一層地串 IMPORTRANGE，更新延遲會拉得更長，所以我盡量只從來源檔直接匯入；另外每次請求最多只能收 10 MB 的資料，儲存格出現「Loading…」是因為請求量太大，要等一下。
總表的報表是從匯入表算出來的，匯入表還沒更新，報表當然也還是舊的，不用去檢查下游的公式。
月底結帳的時候，我會在結帳當天把「匯入」工作表整張複製、貼成值，存成一個有日期的分頁（Windows 按 Ctrl + Shift + V，Mac 按 Cmd + Shift + V，在 Chrome，會直接貼值）。之後分店再改了舊資料，結帳的數字也不會變，要比對差異時多一個依據。
找出錯在哪
總部這樣疊合兩家分店：={A1:D4; F1:J4}。台北店的資料在 A1:D4（4 欄），台中店在 F1:J4（5 欄）。請問結果會是什麼錯誤碼？第二段要怎麼改，才不會出錯、標題也不重複？
出事的時候，我現在都先讀錯誤碼和訊息、再找是哪一段，不會一上來就去改公式。
我是喜特先生，Mr. Sheet，我們下個教學見！
這個步驟如果每週都要重做
- 現在
- 總表出現 #REF! 才發現，再逐段檢查是哪家店改了分頁名稱
- 自動化後
- 每天自動檢查各店檔案，分頁名稱或欄位被改動就立刻寄通知
接下來可以
相關文章
查看所有文章IMPORTRANGE 跨試算表同步：授權、匯入與合併
用 IMPORTRANGE 把另一份試算表的資料匯進來：從網址取得 ID、第一次按「允許存取」、範圍的寫法、把好幾份檔案疊成一張，以及授權跟權限的關係。
共用與保護：多人試算表誰能看、誰能改
把試算表交給多人使用前，先分清檢視者、加註者、編輯者、擁有者能做什麼，決定該指定對象還是用連結，再照三層架構決定每一層怎麼保護，以及要讓不同人看不同資料時為什麼得拆檔。單項操作步驟在共用與保護的專文。
三層架構進階：拆檔、IMPORTRANGE、權限與效能取捨
茶飲店月報從一個檔案長成訂單檔加月報檔：怎麼用 IMPORTRANGE 拆開原數據層、誰該有哪個檔的編輯權、改公式前怎麼用副本測試，以及什麼時候不必拆。
留言討論
共 0 則留言