身為老師或學校行政人員，您是否也曾為了一份長長的學生名單而頭痛？尤其在學期末，計算每位學生的「班級排名」總是一項繁瑣又耗時的任務。傳統的方法不外乎是手動篩選出各班資料，再逐一排名，過程不僅容易出錯，效率也極低。
本文將為您介紹一個強大的 Excel 技巧，讓您告別手動篩選的惡夢。我們將一步步教學，如何僅用一個函數就自動完成班級排名，無需分割任何資料。您將學會如何使用基礎的
RANK 函數快速處理校排名，並深入了解更進階的 SUMPRODUCT 函數，看它如何聰明地解決複雜的班級排名問題。基礎功：
RANK 函數，一秒搞定校排名首先，讓我們從最簡單的「校排名」開始。校排名是在整個資料範圍內進行排名，Excel 內建的
RANK 函數正是處理這個問題最直接、最快速的工具。假設學生的總分資料位於 H 欄，範圍從第 2 列到第 85 列，公式如下：
=RANK(H2, $H$2:$H$85, 0)
這個公式由三個簡單的參數組成：
•
H2：要進行排名的儲存格，也就是您想計算名次的那位學生的分數。•
$H$2:$H$85：比較的完整範圍，即所有學生的分數。這裡的 $ 符號非常重要，它能將範圍「固定」住，確保您下拉複製公式時，比較範圍不會跑掉。•
0：排序方式。0 代表遞減排序，也就是分數越高，名次越前面，這符合我們一般對成績排名的認知。真正的主角：
SUMPRODUCT，聰明計算班級排名RANK 函數雖然好用，但它的設計是在一個連續的範圍內進行比較，無法動態地為每一行加入「只看同班同學」這樣的篩選條件。因此，我們需要請出更強大的 SUMPRODUCT 函數。SUMPRODUCT 的核心邏輯其實非常直觀：其核心邏輯是：「找出同班同學中，分數比我高的人數，最後再加 1。」
假設 A 欄是班級，H 欄是總分，公式如下：
=SUMPRODUCT(($A$2:$A$85=A2) * ($H$2:$H$85>H2)) + 1
拆解公式：
SUMPRODUCT 為何如此神奇？這個看似複雜的公式，其實是由幾個簡單的條件判斷組合而成。讓我們一步步拆解它的運作原理：
第一步：篩選「班級」
($A$2:$A$85=A2) 這段條件式會逐一檢查 A 欄的所有班級，是否與當前學生（A2）的班級相同。如果相同，結果為 TRUE（在計算中等於 1）；如果不同，結果為 FALSE（等於 0）。最終會產生一個由 0 和 1 組成的陣列。第二步：比較「分數」
($H$2:$H$85>H2) 這段條件式會逐一檢查 H 欄的所有分數，是否比當前學生（H2）的分數更高。如果更高，結果為 TRUE (1)；反之則為 FALSE (0)。這也會產生一個由 0 和 1 組成的陣列。第三步：條件相乘 當這兩個陣列相乘時，Excel 會逐一比對。只有當某一列同時滿足「同班」(
TRUE, 即 1) 且「分數更高」(TRUE, 即 1) 時，結果才會是 1 * 1 = 1。只要任何一個條件不滿足（例如，不同班 FALSE, 0），相乘結果就必定是 0。這一步巧妙地篩選出我們真正關心的對象。第四步：加總與 +1
SUMPRODUCT 函數會將前面相乘後的所有結果（全部的 1 和 0）加總起來。這個總和，就精準地代表了「在同一個班級中，分數比我高的總人數」。最後，我們將這個數字 +1，就得到了最終的班級名次。舉例來說，如果班上沒有人比我高分（加總為 0），代表我的排名就在所有這些人之前，因此 0 + 1 就得到了正確的第 1 名。一個小細節：公式如何處理「同分」情況？
您可能已經注意到，公式中使用的是
> (大於)，而不是 >= (大於等於)。這個細節正是處理並列名次的關鍵。當班上有兩位學生並列第一名時，公式在計算他們的名次時，會判斷「比他們分數高的人數為 0」。因此，兩位學生的計算結果都會是
0 + 1 = 1，正確地得到並列第一名。接著，下一位分數的學生在計算名次時，公式會找出有 2 個人比他高分（也就是那兩位並列第一的同學），因此他的名次會是
2 + 1 = 3，也就是第三名。反之，如果我們錯誤地使用了
>= (大於等於)，那麼兩位並列第一的同學在計算時，會把自己也算進去（分數大於或等於自己），得到「有 1 個人分數不比我低」的結果，導致他們的名次變成 1 + 1 = 2（第二名），這顯然是錯誤的。這就是為什麼使用 > 是確保並列排名正確的關鍵。這種
1, 1, 3... 的排名方式，與 RANK 函數處理同分時的預設邏輯完全一致，確保了排名結果的標準化與正確性。總結
透過本文的介紹，相信您已經掌握了處理 Excel 排名的兩大利器：
• 校排名：使用簡單直覺的
RANK 函數。• 班排名：利用
SUMPRODUCT 函數的條件判斷邏輯，同時處理「班級篩選」與「分數比較」。學會這個技巧後，您將能徹底告別手動整理資料的繁瑣步驟，大幅提升工作效率。不妨思考一下，SUMPRODUCT 強大的多條件判斷邏輯，除了用在排名，還能應用在您工作中的哪些場景呢？
沒有留言:
張貼留言
注意：只有此網誌的成員可以留言。