Fórmula de classificação no Excel: Um guia abrangente com exemplos

A classificação permite que você compare valores e identifique padrões em seus conjuntos de dados. O Excel oferece ferramentas poderosas, como RANK(),RANK.EQ() e RANK.AVG(), para facilitar a classificação. Essas funções são úteis em todas as situações, desde a classificação dos participantes de um torneio esportivo por pontos até a análise de tendências de mercado com base em dados de vendas classificados.

Neste artigo, exploraremos as três funções de classificação no Excel e explicaremos como você pode usá-las com exemplos práticos. Também nos aprofundaremos no uso de funções de classificação com outras funções do Excel e no cálculo de classificações de percentil. 

A resposta rápida: Como calcular a classificação no Excel 

Para calcular as classificações em seus conjuntos de dados desejados, use a seguinte fórmula:

=RANK(number, ref, [order])

Entendendo a função RANK() no Excel

Digamos que você organizou uma competição entre os alunos da escola e agora quer classificar milhares de alunos com base no desempenho deles. 

Você passaria horas classificando os dados? Não, porque o Excel pode ajudar. Entre suas muitas funções avançadas está a função RANK(), que permite que você determine a posição de alguém com um único clique .

Para calcular a classificação de um valor em um intervalo de valores, insira a fórmula:

RANK(number, ref, [order])

Na fórmula acima:

• number: é o número que você deseja classificar.
• refé o intervalo de números que você deve levar em conta na classificação.
• orderespecifica a ordem de classificação. Para ascendente, use "1" como valor; para descendente, use "0" (padrão).

Como usar a fórmula RANK() no Excel

Vamos implementar a função RANK() em exemplos do mundo real:

Classificação das notas dos alunos

O cálculo das notas seria desafiador e demorado se você tiver milhares de alunos. Suponha que você esteja preparando os resultados finais dos alunos e queira classificá-los com base nas pontuações. 

Para isso, primeiro você deve preparar seu conjunto de dados.

Exemplo de conjunto de dados de alunos no Excel. Fonte: Imagem do autor.

Digite a fórmula =RANK(B3,($B$3:$B$7)) em uma célula e arraste-a até a última célula preenchida para obter a classificação.

Usar a função RANK() para calcular a classificação dos alunos. Fonte: Imagem do autor.

Agora você tem a classificação do aluno com apenas um clique.

Mas você notou que dois alunos com as mesmas notas têm a mesma classificação de 4? O aluno seguinte, com 23 pontos, salta para a classificação 6, pulando a classificação 5. Isso significa que a fórmula atribui a mesma classificação a pontuações duplicadas. 

Isso pode bagunçar a ordem de pontuações próximas. Mas você pode corrigir esses laços usando a função COUNTIF() . Insira a seguinte fórmula: 

=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1

A funçãoCOUNTIF() ajusta a classificação para valores duplicados. Ele conta quantas vezes as marcas do aluno atual aparecem acima dele na lista.

Quebrar empates usando a função COUNTIF() no Excel. Fonte: Imagem do autor.

Um valor de 28 aparece duas vezes, e o siteCOUNTIF() programa a contagem de ocorrências. Essa contagem de ocorrências é usada para ajustar a classificação. A fórmula adiciona a contagem de ocorrências menos um à classificação original. Isso garante que cada valor duplicado receba uma classificação exclusiva.

Classificação dos números de vendas em um conjunto de dados

Vejamos outro exemplo para mostrar como você pode usar a função Excel RANK() do Excel em ordem crescente ou decrescente.

Se quiser avaliar o desempenho da sua equipe de vendas, você pode classificar os números de vendas mensais usando a função RANK() do Excel.

Aqui, temos um conjunto de dados com números de vendas mensais para cada vendedor. Usaremos a função RANK() para classificá-los com base em quem gerou mais vendas (da maior para a menor).

Lista de números de vendas a serem classificados. Fonte: Imagem do autor.

Para classificar as vendas em ordem crescente, aplicamos a fórmula =RANK(B2, ($B$2:$B$6), 1). Aqui, um valor de "1" para o parâmetro order especifica a ordem ascendente para a classificação.

