IMPORTRANGE 跨試算表同步：授權、匯入與合併

我有一份試算表放手搖飲的菜單，另一份試算表放每天的銷售日報，日報需要菜單的單價。菜單改價格的時候，我得把新單價複製、貼到日報檔，漏掉一次，日報就會用舊價格算下去。今天來聊聊 IMPORTRANGE，讓日報檔自己去菜單檔拿資料。

手動複製和自動匯入差在哪

手動複製貼上，貼完那一刻資料是對的，之後來源檔再怎麼改，目標檔都不知道。用 IMPORTRANGE 的話，目標檔裡放的是一條公式，每次都回頭去來源檔讀。

← 左右滑動看完整的圖，點圖可放大

圖：兩種做法的差別只有箭頭，一個是貼一次，一個是一直連著。

IMPORTRANGE

IMPORTRANGE 可以把另一份試算表裡指定範圍的資料，匯入目前這份試算表。

語法

=IMPORTRANGE(試算表網址或ID, 範圍字串)

• 試算表網址或 ID：來源檔的網址，或是網址裡的那串 ID，要用雙引號包住。
• 範圍字串：「工作表名稱!範圍」，整串也要用雙引號包住，例如 "產品清單!A1:C6"。

試算表 ID 在哪裡

打開來源檔，網址長這樣：

https://docs.google.com/spreadsheets/d/1a2B3c4D5eF6g7H8/edit

/d/ 和後面的 /edit 中間那一串 1a2B3c4D5eF6g7H8 就是 ID。我習慣整串網址直接貼進去，不用自己截 ID，不過 ID 比較短，公式讀起來比較好看。本篇的 ID 都是示意用的，要換成你自己來源檔的。

第一次匯入

來源檔的工作表叫「產品清單」，內容是：

我想把這 5 個品項連標題一起匯進日報檔。在日報檔的 A1 輸入：

• "1a2B3c4D5eF6g7H8"：來源檔的 ID。
• "產品清單!A1:C6"：來源檔的「產品清單」工作表，A1 到 C6 的範圍。

若這組來源與目標檔尚未連結，輸入後通常會出現 #REF!。依官方授權流程，可照下面做：

1. 操作者先確認能開啟來源檔，且能編輯目標檔。
2. 在目標檔輸入公式，稍候後選取 A1，讀錯誤訊息。
3. 若是還沒連結，卡片會寫「你必須連結這些試算表。目標試算表初次從新的來源試算表擷取資料時，你必須授予權限。」左邊是「瞭解詳情」，右邊是「允許存取」，按「允許存取」；若是沒有來源權限，先向來源擁有者取得存取權。#REF! 也可能是範圍錯誤或輸出被擋，不能一律當作未授權。
4. 等待讀取完成，核對標題、列數與品項，再繼續用下游公式。

下圖是授權後的結果：

← 左右滑動看完整的圖，點圖可放大

圖：左邊是日報檔（目標檔），右邊是菜單檔（來源檔），內容一樣。

現在回來源檔把烏龍奶茶改成 58，日報檔的 C3 會跟著變，不用再貼一次。不過同步有時候不是那一秒就到，改完如果沒變，等一下再看看。

目標檔從 A1 展開 6 列 3 欄（含標題），要記得這個範圍下面和右邊要是空的，不然會被擋住，這個在後面「看到錯誤怎麼辦」會再講。

同一份目標檔去匯同一份來源檔，只需要由適當使用者建立一次存取連結。之後再多寫幾條 IMPORTRANGE 去拿來源檔的別的範圍（不管第一個參數用網址還是 ID），不用重新按。

繼續下一節前，請回來源檔把 產品清單!C3 從 58 還原成 55，等待目標檔也顯示 55。下方 QUERY 結果表都以這份還原後的菜單為準。

範圍怎麼寫

範圍字串有幾種寫法，看你想怎麼拿。

整欄不寫列數，來源檔往下新增的資料會自己跟進來：

寫死列數的話，像上面的 A1:C6，新增第 7 列就不會進來，要回頭改公式。我個人遇到會一直新增資料的來源，就用整欄的寫法，固定不變的小清單才寫死範圍。

不過有一點要留意：不寫結尾列（A:C，或後面會用到的 "銷售!A1:B"），回來的是來源工作表的全部列數，包含沒有資料的空白列。來源工作表如果有 1000 列，這個範圍就是 1000 列，資料只佔前面幾列，後面全是空白。單獨放一格沒差，要跟別的資料上下疊就會出問題，下一節會講怎麼處理。

第一個參數除了網址和 ID，也可以是放著完整網址的儲存格，例如 =IMPORTRANGE(E91, "產品清單!A1:C6")，E91 放網址就行。

工作表名稱裡有空格的話，名稱要用單引號包起來，單引號寫在雙引號裡面：

如果只想要其中幾欄、或只留符合條件的列，可以把 IMPORTRANGE 放進 QUERY 裡。比方說只要單價大於 40 的品名跟單價：

來看看結果：

