如何在Google Sheet的D1单元格使用ArrayFormula生成特定序列号

需要在D1单元格使用ArrayFormula生成与下表完全一致的序列号：

规则 #

• 每个唯一姓名对应独立的序列号序列
• 同一姓名且同一类别对应相同序列号，不同类别则序列号不同
• 类别为空的记录不生成序列号
• 品质字段包含"bp"（不区分大小写）的记录不生成序列号

公式 #

在D1单元格输入以下公式：

=ArrayFormula(
  LET(
    data, A2:C,
    names, INDEX(data,,1),
    qualities, INDEX(data,,2),
    categories, INDEX(data,,3),
    valid, (categories<>"")*NOT(REGEXMATCH(qualities, "(?i)bp")),
    key, IF(valid, names&"|"&categories, ""),
    unique_keys, UNIQUE(FILTER(key, key<>"")),
    numbered, HSTACK(unique_keys, SEQUENCE(ROWS(unique_keys))),
    result, XLOOKUP(key, INDEX(numbered,,1), INDEX(numbered,,2), ""),
    IF(valid, result, "")
  )
)

公式说明 #

1. 用LET定义变量简化公式结构
2. valid判断当前行是否符合生成条件：类别非空且品质不含不区分大小写的"bp"
3. 为有效行生成姓名|类别的唯一标识key
4. 对所有唯一key按出现顺序分配序列号
5. 通过XLOOKUP将序列号匹配到对应行，无效行返回空值

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