Google Sheets中替换COUNTIFS公式内的INDIRECT函数

你的问题在于之前用index(DY:; ROW(x)) : index(DY:;ROW(x))拼接范围的写法，Google Sheets无法动态识别这种手动拼接的范围，导致复制或修改后失效。以下是几种可行的替换方案，性能均优于INDIRECT：

方案1：修复INDEX写法（直接返回整行范围） #

用INDEX(DY:DY, ROW(x), 0)直接返回目标行从DY列开始的整行范围（参数0表示返回整行），无需手动拼接：

={
"𝒇x"; 
MAP($A9:$A, LAMBDA(x, IF(AND(x <> "", INDEX(AL:AL, ROW(x)) <> ""),
    IFERROR(
        (COUNTIFS(INDEX(DY:DY, ROW(x), 0), "P", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(INDEX(DY:DY, ROW(x), 0), "D", $DY$6:$6, $AL$6, $DY$5:$5, "H")) / 
        (COUNTIFS(INDEX(DY:DY, ROW(x), 0), "P", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(INDEX(DY:DY, ROW(x), 0), "D", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(INDEX(DY:DY, ROW(x), 0), "I", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(INDEX(DY:DY, ROW(x), 0), "E", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(INDEX(DY:DY, ROW(x), 0), "A", $DY$6:$6, $AL$6, $DY$5:$5, "H"))
    , "-")
, "")))}

方案2：用OFFSET生成动态范围 #

OFFSET语法更直观，可精准定位目标行范围，性能稳定：

={
"𝒇x"; 
MAP($A9:$A, LAMBDA(x, IF(AND(x <> "", INDEX(AL:AL, ROW(x)) <> ""),
    IFERROR(
        (COUNTIFS(OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ)), "P", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ)), "D", $DY$6:$6, $AL$6, $DY$5:$5, "H")) / 
        (COUNTIFS(OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ)), "P", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ)), "D", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ)), "I", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ)), "E", $DY$6:$6, $AL$6, $DY$5:$5, "H") + 
         COUNTIFS(OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ)), "A", $DY$6:$6, $AL$6, $DY$5:$5, "H"))
    , "-")
, "")))}

OFFSET(DY$1, ROW(x)-1, 0, 1, COLUMNS(DY:ZZ))解释：以DY1为起点，向下偏移ROW(x)-1行定位到目标行，向右偏移0列，取1行高，列数覆盖DY到ZZ（可根据实际数据列调整ZZ为最后一列）。

方案3：重构公式减少重复计算（大幅提升性能） #

原公式重复调用多次COUNTIFS，用LET函数定义变量，避免重复计算，同时简化公式：

={
"𝒇x"; 
MAP($A9:$A, LAMBDA(x, IF(AND(x <> "", INDEX(AL:AL, ROW(x)) <> ""),
    IFERROR(
        LET(
            target_row, INDEX(DY:DY, ROW(x), 0),
            p_count, COUNTIFS(target_row, "P", $DY$6:$6, $AL$6, $DY$5:$5, "H"),
            d_count, COUNTIFS(target_row, "D", $DY$6:$6, $AL$6, $DY$5:$5, "H"),
            i_count, COUNTIFS(target_row, "I", $DY$6:$6, $AL$6, $DY$5:$5, "H"),
            e_count, COUNTIFS(target_row, "E", $DY$6:$6, $AL$6, $DY$5:$5, "H"),
            a_count, COUNTIFS(target_row, "A", $DY$6:$6, $AL$6, $DY$5:$5, "H"),
            (p_count + d_count) / (p_count + d_count + i_count + e_count + a_count)
        ), "-")
, "")))}

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