Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

Compare duas colunas para correspondências e diferenças no Excel

Este tutorial fala sobre como comparar duas colunas no Excel, que é um trabalho normal do Excel em nosso trabalho diário. A comparação de duas colunas pode ser feita de maneiras diferentes no Excel, como comparar duas colunas linha por linha ou células por células, comparar duas colunas para destacar correspondências ou diferenças, etc. Aqui, este artigo cobre a maioria dos cenários possíveis de comparação de duas colunas que você pode conhecer e espero que possa ajudá-lo.

Este tutorial cobre

1. Compare duas colunas linha por linha

1.1 Compare células na mesma linha para correspondência exata

1.2 Compare células na mesma linha para correspondência exata ou indiferente a maiúsculas (usando a fórmula IF)

1.3 Compare células na mesma linha e destaque os dados correspondentes ou não correspondentes (usando a Formatação Condicional)

1.4 Compare duas colunas linha por linha e destaque os valores incompatíveis (usando VBA)

2. Compare duas colunas em células e selecione ou destaque dados duplicados ou únicos

2.1 Compare duas colunas, célula por célula, e exiba o resultado de comparação em outra coluna (usando fórmula)

2.2 Compare e destaque dados duplicados ou únicos (usando Formatação Condicional)

2.3 Compare e selecione ou destaque dados duplicados ou únicos (usando uma ferramenta útil)

2.4 Compare duas colunas e liste as duplicatas exatas em outra coluna (usando o código VBA)

2.5 Compare duas colunas e destaque duplicatas (usando código VBA)

3.Compare várias colunas na mesma linha

3.1 Encontre correspondências completas em todas as células na mesma linha (usando a fórmula IF)

3.2 Encontre correspondências em quaisquer duas células na mesma linha (usando a fórmula IF)

3.3 Encontre correspondências completas em todas as células na mesma linha e realce (usando a formatação condicional)

3.4 Compare várias colunas e destaque as diferenças de linha

4. Compare duas colunas e encontre os pontos de dados ausentes

4.1 Compare e encontre os pontos de dados ausentes (usando a fórmula VLOOKUP ou MATCH)

4.2 Compare duas colunas e liste os dados ausentes abaixo (usando a fórmula INDEX)

4.3 Compare e encontre os pontos de dados ausentes e liste-os em outra coluna (usando uma ferramenta útil)

4.4 Compare duas colunas e liste os dados ausentes abaixo (usando VBA)

5. Compare duas colunas (datas) se for maior ou menor que

5.1 Compare duas colunas se for maior ou menor que (usando fórmula)

5.2 Compare duas colunas se for maior ou menor do que o formato (usando a Formatação Condicional)

6. Compare duas colunas e retorne o valor em outra coluna

6.1 Extraia os dados de correspondência exata (usando fórmula ou uma ferramenta útil)

6.2 Extraia os dados de correspondência parcial (usando fórmula)

7. Compare duas colunas e conte correspondências ou diferenças

7.1 Compare duas colunas e conte as correspondências (usando a fórmula SUMPRODUCT)

7.2 Compare duas colunas e conte correspondências ou diferenças (usando uma ferramenta útil)

8. Compare duas colunas com curinga

9. Exclua diferenças ou correspondências

10. Compare duas colunas e calcule a variação percentual entre

11. Compare dois intervalos

11.1 Compare dois intervalos por célula

11.2 Compare dois intervalos se os dados estiverem na mesma ordem

Importante

Neste tutorial, ele fornece alguns dados de exemplo para explicar melhor os métodos de comparação de duas colunas em vários casos. Com base em seu conjunto de dados, você pode precisar alterar ou ajustar alguns conteúdos (referências), no entanto, os princípios básicos permaneceriam os mesmos. Ou baixe diretamente os exemplos de cada caso se deseja apenas verificar se os métodos funcionam ou não.


1. Compare duas colunas linha por linha

Abaixo está um conjunto de dados em que preciso verificar na mesma linha se os nomes na coluna A são iguais aos da coluna B ou não.
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

1.1 Compare células na mesma linha para correspondência exata


Geralmente, se você deseja comparar duas colunas linha por linha para uma correspondência exata, você pode usar a fórmula abaixo:

= B2 = C2

doc compare colunas 1.1 1

Press entrar e arraste a alça de preenchimento para a célula D8. Se a fórmula retornar TRUE, os valores das duas colunas são totalmente iguais, se retornar FALSE, eles são diferentes.
doc compare colunas 1.1 1

1.2 Compare células na mesma linha para correspondência exata ou indiferente a maiúsculas (usando a fórmula IF)


Se você deseja comparar duas colunas linha por linha para não diferenciar maiúsculas de minúsculas ou obter mais descrições, como Corresponder, Discordar, você pode usar a função IF.

Comparando células na mesma linha para correspondência exata

Se você quiser usar os textos “Match” e “Mismatch” para descrever os resultados de comparação, use a fórmula abaixo:

=IF(EXACT(B2,C2),"Match","Mismatch")

doc compare colunas 1.1 1

Press entrar para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento automático para a célula D8.
doc compare colunas 1.1 1

