google-sheets - Google 表格中带有 COUNTIF 的 ARRAYFORMULA

Question

我们有一个 Google 表单，用户将使用该表单提交他们的调查。可能会发生同一用户可能多次提交调查，此时我们只想考虑最新的回复并忽略所有以前的回复。该工作表有两个关键列（时间戳和电子邮件），我们创建了一个附加列“Accepted”，预计有两个值“0”或“1”，其中“0”的响应不被接受，“1”的响应被接受（因为这是最新提交的回复）。

Timestamp | Email | Accepted
2020/06/01 00:05:22 | [e-mail] | 0
2020/06/01 00:35:06 | [e-mail] | 1
2020/06/01 01:45:51 | [e-mail] | 1
2020/06/01 02:03:40 | [e-mail] | 0
2020/06/01 03:44:01 | [e-mail] | 1

Google 表格的第一行有标题。因此，在 C2 中输入的公式为：

=IF(COUNTIF($B$1:B2,B2)=COUNTIF($B$1:B,B2),1,0)

拖动到所有行时效果很好。但是，由于我们希望将 C 列更新为每个新响应，因此尝试使用 ARRAYFORMULA，如下所示：

=ARRAYFORMULA(IF(COUNTIF($B$1:B2,B2:B)=COUNTIF($B$1:B,B2:B),1,0))

它将 0 返回到所有行。示例 Google 表格的链接如下： https ://docs.google.com/spreadsheets/d/1qAdOIV_ZioBw41UR_WEBR_Ean5tQ0Yd_f6oLRi83fOk/edit?usp=sharing

请帮忙。提前致谢 ：）

标签： google-sheetsgoogle-sheets-formula

Answer 1

您可以通过在 Google Apps 脚本中创建的自定义函数来完成此操作。为此，请按照下列步骤操作：

• 在您的电子表格中，选择工具 > 脚本编辑器以打开绑定到您的文件的脚本。
• 在脚本编辑器中复制此函数，并保存项目：

function GETLAST(input) {
  input = input.filter(row => row[1] !== ""); // Remove empty elements
  return input.map(row => { // Iterate through rows
    const responderRows = input.filter(el => el[1] === row[1]); // Filter by email address
    const isLast = responderRows.every(el => el[0] <= row[0]); // Check if it's last email
    return isLast ? 1 : 0
  });
}

• 现在，如果您返回电子表格，您可以像使用任何内置功能一样使用此功能。您只需提供适当的范围（在本例中为A2:B），如您在此处看到的：

笔记：

• 此功能使用提交时间（列A）来查找每个电子邮件地址的最后一次提交。行顺序无关紧要。

参考：

• Google 表格中的自定义函数