如何在Excel中按組排名值？

Author: Sun Last Modified: 2025-08-23

在Excel中處理分組數據時，經常需要比較每個組內的值，例如按地區對銷售數字進行排名、按班級對考試成績進行排名或按類別對交易金額進行排名。雖然Excel提供了強大的數據排名工具，但在組內排名（也稱為“組內排名”或“條件排名”）則需要特定的方法。這在你需要評估不同類別之間的表現或識別頂尖和底層記錄而不想混雜各組結果時特別有用。以下方法探討了按組排名值的實際解決方案，使你在日常任務中更容易準確地解釋和分析你的數據。
A screenshot showing a grouped data set with ranked values in 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中輸入，拖動自動填充手柄向下填滿該公式以應用於數據集中的所有相關行。公式將自動調整以使用每一行的組和值，返回該組內的排名。 A screenshot showing the Excel formula applied to rank values by group in a worksheet

提示和注意事項：

如果你的範圍很大，記住要相應地更新單元格引用。
對於降序排名（例如，最高值為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 代碼。
公式解釋：輕鬆理解複雜的公式。
文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多