Excel按日期分组对数值排名 支持下拉填充的公式实现方法

适用场景与规则 #

• 适配场景：表格存在多行重复日期数据，无需对日期维度做排名，仅需按日期分组，对分组内对应数值做独立排名
• 基础表结构：共3列，分别为Date（日期列）、Value（待排名数值列）、Rank（排名结果列）
• 排名规则：同一日期分组内的数值按从高到低排序，组内最高值排名为1、次高值为2，以此类推，公式支持下拉填充批量计算全量结果

可直接套用的公式 #

全版本通用公式（兼容所有Excel版本） #

假设数据首行是表头，有效数据从第2行开始，第一个待计算排名的单元格是C2（即Rank列第2行），在C2输入以下公式后按回车，直接下拉填充到所有数据行即可：

=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000>B2))+1

使用注意：

• 将公式中$A$2:$A$1000替换为你表格中Date列的实际数据范围，必须加$做绝对引用，避免下拉填充时范围偏移
• 将公式中$B$2:$B$1000替换为你表格中Value列的实际数据范围，同样做绝对引用锁定
• 公式逻辑：统计当前行所属日期组内，数值比当前行大的条目总数，结果加1即为当前值的组内排名，默认支持同值并列排名

新版本Excel简化公式（适用于365/2021及以上支持动态数组的版本） #

如果使用的是新版Excel，可直接用COUNTIFS写法，计算效率更高，无需手动限定数据行范围，直接整列引用即可：

=COUNTIFS($A:$A,A2,$B:$B,">"&B2)+1

输入后直接下拉填充即可完成全量计算。

规则调整说明 #

• 如果需要组内数值从低到高排名，仅需把上述公式中的比较符>替换为<即可
• 如果需要实现同值不并列的连续排名，可在现有公式基础上追加行号判断逻辑即可适配，常规业务场景下上述两个公式已覆盖绝大多数使用需求

内容的提问来源于stack exchange，提问作者Steve