改进Excel E列公式：实现先按B列再按C列的无重复排名
2026-6-16
实现Excel中按得分+助攻的无重复排名
原始数据（A1:E9）
将以下数据复制到Excel的A1:E9单元格：
| 球员 | 得分 | 助攻 | 先按B列再按C列排名（原D列公式） | 仅B列无重复排名（原E列公式） |
|---|---|---|---|---|
| Andy | 10 | 8 | =RANK.EQ($B2,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B2,$C$2:$C$9,">"&$C2) | =RANK.EQ($B2,$B$2:$B$9)+COUNTIFS($B$2:B2,B2)-1 |
| Bernard | 10 | 2 | =RANK.EQ($B3,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B3,$C$2:$C$9,">"&$C3) | =RANK.EQ($B3,$B$2:$B$9)+COUNTIFS($B$2:B3,B3)-1 |
| Carl | 6 | 4 | =RANK.EQ($B4,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B4,$C$2:$C$9,">"&$C4) | =RANK.EQ($B4,$B$2:$B$9)+COUNTIFS($B$2:B4,B4)-1 |
| Derrick | 6 | 3 | =RANK.EQ($B5,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B5,$C$2:$C$9,">"&$C5) | =RANK.EQ($B5,$B$2:$B$9)+COUNTIFS($B$2:B5,B5)-1 |
| Erin | 8 | 6 | =RANK.EQ($B6,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B6,$C$2:$C$9,">"&$C6) | =RANK.EQ($B6,$B$2:$B$9)+COUNTIFS($B$2:B6,B6)-1 |
| Frank | 8 | 6 | =RANK.EQ($B7,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B7,$C$2:$C$9,">"&$C7) | =RANK.EQ($B7,$B$2:$B$9)+COUNTIFS($B$2:B7,B7)-1 |
| Greg | 3 | 6 | =RANK.EQ($B8,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B8,$C$2:$C$9,">"&$C8) | =RANK.EQ($B8,$B$2:$B$9)+COUNTIFS($B$2:B8,B8)-1 |
| Harry | 2 | 9 | =RANK.EQ($B9,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B9,$C$2:$C$9,">"&$C9) | =RANK.EQ($B9,$B$2:$B$9)+COUNTIFS($B$2:B9,B9)-1 |
现有公式问题
- 原D列公式：能按得分降序→助攻降序排名，但存在重复值（如Erin和Frank得分、助攻均相同，D列会出现两个排名3，跳过排名4）。
- 原E列公式：仅针对得分实现无重复排名，但未纳入助攻维度，无法满足多维度排序的需求。
改进后的无重复排名公式
方案1：SUMPRODUCT实现（推荐）
在E2单元格输入以下公式，下拉填充至E9：
=SUMPRODUCT(--(($B$2:$B$9>$B2)+($B$2:$B$9=$B2)*($C$2:$C$9>$C2)))+1
逻辑说明：
($B$2:$B$9>$B2)：统计所有得分高于当前行的条目数量，--将逻辑值转为1/0。($B$2:$B$9=$B2)*($C$2:$C$9>$C2)：统计得分与当前行相同但助攻更高的条目数量。- SUMPRODUCT求和得到所有排名比当前行靠前的条目总数，加1即为当前行的无重复排名。
方案2：基于原公式的改进版
如果偏好使用RANK.EQ+COUNTIFS的组合，可使用以下公式：
=RANK.EQ($B2,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B2,$C$2:$C$9,">"&$C2)+COUNTIFS($B$2:B2,$B2,$C$2:C2,$C2)-1
逻辑说明：
- 前半部分
RANK.EQ(...) + COUNTIFS(...)：继承原D列的多维度排名逻辑，得到初始排名（可能重复）。 - 后半部分
COUNTIFS($B$2:B2,$B2,$C$2:C2,$C2)-1：统计当前行及以上、得分和助攻均与当前行相同的条目数，减1后作为偏移量，给重复条目分配递增的排名，实现无重复效果。
效果验证
改进后，Erin的排名为3，Frank的排名为4，既保留了得分降序→助攻降序的排序规则，又实现了无重复的连续排名，符合需求。
内容的提问来源于stack exchange，提问作者Kram Kramer
相关产品推荐
相关产品推荐
