對多個相同數(shù)值返回平均排位的公式

在Excel中對區(qū)域中的數(shù)值進(jìn)行排位時(shí)要用到RANK函數(shù)，RANK函數(shù)對于并列排位的處理方法是取其最小位次。例如對于一列數(shù)值“9，2，1，2，6，2，7，3，7”中有兩個“7”，按照從大到小的順序排位時(shí)這兩個數(shù)都返回“2”，即并列第二位，同時(shí)第三大的數(shù)字“6”返回“4”。有時(shí)可能不需要對多個相同數(shù)值進(jìn)行這樣的排位，而是要取其平均排位。例如上列數(shù)值中的“7”所占的位次為“2”和“3”（因?yàn)楸取?”小的最大數(shù)“6”的位次為“4”），取其平均值為“(2+3)/2=2.5”，即兩個相同的數(shù)值“7”的排位都為“2.5”。又如該數(shù)列中包含3個“2”，其所占的位次為“6”、“7”和“8”，則數(shù)值“2”的排位為“(6+7+8）/3=7”。要實(shí)現(xiàn)這樣的排位，可以用下面的一些公式，假如上列數(shù)值在A2:A10區(qū)域中。

方法一：添加修正因素

    按從大到小的順序排位時(shí)，在B2單元格中輸入公式，然后向下拖動填充柄填充公式：

    =RANK(A2,$A$2:$A$10)+(COUNT($A$2:$A$10)+1-RANK($A2,$A$2:$A$10,0)-RANK($A2,$A$2:$A$10,1))/2

    如果按照從小到大的順序排位，則上述公式改為：

    =RANK(A2,$A$2:$A$10,1)+(COUNT($A$2:$A$10)+1-RANK($A2,$A$2:$A$10,0)-RANK($A2,$A$2:$A$10,1))/2

    其中公式的后面部分：

    (COUNT($A$2:$A$10)+1-RANK($A2,$A$2:$A$10,0)-RANK($A2,$A$2:$A$10,1))/2

    即為修正因素。無論是按從大到小的順序排位還是按從小到大的順序排位，都無需修改該修正因素。

    方法二：數(shù)組公式

    按從大到小的順序排位時(shí)，在C2單元格中輸入數(shù)組公式：

    =SUM(--(A2<=$A$2:$A$10))-(SUM(--(A2=$A$2:$A$10))-1)/2

    按從小到大的順序排位時(shí)，則上述公式改為：

    =SUM(--(A2>=$A$2:$A$10))-(SUM(--(A2=$A$2:$A$10))-1)/2

    上述兩個公式為數(shù)組公式，公式輸入完畢后按Ctrl+Shift+Enter結(jié)束。

    方法三：RANK.AVG函數(shù)

    RANK.AVG函數(shù)是Excel 2010中新增的排位函數(shù)，用該函數(shù)可以對多個相同數(shù)值返回其平均排位。在Excel 2010中與早期版本中的RANK函數(shù)等價(jià)的是RANK.EQ函數(shù)，而RANK函數(shù)則被歸入到兼容性函數(shù)類別，保留該函數(shù)是為了保持與Excel早期版本的兼容性。

    RANK.AVG函數(shù)的語法與RANK函數(shù)類似，按從大到小的順序（降序）排位時(shí)，在D2單元格中輸入公式：

    =RANK.AVG(A2,$A$2:$A$10,0)

    其最后的參數(shù)“0”可省略。

    按從小到大的順序（升序）排位時(shí)，則上述公式改為：

    =RANK.AVG(A2,$A$2:$A$10,1)