Ranqueamento de dados repetidos

Este artigo foi criado por conta de um comentário que recebemos no artigo Índice + Corresp fazendo ProcV comer poeira. O comentário era na verdade uma dúvida da Franciane (obrigado Franciane!) que inicialmente acreditei que poderia ser resolvido por um artigo que já existia aqui no Função Excel, o PROCV com dados repetidos. Doce engano meu, logo vi que o problema embora muito semelhante não se resolveria da mesma maneira. Assim, quando cheguei a solução do problema me dei conta que seria muito complicado explicar tudo somente respondendo o comentário, e eis que surge este artigo especial para responder a dúvida da Franciane.
Viu só? Você também pode mandar suas dúvidas, nós respondemos 🙂

Sem mais delongas, vamos ao que interessa.

---

Vídeo explicativo

O vídeo abaixo explica tudo no detalhe, assim como o texto. Utilize aquele que achar melhor.

Coloque o vídeo em tela cheia para assistir normalmente

Como ranquear dados repetidos

Imagine que você possui uma lista de dados, onde estão os nomes de atletas de uma competição qualquer e a pontuação obtida por cada um deles, e seu objetivo é ranquear os 5 melhores entre eles, porém… alguns deles tiveram pontuações idênticas. Como mostra a imagem abaixo.

Utilizar a função PROV ou a fórmula ÍNDICE + CORRESP não irá funcionar, pois quando houver resultados repetidos estas duas irão sempre buscar a primeira referência do valor procurado e nunca as demais.

Vamos à solução.

---

Ranqueamento por pontuação

Antes de mais nada vamos simplesmente ranquear os 5 maiores pontos, depois partimos para a parte mais complicada de linkar a pontuação ao respectivo nome do atleta.

Utilizaremos para isso a função MAIOR (clique aqui para saber mais sobre ela). Conforme mostra a imagem abaixo.

A função MAIOR irá analisar qual é o enésimo maior valor existente nas células C3 até a C17 e o valor contido nas células da coluna E irão informar qual é a enésima posição. Esta função é copiada para as células F2 a F6.

(Não sabe por que utilizo o $ nas fórmulas? Clique aqui para aprender)

A primeira parte e mais fácil, está pronta, agora vamos buscar os nomes dos atletas.

---

Ranqueamento dos dados repetidos

Como disse anteriormente, uma função ou fórmula normal não irá conseguir resolver este problema, então iremos precisar de uma fórmula matricial. (Clique aqui para saber mais).

Vou avisar antes de começar para não ter problema depois, ao escrever uma fórmula matricial você deve apertar as teclas CTRL + SHIFT + ENTER e não apenas a tecla Enter como fazemos normalmente ao concluir uma função ou fórmula normal. Além disso, quando acabar a primeira fórmula você deve arrastar para baixo nas demais células e não copiar e colar ou qualquer outra coisa do tipo. Se não fizer isso não irá funcionar.

As imagens abaixo mostram como devemos escrever a fórmula.

=ÍNDICE($B$5:$B$17;MENOR(SE($C$5:$C$17=F2;LIN($C$5:$C$17)-4);CONT.SE($F$2:F2;F2)))

Um zoom para ajudar.

Agora vamos entender cada parte dela.

---

Entendendo a função SE

Em linhas gerai a função SE compara se dois dados são iguais ou não, sendo verdadeiro, o Excel executará um ação, sendo falso, executará outra ação diferente. (Clique aqui para saber mais)

Como neste caso estamos falando de uma função matricial, o SE irá verificar se cada um dos valores contidos nas células de C5 a C17 são iguais a pontuação contida na célula F2, e retorna uma conjunto de resultados, quando a relação é verdadeira retorna o VERDADEIRO, quando é falsa retorna FALSO. Veja na imagem.

Assim sempre que é encontrado uma relação verdadeira a função SE retorna o resultado da função LIN e sempre que falso retorna FALSO.

---

Entendendo a função LIN

De modo geral a função LIN retorna a linha em que uma referência se encontra.

Por se tratar de uma função matricial neste caso, o resultado desta função estará ligado ao resultado da função SE. Assim como dito anteriormente a função SE irá analisar cada um dos dados do intervalo selecionado, enquanto que todas as demais funções desta fórmula farão exatamente o mesmo, ao mesmo tempo. Ou seja, quando uma delas estiver fazendo algum teste com a célula a primeira referência de seu intervalo todas estarão olhando para a primeira referência de seu intervalo, e assim por diante.

