跳至主要内容

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

Author: Xiaoyang Last Modified: 2025-05-07

排行榜用于根据数值对公司或个人进行排名。假设你有一个班级学生成绩的列表,现在你想创建一个动态的前10名学生列表,如下图所示。本文将介绍一些在Excel工作表中创建前10名或前n名列表的公式。


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

在Excel 2019及更早版本中,要提取前10名列表或带条件的前10名列表,你应该使用以下公式:

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

1. 首先,你需要从数据区域中提取前10个值,请在空白单元格G2中输入以下公式,并向下拖动填充柄以获取前10个值,见截图:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
备注:在此公式中,B2:B20是你要获取前10个值的数据列表,而B2是数据列表的第一个单元格。

apply a formula to extract the top 10 values

2. 然后,在单元格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是你想从中提取前10个值名称的数据列表,B2:B20是包含所有值的数据列表,G2是从列B中提取的最大值所在的单元格,而B1是值列表的标题单元格。

use a formula to get relative item

3. 在获得第一个结果后,选择公式单元格并向下拖动填充柄以一次性获取其他名称,见截图:

drag and fill the formula to other cells

a screenshot of kutools for excel ai

使用 Kutools AI 解锁 Excel 魔法

  • 智能执行:执行单元格操作、分析数据和创建图表——所有这些都由简单命令驱动。
  • 自定义公式:生成量身定制的公式,优化您的工作流程。
  • VBA 编码:轻松编写和实现 VBA 代码。
  • 公式解释:轻松理解复杂公式。
  • 文本翻译:打破电子表格中的语言障碍。
通过人工智能驱动的工具增强您的 Excel 能力。立即下载,体验前所未有的高效!

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

有时,你可能需要基于条件获取前10名列表。例如,获取Class1的前10名姓名和分数,如下图所示。

create a dynamic top 10 list with criteria

1. 要获取前10名列表,你也需要使用此公式提取前10个分数:

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

2. 然后同时按下Ctrl + Shift + Enter键以获取第一个结果,然后向下拖动该公式以显示其他值,见截图:

apply a formula to extract the top 10 values based on criteria

3. 接下来,请复制并将以下公式粘贴到单元格I2中,并同时按下Ctrl + Shift + Enter键以提取第一个结果,然后拖动该公式以填充到其他单元格,前10名将显示出来。

=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)))

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

上述公式可能难以理解,如果你使用的是Office 365,通过其INDEX、SORT和SEQUENCE函数,你可以创建简单的公式来完成此任务。

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

要获取前10名数据列表,请使用以下公式:

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

然后,只需按Enter键,所有前10名列表的数据都会立即显示出来,见截图:

use a formula to create a dynamic top 10 list in Office 365

提示:

SORT函数:

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

  • array:你想要排序的单元格范围;
  • [sort_index]:对数组进行排序的列或行号。例如,要按数据范围的第二列排序,则排序索引为2;
  • [sort_order]:数字1(或省略)表示升序排序;数字-1表示降序排序;
  • [by_col]:排序方向。TRUE表示按列排序,FALSE或省略表示按行排序。

在公式中,我们这样使用SORT函数:

SORT(A2:B20,2,-1):表示按第二列降序排列单元格范围A2:A20。


SEQUENCE函数:

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

  • rows:返回的行数,
  • [columns]:返回的列数。如果省略,它将返回单列。
  • [start]:序列中的第一个数字。如果省略,它将从1开始。
  • [step]:每个数字之间的增量。如果排除,默认增量为1。

在此公式中,我们使用SEQUENCE(10)创建了一个从1到10的列表。

最后,将SORT和SEQUENCE函数都放入INDEX函数中:=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}),这将返回数据范围内的前10条记录,并返回第1列和第2列。


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

要显示带条件的前10名列表,你应该将FILTER函数嵌入到SORT函数中,如下所示:

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

然后只需按Enter键,所有基于给定条件的前10名列表数据都会立即显示出来,见截图:

another formula to create a dynamic top 10 list with criteria in office 365

提示:

FILTER函数:

=FILTER(array, include, [if_empty])

  • array:要过滤的单元格范围。
  • include:你用来过滤数组的条件,以获取TRUE或FALSE的结果数组,因此TRUE值将在过滤器中保留。
  • [if_empty]:如果没有匹配结果返回时显示的值。

在此公式中:=FILTER(A2:C25,B2:B25=F2)用于在范围A2:C25内进行过滤,其中B2:B25的值等于特定单元格F2。

最佳办公效率工具

🤖 Kutools AI 助手:基于智能执行生成代码创建自定义公式分析数据并生成图表调用 Kutools 函数等功能,彻底改变数据分析方式…
热门功能查找、高亮或标记重复项 | 删除空行 | 合并不丢失数据的列或单元格 | 四舍五入 ...
高级 LOOKUP多条件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 ....
高级下拉列表快速创建下拉列表 | 从属下拉列表 | 多选下拉列表 ....
列管理器添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域和列 ...
精选功能网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并数据 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...)...
排名前 15 的工具集12 种文本 工具添加文本删除特定字符等) | 50 多 种图表 类型甘特图等) | 40 多种实用 公式基于生日计算年龄等) | 19 种插入 工具插入二维码根据路径插入图片等) | 12 种转换 工具小写金额转大写汇率转换等) | 7 种合并与分割 工具高级合并行分割单元格等) | 还有更多...

使用 Kutools for Excel 提升您的 Excel 技能,体验前所未有的高效。 Kutools for Excel 提供超过 300 种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...


Office Tab 将标签式界面引入 Office,让您的工作更加轻松

  • 在 Word、Excel、PowerPoint 中启用标签式编辑和阅读
  • 在同一窗口的新标签页中打开和创建多个文档,而不是在新窗口中进行操作。
  • 将您的生产力提升 50%,每天为您减少数百次鼠标点击!