Rank data by group in Excel 在 Excel 中依照群組排名資料

Excel Rank function can be used to rank for single column ("Charge") data easily.
Excel 使用 rank 函數可以很容易的排序單一行的數值，這裡以charge 為例。

How about if we want to rank "Charge" by "Method" like this ?
但是如果要先依照 Method 群組後，再排名 Charge呢?

This solution below here is using SUMPRODUCT function to do it:
type "SUMPRODUCT((A$2:A$6=A2)*(B$2:B$6>B2))+1" in C2 and copy this formula to C3 to C6.

以下的方法將以 SUMPRODUCT 函數來達成 
在 C2 格中輸入  "SUMPRODUCT((A$2:A$6=A2)*(B$2:B$6>B2))+1"並複制到其他儲存格。

A$2:A$6 is the area for  all Method and A2 is the Method ("A") for grouping.

B$2:B$6 is the area for all Charge and B2 is the Charge ("10") for ranking. .
>B2 means it will calculate the numbers of value that greater than B2 ("10").

SUMPRODUCT((A$2:A$6=A2)*(B$2:B$6>B2)) will return value 1("A", "20") that means in Method A, only  one value (Charge 20) is greater than Charge 10.

We finally add 1 by +1 to get the correct rank, for example: Rank 1 means 0 value greater than Rank1 value.


A$2:A$6 是 Method 的範圍，而 A2是 Method ("A")要分類的群組。

B$2:B$6 是 Charge 的範圍，而 B2是  Charge ("10")要排名的值。
>B2 表示會計算有幾個值是大於B2的值("10")


SUMPRODUCT((A$2:A$6=A2)*(B$2:B$6>B2)) 會回傳只有1個值("A", "20")。表示在 Method A 中只有一個Charge值("20") 是大於B2的值 (Charge "10)。

最後用+1加上一個名次來修正排名，例如第一名表示只有0個值大於第一名的值，所以名次上要加一。