1. 预想效果

一份学生的成绩单，想要实现按专业分别排名次，同专业的学生按绩点排名，绩点相同再按总成绩排名。
主关键词是平均绩点，第二关键词是总成绩

专业 姓名 总成绩 平均绩点 专业排名（只按绩点排） 专业排名（按绩点和总分排）
汽修 陈豆子 654 2.4
汽修 高可乐 655 2
汽修 郭花卷 670 2.3
汽修 何桃子 688 2.4
汽修 胡丸子 697 2.6
汽修 黄苹果 760 3.5
汽修 林葡萄 706 2.6
汽修 刘小龙 746 3.4
汽修 罗牛牛 668 2.3
汽修 马猴子 658 2
汽修 曹小猫 669 2.1
汽修 严小文 755 3.5
汽修 金小火 534 0.9
新能源 孙猫咪 771 3.4
新能源 王袋鼠 749 3.2
新能源 吴黄鹂 688 2.6
新能源 徐莲子 779 3.6
新能源 杨草莓 713 3
新能源 张太阳 697 2.8
新能源 赵向日 727 3
新能源 周核桃 584 2
新能源 朱大树 686 2.6
新能源 吕小兵 697 2.7
新能源 施小牛 658 2.6
新能源 张小花 746 3.2
新能源 孔小五 726 3

2. 解决思路

2.1 先解决按专业和绩点排名

公式：=SUMPRODUCT(($A$2:$A$27=$A2)*($D2<$D$2:$D$27))+1
SUMPRODUCT第一个参数，是用A列判断是否与A2单元格相等。这里由于是判断专业，所以使用“=”去判断。
SUMPRODUCT第二个参数，是用D列（绩点）是否大于D2单元格，判断整列有多少个单元格的值是大于D2的值。所以这公式是降序排列的，如果升序，就要判断有多少单元格的值小于D2的值了。
最后需要“+1”，是由于判断如果没有大于D2的数，那只有返回0，但排名应该算第1名。
注意：范围一定要用绝对引用“$”
image.png

2.2 再解决绩点相同下按总分排名

在上面的基础上，添加绩点相同的情况下，按部分排名，这里前半段是直接用绩点排名，由于绩点相同时排名是相同的，再用后半段只排名绩点相同的部分人，两段相加就是两个条件下的排名了。所以在这里后半段公式是不需要“+1”的
例：下图里“D2”、“D5”、“D10”绩点都为2.4，后半段的公式就只排名这3个值，排名第一的在原名次上“+0”，第二的在原名次上“+1”，以此类推。
公式：=SUMPRODUCT(($A$2:$A$27=$A2)($D2<$D$2:$D$27))+1+SUMPRODUCT(($A$2:$A$27=$A2)($D$2:$D$27=$D2)*(C2<$C$2:$C$27))
后面一个SUMPRODUCT()函数里
第一参数还是判断专业相同
第二参数是判断绩点相同
第三参数是判断有多少单元格大于C2单元格（总分）的
image.png