Excel Função Grande com Duplicados

Question

Estou tentando aplicar uma função LARGE para obter os 5 maiores valores de uma coluna ignorando duplicatas. Fiz algumas pesquisas e encontrei algumas soluções propostas, mas elas não parecem funcionar para mim. Na minha aplicação real, o intervalo é encontrado por uma fórmula de matriz, por isso não tenho certeza se fórmulas não matriciais funcionarão aqui. Minha função original era {= LARGE (IF ($ A: $ A = $ A2, $ B: $ B), 2)} arrastada pela coluna. Substitua 2 por 3 pelo terceiro maior, 4 pelo quarto maior, etc. Isso funcionou, exceto no caso de duplicatas.

Por exemplo:

Os dados estão na coluna 1 e a saída desejada está na coluna 2:

1    5 
2    4
2    3
3    2 
3    1
3
4
4
5

Coisas que tentei:

1. {= GRANDE (SE (A: A < LARGE (A: A, i-1), A: A), 1)} para o i-ésimo maior valor.

   -Inputar isso dá 3 para ambos i = 3 e i = 4.

2. = GRANDE (A: A, CONTAR (A: A, MAX (A: A)) + 1)

   -Isso não parece ser diferente de apenas LARGE (A: A, 2)

3. {= MAX (SE (A: A < LOOKUP (9.99999999999999E + 307, A: A), A: A))}

   -Esta recupera o valor 4, mas não tenho certeza de como generalizá-lo para o k-ésimo maior valor.

As soluções VBA também estão bem. Tentei codificar um, mas não sabia como codificar na fórmula da matriz IF ($ A: $ A = $ A2, $ B: $ B).

    

Answer 1

Em B1 digite:

=LARGE($A$1:$A$9,1)

Em B2 , insira a Fórmula de matriz :

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

e copie para baixo. Fórmulas de matriz devem ser digitadas com Ctrl + Deslocamento + Enter em vez de apenas o Enter chave.

Basicamente, excluímos itens encontrados anteriormente do intervalo Grande () / Max () .