如何在动态更新的Excel表单中自动统计受访者提交次数？

针对Forms实时更新导致Excel行数动态变化的问题，推荐以下两种无需手动复制公式的永久生效方案：

方案1：动态数组公式（Excel 365/2021及以上版本） #

在结果列的首个单元格（例如E2）输入以下公式，单个公式即可自动填充所有现有行，并在表单新增数据时自动扩展计算：

=BYROW(D2:INDEX(D:D,COUNTA(D:D)),LAMBDA(x,COUNTIF(D$2:INDEX(D:D,COUNTA(D:D)),x)))

公式说明： #

• D2:INDEX(D:D,COUNTA(D:D))：动态定位D列从第2行到最后一个非空单元格的有效数据范围，自动适配Forms新增的响应行
• BYROW + LAMBDA：遍历动态范围内的每个邮箱地址，对每个地址统计其在整个有效范围内的出现次数，即该受访者的累计提交次数

输入公式后，Excel会自动将结果“溢出”到下方所有对应行，后续新增表单数据时无需任何操作，公式会自动更新计算。

方案2：Excel表格（兼容全版本Excel） #

如果使用旧版Excel（不支持动态数组），可以将数据区域转换为Excel表格实现自动扩展：

1. 选中包含Forms响应的整个数据区域（包括表头），按下Ctrl+T创建表格，勾选“我的表格有标题”
2. 在表格的新增列（例如表头设为“累计提交次数”）中输入公式：

=COUNTIF([受访者ID-邮箱],[@[受访者ID-邮箱]])

3. 按下回车后，表格会自动将公式应用到所有现有行，后续Forms新增数据时，表格会自动扩展并将公式同步到新行

注意事项： #

• 确保D列（受访者邮箱列）没有无关空行，否则COUNTA函数会误判有效数据范围
• 动态数组方案无需维护，是最省心的永久生效方式，优先推荐使用支持动态数组的Excel版本

内容的提问来源于stack exchange，提问作者Kris