Comparando células na mesma linha para correspondência sem distinção entre maiúsculas e minúsculas

Se você deseja comparar células sem distinção entre maiúsculas e minúsculas, pode usar a fórmula abaixo:

=IF(B2=C2,"Match","Mismatch")

doc compare colunas 1.1 1

Press entrar para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento automático para a célula E8.
doc compare colunas 1.1 1

Observação

Nas fórmulas acima, você pode alterar os textos “Match” e “Mismatch” para sua própria descrição.

1.3 Compare células na mesma linha e destaque os dados correspondentes ou não correspondentes (usando a Formatação Condicional)


Se você quiser destacar os valores correspondentes ou diferentes, o Formatação condicional recurso pode ajudá-lo.

1. Selecione as duas colunas que são usadas para comparação com (B2: C8, excluindo cabeçalhos de coluna) e clique em Página Inicial > Formatação condicional > Nova regra.
doc compare colunas 1.1 1

2. No popping Nova regra de formatação caixa de diálogo, clique para escolher Use uma fórmula para determinar quais células formatar no Selecione um tipo de regra seção, em seguida, digite = $ B2 = $ C2 na caixa de texto de Formate os valores onde esta fórmula for verdadeira.
doc compare colunas 1.1 1

3. Agora clique Formato para exibir o formatar células diálogo, então sob Preencher guia, escolha uma cor que você precisa para destacar as correspondências.

Ou você pode alterar o tamanho da fonte, o tamanho da fonte, as bordas das células ou o formato do número para destacar as correspondências que você precisa em outras guias.
doc compare colunas 1.1 1

4. Clique OK > OK para fechar as caixas de diálogo, as células na mesma linha serão destacadas se forem iguais.
doc compare colunas 1.1 1

Se você quiser destacar os valores incompatíveis, você pode usar isso no = $ B2 <> $ C2 no Formate os valores onde esta fórmula for verdadeira caixa de texto no Editar regra de formatação diálogo.
doc compare colunas 1.1 1

Em seguida, as diferenças de duas colunas na mesma linha serão destacadas com uma cor especificada.
doc compare colunas 1.1 1

1.4 Compare duas colunas linha por linha e destaque os valores incompatíveis (usando VBA)


Se você deseja comparar duas colunas linha por linha com o código VBA, este tutorial o satisfaz.

1. Ative a planilha que contém as duas colunas usadas para comparar, pressione Alt + F11 chaves para habilitar o Microsoft Visual Basic para Aplicações janela.

2. Na caixa de diálogo pop-up, clique em inserção > Módulo.
doc compare colunas 1.1 1

3. Em seguida, copie e cole a macro abaixo no novo script do módulo.

VBA: compare duas colunas linha por linha e destaque as diferenças

Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range

Dim xWs As Worksheet

Dim xFI As Integer

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

 

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns"

    GoTo SRg

End If

 

Set xWs = xRg.Worksheet

For xFI = 1 To xRg.Rows.Count

    If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then

        xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.

End If

Next xFI

End Sub

doc compare colunas 1.1 1

4. pressione F5 para executar o código, então uma caixa de diálogo aparecerá para selecionar as duas colunas.
doc compare colunas 1.1 1

5. Clique OK. Em seguida, as diferenças de duas colunas foram destacadas com uma cor de fundo.
doc compare colunas 1.1 1

Observação

Você pode alterar a cor de destaque com base em sua própria necessidade, alterando o índice de cores no código, a referência do índice de cores:
doc compare colunas 1.1 1


2. Compare duas colunas nas células e selecione ou destaque dados duplicados ou únicos

Nesta parte, o conjunto de dados é mostrado abaixo, e você deseja encontrar todos os valores que estão na coluna B e na coluna C simultaneamente, ou encontre os valores apenas na coluna B.
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

2.1 Compare duas colunas, células por células, e exiba os resultados de comparação em outra coluna (usando fórmula)


Aqui você pode usar a fórmula combinada com as funções IF e CONT.SE para comparar duas colunas e encontrar os valores que estão na coluna B, mas não na coluna C.

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

doc compare colunas 1.1 1

Press entrar e arraste a alça de preenchimento automático para a célula D8.
doc compare colunas 1.1 1

Observação

1. Esta fórmula compara duas colunas sem distinção entre maiúsculas e minúsculas.

2. Você pode alterar a descrição “Não em C” e “Sim em C” para outras.

3. Se você quiser comparar duas colunas inteiras, altere o intervalo fixo $ C $ 2: $ C $ 8 para $ C: $ C.

2.2 Compare e destaque dados duplicados ou únicos (usando Formatação Condicional)


O Formatação condicional O recurso do Excel é poderoso, aqui você pode usá-lo para comparar duas colunas, célula por célula, e então destacar as diferenças ou correspondências conforme necessário.

Destaque todas as duplicatas ou valores únicos em duas colunas

1. Selecione duas colunas com as quais você irá comparar e clique em Página Inicial > Formatação condicional > Reverter as regras das células > Valores Duplicados.
doc compare colunas 1.1 1

2. No popping Valores Duplicados caixa de diálogo, escolha um formato de realce que você precisa na lista suspensa de valores com.
doc compare colunas 1.1 1

