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

【Google 教學】讓 Google 表單自動計算價格數量總額？用 ARRAYFORMULA 一招搞定！

 您是否也常用 Google 表單來製作產品訂購單或報名表呢？在收集回應後，我們常常需要在連結的 Google 試算表中計算每個品項的總金額（例如：價格 × 數量）。

惱人的問題：新回應打亂公式？

如果您直接在試算表的總金額欄位（假設是 E 欄）輸入公式，例如在 E2 輸入 =C2*D2（C 欄是價格，D 欄是數量），然後將公式往下複製填滿。您會發現一個問題：

每當有新的表單回應提交時，Google 試算表通常會「插入新的一列」來存放新資料，而不是直接寫在最後一列空白列。

這會導致您原本複製好的公式被推往下移，而新插入的那一列（例如新的第 3 列）卻是空白、沒有公式的！您就必須手動再去複製或填滿公式，非常麻煩且容易出錯。

神奇解法：陣列函數 ARRAYFORMULA

為了解決這個困擾，Google 試算表提供了一個強大的武器：ARRAYFORMULA 陣列函數！

ARRAYFORMULA 的厲害之處在於，它允許您只在一個儲存格輸入公式，就能將該公式的計算結果自動擴展應用到指定的整個範圍（多個列或欄）。

這樣一來，就算表單提交導致試算表插入了新的列，因為我們的公式是作用於整個範圍（例如 C2:C 和 D2:D），所以新列的資料也會被自動納入計算，無需手動調整！

與 Excel 的比較： 在 Excel 中，要使用陣列公式，輸入完公式後必須按下 Ctrl + Shift + Enter 才能生效。但在 Google 試算表中，只需要直接使用 ARRAYFORMULA 函數包裝您的公式即可，使用上更為直觀方便。

如何操作？

讓我們來看看實際步驟：

1. 建立 Google 表單與試算表：

   • 建立您的訂購表單，包含「產品名稱」、「價格」、「數量」等欄位。

   • 在「回應」分頁中，點擊綠色的試算表圖示，建立或連結一個 Google 試算表來存放回應。

2. 找出對應欄位：

   • 在試算表中，確認「價格」和「數量」分別在哪個欄位。假設「價格」在 C 欄，「數量」在 D 欄。時間戳記通常在 A 欄，產品名稱在 B 欄。

3. 輸入 ARRAYFORMULA 公式：

   • 我們希望在 E 欄計算總金額。請點擊 E1 儲存格（標題列），輸入欄位標題，例如「總金額」。

   • 接著，點擊 E2 儲存格（第一筆資料列對應的總金額位置），輸入以下公式：

     =ARRAYFORMULA(C2:C * D2:D)

     Excel

   • 公式說明：

     • ARRAYFORMULA(...)：表示這是一個陣列公式。

     • C2:C：代表從 C2 儲存格開始，一直到 C 欄的「最後一列」。這會自動抓取 C 欄所有價格資料。

     • D2:D：代表從 D2 儲存格開始，一直到 D 欄的「最後一列」。這會自動抓取 D 欄所有數量資料。

     • *：執行乘法運算。

   • 輸入完畢後直接按下 Enter。您會看到從 E2 開始，每一列的總金額都自動計算出來了（如果 C、D 欄有數字的話）。

4. （進階）處理空白列的 0 值：

   • 您可能會發現，還沒有資料的列，總金額會顯示為 0。如果覺得礙眼，可以用 IF 函數來判斷：只有當計算結果大於 0 時才顯示，否則顯示空白。

   • 修改 E2 的公式如下：

     =ARRAYFORMULA(IF(C2:C*D2:D>0, C2:C*D2:D, ""))

     Excel

   • 公式說明：

     • IF(C2:C*D2:D>0, ... , "")：判斷 C 欄乘以 D 欄的結果是否大於 0。

     • 如果是 (TRUE)，則顯示 C2:C*D2:D 的計算結果。

     • 如果否 (FALSE，例如等於 0 或錯誤)，則顯示 ""（空字串，也就是看起來空白）。

5. 測試看看：

   • 現在，回到您的 Google 表單，提交幾筆新的訂購資料。

   • 再回到試算表查看，您會發現新的資料列，其 E 欄的總金額已經自動計算完成，而且舊資料的計算也不受影響！下方的空白列也不會再顯示惱人的 0 了。

重要提醒： 在輸入 ARRAYFORMULA 公式之前，請確保目標欄位（例如 E 欄從 E3 開始往下）是「空的」，沒有手動輸入的舊公式或數值，否則 ARRAYFORMULA 可能會因為無法擴展結果而顯示 #REF! 錯誤。若出現此錯誤，請先將目標欄位下方可能存在的資料清除。

結語

利用 ARRAYFORMULA，就能讓您的 Google 表單回應試算表變得更加自動化，省去許多手動計算和調整公式的麻煩。這個技巧非常實用，特別適合需要處理大量訂單或報名資料的朋友。趕快學起來，讓您的工作更有效率吧！

---

希望這篇文章對您有幫助！如果您有任何問題，歡迎留言交流。