Encontre o maior valor, o enésimo maior e outros antes dele? (ignorando duplicatas)

Question

Então eu tenho uma coluna, digamos A, que se parece com isso:

I
15
0
3
15
M
8
0
8
21
Q
0
5
0
0

Posso encontrar o maior valor com=MÁX(A1:A100) Posso encontrar o menor valor com=PEQUENO(A1:A100,1)

Como faço para encontrar o anterior ao maior e o anterior. Posso usar Large para chamar o enésimo valor, mas então como posso saber quantos valores N a função conta para saber quais posições ela usa referem-se aos últimos 3 (o maior e os 2 anteriores)?

Ignorando duplicatas - no exemplo o maior é 21, o anterior é 15 e o anterior é 8. Se eu fizer =LARGE(A1:A100,3) ele retornará 15, pois há dois deles na coluna. E estou procurando o 8 como o terceiro maior valor.

Abaixo você encontrará pessoas prestativas na Internet que forneceram uma tabela dinâmica, uma solução de fórmula de matriz, e finalmente consegui fazer uma fórmula simples.

Você pode fornecer vba para o trabalho caso insista que temos tudo, caso contrário, já há o suficiente.

Answer 1

Use uma tabela dinâmica para obter rapidamente uma versão desduplicada da sua lista (basta adicionar sua coluna aRótulos de linha), então use as funções LARGEe SMALLna lista desduplicada:

=LARGE(D4:D9,3)

Se desejar, você pode organizar a Tabela Dinâmica removendo oTotal gerale trocandoCabeçalhos de campodesligado, então ele apenas mostra sua lista desduplicada.

Answer 2

Com dados na colunaAcomo:

EmB1digitar:

=MAX(A:A)

EmB2insira a fórmula de matriz:

=MAX(IF(A$1:A$10<B1,A$1:A$10))

Então copieB2para baixo o quanto você precisar.

Fórmulas de matrizdeve ser inserido com Ctrl+ Shift+ Enterem vez de apenas com a Entertecla.

Answer 3

Com toda a ajuda legal que tenho recebido, isso me fez pensar se posso fazer o que a tabela dinâmica faz e descobri o seguinte: (mesmo sozinho LOL)

=IF(COUNTIF($A$1:A1,A1)<=1,A1,"")

Fazer uma coluna paralela aos dados de interesse resolve. (por exemplo, cole em C1 e copie)

Ele fornece a lista inicial desduplicada na qual você pode basear grandes e pequenos. Como uma solução simples, não deve interferir em nenhum material volátil que possa estar acontecendo em seus documentos. E eu, pelo menos, posso viver sem o vba. Quaisquer curvas inesperadas devem ser contidas com IFERROR, caso haja tipos de dados mistos.

Answer 4

@helena4 Experimente esta fórmula de matriz

=LARGE(IF(A1:A15 < LARGE(A1:A15,1),A1:A15),3)

Então aperteCtrl+Shift+Enter

ele irá ignorar as duplicatas.