Question

请教Excel高手

A1至A4是四个班的考试成绩（原始分数），B1至B4要给这四个成绩赋分，条件如下：成绩第一名赋分20；和第一名相差1分以下（含1分）赋分19.5；相差2分以下（含2分）赋分19；……成绩相差5分之内（不含5分），每相差1分，赋分少0.5分，即半分半分的减。成绩相差达到5分，每差1分赋分少1分，即1分1分的减。如何写这个公式呢？

Answer 1

成绩相差达到5分，每差1分赋分少1分，有两种理解：比如说相差6分，一种算法是直接6×1，算为6分，另一种算法是相差5分以内的扣4×0.5加上达到5分以上的2×1，这样一起算是4分。一下是具体的例子：
65 → 19.5
62 → 18
60 → 14
66 → 20
上面的结果用公式=IF(((MAX($A$1:$A$4))-A1)<5,20-((MAX($A$1:$A$4))-A1)*0.5,20-((MAX($A$1:$A$4))-A1))

65 → 19.5
62 → 18
60 → 16
66 → 20
上面的结果用公式=IF(((MAX($A$1:$A$4))-A1)<5,20-((MAX($A$1:$A$4))-A1)*0.5,20-((MAX($A$1:$A$4))-A1)*0.5+(4-((MAX($A$1:$A$4))-A1))*0.5)

Answer 2

A2依次类推，EXCEL的复制功能，和计算公式复制你应该会用吧。反正用拖的就行了。 在第二个工作表格里填写如下： 在C1里填写“=IF（OR（原始记录！$J$1

Answer 3

建议先用排名函数RANK(A1,$A$1:$A$4)排列出成绩名次，再用判断函数IF(B1=1,A1+20,0)来赋分。要是写成一个公式有点繁琐。

Answer 4

在B1里输入下面的公式,然后下拉复制:
=IF(MAX($A$1:$A$4)-A1<1,20,IF(MAX($A$1:$A$4)-A1<5,20-(MAX($A$1:$A$4)-A1)*0.5,20-(MAX($A$1:$A$4)-A1)))

Answer 5

B1公式
=20-INT(MAX(A$1:A$4)-A1)*IF(MAX(A$1:A$4)-A1>=5,1,0.5)
往下拉

Answer 6

=20-(MAX($A$1:$A$4)-A1)*(1-0.5*((MAX($A$1:$A$4)-A1)<=5))