RANK求排名时，怎么加个判断函数呢？

boyljd

求排名.rar (17.31 KB, 下载次数: 8)

2021-4-29 17:38 上传

点击文件名下载附件

请各位帮我修改下公式  谢谢！

cttz

=SUMPRODUCT((INT($A$4:$A$14/100)=INT(A4/100))*($D$4:$D$14>D4))+1
下拉。
===============下面是公式的原理===============
原理以A4所在的行为例进行叙述，其他的行同理。
您的表是用101，102这样的三位数字表示年级和班级的，且最高位数表示年级。因此，取年级的本质是取这个数除以100的商。比如101/100商1，201/100商2，就得到了您想要的年级。在Excel中这个运算是用用INT函数来进行的。
INT($A$4:$A$14/100)这部分是表示对A4:A14这个区域中，每一个表示年级和班级的三位数求它除以100的商，即取年级的值，值域为{1,2,3,4,5,6}。后面INT(A4/100)表示对A4中的三位数求这个商，结果是1，表示A4所在行对应的成绩是1年级的。其他行同理。
两部分之间用一个等号连接，表示判定是不是同一个年级。返回的是一个值域为{TRUE，FALSE}的数组，TRUE即表示是同一个年级，在计算中视为1；FALSE表示不是同一个年级，在计算中视为0。
$D$4:$D$14>D4表示判定D4:D14范围内，每一个成绩是不是比D4大的。是的话返回TRUE，计算时视为1；不是的话，返回FALSE，计算时视为0。显然，这两部分的数组通过相乘后再相加的运算就得到了：求同一年级内有多少个成绩比待判定的成绩好。最高的成绩，其值为0。
但名次是从第1位开始排的，所以在整个公式后面加1，表示“如果有0个成绩比它高，则它排第1位”。其他名次同理。
难点在于：用INT函数取年级的思路不容易想到。

cttz

求排名.rar (16.29 KB, 下载次数: 4)

2021-4-29 20:31 上传

点击文件名下载附件

cttz

题主是教师吧，SUMPRODUCT的排名功能，在本论坛中有很多叙述，另有一个【教育专用贴】讲到了所有教育领域可能出现的EXCEL问题，您可以查看一下。

boyljd

cttz 发表于 2021-4-29 20:45
=SUMPRODUCT((INT($A$4:$A$14/100)=INT(A4/100))*($D$4:$D$14>D4))+1
下拉
==================下面是公式 ...

非常感谢，这2个函数正好我也在用。我也是想怎么取左边第一位下手，最后只会加个辅助列白色字体隐藏。但是没想到你的这么巧妙，求整数就搞定了。谢谢大神啦

boyljd

cttz 发表于 2021-4-29 20:45
题主是教师吧，SUMPRODUCT的排名功能，在本论坛中有很多叙述，另有一个【教育专用贴】讲到了所有教育领域可 ...

是的 求老哥给个教育专用贴的地址，谢谢

cttz

boyljd 发表于 2021-4-29 22:13
是的 求老哥给个教育专用贴的地址，谢谢

教育应用贴（分班、统计、课表等）1142楼添加运动会成绩统计表20121215
http://club.excelhome.net/thread-589978-1-1.html
(出处: ExcelHome技术论坛)

hcm19522

=SUMPRODUCT((LEFT(A$4:A$14)=LEFT(A4))*(D$4:D$14>D4))+1

cttz

hcm19522 发表于 2021-4-30 11:14
=SUMPRODUCT((LEFT(A$4:A$14)=LEFT(A4))*(D$4:D$14>D4))+1

这个公式在有10~12年级（高一到高三）的时候，就不那么适用了

boyljd

hcm19522 发表于 2021-4-30 11:14
=SUMPRODUCT((LEFT(A$4:A$14)=LEFT(A4))*(D$4:D$14>D4))+1

取左边和除法求整，那个更不占内存，更好一点？