Google Sheets枚举序列缺失键的公式原理解析

这个公式的作用是：找出A列数字序列中，从第一个单元格（A1）的数值到A列最大数值之间，所有缺失的连续数字。下面分步骤拆解公式逻辑：

一、生成完整的连续数字序列（Filter函数的第一个参数） #

row(indirect("A"&A1&":A"+sort(A1:A, 1, 0)))

拆解为几个子步骤：

1. 获取A列的最大数字：sort(A1:A, 1, 0)对A列所有数字按降序排序，取排序后的第一个值就是A列的最大数字；前面加+是把排序结果转换为纯数字，避免数组格式干扰。
2. 拼接单元格范围字符串："A"&A1&":A" + [最大数字] 会生成类似"A1:A10"的字符串（假设A1是1，最大数字是10），代表从A1到对应行的单元格范围。
3. 转换为实际引用并提取行号：indirect(...)把字符串转换成真实的单元格引用，再用row()提取这个范围内所有单元格的行号——最终得到从A1的数值到A列最大数值的完整连续数字序列。

二、筛选出不在原列的数字（Filter函数的第二个参数） #

isna(match(row(indirect("A"&A1&":A"+sort(A1:A, 1, 0))), A1:A,0))

拆解为几个子步骤：

1. 精确匹配数字：match(完整序列, A1:A, 0)会逐个检查完整序列里的每个数字，在A列中做精确匹配（第三个参数0代表精确匹配规则）。如果数字存在于A列，返回它在A列的位置；不存在则返回#N/A。
2. 标记缺失的数字：isna(...)判断match的结果是否为#N/A，如果是就返回TRUE，表示这个数字是A列缺失的。

三、最终筛选逻辑 #

Filter函数会把第一个参数生成的完整序列中，所有满足第二个参数（返回TRUE）的数字筛选出来，这些就是A列序列里缺失的连续数字。

注意：这个公式的前提是A1必须是A列数字序列的最小值，否则会漏掉最小值之前的缺失数字。

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