Como vimos a função SE encontrou um valor verdadeiro somente na décima posição, pois foi onde apareceu o primeiro VERDADEIRO. Então o resultado da função LIN será a linha que estiver na décima posição de seu intervalo selecionado, neste caso, a linha 14.

IMPORTANTE: Note que a função LIN irá sempre nos retornar a linha exata da célula que retornou verdadeiro na função SE, porém nosso intervalo não inicia na linha 1 e sim na linha 5. Como queremos que esta função retorne o número da linha entre as células C5:C17 não estamos querendo um número entre 5 e 17, isso traria uma referência a uma linha na planilha inteira e não dentro do intervalo C5:C17. O que queremos é que esta função retorne um número entre 1 e 13, onde 1 faria referencia a primeira linha do intervalo C5:C17, ou seja, à linha 5; e 13 faria referência a última linha do intervalo, ou seja, à linha 17, é por isso que existe o -4 logo depois da função LIN.

---

Entendendo a função MENOR

De modo geral esta função busca o enésimo menor valor de um intervalo de dados. (Clique aqui para saber mais sobre ela).

Neste caso a função MENOR irá retornar o enésimo menor valor contido na lista de valores informados no resultado da função SE, lembrando que esta resultou em uma lista de FALSO e VERDADEIRO e sempre que o resultado era verdadeiro, obtínhamos o número de uma linha.

E este enésimo menor valor será indicado pelo resultado da função CONT.SE que, caso retorne 1, iremos obter o primeiro menor valor entre aqueles que possuem aquela pontuação indicada pelas células da coluna F.

---

Entendendo a função CONT.SE

De modo geral esta função conta a quantidade de alguma coisa, dado um critério de avaliação. (Clique aqui para saber mais).

Neste caso esta função irá verificar na coluna F se a pontuação da posição atual se repete ou não. Ou seja, quando estamos na célula G3 e contamos da célula F2:F3 quantas vezes a pontuação contida em F3 aconteceu, obtemos como resultado 1. Porém, quando estamos na célula G4 iremos contar quantas vezes a pontuação contida em F4 apareceu entre F2:F4 e teremos como resposta 2, pois neste exemplo as células F3 e F4 são iguais.

---

Entendendo a função ÍNDICE

Em linhas gerais esta função serve para retornar um dado contido em uma matriz de dados com base em uma linha e uma coluna indicadas pelo usuário. (Clique aqui ara saber mais sobre esta função).

Aqui temos o fechamento de tudo, onde unimos o resultado de todas as demais funções explicadas. Esta função irá retornar o nome do atleta contido no intervalo B5:B17 cuja linha será indicada pelo resultado da função MENOR.

---

Fechando tudo no mesmo pacote

Exemplo 1 – Pontuação não repetida

Na célula F2 temos a pontuação 29 que é única, apenas um atleta a atingiu. Assim teremos os seguintes resultados de cada parte da fórmula.

SE – Irá encontrar somente um valor VERDADEIRO.

LIN – Irá retornar a linha da posição do VERDADEIRO dentro da lista criada pela função SE.

CONT.SE – Irá retornar o valor 1, pois esta pontuação é única, indicando que a função MENOR deverá retornar o primeiro menor valor encontrado na lista gerado pela função SE.

MENOR – irá informar qual a posição do atleta no intervalo entre B5:B17.

ÍNDICE – Irá buscar o nome do atleta que estiver na linha indicada pela função MENOR.

Exemplo 2 – Pontuação repetida

Na célula F4 temos a pontuação 23 que é repetida, e já apareceu na célula F3.

SE – Irá encontrar mais de um valor VERDADEIRO.

LIN – Irá retornar as linhas das posições dos valores VERDADEIRO dentro da lista criada pela função SE.

CONT.SE – Irá retornar o valor 2, pois esta pontuação aparece pela segunda vez no intervalo F2:F4, indicando que a função MENOR deverá retornar o segundo menor valor encontrado na lista gerado pela função SE.

MENOR – irá informar qual a posição do atleta no intervalo entre B5:B17.

ÍNDICE – Irá buscar o nome do atleta que estiver na linha indicada pela função MENOR.

Espero que tenha conseguido responder a pergunta. O problema da Franciane não era exatamente esse, com atletas, mas sou como o Chaves, essa conta eu só sei fazer com laranjas e não com maçãs.

---

Acompanhe o Função Excel