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

LWQ张无忌

数组，下拉
=SUM(IF((COUNTIF(B:B,B:B)=D:D)*(J2>D:D)+(J2=D:D),((D:D-J2)/10+1)*C:C))
=SUM(SUMIFS(C:C,B:B,N$4:X$4,D:D,IF(COUNTIF(B:B,N$4:X$4)>J2,J2,COUNTIF(B:B,N$4:X$4)))*TEXT(1-(J2-COUNTIF(B:B,N$4:X$4))/10,"[>1]0"))

=SUM(IFERROR(IF(MATCH(B:B&D:D,N$4:X$4&COUNTIF(B:B,N$4:X$4),),MID("987",J2-D:D,1)/10*C:C),)+IF(D:D=J2,C:C))

zhangmi

想了一个晚上，只搞出一个非常笨的笨办法，字数超出了，能不能自定义名称啊？能的话，麻烦老师给个小分分，不能也没法，实在搞不出了。。。。数组公式
=SUM($C$2:$C$37*($D$2:$D$37=J2))+SUM(($C$2:$C$37)*(COUNTIF($B$2:$B$37,$B$2:$B$37)<J2)*($D$2:$D$37=COUNTIF($B$2:$B$37,$B$2:$B$37))*TEXT(J2-COUNTIF($B$2:$B$37,$B$2:$B$37),"[=2]!0!.8;[=1]!0!.9;1"))

Michael.Deng

答案(6.8号已完成，6.19心血来潮又缩减了几个字符，94字符，带等号，大括号97字符)
L2=SUM((COUNTIF(B$2:B$37,B$2:B$37)<J2-{-4,0,1,2})*(D$2:D$37=J2-{0,1,2,3})*{10,9,8,7}/10*C$2:C$37)

留个简化思路吧
初始想法
L2=SUM(((J2=$D$2:$D$37)+(COUNTIF($B$2:$B$37,$B$2:$B$37)<J2)*($D$2:$D$37=J2-1)*0.9+(COUNTIF($B$2:$B$37,$B$2:$B$37)<J2-1)*($D$2:$D$37=J2-2)*0.8+(COUNTIF($B$2:$B$37,$B$2:$B$37)<J2-2)*($D$2:$D$37=J2-3)*0.7)*$C$2:$C$37)
简化1
L2=SUM(((COUNTIF($B$2:$B$37,$B$2:$B$37)<J2-{0,1,2})*($D$2:$D$37=J2-{1,2,3})*{0.9,0.8,0.7})*$C$2:$C$37)+SUM((J2=$D$2:$D$37)*$C$2:$C$37)
简化2
L2=SUM(((COUNTIF($B$2:$B$37,$B$2:$B$37)<J2-{-4,0,1,2})*($D$2:$D$37=J2-{0,1,2,3})*{1,0.9,0.8,0.7})*$C$2:$C$37)

三建 算是送分题吧，连我都能答出来，坐等开题

cemmm

{=SUM(C$2:C$37*((J2=D$2:D$37)+(J2>D$2:D$37)*(D$2:D$37=COUNTIF(B:B,B$2:B$37))*(1-(J2-D$2:D$37)/10)))}

hcm19522

L2:L5 区域数组{=TRANSPOSE(MMULT(COLUMN(A:AJ)^0,IF(D2:D37={1,2,3,4},C2:C37,(D2:D37<{1,2,3,4})*(1-({1,2,3,4}-D2:D37)/10)*C2:C37*(COUNTIF(B:B,B2:B37)=D2:D37))))


方式二{=SUM(IF(COUNTIF(B:B,B$2:B$37)=D$2:D$37,(1-TEXT(({1,2,3,4}-D$2:D$37)/10,"0.0;1")),{1,2,3,4}=D$2:D$37)*C$2:C$37*({1,2,3,4}=J2))

wggx4

第一个公式    =SUM(INDEX($E$2:$H$37,,ROW(1:1)))
第二个公式    =SUMPRODUCT(($B2:$B37=N4)*INDEX(MMULT(--IF(ISERR(($E2:$H37)*1),"0",($E2:$H37)*1),(ROW(1:4)^0)),,1))
     （数组公式）
不知道第二个对缺考的学生这么算对不啊，凑个热闹吧。

guodongshu

=SUM(IF((D2:D37=J2)+((D2:D37<J2)*(D2:D37=COUNTIF(B$2:B37,B2:B37))),(10-J2+D2:D37)*C2:C37/10),)
数组公式下拉

sucx007

1. =SUM(C$2:C37*(1-IFERROR(TEXT((J2-D$2:D37)/10,"0.0;1")^(COUNTIF(B:B,B$2:B37)<=D$2:D37),)))

复制代码

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

复制代码

jisijie630

请问老师：如何回复为此贴仅作者可见？

dim麻辣君

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

复制代码

占个楼先，有短的再来

补充内容 (2020-6-13 22:46):
好像不能编辑只能补充了
89字符
=SUM(IFNA(XLOOKUP(N$4:X$4,IF(D$2:D$37>J2,,B$2:B$37),C$2:C$37*(10-J2+D$2:D$37)/10,,,-1),))