Blog

Postado em em 20 de maio de 2020

Ranking no Excel com as fórmulas PROCV e MAIOR

Na publicação de hoje vamos ver como construir um ranking no Excel (que pode ser utilizado para TOP 3, TOP5, TOP 10…) de forma fácil e prática!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso Canal do YouTube para mais vídeos!

Ranking no Excel

Para baixar a planilha utilizada nesta publicação, clique aqui!

O que é um Ranking no Excel?

Um top 10 nada mais é do que um ranking de uma informação ordenada do maior para o menor, ou seja, teremos as 10 melhores informações, podem ser pontos, faturamentos, preços, entre outras possibilidades. O importante é saber que se trata de um ranking no excel dos melhores, lembrando que pode ser um top 3,5, 10 vai depender da aplicação e análise.

Quando utilizar um TOP 10?

Um top 10 (ou qualquer outro de sua escolha) será utilizado para representar de forma reduzida as melhores informações com o intuito de facilitar uma análise desses dados, neste caso vamos verificar os faturamentos mensais, portanto será possível verificar de forma rápida e eficiente os 10 meses que mais faturaram.

Desta forma é possível tomar algumas decisões baseado nessas informações assim como fazer uma comparação com os outros melhores e verificar as diferenças entre eles, se há uma distância muito grande. Por fim, é possível fazer uma análise mais detalhada desses dados e tomar decisões baseadas nessas análises.

Como criar um Ranking no Excel?

Antes de iniciar a criação do Top 10 vamos analisar os dados que temos na tabela.

Tabela de dados
Tabela de dados

Esta é uma tabela de faturamentos mensais de uma empresa fictícia e vai de janeiro de 2017 até dezembro de 2020, ou seja, temos 48 meses de dados para análise.

Feita essa análise rápida dos dados que temos vamos partir para a construção do ranking. Como queremos um top 10 vamos inserir os números de 1 a 10 para que possamos classificar cada um dos meses e faturamentos. Para incluir os números o usuário pode colocar o número 1 e 2, em seguida selecionar e arrastar até o número desejado ou preencher manualmente.

Iniciando a construção do ranking
Iniciando a construção do ranking

Desta forma já temos a quantidade de informações que vamos obter para esse ranking, como temos 10 valores teremos um top 10. O próximo passo que vamos executar é para encontrar o maior valor baseado no número do ranking, ou seja, vamos buscar o primeiro maior faturamento, o segundo maior faturamento até o décimo maior faturamento dentro da nossa tabela.

Para isso vamos utilizar a fórmula MAIOR, é uma fórmula bem simples possui apenas dois argumentos. O primeiro é o intervalo de dados e o segundo a o número do valor que queremos, isso quer dizer que podemos pegar o primeiro maior valor, o segundo maior, e assim por diante.

Fórmula MAIOR
Fórmula MAIOR

Como já temos o intervalo e os valores para o ranking já podemos utilizar essa fórmula para obter o ranking do faturamento.

Fórmula MAIOR preenchida
Fórmula MAIOR preenchida

É possível observar a fórmula MAIOR preenchida com a coluna A, que possui os faturamentos e a célula D2 que possui o valor referente ao ranking que vamos buscar, ou seja, neste caso queremos o primeiro maior valor.

Depois de escrita a fórmula basta arrastar (utilizando o quadrado que fica no canto inferior direito da célula) a fórmula até a última célula.

Ranking de faturamento completo
Ranking de faturamento completo

Assim é possível verificar que temos todos os 10 maiores valores da tabela organizados.

O próximo passo é fazer a busca desse valor de faturamento dentro da tabela para que possamos retornar com o mês de referência de cada um dos faturamentos. Para essa busca vamos utilizar a fórmula PROCV.

Fórmula PROCV
Fórmula PROCV

Essa fórmula possui 4 argumentos. O primeiro deles é o valor a ser procurado, que no nosso caso é o valor de faturamento que temos no ranking.

O segundo é a matriz em que vamos procurar, neste caso serão as colunas A e B, pois são onde temos os valores de faturamento e dos meses.

O terceiro é a coluna em que vamos retornar o valor encontrado, que neste caso é 2, pois é a segunda coluna desse intervalo.

O último argumento é muito importante, pois na fórmula do PROCV ele define se o usuário quer uma busca exata ou se aceita aproximações. São raros os casos em que vamos utilizar com aproximações, normalmente queremos que a fórmula busque valores exatos e isso é muito importante para evitar erros. Então neste último argumento vamos escolher FALSO ou 0 para a busca exata.

Resumindo, vamos procurar o valor de faturamento do ranking na coluna A e vamos retornar a informação da mesma linha da coluna B (que é a segunda coluna), desta forma vamos procurar o faturamento e vamos obter o mês de referência dele.

Fórmula PROCV preenchida
Fórmula PROCV preenchida

Essa é a fórmula a ser utilizada, vamos sempre buscar o valor de faturamento que já foi ranqueado anteriormente, agora basta arrastar a fórmula para as outras células e teremos o nosso Top 10 de faturamento mensal.

Top 10 completo com faturamentos e meses de referência
Top 10 completo com faturamentos e meses de referência

Lembrando que como estamos utilizando fórmulas caso o usuário insira mais informações ou faça alguma alteração de dados o Excel vai atualizar esses valores automaticamente.

Ranking no Excel com Desempate

