我想做一張獎牌榜：先比金牌，金牌相同再比銀牌，最後才比銅牌。SORT 可以把隊伍照順序排好，不過旁邊的「第幾名」要另外算，完全同牌的兩隊也得保留同一個名次。
這篇用合成隊伍練習這套自訂規則，沒有套用任何賽事的官方排名辦法。隊名只是識別用，金、銀、銅都填非負整數；隊名留空代表這列不入榜，名字是 Zero 的隊伍則是有效的零獎牌隊伍。
先把規則與資料放好
比較方式是這樣：
- 金牌多的排前面，銀牌與銅牌再多也不能超過它。
- 金牌相同時，銀牌多的排前面。
- 金牌、銀牌都相同時，才比較銅牌。
- 三種都相同就並列。前面若有兩隊並列第 2，下一隊是第 4。
下面這份資料貼到 A1。空隊名那列的 A6 要真的留空，不要填「空白」兩個字；我刻意把隊伍順序打亂，讓名次不依賴目前誰在上面。
隊伍 金 銀 銅
Silver100 0 100 100
TwinB 2 1 1
Gold1 1 0 0
Top 3 0 0
0 0 0
Next 2 1 0
Bronze100 0 0 100
TwinA 2 1 1
Zero 0 0 0先手動比兩組：Top 的金牌最多，所以在前面；TwinA 與 TwinB 的三種獎牌都相同，名次應該一樣。Gold1 雖然只有一面金牌，仍然要排在沒有金牌的 Silver100 前面，這是這次規則最需要守住的地方。
用 COUNTIFS 算有幾隊在前面
我算名次時，會先問「有幾隊比我前面」，再加 1。想看單欄排名，可以先看 RANK 與百分位函式；這次要比較三欄，我用 COUNTIFS 分組計算。
在 E1 填「競賽名次」，E2 輸入這條，再向下填到 E10：
有幾隊金更多、同金銀更多，或同金銀而銅更多，再加 1
=IF(
A2="",
"",
1 +
COUNTIFS($A$2:$A$10, "<>", $B$2:$B$10, ">"&B2) +
COUNTIFS($A$2:$A$10, "<>", $B$2:$B$10, B2, $C$2:$C$10, ">"&C2) +
COUNTIFS(
$A$2:$A$10, "<>",
$B$2:$B$10, B2,
$C$2:$C$10, C2,
$D$2:$D$10, ">"&D2
)
)這條雖然長，裡面只有三組計數，而且彼此不重複：
- 第一組只數「金牌比這隊多」的隊伍。
- 第二組數「金牌相同，而且銀牌比這隊多」的隊伍。
- 第三組數「金牌、銀牌都相同，而且銅牌比這隊多」的隊伍。
每組都加上隊名不為空的條件，也就是 $A$2:$A$10,"<>"。最外層的 IF 則讓空隊名那一列自己也不顯示名次。$ 鎖住的是全部隊伍的比較範圍，B2、C2、D2 則要跟著目前的列往下換。
E2:E10 的結果如下：
| 列 | 隊伍 | E：競賽名次 |
|---|---|---|
| 2 | Silver100 | 6 |
| 3 | TwinB | 2 |
| 4 | Gold1 | 5 |
| 5 | Top | 1 |
| 6 | （A6 留空） | （空白） |
| 7 | Next | 4 |
| 8 | Bronze100 | 7 |
| 9 | TwinA | 2 |
| 10 | Zero | 8 |
TwinA 與 TwinB 前面都只有 Top，所以都得到 2。Next 前面有 Top、TwinA、TwinB 共三隊，因此得到 4。這種並列後跳號的名次，跟把每一列編成流水號不同喔！
排成金、銀、銅的順序
名次算好後，再把 A:E 整列一起排序。I1:M1 依序填「隊伍、金、銀、銅、競賽名次」，I2 輸入：
排除空隊名，依金銀銅遞減，同牌時按隊名遞增顯示
=SORT(FILTER(A2:E10,A2:A10<>""),2,FALSE,3,FALSE,4,FALSE,1,TRUE)FILTER 先排除空隊名；SORT 接到五欄資料後，依第 2、3、4 欄由大到小排，最後再依第 1 欄隊名由小到大排。FALSE 是遞減，TRUE 是遞增。
來看看結果，I2:M9：
| 隊伍 | 金 | 銀 | 銅 | 競賽名次 |
|---|---|---|---|---|
| Top | 3 | 0 | 0 | 1 |
| TwinA | 2 | 1 | 1 | 2 |
| TwinB | 2 | 1 | 1 | 2 |
| Next | 2 | 1 | 0 | 4 |
| Gold1 | 1 | 0 | 0 | 5 |
| Silver100 | 0 | 100 | 100 | 6 |
| Bronze100 | 0 | 0 | 100 | 7 |
| Zero | 0 | 0 | 0 | 8 |
最後那組隊名排序只是讓畫面穩定，TwinA 顯示在 TwinB 前面，兩隊仍然都是第 2 名。我沒有把隊名放進 E 欄的名次比較，所以它不會變成打破並列的條件。
如果只想取榜單前幾列，可以再看 SORTN 取前 N 名。SORTN 負責排序與保留列，這裡的競賽名次仍然由 E 欄計算。
為什麼不把獎牌乘權重？
常見做法是金牌乘 10000、銀牌乘 100，再加銅牌。我不採用這個版本，因為它得先保證下位獎牌不會累加到超過上一層；這份資料故意放了超過這個假設的隊伍。
想親自看錯在哪，可以在 G1 填「錯誤加權示範」，G2 貼這條，向下填到 G10。這條只用來重現錯誤，不要拿它當名次公式：
刻意錯誤的固定加權示範，不能用來取代逐欄比較
=IF(A2="","",B2*10000+C2*100+D2)同一份資料裡，Silver100 的加權值是 10100，Gold1 是 10000。如果直接照加權值由大到小排，沒有金牌的 Silver100 反而會超過有一面金牌的 Gold1。回到逐欄比較，E 欄給 Gold1 第 5、Silver100 第 6，才符合一開始訂的規則。
新增隊伍時要改哪裡？
這個練習把比較範圍固定到第 10 列。下一隊加在第 11 列時，要擴大 E 欄公式裡所有 A、B、C、D 的比較範圍，把名次公式填到新列，並一起擴大 I2 裡 FILTER 的 A:E 資料範圍與 A 欄條件範圍。
隊伍多了之後，還是先核對「排我前面的隊伍有哪些」，比直接盯著長公式好除錯。我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章排名與百分位函式整理：RANK、LARGE、PERCENTILE、PERCENTRANK、QUARTILE
RANK 系列、LARGE 與 SMALL、PERCENTILE 系列、PERCENTRANK 系列、QUARTILE 系列共 14 個函式的語法與範例，說明並列、INC 與 EXC 的差別，以及空白、文字與超出範圍時會怎樣。
SORTN 取前 N 名：最大 3 筆、最小 2 筆，並列時怎麼留
SORTN 的語法、取前 N 名與最後 N 名的寫法、顯示並列模式 0 到 3 用同一份資料逐一比較、第二層排序決定誰被截掉，以及搭配 FILTER 只看某個通路的排行榜。
SORT 與 UNIQUE：自動排序、自動去除重複
用 SORT 依一欄或多欄排序，用 UNIQUE 去掉重複的列，再把兩個函式套在一起，做出一份會跟著原始資料自動更新的清單。
留言討論
共 0 則留言