Como usar a função Rank e SUMPRODUCT no Excel com COUNTIF
Classificar itens em ordem crescente ou decrescente usando a combinação das funções CONT.SE, RANK.EQ e SUMPRODUCT é relativamente simples. Você pode classificar os itens com ou sem duplicação e manter o controle para pular ou não pular nenhuma classificação na sequência.
Ao atribuir as mesmas classificações a várias entradas idênticas, a função RANK.EQ ignora os números na sequência geral. Para evitar esse salto, use a função SUMPRODUCT com a função CONT.SE. Esta combinação classifica valores duplicados sem pular os números.
Diferentes funções de classificação no Excel
Antes de examinarmos mais de perto sua implementação, vamos revisar brevemente seus outros dois formulários encontrados no Excel; RANK.AVG e RANK.
Em versões anteriores do Excel, apenas a função RANK estava disponível. No entanto, desde o Excel 2010, duas funções de classificação adicionais foram adicionadas, a saber, RANK.AVG e RANK.EQ. Vamos explorar suas diferenças, pois você provavelmente verá todas no Excel.
Função RANK.EQ: RANK.EQ é uma versão nova e aprimorada da função RANK e é compatível com as versões mais recentes do Excel. Ambas as funções, no entanto, são as mesmas e têm o mesmo propósito. O que quer que você use, dá os mesmos resultados.
Função RANK: A função RANK, por outro lado, está disponível para compatibilidade com Excel 2007 e anteriores e pode ser removida em qualquer uma das novas versões do Excel. Hoje, todos usam as versões mais recentes do Excel, portanto, sempre use a função RANK.EQ quando precisar compartilhar planilhas com outras pessoas.
CLASSIFICAÇÃO. AVG: Quando qualquer sequência de classificação contém duplicatas, a função RANK.AVG atribui uma classificação média a cada conjunto de duplicatas. Portanto, ele serve ao mesmo propósito de classificar itens, mas seu objetivo principal é capturar duplicatas.
Como este artigo trata do cálculo das classificações para a lista de itens, vamos ver como a função RANK.EQ irá classificá-los. Além disso, você verá seu uso em combinação com a função CONT.SE.
Antes de implementar a fórmula, vamos ter uma breve visão geral da sintaxe da função RANK.EQ.
Uma Visão Geral da Função de Classificação do Excel
Portanto, há três argumentos na função RANK.EQ; número, ref e pedido. Os primeiros dois argumentos são obrigatórios, enquanto o terceiro é opcional. Para executar RANK.EQ em uma matriz de números, você deve sempre especificar os dois primeiros argumentos.
Vamos dar uma olhada na função de cada argumento:
- Número: este argumento se refere ao número cuja classificação você deseja saber.
- Ref: O argumento Ref refere-se a uma matriz de listas de números na sequência. Lembre-se de que o ref ignora quaisquer valores não numéricos encontrados na matriz.
- Ordem: Apesar de ser um argumento opcional, você pode especificar a função RANK para classificar os itens em ordem decrescente ou crescente escolhendo 0 ou 1 neste argumento. Por padrão, se você deixá-lo de fora, os itens serão classificados em ordem decrescente.
Exemplo de função de classificação do Excel
Portanto, agora que você entende os argumentos da função RANK.EQ, vamos colocá-la em prática em um conjunto de dados para ver como funciona.
Como exemplo, observe os dados de amostra contendo porcentagens de dez alunos. Você pode usar a função Rank.EQ para calcular rapidamente as classificações com base nas porcentagens desses alunos. Isso elimina a necessidade de calculá-los manualmente.
No entanto, ele ignora os números quando há duplicatas envolvidas, então você pode ter que usar outras funções discutidas posteriormente neste artigo. Abaixo está a lista dos alunos com seus percentuais finais.
O primeiro argumento é o número que você deseja classificar, que é a porcentagem de James Walker e outros alunos na linha deste exemplo. Portanto, selecione a célula B2 como o primeiro argumento.
O intervalo de referência, neste caso, será das células B2 a B11, e vamos escolher a ordem crescente no argumento da ordem. Abaixo você pode ver a implementação da função RANK.EQ. A fórmula é semelhante a esta:
=RANK.EQ(B2,$B$2:$B$11,0)
Pressionar Enter executará a fórmula e você obterá a classificação de James Walker.
Neste exemplo, James Walker está classificado em 5º na classe com base em sua porcentagem, o que significa que sua porcentagem é o 5º maior em comparação com o restante dos números em uma matriz.
Usando a função RANK em combinação com a função CONT.SE
É possível usar a função RANK.EQ combinada com a função CONT.SE para interromper o salto de números, mas também irá ignorar a duplicação de classificação. Embora nenhum número se repita duas vezes, dois alunos com o mesmo número podem obter classificações diferentes.
Para entender isso melhor, vamos ver como RANK.EQ funciona junto com COUNTIF. A fórmula é semelhante a esta:
=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1A implementação dessa fórmula resolverá o problema de omissão de números.
Não há duplicação nas classificações acima. Mas, James Walker e Gillian Tillman, que deveriam ter a mesma classificação, agora estão classificados de forma diferente.
Portanto, usar RANK.EQ com COUNTIF resolveu metade do problema, mas não conseguiu gerar o resultado desejado.
Usando a função SUMPRODUCT com a função COUNTIF
Para classificar os alunos em uma lista atribuindo as mesmas classificações para porcentagens equivalentes sem pular nenhum número, você pode usar a função SUMPRODUCT com COUNTIF.
Dê uma olhada na fórmula abaixo:
A fórmula pode parecer complicada, mas é a melhor maneira de classificar os itens corretamente. Desta forma, você pode alcançar os resultados desejados com duplicação de classificação e sem pular números.
Ao fazer o resultado para seus alunos, você pode usar diretamente a fórmula SUMPRODUCT como um substituto para a função RANK. Para cálculos de classificação não duplicados, você pode usar a função RANK.EQ sozinha ou com a função CONT.SE.
Alteração da ordem do resultado final
Na guia Dados , clique no grupo Classificar e Filtrar e escolha uma ordem crescente para ordenar as classificações.
Compare os resultados em três linhas lado a lado para entender melhor como funciona cada método de classificação de itens.
Classifique itens facilmente usando as funções RANK.EQ e SUMPRODUCT
Aqui está como você pode usar as funções RANK e SUMPRODUCT em combinação com a função CONT.SE para classificar itens, com ou sem duplicações. Você também pode decidir se ignora qualquer número na sequência ou não.
Dependendo da situação, você pode usar uma variedade de métodos para classificar os números de acordo com um determinado conjunto de critérios. Este é um exemplo de como as funções do Excel podem ajudá-lo a economizar muito tempo e esforço em cálculos manuais. Há muito mais a explorar para criar planilhas do Excel com aparência profissional.
