Classificar dados repetidos no Excel com fórmula

Neste artigo é demonstrado como classificar dados repetidos no Excel de forma rápida e dinâmica, de forma que ao alterar os dados eles já são classificados.

No exemplo do gif animado acima temos a classificação do maior para o menor e vice-versa, feitos de forma imediata através de fórmula matricial. Veja também este artigo: Função SOMASE OU

Fórmula para classificação de repetidos

A fórmula utilizada para a classificação de valores pode ser o MAIOR e MENOR usando PROCV para retornar os dados, isso no caso de itens não repetidos no Excel.

A forma para resolver o problema da classificação de itens repetidos é uma fórmula matricial, ou seja, as células são tratadas individualmente para que sejam tratados gerando-os como valores únicos e depois aplicando o índice para retornar o valor conforme a sua posição.

Entendendo a fórmula

Veja as partes da fórmula passo-a-passo:

Fórmula: {=ÍNDICE($A$1:$B$9;CORRESP(MENOR(($A$1:$A$9)*(1+(LIN($A$1:$A$9)/1000));LIN());($A$1:$A$9)*(1+(LIN($A$1:$A$9)/1000));0);2)}

1. ($A$1:$A$9)*(1+(LIN($A$1:$A$9)/1000)): Nesta parte da fórmula nós temos a multiplicação da lista de A1:A9 multiplicado pelos valores de 1+o número da linha dividido por 1000. Veja que no caso da linha é utilizada LIN($A$1:$A$9) aonde temos os números de cada linha deste intervalo. O motivo é criar uma lista de valores únicos. Resultado: {90,09;80,16;10,03;60,24;50,25;30,18;20,14;30,24;10,09}
2. MENOR(($A$1:$A$9)*(1+(LIN($A$1:$A$9)/1000));LIN()): a Função menor é então aplicada sobre esta lista de valores e aplicado como segundo parâmetro a função LIN(), que neste caso terá a função de retornar a posição 1, 2, 3... desta lista. Este é o valor utilizado na busca do CORRESP. Resultado: {10,03}
3. ($A$1:$A$9)*(1+(LIN($A$1:$A$9)/1000)): Esta é a matriz procurada da função corresp, semelhante a utilizada na busca dos valores do valor a ser procurado. Resultado: {90,09;80,16;10,03;60,24;50,25;30,18;20,14;30,24;10,09}
4. CORRESP(MENOR(($A$1:$A$9) * (1+(LIN($A$1:$A$9)/1000)) ; LIN()) ; ($A$1:$A$9) * (1+(LIN($A$1:$A$9)/1000));0): O Corresp faz então a busca do valor conforme a sua posição na lista criada de forma matricial. Resultado: {3}
5. =ÍNDICE($A$1:$B$9 ; CORRESP(MENOR(($A$1:$A$9) * (1+(LIN($A$1:$A$9)/1000)) ;LIN());($A$1:$A$9) *(1+(LIN($A$1:$A$9)/1000));0);2): A parte final é a união dos dados do valor a ser buscado e da lista matricial de valores. A função Índice busca os dados na lista A1:B9 na posição definida pelo CORRESP, no caso da primeira linha 3. Resultado: 10.

Atenção: Na aplicação desta fórmula utilize CTRL+SHIFT+ENTER

Conclusão

O resultado da classificação automática, tanto de menor quanto para maior funciona perfeitamente com a função matricial, porém, assim como no uso de qualquer fórmula matricial ela deve ser usada com prudência porque ela pode pesar na sua planilha.

Veja mais em: Classificação automática no Excel

Download

Para o download do exemplo utilizado neste artigo preencha o seu nome e e-mail no formulário abaixo.