Precisa atribuir uma colocação contínua por Status no Excel sem empates nem “buracos”? Veja como gerar ranks 1, 2, 3… dentro de cada grupo (Complete, Started, Not Started) com fórmulas modernas — rápidas, legíveis e à prova de duplicatas.
Visão geral: por que o rank “clássico” falha
Quando usamos RANK, RANK.EQ ou contagens simples com COUNTIFS, valores repetidos geram empates (por exemplo, 3, 3, 3…) e deixam lacunas (o 2 some). Em relatórios de elegibilidade, trilhas de aprendizagem ou funis de inscrição, isso distorce análises e abre espaço para erros de comunicação (“por que não existe o 2?”). O desafio é produzir, dentro de cada Status, um rank contínuo que avance mesmo diante de empates de pontuação.
A solução em uma linha (Excel 365 / 2021)
Seus dados estão assim:
- Coluna A — Status da Inscrição (Complete, Started, Not Started)
- Coluna B — Pontos de Comportamento (número)
- Coluna C — Rank contínuo (resultado desejado)
Insira a fórmula abaixo em C2 e copie para baixo:
=IF(B2="","",
COUNT(
UNIQUE(
FILTER($B$2:$B$1000,
($A$2:$A$1000=A2) * ($B$2:$B$1000<=B2)
)
)
)
)Como funciona, passo a passo
- FILTER retorna apenas os pontos do mesmo Status da linha e que são menores ou iguais ao ponto atual.
- UNIQUE remove duplicatas; cada valor distinto conta apenas uma vez.
- COUNT contabiliza quantos valores distintos existem até aquele ponto — este número é o rank.
- O IF inicial impede cálculo em linhas vazias.
Dica de compatibilidade: em instalações do Excel traduzidas, as funções podem aparecer como SE, CONTAR, ÚNICO e FILTRAR. O separador de argumentos pode ser ; em vez de ,. A lógica permanece idêntica.
Aplicando na prática (guia rápido)
- Certifique-se de que a coluna B contém apenas números (sem textos numéricos).
- Na célula C2, cole a fórmula acima.
- Confirme a referência do intervalo (
$2:$1000) de acordo com o seu conjunto de dados real. - Arraste para baixo ou, se os dados estiverem em uma Tabela do Excel, a fórmula preencherá automaticamente as novas linhas.
Exemplo completo com resultado esperado
Dados de entrada (A e B):
| Linha | Status (A) | Pontos (B) |
|---|---|---|
| 2 | Complete | 80 |
| 3 | Complete | 80 |
| 4 | Complete | 95 |
| 5 | Started | 30 |
| 6 | Started | 30 |
| 7 | Started | 50 |
| 8 | Not Started | 0 |
| 9 | Not Started | 0 |
| 10 | Not Started | 10 |
Saída esperada (inclui a coluna C – Rank contínuo por grupo):
| Linha | Status | Pontos | Rank (C) |
|---|---|---|---|
| 2 | Complete | 80 | 1 |
| 3 | Complete | 80 | 1 |
| 4 | Complete | 95 | 2 |
| 5 | Started | 30 | 1 |
| 6 | Started | 30 | 1 |
| 7 | Started | 50 | 2 |
| 8 | Not Started | 0 | 1 |
| 9 | Not Started | 0 | 1 |
| 10 | Not Started | 10 | 2 |
Por que não usar RANK / COUNTIFS puros
As funções de rank tradicionais medem posição no conjunto inteiro e não “saltam” para o próximo número quando há empates. Um trio de valores empatados em 3º lugar resulta em “3, 3, 3” e fará o próximo item pular para “6”. Esse comportamento é matematicamente válido para algumas estatísticas, mas é ruim para comunicação operacional, pois cria buracos nos números. A abordagem com FILTER + UNIQUE + COUNT troca o conceito “posição” por “quantos valores distintos até aqui”, entregando a sequência desejada.
Variações úteis
Rank decrescente
Para rank do maior para o menor, mude o operador na cláusula de filtro:
=IF(B2="","",
COUNT(
UNIQUE(
FILTER($B$2:$B$1000,
($A$2:$A$1000=A2) * ($B$2:$B$1000>=B2)
)
)
)
)Top N por Status (com corte)
Para exibir somente os N primeiros por Status (por exemplo, N = 3), combine com uma regra de formatação condicional ou um filtro na coluna Rank. Se quiser marcar “Top 3” diretamente em uma coluna auxiliar:
=IF(C2<=3,"Top 3","")Ignorar zeros ou nulos
Para desconsiderar zeros no rank, refine a condição do FILTER:
=IF(B2="","",
COUNT(
UNIQUE(
FILTER($B$2:$B$1000,
($A$2:$A$1000=A2) ($B$2:$B$1000<=B2) ($B$2:$B$1000>0)
)
)
)
)Referências estruturadas (Tabelas do Excel)
Ao transformar o intervalo em uma Tabela (ex.: tbInscricoes com colunas Status e Pontos), a fórmula fica mais legível e se expande sozinha:
=IF([@[Pontos]]="","",
COUNT(
UNIQUE(
FILTER(tbInscricoes[Pontos],
(tbInscricoes[Status]=[@Status]) * (tbInscricoes[Pontos]<=[@[Pontos]])
)
)
)
)Opção enxuta com XMATCH (Excel 365)
Se quiser ainda mais concisão, use XMATCH localizando a posição da pontuação na lista de valores distintos e ordenados por Status:
=IF(B2="","",
XMATCH(
B2,
SORT(UNIQUE(FILTER($B$2:$B$1000,$A$2:$A$1000=A2))),
0
)
)O resultado é idêntico ao da solução principal e, para bases grandes, essa versão costuma ser mais rápida.
Compatibilidade: versões sem FILTER/UNIQUE
Se o seu Excel não possui funções dinâmicas, use uma alternativa matricial (confirmar com Ctrl+Shift+Enter):
Versão “enxuta” com SUMPRODUCT
=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000<B2)/COUNTIF($B$2:$B$1000,$B$2:$B$1000))+1Ela soma as ocorrências “menores que” distribuindo empates em frações (1 dividido pela contagem do valor), o que efetivamente conta distintos.
Mais robusta quando o mesmo ponto aparece em diferentes Status:=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000<B2)/COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,$B$2:$B$1000))+1
Aqui a divisão considera a contagem dentro do mesmo Status, evitando interferência de pontos idênticos em grupos diferentes.
Alternativa com FREQUENCY
Outra fórmula clássica para contar distintos menores que B2 dentro do grupo:
=SUM(
--(FREQUENCY(
IF(($A$2:$A$1000=A2)*($B$2:$B$1000<B2), MATCH($B$2:$B$1000,$B$2:$B$1000,0)),
ROW($B$2:$B$1000)-ROW($B$2)+1
)>0)
)+1Refinando legibilidade e desempenho
Usando LET para nomear subexpressões
Nomear faixas e condições evita repetição e melhora a manutenção:
=LET(
rSts,$A$2:$A$1000,
rPts,$B$2:$B$1000,
sts,A2,
val,B2,
IF(val="","",
COUNT(UNIQUE(FILTER(rPts,(rSts=sts)*(rPts<=val))))
)
)Boas práticas de performance
| Tema | Dica prática |
|---|---|
| Ajuste de intervalos | Troque $2:$1000 pelo último registro real. Intervalos menores recalculam mais rápido. |
| Tabelas | Converta para Tabela do Excel e use referências estruturadas; o Excel gerencia o dimensionamento automaticamente. |
| Volatilidade | Evite funções voláteis (OFFSET, INDIRECT) perto da fórmula; elas disparam recálculos desnecessários. |
| Ordenação prévia | Ordenar por Status e Pontos antes de aplicar a fórmula facilita auditoria visual e pode simplificar filtros. |
Validação e auditoria dos resultados
Quer confirmar que não há “buracos” nos ranks de cada Status?
- Use uma Tabela Dinâmica com Linhas = Status e um campo calculado para Máx Rank e Distintos de Pontos (com Contagem Distinta se disponível). Os dois números devem coincidir.
- Ou crie uma checagem por Status (supondo lista única de Status em
E2:E10):
=LET(
s,E2,
rSts,$A$2:$A$1000,
rPts,$B$2:$B$1000,
rRanks,$C$2:$C$1000,
maxRank,MAX(IF(rSts=s,rRanks)),
qtdDist,COUNTA(UNIQUE(FILTER(rPts,(rSts=s)*(rPts<>"")))),
IF(maxRank=qtdDist,"OK","Verificar")
)Integração com relatórios e dashboards
- Segmentação por Status: Com o rank contínuo, é simples destacar “Top 5” de cada Status via Formatação Condicional.
- Detalhe de empates: Exiba uma mensagem como “Empate no rank 2 (80 pts)” com
=IF(C2=2,"Empate", "")combinada com um contador por Status e Pontos. - Relatórios mensais: Se a pontuação muda ao longo do tempo, considere uma coluna “Mês/Ano” e duplique o mesmo conceito de agrupamento por (Status, Período).
Erros comuns (e como evitá-los)
- Pontos como texto: valores como “80 ” (com espaço) ou “80,0” armazenados como texto geram ordenação errada. Use Texto para Colunas ou Valor para converter.
- Intervalo longo demais: usar colunas inteiras (
$B:$B) degrada performance. Prefira intervalos dimensionados. - Status inconsistentes: “Not Started” vs. “Not Started”. Padronize com Dados > Validação ou crie um dicionário de normalização.
- Falta de condição <=: trocar inadvertidamente por
<fará com que empates “pulem” o número. Garanta<=(ou>=no caso descendente).
Abordagem via Power Query (sem fórmulas na planilha)
- Selecione sua Tabela > Dados > Obter & Transformar > De Tabela/Intervalo.
- No Power Query, Classifique por Status (A-Z) e, em seguida, por Pontos (crescente).
- Agrupe por Status e, em cada grupo, adicione uma Coluna de Índice começando em 1 apenas sobre a lista de valores distintos de Pontos (use Remover Duplicatas antes do índice). Depois, mescle esse mapa de (Pontos Distintos → Rank) de volta no grupo original.
- Expanda a coluna mesclada para trazer o Rank contínuo à tabela final e Carregue de volta ao Excel.
Essa estratégia é excelente para bases estáticas ou ETLs repetitivos.
FAQ rápido
O que acontece se eu inserir novas linhas?
Em Tabelas do Excel, a fórmula se autoexpande. Em intervalos comuns, ajuste o limite do intervalo ou use OFFSET/INDEX (com parcimônia) para rangos dinâmicos.
Posso ranquear também por um critério secundário?
Sim. Para desempates visuais (não no número do rank), ordene por Status, depois Pontos, depois um segundo critério (ex.: data). O rank contínuo permanece estável.
Como exibir tie-breaker só para leitura?
Crie uma coluna “Ordem Interna” com =TEXT(B2,"000000")&"-"&ROW() e use-a apenas para ordenação, mantendo o rank contínuo intacto.
Funciona com números negativos?
Sim. A comparação <= (ou >=) trata negativos corretamente.
Como inverter rapidamente para descendente?
Troque <= por >= na solução principal, ou em XMATCH use a lista SORT(...,-1) para ordem decrescente.
Resumo das fórmulas-chave
| Cenário | Fórmula |
|---|---|
| Rank contínuo (365/2021) | =IF(B2="","",COUNT(UNIQUE(FILTER($B$2:$B$1000,($A$2:$A$1000=A2)*($B$2:$B$1000<=B2))))) |
| Versão com XMATCH | =IF(B2="","",XMATCH(B2,SORT(UNIQUE(FILTER($B$2:$B$1000,$A$2:$A$1000=A2))),0)) |
| Sem funções dinâmicas | =SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000<B2)/COUNTIF($B$2:$B$1000,$B$2:$B$1000))+1 |
| Sem dinâmicas (robusta por Status) | =SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000<B2)/COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,$B$2:$B$1000))+1 |
| Rank descendente | Troque <= por >= na fórmula principal. |
Conclusão
Ao ranquear por Status com FILTER + UNIQUE + COUNT (ou XMATCH), você elimina empates visuais e lacunas numéricas, mantendo a sequência 1, 2, 3… dentro de cada grupo — exatamente o que gestores e usuários finais esperam enxergar. A solução é simples de auditar, escalável, compatível com Tabelas e possui alternativas sólidas para versões antigas do Excel. Com ela, seus relatórios ficam mais claros, comparáveis e prontos para tomada de decisão.