Como contar o número de ocorrências repetidas

Question

Eu tenho uma planilha do Excel que parece mais ou menos assim:

+------------+-----------+
| First Name | Last Name |
+------------+-----------+
| John       | Paul      |
| Joe        | Jones     |
| John       | Paul      |
+------------+-----------+

O que eu preciso ser capaz de fazer é detectar a quantidade de entradas duplicadas e mostrar um número de linhas onde o nome e o sobrenome estavam lá pelo menos duas vezes.

Então, basicamente, estou procurando quantas pessoas na lista estavam na lista pelo menos duas vezes.

    

Answer 1

Aqui estão alguns métodos que você pode tentar.

Usando uma fórmula de matriz

Assumindo que os primeiros nomes estão em col A e os sobrenomes estão em col B (com A1 e B1 contendo os rótulos de cabeçalho "Nome" e "Sobrenome"), insira a seguinte fórmula em C2 .

=SUM(($A$2:$A$11=A3)*($B$2:$B$11=B3))

Depois de digitar a fórmula, pressione Ctrl + Deslocar + Digite para inseri-la como uma fórmula de matriz. Copie-o para as células em branco abaixo dele. A fórmula mostrará quantas vezes uma combinação de nomes aparece nos seus dados. Você pode então usar Formatação Condicional para ver mais visivelmente quais pessoas estão listadas em sua tabela pelo menos duas vezes.

Usando COUNTIF & Outra coluna

Assumindo que os primeiros nomes estão em col A e os sobrenomes estão em col B (com A1 e B1 contendo os rótulos de cabeçalho "Nome" e "Sobrenome"), insira a seguinte fórmula em C2 e copie-a para as células abaixo dela.

=A2&B2

Você receberá algo como "JohnPaul" ou "JoeJones". Você pode então ocultar esta coluna mais tarde.

Na célula D2, digite o ff. Fórmula. Copie-o para as outras células abaixo dele.

=COUNTIF($C$2:$C$11,C2)

Você verá o mesmo resultado da fórmula de matriz acima.

Usando a função COUNTIFS & Formatação Condicional

Eu acredito que esta função só está disponível no Excel 2007 e acima.

Digamos que os primeiros nomes estejam em A e os últimos nomes em B.

Destaque ambas as colunas e vá para Formatação condicional .
Crie uma nova regra > > Use uma fórmula para determinar quais células devem ser formatadas
Digite o ff. fórmula:

=COUNTIFS($A:$A,$A1,$B:$B,$B1)>=2

Defina seu formato preferido e clique em OK.
Na janela Gerenciador de regras de formatação condicional, defina Aplica-se a como =$A:$B

    

Answer 2

Se eu estivesse lidando com isso, primeiro classificaria as linhas de forma que meu sobrenome fosse primeiro nome, para que todas as duplicatas ficassem próximas uma da outra.

Digamos que os primeiros nomes estejam na coluna B e os últimos nomes na coluna C. Suponhamos também que a primeira linha de dados é a linha 2. Vamos supor também que colocaremos as contagens de duplicatas em execução na coluna A.

Em seguida, em CELL A2, coloque a seguinte fórmula:

=IF(AND(B2=B1,C2=C1),A1+1,1)

Agora, copie A2 e cole-o na coluna A para que essa fórmula esteja ao lado de todos os nomes da sua lista.

Se você fez tudo isso corretamente, a coluna A agora conterá uma contagem de duplicatas em execução. Se você quiser detectá-los mais facilmente, coloque a formatação condicional na coluna A para que os valores > 1 vai se destacar.

Boa sorte!

    

Answer 3

Para obter a contagem de valores com duplicatas na lista, use a seguinte fórmula:

=COUNTA(A1:A20)-SUM(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)=1,1,0))

Insira isso como uma fórmula de matriz pressionando Ctrl + Shift + Enter. Isso lhe dará a contagem em uma célula - sem necessidade de colunas extras.

Você também pode retornar os números de linha de cada entrada duplicada, mas precisará de uma pequena ajuda de uma função definida pelo usuário do VBA (UDF). Para criar a UDF no VBA, pressione Alt + F11 e insira um módulo. Cole o seguinte código no módulo.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr

out = ""
i = 1

Do While i <= UBound(rra, 1)
    If rra(i, 1) <> False Then
        out = out & rra(i, 1) & sep
    End If
    i = i + 1
Loop
out = Left(out, Len(out) - Len(sep))

CCARRAY = out
Exit Function

EH:
rra = rr.Value
Resume Next

End Function

Agora, para obter a lista de números de linha de duplicados, insira a seguinte fórmula em uma célula:

=CCARRAY(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)>1,ROW(B1:B20),0),", ")

Insira essa fórmula como uma fórmula de matriz pressionando Ctrl + Shift + Enter.

Você também pode usar essa UDF para obter uma lista dos valores reais que possuem duplicatas. A fórmula a seguir funcionará para essa finalidade:

=CCARRAY(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)>1,A1:A20&" "&B1:B20,0),", ")

Insira também isso como uma fórmula de matriz.