Excel：按Value升序、Days降序级联排序生成Sort ID列的公式需求

现有两列数据Days和Value，需在不实际排序数据的前提下，通过公式生成第三列，得到该行按以下规则排序后的位置ID（优先级）：

• 第一排序规则：Value 从小到大
• 第二排序规则：Days 从大到小

示例数据 #

公式方案 #

适用于Excel 365/2021（动态数组版本） #

假设数据从A2单元格开始（A列=Days，B列=Value），在C2单元格输入以下公式后下拉填充：

=RANK.EQ(B2,$B$2:$B$8)+COUNTIFS($B$2:$B$8,B2,$A$2:$A$8,">"&A2)

或使用自动溢出的动态数组公式（输入后无需下拉）：

=BYROW(A2:B8,LAMBDA(x,RANK.EQ(INDEX(x,2),B2:B8)+COUNTIFS(B2:B8,INDEX(x,2),A2:A8,">"&INDEX(x,1))))

公式逻辑

1. RANK.EQ(B2,$B$2:$B$8)：计算当前Value在所有值中的升序排名（相同值共享排名）
2. COUNTIFS(...)：统计同Value分组内，Days大于当前行的行数，用来调整同组内的排序——Days越大优先级越高，因此当前行的位置需要加上这些行数，让Days更小的行排在同组后方

适用于旧版Excel（无动态数组） #

同样假设数据从A2开始，在C2单元格输入公式后下拉：

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

公式逻辑

1. ($B$2:$B$8<B2)：统计所有Value小于当前行的行数（优先级更高的行）
2. ($B$2:$B$8=B2)*($A$2:$A$8>A2)：统计同Value分组内，Days大于当前行的行数（优先级更高的行）
3. 两者求和后加1，得到当前行的最终位置ID

内容的提问来源于stack exchange，提问作者Ben.Name