请教对成绩进行等级赋分公式

astupig

i2单元格输入：
=IF(B2="","",LOOKUP(1,0/($W$2:$W$22>=(RANK(B2,B$2:B$626)/COUNTIF(B$2:B$626,">0"))*100),$X$2:$X$22))

W1：X22输入：
人数比例        赋分
100        40
99        43
98        46
96        49
93        52
89        55
84        58
78        61
71        64
64        67
57        70
50        73
43        76
36        79
28        82
21        85
15        88
10        91
6        94
3        97
1        100

blackieliu

micch 发表于 2018-7-25 10:35
你这说法完全是理想的说法，1个100，第二档2个97，这个个数怎么定，总人数如果187，按比例下来都是小数； ...

这个问题没考虑到，那如果是后面的档次不去管它 ，依次做下来该如何实现呢？
比如100如果有同分数4个，那就4个100,97的还是按比例算

blackieliu

astupig 发表于 2018-7-25 11:18
i2单元格输入：
=IF(B2="","",LOOKUP(1,0/($W$2:$W$22>=(RANK(B2,B$2:B$626)/COUNTIF(B$2:B$626,">0"))*10 ...

请问 最后两行数据出现#NA是怎么回事
21等.rar (28.16 KB, 下载次数: 40)

2018-7-26 10:10 上传

点击文件名下载附件

astupig

数据应该是从W2，X2开始，而你就是从W1，X1开始。
你的W1就是100，X1就是40.

正确的应该是：
有标题，也就是W1是人数比例，X1是赋分。
然后W2是100，X2是40.

因为公式就是从X2

blackieliu

astupig 发表于 2018-7-26 10:34
数据应该是从W2，X2开始，而你就是从W1，X1开始。
你的W1就是100，X1就是40.

调整了一下可以了，谢谢！可以教我一下公式里面这样的具体含义吗

astupig

blackieliu 发表于 2018-7-27 09:46
调整了一下可以了，谢谢！可以教我一下公式里面这样的具体含义吗

1. LOOKUP(1, 0/((条件1)*(条件2)(..)), 结果区域)是万金油公式，这个公式设计的时候，有几个妙处：抓住了LOOKUP查询时可以自动忽略错误的特性；使用逆向思维（不符合条件则错误）；使用LOOKUP的时候，正常情况下要排序，但是用0除后，就可以避免排序的困扰，如果有多个相同结果则返回最大非错误值。

用于多条件查询。 查询的时候，
有以下结果：（建议你用按F9一步一步看公式的执行结果）
1）如果有一个匹配，则返回匹配结果。
2）如果有多个匹配，则返回多个匹配结果中行数/列数最大的那一个结果。比如A23,A28都是匹配，则返回A28.
3）如果没有匹配的，由于是用0除，如果是FALSE，则错误，LOOKUP则返回“非错误”的最大的单元格（最靠后的单元格）。比如A2考号是10588的政治分数，他在学生中占比是前0.5%，W列所有的值都是大于0.5%的，所以返回最大的值（X22）； 比如A10（考号10542)，他在学生中占比是前4.8%，只有倒数倒数第三行W20以上才是大于4.8%，W21/W22都是小于4.8%的，所以W21/W22是FALSE，用0除则是DIV错误，所以返回非错误值的倒数第三行X20的值94.

2. (RANK(B2,B$2:B$626)/COUNTIF(B$2:B$626,">0"))*100 这个公式是用来算占比前多少%

blackieliu

astupig 发表于 2018-7-27 10:21
1. LOOKUP(1, 0/((条件1)*(条件2)(..)), 结果区域)是万金油公式，这个公式设计的时候，有几个妙处：抓住 ...

非常感谢你的热心指导  

blackieliu

blackieliu 发表于 2018-8-5 10:20
非常感谢你的热心指导

你好，能否再请教一下如何实现对算出的比例四舍五入得到相应等级的人数， 比如物理有123个人，那么1%就是1.23 四舍五入得到1个1百；但如果物理是有188个人，乘以1%就是1.88 四舍五入得到2，那就有2个100分
请问公式该如何修改

vvw123

w22单元格输入=ROUND(COUNTIF(B$2:B$626,">0")/100,0)/COUNTIF(B$2:B$626,">0")*100

micch

blackieliu 发表于 2018-7-26 10:05
这个问题没考虑到，那如果是后面的档次不去管它 ，依次做下来该如何实现呢？
比如100如果有同分数4个， ...

21等赋分.rar (22.96 KB, 下载次数: 67)

2018-8-15 22:35 上传

点击文件名下载附件

这么久了，还没弄好？以政治为例，分配方案怎么弄都不行，你应该手工做一个结果试试，然后才能根据你的具体方案写公式