使用Google Sheets Filter查找数组首个非连续对应缺失值的方法

适用于已排序的整数数组（从逗号分隔字符串拆分得到），支持全连续时返回最后一个值+1的要求，无需依赖实际单元格区域，所有计算在内存中完成。

最优方案（Excel 365/2021及以上版本） #

直接用LET+XMATCH实现，天然支持短路查找，性能最优：

=LET(
  // 定义起始值为数组最小值
  start, MIN(arr),
  // 原数组长度
  len, COUNT(arr),
  // 生成对应长度的完美连续预期序列
  expected_seq, SEQUENCE(len, 1, start),
  // 查找第一个不匹配的位置，找到就返回，不需要遍历全数组
  first_diff_pos, XMATCH(TRUE, arr <> expected_seq),
  // 无差异则返回最大值+1，否则返回预期序列对应位置的缺失值
  IF(ISNA(first_diff_pos), MAX(arr)+1, INDEX(expected_seq, first_diff_pos))
)

如果你的arr是直接从单元格的逗号分隔字符串生成，不需要提前定义名称，可以直接把拆分逻辑放到公式里，比如要处理A1单元格的字符串：

=LET(
  arr, --TEXTSPLIT(A1, ","),
  start, MIN(arr),
  len, COUNT(arr),
  expected_seq, SEQUENCE(len, 1, start),
  first_diff_pos, XMATCH(TRUE, arr <> expected_seq),
  IF(ISNA(first_diff_pos), MAX(arr)+1, INDEX(expected_seq, first_diff_pos))
)

原思路问题说明 #

你之前写的公式用FILTER+COUNTA的逻辑，会先筛选所有匹配的元素再统计数量，无法实现短路查找，数组长度大的时候性能会明显下降，而且公式缺少了+1的偏移，最终取索引的时候会得到错误值。

COUNTIF方案可行性 #

COUNTIF可以实现需求，但性能比上述最优方案差，需要生成从最小值到最大值+1的完整序列再逐个判断是否存在，参考公式：

=MIN(IF(COUNTIF(arr, SEQUENCE(MAX(arr)-MIN(arr)+2, 1, MIN(arr)))=0, SEQUENCE(MAX(arr)-MIN(arr)+2, 1, MIN(arr))))

旧版Excel需要按Ctrl+Shift+Enter作为数组公式输入。

旧版Excel兼容方案 #

如果不支持LET和XMATCH，可以用以下数组公式实现：

=IFERROR(INDEX(SEQUENCE(COUNT(arr),1,MIN(arr)),MATCH(TRUE,arr<>SEQUENCE(COUNT(arr),1,MIN(arr)),0)),MAX(arr)+1)

输入时需要按Ctrl+Shift+Enter确认。

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