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

沈志辉

=SUM((MMULT(N(B$2:B$37=TRANSPOSE(B$2:B$37)),ROW(1:36)^0)=CHOOSE(Z2,{4,3,2},{4,3,2},{4,2,3},{4,3,2}))*(D$2:D$37=CHOOSE(Z2,1,2,{3,2,3},{4,3,2}))*C$2:C$37*CHOOSE(Z2,1,1,{1,0.9,1},{1,0.9,0.8}))

补充内容 (2020-6-27 16:04):
=SUM((COUNTIF(B$2:B37,B$2:B37)=IF(J2-3,{4,3,2},{4,2,3}))*(D$2:D37=CHOOSE(J2,1,2,{3,2,3},{4,3,2}))*C$2:C37*CHOOSE(J2,1,1,{1,0.9,1},{1,0.9,0.8}))

沈志辉

              

太阳之子

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

复制代码

liu2336904

不会做，先来个超标的
=SUM(SUMIFS(C:C,B:B,N$4:X$4,D:D,TEXT(COUNTIF(B:B,N$4:X$4),"[>"&J2&"]"&J2))*(1-TEXT(COUNTIF(B:B,N$4:X$4)-J2,"!0;0")/10))

cutecpu

=SUM(C$2:C45*(10-J2+D$2:D45)*((D$2:D45=J2)+(COUNTIF(B:B,B$2:B45)=D$2:D45)*(J2>D$2:D45)))/10

祝顺心，南无阿弥陀佛！

补充内容 (2020-6-11 14:36):
(5/27)思路2: =SUM(C$2:C45*(D$2:D45=J2-TEXT(COUNTIF(B:B,B$2:B45)-J2,"!0;0"))*(10-J2+D$2:D45))/10
(6/7)思路3: =SUM(C$2:C45*(D$2:D45=COUNTIFS(B:B,B$2:B45,D:D,"<="&J2))*(10-J2+D$2:D45))/10

王嘉玲

77字符，应该可以继续优化，还在思考中。。。。。

1. =SUM(IFERROR(C:C*(1-TEXT("."&J2-D:D,IF(COUNTIF(B:B,B:B)>D:D,,".0")&";;0")),))

复制代码

梦幻小丑

1. =SUM((COUNTIFS(B:B,B$2:B37,D:D,"<="&J2)=D$2:D37)*(10+D$2:D37-J2)/10*C$2:C37)

复制代码

1. =SUM(IF(COUNTIFS(B:B,B$2:B37,D:D,">"&D$2:D37),A1:D1=0,{10,9,8,7}/10)*(D$2:D37+{0,1,2,3}=J2)*C$2:C37)

复制代码

CheryBTL

先凑一个：

1. =SUM(SUMIFS(C:C,B:B,N$4:X$4,D:D,TEXT(COUNTIF(B:B,N$4:X$4),"[<"&J2&"]0;;"&J2&";"))*(1-TEXT(J2-COUNTIF(B:B,N$4:X$4),"0;!0;0")/10))

复制代码

简化114字符：

1. =SUM((SUMIFS(C:C,B:B,N$4:X$4,D:D,J$2:J2)=0)*SUMIFS(C:C,B:B,N$4:X$4,D:D,J$1:J1)*(J$2:J2+9-J2)/10,SUMIF(D:D,J2,C:C))

复制代码

102339267

=SUM(SUMIFS(C:C,B:B,N$4:X$4,D:D,COUNTIFS(B:B,N$4:X$4,D:D,"<="&J4))*((COUNTIFS(B:B,N$4:X$4,D:D,"<="&J4)-J4)/10+1))

wggx4

=SUM(OFFSET($D$2,,J2,36,))