Excel教學Office軟件超實用教學數學及統計函數

對比自己的銷售額排名

要計算排名,大家會聯想到用RANK函數。但要按條件計算排名,RANK用做到嗎?看來不行,那應該用什麼函數取代呢?

首先,我們認識一下RANK這個函數: RANK函數是排名函數,最常用的是求某一個數值在某一區域內的排名。 RANK函數語法形式:rank(number,ref,[order])
  • number :需要求排名的那個數值;
  • ref :排名的參照數值區域;
  • order:為0或1,0的情況默認不用輸入,得到的就是從大到小的排名,對應的輸入1是逆序排名。
如果要對比自己的銷售額排名,即是要多加一個「銷售員=自己的名字」的條件,你會發現RANK是做不到按條件排名的。
首先我們認識一下SUMPRODUCT函數
  • 這個函數被譽為計算全能王,有限的篇幅裡,這裡只能講最核心的知識。直接上結論,大家記住結論即可,以後有機會詳細講解。
  • SUMPRODUCT函數的萬能公式為:=SUMPRODUCT((條件1)*(條件2)*……*求和區域)
  • 可以實現單一條件求和、多條件求和。 因此,在這個案例中,SUMPRODUCT函數括號內的這一坨,最終實現的功能就是按照某一個條件求和。
而SUMPRODUCT如何得到對比自己的銷售額排名呢? 首先,以H2作例,公式是:
=SUMPRODUCT(($G$2:$G$33>G2)*($B$2:$B$33=B2))+1
以上公式有兩個條件:
  1. $G$2:$G$33>G2即是找出所有人的銷售額有多少個比自己的大。例如如果有100個銷售額比自己的大﹐證明了自己的銷售額是排名100+1,即是101位
  2. $B$2:$B$33=B2會再變窄範圍,只比較銷售員=自己的名字
以上兩個條件需要同時成立,就可以找出「對比自己的銷售額時比G2大」的數量,最後+1,就是自己的排名了。亦即是$192在「Tom」的10個銷售額中排行第8了。

下載示範檔案