Precisa saber quantos cargos diferentes existem por província/estado, sem confundir com o número de funcionários por cargo? Aprenda a usar Tabela Dinâmica com Modelo de Dados para ativar “Contagem Distinta” e veja alternativas com Power Query, fórmulas do Microsoft 365 e DAX.
Visão geral do problema
Ao analisar dados de RH, é comum surgir a pergunta: quantos cargos distintos existem em cada província/estado? A Tabela Dinâmica padrão do Excel faz soma/contagem de linhas (isto é, de funcionários), e não a contagem de valores únicos da coluna Cargo por Província. Para responder corretamente, você precisa contar valores únicos de “Cargo” dentro de cada grupo de “Província”.
A solução nativa do Excel para isso é a opção Contagem Distinta (Distinct Count), que aparece quando a Tabela Dinâmica usa o Modelo de Dados. Abaixo está o passo a passo completo e, em seguida, alternativas para quando esse recurso não estiver disponível.
Solução rápida com Tabela Dinâmica e Modelo de Dados
Pré-requisitos e observações
- Excel no Windows: disponível no Excel 2013+ e no Microsoft 365 (desde que a versão ofereça o Modelo de Dados).
- Excel no Mac: a disponibilidade da Contagem Distinta pode variar por versão. Se a opção não aparecer, use as alternativas com Power Query, fórmulas do Microsoft 365 ou Power Pivot (quando disponível).
- Organize sua base como Tabela do Excel (Ctrl+T ou Formatar como Tabela). Isso facilita atualização e mantém referências dinâmicas.
Passo a passo
- Selecione sua base de dados e acesse Inserir › Tabela Dinâmica.
- No diálogo, marque Adicionar estes dados ao Modelo de Dados e confirme.
- No painel de campos da Tabela Dinâmica:
- Arraste Província para Linhas.
- Arraste Cargo para Valores.
- Na área Valores, clique na setinha do campo Cargo › Configurações do Campo de Valor › Resumir valores por › Contagem Distinta (Distinct Count) › OK.
Pronto: para cada província, você verá o número de cargos únicos. Opcionalmente, renomeie o campo em Valores para Cargos Distintos e formate como número sem casas decimais.
Como lidar com cargos vazios
Se existirem linhas em que Cargo está vazio, a contagem pode exibir um item “(em branco)”. Para ignorá-lo, aplique um filtro na própria Tabela Dinâmica:
- Arraste Cargo também para a área Filtros e desmarque (em branco); ou
- Limpe a coluna de origem (remova linhas com cargo vazio ou preencha corretamente) antes de criar a Tabela Dinâmica.
Por que a Contagem Distinta exige o Modelo de Dados?
O Modelo de Dados (internamente baseado no mecanismo VertiPaq) permite que a Tabela Dinâmica utilize agregações que dependem de cardinalidade (número de valores únicos) de uma coluna. A versão tradicional da Tabela Dinâmica, sem o Modelo de Dados, não expõe a opção Contagem Distinta. Portanto, marcar Adicionar estes dados ao Modelo de Dados no momento da criação é o gatilho para liberar o recurso.
Dados limpos = contagem correta
Diferenças de grafia, espaços extras e maiúsculas/minúsculas inconsistentes podem resultar em múltiplos “valores únicos” que, na prática, representam o mesmo cargo. Antes de contar, padronize:
- Localizar & Substituir para harmonizar nomes (ex.: “Analista Sr.” → “Analista Sênior”).
- Crie uma coluna auxiliar:
=ARRUMAR([@Cargo])para remover espaços extras;=MAIÚSCULA([@Cargo])ou=MINÚSCULA([@Cargo])para padronizar caixa.
| Original | Problema | Padronização sugerida |
|---|---|---|
| Analista Sênior | Espaços duplos | Analista Sênior |
| analista sênior | Caixa inconsistente | ANALISTA SÊNIOR (ou Analista Sênior) |
| Coord. de Vendas | Abreviações distintas | Coordenador de Vendas |
Exemplo rápido e resultado esperado
Suponha a base abaixo:
| Funcionário | Província | Cargo |
|---|---|---|
| 1001 | SP | Analista |
| 1002 | SP | Analista |
| 1003 | SP | Coordenador |
| 1004 | RJ | Analista |
| 1005 | RJ | Supervisor |
| 1006 | RJ | |
| 1007 | MG | Diretor |
Com a Tabela Dinâmica usando Contagem Distinta de Cargo por Província, você verá algo como:
| Província | Cargos Distintos | Observação |
|---|---|---|
| SP | 2 | Analista, Coordenador |
| RJ | 2 ou 3 | 2 se ignorar “em branco”; 3 se contar “em branco” |
| MG | 1 | Diretor |
Se preferir ignorar “em branco”, aplique o filtro sugerido acima ou corrija a base de origem.
Alternativas quando a opção não aparece
Power Query
O Power Query é excelente para deduplicar e resumir dados antes de criar a Tabela Dinâmica.
- Selecione a base › Dados › De Tabela/Intervalo para abrir o Editor do Power Query.
- Selecione as colunas Província e Cargo (Ctrl+clique).
- Use Remover Duplicatas para obter uma lista única de combinações Província–Cargo.
- Com a lista única, aplique Agrupar Por em Província com a operação Contagem de Linhas e nomeie o resultado como Cargos Distintos.
- Fechar & Carregar em uma Tabela ou direto numa Tabela Dinâmica.
Vantagem: a contagem fica estável mesmo em versões sem Distinct Count e você pode embutir outras regras (ex.: ignorar nulos, normalizar textos) no fluxo do Power Query.
Fórmulas do Microsoft 365 (dinâmicas)
Para calcular célula a célula, uma fórmula compacta resolve. Suponha que a província esteja em G2 e sua Tabela se chame Tabela:
Versão com separador “;” (pt-BR/pt-PT):
=LINHAS(ÚNICO(FILTRO(Tabela[Cargo]; Tabela[Província]=G2)))
Para desconsiderar cargos vazios, adicione a condição (Tabela[Cargo]<>""):
=LINHAS(ÚNICO(FILTRO(Tabela[Cargo]; (Tabela[Província]=G2)*(Tabela[Cargo]<>""))))
Versão com vírgula “,” (separador internacional):
=ROWS(UNIQUE(FILTER(Tabela[Cargo], Tabela[Província]=G2)))
=ROWS(UNIQUE(FILTER(Tabela[Cargo], (Tabela[Província]=G2)*(Tabela[Cargo]<>""))))
Arraste a fórmula para as demais províncias ou use uma lista única de províncias com =ÚNICO(Tabela[Província]) ao lado, referenciando cada item.
Power Pivot (DAX)
Se você já trabalha com o Modelo de Dados/Power Pivot, crie uma medida:
Cargos Distintos = DISTINCTCOUNT(Tabela[Cargo])Depois, coloque Província em Linhas e a medida Cargos Distintos em Valores. Para ignorar vazios, uma abordagem prática é tratar esses registros na origem (Power Query) ou filtrar “(em branco)” na Tabela Dinâmica.
Cenários avançados úteis
Filtrar por departamento ou unidade
Acrescente Departamento à área Filtros ou use Segmentações de Dados (Slicers) para que o usuário selecione uma unidade e veja a contagem de cargos distintos por província apenas naquele recorte.
Considerar período de atividade
Se você precisa contar cargos distintos ativos em uma data (ex.: “em 31/12/2024”), crie uma coluna Ativo? (verdadeiro/falso) com base em datas de início/fim e filtre por ela antes da contagem. No Power Query, essa regra fica documentada no fluxo; no DAX, você pode usar um campo de data e filtros de intervalo.
Exibir Top N províncias
Depois de obter a contagem, aplique classificação Maior para o Menor e use o filtro de valor da Tabela Dinâmica para mostrar apenas as N províncias com mais cargos distintos.
Cruzamento com cidades
Quer entender a diversidade de cargos por Província > Cidade? Coloque Província e Cidade em Linhas (em hierarquia) e mantenha Cargo em Valores com Contagem Distinta. Assim, você vê o total por província e também por cidade.
Erros comuns e como resolver
- “Contagem Distinta” desabilitada: a Tabela Dinâmica não foi criada com o Modelo de Dados. Recrie a Tabela Dinâmica e marque Adicionar estes dados ao Modelo de Dados.
- Resultados maiores que o esperado: há variações de texto (ex.: “Coord.” vs “Coordenador”). Padronize com ARRUMAR/MAIÚSCULA e Localizar & Substituir, ou normalize no Power Query.
- Entrada “(em branco)” aparecendo: filtre “(em branco)” na Tabela Dinâmica, preencha valores na base ou resolva no Power Query removendo linhas sem cargo.
- Contagem muda após atualização: verifique se novas linhas seguem o mesmo padrão de escrita e se a Tabela está atualizada (Dados › Atualizar Tudo).
Boas práticas para desempenho e manutenção
- Trabalhe com Tabelas nomeadas (ex.:
tbFuncionarios) para que a Tabela Dinâmica e as fórmulas se ajustem automaticamente. - Evite colunas desnecessárias no Modelo de Dados; mantenha apenas o que é usado na análise.
- Padronize as regras de limpeza no Power Query para garantir repetibilidade quando novas cargas chegarem.
- Documente os campos: Província (texto curto), Cargo (texto padronizado), Departamento (opcional), datas (se precisar de recortes temporais).
Guia rápido de implementação
- Prepare a base: crie uma Tabela, normalize a coluna Cargo com ARRUMAR/MAIÚSCULA e corrija vazios.
- Crie a Tabela Dinâmica marcando Adicionar estes dados ao Modelo de Dados.
- Monte o layout: Província em Linhas, Cargo em Valores.
- Aplique Contagem Distinta nas Configurações do Campo de Valor.
- Refine: remova “(em branco)”, formate números, adicione Slicers (Departamento/Período) se necessário.
- Valide comparando com uma amostra manual ou com a alternativa via Power Query/fórmulas para garantir consistência.
Resolução com Power Query — passo a passo detalhado
Para transformar de ponta a ponta usando apenas o Power Query e entregar a contagem pronta:
- Selecione a base › Dados › De Tabela/Intervalo.
- No Editor:
- Remova espaços e padronize Cargo com Transformar › Formatar › Limpar e Maiúsculas/Minúsculas, se quiser.
- Selecione Província e Cargo e clique em Remover Duplicatas.
- Com a lista única, use Agrupar Por em Província com operação Contagem de Linhas nomeada como Cargos Distintos.
- Fechar & Carregar em Tabela. Se preferir, carregue no Modelo de Dados e crie uma Tabela Dinâmica a partir daí.
Comparando abordagens
| Abordagem | Quando usar | Prós | Contras |
|---|---|---|---|
| Tabela Dinâmica + Modelo de Dados | Excel 2013+ no Windows/365 com recurso disponível | Rápida, nativa, interativa | Depende do Modelo de Dados |
| Power Query | Padronizar e consolidar antes de contar | Fluxo reproduzível, limpa a base | Requer conhecer o Editor PQ |
| Fórmulas (Microsoft 365) | Planilhas sem Tabelas Dinâmicas ou para cálculos linha a linha | Flexível, transparente | Escalabilidade menor em dados muito grandes |
| Power Pivot (DAX) | Modelos robustos, várias tabelas, medidas | Escala e expressividade | Curva de aprendizado de DAX |
Perguntas frequentes
Como classifico do maior para o menor?
Clique em qualquer valor da coluna Cargos Distintos na Tabela Dinâmica › Classificar › Maior para o Menor.
Posso exibir apenas províncias com mais de X cargos?
Sim. Use Filtro de Valor na Tabela Dinâmica: Maior que… ou Entre….
As letras maiúsculas/minúsculas influenciam?
Podem influenciar dependendo de como os dados chegam e são tratados. Para evitar variações, padronize a caixa (MAIÚSCULA ou Minúscula) antes da contagem.
Como atualizo a contagem quando a base muda?
Use Dados › Atualizar Tudo. Se usar Power Query, o fluxo de limpeza será reexecutado e a Tabela Dinâmica refletirá as novas contagens.
Checklist final
- Base como Tabela e campos consistentes.
- Inserir › Tabela Dinâmica com Modelo de Dados ativado.
- Província em Linhas, Cargo em Valores com Contagem Distinta.
- Filtre (em branco) se necessário.
- Se a opção não estiver disponível: Power Query, fórmulas do Microsoft 365 ou medida DAX.
Resumo em uma frase
Para contar cargos distintos por província no Excel, use a Tabela Dinâmica com Modelo de Dados para habilitar Contagem Distinta; quando não estiver disponível, recorra a Power Query, fórmulas dinâmicas ou DAX.