如何在表格中使用公式和 Google 表單回复

已發表: 2022-06-16

當人們提交您的 Google 表單時，會在存儲表單響應的 Google 表格中插入一個新行。 此電子表格行包含一個時間戳列，即提交表單的實際日期，工作表中的其他列包含所有用戶的答案，每列一個。

您可以擴展 Google 表單工作表，使其也包含公式字段，並且只要 Google 表單向工作表添加新行，就會自動計算單元格值。 例如：

  • 您可以有一個自動編號公式，為每個表單響應分配一個自動遞增但順序的 ID。 當您使用 Google 表單開具發票時，它會很有用。
  • 對於客戶訂單，可以在 Google Sheets 中編寫公式，根據商品選擇、國家（稅率不同）和表單中選擇的數量計算總金額。
  • 對於酒店預訂表格，一個公式可以根據客戶在谷歌表格中填寫的入住和退房日期自動計算房間租金。
  • 對於測驗，老師可以通過將表格中輸入的值與實際答案匹配並分配分數來自動計算學生的最終分數。
  • 如果用戶提交了多個表單，則公式可以幫助您確定用戶在提交表單後立即輸入的條目總數。

Autofill Google Sheets Formulas

Google 表格的 Google 表格公式

在本分步指南中，您將了解如何將公式添加到與 Google 表單關聯的 Google 表格中。 提交新響應時，將自動計算響應行中相應的單元格值。

為了更好地了解我們正在努力實現的目標，請打開此 Google 表單並提交回复。 接下來，打開此 Google 表格，您會在新的一行中找到您的回复。 FK 列使用公式自動填充。

下面的所有示例都將使用 Google Sheets 的 ArrayFormula 函數，儘管其中一些示例也可以使用FILTER函數編寫。

具有唯一 ID 的自動編號表單響應

打開存儲表單響應的 Google 表格，轉到第一個空列並將以下公式複制粘貼到空列的第 1 行。

 =ArrayFormula( IFS( ROW(A:A)=1, "Invoice ID", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, LEFT(CONCAT(REPT("0",5), ROW(A:A) -1),6) ) )

ROW()函數返回當前響應行的行號。 它為 Invoice Column 中的第一行返回1 ，因此我們在第一行中設置列標題。 對於後續行，如果該行的第一列（通常為 Timestamp）不為空，則會自動生成發票 ID。

ID 將類似於00001 、 00002等。 您只需將公式放置在列的第一行，它就會自動填充列中的所有其他行。

IFERROR函數如果不是錯誤值則返回第一個參數，否則返回第二個參數（如果存在），或者如果第二個參數不存在則返回空白。 所以在這種情況下1/0是一個錯誤，因此它總是返回一個空白值。

谷歌表單的日期計算公式

您的 Google 表單有兩個日期字段 - 入住日期和退房日期。 酒店價格可能會在每個季節有所不同，因此您在 Google 表格中有一個單獨的表格來維護每月的房間租金。

Google Sheets Date Formula

Google 表格中的 C 列保存入住日期的響應，而 D 列存儲退房日期。

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IF(NOT(ISBLANK(A:A)), (D:D - C:C) * VLOOKUP(MONTH(D:D), 'Room Rates'!$B$2:$C$13,2, TRUE), "" ) ) )

該公式使用VLOOKUP獲取表單響應中指定的旅行日期的房價，然後通過將房租乘以住宿時長來計算房租。

同樣的公式也可以用IFS代替VLOOKUP來寫

=ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IFS(ISBLANK(C:C), "", MONTH(C:C) < 2, 299, MONTH(C:C) < 5, 499, MONTH(C:C) < 9, 699, TRUE, 199 ) ) )

根據發票金額計算稅額

在這種方法中，我們將使用FILTER函數，這可能會導致比使用IF函數更簡單的公式。 缺點是您必須在第 1 行寫入列標題並將公式粘貼到第 2 行（因此應該存在一個表單響應以使公式起作用）。

 =ArrayFormula(FILTER(E2:E, E2:E<>"")*1.35)

在這裡，我們對發票價值應用 35% 的稅，此公式應添加到標題為“稅額”列的第 2 行，如屏幕截圖所示。

在 Google 表單中分配測驗分數

哪個城市被稱為大蘋果？ 這是 Google 表單中的一個簡答題，因此學生可以給出紐約、紐約市、紐約市等回答，而且他們仍然是正確的。 老師必須給正確答案打 10 分。

 =ArrayFormula( IF(ROW(A:A) = 1, "Quiz Score", IFS( ISBLANK(A:A), "", REGEXMATCH(LOWER({B:B}), "new\s?york"), 10, {B:B} = "NYC", 10, TRUE, 0 ) ) )

在這個公式中，我們使用了類似於編程中的IF THEN語句的IFS函數。 我們正在使用REGEXMATCH使用正則表達式一次性匹配New York, New York, newyork等值。

如果沒有任何條件為真，則IFS函數返回NA ，因此我們在末尾添加一個TRUE檢查，如果前面的條件都不匹配並返回0 ，則該檢查將始終被評估為true 。

提取表單受訪者的名字

如果您有要求用戶填寫全名的表單字段，您可以使用 Google 表格功能從全名中提取名字，並使用該字段發送個性化電子郵件。

 =ArrayFormula( IFS( ROW(A:A)=1, "First Name", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, PROPER(REGEXEXTRACT(B:B, "^[^\s+]+")) ) )

我們在這裡使用RegexExtract方法來獲取名稱字段中第一個空格之前的字符串。 PROPER函數將把名字的第一個字母大寫，以防用戶以小寫形式輸入他們的名字。

查找重複的 Google 表單提交

如果您的 Google 表單是收集電子郵件地址，您可以使用該字段快速檢測同一用戶多次提交的回复。

 =ArrayFormula( IFS( ROW(A:A)=1, "Is Duplicate Entry?", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, IF(COUNTIF(B:B, B:B) > 1, "YES", "") ) )

假設 B 列存儲表單回复者的電子郵件地址，我們可以使用COUNTIF函數快速標記回復電子表格中的重複條目。 您還可以在表格中使用條件格式來突出顯示可能重複條目的行。

帶有自動填充值的電子郵件表單響應

您可以使用 Document Studio 自動向表單回复者發送電子郵件。 在 Google 表格自動填充公式值後發送電子郵件。 原始表單響應和計算值也可以包含在生成的 PDF 文檔中。