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

如何在 Excel 中创建动态的前 10 名或前 N 名列表？

作者Xiaoyang修改日期

在众多项目和业务流程中，常常需要根据绩效或数值对个人、组织、产品等实体进行排名。“前 N 名列表”能有效凸显表现最优的条目，例如按成绩排名的优秀学生、销售业绩领先的销售人员，或收入最高的部门。例如，您可能拥有一份学生成绩表，并希望动态提取前 10 名高分学生，用于颁奖、分析或跟踪教育成果（如下图所示）。在 Excel 中创建动态的前 10 名（或前 N 名）列表，可在数据更新时自动刷新结果，既节省时间，又避免手动排名可能引发的错误。本指南将为您介绍多种实用方法——包括公式、数据透视表和 VBA 宏——助您高效构建动态前 10 名或前 N 名列表，灵活应对各类数据分析需求。

在 Excel 中创建动态前 10 名列表

在 Excel 2019 及更早版本中，创建动态的前 10 名（或前 N 名）列表需结合多个公式，以同时提取最高值及其对应的姓名或 ID。该方法应用广泛，特别适用于需要列表随数据变化自动更新的场景。以下操作演示了如何使用经典 Excel 公式实现这一目标。这些公式灵活实用，无需依赖任何特殊 Excel 加载项，但相较于现代动态数组函数，设置过程略显复杂。

用于创建动态前 10 名列表的公式

1. 首先，从数值区域中提取前 10 个最高值。在空白单元格（例如 G2）中输入以下公式，然后向下拖动填充柄，即可生成动态的前 10 名数值列表。参见下图：

=LARGE($B$2:$B$20,ROWS(B$2:B2))
注意：此处，B2:B20 为分数或数值范围，而 B2 是该列的首个单元格。请根据您的数据大小和位置调整这些单元格引用。

应用公式提取前10个值

2. 接下来，为显示与这些最高值对应的姓名（或 ID），请在 F2 单元格中输入以下公式。此公式为数组公式，输入后需按 Ctrl + Shift + Enter 确认，即可查找与刚刚提取的最高值对应的姓名：

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))
参数说明：
-A2:A20 为提取姓名的范围；
-B2:B20 为分数或数值范围；
-G2 为上方公式得出的最高值；
-B1 为数值列表的标题，用于 ROW 计算中的偏移。
此公式可动态关联最高值与其对应姓名。若数值范围包含重复值，COUNTIF 可确保每个匹配姓名仅与其分数一同出现一次。

使用公式获取相关项

3. 提取首个结果后，选中 F2 单元格中的公式，向下拖动填充柄，将公式复制至所需行数，即可动态扩展结果，完整呈现前 N 名条目的姓名及其对应分数。参见下图：

拖动并将公式填充到其他单元格

kutools for excel AI 的截图

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

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

在某些分析任务中，您可能需要一个仅显示满足特定条件的前 N 名条目的列表——例如，将前 N 名结果限定于某个特定小组、团队或类别。比如，您或许希望从包含多个班级成绩的总表中，仅提取“1 班”的前 10 名成绩。以下是针对此类场景使用公式的方法：

根据条件创建动态前10名列表

1. 首先，从数据集中提取满足指定条件（例如“1 班”）的前 10 个最高值。在目标单元格（例如 J2）中输入以下公式：

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2. 输入公式后，按 Ctrl + Shift + Enter 将其确认为数组公式，然后向下拖动填充柄即可填充至其他单元格。该公式将返回符合所选条件（例如所有“1 班”的成绩）的前 10 个最高值。

应用公式根据条件提取前10个值

3. 要根据您的条件列出这些最高值对应的名称，请将下方公式复制粘贴到单元格 I2 中，然后按 Ctrl + Shift + Enter 以数组公式方式输入。随后向下填充，即可快速生成完整的名称列表。

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

在 Office365 中使用公式创建动态前10名列表

请务必调整公式中的区域，使其与您的实际数据结构完全匹配。请注意，对大型区域使用数组公式可能会影响性能。若前 10 名中存在重复分数，该公式将正确处理并列情况，在成绩相同时显示所有对应的学生姓名。

