【Google 教學】告別手動更新！讓 Google 表單訂單金額在試算表中自動計算

一、 前言

• 痛點引入： 使用 Google 表單製作訂購單或報名表很方便，但每次收到新回覆，都要手動到試算表更新總金額公式嗎？

• 問題深化： Google 表單新增回覆時常「插入新列」，導致手動下拉的公式失效或錯位，需要不斷重新調整。

• 解決方案預告： 介紹 Google 試算表的 ARRAYFORMULA 函數，只需設定一次，即可自動計算所有新進訂單的總額。

• 本文目標： 教學如何使用 ARRAYFORMULA 解決自動計算問題，省時又不易出錯。

二、 為何手動拉公式會失敗？惱人的痛點說明

• 常見做法： 在試算表「總價」欄位輸入公式（如 =C2*D2），然後手動向下拖曳填滿。

• 失敗原因：

  • Google 表單提交新回覆時，預設行為是插入新的一列，而非附加在最下方。

  • 插入列會導致原先已下拉填滿的公式列被推擠下去，新插入的列沒有公式，無法計算。

  • 使用者需要不斷檢查並重新複製貼上公式。

• 額外困擾： 預先下拉太多公式，沒有資料的儲存格會顯示多餘的「0」。

三、 解決方案：陣列函式 ARRAYFORMULA 登場！

• 函數介紹： 說明 ARRAYFORMULA 的基本功能 - 將單一公式應用於整個範圍或陣列。

• 核心優勢：

  • 只需在一個儲存格設定公式。

  • 自動應用到指定範圍內的所有列（包含未來新增的列）。

  • 完美解決因「插入列」導致公式失效的問題。

• 與 Excel 差異： Google 試算表使用 ARRAYFORMULA() 包覆即可，比 Excel 按 Ctrl+Shift+Enter 更直觀。

四、 一步步教你做：設定 ARRAYFORMULA 自動計算

• 情境假設：

  • Google 試算表 C 欄為「價格」（資料從 C2 開始）。

  • D 欄為「數量」（資料從 D2 開始）。

  • E 欄為要自動計算的「總價」。

• 步驟 1：清空目標欄位

  • 強調必須先刪除 E 欄（E2 及以下）所有手動輸入的公式或數值，否則 ARRAYFORMULA 會因無法擴展而報錯 (#REF!)。

• 步驟 2：輸入基本 ARRAYFORMULA 公式

  • 在儲存格 E2 輸入：=ARRAYFORMULA(C2:C * D2:D)

  • 解釋 C2:C 和 D2:D 代表從第 2 列開始的整個 C 欄和 D 欄。

• 步驟 3：觀察結果與問題

  • 公式會自動計算有資料的列。

  • 點出問題：沒有資料的列會顯示 0。

五、 進階處理：結合 IF 函數，消除多餘的 0

• 目標： 讓沒有價格和數量的列保持空白，而非顯示 0。

• 方法： 在 ARRAYFORMULA 內加入 IF 判斷式。

• 步驟 1：修改公式

  • 在儲存格 E2 輸入改良版公式：=ARRAYFORMULA(IF(C2:C*D2:D>0, C2:C*D2:D, ""))

  • 解釋公式邏輯：

    • IF(C2:C*D2:D>0, ... , ...)：判斷「價格 * 數量」的結果是否大於 0。

    • C2:C*D2:D：如果大於 0，則顯示計算結果。

    • ""：如果不成立（例如 C 或 D 欄為空，導致結果為 0 或錯誤），則顯示空白（由兩個雙引號表示）。

  • (可選) 提及另一種判斷方式：用 ISBLANK(C2:C) 或 C2:C="" 判斷來源儲存格是否為空。

• 步驟 2：驗證效果

  • 觀察試算表，確認沒有資料的列不再顯示 0。

  • 可再次提交 Google 表單新回覆，驗證總價是否自動計算且格式正確。

六、 總結

• 再次強調優點： 使用 ARRAYFORMULA (+ IF) 只需設定一次公式，即可一勞永逸地自動計算 Google 表單回覆的總價。

• 效益： 大幅提升效率，避免手動錯誤，表格更簡潔。

• 鼓勵行動： 鼓勵讀者立即應用此技巧到自己的 Google 表單與試算表中。

---