基于组织层级结构计算Expected count的Excel函数需求

核心公式（Excel 365/2021 动态数组版，适配数千行+8级层级） #

在H2单元格输入公式后按回车，自动溢出填充所有行：

=BYROW(A2:G1000, LAMBDA(r,
    LET(
        // 定义层级列范围（最多8级，这里为A-H，可按需修改）
        level_range, INDEX(r,1):INDEX(r,8),
        // 定位当前行最后一个非空层级的位置
        last_level_col, XLOOKUP("*", level_range, SEQUENCE(8),,0,-1),
        // 生成当前行的层级路径（用|分隔避免匹配歧义）
        current_path, TEXTJOIN("|", TRUE, INDEX(level_range,1):INDEX(level_range,last_level_col)),
        // 生成所有行的完整层级路径前缀（用于匹配下属）
        all_prefixes, BYROW(A2:H1000, LAMBDA(row_levels, 
            LEFT(TEXTJOIN("|", TRUE, row_levels), LEN(current_path)+1)
        )),
        // 筛选当前行的直接下属单元
        sub_units, FILTER(INDEX(A2:H1000,,last_level_col+1), 
            all_prefixes=current_path&"|" * NOT(ISBLANK(INDEX(A2:H1000,,last_level_col+1)))
        ),
        // 统计唯一下属单元数量
        unique_subs, COUNTA(UNIQUE(sub_units)),
        // 获取当前行的Team headcount
        headcount, INDEX(r,7),
        // 按规则计算结果
        IF(headcount=0,
            unique_subs*2,
            IF(unique_subs=0, headcount-1, headcount-1 + unique_subs*2)
        )
    )
))

公式关键细节 #

• 层级适配：把公式中的A2:H1000和SEQUENCE(8)改成实际的层级列范围（比如当前是A-F就设为A2:F1000和SEQUENCE(6)）。
• 路径匹配逻辑：用TEXTJOIN拼接层级为带分隔符的字符串，确保“集团A”和“集团A部”不会被误判为同一上级。
• 性能优化：动态数组函数BYROW+LAMBDA批量处理，比传统数组公式更适合数千行的大数据量场景。

旧版Excel兼容方案（2019及更早版本） #

旧版无动态数组支持，可在H2单元格输入以下数组公式（按Ctrl+Shift+Enter确认），下拉填充：

=IF(G2=0,
    SUMPRODUCT(1/COUNTIF(FILTER(INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)),LEFT(TEXTJOIN("|",TRUE,A:INDEX(A:H,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A)+1)),LEN(TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1)))+1)=TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1))&"|" AND INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64))<>""),FILTER(INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)),LEFT(TEXTJOIN("|",TRUE,A:INDEX(A:H,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A)+1)),LEN(TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1)))+1)=TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1))&"|" AND INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64))<>"")))*2,
    IF(SUMPRODUCT(1/COUNTIF(FILTER(INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)),LEFT(TEXTJOIN("|",TRUE,A:INDEX(A:H,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A)+1)),LEN(TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1)))+1)=TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1))&"|" AND INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64))<>""),FILTER(INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)),LEFT(TEXTJOIN("|",TRUE,A:INDEX(A:H,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A)+1)),LEN(TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1)))+1)=TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1))&"|" AND INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64))<>"")))=0,
        G2-1,
        G2-1 + SUMPRODUCT(1/COUNTIF(FILTER(INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)),LEFT(TEXTJOIN("|",TRUE,A:INDEX(A:H,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A)+1)),LEN(TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1)))+1)=TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1))&"|" AND INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64))<>""),FILTER(INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)),LEFT(TEXTJOIN("|",TRUE,A:INDEX(A:H,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A)+1)),LEN(TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1)))+1)=TEXTJOIN("|",TRUE,A2:INDEX(A2:H2,LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))-COLUMN(A2)+1))&"|" AND INDIRECT(CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64)&":"&CHAR(LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))+64))<>"")))*2
    )
)

注：旧版公式冗长且性能较差，建议优先升级至Excel 365，或添加辅助列拆分计算（比如先计算每行的层级路径、下属单元数量）。

规则对应验证 #

• 无下属单元：unique_subs=0时，返回headcount-1
• 有下属单元：返回headcount-1 + unique_subs×2
• Team headcount为0：返回unique_subs×2

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