3. Clique OK. Em seguida, as duplicatas em duas colunas foram destacadas.
doc compare colunas 1.1 1

Observação

Se você deseja destacar os valores únicos (as diferenças) em duas colunas, clique também Página Inicial > Formatação condicional > Reverter as regras das células > Valores Duplicados para exibir o Valores Duplicados diálogo, mude o Duplicar para único na lista suspensa à esquerda, escolha outro formato na lista suspensa de valores e clique em OK.
doc compare colunas 1.1 1

Os valores exclusivos serão destacados.
doc compare colunas 1.1 1

Encontre e destaque os valores na coluna B, se também estiver na coluna C

Se você quiser destacar os valores na coluna B que também estão na coluna C, o CFormatação adicional recurso também pode lhe fazer um favor.

1. Selecione a coluna B2: B8, clique Página Inicial > Formatação condicional > Nova regra.
doc compare colunas 1.1 1

2. No Nova regra de formatação diálogo, escolha Use uma fórmula para determinar quais células formatar de Selecione um tipo de regra seção, em seguida, digite = CONT.SE ($ C $ 2: $ C $ 8, $ B2)> 0 na caixa de texto de Formate os valores onde esta fórmula for verdadeira.
doc compare colunas 1.1 1

3. clique Formato para ir para o formatar células diálogo, sob Preencher guia, escolha uma cor para destacar as correspondências.

Você pode usar outros formatos para destacar os valores nas guias Fonte, Número e Borda.
doc compare colunas 1.1 1

4. Clique OK > OK. Em seguida, os valores na coluna B que também existem na coluna C foram destacados com a cor especificada.
doc compare colunas 1.1 1

Se você deseja destacar os valores que apenas na coluna B, mas não na coluna C, repita as etapas acima, mas altere a fórmula na etapa 2 para = CONT.SE ($ C $ 2: $ C $ 8, $ B2) = 0 no Nova regra de formatação diálogo.
doc compare colunas 1.1 1

Em seguida, escolha outra cor para formatar os valores.
doc compare colunas 1.1 1

Observação

Aqui, a Formatação Condicional compara duas colunas sem distinção entre maiúsculas e minúsculas.

2.3 Compare e selecione ou destaque dados duplicados ou únicos (usando uma ferramenta útil)


Às vezes, depois de comparar duas colunas, você pode realizar outras ações nas correspondências ou diferenças, como seleção, exclusão, cópia e assim por diante. Neste caso, uma ferramenta útil - Selecionar células iguais e diferentes of Kutools for Excel pode selecionar diretamente as correspondências ou diferenças para melhor fazer a próxima operação, também pode destacar diretamente os valores.
doc compare colunas 1.1 1

Depois de instalação grátis Kutools para Excel, faça o seguinte:

1. Clique Kutools > Selecionar > Selecionar células iguais e diferentes.
doc compare colunas 1.1 1

2. Em seguida, no Selecionar células iguais e diferentes caixa de diálogo, faça o seguinte:

1) No Encontre valores dentro e De acordo com o seções, escolha duas colunas com as quais deseja comparar;

2) Escolha Cada fila opção;

3) Escolha Mesmos valores or Valores Diferentes como você precisa;

4) Se você deseja destacar os valores, escolha a cor que você precisa.
doc compare colunas 1.1 1

3. Clique Ok, uma caixa de diálogo aparecerá para lembrá-lo do número de valores que foram encontrados, clique OK para fechar o diálogo. E ao mesmo tempo, os valores foram selecionados, agora você pode excluir ou copiar ou fazer outras operações.
doc compare colunas 1.1 1

Se você verificar o Preencher a cor de fundo e Cor da fonte de preenchimento caixas de seleção, o resultado é mostrado como este:
doc compare colunas 1.1 1

Observação

Se você deseja comparar com diferenciação de maiúsculas e minúsculas, verifique o Maiúsculas e minúsculas opção.

Esta ferramenta permite comparar duas colunas em planilhas diferentes.

2.4 Compare duas colunas e liste as duplicatas exatas em outra coluna (usando o código VBA)


Se você quiser listar os valores correspondentes em outra coluna após comparar duas colunas célula por célula, aqui o código de macro abaixo pode ajudá-lo.

1. Ative a planilha com a qual deseja comparar duas colunas e pressione Alt + F11 chaves para exibir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Módulo no Microsoft Visual Basic para Aplicações janela.
doc compare colunas 1.1 1

3. Em seguida, copie e cole o código abaixo no novo script do módulo em branco.

VBA: lista as duplicatas ao lado da coluna após comparar duas colunas

Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

Dim xIntSR, xIntER, xIntSC, xIntEC As Integer

On Error Resume Next

SRg:

Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SsRg

End If

Set xWs = xRg.Worksheet

 

For Each xRgF1 In xRgC1

    For Each xRgF2 In xRgC2

        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value

    Next xRgF2

Next xRgF1

End Sub

doc compare colunas 1.1 1

4. Pressione F5 chave para executar o código, há duas caixas de diálogo que aparecem uma a uma para você selecionar duas colunas separadamente.

