Google Sheets 教學：跨試算表 VLOOKUP 查詢，用 IMPORTRANGE 實現資料同步！

你是否曾經想在 Google Sheets 中，像 Excel 一樣對另一個完全獨立的檔案進行 VLOOKUP 查詢？許多使用者都想知道，是否能將一份總表（例如：產品價目表）的資料，動態地查詢到另一份訂單或報表中。

答案是肯定的！透過 Google Sheets 的 IMPORTRANGE 函式，我們可以輕鬆地將不同試算表檔案的資料匯入，並結合 VLOOKUP 進行查詢，實現跨檔案的資料連動。今天就讓我們跟著教學影片，一步步學會這個強大的技巧。

範例情境

假設我們有兩個獨立的 Google Sheets 檔案：

1. 來源檔案： 香純滴雞精價目表 - 這份檔案是我們的資料庫，包含了所有產品的「編號」、「項目名稱」與「價格」。

2. 目標檔案： Vlookup跨檔案查詢 - 我們希望在這份檔案中，只要輸入「編號」，就能自動帶出對應的「項目名稱」和「價格」。

操作步驟

首先，我們需要告訴目標檔案要去哪裡抓資料。

1. 打開你的來源檔案 (香純滴雞精價目表)。

2. 複製瀏覽器網址列上的完整網址。這就是 IMPORTRANGE 函式需要的第一個參數。

3. 記下你要抓取的資料範圍，包含工作表名稱與儲存格範圍。在我們的範例中，工作表名稱是 工作表1，資料範圍則是 A1 到 C11。

接著，回到你的目標檔案，我們開始建立公式。

1. 在要顯示「項目名稱」的儲存格 (例如 B2) 中，輸入 VLOOKUP 的基本結構。我們要查詢的目標是儲存格 A2 的「編號」。

   =VLOOKUP(A2, ... , 2, 0)

2. VLOOKUP 的第二個參數是「資料範圍」，這裡就是 IMPORTRANGE 發揮作用的地方。我們將 IMPORTRANGE 函式嵌入其中：

   IMPORTRANGE("試算表網址", "範圍字串")

   • 試算表網址： 貼上我們在步驟一複製的完整網址。

   • 範圍字串： 輸入 工作表名稱!儲存格範圍，例如 "工作表1!A1:C11"。

3. 為了方便等一下向下複製公式，我們最好使用絕對位址 ($) 將範圍固定住，變成 "工作表1!$A$1:$C$11"。

將 VLOOKUP 與 IMPORTRANGE 組合起來，查詢「項目名稱」(第 2 欄) 的完整公式如下：

=VLOOKUP(A2, IMPORTRANGE("https://docs.google.com/spreadsheets/d/1b9B9FLiasxm...", "工作表1!$A$1:$C$11"), 2, 0)

注意：第一次使用 

授權後，你就會看到對應的項目名稱成功被查詢出來了！

1. 將 B2 儲存格的公式複製到 C2 (價格欄)。

2. 修改公式的第三個參數 (回傳欄位索引)，將 2 改成 3，因為價格是在來源資料的第三欄。

   =VLOOKUP(A2, IMPORTRANGE("https://docs.google.com/spreadsheets/d/1b9B9FLiasxm...", "工作表1!$A$1:$C$11"), 3, 0)

當你將公式向下拖曳時，如果「編號」欄是空的，會出現 #N/A 的錯誤訊息。我們可以利用 IFERROR 函式來處理這種情況。

將原本的公式用 IFERROR 包起來，如果發生錯誤，就顯示為空白 ("")。

• 項目名稱公式：

  =IFERROR(VLOOKUP(A2, IMPORTRANGE("...", "工作表1!$A$1:$C$11"), 2, 0), "")

• 價格公式：

  =IFERROR(VLOOKUP(A2, IMPORTRANGE("...", "工作表1!$A$1:$C$11"), 3, 0), "")

現在，將這兩個美化過的公式向下拖曳，你的報表看起來就非常乾淨專業了！

重點回顧

• 核心函式： IMPORTRANGE 是實現跨檔案資料匯入的關鍵。

• 參數格式： IMPORTRANGE 的兩個參數（網址、範圍字串）都必須用雙引號 "" 包起來。

• 首次授權： 連接新的檔案時，記得要點擊「允許存取」。

• 絕對位址： 在定義資料範圍時，使用 $ (例如 $A$1:$C$11) 可以確保複製公式時範圍不會跑掉。

• 錯誤處理： 搭配 IFERROR 可以讓你的報表在查無資料時保持整潔。

學會了 VLOOKUP 與 IMPORTRANGE 的組合技，你就能更靈活地組織與串連你的 Google Sheets 資料，打造出自動化更新的儀表板與報表！ 

AppSheet 教學：使用 SPLIT 與 INDEX 函數自動遞增數字區間（例如：複製 101-120 為 121-140）

在開發 AppSheet 應用程式時，我們時常需要處理文字字串，並從中提取或生成新的資訊。今天我們要解決一個常見且實用的情境：如何自動化建立有連續數字區間的任務。

