Dicas & Truques,Material de Ensino
Ranqueamento em Excel
maio
O ranqueamento em Excel é uma das necessidades mais corriqueiras em se tratando de análises diversas, ela consiste na ordenação crescente ou decrescente de valores para se extrair uma priorização ou relevância nas informações, podendo ser estas em forma de consumo, venda, faturamento ou outros parâmetros quantitativos, ou seja, que podem ser mensurados.
Por exemplo, quando se pede um relatório dos TOP 10 em vendas, entende-se que é o rank dos 10 Clientes mais importantes da empresa nas vendas, sendo assim, as informações usualmente são ordenadas de forma decrescente, do maior para o menor. No entanto, em uma competição de corrida, quando se solicita o rank dos menores tempos, esta informação normalmente é ordenada de forma crescente, do menor para o maior.
O ranqueamento pode ser apresentado não somente em forma de tabelas, mas também comumente em gráficos de barras verticais ou horizontais, e com isso, podendo compor um Painel de Resultados ou Dashboards.
Ranqueamento
Para exemplificar as técnicas de ranqueamento iremos montar um relatório dos TOP 10 em vendas/ano de uma empresa X, mas para isso utilizaremos a seguinte base de dados e fórmulas.
Fórmula para ordenação decrescente de valores:
=MAIOR(Intervalo_Valor;Numero_Posicao)
Fórmula para associar o valor ordenado ao respectivo Cliente:
=ÍNDICE(Matriz;CORRESP(Valor_Venda; Intervalo_Valor;0);Coluna_Matriz)
Para entendermos um pouco melhor a aplicação desta fórmula vamos observar o esquema logo a seguir.
Matriz: $A$2:$B$16
Intervalo_Valor: $B$2:$B$16
Valor_Venda: F2; F3; F4…F11
Numero_Posicao: D2; D3; D4…D11
Ordenação decrescente dos 10 maiores valores (TOP 10):
- Na célula F2 a fórmula, =MAIOR($B$2:$B$16;D2) ordenará de forma decrescente os valores do intervalo $B$2:$B$16, tomando-se como referência a célula D2, ou seja, a posição 1 do rank.
- Após arrastar a fórmula até a linha 11, posição 10 do rank, obteremos de forma ordenada os 10 maiores valores do intervalo $B$2:$B$16.
Associação dos Clientes conforme a ordenação:
- Como a nossa busca é da direita para a esquerda na matriz $A$2:$B$16, não será possível utilizar a função PROCV, sendo assim, utilizaremos a fórmula combinada, ÍNDICE e CORRESP na célula E2.
- A função CORRESP(F2;$B$2:$B$16;0) fornecerá a posição que o valor F2 ocupa no intervalo $B$2:$B$16, sendo este valor 8 (linha 9). O “0”, significa que queremos obter a posição exata, e não aproximada.
- A fórmula, =ÍNDICE($A$2:$B$16;CORRESP(F2;$B$2:$B$16;0);1), nos retornará finalmente o nome do Cliente contido na matriz $A$2:$B$16, no qual, encontra-se na posição 8 (linha 9) e coluna “1” da matriz (Coluna_Matriz), ou seja, coluna Cliente. Em resumo a fórmula na célula E2 terá a seguinte configuração, ÍNDICE($A$2:$B$16;8;1).
- Após arrastar a fórmula até a linha 11, posição 10 do rank, teremos os nomes de todos os Clientes de forma ordenada.
Obs.: Perceba que o cifrão foi utilizado tanto na Matriz, $A$2:$B$16, como no Intervalo_Valor, $B$2:$B$16, para fixar a movimentação na horizontal e vertical destes parâmetros.
Disponibilizo para download logo abaixo, o exemplo apresentado.
Espero que tenham gostado!
Caramba!!! Procurei isso a noite toda. Obrigado pelo conteúdo já completo. VlW!!!!
Olá Guilherme! Fico muito feliz que lhe ajudou…Qualquer ajuda que necessite estarei a disposição. Estarei retomando os artigos do site, com mais conteúdo de qualidade. abs
Perfeito! Porém, estou com um problema. Quando os nomes estão na horizontal, como escrevo a coluna, na fórmula?
Boa Noite Rose,
Em caso de coluna a fórmula, Índice + Corresp deve ser alterada.
ÍNDICE($A$2:$B$16;CORRESP(F2;$B$2:$B$16;0);1)
O número 1 no final é referente a posição da coluna, que no seu caso será dinâmica, ou seja, o CORRESP deve ser ajustado neste local, e o CORRESP atualmente que é aplicado na linha deve ser fixo.
Existe a fórmula TRANSPOR também, ela transpõe as linhas em coluna ou vice e versa.
Espero ter ajuda, abraços!
Tenho um pequeno problema, quando dois clientes tem o mesmo valor de venda! Aí o ranking não fica correto, repetido o nome do primeiro.
Olá Rivaldo em caso de empate siga a seguinte fórmula:
=ORDEM.EQ(B2;$B$2:$B$16;0)+CONT.SE($B$2:B2;B2)-1
A Fórmula percorre todos os valores e aplica um ranqueamento com a função “ORDEM.EQ” na sequencia com a função “CONT.SE” contabiliza os eventuais valores repetidos, o “-1” é para ajustar a fórmula quanto a encontrar um valor exclusivo, por exemplo caso exista somente um único segundo lugar a fórmula retornará o cálculo 2+1-1=2.
Espero ter ajudado e explicado a lógica, mesmo que de uma maneira objetiva.
Abs,
Carlos R. Roa
O que fazer para criar critério de desempate? Porque o que eu fiz ficou ótimo, porém quando os números são iguais fica o primeiro nome da coluna inicial.
Boa Noite Carlos,
Estou fora de casa, mas na semana que vêm lhe envio um exemplo para solucionar este problema de empate.
Tambem gostaria de saber
Olá Márcio em caso de empate siga a seguinte fórmula:
=ORDEM.EQ(B2;$B$2:$B$16;0)+CONT.SE($B$2:B2;B2)-1
A Fórmula percorre todos os valores e aplica um ranqueamento com a função “ORDEM.EQ” na sequencia com a função “CONT.SE” contabiliza os eventuais valores repetidos, o “-1” é para ajustar a fórmula quanto a encontrar um valor exclusivo, por exemplo caso exista somente um único segundo lugar a fórmula retornará o cálculo 2+1-1=2.
Espero ter ajudado e explicado a lógica, mesmo que de uma maneira objetiva.
Abs,
Carlos R. Roa
Olá Carlos em caso de empate siga a seguinte fórmula:
=ORDEM.EQ(B2;$B$2:$B$16;0)+CONT.SE($B$2:B2;B2)-1
A Fórmula percorre todos os valores e aplica um ranqueamento com a função “ORDEM.EQ” na sequencia com a função “CONT.SE” contabiliza os eventuais valores repetidos, o “-1” é para ajustar a fórmula quanto a encontrar um valor exclusivo, por exemplo caso exista somente um único segundo lugar a fórmula retornará o cálculo 2+1-1=2.
Espero ter ajudado e explicado a lógica, mesmo que de uma maneira objetiva.
Abs,
Carlos R. Roa
Carlos, preciso de outra duvida, tenho uma planilha com linhas na horizontal, com percentuais de market share já com os nomes, mas abaixo delas estas informações, 14,36%, 13,83%, 61,76% e 10,06% criei outras linhas na lateral para saber qual ordem esta cada uma, sendo do 1º ao 4º lugar, qual formula coloco?
Olá Wenderson,
Pelo que entendo você gostaria de criar um ranqueamento em linha ao invés de coluna. Para isso teriamos que fazer alguns ajuste na formula “ÍNDIDE + CORRESP” ficara assim:
=MAIOR(Intervalo_Valor;Numero_Posicao)
ÍNDICE(Matriz; Linha_Matriz; CORRESP(Valor_Venda; Intervalo_Valor;0))
Perceba que ao invés de termos uma “Coluna_Matriz” teremos uma “Linha_Matriz”, que corresponde a posição da linha que será ranqueada, e o “Intervalo_Valor” é a sua linha com os valores.
Espero ter ajudado.
Atenciosamente,
Carlos R. Roa