Como extrair valores únicos de várias colunas no Excel?
Suponha que você tenha várias colunas com múltiplos valores, onde alguns valores se repetem dentro da mesma coluna ou em colunas diferentes. Agora, você precisa identificar os valores que são únicos em todas as colunas. Existem métodos rápidos para extrair esses valores únicos de várias colunas no Excel? Vamos explorar algumas soluções eficientes.
Extrair valores únicos de várias colunas com fórmulas
Esta seção abordará duas fórmulas: uma usando uma fórmula de matriz adequada para todas as versões do Excel e outra usando uma fórmula de matriz dinâmica especificamente para o Excel 365.
Extrair valores únicos de várias colunas com Fórmula de Matriz para todas as versões do Excel
Para usuários de qualquer versão do Excel, fórmulas de matriz podem ser uma ferramenta poderosa para extrair valores únicos de várias colunas. Aqui está como você pode fazer isso:
1. Supondo que seus valores estejam no intervalo A2:C9, insira a seguinte fórmula na célula E2:
=INDIRETO(TEXT(MÍNIMO(SE(($A$2:$C$9<>"")*(CONT.SE($E$1:E1,$A$2:$C$9)=0);LIN($2:$9)*100+COL($A:$C);7^8));"R0C00");)&""
2. Em seguida, pressione as teclas Shift + Ctrl + Enter juntas e arraste a alça de preenchimento para extrair os valores únicos até que células em branco apareçam. Veja a captura de tela:
- $A$2:$C$9: Isso especifica o intervalo de dados a ser verificado, que são as células de A2 a C9.
- SE(($A$2:$C$9<>"")*(CONT.SE($E$1:E1;$A$2:$C$9)=0); LIN($2:$9)*100+COL($A:$C); 7^8):
- $A$2:$C$9<>"" verifica se as células no intervalo não estão vazias.
- CONT.SE($E$1:E1;$A$2:$C$9)=0 determina se os valores dessas células ainda não foram listados no intervalo de células de E1 a E1.
- Se ambas as condições forem atendidas (ou seja, o valor não está vazio e ainda não foi listado na coluna E), a função SE calcula um número único com base na linha e coluna (LIN($2:$9)*100+COL($A:$C)).
- Se as condições não forem atendidas, a função retorna um número grande (7^8), que serve como espaço reservado.
- MÍNIMO(...): Encontra o menor número retornado pela função SE acima, correspondendo à localização do próximo valor único.
- TEXTO(...;"R0C00"): Converte esse número mínimo em um endereço no estilo R1C1. O código de formato R0C00 indica a conversão do número para o formato de referência de célula do Excel.
- INDIRETO(...): Usa a função INDIRETO para converter o endereço no estilo R1C1 gerado na etapa anterior de volta para uma referência de célula normal no estilo A1. A função INDIRETO permite referenciar células com base no conteúdo de uma string de texto.
- &"": Anexar &"" ao final da fórmula garante que a saída final seja tratada como texto, então mesmo números serão exibidos como texto.
Extrair valores únicos de várias colunas com fórmula para Excel 365, Excel 2021 e versões mais recentes
O Excel 365, Excel 2021 e versões mais recentes suportam matrizes dinâmicas, tornando muito mais fácil extrair valores únicos de várias colunas:
Insira ou copie a seguinte fórmula em uma célula em branco onde você deseja colocar o resultado e clique na tecla Enter para obter todos os valores únicos de uma vez. Veja a captura de tela:
=UNIQUE(TOCOL(A2:C9,1))
Extrair valores únicos de várias colunas com o Assistente AI do KUTOOLS
Libere o poder do "Assistente AI do KUTOOLS" para extrair perfeitamente valores únicos de várias colunas no Excel. Com apenas alguns cliques, essa ferramenta inteligente analisa seus dados, identificando e listando entradas únicas em qualquer intervalo selecionado. Esqueça o incômodo de fórmulas complexas ou códigos VBA; Abraçando a eficiência do "Assistente AI do KUTOOLS", transforme seu fluxo de trabalho no Excel em uma experiência mais produtiva e livre de erros.
Após instalar o Kutools for Excel, clique em "Kutools AI" > "Assistente AI" para abrir o painel "Assistente AI do KUTOOLS":
- Digite sua solicitação na caixa de bate-papo e clique no botão "Enviar" ou pressione a tecla Enter para enviar a pergunta;
"Extraia valores únicos do intervalo A2:C9, ignorando células em branco, e coloque os resultados começando em E2:" - Após a análise, clique no botão "Executar" para executar. O Assistente AI do KUTOOLS processará sua solicitação usando IA e retornará os resultados diretamente na célula especificada no Excel.
Extrair valores únicos de várias colunas com Tabela Dinâmica
Se você está familiarizado com a tabela dinâmica, pode facilmente extrair os valores únicos de várias colunas com os seguintes passos:
1. Primeiro, insira uma nova coluna em branco à esquerda dos seus dados. Neste exemplo, inserirei a coluna A ao lado dos dados originais.
2. Clique em uma célula em seus dados e pressione as teclas "Alt+D", depois pressione imediatamente a tecla "P" para abrir o assistente "Tabela Dinâmica e Gráfico Dinâmico", escolha "Intervalos de consolidação múltipla" na etapa 1 do assistente, veja a captura de tela:
3. Em seguida, clique no botão Próximo, marque a opção "Criar um único campo de página para mim" na etapa 2 do assistente, veja a captura de tela:
4. Continue clicando no botão "Próximo", clique para selecionar o intervalo de dados que inclui a nova coluna à esquerda das células, depois clique no botão "Adicionar" para adicionar o intervalo de dados à caixa de lista "Todos os intervalos", veja a captura de tela:
5. Após selecionar o intervalo de dados, continue clicando em "Próximo", na etapa 3 do assistente, escolha onde deseja colocar o relatório da Tabela Dinâmica como preferir.
6. Finalmente, clique em "Concluir" para finalizar o assistente, e uma Tabela Dinâmica será criada na planilha atual. Em seguida, desmarque todos os campos da seção Escolher campos para adicionar ao relatório, veja a captura de tela:
7. Depois, marque o campo Valor ou arraste o Valor para o rótulo "Linhas", agora você obterá os valores únicos das várias colunas conforme segue:
Extrair valores únicos de várias colunas com código VBA
Com o seguinte código VBA, você também pode extrair os valores únicos de várias colunas.
1. Mantenha pressionadas as teclas "ALT + F11" e abrirá a janela "Microsoft Visual Basic for Applications".
2. Clique em "Inserir" > "Módulo" e cole o seguinte código na Janela do Módulo.
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. Em seguida, pressione F5 para executar este código, e uma caixa de prompt aparecerá para lembrá-lo de selecionar o intervalo de dados que deseja usar. Veja a captura de tela:
4. E depois clique em OK, outra caixa de prompt aparecerá para permitir que você escolha um lugar para colocar o resultado, veja a captura de tela:
5. Clique em OK para fechar esta caixa de diálogo, e todos os valores únicos terão sido extraídos de uma só vez.
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, agora você quer contar o número de valores únicos (os valores que aparecem na lista apenas uma vez) ou valores distintos (todos os valores diferentes na lista, significa valores únicos +1ª duplicata) em uma coluna, conforme mostrado na captura de tela à esquerda. Neste artigo, falarei sobre como lidar com essa tarefa no Excel.
- Extrair Valores Únicos Com Base em Critérios No Excel
- Suponha que você tenha o seguinte intervalo de dados e queira listar apenas os nomes únicos da coluna B com base em um critério específico da coluna A para obter o resultado conforme mostrado na captura de tela abaixo. Como você poderia lidar com essa tarefa no Excel rapidamente e facilmente?
- Permitir Apenas Valores Únicos No Excel
- Se você quiser manter apenas valores únicos sendo inseridos em uma coluna da planilha e evitar duplicatas, este artigo apresentará alguns truques rápidos para você lidar com essa tarefa.
- Somar Valores Únicos Com Base em Critérios No Excel
- Por exemplo, tenho um intervalo de dados que contém as colunas Nome e Pedido. Agora, para somar apenas valores únicos na coluna Pedido com base na coluna Nome, conforme mostrado na captura de tela a seguir. Como resolver essa tarefa rapidamente e facilmente no Excel?
Melhores Ferramentas de Produtividade para Office
Potencialize suas habilidades no Excel com o Kutools para Excel e experimente uma eficiência sem igual. O Kutools para Excel oferece mais de300 recursos avançados para aumentar sua produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
O Office Tab traz interface com abas para o Office e facilita muito o seu trabalho
- Habilite a edição e leitura com abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas abas na mesma janela, em vez de abrir novas janelas.
- Aumente sua produtividade em50% e reduza centenas de cliques do mouse todos os dias!