Excel中国式排名

1. 示例

假设数据区域为A1:A4,升序排名:

--+---+---+--
  | A | B | C
--+---+---+--
1 | 1 | 1 | 1
--+---+---+--
2 | 1 | 1 | 1
--+---+---+--
3 | 2 | 3 | 2
--+---+---+--
4 | 3 | 4 | 3
--+---+---+--

默认美式排名B,公式为:

=RANK(A1,A$1:A$4,1)

例如,有2个并列第1名,之后的排名为第3名。

中国式排名C,则无论有几个并列第1名,之后的排名仍应该是第2名,即并列排名不占用名次。

2. 中国式排名公式

=SUMPRODUCT((A$1:A$4<=A1)/COUNTIF(A$1:A$4,A$1:A$4))

2.1. 公式解释一:(A$1:A$4<=A1)

A$1:A$4<=A1
A$1:A$4<=A2
A$1:A$4<=A3
A$1:A$4<=A4

<=A1意思是找到小于等于A1的数据值,返回逻辑值。逻辑值参与运算时FALSE=0,TRUE=1,运算结果为:

{TRUE;TRUE;FALSE;FALSE} -> {1;1;0;0}
{TRUE;TRUE;FALSE;FALSE} -> {1;1;0;0}
{TRUE;TRUE; TRUE;FALSE} -> {1;1;1;0}
{TRUE;TRUE; TRUE; TRUE} -> {1;1;1;1}

2.2. 公式解释二:COUNTIF(A$1:A$4,A$1:A$4)

COUNTIF(A$1:A$4,A$1:A$4)
COUNTIF(A$1:A$4,A$1:A$4)
COUNTIF(A$1:A$4,A$1:A$4)
COUNTIF(A$1:A$4,A$1:A$4)

意思是求出A1:A4的每个数据在A1:A4的全部数据中有几个是相同的。运算结果为:

{2;2;1;1}
{2;2;1;1}
{2;2;1;1}
{2;2;1;1}

2.3. 公式解释三:(A$1:A$4<=A1)/COUNTIF(A$1:A$4,A$1:A$4)

(A$1:A$4<=A1)/COUNTIF(A$1:A$4,A$1:A$4)
(A$1:A$4<=A2)/COUNTIF(A$1:A$4,A$1:A$4)
(A$1:A$4<=A3)/COUNTIF(A$1:A$4,A$1:A$4)
(A$1:A$4<=A4)/COUNTIF(A$1:A$4,A$1:A$4)

运算结果为:

{0.5;0.5;0;0}
{0.5;0.5;0;0}
{0.5;0.5;1;0}
{0.5;0.5;1;1}

为1的值有2个,每行对应的排名项中值为0.5,全部加起来为1,最终实现了相同的数据值只计算一次排名。

2.4. 公式解释四:SUMPRODUCT((A$1:A$4<=A1)/COUNTIF(A$1:A$4,A$1:A$4))

SUMPRODUCT((A$1:A$4<=A1)/COUNTIF(A$1:A$4,A$1:A$4))
SUMPRODUCT((A$1:A$4<=A2)/COUNTIF(A$1:A$4,A$1:A$4))
SUMPRODUCT((A$1:A$4<=A3)/COUNTIF(A$1:A$4,A$1:A$4))
SUMPRODUCT((A$1:A$4<=A4)/COUNTIF(A$1:A$4,A$1:A$4))

运算结果为:

{0.5;0.5;0;0} -> 1
{0.5;0.5;0;0} -> 1
{0.5;0.5;1;0} -> 2
{0.5;0.5;1;1} -> 3

SUMPRODUCT函数用于在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。