Excel - Encontrando o enésimo maior valor para cada membro de uma lista

Question

Eu tenho um monte de dados que consiste em valores em sites diferentes como este:

Site  Value
A      1
A      2
A      5
A      7
B      5
B      7
B      11

O número de valores em cada site pode variar. Desejo extrair o enésimo maior valor para cada site, por exemplo. para n = 2

Site  Value
A     2
B     7

Existe uma maneira mais fácil de fazer isso do que adicionar manualmente algumas linhas em branco entre cada site e usar LARGE?

    

Answer 1

Você pode agrupar LARGE em um FI e criar uma Fórmula de Matriz para obter o enésimo maior valor de um subconjunto. Uma única fórmula funcionará para cada site.

NasColunasAeamp;Béseusdados.CrieumaListaMestradeSitesnaColunaDecoloqueoenésimovalordoqualificadordelargetdigamos2,34oquefornecessárionacolunaE.Agora,emF,coloqueafórmula

=LARGE(IF($A$1:$A$7=D1,$B$1:$B$7,""),E1)

Coloque-o como uma fórmula de matriz pressionando CTRL + SHIFT + ENTER e arraste-o para baixo.

    

Answer 2

Relacionado a minha resposta em outra pergunta .

Você pode criar uma matriz ou intervalo usando a combinação dessas fórmulas

INDEX( value_range, MATCH( lookup_value, lookup_range, 0 )) para o início do intervalo

INDEX( value_range, MATCH( lookup_value, lookup_range, 1 )) para o final do intervalo

Então, na sua pergunta, você pode usar isso combinado com LARGE

=LARGE(INDIRECT(
   INDEX($B$1:$B$7, MATCH($C1, $A$1:$A$7, 0))&":"
   INDEX($B$1:$B$7, MATCH($C1, $A$1:$A$7, 1))
   ), 2)

Observação: você precisa classificar os sites. Ascendente ou descendente é bom, apenas para garantir que os sites estão agrupados.

Onde a coluna B refere-se à sua lista de valores e a coluna A à sua lista de sites.

$C1 refere-se ao site que você deseja obter o enésimo valor (no seu exemplo, que deve ser "A")