Excel: conta o número de vezes que um valor aparece duas vezes e somente duas vezes em uma coluna

Question

Eu tenho um conjunto de dados de 100.000 linhas. Ele é configurado de tal maneira que a coluna A contém um nome de grupo e, em seguida, repete o nome do grupo para o número de membros exclusivos desse grupo. Eu estou tentando obter uma contagem de quantas vezes um valor aparece duas vezes e apenas duas vezes.

• Um valor nunca aparecerá apenas uma vez - sempre haverá "nome do grupo" imediatamente seguido por quantos membros estiverem nesse grupo, em linhas individuais. Portanto, para qualquer entidade distinta, sempre há pelo menos duas linhas.
• Frequentemente, um valor aparece três ou mais vezes.
• Se o valor aparecer 3 ou mais vezes, não quero incluir nenhuma dessas linhas na contagem. Estou realmente procurando o número de vezes que um par distinto aparece.

Answer 1

Supondo que seus dados estejam em A1: A100000

1) Copie todos os valores exclusivos em uma coluna separada (usando Data - > advanced filter) - Usarei a coluna C para o meu exemplo.

2) Coloque a seguinte fórmula: =Countif(A$1:A$100000, C1) em D1, preencha cada célula em D para cada valor exclusivo em C.

3) Em outra célula use a seguinte fórmula: =Countif(D1:D??, 2) (onde ?? é a última linha das colunas C e D)

    

Answer 2

Copie a seguinte fórmula na coluna b:

=COUNTIF($A$1:$A$100000,A1)=2

Isso identificará todos os pares. No entanto, mostrará ambas as entradas no par. O que eu normalmente faço nesses casos (mesmo que isso quebre a providência de dados) é copiar essa lista filtrada para outra planilha e remover duplicatas.

Se você quiser fazer isso de uma maneira repetível que mantenha a providência, recomendo usar uma fórmula de matriz de tipo de lista única depois de executar a contagem

A opção B, é usar uma tabela dinâmica, colocar seus valores na coluna A nas linhas, contar (A) nos valores e filtrar linhas nos valores em que count = 2