Fórmula genérica
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Resumo
Para classificar dinamicamente os dados que contêm apenas valores numéricos, você pode usar uma coluna auxiliar e uma fórmula criada com as funções RANK e COUNTIF. No exemplo mostrado, a fórmula em D5 é:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
onde "vendas" é o intervalo nomeado C5: C11.
Explicação
Observação: esta fórmula é a configuração para uma fórmula que pode extrair e exibir dados usando uma ordem de classificação predefinida em uma coluna auxiliar. Um exemplo aqui.
O núcleo desta fórmula é a função RANK, que é usada para gerar uma classificação de valores de vendas, onde o número mais alto é classificado como # 1:
=RANK(C5,sales)
Aqui, RANK usa o intervalo nomeado "vendas" (C5: C11) por conveniência. Por padrão, RANK atribuirá 1 ao valor mais alto, 2 ao segundo valor mais alto e assim por diante. Isso funciona perfeitamente, desde que os valores numéricos sejam únicos. No entanto, para lidar com valores numéricos que contêm duplicatas, precisamos usar a função CONT.SE para quebrar os empates. Isso é feito adicionando o resultado deste snippet ao valor retornado por RANK:
COUNTIF($C$5:C5,C5)-1
Observe que o intervalo é inserido como uma referência mista que se expandirá conforme a fórmula é copiada para baixo na tabela. Conforme escrito, essa referência incluirá a linha atual, portanto, subtraímos 1 para "zerar" a primeira ocorrência. Isso significa que a expressão retornará zero para cada valor numérico até que uma duplicata seja encontrada. Na segunda instância, a expressão retornará 1, na terceira instância, ela retornará 2 e assim por diante. Isso efetivamente quebra os empates e permite que a fórmula gere uma lista sequencial de números sem lacunas.
Depois que a fórmula estiver definida, os dados podem ser classificados pela coluna auxiliar. Ele também pode ser recuperado com INDEX usando os valores na coluna auxiliar.
Nota: Esta fórmula é adaptada de um exemplo no excelente livro Control + Shift + Enter, de Mike Girvin.
