如何在Excel中分組排名？兩個公式輕鬆搞定！

教導主任給了我這次全校學生的期中考試成績表，讓我把每位同學所在班級排名名次以及在全校排名名次都列出來。

如果單純做一種排名，比如全校排名我們可能會想到：直接把所有人分數做降序然後從上到下1，2，3……遞增下去，但是即使這樣也有問題就是分數一樣的人排名不一樣了。幸運的是Excel提供了排名函式「RANK」。

「RANK」作用：返回一列數字的數字排位，語法；

「RANK」語法：RANK(需要排名的數字,排名數字列表,升序或降序)；

對於全校排名只需要在第一個學生全校排名列輸入公式：「=RANK(C2,C$2:C$12)」然後向下填充即可。

然後班級可以先以班級排序，保證同一個班級都在一起，然後以每個班級為單位使用一次RANK函式，比如本例中在D2單元格中輸入「=RANK(C2,C$2:C$5)」。

然後每個班級都做一次。這樣最終也能完成，不說有多少個班級就要寫多少次RANK函式，還有個致命的缺陷就是，如果我們把所有資料按全校排名升序排序，我們會發現班級排名裡很多資料都顯示了#N/A。顯然班級排序的寫法還是有侷限性的。

這要怎麼辦呢，還有什麼函式能達到排名的效果呢？

這兒還真有個比較特別的函式「SUMPRODUCT」，說它特殊是因為如果你在所有函式列表裡面看到這個函式，那麼你會看到這樣的解釋：屬於數學和三角函式分類下，作用是返回對應的陣列元素的乘積和。這實在無法讓人把它和排名聯想到一起，但是如果你點選這個函式看到詳細解釋：SUMPRODUCT函式返回對應範圍或陣列的個數之和，預設操作是乘法，但也可以進行加減除運算，在繼續看下去就會發現，咦這個函式有點意思。

我們來看看官網給的其中一個示例。這個例子是什麼意思呢？官網給出的解釋是：本示例使用 SUMPRODUCT 返回給定項和大小的總銷售額。我們看公式：「=SUMPRODUCT((B2:B7=B10)*(C2:C7=C10)*D2:D7」，首先「(B2:B7=B10)」我們可以解讀出在B2:B7單元格區域查找出值為B10單元格值的行，也就是Item列為Y的行，然後「(C2:C7=C10)」可以解讀出在C2:C7單元格區域查找出值為C10單元格值的行，也就是Size列為M的行，而「D2:D7」我們只能看出來是拿到前面篩選出結果的D列值，至於拿到值以後怎麼操作我們並無法看出來，但是結合前面篩選出來的記錄是第3行和第6行，對於D列值分別為21和41，在看看D10單元格中的公式最後結果是62我們可以推測出這個公式是返回指定Item列和Size列記錄的Sold列和。

之所以在這裡解釋這麼多，是為了來說明我們怎麼靈活的使用這函式來實現官網沒介紹的功能。

透過上面的解釋我們可以推斷出「(B2:B7=B10)*(C2:C7=C10)」是篩選作用，「D2:D7」是求和作用。那麼「=SUMPRODUCT((B2:B7=B10)*(C2:C7=C10)」是否返回的就是篩選出來的資料條數呢？我們再把思維開啟點，這裡可以寫多個篩選條件，我們是否可以想成一個對應班級篩選，一個對應分數篩選，再把思維開啟點，篩選可以寫成「(C2:C7=C10)」也就可以寫出「(C2:C7>C10)」，再想想分數，大於指定的分數的個數是不是就可以轉換為排名呢。

最後我們得到公式：「=SUMPRODUCT((A$2:A$12=A2)*(C$2:C$12>C2))+1」。加1是因為大於當前值的個數加1正好就是當前值的排名。

然後我們在對全校排名進行升序，結果如下：

可以看到結果也是正常的。我們來看看整個操作過程：

我結合我們這個例子給大家好好解釋這個函式怎麼用來做排序。

從這個例子中我們可以發現只有真正理解了一個函式才能用好這個函式。