如何编写Google Sheets公式查找缺失序列号（含多值场景）

一、基础场景（PART 1） #

给定Google Sheets中包含STUDIO、FIN YEAR、NUMBER三列的数据集，需编写公式在单个单元格生成指定格式的缺失序列号输出，规则如下：

• 序列号按每个STUDIO的每个FIN YEAR从1开始计数，忽略重复值
• 仅显示存在缺失序列号的STUDIO及对应财年，无缺失则不展示

基础场景实现公式 #

=TEXTJOIN(CHAR(10), TRUE, ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(FILTER(A2:C, NOT(ISBLANK(A2:A)))), {UNIQUE(FILTER(A2:B, NOT(ISBLANK(A2:A)))), BYROW(UNIQUE(FILTER(A2:B, NOT(ISBLANK(A2:A)))), LAMBDA(x, TEXTJOIN(", ", TRUE, FILTER(SEQUENCE(MAX(FILTER(C:C, A:A=INDEX(x,1), B:B=INDEX(x,2)))), NOT(ISNUMBER(MATCH(SEQUENCE(MAX(FILTER(C:C, A:A=INDEX(x,1), B:B=INDEX(x,2)))), UNIQUE(FILTER(C:C, A:A=INDEX(x,1), B:B=INDEX(x,2))), 0))))))}, 3, FALSE), "")))

二、扩展场景（PART 2） #

扩展上述公式，支持NUMBER列单元格包含单个或多个数字的场景（默认按逗号+空格拆分多值，可根据实际分隔符调整）。

扩展场景实现公式 #

=TEXTJOIN(CHAR(10), TRUE, ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(FILTER(A2:C, NOT(ISBLANK(A2:A)))), {UNIQUE(FILTER(A2:B, NOT(ISBLANK(A2:A)))), BYROW(UNIQUE(FILTER(A2:B, NOT(ISBLANK(A2:A)))), LAMBDA(x, LET(
    nums, FLATTEN(SPLIT(TEXTJOIN(", ", TRUE, FILTER(C:C, A:A=INDEX(x,1), B:B=INDEX(x,2))), ", ")),
    unique_nums, UNIQUE(VALUE(nums)),
    max_num, MAX(unique_nums),
    missing, FILTER(SEQUENCE(max_num), NOT(ISNUMBER(MATCH(SEQUENCE(max_num), unique_nums, 0)))),
    IF(COUNTA(missing)>0, TEXTJOIN(", ", TRUE, missing), "")
)))}), 3, FALSE), "")))

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