Tabela de Pivô simples para contar valores únicos
ABC 123
ABC 123
ABC 123
DEF 456
DEF 567
DEF 456
DEF 456
O que eu quero é uma tabela giratória que me mostre isto:
ABC 1
DEF 2
a tabela de pivô simples que eu crio só me dá isto (uma contagem de quantas linhas):
ABC 3
DEF 4
Mas quero o número de valores únicos.
O que estou realmente a tentar fazer é descobrir quais os valores no a primeira coluna não tem o mesmo valor na segunda coluna para todas as linhas. Em outras palavras, " ABC " é "bom"," DEF "é"mau"
Tenho a certeza que há uma maneira mais fácil de o fazer, mas pensei em dar uma hipótese à mesa pivot...
17 answers
Inserir uma terceira coluna e na célula C2
colar esta fórmula
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
E copia-o. Agora Crie o seu pivô com base na 1ª e 3ª coluna. Ver instantâneo
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
Mas algo mais do género:
ABC 123
ABC 123
ABC 123
ABC 456
DEF 123
DEF 456
DEF 567
DEF 456
DEF 456
E queria que aparecesse como:
ABC
123 3
456 1
DEF
123 1
456 3
567 1
Eu encontrei a melhor maneira de colocar os meus dados neste formato e depois ser capaz de manipulá-los ainda mais era usar
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.
Então copiei estes dados, colei-os como valores, e depois coloquei-os noutra tabela de pivô para manipulá-los mais facilmente. Para tua informação, tinha cerca de 25 cêntimos. milhões de linhas de dados então isso funcionou muito melhor do que algumas das abordagens de fórmula, especialmente aquelas que tentam comparar em duas colunas/conjuntos de dados porque ele continuou quebrando a aplicação.A capacidade de fazer uma "contagem distinta" faz parte do Excel 2013, mas não é ativada automaticamente.
Então, se você executar uma cópia do EXCEL 2013, aqui está uma maneira brilhante de resolver isso sem o incómodo de passar por uma função: http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/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
- (Faça uma cópia dos seus dados primeiro)
- concatenar as colunas
- Remover duplicados na coluna concatenada
- ú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
- Ordenar pelas duas colunas (A, B neste exemplo)
-
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,
- Ordenar uma única coluna (a)
-
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