Excel动态层级公式异常排查及合规实现方案问询

(A) 当前公式存在的核心问题 #

1. 层级数量计算错误：
   原公式用SUM(IFERROR(SEARCH("Level ?";Current_costs[#Headers]);0))计算层级列数，存在两处硬伤：一是?为单字符通配符，无法匹配Level 10这类多数字层级表头；二是SUM会把SEARCH返回的匹配位置相加，而非统计匹配列数，仅在少量层级时偶然得到正确结果，表头格式或数量变更后直接失效。

2. 层级匹配逻辑错误：
   原公式通过拼接当前行前A层内容，再去全表匹配拼接值获取Order。但同一父层级下的子层级（比如A→B→C下的E、F、G），前3层拼接值均为ABC，MATCH会返回第一个匹配行（E所在行）的Order=1，导致后续子层级的Order全部错误（F、G的第四层本该是2、3，却都取成1）。

3. 层级连续性判断逻辑混乱：
   原公式用EXPAND(IFERROR(SEQUENCE(Depth;;;0);0);Levels;;0)生成对比数组，再通过拼接字符串判断层级是否连续，这部分实现绕弯且极易出错，比如当Depth=4时生成的数组逻辑不符合预期，可能导致后续跳过本该执行的层级计算。

(B) 满足要求的正确公式实现 #

以下公式支持动态层级数量、无需VBA，完全基于Order列生成正确的层级值：

=LET(
    // 动态识别所有带"Level "的表头列
    Level_Headers, FILTER(Current_costs[#Headers], ISNUMBER(SEARCH("Level ", Current_costs[#Headers]))),
    // 获取当前行的所有Level列值
    Current_Levels, CHOOSEROWS(Current_costs[@], MATCH(Level_Headers, Current_costs[#Headers], 0)),
    // 当前行的Depth值
    D, [@Depth],
    // 逐个计算每一层的Order值
    Hierarchy_Parts, BYROW(SEQUENCE(D), LAMBDA(k,
        // 筛选出：前k层与当前行完全一致，且Depth=k的行，取其Order
        XLOOKUP(TRUE,
            BYROW(CHOOSEROWS(Current_costs[Level_Headers], SEQUENCE(ROWS(Current_costs))), LAMBDA(r,
                AND(r=TAKE(Current_Levels, k))
            )) * (Current_costs[Depth]=k),
            Current_costs[Order],
            ""
        )
    )),
    // 用点连接各层级的Order值
    TEXTJOIN(".", TRUE, Hierarchy_Parts)
)

公式说明： #

• 动态适配层级：通过FILTER+SEARCH自动识别所有层级列，不管是4层还是更多，都能自动适配。
• 精准匹配父层级：对每一层k，遍历全表找到前k层内容与当前行完全一致且Depth等于k的行，用XLOOKUP取其Order值，确保每个层级的编号准确对应父层级下的顺序。
• 逻辑简洁高效：去掉原公式中冗余的层级连续性判断（根据注释，表格已做错误校验），直接聚焦核心的层级匹配与Order取值。

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