Importante: Selecione a coluna da esquerda primeiro e, em seguida, selecione a coluna da direita na segunda caixa de diálogo, ou as duplicatas substituirão os dados originais na segunda coluna.
doc compare colunas 1.1 1doc compare colunas 1.1 1

5. Clique OK > OK, então as correspondências foram listadas automaticamente na coluna direita das duas colunas.
doc compare colunas 1.1 1

Observação

O código VBA compara duas colunas com distinção entre maiúsculas e minúsculas.

2.5 Compare duas colunas e destaque duplicatas (usando código VBA)


Se você quiser comparar duas colunas célula por célula e então destacar as correspondências, você pode tentar o código abaixo.

1. Ative a planilha com a qual deseja comparar duas colunas e pressione Alt + F11 chaves para exibir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Módulo no Microsoft Visual Basic para Aplicações janela.
doc compare colunas 1.1 1

3. Copie o código de macro abaixo e cole-o no novo espaço em branco Módulo script.

VBA: compare duas colunas e destaque duplicatas

Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019

Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

SRg:

Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SsRg

End If

 

    For Each xRgF1 In xRgC1

        For Each xRgF2 In xRgC2

            If xRgF1.Value = xRgF2.Value Then

               xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)

            End If

        Next

    Next

End Sub

doc compare colunas 1.1 1

4. Pressione F5 chave para executar o código. Na primeira caixa de diálogo que aparece, selecione a coluna com a qual deseja comparar os valores duplicados.
doc compare colunas 1.1 1

5. Clique OK. Na segunda caixa de diálogo, selecione a coluna em que deseja destacar os valores duplicados.
doc compare colunas 1.1 1

6. Clique OK. Agora, as duplicatas na segunda coluna foram destacadas com uma cor de fundo após a comparação com a primeira coluna.
doc compare colunas 1.1 1

Observação

1. O código compara colunas com distinção entre maiúsculas e minúsculas.

2. Você pode alterar a cor de destaque com base em sua própria necessidade, alterando o índice de cor no código, a referência do índice de cor:
doc compare colunas 1.1 1


3. Compare várias colunas na mesma linha

Às vezes, você pode querer comparar mais de duas colunas na mesma linha, como o conjunto de dados conforme a captura de tela mostrada abaixo. Aqui nesta seção, ele lista diferentes métodos de comparação de várias colunas.
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

3.1 Encontre correspondências completas em todas as células na mesma linha (usando a fórmula IF)


Para encontrar correspondências completas nas colunas da mesma linha, a fórmula IF abaixo pode ajudá-lo.

=IF(AND(B2=C2, B2=D2), "Full match", "Not")

 doc compare colunas 1.1 1

Se as células na mesma linha corresponderem umas às outras, “Correspondência total” será exibido ou “Não” será exibido.

Press entrar para obter o primeiro resultado de comparação e arraste a alça de preenchimento automático para a célula E7.
doc compare colunas 1.1 1

Observação

1. A fórmula compara colunas sem distinção entre maiúsculas e minúsculas.

2. Se você precisar comparar mais ou igual a três colunas, você pode usar a fórmula abaixo:

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

 doc compare colunas 1.1 1

Na fórmula, 3 é o número de colunas, você pode alterá-lo para atender às suas necessidades.

3.2 Encontre correspondências em quaisquer duas células na mesma linha (usando a fórmula IF)


Às vezes, você deseja descobrir se quaisquer duas colunas na mesma linha são correspondidas, você pode usar a fórmula IF abaixo.

=IF(OR(B2=C2, C2=D2, B2=D2), "Match", "Not")

 doc compare colunas 1.1 1

Nesta fórmula, você precisa comparar qualquer par de células na mesma linha. “Corresponder” indica que existem quaisquer duas células correspondidas, se não houver células correspondendo, a fórmula exibir “Não”, você pode alterar os textos conforme necessário.

Press entrar e arraste a alça de preenchimento para a célula E7.
doc compare colunas 1.1 1

Observação

1. Esta fórmula não oferece diferenciação de maiúsculas e minúsculas.

2. Se houver muitas colunas necessárias para comparar, comparar qualquer par de células na fórmula pode ser muito longo. Neste caso, você pode considerar o uso da fórmula abaixo, que combina as funções IF e CONT.SE.

=IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,"Not","Match")

 doc compare colunas 1.1 1

3.3 Encontre correspondências completas em todas as células na mesma linha e realce (usando a formatação condicional)


Se quiser destacar as linhas em que todas as células correspondem entre si, você pode usar o Formatação condicional recurso no Excel.

1. Selecione o intervalo que você usa e clique em Página Inicial > Formatação condicional > Nova regra.
doc compare colunas 1.1 1

2. No Nova regra de formatação diálogo, escolha Use uma fórmula para determinar quais células formatar de Selecione um tipo de regra seção, então você pode usar uma das fórmulas abaixo na Formate os valores onde esta fórmula for verdadeira caixa de texto.

=AND($B2=$C2, $B2=$D2)

Or

=COUNTIF($B2:$D2, $B2)=3

 doc compare colunas 1.1 1

Importante: Se o número de colunas for maior que três, supondo que seja 5, as fórmulas devem ser alteradas para:

=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2)

Or

=COUNTIF($B2:$F2, $B2)=5

3. Clique Formato para ir para a caixa de diálogo Formatar células e, em seguida, escolha uma cor de preenchimento ou outra formatação de célula para destacar as linhas.
doc compare colunas 1.1 1

4. Clique OK > OK, agora apenas as linhas nas quais todas as células coincidem serão destacadas.
doc compare colunas 1.1 1

Observação

As fórmulas acima não aceitam distinção entre maiúsculas e minúsculas.

3.4 Compare várias colunas e destaque as diferenças de linha


Se você deseja destacar as diferenças de linha, o que significa que compara as células da coluna uma a uma, e encontra as diferentes células de acordo com a primeira coluna, você pode usar o recurso integrado do ExcelIr para especial.

1. Selecione o intervalo em que deseja destacar as diferenças de linha e clique em Página Inicial > Encontrar e selecionar > Ir para especial.
doc compare colunas 1.1 1

2. No popping Ir para especial diálogo, verificar Diferenças de linha opção.
doc compare colunas 1.1 1

3. Clique OK. Agora as diferenças de linha foram selecionadas.
doc compare colunas 1.1 1

4. Agora mantenha as células selecionadas, clique Página Inicial > Cor de preenchimento para selecionar uma cor no menu suspenso.
doc compare colunas 1.1 1

Observação

Este método compara células sem distinção entre maiúsculas e minúsculas.


4. Compare duas colunas e encontre os pontos de dados ausentes

Supondo que haja duas colunas, a coluna B é mais longa e a coluna C é mais curta, conforme mostrado na imagem abaixo. Em comparação com a coluna B, como descobrir os dados ausentes na coluna C?
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

4.1 Compare e encontre os pontos de dados ausentes (usando a fórmula VLOOKUP ou MATCH)


Se você deseja apenas identificar quais dados estão faltando após comparar duas colunas, você pode usar uma das fórmulas abaixo:

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Or

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

 doc compare colunas 1.1 1

Press entrar e arraste a alça de preenchimento automático sobre a célula D10. Agora, se os dados estiverem nas colunas B e C, a fórmula retornará FALSO; se os dados estiverem apenas na coluna B, mas faltarem na coluna C, a fórmula retornará VERDADEIRO.
doc compare colunas 1.1 1

Observação

As duas fórmulas acima comparam dados sem distinção entre maiúsculas e minúsculas.

4.2 Compare duas colunas e liste os dados ausentes abaixo (usando a fórmula INDEX)


Se você quiser listar os dados ausentes abaixo da coluna mais curta depois de comparar duas colunas, a fórmula de matriz INDEX pode ajudá-lo.

Na célula abaixo da coluna mais curta, supondo a célula C7, digite a fórmula abaixo:

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

 doc compare colunas 1.1 1

Press Shift + Ctrl + Enter para obter os primeiros dados ausentes e, em seguida, arraste a alça de preenchimento automático para baixo até retornar o valor de erro # N / A.
doc compare colunas 1.1 1

Em seguida, você pode remover o valor de erro e todos os dados ausentes foram listados abaixo da coluna mais curta.

Observação

Esta fórmula compara células sem distinção entre maiúsculas e minúsculas.

4.3 Compare e encontre os pontos de dados ausentes e liste-os em outra coluna (usando uma ferramenta útil)


Se você quiser fazer alguma operação de acompanhamento nos dados ausentes após comparar duas colunas, como listar os dados ausentes em outra coluna ou complementar os dados ausentes abaixo da coluna mais curta, você pode tentar uma ferramenta útil-Selecionar células iguais e diferentes of Kutools for Excel.

1. Clique Kutools > Selecionar > Selecionar células iguais e diferentes.
doc compare colunas 1.1 1

2. No Selecionar células iguais e diferentes caixa de diálogo, faça como abaixo:

1) em Encontre valores em seção, escolha a coluna mais longa que contém a lista completa.
In De acordo com o seção, escolha a coluna mais curta que perde alguns dados.

2) Escolha Cada fila opção.

3) Escolha Valores Diferentes opção.
doc compare colunas 1.1 1

3. Clique Ok, uma caixa de diálogo aparecerá para lembrá-lo do número de dados ausentes, clique OK para fechá-lo. Em seguida, os dados ausentes foram selecionados.
doc compare colunas 1.1 1

Agora você pode pressionar Ctrl + C para copiar os dados ausentes selecionados e colá-los pressionando Ctrl + V chaves abaixo da coluna mais curta ou outra nova coluna conforme necessário.
doc compare colunas 1.1 1doc compare colunas 1.1 1

Observação

Marcando o Não diferencia maiúsculas de minúsculas opção na caixa de diálogo Selecionar células iguais e diferentes irá comparar duas colunas com distinção entre maiúsculas e minúsculas.

4.4 Compare duas colunas e liste os dados ausentes abaixo (usando VBA)


Aqui está um código de macro que pode preencher os dados ausentes abaixo das duas colunas.

1. Abra a folha que você usa, pressione Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Módulo para criar um novo módulo para colocar abaixo do código VBA.
doc compare colunas 1.1 1

