Olá seja bem vindo ao meu mais novo artigo

Será demonstrado alguns tipos de ranking existentes no Excel.

No exemplo queremos segmentar uma base com os maiores valores, criando um TOP +5 ou um TOP -5, para obter os melhores vendedores, maiores produtores ou maiores rendimentos financeiros ou menores refugos, os exemplos são inúmeros.

Mas vamos ao exemplo abaixo

Qual seria a melhor forma de obter o ranking destes dados? Crescente ou Decrescente?

Ranking com 1 critério

1° Ranking Normal - ORDEM|RANK ou ORDEM.EQ|RANK.EQ

Neste método, se utiliza a função chamada ORDEM|RANK (versão do Excel 2007 ou superior), o curioso que a função ORDEM.EQ|RANK.EQ tem a mesma funcionalidade.

Segue a fórmula aplicada na célula E3

=ORDEM(C3;$C$3:$C$7)

ou em inglês

=RANK(C3;$C$3:$C$7)

Por padrão a função ORDEM|RANK retorna o critério decrescente (do maior para o menor), caso necessite da função crescente basta acrescentar o número "1" no final da função.

Neste momento temos um problema, quando há valores iguais, no exemplo demonstrado acima com dois valores com 90 e dois valores com 70, para obtenção de ranking associado a um texto, ficará complexo resultar quais são os valores dos rankings duplicados.

2° Ranking Sem Duplicado - ORDEM|RANK + CONT.SE|COUNT.IF

Neste método, no caso do ranking duplicado será associado o primeiro resultado como 1° e o próximo duplicado irá acrescentar + 1, segue duas formas para resultar o ranking sem valores duplicado, iniciando na célula F3

=ORDEM(C3;$C$3:$C$7)+CONT.SE($C$3:C3;C3)-1

ou em inglês

=RANK(C3;$C$3:$C$7)+COUNTIF($C$3:C3;C3)-1

Também é possível realizar esse método de Ranking sem a utilização da função ORDEM|RANK, que segue abaixo

=CONT.SE($C$3:$C$7;">"&C3)+CONT.SE($C$3:C3;C3)

ou em inglês

=COUNTIF($C$3:$C$7;">"&C3)+COUNTIF($C$3:C3;C3)

Não recomendo utilizar esta estratégia, devido ao alto consumo de processamento que exige a função CONT.SE|COUNTIF no Excel.

Caso necessite inverter o critério de Decrescente para Crescente basta adicionar o critério 1 na função ORDEM|RANK e trocar o critério ">" (maior) pelo "<" (menor).

3° Ranking Sem pular Números - ÍNDICE|INDEX + CONT.SE|COUNT.IF

Neste método o Ranking necessita de valores sem pular números como o que ocorre no Ranking normal (1° Caso), um exemplo da utilização deste método, segue no artigo abaixo;

▷ Métodos Ordinais Multicritério de Borda, Condorcet, Copeland no Excel

Segue a fórmula iniciando na célula G3

=SOMA(ÍNDICE((C3<=$C$3:$C$7)/CONT.SE($C$3:$C$7;$C$3:$C$7);0))

ou em inglês

=SUM(INDEX((C3<=$C$3:$C$7)/COUNTIF($C$3:$C$7;$C$3:$C$7);0))

4° Ranking de Besson (Médio) - ORDEM.MÉD|RANK.AVG

O Ranking Médio ou o Ranking de Besson é uma mescla do 1° Ranking (Normal) com o 2° Ranking sem Duplicado, realizando uma média dos valores, onde a coluna de média será a coluna F e a coluna de Critério será a E. No Excel existe a função que realiza esse cálculo, que segue na Célula H3

=ORDEM.MÉD(C3;$C$3:$C$7)

ou em inglês

=RANK.AVG(C3;$C$3:$C$7)

Caso não tenha essa função é possível utilizar a função auxiliar como MÉDIASE, segue a função auxiliar aplicada abaixo,

=MÉDIASE($F$3:$F$7;F3;$G$3:$G$7)

ou em inglês

=AVERAGEIF($F$3:$F$7;F3;$G$3:$G$7)

Note que serão necessárias duas colunas auxiliares neste método.

Um exemplo prático com a utilização deste método (ORDEM.MÉD|RANK.AVG) é o método multicritério ORESTE, que segue abaixo

▷ ORESTE no Excel

Ranking com 2 critérios

Quando necessita mais de um critério, as funções do Excel já não funcionam, pois foram criadas para somente uma coluna de critério, para isso será necessário criar algumas estratégias de saídas para retornar corretamente o ranking com mais de 1 critério, vamos a eles;

