主页 / user-17988705

Jonathan's questions

Martin Hope
Jonathan
Asked: 2025-01-07 05:24:14 +0800 CST
  • 5

我正在尝试找出一种方法将所有这些替换indirect到这个公式中：

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

到目前为止我一直使用以下公式，但只要我修改单元格，或者即使我只是复制/清除然后将相同的公式粘贴回同一个单元格（似乎index(DY:; ROW(x)) : index (DY:;ROW(x))不再受支持或被识别为公式的一部分），它就会停止工作。

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

我需要替换间接代码，因为我的电子表格中有很多代码，这会减慢工作表的速度（甚至导致工作表的某些部分无法工作）。有没有办法替换间接代码，或者只是让系统再次识别代码？

google-sheets