[开_155]依次数统计分数(待总结)

五华

82字符长度

1. =SUM(C$2:C88*(1-TEXT(J2-D$2:D88,IF(COUNTIF(B:B,B$2:B88)>D$2:D88,1,"!.0")&";1;0")))

复制代码

dipperrrr

瞎写一个
=SUM(C$2:C$37*(MAXIFS(D:D,B:B,B$2:B$37,D:D,"<="&J2)=D$2:D$37)*(10-J2+D$2:D$37))/10

265094

=SUM(C$2:C$37*(D$2:D$37=COUNTIFS(B:B,B$2:B$37,D:D,"<="&J2))*(1-TEXT(J2-D$2:D$37,"\.0;\0")))

pxiceplay

火车占楼，等着看前面几十位高手的答案→_→
L1数组公式：
=SUM(SUMIFS(C:C,B:B,N$4:X$4,D:D,COUNTIFS(B:B,N$4:X$4,D:D,"<="&J2))*(1-(J2-COUNTIFS(B:B,N$4:X$4,D:D,"<="&J2))/10))
勉强及格：
=SUM((B$2:B$37=N$4:X$4)*(D$2:D$37=COUNTIFS(B:B,N$4:X$4,D:D,"<="&J3))*(10+D$2:D$37-J3)/10*C$2:C$37)

然后简化出一个理论上可行实际电脑报错不能计算的公式，不知道有没有电脑能跑动：
=SUM((B:B=N:X)*(D:D=COUNTIFS(B:B,N$4:X$4,D:D,"<="&J2))*(1+(D:D-J2)/10)*C:C)

questionsome

实在写不出来 跟着各位老师后面蹭思路

天意转难知

=SUM(((COUNTIF(B$2:B37,B$2:B37)=D$2:D37)*(D$2:D37<J2)*(10-J2+D$2:D37)/10+(D$2:D37=J2))*C$2:C37)

cjh-01

也开火车，

1. =MMULT(IF(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0)=0,INDEX(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0),MMULT(COLUMN(A:D)^0,N(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0)<>0)),COLUMN(A:K))*(1-(ROW(1:4)-MMULT(COLUMN(A:D)^0,N(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0)<>0)))*0.1),IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0)),ROW(1:11)^0)
   
2. 
   
3. =MMULT(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),INDEX(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0),MMULT(COLUMN(A:D)^0,N(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0)<>0)),COLUMN(A:K))*(1-(ROW(1:4)-MMULT(COLUMN(A:D)^0,N(IFERROR(INDEX($C$2:$C$37,MATCH($N$4:$X$4&$J$2:$J$5,$B$2:$B$37&$D$2:$D$37,)),0)<>0)))*0.1)),ROW(1:11)^0)

复制代码

L2:L5区域数组公式。

精简了几十个字符，还是很长，估计思路不对。

gh_li0606

也来试一下

nsxlyj

=SUM(CHOOSE(1+MMULT(TRANSPOSE(ROW($2:2)^0),N(COUNTIF(B$2:B$37,N$4:X$4)<J$2:J2)),0,9,8,7,0)/10*SUMIFS(C$2:C$37,B$2:B$37,N$4:X$4,D$2:D$37,MAXIFS(D$2:D$37,B$2:B$37,N$4:X$4))+SUMIFS(C$2:C$37,B$2:B$37,N$4:X$4,D$2:D$37,J2))

丢丢表格