As tabelas dinâmicas do Excel são incríveis (sei que menciono isso sempre que escrevo sobre as tabelas dinâmicas, mas é verdade).
Com um conhecimento básico e um pouco de arrastar e soltar, você pode realizar um grande volume de trabalho em poucos segundos.
Embora muito possa ser feito com alguns cliques nas tabelas dinâmicas, há algumas coisas que precisam de algumas etapas extras ou um pouco de solução.
E uma delas é contar valores distintos em uma Tabela Dinâmica.
Neste tutorial, mostrarei como contar valores distintos e também valores exclusivos em uma tabela dinâmica do Excel.
Mas antes de começar a contar valores distintos, é importante entender a diferença entre 'contagem distinta' e 'contagem única'
Contagem distinta vs. contagem única
Embora possam parecer a mesma coisa, não é.
Abaixo está um exemplo onde há um conjunto de dados de nomes e eu listei nomes exclusivos e distintos separadamente.
Valores / nomes únicos são aqueles que ocorrem apenas uma vez. Isso significa que todos os nomes que se repetem e têm duplicatas não são exclusivos. Nomes exclusivos são listados na coluna C no conjunto de dados acima
Valores / nomes distintos são aqueles que ocorrem pelo menos uma vez no conjunto de dados. Portanto, se um nome aparecer três vezes, ainda será contado como um nome distinto. Isso pode ser conseguido removendo os valores / nomes duplicados e mantendo todos os distintos. Nomes distintos são listados na coluna B no conjunto de dados acima.
Com base no que tenho visto, na maioria das vezes, quando as pessoas dizem que desejam obter a contagem única em uma Tabela Dinâmica, elas realmente querem dizer uma contagem distinta, que é o que estou abordando neste tutorial.Contar valores distintos na tabela dinâmica do Excel
Suponha que você tenha os dados de vendas conforme mostrado abaixo:
Clique aqui para baixar o arquivo de exemplo e acompanhar
Com o conjunto de dados acima, digamos que você deseja encontrar a resposta para as seguintes perguntas:
- Quantos representantes de vendas existem em cada região (o que nada mais é do que a contagem distinta de representantes de vendas em cada região)?
- Quantos representantes de vendas venderam a impressora em 21-2022?
Embora as tabelas dinâmicas possam resumir os dados instantaneamente com alguns cliques, para obter a contagem de valores distintos, você precisará realizar mais algumas etapas.
Se você estiver usando Excel 2013 ou versões posteriores, há uma funcionalidade embutida na Tabela Dinâmica que fornece rapidamente a contagem distinta. E se você estiver usando Excel 2010 ou versões anteriores, você terá que modificar os dados de origem adicionando uma coluna auxiliar.Os dois métodos a seguir são abordados neste tutorial:
- Adicionar uma coluna auxiliar no conjunto de dados original para contar valores exclusivos (funciona em todas as versões).
- Adicionar os dados a um modelo de dados e usar a opção Contagem distinta (disponível no Excel 2013 e versões posteriores).
Há um terceiro método que Roger mostra neste artigo (que ele chama de método Pivot de Tabela Dinâmica).
Vamos começar!
Adicionando uma coluna auxiliar no conjunto de dados
Observação: se você estiver usando o Excel 2013 e versões superiores, ignore este método e vá para o próximo (pois ele usa uma funcionalidade de Tabela Dinâmica embutida - Contagem Distinta).Esta é uma maneira fácil de contar valores distintos na Tabela Dinâmica, pois você só precisa adicionar uma coluna auxiliar aos dados de origem. Depois de adicionar uma coluna auxiliar, você pode usar esse novo conjunto de dados para calcular a contagem distinta.
Embora seja uma solução fácil, existem algumas desvantagens nesse método (abordadas posteriormente neste tutorial).
Deixe-me primeiro mostrar como adicionar uma coluna auxiliar e obter uma contagem distinta.
Suponha que eu tenha o conjunto de dados conforme mostrado abaixo:
Adicione a seguinte fórmula na coluna F e aplique-a a todas as células que possuem dados nas colunas adjacentes.
= IF (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)
A fórmula acima usa a função COUNTIFS para contar o número de vezes que um nome aparece em uma determinada região. Além disso, observe que o intervalo de critérios é $ C $ 2: C2 e $ B $ 2: B2. Isso significa que ele continua se expandindo conforme você desce na coluna.
Por exemplo, na célula E2, os intervalos de critérios são $ C $ 2: C2 e $ B $ 2: B2 e na célula E3 esses intervalos se expandem para $ C $ 2: C3 e $ B $ 2: B3.
Isso garante que a função COUNTIFS conte a primeira instância de um nome como 1, a segunda instância do nome como 2 e assim por diante.
Uma vez que queremos apenas os nomes distintos, a função IF é usada, a qual retorna 1 quando um nome aparece para uma região pela primeira vez e retorna 0 quando ele aparece novamente. Isso garante que apenas nomes distintos sejam contados e não as repetições.
Abaixo está como seu conjunto de dados ficaria quando você adicionasse a coluna auxiliar.
Agora que modificamos os dados de origem, podemos usar isso para criar uma Tabela Dinâmica e usar a coluna auxiliar para obter a contagem distinta do representante de vendas em cada região.
Abaixo estão as etapas para fazer isso:
- Selecione qualquer célula do conjunto de dados.
- Clique na guia Inserir.
- Clique em Tabela Dinâmica (ou use o atalho de teclado - ALT + N + V)
- Na caixa de diálogo Criar Tabela Dinâmica, certifique-se de que a Tabela / Intervalo esteja correta (e inclui a coluna auxiliar) e 'Nova Planilha' selecionada.
- Clique OK.
As etapas acima inserem uma nova planilha com a Tabela Dinâmica.
Arraste o campo ‘Região’ na área Linhas e o campo ‘Contagem D’ na área Valores.
Você obterá uma Tabela Dinâmica conforme mostrado abaixo:
Agora você pode alterar o cabeçalho da coluna de ‘Soma da contagem de D’ para ‘Representante de vendas’.
Desvantagens de usar uma coluna auxiliar:
Embora esse método seja bastante direto, devo destacar algumas desvantagens que vêm com a modificação dos dados de origem em uma Tabela Dinâmica:
- A fonte de dados com a coluna auxiliar não é tão dinâmica quanto uma Tabela Dinâmica. Embora você possa fatiar e dividir os dados da maneira que quiser com uma Tabela Dinâmica, ao usar uma coluna auxiliar, você perde uma parte dessa habilidade. Digamos que você adicione uma coluna auxiliar para obter a contagem de um representante de vendas distinto em cada região. Agora, e se você também quiser obter uma contagem distinta de representantes de vendas que vendem impressoras. Você terá que voltar aos dados de origem e modificar a fórmula da coluna auxiliar (ou adicionar uma nova coluna auxiliar).
- Como você está adicionando mais dados à origem da Tabela Dinâmica (que também é adicionada ao Cache Dinâmico), isso pode levar a um tamanho maior do arquivo Excel.
- Como estamos usando uma fórmula do Excel, isso pode tornar sua pasta de trabalho do Excel lenta, caso você tenha milhares de linhas de dados.
Adicionar dados ao modelo de dados e resumir usando uma contagem distinta
A Tabela Dinâmica adicionou uma nova funcionalidade ao Excel 2013 que permite obter a contagem distinta enquanto resume o conjunto de dados.
Caso você esteja usando uma versão anterior, não será possível usar este método (já que deve tentar adicionar a coluna auxiliar conforme mostrado no método acima).
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja obter a contagem do representante de vendas exclusivo em cada região.
Abaixo estão as etapas para obter um valor de contagem distinto na Tabela Dinâmica:
- Selecione qualquer célula do conjunto de dados.
- Clique na guia Inserir.
- Clique em Tabela Dinâmica (ou use o atalho de teclado - ALT + N + V)
- Na caixa de diálogo Criar Tabela Dinâmica, certifique-se de que a Tabela / Intervalo esteja correta e a Nova Planilha em Selecionada.
- Marque a caixa que diz - “Adicionar esses dados ao modelo de dados”
- Clique OK.
As etapas acima inserem uma nova planilha com a nova Tabela Dinâmica.
Arraste a Região na área Linhas e Representante de vendas na área Valores. Você obterá uma Tabela Dinâmica conforme mostrado abaixo:
A Tabela Dinâmica acima fornece a contagem total do representante de vendas em cada região (e não a contagem distinta).
Para obter a contagem distinta na Tabela Dinâmica, siga as etapas abaixo:
- Clique com o botão direito em qualquer célula na coluna ‘Contagem de representante de vendas’.
- Clique em Configurações do campo de valor
- Na caixa de diálogo Configurações do campo de valor, selecione ‘Contagem distinta’ como o tipo de cálculo (pode ser necessário rolar a lista para baixo para encontrá-lo).
- Clique OK.
Você notará que o nome da coluna muda de ‘Contagem do representante de vendas’ para ‘Contagem distinta do representante de vendas’. Você pode alterá-lo para o que quiser.
Algumas coisas que você sabe quando adiciona seus dados ao modelo de dados:
- Se você salvar seus dados no modelo de dados e depois abrir em uma versão mais antiga do Excel, será exibido um aviso - "Algumas funções da tabela dinâmica não serão salvas". Você pode não ver a contagem distinta (e o modelo de dados) quando aberto em uma versão mais antiga que não é compatível.
- Quando você adiciona seus dados a um Modelo de Dados e cria uma Tabela Dinâmica, ela não mostra as opções para adicionar campos calculados e colunas calculadas.
Clique aqui para baixar o arquivo de exemplo
E se você quiser contar valores exclusivos (e não valores distintos)?
Se quiser contar valores exclusivos, você não tem nenhuma funcionalidade embutida na Tabela Dinâmica e terá que contar apenas com colunas auxiliares.
Lembre-se - valores únicos e valores distintos não são iguais. Clique aqui para saber a diferença.Um exemplo pode ser quando você tem o conjunto de dados abaixo e deseja descobrir quantos representantes de vendas são exclusivos para cada região. Isso significa que eles operam em apenas uma região específica e não nas outras.
Nesses casos, você precisa criar uma ou mais colunas auxiliares.
Para este caso, a fórmula abaixo resolve:
= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2) / COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)
A fórmula acima verifica se o nome de um representante de vendas ocorre em apenas uma região ou em mais de uma região. Ele faz isso contando o número de ocorrências de um nome em uma região e dividindo-o pelo número total de ocorrências do nome. Se o valor for menor que 1, indica que o nome ocorre em duas ou mais regiões.
Caso o nome ocorra em mais de uma região, ele retorna 0, caso contrário, retorna um.
A fórmula também verifica se o nome se repete na mesma região ou não. Se o nome for repetido, apenas a primeira instância do nome retornará o valor 1 e todas as outras instâncias retornarão 0.
Isso pode parecer um pouco complexo, mas novamente depende do que você está tentando alcançar.
Portanto, se você quiser contar valores exclusivos em uma Tabela Dinâmica, use colunas auxiliares e se quiser contar valores distintos, você pode usar a funcionalidade embutida (no Excel 2013 e superior) ou pode usar uma coluna auxiliar.
Clique aqui para baixar o arquivo de exemplo
Você também pode gostar dos seguintes tutoriais de tabela dinâmica:
- Como filtrar dados em uma tabela dinâmica no Excel
- Como agrupar datas em tabelas dinâmicas no Excel
- Como agrupar números em uma tabela dinâmica no Excel
- Como aplicar formatação condicional em uma tabela dinâmica no Excel
- Segmentações de dados na tabela dinâmica do Excel
- Como atualizar a tabela dinâmica no Excel
- Excluir uma Tabela Dinâmica no Excel
