在列中搜索一组值并列出缺失值

Question

我有一列 45 个空格，其中包含字母和数字。我想编写一个公式，在该列中搜索 1-10 的值，并列出找到多次或未找到的值。我还没有找到使用现有公式来执行此操作的方法。即给定以下列...

1
3
6
2
8
7
8
10

结果 [4,5,8,9]

Answer 1

使用以下公式：

=IFERROR(SMALL(IF(COUNTIF(SourceRange,ROW($1:$10))=1,"",ROW($1:$10)),ROW()),"")

• COUNTIF(SourceRange,ROW($1:$10))- 检查源范围（公式中的命名范围）中数字 1-10 的出现次数
• IF(COUNTIF(...)=1,"",ROW($1:$10))- 为 1-10 的数字创建一个列表，对于范围内出现一次的数字，列表包含空字符串，对于所有其他情况（未出现或出现多次），列表包含数字本身
• SMALL(IF(...),ROW())- 将列表中的数字输入到一列中
• =IFERROR(SMALL(...),"")- 用空白而不是错误填充值后的范围

将此公式放在任意列中，从第一行开始，至少向下填充十行以获得正确的结果，以防需要显示所有数字。

这是一个数组公式，您需要用CTRL+ SHIFT+输入它ENTER。