如何在 Excel 中按組對值進行排名？

在 Excel 中處理分組數據時，通常需要比較每個組內的值，例如按地區對銷售數字進行排名、按班級對考試成績進行排名或按類別對交易金額進行排名。雖然 Excel 提供了強大的數據排名工具，但組內排名（也稱為“組-wise 排名”或“條件排名”）需要特定的方法。這在您需要評估不同類別之間的表現或識別頂部和底部記錄時特別有用，而不會將各組的結果混合在一起。以下方法探討了按組對值進行排名的實際解決方案，使您能夠更準確地解讀和分析日常任務中的數據。

按組對值進行排名
VBA 代碼 - 使用宏自動化每個組內的值排名

按組對值進行排名

對於需要在不同組內對值進行排名的情況，例如按班級對學生進行評分或按不同地區列出銷售數據，Excel 沒有直接的“按組排名”功能。然而，一個精心設計的公式可以高效地實現組-wise 排名，而無需額外的數據操作。

要做到這一點，您可以使用結合邏輯測試與聚合函數的數組公式。這種方法允許您僅在指定的組內比較每個值，從而為每個數據點生成所需的排名。

請按照以下步驟操作：

• 將您的分組數據準備在列中，例如組（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，拖動自動填充手柄向下填充該公式到數據集的所有相關行。該公式將自動調整以使用每行的組和值，返回該組內的排名。

提示和注意事項：

• 如果您的範圍很大，請記住相應地更新單元格引用。
• 對於降序排名（例如，最高值為 1），將公式比較從 B2<$B$2:$B$11 修改為 B2>$B$2:$B$11。
• 要處理重複值，此公式會在同一組內為相同值分配相同的排名。如果您需要連續唯一的排名，請考慮使用額外的輔助列。

這種基於公式的解決方案靈活且易於應用於 Excel 中大多數分組表結構。然而，對於非常大的數據集，由於依賴數組邏輯，計算性能可能會減慢。

VBA 代碼 - 使用宏自動化每個組內的值排名

對於希望自動化排名過程或更高效處理大型數據集的用戶，編寫 VBA 宏可能是一種有價值的方法。宏可以自動化重複步驟、提供更多自定義選項並快速處理數據，相比複雜的公式更具優勢。這對於計劃報表生成、重複排名任務或當您想避免工作表中公式混亂的情景來說是理想的。

在繼續之前，務必保存您的工作並在 Excel 設置中啟用宏。以下是您可以編寫和運行此解決方案的方法：

1. 按下 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

2. 點擊 運行。將彈出一個對話框，提示您選擇完整的數據範圍、分組列和值列。然後宏將生成一個新列，其中包含每個值在其組內的排名。

注意事項和故障排除：

• 確保列選擇與您的數據匹配：分組列和值列必須正確對齊。
• 如果包括數據標題，請根據數據結構調整代碼中的初始循環索引以獲得正確的排名。
• 要按降序排名，相應地修改比較 If dictGroups(GroupKey)(j) < arrValues(i,1)。
• 如果您遇到權限或宏安全警告，請檢查 Excel 的宏安全設置，位於 文件 > 選項 > 信任中心。

此 VBA 方法為更高級或擴展應用提供了靈活性和穩健的性能，特別是在與自動化報告工作流程集成時。

使用 Kutools AI 解鎖 Excel 的魔法

• 智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
• 自訂公式：生成量身定制的公式，簡化您的工作流程。
• VBA 編碼：輕鬆編寫和實現 VBA 代碼。
• 公式解釋：輕鬆理解複雜的公式。
• 文本翻譯：打破電子表格中的語言障礙。

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