在 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 单元格中输入公式后，向下拖动填充柄，即可将该公式自动填充至数据集中的所有相关行。公式会智能调整，引用每行对应的组和值，精准返回该组内的排名。

提示与注意事项：

• 如果您的数据范围较大，请记得相应地更新单元格引用。
• 若要按降序排名（例如最高值为第 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

1. 单击运行，系统将弹出对话框，提示您选择完整的数据区域、组列和值列，随后宏将在新列中生成各组内每个值的排名。

注意事项与故障排除：

• 请确保所选列与您的数据保持一致：组列与值列必须准确对齐。
• 如果数据包含标题行，请根据您的数据结构调整代码中的初始循环索引，以确保排名准确无误。
• 若要按降序排名，请将比较条件相应修改为 If dictGroups(GroupKey)(j) < arrValues(i,1)。
• 如遇权限或宏安全警告，请前往文件 > 选项 > 信任中心检查 Excel 的宏安全设置。

这种 VBA 方法在更高级或大规模应用场景中兼具灵活性与稳健性能，尤其适合集成到自动化报告工作流中。

借助 KUTOOLS AI 解锁 Excel 的神奇功能

• 智能执行：只需输入简单命令，即可执行单元格操作、分析数据并创建图表。
• 自定义公式：生成量身定制的公式，助您优化工作流程！
• VBA 编码：轻松编写并运行 VBA 代码。
• 公式解析：轻松掌握复杂公式，一目了然！
• 文本翻译：轻松打破电子表格中的语言障碍！