Mas antes gostaria de fazer um agradecimento em especial ao Mestre Fabio Gatti, por mostrar uma estratégia muito show para a resolução dos Rankings com mais de 1 critério. Obrigadão meu amigo novamente!

1° Ranking Normal Duplo Critério

Quando há dois critérios, a estratégia define que será atribuído um peso maior para o primeiro critério e para o segundo critério um peso menor para que o ranking fique correto, segue a fórmula na célula F3

=ÍNDICE(CORRESP(($C$3:$C$7)*1000+($D$3:$D$7);MAIOR(($C$3:$C$7)*
1000+($D$3:$D$7);LIN(INDIRETO("1:"&CONT.VALORES($C$3:$C$7))))
;0);LINS($F$3:F3))

ou em inglês

=INDEX(MATCH(($C$3:$C$7)*1000+($D$3:$D$7);LARGE(($C$3:$C$7)*
1000+($D$3:$D$7);ROW(INDIRECT("1:"&COUNTA($C$3:$C$7))))
;0);ROWS($F$3:F3))

Neste método, os valores do ranking se repetem caso haja um empate no primeiro e segundo critério.

2° Ranking Sem Duplicados Duplo Critério

Neste método é adicionado a função CONT.SES com o primeiro e o segundo critério, para não apresentar valores duplicados. Segue a fórmula iniciando na célula G3

=ÍNDICE(CORRESP(($C$3:$C$7)*1000+($D$3:$D$7);MAIOR(($C$3:$C$7)*1000+
($D$3:$D$7);LIN(INDIRETO("1:"&CONT.VALORES($C$3:$C$7))));0);
LINS($F$3:F3))+CONT.SES($C$3:C3;C3;$D$3:D3;D3)-1

ou em inglês

=INDEX(MATCH(($C$3:$C$7)*1000+($D$3:$D$7);LARGE(($C$3:$C$7)*1000+
($D$3:$D$7);ROW(INDIRECT("1:"&COUNTA($C$3:$C$7))));0);
ROWS($F$3:F3))+COUNTIFS($C$3:C3;C3;$D$3:D3;D3)-1

Segue o artigo abaixo, com o exemplo da utilização deste Ranking

▷ ELECTRE I no Excel

3° Ranking Sem pular Números Duplo Critério

Nesta estratégia deve adicionar o peso diferenciado para cada critério, segue a fórmula na Célula H3

=SEERRO(SOMA(ÍNDICE((((D3+1000)*10000)+((C3+10)*10)<=ÍNDICE((($D$3:$D$7+1000)*10000)+(($C$3:$C$7+10)*10);0))/
CONT.SES($D$3:$D$7;$D$3:$D$7;$C$3:$C$7;$C$3:$C$7);0));"")

ou em inglês

=IFERROR(SUM(INDEX((((D3+1000)*10000)+((C3+10)*10)<=INDEX((($D$3:$D$7+1000)*10000)+(($C$3:$C$7+10)*10);0))/
COUNTIFS($D$3:$D$7;$D$3:$D$7;$C$3:$C$7;$C$3:$C$7);0));"")

Pode-se notar que nesta estratégia o Ranking fica duplicado quando os valores dos critérios são iguais.

4° Ranking de Besson (Médio) Duplo Critério

Para o Ranking de Besson com dois critérios, deverá utilizar as duas colunas auxiliares do Ranking Duplo Normal (Coluna F) e do Ranking Duplo Sem Duplicado (Coluna G), que segue abaixo a fórmula iniciando na Célula I3

=MÉDIASE($F$3:$F$7;F3;$G$3:$G$7)

ou em inglês

=AVERAGEIF($F$3:$F$7;F3;$G$3:$G$7)

Neste método, pode utilizar com múltiplos critérios, sendo necessário atualizar os critérios do 1° e 2° método.

Segue todos os Rankings no Excel de maneira Dinâmica.

Espero que tenha gostado desse artigo!

Caso tenha alguma sugestão de outra técnica ou melhoria do conteúdo acima, poste aqui nos comentários.

Se gostou, curta e compartilhe este artigo para que todos saibam o que é possível fazer dentro do MS Excel.

Venha fazer o melhor curso EAD de Excel comigo! Clique e compare!

Abraços a todos e até o próximo artigo!

Fabio BALDINI

Frase do Dia: "Se você olhar as pessoas no seu círculo e não ficar inspirado, então você não tem um círculo. Você tem uma jaula" Autor: Nipsey Hussle