Como extrair valores únicos de várias colunas no Excel?
Se você trabalha frequentemente com conjuntos de dados distribuídos por várias colunas no Excel, pode enfrentar situações em que certos valores são duplicados dentro da mesma coluna ou entre colunas diferentes. Em muitas tarefas de relatórios ou análise de dados, torna-se necessário identificar e extrair todos os valores únicos — aqueles que aparecem apenas uma vez em toda a seleção, independentemente de onde estejam localizados. Fazer isso manualmente pode ser demorado e sujeito a erros, especialmente ao lidar com grandes conjuntos de dados ou tabelas complexas. Felizmente, o Excel oferece uma variedade de métodos para extrair esses valores únicos de forma eficiente.
Este guia apresenta várias soluções que você pode usar com base na sua versão do Excel e suas preferências — como fórmulas adequadas para todas as versões, fórmulas de matriz dinâmica para versões recentes, o uso do Assistente AI do Kutools para resultados diretos, 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á momentos em que você pode desejar realizar essa extração usando funções internas do Excel. Esta seção detalha como fazer isso usando duas abordagens: uma fórmula de matriz adequada para todas as versões do Excel e uma fórmula de matriz dinâmica disponível em versões mais recentes, como Excel 365 e Excel 2021. Esses métodos são ideais quando você quer uma solução baseada em fórmulas direta, requer atualizações frequentes conforme seus dados mudam, ou precisa evitar complementos externos ou códigos.
Extrair valores únicos de várias colunas com fórmula de matriz para todas as versões do Excel
Para compatibilidade em todas as versões do Excel, usar uma fórmula de matriz permite extrair valores únicos de várias colunas — mesmo que seu Excel não suporte matrizes dinâmicas. Essa abordagem utiliza uma combinação das funções INDIRETO, TEXTO, MÍNIMO, SE, CONT.SE, LINHA e COLUNA, tornando-a flexível para várias estruturas de dados.
Suponha que seus dados estão localizados no intervalo A2:C9. Para extrair os valores únicos começando na célula E2, use o seguinte procedimento:
1. Clique na célula E2 (ou a primeira célula do seu intervalo de saída) e insira a seguinte fórmula de matriz:
=INDIRETO(TEXTO(MÍNIMO(SE(($A$2:$C$9<>"")*(CONT.SE($E$1:E1,$A$2:$C$9)=0),LINHA($2:$9)*100+COLUNA($A:$C),7^8)),"R0C00"),)&""
- A2:C9 é o intervalo de dados do qual você deseja extrair valores únicos.
- E1:E1 refere-se às células imediatamente acima da sua primeira célula de saída e é necessário para rastrear quais entradas já foram exibidas.
- $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 ajustar ao layout da sua própria planilha.
2. Depois de inserir a fórmula, em vez de apenas pressionar Enter, pressione Ctrl + Shift + Enter juntos para confirmá-la como uma fórmula de matriz. Quando feito corretamente, chaves {} aparecerão ao redor da sua fórmula na barra de fórmulas. Em seguida, arraste a alça de preenchimento de E2 para baixo na coluna. Continue arrastando até que células em branco apareçam, indicando que não há mais valores únicos para extrair. Esse processo garante que todos os valores únicos serão exibidos na coluna de destino.
- $A$2:$C$9: Especifica todo o conjunto de células a serem examinadas para valores únicos.
- SE(($A$2:$C$9<>"")*(CONT.SE($E$1:E1,$A$2:$C$9)=0), LINHA($2:$9)*100+COLUNA($A:$C),7^8):
- $A$2:$C$9<>"" garante que células em branco sejam ignoradas.
- CONT.SE($E$1:E1,$A$2:$C$9)=0 verifica se apenas novos valores (ainda não extraídos) são incluídos.
- Se ambas as condições forem verdadeiras, a saída correspondente será um cálculo baseado na linha e coluna da célula para gerar um número de índice único.
- Se qualquer condição for falsa, a fórmula retorna um número muito grande (7^8) para evitar seleção acidental.
- MÍNIMO(...): Identifica o menor número de índice, localizando efetivamente a posição do próximo valor único disponível nos dados.
- TEXTO(...,"R0C00"): Altera o índice em uma referência de célula válida usando o estilo R1C1.
- INDIRETO(...): Converte a referência de célula criada acima em um valor do intervalo de dados.
- &"": Força o resultado da fórmula a ser tratado como texto, garantindo que não haja surpresas de formatação.
Extrair valores únicos de várias colunas com fórmula para Excel 365, Excel 2021 e versões mais recentes
Se você usa Excel 365, Excel 2021 ou uma versão mais recente, tem acesso a funções de matriz dinâmica, que fornecem uma maneira mais simples e intuitiva de extrair valores únicos de várias colunas. As funções ÚNICO e TOCOL facilitam e agilizam a combinação de dados entre colunas e a eliminação de duplicatas em uma única etapa — especialmente útil para quem trabalha com conjuntos de dados constantemente atualizados ou maiores.
Para usar esse método, basta selecionar uma célula em branco (por exemplo, E2, ou onde você deseja que os resultados apareçam), inserir esta fórmula e pressionar Enter:
=UNIQUE(TOCOL(A2:C9,1))Depois de pressionar Enter, todos os valores únicos do intervalo A2:C9 serão inseridos automaticamente nas células abaixo da fórmula. Esse recurso é particularmente eficiente — a saída é atualizada dinamicamente à medida que seus dados de origem mudam, economizando etapas manuais de atualização.
- TOCOL(A2:C9,1): Converte seu intervalo de valores de várias colunas em uma única coluna, removendo automaticamente células em branco.
- ÚNICO(...): Extrai cada valor apenas uma vez, fornecendo uma lista limpa e sem duplicatas.
Extrair valores únicos de várias colunas com o Assistente AI do Kutools
Se você prefere uma abordagem mais simplificada e deseja reduzir o esforço manual, o Assistente AI do Kutools no Kutools para Excel pode ajudá-lo a extrair valores únicos de várias colunas facilmente. Esse método é especialmente valioso se você não está familiarizado com fórmulas ou quer evitar o risco de erros de fórmula. O Assistente AI interpreta suas instruções e processa os dados automaticamente, ideal para iniciantes e usuários que buscam uma solução rápida em poucos cliques.
Após a instalação, clique em Kutools AI > Assistente AI para abrir o painel "Assistente AI do Kutools":
- Insira sua solicitação na caixa de chat, como: "Extraia valores únicos do intervalo A2:C9, ignorando células em branco, e coloque os resultados começando em E2:"
- Clique em "Enviar" ou pressione Enter, e após a IA analisar a solicitação, basta clicar em "Executar" para iniciar. Os resultados aparecerão instantaneamente na sua planilha, na localização exata que você indicou.
Dica: Essa solução é muito útil se o fluxo de trabalho de extração de dados variar ou se você desejar recursos de processamento de linguagem natural. Lembre-se de verificar novamente a lista extraída quanto a células em branco se seus dados originais não forem perfeitamente consistentes, pois entradas em branco podem ser incluídas ou filtradas com base nos detalhes da sua solicitação de IA.
Extrair valores únicos de várias colunas com Tabela Dinâmica
As Tabelas Dinâmicas são outro método conveniente para extrair valores únicos, especialmente se você prefere trabalhar com ferramentas visuais e deseja resumir ou analisar ainda mais os itens únicos, como contar ocorrências. Essa abordagem é simples e não requer fórmulas. No entanto, exige algumas etapas de configuração e pequenos rearranjos de dados, especialmente se as colunas envolvidas tiverem cabeçalhos diferentes.
Aqui está um processo sugerido para extrair valores únicos usando uma Tabela Dinâmica:
1. Insira uma nova coluna em branco imediatamente à esquerda dos seus dados. Por exemplo, insira uma nova coluna A se seus dados começarem na coluna B. Esse ajuste ajuda a garantir a consolidação correta do intervalo.
2. Selecione qualquer célula dentro do seu conjunto de dados, pressione Alt + D, depois pressione rapidamente P para iniciar o "Assistente de Tabela Dinâmica e Gráfico Dinâmico". Na primeira etapa do assistente, selecione "Intervalos de consolidação múltipla." Isso permite combinar valores de várias colunas em um único campo resumido.
3. Clique em Próximo, depois escolha "Criar um único campo de página para mim." Essa etapa organiza todos os seus dados como um único grupo para facilitar a extração de valores únicos.
4. Na próxima etapa, selecione todo o intervalo de dados (inclua a nova coluna em branco), clique no botão Adicionar para trazer sua seleção para a lista "Todos os intervalos", e clique em Próximo.
5. Na etapa final do assistente, selecione onde deseja colocar a Tabela Dinâmica (nova planilha ou folha existente), depois clique em Concluir para gerar o relatório da Tabela Dinâmica.
6. Na nova Tabela Dinâmica, desmarque todos os campos na seção "Escolher campos para adicionar ao relatório" para limpar a exibição padrão.
7. Finalmente, arraste o campo "Valor" para a área Linhas. A Tabela Dinâmica exibirá todos os valores únicos do seu intervalo original de várias colunas, organizados de forma clara em uma única coluna.
Limitações: Os dados precisam de um arranjo preliminar, e se o conjunto de dados de origem for atualizado, você deve atualizar a Tabela Dinâmica para ver novos valores únicos.
Extrair valores únicos de várias colunas com código VBA
Em casos em que você precisa automatizar a extração ou lidar com grandes e irregulares conjuntos de dados, usar código VBA (Visual Basic for Applications) pode fornecer uma solução rápida e reutilizável. Isso é ideal para usuários com conhecimento básico no editor VBA do Excel ou para tarefas recorrentes onde você quer minimizar operações manuais. O VBA também pode lidar com grandes volumes de dados de forma mais eficiente do que fórmulas de matriz.
1. Abra o editor VBA pressionando Alt + F11. Na janela "Microsoft Visual Basic for Applications" que aparece, clique em Inserir > Módulo para adicionar um novo módulo.
2. No novo módulo, cole o código abaixo:
VBA: Extrair 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. Pressione F5 para executar o código. Um diálogo solicitará que você selecione o intervalo de dados. Selecione todas as colunas relevantes (incluindo aquelas com células em branco).
4. Após clicar em OK, outro prompt perguntará onde exibir os valores únicos. Especifique uma célula superior onde você deseja listar os resultados (por exemplo, E2).
5. Clique em OK, e a macro será executada automaticamente. Todos os valores únicos aparecerão, começando na localização especificada.
- Se você receber erros como #VALOR! ou #TRANBORDAR! ao usar fórmulas, verifique seus intervalos e certifique-se de que a área de saída está limpa.
- Sempre verifique linhas ocultas ou células mescladas no intervalo de dados, pois elas podem afetar a correção da extração de valores únicos.
- Fórmulas de matriz e matriz dinâmica são atualizadas automaticamente com mudanças, mas soluções com Filtro Avançado e Tabela Dinâmica podem exigir atualização manual ou reexecução.
- Para tarefas recorrentes, considere automatizar a extração usando VBA para consistência e rapidez.
- Faça backup dos seus dados antes de aplicar rotinas de extração ou automação em massa, especialmente em pastas de trabalho complexas.
Mais artigos relacionados:
- Contar o Número de Valores Únicos e Distintos de uma Lista
- Suponha que você tenha uma longa lista de valores com alguns itens duplicados, e deseja contar quantos valores únicos (valores que aparecem apenas uma vez) ou totais distintos existem em uma coluna, conforme mostrado na captura de tela à esquerda. Este artigo explica métodos eficientes para contar entradas únicas e distintas no Excel.
- Extrair Valores Únicos Baseados em Critérios no Excel
- Suponha que você deseja extrair apenas os nomes únicos da coluna B com base em uma condição específica na coluna A, produzindo resultados conforme mostrado na captura de tela. Este tutorial demonstra maneiras de aplicar critérios ao extrair valores únicos.
- Permitir Apenas Valores Únicos no Excel
- Se você deseja permitir apenas entradas únicas em uma coluna da planilha e evitar valores duplicados, este artigo apresenta técnicas práticas para impor regras de unicidade no Excel.
- Somar Valores Únicos Baseados em Critérios no Excel
- Por exemplo, talvez você precise somar apenas os valores únicos na coluna "Ordem" com base em nomes em uma coluna adjacente, conforme mostrado na captura de tela. Este artigo discute abordagens para combinar cálculos únicos e condicionais.
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