Tabela de Pivô simples para contar valores únicos

Actualização: pode fazer isto agora automaticamente com o Excel 2013. Criei isto como uma nova resposta porque a minha resposta anterior resolve um problema ligeiramente diferente.

Se tiver essa versão, seleccione então os seus dados para criar uma tabela de pivô, e quando criar a sua tabela, certifique-se que a opção 'Adicionar estes dados ao modelo de dados' é a opção Verificar (ver abaixo).

Então, quando a sua tabela pivot abrir, crie as suas linhas, colunas e valores normalmente. Entao carregue no campo que deseja calcular a contagem distinta e editar a configuração do valor do campo:

Finalmente, desloque-se para a última opção e escolha 'contagem distinta.'

Isto deve actualizar os valores da sua tabela pivot para mostrar os dados que procura.

Eu gostaria de lançar uma opção adicional na mistura que não requer uma fórmula, mas pode ser útil se você precisar contar valores únicos dentro do conjunto em duas colunas diferentes. Usando o exemplo original, eu não tinha:

ABC   123  
ABC   123  
ABC   123   
DEF   456  
DEF   567  
DEF   456  
DEF   456

E quero que apareça como:

ABC   1  
DEF   2

ABC   123  
ABC   123  
ABC   123  
ABC   456  
DEF   123  
DEF   456  
DEF   567  
DEF   456  
DEF   456

ABC  
   123    3  
   456    1  
DEF  
   123    1  
   456    3  
   567    1

Uma vez que você selecione 'Running total in', então escolha o cabeçalho para o conjunto de dados secundários (neste caso, seria o cabeçalho ou título da coluna do conjunto de dados que inclui 123, 456 e 567). Isso lhe dará um valor máximo com a contagem total de itens nesse conjunto, dentro de seu conjunto de dados primários.

A capacidade de fazer uma "contagem distinta" faz parte do Excel 2013, mas não é ativada automaticamente.

Descobri que a abordagem mais fácil é usar a opção {[[0]} sob Value Field Settings (à esquerda carregue no campo na Área Values. A opção para Distinct Count está no fundo da lista.

Aqui estão o antes (superior; normal Count) e depois (inferior; Distinct Count)

Não é necessário que a tabela seja ordenada para que a seguinte fórmula devolva um 1 por cada valor único presente.

Assumindo que o intervalo da tabela para os dados apresentados na pergunta é A1: B7 introduza a seguinte fórmula na célula C1:

=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))

Copie essa fórmula para todas as linhas e a última linha conterá:

=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))

Isto resulta em um 1 sendo devolvido na primeira vez que um registro é encontrado e 0 para todos os tempos depois.

Basta somar a coluna na sua tabela pivot

1. (Faça uma cópia dos seus dados primeiro)
2. concatenar as colunas
3. Remover duplicados na coluna concatenada
4. último eixo no conjunto resultante

Nota: Eu gostaria de incluir imagens para tornar isto ainda mais fácil de entender, mas não porque este é o meu primeiro post;)

no entanto , esta técnica pode causar problemas ao trabalhar com um grande conjunto de dados (o meu computador congelou em 50.000 linhas). Alguns métodos menos intensivos em processamento:

Controlo da unicidade

1. Ordenar pelas duas colunas (A, B neste exemplo)

2. Utilize uma fórmula que veja menos dados

   =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1) 
   

Verificações múltiplas de unicidade

Se precisar de verificar singularidade em colunas diferentes, você não pode confiar em dois tipos.

Em vez disso,

1. Ordenar uma única coluna (a)

2. Adicionar uma fórmula que abranja o número máximo de registos para cada grupo. Se ABC tiver 50 linhas, a fórmula será

   =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)
   

O Excel 2013 pode contar de forma distinta em pivots. Se não houver acesso a 2013, e for uma quantidade menor de dados, eu faço duas cópias dos dados raw, e na cópia b, selecione ambas as colunas e remova duplicados. Então faz o pivô e conta a tua coluna B.

Pode usar contadores para vários critérios,

=1 / COUNTIFS (A:A,A2,B: B, B2) e depois arrastar para baixo. Você pode colocar os critérios que quiser lá, mas tende a levar muito tempo para processar.

Passo 1. Adicionar uma coluna

Passo 2. Utilize a fórmula =IF(COUNTIF(C2:$C$2410,C2)>1,0,1) no primeiro registo

Passo 3. arrasta - o para todos os registos

Passo 4. Filtro ' 1 ' na coluna com a fórmula

Você pode fazer uma coluna adicional para armazenar a singularidade, e então somar que para cima na sua tabela de pivô.

O que quero dizer é que a célula {[[0]} deve ser sempre 1. A célula C2 deve conter a fórmula =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1). Copie esta fórmula para baixo de modo que a célula C3 conteria =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) e assim por diante.

Se você tem uma célula de cabeçalho, você vai querer mover todos estes para baixo uma linha e a sua fórmula C3 deve ser =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1).

Se tiver os dados ordenados.. sugiro a utilização da seguinte fórmula

=IF(OR(A2<>A3,B2<>B3),1,0)

Isto é mais rápido porque usa menos células para calcular.

Eu normalmente ordenar os dados pelo campo que eu preciso fazer a contagem distinta de então usar se (A2=A1, 0, 1); você começa então um 1 na linha superior de cada grupo de IDs. Simples e não leva tempo para calcular em grandes conjuntos de dados.

Também pode usar para a coluna auxiliar VLOOKUP. Eu testei e parece um pouco mais rápido do que COUNTIF.

Se estiver a usar o cabeçalho e os dados começarem na célula A2, então em qualquer célula em linha use esta fórmula e copie em todas as outras células da mesma coluna:

=IFERROR(IF(VLOOKUP(A2;$A$1:A1;1;0)=A2;0;1);1)

• Adicione uma nova coluna C e preencha C2 com a fórmula "=1/COUNTIF ($A:$A, A2) "
• arrastar a fórmula até ao resto da coluna
• pivô com a coluna a como Legenda da linha, e Sum{coluna C) em valores para obter o número de valores únicos na coluna a