Como comparar valores separados por vírgulas em duas células e obter os valores duplicados ou únicos no Excel?
Conforme ilustrado na captura de ecrã abaixo, existem duas colunas – Coluna1 e Coluna2 –, em que cada célula contém números separados por vírgulas. Como pode comparar os números separados por vírgulas na Coluna1 com o conteúdo da célula correspondente na mesma linha da Coluna2 e obter todos os valores duplicados ou únicos?
Este tutorial apresenta dois métodos para o ajudar a concluir esta tarefa com sucesso.
Comparar valores separados por vírgulas em duas células e devolver valores duplicados ou únicos com fórmulas
Esta secção apresenta duas fórmulas para comparar valores separados por vírgulas em duas células e devolver os elementos duplicados ou únicos entre elas.
Nota: As fórmulas seguintes funcionam apenas no Excel para 365. Se estiver a utilizar outra versão do Excel, experimente o método VBA indicado abaixo.
Tomando como exemplo as duas colunas acima, para comparar os números separados por vírgulas na Coluna1 com os da Coluna2 na mesma linha e obter os valores duplicados ou únicos, siga estes passos.
Devolver Valores Duplicados
1. Selecione uma célula para apresentar os números duplicados entre as duas células especificadas, cujos valores estão separados por vírgulas; neste caso, selecionei a célula D2, introduzi a fórmula abaixo e prima a tecla Enter. Em seguida, selecione a célula com a fórmula e arraste a Alça de Preenchimento Automático para baixo, de modo a obter os números duplicados entre as células das restantes linhas.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, «»)))
Devolver valores únicos
Para obter os números únicos entre as duas células especificadas — que contêm números separados por vírgulas na mesma linha — a fórmula seguinte pode ajudar.
1. Selecione uma célula para apresentar os números únicos; neste caso, selecionei a célula E2, introduzi a fórmula abaixo e prima a tecla Enter. Em seguida, selecione a célula com a fórmula e arraste a Alça de Preenchimento Automático para baixo para obter os números únicos nas células das restantes linhas.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Notas:
Desbloqueie a Magia do Excel com KUTOOLS AI
- Execução Inteligente: Realize operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- fórmulas personalizadas: Crie fórmulas personalizadas para simplificar os seus fluxos de trabalho.
- Programação VBA: Escreva e implemente código VBA com facilidade.
- Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
- Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.
Comparar duas colunas com valores separados por vírgulas e devolver valores duplicados ou únicos com VBA
A função definida pelo utilizador apresentada nesta secção permite comparar os valores separados por vírgulas em duas células especificadas e devolver os valores duplicados ou únicos entre elas. Proceda da seguinte forma.
Utilizando o mesmo exemplo acima, para comparar os números separados por vírgulas na Coluna1 com os números separados por vírgulas na mesma linha da Coluna2 e devolver valores duplicados ou únicos, experimente a função definida pelo utilizador nesta secção.
1. Com o livro aberto, prima as teclas Alt+F11para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Na janela do Microsoft Visual Basic para Aplicações, clique em Inserir>Módulo, e copie o seguinte código VBA para a janela Módulo (Código).
Código VBA: Comparar valores separados por vírgulas em duas células e devolver valores duplicados/únicos
Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
Dim R1Arr As Variant
Dim R2Arr As Variant
Dim Ans1 As String
Dim Ans2 As String
Dim Separator As String
Dim d1 As New Dictionary
Dim d2 As New Dictionary
Dim d3 As New Dictionary
Application.Volatile
Separator = ", "
R1Arr = Split(Rng1.Value, Separator)
R2Arr = Split(Rng2.Value, Separator)
Ans1 = ""
Ans2 = ""
For Each ch In R2Arr
If Not d2.Exists(ch) Then
d2.Add ch, "1"
End If
Next
If Op Then
For Each ch In R1Arr
If d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans1 = Ans1 & ch & Separator
End If
End If
Next
If Ans1 <> "" Then
Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
End If
COMPARE = Ans1
Else
For Each ch In R1Arr
If Not d1.Exists(ch) Then
d1.Add ch, "1"
End If
Next
For Each ch In R1Arr
If Not d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
For Each ch In R2Arr
If Not d1.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
If Ans2 <> "" Then
Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
End If
COMPARE = Ans2
End If
End Function3. Depois de colar o código na janela Módulo (Código), vá a Ferramentas > Referências para abrir a janela Referências – ProjetoVBA, marque a caixa Microsoft Scripting Runtime e clique no botão OK.
4. Prima as teclas Alt + Q para fechar a janela do Microsoft Visual Basic para Aplicações.
5. Agora precisa de aplicar duas funções separadamente para obter os valores duplicados e os valores únicos de duas células cujos conteúdos são listas separadas por vírgulas.
Devolver valor duplicado
Selecione uma célula para apresentar os números duplicados; neste exemplo, seleciono a célula D2, introduzo a fórmula abaixo e pressiono a tecla Enterpara obter os números duplicados entre as células A2 e B2.
Selecione a célula com a fórmula e arraste a Alça de Preenchimento Automático para baixo, obtendo assim os números duplicados nas células das linhas seguintes.
=COMPARE(A2,B2,TRUE)
Devolver valores únicos
Selecione uma célula para apresentar os números únicos; neste exemplo, seleciono a célula E2, introduzo a fórmula abaixo e pressiono a tecla Enterpara obter os números únicos nas células A2 e B2.
Selecione a célula com a fórmula e arraste a Alça de Preenchimento Automático para baixo, obtendo assim números únicos nas células das linhas seguintes.
=COMPARE(A2,B2,FALSE)
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos