Aprenda a montar um Ranqueamento em Excel - Acadêmicos do Excel
Dicas & Truques,Material de Ensino
Ranqueamento em Excel
Posted on 05/05/2017 25/10/2017 by Carlos Roberto Roa
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.
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.
Figura 1 – Base de dados das vendas anuais por Cliente
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.
Figura 2 – Esquema dos parâmetros pertencentes as funções, dividido por cores.
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.
Esse registro foi postado em Dicas & Truques,Material de Ensino e marcado ÍNDICE CORRESP,MAIOR,Ordenar,Rank Clientes,Rank em Excel,Ranking Clientes,Ranking em Excel,Ranqueamento em Excel.
Contagem com valores repetidos
Aprenda a utilizar as funções SOMASE/SOMAESES (SUMIF/SUMIFS)
13 thoughts on "Ranqueamento em Excel"
Caramba!!! Procurei isso a noite toda. Obrigado pelo conteúdo já completo. VlW!!!!
12/06/2018 em 10:09h
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
09/10/2018 em 21:17h
Perfeito! Porém, estou com um problema. Quando os nomes estão na horizontal, como escrevo a coluna, na fórmula?
08/08/2019 em 15:56h
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!
08/08/2019 em 22:27h
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.
13/09/2019 em 00:30h
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.
14/02/2021 em 15:11h
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.
20/09/2019 em 02:41h
Estou fora de casa, mas na semana que vêm lhe envio um exemplo para solucionar este problema de empate.
20/09/2019 em 22:10h
Márcio Rodrigo Fernandes da Cruz says:
17/01/2021 em 08:41h
Olá Márcio em caso de empate siga a seguinte fórmula:
14/02/2021 em 15:08h
Olá Carlos em caso de empate siga a seguinte fórmula:
14/02/2021 em 15:10h
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?
18/03/2021 em 11:35h
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:
Í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.