表單回覆表自動填滿：編號、單價、金額、狀態一次搞定

如果你用 Google 表單收訂單，回覆工作表只會有「時間戳記、品項、杯數」這幾欄，編號、單價、金額還是要自己補。今天就用 ARRAYFORMULA 在回覆表旁邊做四個自動欄位，新回覆進來就自己填好。

ARRAYFORMULA 本身的寫法，包含 A2:A 這種開放式範圍和 IF(A2:A="", "", ...) 擋空白，我在 ARRAYFORMULA 入門 講過，這篇會直接用。

用 Excel 的人：本篇用到的 XLOOKUP、IFERROR、TEXT、ROW，在 Excel 365／2021／2024 都可以用，而且動態陣列會自動溢出，不需要 ARRAYFORMULA；IFNA 在 Excel 2013 以後也有。開放式範圍 A2:A 則是 Google 試算表的寫法。

資料長什麼樣

我做了一份手搖飲的訂購表單，回覆工作表目前有 7 筆。A 到 C 欄是表單寫進來的：

D 到 G 是我要做的四欄，標題分別是「編號」「單價」「金額」「狀態」，內容先留空。I、J 欄放價目表：

最後一筆「芋頭鮮奶」價目表裡還沒有，我故意留著，後面會看到它怎麼被處理。

如果價目表放在別的工作表，把 I2:J 改成 價目表!A2:B 這類寫法就可以，我這邊為了排版放在同一張。

自動編號

編號想要 Q-001、Q-002 這樣的格式。在 D2 輸入：

• A2:A=""：時間戳記是空白，代表還沒有這筆回覆。表單一定會填時間戳記，所以我拿它當判斷欄。
• ROW(A2:A)-1：ROW 回傳列號，第 2 列減 1 就是 1。
• TEXT(…, "000")：補成三位數，1 變成 001。

來看看結果：

編號是由列號算出來的，所以如果你把某一列整列刪掉，下面的編號會往前補。公式所在的第 2 列也不要刪，刪掉公式就跟著不見了。需要編號永遠不變的話，這招就不適合。

查價目表帶出單價

單價要依品項去 I:J 欄查。在 E2 輸入：

• B2:B：查詢值給整欄品項，VLOOKUP 會一個一個查。
• I2:J：價目表，也是開放式，之後價目表多一個品項就會查得到。
• 2：回傳價目表的第 2 欄，也就是單價。
• 0：完全比對。
• IFERROR(…, "查無品項")：查不到的時候改顯示文字，不要整欄一堆 #N/A。

結果是 45、55、35、40、45、55，第 8 列的「芋頭鮮奶」是「查無品項」。

如果你習慣 XLOOKUP，也可以這樣寫：

結果一樣。我個人比較常用 XLOOKUP，因為不用數第幾欄，找不到的值也直接是參數，不用另外包 IFERROR。XLOOKUP 的用法在 XLOOKUP 基礎。

一次回傳三欄：索引用橫向陣列

延伸方格子 ARRAYFORMULA 多欄查表，這次只用合成產品代碼，不用姓名電話。另開空白表，A1:D3 放：

F2:F5 依序填 P2、P1、留空、P9，H2 貼下式，先清空 H2:J5：

{2,3,4} 的逗號把三個索引排成一列；查詢值是一欄，組合後就是多列三欄。索引是相對於 A2:D3 的欄位，不是工作表欄字母。若改成 {4,2,3}，P2 會回 60、40、50，P1 會回 30、10、20。

FALSE 表示完全比對；來源代碼重複時，不會自動把所有同代碼列都列出，所以代碼應先有唯一性規則。IFNA 只處理找不到，不把範圍選錯等所有錯誤都藏成「查無代碼」。若習慣分開指定查詢欄與回傳欄，可以再看 XLOOKUP，不要因為少一個函式就認定某種寫法一定較快。

算金額

金額是杯數乘單價。不過 E 欄現在有一列是文字「查無品項」，直接 C2:C*E2:E 的話，那一列會變成 #VALUE!。所以我先用 ISNUMBER 檢查單價是不是數字，不是就顯示「待確認」。在 F2 輸入：

結果是 135、110、140、40、270、55，第 8 列是「待確認」。

這邊用到的是另一個 ARRAYFORMULA（E2）的結果，也就是說公式可以接著用前一欄的公式結果，E 欄多一列，F 欄跟著多一列。

用巢狀 IF 標狀態

我想把金額大於等於 200 的標成「大單」，100 到 199 標成「一般」，更少的是「小單」，待確認的原樣標「待確認」。條件有好幾層，我選巢狀 IF。在 G2 輸入：

為什麼用巢狀 IF、不用 IFS？巢狀 IF 寫起來比較長，但每一層都是熟悉的單格寫法，容易讀。巢狀 IF 的讀法可以看 巢狀 IF 與 AND、OR。

這邊的順序有講究：先判斷是不是數字，再比大小。因為文字「待確認」拿來跟 200 比較，在試算表裡不會報錯，而且可能被判成比數字大，那一列就會被標成「大單」。

來看看結果：

新回覆進來會怎樣

現在 A9 多了一筆：蘋果綠茶 5 杯。D9 到 G9 不用動，會自己變成 Q-008、40、200、大單。第 10 列沒有資料，四欄都是空白。

要維護的地方只有兩個：價目表多一個品項，就接在 I、J 欄最後一列；要改規則（例如大單的門檻），改 G2 那一格的公式就好。

這四個公式各自放在第 2 列，D2 到 G2 以下不要手動輸入東西，不然結果會被擋住，顯示 #REF!。

新回覆進來，四欄都會自己跟上。試著把自己的表單回覆表套進去看看吧！

我是喜特先生，Mr. Sheet，我們下個教學見！