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

---

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