Excel - 提取前 5 个值 [英] Excel - extracting top 5 values

问题描述

我想根据每位员工的销售额(没有数据透视表/自动过滤器)提取前 5 名玩家.

参考我的输入输出截图

单元格 A1 中的公式:

=INDEX(A$13:A52,AGGREGATE(15,6,ROW($1:$40)/(B$13:B$52=B1),COUNTIF(B$1:B1,B1)))

单元格 B1 中的公式:大 (B$13:B$52,ROW())

I would like to extract the top 5 players based on the sales by each employee (without Pivot Table / Auto filter).

Refer my input and output screenshot

Snapshot

Any suggestions, how to obtain first top 5 ranks (even if repeated; as shown in the screenshots)

I have verified Extract Top 5 Values for Each Group in a List without VBA and some other links also.

Thanks in advance for your time and consideration! Please let me know if my request is unclear and/or if you have any specific questions.

解决方案

This is what I use to track the top 5 absentees... Edit to suit your needs.

Formula in cell A1:

=INDEX(A$13:A52,AGGREGATE(15,6,ROW($1:$40)/(B$13:B$52=B1),COUNTIF(B$1:B1,B1)))

Formula in cell B1: LARGE(B$13:B$52,ROW())

这篇关于Excel - 提取前 5 个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！