如何在Google Sheets中使用Arrayformula和Match函数来使用动态范围？

Question

在列A中，我有一组电子邮件地址；在列B中，我想找到重复项，所以如果一个电子邮件地址在A中出现过，在B中就会触发一个标记。 我正在使用这个公式，它非常有效：=if(isna(match(a3,$A$2:A2,0)),"New","Duplicate")

请注意，当我拖动此公式时，$A$2会保持不变以便范围增长（例如对于单元格B51，范围将为$A$2:A50）。

我的问题是，由于列A会自动更新（例如添加新的电子邮件地址），因此我希望列B也能自动更新。我尝试使用Arrayformula但无法弄清楚：( 我尝试了以下两种方法: =arrayformula(if(isna(match(A3:A,$A$2:A2,0)),"New","Duplicate")) 和 =arrayformula(if(isna(match(A3:A,$A$2:A2:A,0)),"New","Duplicate")) 但它们都无效。

这里有一个包含示例和我（失败的）解决尝试的电子表格： https://docs.google.com/spreadsheets/d/1N3pFPnT452FmWa9w8EkYpIq-ZnivjoCzt5ORrNEKgLQ/edit#gid=0

- Tom

---

为什么在更新电子邮件列表时不删除重复项？ - Cooper

这是用于报告的，所以我需要知道有多少封新邮件和有多少封重复的邮件。 - Tom

Answer 1

请尝试： =ArrayFormula(IFERROR(if(VLOOKUP(A2:A,{A2:A,ROW(A2:A)},2,)=ROW(A2:A), "新的", "重复的"))) 如果匹配行=当前行，则为“新的”，否则为“重复的”。

我使用了vlookup，因为它可以与ArrayFormula一起使用。

- Max Makhrov

Answer 2

您可以使用Match函数来确定当前电子邮件地址的第一个匹配项是否在数组的当前行之前。

=arrayformula(if(match(A2:index(A2:A,COUNTA(A2:A)),A2:index(A2:A,COUNTA(A2:A)),0)<row(A2:index(A2:A,COUNTA(A2:A)))-1,"Duplicate","New"))

- Tom Sharpe

---

运行得很好，但最近我发现了一些奇怪的问题，我在3个不同的工作表上使用它，在其中一个表格中它表现如预期，但在另外两个表格中，公式无法完全更新至底部。其中一个情况下，它仅适用于15743封电子邮件中的15566封，而在另一个情况下，它仅适用于18243封电子邮件中的18063封。这不是文件大小的问题，因为在另一个文件中，它可以处理超过25000封电子邮件。你有什么建议吗？ - Tom

可能是因为电子邮件列表中有一些空单元格。我正在使用COUNTA来查看数据结束的位置，以尝试使其更有效，但仅在没有空单元格时才起作用。您可以简化它，并在我放置A2：INDEX ...的地方只需放置A2：A，但不确定对性能会产生什么影响。 - Tom Sharpe

Answer 3

你也可以使用countif函数来查看当前行上方的所有内容：

=IF(countif($A$1:A2,A2)>1,"重复","新")

- Aurielle Perlmann

---

谢谢，这是一个非常优雅的方法，但当我添加arrayformula时它会出错（如果一个电子邮件重复两次，那么第一个也会被标记为“重复”） =arrayformula(IF(countif($A$1:A2:A,A2:A)>1,"重复","新")) - Tom