É muito comum que em alguns casos você tenha informações iguais no seu ranking, então o que podemos fazer nesses casos?

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

O que é um ranking com desempate no Excel?

É uma maneira de criarmos um ranking, um top 5 no Excel por exemplo, no entanto com uma maneira de desempatar as posições que estão com um mesmo valor, desta forma evitamos de ter 1 posição sendo ocupada por mais de uma pessoa ou produto.

Quando utilizar essa ferramenta?

Esse tipo de ranking é utilizado quando o usuário não quer mais de um resultado em uma mesma posição, ou seja, se as duas informações são iguais será utilizado algum critério para definir qual deles vai ficar em cada posição, assim o usuário terá somente 1 informação em cada posição. Outra coisa que pode acontecer é repetir as informações em duas posições diferentes, ou seja,

Como criar um ranking Excel com critério de desempate?

Nesta aula vamos criar um ranking dinâmico no Excel para podermos criar um top 5 com as informações corretas sem que tenhamos repetições.

Ranking sem critério de desempate
Ranking sem critério de desempate

É possível observar que neste caso dentro do nosso top 5 temos informações repetidas e não é isso que queremos. Dentro da tabela é possível observar que de fato temos duas pessoas com o faturamento de R$48.000,00, no entanto, somente 1 delas está aparecendo dentro do top 5.

Isso acontece porque na coluna H estamos utilizando a fórmula MAIOR para obter os 5 maiores valores com base na coluna F. E na coluna G estamos utilizando a fórmula PROCV para encontrar o nome do vendedor baseado no faturamento.

Como a fórmula PROCV retornar apenas a primeira ocorrência do que encontra acabamos tendo esse problema de ter 2 nomes iguais em posições diferentes.

Para resolver esse problema vamos criar 3 colunas auxiliares dentro da tabela onde temos o faturamento dos vendedores e em seguida vamos utilizar uma dessas informações para o desempate dos vendedores com o faturamento igual.

Colunas auxiliares
Colunas auxiliares

Vamos agora a descrição de cada uma das colunas, elas são bem simples:

Auxiliar 1 – Aqui vamos fazer o somatório do faturamento com a quantidade vendida, ou seja, dentro da fórmula teremos =B2+C2. Depois basta arrastar a fórmula para as demais células.

Auxiliar 2 – Nesta coluna temos nada mais do que a mesma informação que a coluna A, ou seja, dentro da fórmula teremos =A2. Depois basta arrastar a fórmula para as demais células. Essa coluna é criada para facilitar o uso da fórmula PROCV.

Auxiliar 3 – É o mesmo procedimento utilizado na Auxiliar 2, no entanto para trazer as informações de faturamento de cada vendedor, então dentro da fórmula teremos =B2. Depois basta arrastar a fórmula para as demais células.

Critério de desempate
Critério de desempate

Já para o ranking propriamente dito já vamos criar uma coluna extra que é para verificar o desempate utilizando a coluna auxiliar 1. Para a coluna J, vamos utilizar a fórmula MAIOR para obter a ordem dos 5 maiores valores utilizando a coluna H como referência.

Fórmula MAIOR para calcular o ranking
Fórmula MAIOR para calcular o ranking no Excel

Como estamos utilizando o número da coluna H como referência vamos pegar o primeiro maior valor, depois o segundo maior valor, até o quinto para fecharmos o top 5.

Feito isso podemos preencher as colunas I e K, pois nelas vamos utilizar a fórmula PROCV para buscar o nome do vendedor com esse faturamento que se encontra coluna Auxiliar 1 e o faturamento real de cada um dos vendedores.

PROCV para obter o nome do vendedor
PROCV para obter o nome do vendedor

Para a coluna I temos a fórmula PROCV para buscar o faturamento de desempate, pois assim conseguimos buscar realmente quem de fato ficou naquela posição levando em conta a quantidade que foi vendida como critério de desempate.

PROCV para obter o faturamento do vendedor
PROCV para obter o faturamento do vendedor

Para a coluna K também será utilizada a fórmula PROCV, no entanto ela será utilizada para obter o faturamento inicial de cada um dos vendedores, ou seja, a informação que tínhamos sem somar a quantidade ao valor de faturamento.

Com isso o ranking com critério de desempate foi criado, agora para melhorar a visualização basta ocultar as colunas que possuem essas informações para que os usuários não tenham que visualizar esses dados auxiliares.

Ranking com critério de desempate completo
Ranking com critério de desempate completo

Conclusão – Ranking no Excel

Nesta aula foi possível aprender a criar um top 10 fazendo um ranking no Excel dos maiores faturamentos utilizando as fórmulas MAIOR e PROCV. É claro que em alguns casos nós podemos ter valores iguais, então tivemos uma segunda parte da aula para te mostrar como ajustar isso.

Nessa segunda parte foi possível aprender a como criar um ranking utilizando um critério de desempate. Desta forma não teremos nomes repetidos dentro do ranking e as informações com valores iguais serão colocadas na ordem correta por conta desse critério.

Hashtag Free Excel Básico

Apostila Básica de Excel

Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!

Hashtag Treinamentos

Para acessar outras publicações de Excel Intermediário, clique aqui!


Quer aprender mais sobre Excel com um minicurso básico gratuito?

Quer ter acesso a um Minicurso de Finanças no Excel de forma 100% gratuita? Preencha seu e-mail abaixo!