本帖最后由 ovxyz 于 2024-12-19 11:51 编辑
Y3=SUM(IF(U3="总分",BYROW(CHOOSECOLS($D$3:$Q$34,1,4,6,8,10,12,14),LAMBDA(x,AND(x>=VLOOKUP(S3,$AE$3:$AL$5,{2,3,4,5,6,7,8},0)))),BYROW(CHOOSECOLS($D$3:$Q$34,1,MATCH(U3,$D$1:$R$1,0)),LAMBDA(x,AND(x>=VLOOKUP(S3,CHOOSECOLS($AE$3:$AL$5,1,2,MATCH(U3,$AE$2:$AL$2,0)),{2,3},0)))))*($A$3:$A$34=V3)*($S$3:$S$34=W3))
下拉
稍微简化一下:
=SUM(BYROW((CHOOSECOLS($D$3:$Q$34,1,IF(U3="总分",SEQUENCE(,6,4,2),MATCH(U3,$D$1:$R$1,0)))-VLOOKUP(S3,CHOOSECOLS($AE$3:$AL$5,IF(U3="总分",SEQUENCE(,8),HSTACK(1,2,MATCH(U3,$AE$2:$AL$2,0)))),IF(U3="总分",{2,3,4,5,6,7,8},{2,3}),0))>=0,AND)*($A$3:$A$34=V3)*($S$3:$S$34=W3))
|