Classificar os valores em ordem crescente. Fonte: Imagem do autor.

Depois que você inserir a fórmula, basta manter o cursor pressionado, arrastar o canto da célula para baixo e aplicá-la ao restante dos dados de vendas.

Da mesma forma, se você quiser classificar os valores em ordem decrescente, escreva "0" como o valor do parâmetro order na fórmula, assim: =RANK(B2, ($B$2:$B$6), 0)

Você pode ver a diferença nos resultados:

Classificar os valores em ordem decrescente. Fonte: Imagem do autor.

Você pode notar os sinais de dólar nas referências ($B$2:$B$6). Essas são chamadas de referências absolutas. Eles garantem que a fórmula sempre se refira ao mesmo intervalo de dados de vendas, mesmo quando você a copia, para evitar erros.

Se você não usar a referência absoluta, como =RANK(A2, A2:A10)o intervalo será ajustado à medida que você arrastar para baixo:

• Fórmula no G3: =RANK(G3,(G3:G7), 1)
• Fórmula em G4: =RANK(G4,(G4:G8), 1)

Para evitar esse problema, use referências absolutas pressionando "F4". Agora a fórmula será: =RANK(G3,($G$3:$G$7), 1)

Quando você arrastar a fórmula acima para a última célula preenchida, as referências relativas serão alteradas com base na posição de cada célula. Você pode ver a diferença nos resultados ao usar referências absolutas:

Diferença entre as classificações com e sem referência absoluta. Fonte: Imagem do autor.

Funções avançadas do Rank Excel: RANK.EQ() e RANK.AVG()

Quando você encontra valores duplicados no Excel, as coisas podem ficar complicadas. É nesse ponto que eu prefiro usar as funções RANK.EQ() e RANK.AVG(). Ambos são um pouco diferentes de manusear. Vamos dar uma olhada em como eles funcionam.

A função RANK.EQ() do Excel

RANK.EQ() funciona exatamente como a função regular RANK(), mas com um nome mais preciso. Quando há duplicatas, o site RANK.EQ() atribui a mesma classificação a todas elas e simplesmente ignora a(s) próxima(s) classificação(ões) na fila.

Sintaxe: =RANK.EQ(number, ref, [order])

Na fórmula acima:

• number: é o número que você deseja classificar.
• refé o intervalo de números que você deve levar em conta na classificação.
• orderespecifica a ordem de classificação. Para ascendente, use "1" como valor; para descendente, use "0" (padrão).

Vamos entender essa fórmula com um exemplo: Eu tinha um conjunto de dados com as populações de vários países e queria classificá-los com base em suas populações.

Veja como eu fiz isso usando a função RANK.EQ() função:

Usando a função RANK.EQ(). Fonte: Imagem do autor.

Na imagem acima, o país com a maior população está na posição 1, e assim por diante. Agora, você vê como o RANK.EQ() lida com os laços .

Como os EUA e a Indonésia têm populações idênticas, eles compartilham a mesma classificação de 2. Isso deixa claro como o site RANK.EQ() atribui a mesma classificação às duplicatas e ignora a próxima classificação na sequência.

A função RANK.AVG() do Excel

A funçãoRANK.AVG() adota uma abordagem diferente para lidar com os empates nas classificações. Em vez de atribuir a mesma classificação a todas as duplicatas, ele calcula a média das classificações que elas teriam recebido individualmente.

Sintaxe: RANK.AVG(number, ref, [order])

Na fórmula acima:

• number: é o número que você deseja classificar.
• refé o intervalo de números que você deve levar em conta na classificação.
• orderespecifica a ordem de classificação. Para ascendente, use "1" como valor; para descendente, use "0" (padrão).

Vamos entender isso com um exemplo: Eu tinha um conjunto de dados de pontuações, então apliquei a função RANK.AVG() para ver como ela lida com pontuações empatadas.

Usando a função RANK.AVG(). Fonte: Imagem do autor.

