前5个最高的特定行 [英] top 5 highest only specific rows

问题描述

我在A列中有这个，B列是文本后面的数字.

I have this in my column A and column B is the number after the text.

Access rights   
Question    2
Total - Access rights   2
Adobe software  
Error   1
Total - Adobe software  1
Autorisaties    
Question    1
Total - Autorisaties    1
AX  
Question    13
Error   14
Total - AX  27
Backup  
Question    1
Total - Backup  1
Computer    
Question    4
Error   6
Total - Computer    10

现在我有这个公式:

=INDEX($A$2:$A28;MATCH(1;INDEX(($B$2:$B$28=LARGE($B$2:$B$28;ROWS(D$1:D1)))*(COUNTIF(D$1:D1;$A$2:$A$28)=0););0))

获得最大值.因此，在这种情况下，第一行是Total AX. 当我向下拖动该公式时，它会显示错误，因为那是第二大数字.

to get the largest value. So in this case the first line is Total AX. And when I drag that formula down it says error because that is the 2nd largest number.

我想要的是，它查看的是total-值，因此在这种情况下，应首先按Total -AX，然后按Total -computer，再按总访问权限，然后再按总访问权限，然后再按其余权限，因为其中有些显示为1

What I want is that it look looks at the total - value so in this case it should first by Total -AX and then Total -computer and then total access rights and than the rest because some of them are showing 1 as total

推荐答案

给您一个想法:

E1中的公式:

=INDEX($A$1:$A$10,MATCH(LARGE((ISNUMBER(SEARCH("Total - ",$A$1:$A$10))*($B$1:$B$10)),ROW()),$B$1:$B$10,0))

通过 Ctrl Shift Enter

向下拖动....

编辑

如果有重复，建议在IMO中使用一个帮助列.像这样:

In case you have duplicates, an helper column would be advisable IMO. Like so:

E1中的公式:

=LARGE((ISNUMBER(SEARCH("Total - ",$A$1:$A$10))*($B$1:$B$10)),ROW())

F1中的公式:

=INDEX($A$1:$A$10,SMALL(IF(E1=$B$1:$B$10,ROW($A$1:$A$10)-ROW($A$1)+1),COUNTIF($E$1:E1,E1)))

都是通过 Ctrl Shift Enter

这篇关于前5个最高的特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！