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.
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.
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.
Clique 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:
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.
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:
Press entrar para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento automático para a célula D8.
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:
Press entrar para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento automático para a célula E8.
Observação
Nas fórmulas acima, você pode alterar os textos “Match” e “Mismatch” para sua própria descrição.
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 Início > Formatação condicional > Nova regra.
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.
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.
4. Clique OK > OK para fechar as caixas de diálogo, as células na mesma linha serão destacadas se forem iguais.
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.
Em seguida, as diferenças de duas colunas na mesma linha serão destacadas com uma cor especificada.
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.
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
4. pressione F5 para executar o código, então uma caixa de diálogo aparecerá para selecionar as duas colunas.
5. Clique OK. Em seguida, as diferenças de duas colunas foram destacadas com uma cor de fundo.
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:
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.
Clique para baixar o arquivo de amostra
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.
Press entrar e arraste a alça de preenchimento automático para a célula D8.
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)
A 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 Início > Formatação condicional > Reverter as regras das células > Valores Duplicados.
2. No popping Valores Duplicados caixa de diálogo, escolha um formato de realce que você precisa na lista suspensa de valores com.
3. Clique OK. Em seguida, as duplicatas em duas colunas foram destacadas.
Observação
Se você deseja destacar os valores únicos (as diferenças) em duas colunas, clique também Início > 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.
Os valores exclusivos serão destacados.
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 Início > Formatação condicional > Nova regra.
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.
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.
4. Clique OK > OK. Em seguida, os valores na coluna B que também existem na coluna C foram destacados com a cor especificada.
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.
Em seguida, escolha outra cor para formatar os valores.
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.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Clique Kutools > Selecionar > Selecionar células iguais e diferentes.
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.
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.
Se você verificar o Preencher a cor de fundo e Cor da fonte de preenchimento caixas de seleção, o resultado é mostrado como este:
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.
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
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.
5. Clique OK > OK, então as correspondências foram listadas automaticamente na coluna direita das duas colunas.
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.
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
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.
5. Clique OK. Na segunda caixa de diálogo, selecione a coluna em que deseja destacar os valores duplicados.
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.
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:
À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.
Clique 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.
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.
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:
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.
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.
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.
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 Início > Formatação condicional > Nova regra.
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.
Or
Importante: Se o número de colunas for maior que três, supondo que seja 5, as fórmulas devem ser alteradas para:
Or
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.
4. Clique OK > OK, agora apenas as linhas nas quais todas as células coincidem serão destacadas.
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 Início > Encontrar e selecionar > Ir para especial.
2. No popping Ir para especial diálogo, verificar Diferenças de linha opção.
3. Clique OK. Agora as diferenças de linha foram selecionadas.
4. Agora mantenha as células selecionadas, clique Início > Cor de preenchimento para selecionar uma cor no menu suspenso.
Observação
Este método compara células sem distinção entre maiúsculas e minúsculas.
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?
Clique 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:
Or
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.
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:
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.
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.
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.
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.
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.
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.
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.
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
3. Em seguida, pressione F5 para executar o código, uma caixa de diálogo é exibida para selecionar as duas colunas de comparação.
4. Clique OK. Agora, os dados ausentes foram listados abaixo das duas colunas.
Observação
O código compara células sem distinção entre maiúsculas e minúsculas.
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.
Clique 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.
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.
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 Início > Formatação condicional > Nova regra.
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.
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.
4. Clique OK > OK. Em seguida, as células na coluna Data1 que são maiores do que as da coluna Data2 foram destacadas.
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.
Clique 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:
Or
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.
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.
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.
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.
3. Clique Ok, o primeiro valor foi encontrado e arraste a alça de preenchimento automático para a célula F6.
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.
Escolha uma das fórmulas abaixo para resolver este trabalho:
Or
Press entrar e arraste a alça de preenchimento automático para a célula F5, todos os valores foram encontrados.
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.
Abaixo o conjunto de dados é um exemplo para comparar e contar correspondências ou diferenças.
Clique 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.
Press entrar chave para obter o resultado.
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.
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.
3. Clique Ok. Uma caixa de diálogo é exibida para informar quantas células correspondentes ou diferentes estão selecionadas.
Células correspondentes
Células diferentes
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:
Clique 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.
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.
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:
Observação
1. Você também pode usar a fórmula para contar se a célula contiver valores em outra coluna
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.
Se você deseja excluir as diferenças ou correspondências depois de comparar duas colunas, conforme a captura de tela abaixo:
Clique para baixar o arquivo de amostra
Você pode usar a fórmula para encontrar a diferença ou correspondências em primeiro lugar:
Press entrar e arraste a alça de preenchimento automático até a célula D8.
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.
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.
Agora, apenas as diferenças ou correspondências foram filtradas. Você pode selecioná-los e pressionar Excluir chave para removê-los.
Agora clique Dados > filtros novamente para limpar o filtro.
Remova a coluna de fórmula se não precisar mais dela.
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.
Clique 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.
Press entrar para obter um número e, em seguida, arraste a alça de preenchimento automático para a célula D7.
Em seguida, formate o resultado da fórmula como porcentagem. Selecione os resultados, clique Início guia e vá para Número grupo para clicar Estilo percentual.
Os resultados da fórmula foram formatados como porcentagens.
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.
Clique 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.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Clique Kutools > Selecionar > Selecionar células iguais e diferentes.
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.
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.
Destacando os mesmos valores
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.
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.
1. Clique Kutools > Comparar células.
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.
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.
Você também pode estar interessado em |
|
Comparar datas se forem maiores que outra data no Excel Compare duas folhas lado a lado Vlookup para comparar duas listas em planilhas separadas Encontrar, destacar, filtrar, contar, excluir duplicatas no Excel |
As melhores ferramentas de produtividade para escritório
O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%
- 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...
- 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!
You are guest
or post as a guest, but your post won't be published automatically.
Be the first to comment.