青茶是 35，沒有超過 40，所以被篩掉了。QUERY 的寫法在 QUERY 基礎 有講，這邊先看得懂這一條就好。

把好幾份檔案疊在一起

三家分店各有一份銷售檔，工作表都叫「銷售」，欄位一樣是品項和杯數。每份有 1 列標題、2 列資料：

我想在總部檔把三份疊成一張。用大括號 {} 把三個 IMPORTRANGE 用分號 ; 接起來，分號就是「上下疊」的意思。因為 "銷售!A1:B" 這種開放式範圍會帶回整張工作表的列數（含空白列），直接疊的話，來源工作表若有 1000 列，兩段疊起來就近 2000 列，夾著大量空白列，下方有資料還會被擋成 #REF!，訊息是「系統未展開陣列結果，以免覆蓋某某儲存格中的資料。」。所以每一份外面包一層 QUERY，用 where Col1 is not null 把空白列濾掉：

• 第一個從 A1 開始，連標題一起拿。
• 第二個、第三個從 A2 開始，跳過各自的標題，不然標題會出現三次。
• where Col1 is not null：第 1 欄有東西的列才留下，空白列就不會跟進來。用 FILTER 也可以做到同樣的事。
• 每份來源檔第一次都要各按一次「允許存取」。

來看看結果：

1 列標題加上 2 + 2 + 2 列資料，一共 7 列。要注意，上下疊的幾份資料欄數要一樣，不然整個結果會變成 #VALUE!，訊息是「ARRAY_LITERAL 中的陣列常值有一或多列缺少值。」之後哪家分店新增一列，總表就跟著多一列。

誰需要有權限

很多人會卡在這邊，我用一張圖說明。

← 左右滑動看完整的圖，點圖可放大

圖：授權是由你這個目標檔的編輯者完成的，其他人只是打開目標檔。

「你」要先對來源檔至少有檢視權限，才能按下「允許存取」。按完之後，這份目標檔就可以去讀那份來源檔。其他人打開目標檔時，看到的是匯入進來的資料，不需要自己也有來源檔的權限。

這也代表一件事：按下「允許存取」，授權的是「這份目標檔可以讀來源檔」，不只公式裡寫的那段範圍。Google 的說明寫得很清楚：授權之後，目標檔的任何編輯者都能用 IMPORTRANGE 從來源檔的任何位置拉資料；能打開目標檔的人，則看得到匯進來的結果。所以不是什麼檔案都適合隨手授權，目標檔的編輯權限要收緊，敏感的欄位只匯需要的那幾欄，這個在 協作與權限 有講到。

官方另說明，連結持續有效直到授權者被移出來源檔；換負責人時要核對這一點。

至於要不要為了讓別人看到而把來源檔改成「知道連結的任何人」，我個人不會這樣做，因為這等於把整份來源檔都公開了。

看到錯誤怎麼辦

• #REF! 加上「允許存取」：核對來源、目標編輯者與來源權限，再建立這一組檔案的連結；不是每條公式或每位管理員各做一次。
• #REF!，訊息是「在匯入範圍中找不到範圍或工作表。」：檢查工作表名稱有沒有打錯、有沒有多空格，驚嘆號是不是半形的 !（全形的 ！ 也是這個錯誤）。來源的工作表如果被改名，目標檔也會變這個錯，名稱改回來就恢復。
• #VALUE!，訊息是「ARRAY_LITERAL 中的陣列常值有一或多列缺少值。」：上下疊的時候欄數不一致。疊合裡有一段是 #REF!（例如某份來源的工作表名稱錯了），整個結果也會變成這個 #VALUE!，不是 #REF!，要逐段單獨檢查。
• #REF!，訊息是「系統未展開陣列結果，以免覆蓋某某儲存格中的資料。」：匯入範圍的下方或右邊有東西擋住，訊息裡的儲存格就是擋路的那一格。清掉那些儲存格，或把公式移到空的地方。
• 你按了「允許存取」卻還是 #REF!：先確認你自己能不能打開來源檔，沒有權限的話按了也沒用，需要向來源檔的擁有者要檢視權限。

資料多的時候

每一條 IMPORTRANGE 都要去讀另一份檔案，條數多了，目標檔可能會開得比較慢，我沒有量過具體差多少。我個人的做法是：一個來源檔只匯一次，放進一張專門的「匯入」工作表，其他公式再從那張工作表取值，不要每個儲存格各寫一條 IMPORTRANGE。

匯進來的資料之後怎麼清理、怎麼算，我會再放到別的工作表，原始匯入的那張不動。這個分層的想法在 三層式架構 有講，從表單接到報表的整條自動鏈則在 自動化流程設計。

練習

小測驗

匯入網頁表格或 CSV 的做法，在 網頁資料匯入函式 有講。

多份檔案真的組起來的例子在 多門市銷售彙總，公式突然 #REF! 的時候怎麼查，在 IMPORTRANGE 為什麼突然 #REF!。

我是喜特先生，Mr. Sheet，我們下個教學見！