如何在 Excel 中依群組對數值進行排名？

作者孫修改日期2025-06-30

在 Excel 中處理分組資料時，經常需要在各群組內比較數值，例如依地區排名銷售金額、依班級排名測驗成績，或依類別排名交易金額。雖然 Excel 提供強大的資料排名功能，但「群組內排名」（又稱「分組排名」或「條件式排名」）需透過特定方法才能實現。當您需要評估不同類別的表現，或找出各群組中的最佳與最差紀錄，同時避免群組間結果相互干擾時，此方法尤為實用。以下將介紹實用的群組內排名解決方案，助您在日常工作中更精準地解讀與分析資料。
顯示 Excel 中已分組資料集並包含排名值的螢幕截圖

依群組排名數值
VBA 程式碼－使用巨集自動化各群組內的數值排名

依群組排名數值

當您在不同群組內（例如依班級評分學生，或依地區列出銷售業績）需要進行排名時，Excel 並未內建直接的「依群組排名」功能。然而，只要運用精心設計的公式，就能高效達成分組排名，無需額外的資料處理步驟。

為此，您可以運用結合邏輯測試與彙總函數的陣列公式，確保每個數值僅在指定群組內進行比較，從而為每個資料點精準產生所需的排名。

請依照下列步驟操作：

將分組資料整理為欄位格式，例如群組（A2:A11）與數值（B2:B11）。
選取鄰接資料的空白儲存格——通常是緊鄰數值的第一列儲存格，例如 C2.
輸入下列公式：

=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1

此公式透過計算同一群組中有多少數值小於目前數值來運作。以下是各參數的說明：

($A$2:$A$11=A2)
→ 此條件會檢查範圍 A2:A11 中每個儲存格是否等於 A2 的值。
→ 並傳回由 TRUE/FALSE（或 1/0）組成的陣列，標示每列是否與 A2 屬於同一群組。
(B2<$B$2:$B$11)
→ 此條件用於檢查 B2:B11 範圍中有多少數值大於 B2.
→ 若 B2 小於指定值，則傳回 TRUE（1）；否則傳回 FALSE（0）。
*（乘法）
→ 此運算結合兩個條件：
群組相符（A2）
B2 中的數值小於其他數值
→ 因此僅計算屬於相同群組且數值較小的列。
SUMPRODUCT(...)
→ 加總符合兩個條件的資料列數。
+1
→ 排名從 1 開始（而非 0），因此我們在較小數值的計數上加 1.

在 C2 輸入公式後，向下拖曳自動填滿控點，即可將公式套用至資料集中所有相關列。公式會自動調整，引用各列對應的群組與數值，並傳回該群組內的排名。顯示在工作表中套用 Excel 公式以按群組對值進行排名的螢幕截圖

提示與注意事項：

若您的範圍較大，請務必同步更新對應的儲存格參照。
若要以遞減順序排名（例如最高數值為第 1 名），請將公式中的比較條件從 B2<$B$2:$B$11 修改為 B2>$B$2:$B$11.
為處理重複值，此公式會為同一群組中相同的數值指派相同的排名。若您需要連續且唯一的排名，建議搭配額外的輔助欄位使用。

這種基於公式的做法靈活且適用於大多數 Excel 分組表格結構，但對於極大資料集，由於依賴陣列運算，計算效能可能會降低。

VBA 程式碼－使用巨集自動化各群組內的數值排名

對於希望自動化排名流程或更高效處理大型資料集的使用者來說，撰寫 VBA 巨集是一種極具價值的解決方案。巨集不僅能自動執行重複性步驟、提供更高的自訂彈性，還能比複雜公式更快速地處理資料。此方法特別適合用於定期報表產出、重複性的排名任務，或當您希望避免工作表中堆積過多公式時。

開始前，請務必先儲存您的工作，並在 Excel 設定中啟用巨集。以下是撰寫與執行此解決方案的步驟：

按下 Alt + F11 鍵，立即啟用 VBA 編輯器！在彈出的 Microsoft Visual Basic for Applications 視窗中，點選插入 > 模組，並將下列程式碼貼到開啟的模組中：

Sub RankValuesByGroup()
    Dim DataRange As Range
    Dim GroupRng As Range
    Dim ValueRng As Range
    Dim OutCol As Range
    Dim dictGroups As Object
    Dim arrValues, arrRanks
    Dim i As Long, j As Long
    Dim GroupKey As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the data table range (including group and value columns)", xTitleId, Selection.Address, Type:=8)
    If DataRange Is Nothing Then Exit Sub
    
    Set GroupRng = Application.InputBox("Select the group column within your range", xTitleId, DataRange.Columns(1).Address, Type:=8)
    Set ValueRng = Application.InputBox("Select the value column to rank within your range", xTitleId, DataRange.Columns(2).Address, Type:=8)
    
    Set OutCol = DataRange.Offset(0, DataRange.Columns.Count).Resize(DataRange.Rows.Count, 1)
    OutCol.Cells(1).Value = "RankByGroup"
    
    Set dictGroups = CreateObject("Scripting.Dictionary")
    arrValues = ValueRng.Value
    arrRanks = ValueRng.Value
    
    ' Build group dictionaries for ranking
    For i = 2 To UBound(arrValues, 1)
        GroupKey = GroupRng.Cells(i, 1).Value
        If Not dictGroups.Exists(GroupKey) Then
            dictGroups.Add GroupKey, CreateObject("System.Collections.ArrayList")
        End If
        dictGroups(GroupKey).Add arrValues(i, 1)
    Next i
    
    ' Rank within each group
    For i = 2 To UBound(arrValues, 1)
        GroupKey = GroupRng.Cells(i, 1).Value
        Dim countLower As Long
        countLower = 0
        For j = 0 To dictGroups(GroupKey).Count - 1
            If dictGroups(GroupKey)(j) < arrValues(i, 1) Then
                countLower = countLower + 1
            End If
        Next j
        arrRanks(i, 1) = countLower + 1
    Next i
    
    ' Output results
    For i = 2 To UBound(arrRanks, 1)
        OutCol.Cells(i, 1).Value = arrRanks(i, 1)
    Next i
    
    MsgBox "Ranking by group completed.", vbInformation, xTitleId
End Sub

點選執行後，系統將彈出對話方塊，提示您選擇完整的數據區域、群組欄位與數值欄位。巨集會自動產生一個新欄位，內含各群組中每個數值的排名。

注意事項與疑難排解：

請確保所選欄位與您的資料相符：群組欄位與數值欄位務必正確對應。
若資料包含標題列，請根據您的資料結構調整程式碼中的初始迴圈索引，以確保排名正確無誤。
若要以遞減順序排名，請將比較條件相應修改為 If dictGroups(GroupKey)(j) < arrValues(i,1)。
若您遇到權限或巨集安全性警告，請前往檔案 > 選項 > 信任中心，檢查 Excel 的巨集安全性設定。

此 VBA 方法在進階或大規模應用中展現高度彈性與穩健效能，尤其適合整合至自動化報表工作流程。

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

智慧執行：透過簡易指令，輕鬆執行儲存格操作、分析資料，並建立圖表！
自訂公式：打造專屬公式，讓您的工作流程更順暢！
VBA 編碼：輕鬆撰寫並套用 VBA 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多