VBA: compare duas colunas e preencha os dados ausentes

Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021

Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range

Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer

Dim xWs As Worksheet

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns as a range"

    GoTo SRg

End If

Set xWs = xRg.Worksheet

 

xIntSC = xRg.Column

xIntEC = xRg.Columns.Count + xIntSC - 1

xIntSR = xRg.Row

xIntER = xRg.Rows.Count + xIntSR - 1

 

Set xRg = xRg.Columns

Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))

Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))

xIntR = 1

For Each xFRg In xRgC1

    If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then

        xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

xIntR = 1

For Each xFRg In xRgC2

    If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then

        xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

End Sub

doc compare colunas 1.1 1

3. Em seguida, pressione F5 para executar o código, uma caixa de diálogo é exibida para selecionar as duas colunas de comparação.
doc compare colunas 1.1 1

4. Clique OK. Agora, os dados ausentes foram listados abaixo das duas colunas.
doc compare colunas 1.1 1

Observação

O código compara células sem distinção entre maiúsculas e minúsculas.


5. Compare duas colunas (datas) se for maior ou menor que

Se houver duas colunas de datas conforme a captura de tela mostrada abaixo, você pode querer comparar qual data está mais tarde na mesma linha.
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

5.1 Compare duas colunas se for maior ou menor que (usando fórmula)


Você pode usar a fórmula simples para descobrir rapidamente se a data 1 é posterior à data 2 em cada linha.

=IF(B2>C2,"Yes","No")

 doc compare colunas 1.1 1

Press entrar para obter o primeiro resultado comparado e, em seguida, arraste a alça de preenchimento automático para a célula C6 para obter todos os resultados.
doc compare colunas 1.1 1

Observação

1. No Excel, as datas são armazenadas como séries numéricas; na verdade, são números. Portanto, você aplica a fórmula para comparar as datas diretamente.

2. Se você quiser comparar se a data 1 é anterior à data 2 em cada linha, altere o símbolo > para < na fórmula.

5.2 Compare duas colunas se for maior ou menor do que o formato (usando a Formatação Condicional)


Se desejar destacar as células na coluna Data 1, se forem maiores que Data 2, você pode usar o Formatação condicional recurso no Excel.

1. Selecione as datas na coluna B (Data1) e clique em Página Inicial > Formatação condicional > Nova regra.
doc compare colunas 1.1 1

2. No Nova regra de formatação diálogo, selecione Use uma fórmula para determinar quais células formatar no Selecione um tipo de regra seção e digite a fórmula = $ B2> $ C2 na caixa de texto de Formate os valores onde esta fórmula for verdadeira.
doc compare colunas 1.1 1

Se você quiser destacar as células na coluna B que são menores do que as da coluna C, use a fórmula = $ B2 <$ C2.

3. Clique Formato botão para abrir o formatar células caixa de diálogo e escolha o tipo de formato que você precisa.
doc compare colunas 1.1 1

4. Clique OK > OK. Em seguida, as células na coluna Data1 que são maiores do que as da coluna Data2 foram destacadas.
doc compare colunas 1.1 1


6. Compare duas colunas e retorne o valor em outra coluna

Por exemplo, existem duas tabelas, agora você deseja comparar a coluna B e a coluna E, em seguida, encontre os preços relativos da coluna C e retorne-os na coluna F.
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

6.1 Extraia os dados de correspondência exata (usando fórmula ou uma ferramenta útil)


Aqui, ele apresenta algumas fórmulas úteis e uma ferramenta para resolver esse trabalho.

Método de fórmula

Na célula F2 (a célula em que você deseja colocar o valor retornado), use uma das fórmulas abaixo:

=VLOOKUP(E2,$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

 doc compare colunas 1.1 1

Press entrar chave, e o primeiro valor foi encontrado. Em seguida, arraste a alça de preenchimento automático para a célula F6, todos os valores foram extraídos.
doc compare colunas 1.1 1

Observação

1. As fórmulas não aceitam distinção entre maiúsculas e minúsculas.

2. O número 2 na fórmula indica que você encontra os valores correspondentes na segunda coluna da matriz da tabela.

3. Se as fórmulas não puderem encontrar o valor relativo, ela retornará o valor de erro # N / A.
doc compare colunas 1.1 1

Um método de ferramenta útil

Se você se confunde com fórmulas, pode tentar a ferramenta útil - Fórmula Helper of Kutools for Excel, que contém várias fórmulas para resolver a maioria dos problemas no Excel. Com ele, você só precisa selecionar o intervalo, mas não precisa se lembrar como as fórmulas usam.

Depois de instalação grátis Kutools para Excel, faça o seguinte:

1. Selecione a célula F2 (a célula em que deseja colocar o valor de retorno) e clique em Kutools > Fórmula Helper > Pesquisa e Referência > Procure um valor na lista.
doc compare colunas 1.1 1

2. No Auxiliar de Fórmulas diálogo, à direita Entrada de argumentos seção, selecione o intervalo da matriz da tabela, valor de pesquisa e especifique em qual coluna você deseja procurar o valor de retorno.

Ponta: Lembre-se de alterar a referência absoluta no Valor_procurado caixa para relativa, ou você só pode encontrar o primeiro valor.
doc compare colunas 1.1 1

3. Clique Ok, o primeiro valor foi encontrado e arraste a alça de preenchimento automático para a célula F6.
doc compare colunas 1.1 1

6.2 Extraia os dados de correspondência parcial (usando fórmula)


Se houver alguma pequena diferença entre as duas colunas comparadas, como mostrado na captura de tela abaixo, os métodos acima não funcionarão.
doc compare colunas 1.1 1

Escolha uma das fórmulas abaixo para resolver este trabalho:

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

 doc compare colunas 1.1 1

Press entrar e arraste a alça de preenchimento automático para a célula F5, todos os valores foram encontrados.
doc compare colunas 1.1 1

Observação

1. As fórmulas não aceitam distinção entre maiúsculas e minúsculas.

2. O número 2 na fórmula indica que você encontra os valores de retorno na segunda coluna da matriz da tabela.

3. Se as fórmulas não puderem encontrar o valor relativo, ela retornará o valor de erro # N / A.

4. * na fórmula é um curinga que é usado para indicar qualquer caractere ou strings.


7. Compare duas colunas e conte correspondências ou diferenças

Abaixo o conjunto de dados é um exemplo para comparar e contar correspondências ou diferenças.
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

7.1 Compare duas colunas e conte as correspondências (usando a fórmula SUMPRODUCT)


A fórmula SUMPRODUCT pode contar rapidamente as correspondências em duas colunas.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

 doc compare colunas 1.1 1

Press entrar chave para obter o resultado.
doc compare colunas 1.1 1

Observação

A fórmula conta células sem distinção entre maiúsculas e minúsculas.

7.2 Compare duas colunas e conte correspondências ou diferenças (usando uma ferramenta útil)


Se você deseja contar correspondências ou diferenças entre duas colunas, a ferramenta útil - Selecionar células iguais e diferentes.

Depois de instalação grátis Kutools para Excel, faça o seguinte:

1. Clique Kutools > Selecionar > Selecionar células iguais e diferentes.
doc compare colunas 1.1 1

2. No Selecionar células iguais e diferentes caixa de diálogo, escolha os intervalos de duas colunas em Encontre valores em e De acordo com o seções separadamente e, em seguida, escolha Cada filae escolha Mesmos valores or Valores Diferentes opção conforme você precisa.
doc compare colunas 1.1 1

3. Clique Ok. Uma caixa de diálogo é exibida para informar quantas células correspondentes ou diferentes estão selecionadas.

Células correspondentes
doc compare colunas 1.1 1

Células diferentes
doc compare colunas 1.1 1


8. Compare duas colunas com curinga

Supondo que aqui está uma lista de dados na coluna B, e você deseja contar as células que contêm ”Apple” ou “Candy” na coluna D, conforme a captura de tela abaixo:
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

Para contar se uma célula contém um ou mais valores, você pode usar uma fórmula com curingas para resolver esse problema.

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

 doc compare colunas 1.1 1

Press Shift + Ctrl + Enter para obter a primeira verificação e, em seguida, arraste a alça de preenchimento automático para a célula F8.
doc compare colunas 1.1 1

Agora, se a célula relacionada contém um ou mais valores na coluna D, o resultado exibe o número maior que 0, se não contém nenhum valor na coluna D, ele retorna 0.

Se você quiser contar o número total de células que contêm os valores da coluna D, use a fórmula abaixo da célula F8:
doc compare colunas 1.1 1

Observação

1. Você também pode usar a fórmula para contar se a célula contiver valores em outra coluna

=SUMPRODUCT(COUNTIF(B2,"*" &$D$2:$D$3& "*"))

Esta fórmula só precisa pressionar entrar e arraste a alça de preenchimento automático.

2. Nas fórmulas, * é o curinga que indica qualquer caractere ou string.


9. Exclua diferenças ou correspondências

Se você deseja excluir as diferenças ou correspondências depois de comparar duas colunas, conforme a captura de tela abaixo:
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

Você pode usar a fórmula para encontrar a diferença ou correspondências em primeiro lugar:

=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),"Difference","Match")

doc compare colunas 1.1 1

Press entrar e arraste a alça de preenchimento automático até a célula D8.
doc compare colunas 1.1 1

Em seguida, aplique o filtros recurso no sobressair para filtrar as diferenças ou valores de correspondências.

Selecione a coluna de fórmula e clique em Dados > filtros.
doc compare colunas 1.1 1

Em seguida, o botão de filtro aparece na coluna C, clique no botão de filtro para expandir o menu suspenso, escolha o Diferença or Combine como você precisa. Então clique OK terminar.
doc compare colunas 1.1 1

Agora, apenas as diferenças ou correspondências foram filtradas. Você pode selecioná-los e pressionar Excluir chave para removê-los.
doc compare colunas 1.1 1doc compare colunas 1.1 1

Agora clique Dados > filtros novamente para limpar o filtro.
doc compare colunas 1.1 1

Remova a coluna de fórmula se não precisar mais dela.
doc compare colunas 1.1 1


10. Compare duas colunas e calcule a variação percentual entre