前言：情境與挑戰

假設我們正在建立一個學習進度追蹤 App，用來記錄背誦單字或閱讀章節的進度。每一筆紀錄可能像這樣：

• 高中7000單字表 第2級填空測驗 101-120

• 高中7000單字表 第2級填空測驗 121-140

當我們完成「101-120」這個區間的學習後，希望可以一鍵新增下一筆任務，並且 App 自動幫我們產生「121-140」這個新的區間，省去手動計算與輸入的麻煩。

過去我們可能會想使用 MID() 函數來抓取數字，但當數字位數不同時（例如從 81-100 到 101-120），MID() 的參數設定會變得很複雜且沒有彈性。因此，我們需要一個更強大的組合：SPLIT() 與 INDEX()。

核心功能：SPLIT() 與 INDEX() 函數介紹

1. SPLIT(text, separator)

   • 功能：這個函數可以將一個文字字串，根據你指定的「分隔符號」，切割成一個清單（List）。

   • 範例：SPLIT("161-180", "-") 會回傳一個包含兩個元素的清單：{"161", "180"}。

2. INDEX(list, position)

   • 功能：這個函數可以從一個清單中，取出指定「位置」的元素。位置從 1 開始計算。

   • 範例：承上例，INDEX(SPLIT("161-180", "-"), 1) 會回傳清單中的第 1 個元素，也就是文字 "161"。INDEX(..., 2) 則會回傳 "180"。

公式詳解：如何組合函數達成目標

我們的目標是建立一個「複製並新增下一區間」的動作 (Action)。這個動作的核心是使用 LINKTOFORM() 函數來開啟一個新增資料的表單，並預先填入我們計算好的新數值。

假設我們的區間是 20 個數字一組（例如 101-120，121-140），以下是完整的公式：

codeCode

LINKTOFORM(
    "Work_Form", 
    "ProjectID", [ProjectID],
    "WorkNote", CONCATENATE(
        NUMBER(INDEX(SPLIT([WorkNote], "-"), 1)) + 20,
        "-",
        DECIMAL(INDEX(SPLIT([WorkNote], "-"), 2)) + 20
    )
)

讓我們一步步拆解這個公式，特別是 WorkNote 欄位的生成邏輯：

1. SPLIT([WorkNote], "-"):

   • 取得目前這筆資料的 WorkNote 欄位值（例如 "161-180"），並用 - 切割成清單 {"161", "180"}。

2. INDEX(..., 1) 和 INDEX(..., 2):

   • 分別取出清單中的第一個值 "161"（起始數字）和第二個值 "180"（結束數字）。

3. NUMBER(...) + 20 和 DECIMAL(...) + 20:

   • INDEX 取出的值是文字格式，無法直接進行數學運算。我們必須使用 NUMBER() 或 DECIMAL() 將其轉換為數字格式。

   • 將轉換後的起始數字與結束數字各自加上我們的區間值 20。

   • 161 + 20 得到 181。

   • 180 + 20 得到 200。

4. CONCATENATE(...):

   • 最後，使用 CONCATENATE() 函數將新的起始數字 181、分隔符號 "-"、以及新的結束數字 200 重新組合成新的文字字串 "181-200"。

5. LINKTOFORM(...):

   • 這個函數會開啟名為 Work_Form 的表單。

   • "ProjectID", [ProjectID]: 將目前任務的 ProjectID 複製過去。

   • "WorkNote", ...: 將我們剛剛用 CONCATENATE 產生出的新區間 "181-200" 填入到新表單的 WorkNote 欄位中。

AppSheet 實作步驟

1. 前往 Behavior > Actions 頁面。

2. 點擊 + New Action 新增一個動作。

3. Action name: 命名為一個易於辨識的名稱，例如「複製新增(區間20)」。

4. For a record of this table: 選擇你的任務資料表（影片中為 Work）。

5. Do this: 選擇 App: go to another view within this app。

6. Target: 在這裡貼上我們上面詳解的 LINKTOFORM() 公式。

7. Appearance: 你可以設定這個按鈕的圖示 (Action icon) 與顯示名稱 (Display name)。

8. Behavior (選用): 你可以設定顯示條件 (Only if this condition is true)，例如設定只有在任務完成後 (ISNOTBLANK([EndTime])) 才顯示這個複製按鈕，避免重複新增。

設定完成後儲存，你就會在 App 的詳細檢視頁面看到這個新的動作按鈕了。

成果展示

當我們點擊一筆 161-180 的任務，並按下「複製新增(區間20)」按鈕時：

AppSheet 會立刻開啟一個新的任務表單，並自動將 WorkNote 欄位填上 181-200，完美達成我們的目標！

總結

透過 SPLIT、INDEX、NUMBER 與 CONCATENATE 這些函數的靈活組合，我們成功地在 AppSheet 中實現了自動化數字區間遞增的功能。這不僅大幅提升了操作效率，也減少了手動輸入可能發生的錯誤。這個技巧可以廣泛應用於任何需要處理和生成連續編號或區間的場景，是 AppSheet 開發者必學的實用技能之一。