KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中按组对数值进行排名？

作者孙修改日期

在 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 中启用宏设置。以下是编写和运行此解决方案的步骤：

  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 for excel ai 的截图

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

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

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