Existem duas colunas, uma contém os preços originais e a outra contém os preços de venda. Agora, esta parte apresenta uma fórmula para comparar essas duas colunas e, em seguida, calcular a variação percentual entre as duas colunas.
doc compare colunas 1.1 1

amostra de docClique para baixar o arquivo de amostra

Você pode usar a fórmula abaixo para descobrir a variação percentual entre os dois preços na mesma linha.

=(C2-B2)/B2

 doc compare colunas 1.1 1

Press entrar para obter um número e, em seguida, arraste a alça de preenchimento automático para a célula D7.
doc compare colunas 1.1 1

Em seguida, formate o resultado da fórmula como porcentagem. Selecione os resultados, clique Página Inicial guia e vá para Número grupo para clicar Estilo percentual.
doc compare colunas 1.1 1

Os resultados da fórmula foram formatados como porcentagens.
doc compare colunas 1.1 1


11. Compare dois intervalos

Agora você sabe como comparar duas colunas depois de ler os métodos acima. No entanto, em alguns casos, você pode querer comparar dois intervalos (duas séries com várias colunas). Você pode usar os métodos acima (as fórmulas ou formatação condicional) para compará-los coluna por coluna, mas aqui apresenta uma ferramenta útil - Kutools for Excel pode resolver este trabalho em diferentes casos rapidamente com o formula free.

amostra de docClique para baixar o arquivo de amostra

11.1 Compare dois intervalos por célula


Aqui estão dois intervalos que precisam ser comparados por células, você pode usar o Selecionar células iguais e diferentes utilitário do Kutools para Excel para lidar com isso.
doc compare colunas 1.1 1

Depois de instalação grátis Kutools para Excel, faça o seguinte:

1. Clique Kutools > Selecionar > Selecionar células iguais e diferentes.
doc compare colunas 1.1 1

2. No popping Selecionar células iguais e diferentes caixa de diálogo, faça como abaixo:

1) No Encontre valores em seção, escolha o intervalo que você deseja descobrir as correspondências ou diferenças depois de comparar dois intervalos.

2) No De acordo com o seção, escolha o outro intervalo usado para o intervalo comparado.

3) em Baseado em seção, escolha Célula única.

4) Em seguida, no Procure seção, escolha o tipo de células que deseja selecionar ou realçar.

5) No Processamento de resultados seção, você pode realçar as células pela cor de fundo de preenchimento ou pela cor da fonte, se você não precisa realçar, não marque as caixas de seleção.
doc compare colunas 1.1 1

3. Clique Ok. Uma caixa de diálogo é exibida e lembra quantas células / linhas foram selecionadas, clique OK para fechá-lo. Agora, as células que são diferentes daquelas no outro intervalo foram selecionadas e destacadas.
doc compare colunas 1.1 1

Destacando os mesmos valores
doc compare colunas 1.1 1

Observação

Se você quiser comparar dois intervalos por linha, também pode aplicar o Selecionar células iguais e diferentes recurso, mas, neste caso, escolha o Cada fila opção.
doc compare colunas 1.1 1doc compare colunas 1.1 1

11.2 Compare dois intervalos se os dados estiverem na mesma ordem


Se você quiser comparar dois intervalos por linha, o Recurso de comparação de células do Kutools para Excel pode ajudá-lo.

Depois de instalação grátis Kutools para Excel, faça o seguinte:

Supondo que o intervalo F2: H7 seja um modelo, agora você deseja descobrir se os dados no intervalo B2: D7 estão na ordem certa de acordo com o intervalo F2: H7.
doc compare colunas 1.1 1

1. Clique Kutools > Comparar células.
doc compare colunas 1.1 1

2. No Comparar células caixa de diálogo, defina como abaixo:

1) Escolha os dois intervalos no Encontre valores em e De acordo com o caixas separadamente.

2) Escolha o tipo de célula que deseja destacar no Procure seção.

3) Escolha o tipo de destaque no Processamento de resultados seção.
doc compare colunas 1.1 1

3. Clique Ok. Uma caixa de diálogo aparece e lembra quantas células foram selecionadas, clique OK para fechá-lo. Agora, as células que são diferentes daquelas no outro intervalo foram selecionadas e destacadas.
doc compare colunas 1.1 1


Você também pode estar interessado em

Comparar datas se forem maiores que outra data no Excel
Fornece fórmulas e uma ferramenta útil para comparar duas datas e descobrir se as datas são superiores a outra.

Compare duas folhas lado a lado
Neste artigo, ele pode ajudá-lo a comparar rapidamente duas planilhas para encontrar as correspondências e diferenças.

Vlookup para comparar duas listas em planilhas separadas
Aqui, apresentará a função PROCV para comparar duas listas em duas planilhas diferentes.

Encontrar, destacar, filtrar, contar, excluir duplicatas no Excel
É um tutorial longo e detalhado que fornece muitos métodos diferentes para localizar, destacar, filtrar, contar e excluir duplicatas.



  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Agrupamento de tabela dinâmica por número da semana, dia da semana e mais ... Mostrar células desbloqueadas, bloqueadas por cores diferentes; Destacar células que possuem fórmula / nome...
guia kte 201905
  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.