在 Office 365 中创建动态前 10 名列表

早期版本的 Excel 需要组合多个函数并依赖数组公式，而 Office 365（以及 Excel 2021）引入了 INDEX、SORT、SEQUENCE 和 FILTER 等动态数组函数，显著简化了操作流程。这些函数让创建动态前 10 名列表变得更加轻松高效，有效降低出错风险，尤其适用于持续增长或频繁变动的数据表。若您的数据环境不断更新，这些函数将优化分析流程，助您更快做出业务决策。

创建动态前 10 名列表的公式

要在 Office 365 中提取并动态显示前 10 名列表，请在目标输出单元格中输入下方公式。您只需根据实际需求调整区域和数值，公式将在数据更新时自动呈现最新的前 10 名结果。

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

只需按下 Enter 键，完整的前 10 名列表将立即显示，并动态更新——新增数据或修改分数后，排名即刻刷新！

在 Office365 中使用公式创建动态前10名列表

提示：

SORT 函数：

=SORT(array, [sort_index], [sort_order], [by_col])

  • array：要排序的数据区域。
  • [sort_index]：用于排序的列号，典型成绩表中通常为第二列。
  • [sort_order]：使用 1 表示升序，-1 表示降序；若要获取最高分，请使用-1.
  • [by_col]：按列（TRUE）排序，或按行（FALSE 或省略）排序。

例如：SORT(A2:B20,2,-1) 可按第二列对 A2:B20 区域进行降序排序。

SEQUENCE 函数：

=SEQUENCE(rows, [columns], [start], [step])

  • rows：要返回的行数，例如输入 10 即可显示前 10 名列表。
  • [columns]：（可选）指定要返回的列数。
  • [start]：（可选）起始值。
  • [step]：（可选）递增步长。

SEQUENCE(10) 生成从 1 到 10 的数字序列，以便 INDEX 能够提取前 10 个已排序的结果。

结合上述函数，输入 =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}) 即可动态生成前 10 名的两列列表。

带条件的动态前 10 名列表公式

如果您需要为特定组（例如“班级 1”）提取前 10 名，这些高级 Office 365 函数可在仅保留满足条件的行的同时，轻松生成前 N 名列表。请将下方公式粘贴至目标位置，并根据实际需求调整区域和条件单元格：

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

输入公式后，只需按下 Enter 键，系统便会立即显示根据指定条件筛选并排序的前 10 名列表，并在您修改数据或条件时自动更新。

另一种在 Office365 中根据条件创建动态前10名列表的公式

提示：

FILTER 函数：

=FILTER(array, include, [if_empty])

  • array：要筛选的单元格区域。
  • include：包含条件（例如，等于指定班级）。
  • [if_empty]：（可选）当没有结果满足条件时显示的内容。

=FILTER(A2:C25,B2:B25=F2) 仅返回 B 列中与 F2 单元格值匹配的行。

使用数据透视表创建动态前 10 名列表

数据透视表：交互式自动显示前 N 名结果

构建动态前 N 名列表的另一种高效方法是利用 Excel 的数据透视表功能。该方法尤其适用于大型数据集、交互式分析（例如快速调整前 N 项数量或应用筛选条件），以及希望避免使用复杂公式的情形。数据透视表操作直观、用户友好，并能在源数据更新时自动刷新，是打造共享仪表板或动态报告的理想之选。

使用数据透视表创建动态前 N 名列表的步骤如下：

  1. 单击数据表中的任意位置，然后依次点击插入 > 数据透视表。
  2. 在“数据透视表”对话框中，选择数据透视表的放置位置，然后单击确定。
  3. 将“姓名”（或类似标识符）字段拖入行区域。
  4. 将“分数”（或数值列）字段拖入值区域。默认通常为“求和项”或“计数项”——而前 N 名列表一般需使用“求和”或“最大值”。如有需要，右键单击并选择值字段设置进行调整。
  5. 右键单击“分数”列中的某个数值，选择排序> 降序排列大到小，即可对该列进行降序排序。
  6. 要限制为前 N 个结果，请单击“行标签”旁边的下拉箭头，选择值筛选 > 前10项……，设置所需数量（例如前 10 项）及筛选字段，然后点击确定。

