Google Sheets 教學：用 ARRAYFORMULA 和 VLOOKUP 處理不同區域的免運門檻

前言

今天的 Google Sheets 試算表教學，要分享一個實用的技巧：如何使用陣列公式 ARRAYFORMULA 搭配 VLOOKUP，來處理不同購買區域、不同免運費門檻的計算問題。

問題緣由

之前我們可能習慣用 IF 函數來寫陣列公式處理條件判斷。但最近有網友問到，如果條件變多，例如不同的「購買區域」會對應到不同的「購買金額免運費標準」，該怎麼解決？如果用 IF 一層層包下去會變得很複雜。這時候，VLOOKUP 加上 ARRAYFORMULA 就是一個更優雅的解決方案。

應用情境

假設我們有一張訂單記錄表（例如 工作表1），包含欄位：品名、金額、區域、基本運費、總金額。
同時，我們有另一張表（例如 工作表2）定義了不同「區域」的「免運費金額門檻」。

• 工作表1 (訂單記錄):

  • A欄: 品名

  • B欄: 金額 (例如：300)

  • C欄: 區域 (例如：甲)

  • D欄: 基本運費 (例如：50)

  • E欄: 總金額 (這是我們要計算的)

• 工作表2 (免運門檻):

  • A欄: 區域 (甲, 乙, 丙...)

  • B欄: 免運費金額 (400, 1000, 600...)

目標： 我們希望在 工作表1 的 E欄（總金額）自動計算：如果該筆訂單的「金額」(B欄) 小於其「區域」(C欄) 在 工作表2 中對應的「免運費金額」門檻，則「總金額」 = 「金額」 + 「基本運費」；反之，若達到或超過免運門檻，則「總金額」 = 「金額」。並且希望這個公式能自動套用到所有列，包含未來新增的資料。

核心公式解析

我們可以在 工作表1 的 E2 儲存格（假設標題在第一列）輸入以下公式：

=ARRAYFORMULA(
  IF(B2:B="", "", 
    IF(B2:B < VLOOKUP(C2:C, '工作表2'!$A$1:$B$5, 2, 0), 
       B2:B + D2:D, 
       B2:B
    )
  )
)

Excel

公式說明：

1. ARRAYFORMULA(...): 這是最外層的陣列公式宣告，讓裡面的公式能自動擴展套用到指定的範圍。

2. IF(B2:B="", "", ...): 這是第一層 IF，用來處理空白列。它檢查 B欄（金額）從第二列開始 (B2:B) 是否為空。如果是空的，就傳回空值 ""，避免 #N/A 錯誤；如果不為空，則執行後面的主要邏輯。

3. IF(B2:B < VLOOKUP(...), B2:B + D2:D, B2:B): 這是核心的判斷邏輯。

   • VLOOKUP(C2:C, '工作表2'!$A$1:$B$5, 2, 0): 這部分是關鍵。

     • C2:C: 我們要查詢的值是 C欄 的「區域」。

     • '工作表2'!$A$1:$B$5: 這是我們的查詢範圍，也就是定義免運門檻的表格。記得用 $ 鎖定範圍，以免公式下拉時跑掉 (雖然 ARRAYFORMULA 通常不需要下拉)。A1:B5 需根據你的實際表格大小調整。

     • 2: 我們要傳回查詢範圍中的第 2 欄，也就是「免運費金額」。

     • 0: 代表需要精確比對區域名稱。

     • 這個 VLOOKUP 會為 C欄 的每一列找到對應的免運門檻金額。

   • B2:B < VLOOKUP(...): 比較 B欄 的實際「金額」是否 小於 查到的「免運費金額」門檻。

   • B2:B + D2:D: 如果條件成立 (金額 < 門檻)，則計算「金額」加上「基本運費」。

   • B2:B: 如果條件不成立 (金額 >= 門檻)，則直接傳回「金額」（表示免運）。

4. 範圍注意: B2:B, C2:C, D2:D 這些開放式範圍 (B2:B 指 B欄第2列到底) 會讓公式自動處理所有列。如果你只想處理固定範圍，例如到第10列，可以寫成 B2:B10, C2:C10, D2:D10。重點是所有參與計算的範圍列數要一致。

除錯小提示

• 撰寫 ARRAYFORMULA 時，如果公式複雜，可以先不加 ARRAYFORMULA，針對單一列（例如第2列）寫出正確的 IF(B2 < VLOOKUP(C2, ...), B2+D2, B2) 公式。

• 確認單列公式無誤後，再將所有單列參照 (B2, C2, D2) 改成範圍參照 (B2:B, C2:C, D2:D 或 B2:B10, C2:C10, D2:D10)，最後再包上 ARRAYFORMULA()。

• 用影片中提到的「剪下貼上」方式逐步建構或檢查公式，有助於釐清括號和參數是否正確。

• VLOOKUP 的查詢範圍 ('工作表2'!$A$1:$B$5) 務必正確且建議鎖定。

結論

透過 ARRAYFORMULA, IF, VLOOKUP 的組合，我們可以用一個簡潔的公式，優雅地解決根據不同條件（區域）查詢不同標準（免運門檻）並進行計算的問題，同時實現了公式自動套用至新資料列的便利性。希望這個教學對你有幫助！