Fórmula genérica
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Resumo
Para classificar itens em uma lista usando um ou mais critérios, você pode usar a função COUNTIFS. No exemplo mostrado, a fórmula em E5 é:
=COUNTIFS(groups,C5,scores,">"&D5)+1
onde "grupos" é o intervalo nomeado C5: C14 e "pontuações" é o intervalo nomeado D5: D14. O resultado é uma classificação para cada pessoa em seu próprio grupo.
Observação: embora os dados sejam classificados por grupo na captura de tela, a fórmula funcionará bem com dados não classificados.
Explicação
Embora o Excel tenha uma função RANK, não há função RANKIF para executar uma classificação condicional. No entanto, você pode criar facilmente um RANK condicional com a função COUNTIFS.
A função COUNTIFS pode realizar uma contagem condicional usando dois ou mais critérios. Os critérios são inseridos em pares de intervalo / critério. Nesse caso, o primeiro critério restringe a contagem ao mesmo grupo, usando o intervalo denominado "grupos" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Por si só, isso retornará o total de membros do grupo "A", que é 5.
O segundo critério restringe a contagem a apenas pontuações maiores do que a "pontuação atual" de D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Os dois critérios trabalham juntos para contar as linhas em que o grupo é A e a pontuação é mais alta. Para o primeiro nome na lista (Ana), não há pontuações mais altas no grupo A, então COUNTIFS retorna zero. Na próxima linha (Edward), há três pontuações no grupo A superiores a 79, então COUNTIFS retorna 3. E assim por diante.
Para obter uma classificação adequada, simplesmente adicionamos 1 ao número retornado por COUNTIFS.
Invertendo a ordem de classificação
Para inverter a ordem de classificação e classificar em ordem (ou seja, o menor valor é classificado como # 1), basta usar o operador menor que ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Em vez de contar pontuações maiores que D5, esta versão contará pontuações menores que o valor em D5, revertendo efetivamente a ordem de classificação.
Duplicados
Como a função RANK, a fórmula nesta página atribuirá valores duplicados à mesma classificação. Por exemplo, se um valor específico for atribuído uma classificação de 3, e houver duas instâncias do valor nos dados sendo classificados, ambas as instâncias receberão uma classificação de 3 e a próxima classificação atribuída será 5. Para imitar o comportamento da função RANK.AVG, que atribuiria uma classificação média de 3,5 nesse caso, você pode calcular um "fator de correção" com uma fórmula como esta:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
O resultado desta fórmula acima pode ser adicionado à classificação original para obter uma classificação média. Quando um valor não tem duplicatas, o código acima retorna zero e não tem efeito.
