Google Sheets：如何修改公式映射含‘Submission time:’的单元格并返回唯一值？

现有Google Sheets公式如下：

=MAP(FILTER(A:A, REGEXMATCH(A:A, "Submission time:")), LAMBDA(Σ, IF(Σ = "", , IFNA(INDEX(A:A, XMATCH(Σ, A:A)-2)))))

但数据中包含“Submission time:”的行并非唯一，示例数据如下：

Submission time: 2024-01-09 07:41:37
Submission time: 2024-01-09 07:41:37
Submission time: 2024-01-09 07:41:37
Submission time: 2024-01-09 07:41:37
Submission time: 2023-12-21 18:56:02
Submission time: 2023-12-21 18:56:02
Submission time: 2023-12-21 18:56:02
Submission time: 2023-12-21 18:56:02

要求每个包含“Submission time:”的行对应的返回值唯一，需修改公式实现正确映射。

原公式的核心问题是XMATCH(Σ, A:A)只会返回第一个匹配文本的行号，导致所有重复的“Submission time:”行都返回同一个结果。要实现每行返回唯一值，需要直接定位每个匹配行的实际行号，而非依赖文本匹配。

修正后的公式 #

=MAP(FILTER(ROW(A:A), REGEXMATCH(A:A, "Submission time:")), LAMBDA(row_num, IFNA(INDEX(A:A, row_num - 2))))

公式解析 #

• FILTER(ROW(A:A), REGEXMATCH(A:A, "Submission time:"))：筛选出所有包含目标文本的单元格的行号，确保每个匹配行的位置唯一。
• MAP(..., LAMBDA(row_num, ...))：遍历每个行号，针对单个行号执行逻辑。
• INDEX(A:A, row_num - 2)：根据当前匹配行的行号，直接获取其上方第2行的内容，每个重复行都会对应自身的目标行，返回值自然唯一。
• IFNA()：处理边界异常（比如前两行出现目标文本时避免报错）。

如果需要保留原公式的空值判断逻辑，可调整为：

=MAP(FILTER(ROW(A:A), REGEXMATCH(A:A, "Submission time:")), LAMBDA(row_num, IF(INDEX(A:A, row_num) = "", , IFNA(INDEX(A:A, row_num - 2)))))

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