Como encontrar valores comuns em 3 colunas no Excel?
Trabalhar com dados no Excel frequentemente envolve comparar listas para identificar entradas compartilhadas ou duplicadas. Embora comparar duas colunas para localizar valores comuns seja uma tarefa frequente, há situações em que você precisa determinar quais valores aparecem em três colunas separadas ao mesmo tempo. Por exemplo, ao consolidar dados de pesquisa, mesclar registros de vendas ou analisar entradas duplicadas em várias listas, é importante extrair com precisão o conjunto de itens presentes em todas as três colunas, conforme demonstrado na captura de tela abaixo. Este artigo apresenta vários métodos práticos para resolver esse problema no Excel, permitindo que você identifique de forma eficiente e confiável os valores comuns entre três colunas — seja usando fórmulas ou VBA.
Encontrar valores comuns em 3 colunas com fórmulas de matriz
Macro VBA para extrair valores presentes em todas as três colunas
Encontrar valores comuns em 3 colunas com fórmulas de matriz
Para encontrar e extrair valores comuns entre três colunas, você pode usar fórmulas de matriz projetadas para procurar itens que aparecem em todos os intervalos selecionados. Isso é particularmente útil em conjuntos de dados onde você não deseja depender de suplementos adicionais do Excel ou ferramentas externas.
Insira esta fórmula de matriz em uma célula em branco onde deseja exibir 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 usar esta fórmula de matriz:
- Depois de inserir a fórmula na célula selecionada, pressione Shift + Ctrl + Enter (não apenas Enter). O Excel colocará a fórmula entre chaves para indicar que é uma fórmula de matriz.
- Arraste a fórmula pela coluna até que células em branco apareçam. Isso listará todos os valores compartilhados pelas três colunas, e células em branco indicarão que não existem mais correspondências.
Notas e explicações de parâmetros:
- Se você preferir uma fórmula de matriz diferente, esta também retorna todos os valores únicos presentes em todas as três colunas:
Novamente, lembre-se de pressionar 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, C2:C9 são os intervalos de dados em cada uma das três colunas que você deseja comparar.
- E1 refere-se à célula diretamente acima de onde sua fórmula começa (para lógica de exclusão). Ajuste as referências de células para corresponder aos seus intervalos de dados reais e ao local onde deseja que os resultados apareçam.
- Esses métodos funcionam bem para conjuntos de dados moderados, mas podem ficar lentos em volumes muito grandes devido às demandas de cálculo das fórmulas de matriz.
- Tenha cuidado para evitar ajustar os intervalos de origem no meio do processo, pois isso pode levar a resultados imprecisos ou erros nas fórmulas.
- Se o resultado incluir linhas em branco, significa que todos os valores comuns foram extraídos e as células restantes não têm mais interseções.
Desbloqueie a Magia do Excel com o Kutools AI
- Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
- Codificação VBA: Escreva e implemente código VBA sem esforço.
- Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
- Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.
Macro VBA para extrair valores presentes em todas as três colunas
Se você preferir uma abordagem automatizada que não exija inserir ou copiar fórmulas complexas, pode usar o VBA do Excel para percorrer seus dados e exibir apenas os valores presentes em cada uma das três colunas. Esse método é particularmente útil para conjuntos de dados muito grandes ou ao trabalhar com intervalos dinâmicos, já que o VBA é mais eficiente no manuseio de tarefas repetitivas e critérios personalizados.
1. Clique em Desenvolvedor > Visual Basic para abrir o editor VBA (se a guia Desenvolvedor não estiver visível, você pode ativá-la via Arquivo > Opções > Personalizar Faixa de Opções).
2. No editor 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 VBA, com o módulo selecionado, pressione F5 ou clique no botão Executar (▶) para executar o código. Você será solicitado, em sequência, a selecionar cada um dos três intervalos de colunas que deseja comparar. Use o mouse para destacar as células apropriadas durante cada solicitação.
4. A macro processará suas seleções e exibirá todos os valores presentes em todas as três colunas na próxima coluna vazia à direita do seu conjunto de dados atual, começando na primeira linha.
Esse método é eficiente ao trabalhar com grandes ou dinâmicos conjuntos de dados e pode ser facilmente estendido para quatro ou mais colunas duplicando a lógica do dicionário. Lembre-se sempre de salvar sua pasta de trabalho antes de executar macros, pois alterações não salvas não podem ser desfeitas se você desejar reverter.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins