Como extrair valores únicos de várias colunas no Excel?
Se trabalha frequentemente com conjuntos de dados distribuídos por várias colunas no Excel, é provável que se depare com valores duplicados — quer dentro da mesma coluna, quer entre colunas diferentes. Em muitas tarefas de relatórios ou análise de dados, torna-se essencial identificar e extrair todos os valores únicos, ou seja, aqueles que aparecem apenas uma vez em toda a seleção, independentemente da sua localização. Fazer isto manualmente pode ser moroso e propenso a erros, especialmente com grandes volumes de dados ou tabelas complexas. Felizmente, o Excel oferece uma variedade de métodos para extrair esses valores únicos de forma rápida e eficiente.
Este guia apresenta diversas soluções adaptadas à sua versão do Excel e às suas preferências — desde fórmulas compatíveis com todas as versões e fórmulas de matriz dinâmica para versões mais recentes, até ao uso do KUTOOLS AI Aide para resultados imediatos, Tabelas Dinâmicas para consolidação visual e código VBA para extração automatizada em cenários complexos.
Extrair valores únicos de várias colunas com fórmulas
Há ocasiões em que poderá querer realizar esta extração utilizando funções incorporadas do Excel. Esta secção explica como fazê-lo com duas abordagens: uma fórmula de matriz compatível com todas as versões do Excel e uma fórmula de matriz dinâmica, disponível nas versões mais recentes, como o Excel 365 e o Excel 2021. Estes métodos são ideais se procura uma solução direta baseada em fórmulas, necessita de atualizações automáticas sempre que os seus dados mudarem ou pretende evitar complementos externos ou código personalizado.
Extrair valores únicos de várias colunas com fórmula de matriz para todas as versões do Excel
Para garantir compatibilidade com todas as versões do Excel, utilize uma fórmula de matriz para extrair valores únicos de várias colunas — mesmo que o seu Excel não suporte matrizes dinâmicas. Esta abordagem combina as funções INDIRETO, TEXTO, MÍNIMO, SE, CONTAR.SE, LIN e COL, oferecendo flexibilidade para adaptar-se a diversas estruturas de dados.
Suponha que os seus dados estejam localizados no intervalo A2:C9. Para extrair os valores únicos a partir da célula E2, siga o procedimento seguinte:
1.Clique na célula E2(ou na primeira célula da sua Área de Colocação da Lista) e introduza a seguinte fórmula de matriz:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>«»)*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&«»
- A2:C9 é o intervalo de dados do qual pretende extrair valores únicos.
- E1:E1 refere-se às células imediatamente acima da sua primeira célula de saída e é essencial para acompanhar quais as entradas já produzidas.
- $2:$9 são as referências de linha dos seus dados; $A:$C são as referências de coluna. Ajuste-as conforme necessário para se adaptarem à estrutura da sua folha de cálculo.
2. Depois de introduzir a fórmula, em vez de premir apenas Enter, prima Ctrl + Shift + Enter em simultâneo para a confirmar como fórmula de matriz. Quando feito corretamente, surgirão chavetas {} à volta da sua fórmula na Barra de Fórmulas. Em seguida, arraste a alça de preenchimento desde E2 ao longo da coluna e continue até aparecerem células em branco — sinal de que já não há mais valores únicos para extrair. Este processo garante que todos os valores únicos sejam apresentados na coluna de destino.
- $A$2:$C$9Especifica o conjunto completo de células a analisar em busca de valores únicos.
- IF(($A$2:$C$9<>«»)*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>«»Garante que as células em branco sejam ignoradas.
- COUNTIF($E$1:E1,$A$2:$C$9)=0assegura que apenas os valores novos (ainda não extraídos) sejam incluídos.
- Se ambas as condições forem verdadeiras, o resultado é um cálculo baseado na linha e na coluna da célula, gerando um número de índice único.
- Se qualquer uma das condições for falsa, a fórmula devolve um número muito elevado ()7^8) para evitar seleções acidentais.
- MIN(...)Identifica o índice mais baixo, localizando efetivamente a posição do próximo valor único disponível nos dados.
- TEXT(...,"R0C00")Converte o índice numa referência de célula válida utilizando a notação R1C1.
- INDIRECT(...)Converte a referência de célula criada acima num valor extraído do seu Intervalo de Dados.
- &«»Força o resultado da fórmula a ser tratado como texto, evitando surpresas indesejadas com a formatação.
Extrair valores únicos de várias colunas com fórmula para Excel 365, Excel 2021 e versões mais recentes
Se utilizar o Excel 365, o Excel 2021 ou uma versão mais recente, tem acesso a funções de matriz dinâmica que oferecem uma forma mais simples e intuitiva de extrair valores únicos de várias colunas. As funções UNIQUE e TOCOL facilitam e aceleram a combinação de dados entre colunas e a eliminação de duplicados num único passo — especialmente útil para quem trabalha com conjuntos de dados em constante atualização ou de maior dimensão.
Para utilizar este método, basta selecionar uma célula vazia (por exemplo,)E2, ou onde desejar que os resultados apareçam), introduzir esta fórmula e premir Enter:
=UNIQUE(TOCOL(A2:C9,1))Após premir Enter, todos os valores únicos do intervalo A2:C9 serão automaticamente distribuídos nas células abaixo da fórmula. Esta funcionalidade é particularmente eficiente — a saída atualiza-se dinamicamente à medida que os seus dados de origem mudam, poupando-lhe passos manuais de atualização.
- TOCOL(A2:C9,1): Transforma o seu intervalo de várias colunas numa única coluna, eliminando automaticamente as células em branco.
- UNIQUE(...): Extrai cada valor apenas uma vez, garantindo uma lista limpa e sem duplicados.
Extraia valores únicos de várias colunas com o KUTOOLS AI Aide
Se pretender uma abordagem mais simples e quiser reduzir o esforço manual, o KUTOOLS AI Aide no Kutools para Excel permite-lhe extrair valores únicos de várias colunas com facilidade. Este método é especialmente útil se não estiver familiarizado com fórmulas ou quiser evitar o risco de erros nas mesmas. O KUTOOLS AI Aide interpreta as suas instruções e processa os dados automaticamente, sendo ideal tanto para principiantes como para utilizadores que procuram uma solução rápida em apenas alguns cliques.
Após a instalação, clique em KUTOOLS AI>Assistente de IApara abrir o painel «KUTOOLS AI Aide»:
- Introduza o seu pedido na caixa de conversação, por exemplo:«Extrair valores únicos do intervalo A2:C9, ignorando células em branco, e colocar os resultados a partir de E2:»
- Clique em «Enviar» ou prima Enter. Após a análise pela IA, basta clicar em «Executar» para executar — os resultados aparecerão instantaneamente na sua folha de cálculo, exatamente na localização indicada.
Dica: Esta solução é especialmente útil se o seu fluxo de trabalho de extração de dados variar ou se precisar de funcionalidades avançadas de processamento de linguagem natural. Lembre-se de rever novamente a lista extraída em relação a células vazias, caso os seus dados originais não sejam perfeitamente consistentes — afinal, entradas em branco poderão ser incluídas ou filtradas consoante os detalhes do seu pedido à IA.
Extraia valores únicos de várias colunas com o Tabela Dinâmica
As Tabelas Dinâmicas são outro método conveniente para extrair valores únicos, especialmente se preferir trabalhar com ferramentas visuais e quiser resumir ou analisar ainda mais esses itens — por exemplo, contando as suas ocorrências. Trata-se de uma abordagem simples, que não exige fórmulas, mas requer alguns passos de configuração e uma ligeira reorganização dos dados, sobretudo quando as colunas envolvidas têm títulos diferentes.
Eis um processo sugerido para extrair valores únicos utilizando um Tabela Dinâmica:
1.Insira uma nova coluna em branco imediatamente à esquerda dos seus dados. Por exemplo, insira uma nova coluna A se os seus dados começarem na coluna B. Este ajuste ajuda a garantir uma consolidação correta do intervalo.
2. Selecione qualquer célula dentro do seu conjunto de dados, prima Alt + D e, em seguida, prima rapidamente P para iniciar o «Assistente de Tabela e Gráfico Dinâmicos». No primeiro passo do assistente, selecione «Vários intervalos de consolidação». Isto permite-lhe combinar valores de diversas colunas num único campo resumido.
3. Clique em Seguinte e, de seguida, escolha «Criar um único campo de página para mim». Este passo organiza todos os seus dados num único grupo, facilitando a extração de valores únicos.
4. No passo seguinte, selecione todo o intervalo de dados (incluindo a nova coluna em branco), clique no botão Adicionar para adicionar a sua seleção à lista «Todos os intervalos» e clique em Seguinte.
5. No último passo do assistente, selecione onde pretende colocar a Tabela Dinâmica (numa nova planilha ou numa folha existente) e, em seguida, clique em Concluir para gerar o relatório da Tabela Dinâmica.
6. Na nova Tabela Dinâmica, desmarque todos os campos na secção «Escolher campos para adicionar ao relatório» para limpar a vista predefinida.
7. Por fim, arraste o campo «Valor» para a área Linhas. A Tabela Dinâmica apresentará todos os valores únicos do seu intervalo original de múltiplas colunas, organizados de forma clara numa única coluna.
Limitações:Os dados exigem uma preparação prévia e, se o seu conjunto de Dados de Origem for atualizado, terá de atualizar o Tabela Dinâmica para ver os novos valores únicos.
Extrair valores únicos de várias colunas com código VBA
Em situações que exijam automatizar a extração ou lidar com conjuntos de dados grandes e irregulares, o uso de código VBA (Visual Basic for Applications) oferece uma solução rápida e reutilizável. Esta opção é ideal para utilizadores com conhecimentos básicos do editor VBA do Excel ou para tarefas recorrentes em que pretenda reduzir ao mínimo as operações manuais. Além disso, o VBA processa grandes volumes de dados de forma mais eficiente do que as fórmulas matriciais.
1. Abra o editor do VBA premindo Alt + F11. Na janela «Microsoft Visual Basic for Applications» que surge, clique em Inserir > Módulo para adicionar um novo módulo.
2.No novo módulo, cole o código abaixo:
VBA: Extraia valores únicos de várias colunas
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Prima F5 para executar o código. Uma caixa de diálogo irá solicitar-lhe que selecione o Intervalo de Dados. Selecione todas as colunas relevantes (incluindo as que têm células vazias).
4.Após clicar em OK, surge outra caixa de diálogo a perguntar onde pretende colocar os valores únicos. Indique uma célula acima da qual deseja que os resultados sejam listados (por exemplo,)E2).
5. Clique em OK e a macro será executada automaticamente. Todos os valores únicos aparecerão, começando na localização que indicou.
- Se obtiver erros como #VALOR! ou #TRANSBORDAR! ao utilizar fórmulas, verifique os seus intervalos e certifique-se de que a área de saída está livre.
- Verifique sempre se existem linhas ocultas ou células mescladas no seu intervalo de dados, pois podem comprometer a precisão da extração dos seus valores únicos.
- As fórmulas de matriz e de matriz dinâmica atualizam-se automaticamente sempre que houver alterações, enquanto as soluções com Filtro Avançado e Tabela Dinâmica poderão necessitar de uma atualização manual ou de serem executadas novamente.
- Para tarefas recorrentes, automatize a extração com VBA e garanta consistência e rapidez.
- Faça sempre uma cópia de segurança dos seus dados antes de aplicar rotinas de extração em massa ou automação, especialmente em livros de trabalho complexos.
Mais artigos relacionados:
- Contar o Número de Valores Únicos e Distintos de uma Lista
- Imagine que tem uma longa lista de valores com alguns itens duplicados e pretende contar quantos valores únicos (ou seja, os que aparecem apenas uma vez) ou o número total de valores distintos existem numa coluna, tal como ilustrado na captura de ecrã à esquerda. Este artigo apresenta métodos eficientes para contar entradas únicas e distintas no Excel.
- Extrair Valores Únicos Com Base em Critérios no Excel
- Imagine que pretende extrair apenas os nomes únicos da coluna B com base numa condição específica na coluna A, obtendo resultados como os apresentados na captura de ecrã. Este tutorial mostra-lhe como aplicar critérios ao extrair valores únicos.
- Permitir Apenas Valores Únicos no Excel
- Se pretender permitir apenas entradas únicas numa coluna da folha de cálculo e prevenir valores duplicados, este artigo apresenta técnicas práticas para impor regras de unicidade no Excel.
- Somar Valores Únicos Com Base em Critérios no Excel
- Por exemplo, poderá precisar de somar apenas os valores únicos na coluna «Encomenda», com base nos nomes presentes numa coluna adjacente, tal como ilustrado na captura de ecrã. Este artigo explora abordagens para combinar cálculos únicos e condicionais.
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