☰

从一列的连续数字列表中查找缺失的条目？

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
从一列的连续数字列表中查找缺失的条目？

我有一张 Excel 表，其中包含 66000 个测试用例的结果。或者，至少应该如此...

现在，因为我异步运行测试并不断停止和启动，所以我有先见之明，确保测试数字被放入输出中。现在，使用基于这些测试数字的 Excel 2007 的“删除重复项”功能后，我发现我有 65997 行数据。所以其中三人失踪。

这里的工作是找到丢失的任务编号。

测试编号位于 A 列，按升序排列，并且保证没有重复。其他数据位于其他列，并且这些列必须与测试编号保持一致。

  |    A
--+---------
1 | testNum
2 | 1
3 | 2
4 | 3
5 | ...

假设测试用例太多，无法手动进行搜索，因为我有另一个接近一百万个项目的数据集，我很快就要对其进行类似的工作。

我可以使用 VBA 解决这个问题，但想知道是否存在我所缺少的更直接的解决方案？

答案1

假设您的数据在 A 列并从第 2 行开始，请选择从第 2 行开始的自由列中的范围，然后至少包括你应该得到结果的行数（这很重要：如果您的范围包含的单元格少于您的总序列，则缺失结果的列表将被截断）1.在公式栏中 –不是直接在细胞中！– 插入以下公式：

=IFERROR(
   SMALL(
     IF(
       ISNA(MATCH(ROW(A1:A66000)-1,A2:A66001,0)),
       ROW(A1:A66000)-1,
     ""),
   ROW()-1),
 "")

– 注意这需要写在一行上；此处的缩进只是为了便于阅读。按 将其存储为数组公式Ctrl+Shift+Enter。如果您正确执行此操作，公式将显示在括号中，并且当您尝试编辑数组范围内的单个单元格时，Excel 会发出警告。

对于以下屏幕截图中 A 列中的值，作为数组公式存储的公式B2:B21（20 行，因为完整序列为 1 到 20）将在 B 列中返回以下值：

序列中缺失值的公式化列表的屏幕截图

如您所见，这些是序列中缺失的数字（在屏幕截图中突出显示）。

  1. ROW(A1:AXXXXX)只要引用包含的行数恰好等于您的总序列（您的示例中为 66000 行，因此A1:A66000），那么在选择要应用的范围时您可以酌情宽容。

答案2

在从第 2 行开始的空列中（假设Z）输入一个公式，并向下复制所有使用的行

=A2=A1+1

Z在列中搜索FALSE. 将会在缺失数字后的行中找到测试

您也可以尝试

=IF(A2=A1+1,"",A1+1)

答案3

此解决方案需要两个辅助列。在 B 列中，填写从 1 到 66,000 的连续数字。在 C2（第一行数据）中，输入

=IF(A2=B2,0,1)

在 C3 中输入

=IF(B3=OFFSET(A3,-1*SUM(C$2:C2),0),0,1)

向下填充 C3 至 C66001。

从这里您可以过滤 B:C 以查找 C 列中值为 1 的记录。这将显示缺失的数字。

或者

如果您想要在最后一步有所创新，您可以使用此数组公式并根据需要向下填充：

=IFERROR(SMALL(IF($C$2:C$32<>0,$B$2:$B$32),ROW()-1),"")

使用 Ctrl+Shift+Enter 输入公式。此示例输入第 2 行。您需要调整ROW()-1公式中的 ，使第一个条目等于 1。例如，如果您从 E5 开始列出缺失数字，则需要将其更改为ROW()-4。[感谢 @kopischke 提供此方法：关联]

相关内容