Como encontrar valores comuns em três colunas no Excel?
Trabalhar com dados no Excel implica frequentemente comparar listas para identificar entradas partilhadas ou duplicadas. Embora a comparação de duas colunas à procura de valores comuns seja uma tarefa habitual, há situações em que é necessário determinar quais os valores que aparecem simultaneamente em três colunas distintas. Por exemplo, ao consolidar respostas de inquéritos, fundir registos de vendas ou analisar entradas duplicadas em múltiplas listas, é essencial extrair com precisão o conjunto de itens presentes nas três colunas, tal como ilustrado na imagem seguinte. Este artigo apresenta vários métodos práticos para resolver este desafio no Excel, permitindo-lhe identificar de forma eficiente e fiável os valores comuns entre três colunas — quer opte por fórmulas, quer prefira utilizar VBA.
Encontrar valores comuns em 3 colunas com fórmulas matriciais
Macro VBA para extrair valores presentes nas três colunas
Encontrar valores comuns em 3 colunas com fórmulas matriciais
Para encontrar e extrair valores comuns entre três colunas, pode utilizar fórmulas matriciais concebidas para identificar itens presentes nos três intervalos selecionados. Esta abordagem revela-se especialmente útil em conjuntos de dados onde pretende evitar a dependência de extras do Excel ou ferramentas externas.
Introduza esta fórmula matricial numa célula vazia onde deseja apresentar o primeiro valor comum:
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))Como utilizar esta fórmula matricial:
- Após introduzir a fórmula na célula selecionada, prima Shift + Ctrl + Enter (e não apenas Enter). O Excel colocará a fórmula entre chavetas para indicar que se trata de uma fórmula matricial.
- Arraste a fórmula para baixo na coluna até surgirem células vazias. Assim, obterá todos os valores partilhados pelas três colunas, sendo que as células vazias indicarão que já não há mais correspondências.
Notas e explicações dos parâmetros:
- Se preferir uma fórmula matricial alternativa, esta também devolve todos os valores únicos presentes nas três colunas:
Mais uma vez, lembre-se de premir Shift + Ctrl + Enter após digitar ou colar a fórmula.=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0)) - Nestas fórmulas:
- A2:A10, B2:B8 e C2:C9 são os intervalos nas três colunas que pretende comparar.
- E1 refere-se à célula imediatamente acima do local onde a sua fórmula começa (para lógica de exclusão). Ajuste as referências das células de modo a corresponderem ao seu intervalo real e à localização exata onde pretende que os resultados apareçam.
- Estes métodos funcionam bem com conjuntos de dados moderados, mas podem tornar-se lentos com volumes muito grandes devido às exigências computacionais das fórmulas matriciais.
- Tenha cuidado para não alterar o intervalo de origem a meio do processo, pois isso pode provocar resultados incorretos ou erros na fórmula.
- Se o resultado incluir linhas em branco, isso significa que todos os valores comuns já foram extraídos e as células restantes não têm mais interseções.
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.
Macro VBA para extrair valores presentes nas três colunas
Se preferir uma abordagem automatizada que não exija introduzir ou copiar fórmulas complexas, pode utilizar o VBA do Excel para percorrer os seus dados e apresentar apenas os valores presentes nas três colunas. Este método revela-se especialmente útil com conjuntos de dados muito grandes ou intervalos dinâmicos, já que o VBA lida de forma mais eficiente com tarefas repetitivas e critérios personalizados.
1. Clique em Programador > Visual Basicpara abrir o editor do VBA (se o separador)Programador não estiver visível, pode ativá-lo através de Ficheiro > Opções > Personalizar Friso).
2. No editor do VBA, clique em Inserir > Módulo para criar um novo módulo. Em seguida, cole o código abaixo na janela do módulo:
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub3. Na janela do VBA, com o módulo selecionado, prima F5 ou clique no botão Executar (▶) para executar o código. Ser-lhe-á solicitado, sequencialmente, que selecione cada um dos três intervalos de colunas que pretende comparar. Utilize o rato para destacar as células apropriadas em cada pedido.
4. A macro processará as suas seleções e apresentará todos os valores das três colunas na próxima coluna vazia à direita do seu conjunto de dados atual, começando na primeira linha.
Este método revela-se altamente eficiente ao lidar com conjuntos de dados grandes ou dinâmicos e pode ser facilmente expandido para quatro ou mais colunas, bastando duplicar a lógica do dicionário. Lembre-se sempre de guardar a sua pasta de trabalho antes de executar macros, pois as alterações não guardadas não poderão ser anuladas caso pretenda reverter.
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