Usando =RANK.AVG(D3,$D$3:$D$6), a pontuação 88 recebe uma classificação de 2,5. Isso significa que a função calcula a média das classificações que teria atribuído individualmente. 

Essa abordagem mantém uma distribuição mais equilibrada das classificações, especialmente em conjuntos de dados com vínculos frequentes.

Combinando RANK() com outras funções do Excel

O uso da funçãoRANK() com outras funções do Excel é útil em alguns cenários. Vamos analisar alguns casos de uso comuns.

Combinação de RANK() e ROUNDUP() para categorizar dados

Veja como você pode combinar a função ROUNDUP() com a função RANK() função:

Combinando as funções RANK() e ROUNDUP() no Excel. Fonte. Imagem do autor.

Usei a fórmula =ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0), que calcula a classificação do valor na célula B2 dentro do intervalo B2 em ordem crescente. Em seguida, ele divide essa classificação por três e arredonda o resultado para o número inteiro mais próximo.

Com a fórmula acima, você categoriza efetivamente os dados em grupos ou níveis distintos, o que pode ajudar a identificar e analisar diferentes níveis de desempenho ou categorizar dados para análise posterior.

Combinando RANK.EQ() e COUNTIFS() para desempatar

Discutimos algumas maneiras de lidar com empates usando a funçãoRANK() do Excel . No entanto, digamos que você queira classificar os produtos apenas com base no preço. Isso pode parecer arbitrário quando vários itens compartilham o mesmo custo. 

Portanto, vamos introduzir um critério secundário para desempatar e criar um sistema de classificação mais objetivo.

Considere uma lista de clientes que compraram fones de ouvido. Queremos classificá-los pelo preço (critério principal) e, em seguida, usar a classificação do cliente (critério secundário) para diferenciar em caso de empate.

Exemplo de lista de clientes. Fonte: Imagem do autor 

Para isso, combinaremos a função RANK.EQ() com a funçãoCOUNTIFS(). Veja como:

=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">"& $C2)

Calculando a classificação usando Calculando a classificação usando RANK.EQ() e COUNTIFS(). Fonte: Imagem do autor.

A seção COUNTIFS($B$2:$B$9,$B2)conta a frequência com que o preço atual aparece na lista. Usamos referências absolutas para o intervalo, $B$2:$B$9, para manter a consistência, mas mantemos a célula de critérios, $B2, relativa para que a fórmula verifique cada linha individualmente.

A segunda parte, $C$2:$C$9,">"&$C2encontra o número de classificações de clientes mais altas do que a classificação do fone de ouvido atual, C2.

COUNTIFS() segue a lógica "AND": Ele conta apenas as células que atendem a todas as condições. Se nenhum outro fone de ouvido do mesmo preço tiver uma classificação mais alta, COUNTIFS() retornará 0. É por isso que a classificação determinada pelo site RANK.EQ() permanece inalterada.

Cálculo da classificação percentual no Excel

Como a função RANK() classifica um valor numérico, você pode usar uma fórmula de classificação de percentil do Excel para calcular a classificação do valor em um conjunto de dados como uma porcentagem do conjunto de dados total. 

O Excel oferece duas funções para calcular as classificações de percentil. Vamos explorar os dois. 

A função PERCENTRANK.INC() do Excel

PERCENTRANK.INC() calcula a classificação de percentil de um valor, incluindo os menores e maiores valores no conjunto de dados.

Vamos entender essa função com um exemplo: Eu tinha um conjunto de dados que representava as pontuações dos exames de 10 alunos e queria calcular a classificação percentual de cada pontuação, incluindo os menores e os maiores valores. 

Então, usei a fórmula =PERCENTRANK.INC($A$2:$A$11, A2):

Cálculo da classificação usando a função PERCENTRANK.INC(). Fonte: Imagem do autor

No exemplo acima, você pode ver que a funçãoPERCENTRANK.INC() inclui os menores e os maiores valores no conjunto de dados. A pontuação 45 (menor valor) tem uma classificação de percentil 0, e a pontuação 90 (maior valor) tem uma classificação de percentil 1.

