查找多个连续数字系列中的序列间隙

Question

我有一个按数字顺序排列的数字列表，从 1 开始重复。每个系列中的最后一个数字各不相同。例如：会有 1-7、1-12、1-24 等序列。

我需要一个公式来找出这些系列中缺失的数字。其中一些系列只有一个数字。这是从我按专辑排序的音乐收藏中截取的，目的是找到缺少曲目的专辑。

我希望截图清晰并且解释得足够好。

截屏

编辑：我添加了一个更好的屏幕截图以便澄清。

屏幕截图 2

Answer 1

该解决方案寻找间隙并显示间隙中第一个缺失的项目。

列表中的每个项目必须是：

• 一加上一个项目
• 一个本身

包含列中的数据A， 在B1进入：

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

并向下复制：

这种简单方法的缺点是它只显示了第一的如果连续有几件物品缺失，则表示该物品缺失。

笔记：

如果您使用 Excel 365，则可以获得更完整的输出。对于 Excel 365，公式为：

=IF(OR(A2=A1+1,A2=1),"",SEQUENCE(1,A2-A1-1,A1+1))

在上图中，6 到 9 之间有两个缺失项。两个缺失项都显示在输出中。

Answer 2

：警告：

我的解决方案是基于 OP 对@Bingo 的上述评论的回复，“能够在一列中找到序列间隙的公式就太好了”。

• 单元格 AI2 中的数组（CSE）公式：

  {=IFERROR(SMALL(IF(COUNTIF($AH$2:$AH$5,ROW($1:$24))=0,ROW($1:$24),""),ROW(AH1)),"")}
  

注意：

• 完成配方Ctrl+Shift+Enter并向下填充。

• ROW($1:$24)是可编辑的，它应该开始和结束缺失序列的数量（1 到 24）。

根据需要调整公式中的单元格引用。

Answer 3

如果我理解正确的话，您的屏幕截图可能只显示系列只有一个数字。

我建议您添加辅助列来填充您应该拥有的所有数字。

然后在另一列中输入以下公式：

=VLOOKUP(A1,B:B,1,0)

在下面我的截图中，A 列填充了所有应该编号的数字，而 B 列实际上是现在可用的数字：

例如，C11 显示“N/A”，因此此单元格中缺少的数字为 A 列中的“11”