如何使用arrayformula生成关联前列排除空值的会计科目树动态序列号

核心思路是按科目层级分别统计同上级下的非空累计数，再拼接成完整序列号，原生ARRAYFORMULA无法直接实现跨行条件统计，可搭配LAMBDA家族函数实现整列自动计算，无需下拉。

适用场景 #

• 按层级关联生成序列号，一级、二级、三级科目序号分别和上级科目绑定
• 自动跳过空行/空科目单元格
• 插入、删除行后自动重算，序列号不会错乱

参考公式 #

假设你的一级科目存放在B列、二级科目存放在C列、三级科目存放在D列，序列号需要输出在A列，在A2单元格输入以下公式即可自动向下溢出整列结果：

=BYROW(B2:B, LAMBDA(curr一级, IF(curr一级="",,
  # 一级序号：统计到当前行为止的非空一级科目个数
  COUNTIF(B$2:curr一级, "<>") &
  # 二级序号：同一级下的非空二级科目累计数，为空则不显示
  IF(OFFSET(curr一级,0,1)="",, "." & COUNTIFS(B$2:curr一级, curr一级, C$2:OFFSET(curr一级,0,1), "<>")) &
  # 三级序号：同一级+二级下的非空三级科目累计数，为空则不显示
  IF(OFFSET(curr一级,0,2)="",, "." & COUNTIFS(B$2:curr一级, curr一级, C$2:OFFSET(curr一级,0,1), OFFSET(curr一级,0,1), D$2:OFFSET(curr一级,0,2), "<>"))
)))

若使用不支持LAMBDA的旧版Google Sheets，可拆分辅助列实现：

1. 一级辅助列：=ARRAYFORMULA(IF(B2:B="",,COUNTIFS(B2:B,"<>",ROW(B2:B),"<="&ROW(B2:B))))
2. 二级辅助列：=ARRAYFORMULA(IF(C2:C="",,COUNTIFS(B2:B,B2:B,C2:C,"<>",ROW(B2:B),"<="&ROW(B2:B))))
3. 三级辅助列：=ARRAYFORMULA(IF(D2:D="",,COUNTIFS(B2:B,B2:B,C2:C,C2:C,D2:D,"<>",ROW(B2:B),"<="&ROW(B2:B))))
4. 最终序列号：=ARRAYFORMULA(IF(B2:B="",,一级辅助列&IF(C2:C="",,"."&二级辅助列)&IF(D2:D="",,"."&三级辅助列)))

自定义调整 #

• 有更多层级的话，参照上述逻辑新增对应层级的COUNTIFS统计段即可
• 列位不匹配的话，把公式里的B/C/D列替换为你实际的科目列即可

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