您的数据透视表现已支持动态显示前 10 名（或您自定义的任意 N 值）。如需调整前 N 名，只需重新进入筛选设置即可。若数据有更新，刷新数据透视表便可即时获取最新排名。

此方法的优势在于设置快捷、排序便捷且支持交互式调整。但请注意，数据透视表无法自动为其他列添加对应的行，除非这些列已包含在“行”或“值”区域中。高级用户还可通过分组、创建切片器，或将 Top N 筛选器集成到仪表板中，进一步自定义报告。

使用 VBA 创建动态前 10 名列表

VBA 宏：自动生成并刷新前 N 名列表

对于需要自动化提取并刷新动态前 N 名列表的用户（尤其是在处理大量或频繁更新的数据时），使用 VBA 宏是理想之选。宏不仅能显著减少重复性操作，还能确保结果的一致性。您只需编写一个例程，每次运行时自动对数据排序，并将前 N 行精准复制到指定位置。

使用 VBA 宏创建动态前 N 名列表的步骤如下：

  1. 单击开发工具 > Visual Basic，即可打开 VBA 编辑器。（如果未看到“开发工具”选项卡，请前往“文件” > “选项” > “自定义功能区”，并启用“开发工具”。）
  2. 在 VBA 窗口中，单击插入 > 模块，即可添加新模块。
  3. 将以下 VBA 代码粘贴到模块中：
Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
    Dim DataRange As Range
    Dim OutputRange As Range
    Dim N As Integer
    Dim ws As Worksheet, tempWS As Worksheet
    Dim xTitleId As String
    Dim LastCol As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
    Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
    N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
    
    If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
    
    ' Create a temporary worksheet to avoid sorting original data
    Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    DataRange.Copy tempWS.Range("A1")
    
    ' Determine last column for sorting key
    LastCol = DataRange.Columns.Count
    
    ' Sort in temporary sheet
    tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
    
    ' Copy headers and top N rows to output
    tempWS.Rows(1).Copy Destination:=OutputRange
    tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
    
    ' Optional: Delete temporary sheet
    Application.DisplayAlerts = False
    tempWS.Delete
    Application.DisplayAlerts = True
    
    Application.CutCopyMode = False
End Sub

4. 要执行该宏，请确保您的数据已正确组织为带标题的表格。按下 F5 键，或点击 VBA 编辑器中的运行按钮按钮，系统将提示您：

  1. 请选择您的数据区域（包含标题，以确保正确排序）。
  2. 请选择用于粘贴结果的输出单元格。
  3. 请输入数字 N（例如，10 代表前 10 名）。

该宏会将前 N 条记录（含标题）复制到您指定的位置。

首次测试时，建议在工作簿的备份或副本中操作。若出现错误（例如选错区域），请重新运行，并确保所选区域和数据布局正确无误。

此方案非常适合自动化重复性报告任务、创建仪表板，或在无需手动输入公式或排序的情况下快速生成前 N 名报告。您还可以进一步自定义 VBA 脚本，实现更复杂的排名逻辑，例如按指定列排序，或将结果导出至其他工作簿。

故障排除：若宏未按预期运行，请检查数据表是否包含正确的标题，修正数据类型以避免排序问题，并确保每次提示时准确选择单元格引用。运行宏前请务必保存工作，以防数据被意外修改。

总之，Excel 提供了多种方式来生成和维护动态前 N 名列表——从传统公式、功能强大的 Office 365 函数，到用于交互式分析的数据透视表，以及适用于高级自动化的 VBA 宏。请选择最适合您工作流程与数据规模的方案：公式适用于大多数手动分析场景；Office 365 函数在简洁性与功能强大之间实现最佳平衡；数据透视表则擅长快速、灵活地汇总数据；而 VBA 尤其适合自动化大规模、重复性的排名任务。请始终验证公式或代码的完整性，并在项目推进过程中，根据数据结构的变化及时调整单元格引用。

最佳办公效率工具

🤖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 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