A função PERCENTRANK.EXC() do Excel

PERCENTRANK.EXC() calcula a classificação de percentil de um valor, excluindo os menores e maiores valores do conjunto de dados.

Vamos entender essa função com um exemplo:  Apliquei a função PERCENTRANK.EXC() no mesmo conjunto de dados de alunos da seção anterior:

Cálculo da classificação usando a função PERCENTRANK.EXC(). Fonte: Imagem do autor

No exemplo acima, a funçãoPERCENTRANK.EXC() exclui os menores e os maiores valores do conjunto de dados. Isso significa que a pontuação 45 (menor valor) não tem uma classificação de percentil 0, e a pontuação 90 (maior valor) não tem uma classificação de percentil 1.

Erros comuns da função RANK() do Excel e como evitá-los

As funções do Excel são úteis e fáceis de usar em todos os trabalhos comerciais. Mas, às vezes, até mesmo os usuários mais habilidosos podem cometer alguns erros comuns. Portanto, estou compartilhando alguns dos erros mais comuns a serem observados e como você pode evitá-los:

Copiar e colar fórmulas

Quando você copiar sua fórmula de classificação entre as células, certifique-se de não usar referências absolutas acidentalmente. Cada célula pode fazer referência a uma parte diferente dos dados e prejudicar suas classificações. 

Para evitar isso, use referências absolutas, como $A$1:$A$10ao configurar suas fórmulas para bloquear o intervalo correto em todas as classificações.

Não lidar com duplicatas

Digamos que você tenha dois vendedores empatados com o maior número de vendas. A forma como você lida com os empates na classificação pode fazer uma grande diferença: as funções RANK() e RANK.EQ() tratam os empates de forma semelhante.

Portanto, você deve decidir se os valores empatados merecem a mesma classificação ou uma classificação diferente, ou se a média das classificações deve ser calculada usando RANK.AVG(). Em seguida, use a função correta de acordo. 

Pedido incorreto 

Parece simples, mas se você esquecer de definir o argumento order em sua função pode classificar as classificações na direção errada (ascendente ou descendente).

É por isso que você deve sempre verificar novamente esse argumento: Use "0" (ou deixe em branco) para ordem decrescente e "1" para ordem crescente. Certifique-se de que a ordem esteja alinhada com a forma como você deseja que seus dados sejam classificados.

Fracasso de função

Quando você trabalha com conjuntos de dados diferentes, é muito importante escolher a função de classificação correta, pois o uso da função de classificação errada produzirá resultados indesejáveis.

Para evitar esses problemas, use RANK.EQ() para a classificação padrão e RANK.AVG() para cenários em que o tratamento de empates afeta o resultado, como na análise estatística.

Uso de fórmulas desatualizadas 

À medida que seus dados aumentam, você deve atualizar as referências de intervalo em suas fórmulas. Isso significa que novos pontos de dados não serão incluídos em suas classificações, e você pode ficar frustrado por não estar obtendo a resposta correta.

É por isso que você deve atualizar e verificar regularmente os intervalos em suas fórmulas. Isso garante que todos os seus dados valiosos sejam considerados para classificação.

Considerações finais

Agora você sabe como usar funções como RANK(), RANK.EQ() e RANK.AVG() para lidar com empates e resolver vários problemas de classificação. Essas funções ajudam em diferentes cenários, como a análise das notas dos alunos ou o pedido de números de vendas.

Se você estiver interessado em expandir ainda mais suas habilidades em Excel, recomendo que confira alguns cursos e recursos excelentes disponíveis no DataCamp: 

• Comece com o curso Introdução ao Excel para desenvolver o conhecimento básico e, em seguida, avance para Análise de dados no Excel e Fundamentos do Excel.  
• Considere o curso Data Visualization in Excel para aprimorar sua capacidade de visualizar dados. 
• O curso Introdução ao PowerQuery no Excel beneficia muito a manipulação de dados mais avançada. 
• No entanto, para formalizar sua experiência com o Excel, confira este guia abrangente sobre Como obter uma certificação do Microsoft Excel.