如何用ArrayFormula在Google Sheets中生成分组序列号？

请分别给出A1、B1、C1和D1单元格的ArrayFormula，以生成目标输出列。

关键定义 #

• 主标题：指亲属、朋友、商务伙伴这类前后各带一个空格的名称；其余为普通名称。
• 输入列：NAME列（E列）和PAX列（F列），其余为需生成的输出列。

通用序列号生成规则 #

1. 序列号完全基于NAME列和PAX列生成
2. 若PAX值为0，对应输出列内容为空

A、B、C列规则 #

当PAX为空时：

• 若对应行是主标题：输出对应格式标识（A列字母、B列数字、C列罗马数字）
• 若对应行是普通名称：输出为空

D列规则 #

当PAX为空时：

• 若对应行是主标题：输出为空
• 若对应行是普通名称：生成序列号，每遇到下一个主标题则从1重新开始计数

数据示例 #

各列ArrayFormula #

A1单元格公式 #

=ARRAYFORMULA(
  LET(
    is_header, REGEXMATCH(E:E, "^ .* $"),
    pax_valid, F:F<>0,
    header_count, SCAN(0, is_header, LAMBDA(a,c, IF(c, a+1, a))),
    seq, COUNTIFS(header_count, header_count, ROW(E:E), "<="&ROW(E:E), pax_valid, TRUE),
    IF(
      is_header, CHAR(64+header_count),
      IF(pax_valid, seq, "")
    )
  )
)

B1单元格公式 #

=ARRAYFORMULA(
  LET(
    is_header, REGEXMATCH(E:E, "^ .* $"),
    pax_valid, F:F<>0,
    header_count, SCAN(0, is_header, LAMBDA(a,c, IF(c, a+1, a))),
    seq, COUNTIFS(header_count, header_count, ROW(E:E), "<="&ROW(E:E), pax_valid, TRUE),
    IF(
      is_header, header_count,
      IF(pax_valid, seq, "")
    )
  )
)

C1单元格公式 #

=ARRAYFORMULA(
  LET(
    is_header, REGEXMATCH(E:E, "^ .* $"),
    pax_valid, F:F<>0,
    header_count, SCAN(0, is_header, LAMBDA(a,c, IF(c, a+1, a))),
    seq, COUNTIFS(header_count, header_count, ROW(E:E), "<="&ROW(E:E), pax_valid, TRUE),
    roman_header, MAP(header_count, LAMBDA(x, IF(x=0, "", ROMAN(x)))),
    IF(
      is_header, roman_header,
      IF(pax_valid, seq, "")
    )
  )
)

D1单元格公式 #

=ARRAYFORMULA(
  LET(
    is_header, REGEXMATCH(E:E, "^ .* $"),
    pax_valid, F:F<>0,
    group, SCAN(0, is_header, LAMBDA(a,c, IF(c, a+1, a))),
    D_seq, COUNTIFS(group, group, ROW(E:E), "<="&ROW(E:E), pax_valid, TRUE),
    IF(
      is_header, "",
      IF(pax_valid, D_seq, "")
    )
  )
)

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