如何在表格中使用公式和 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 文档中。