本帖最后由 良良 于 2024-10-16 16:18 编辑
班级数与“早读"表不匹配?
C6若改为:3,
D6- =NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$N:$N,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$T:$T,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$Z:$Z,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$AF:$AF,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$AL:$AL,"<>"))
复制代码
简化一下:
- =SUM(MMULT(SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),OFFSET(早读!$N:$N,,ROW($1:$5)*6-6),"<>"),{-1;1;-1}))
复制代码 |