Como extrair valores exclusivos de várias colunas no Excel?
Supondo que você tenha várias colunas com vários valores, alguns valores são repetidos na mesma coluna ou em colunas diferentes. E agora você deseja encontrar os valores que estão presentes em cada coluna apenas uma vez. Existe algum truque rápido para você extrair valores exclusivos de várias colunas no Excel?
Extraia valores exclusivos de várias colunas com fórmulas
Esta seção cobrirá 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 específica para Excel 365.
Extraia valores exclusivos 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, as fórmulas de matriz podem ser uma ferramenta poderosa para extrair valores exclusivos em várias colunas. Veja como você pode fazer isso:
1. Assumindo seus valores no intervalo A2: C9, insira a seguinte fórmula na célula E2:
=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"),)&""
2. Então aperte Shift + Ctrl + Enter juntas e arraste a alça de preenchimento para extrair os valores exclusivos até que as células em branco apareçam. Veja a imagem:
- $ A $ 2: $ C $ 9: especifica o intervalo de dados a ser verificado, que são as células de A2 a C9.
- 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<>"" verifica se as células do intervalo não estão vazias.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 determina se os valores destas 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 está listado na coluna E), a função SE calcula um número exclusivo com base em sua linha e coluna (ROW($2:$9)*100+COLUMN($A: $C)).
- Se as condições não forem atendidas, a função retornará um número grande (7^8), que serve como espaço reservado.
- MÍN(...): Encontra o menor número retornado pela função IF acima, correspondendo à localização do próximo valor exclusivo.
- TEXTO(...,"R0C00"): converte esse número mínimo em um endereço estilo R1C1. O código de formato R0C00 indica a conversão do número no formato de referência de célula do Excel.
- INDIRETO(...): usa a função INDIRETO para converter o endereço de estilo R1C1 gerado na etapa anterior em uma referência de célula de estilo A1 normal. A função INDIRETO permite a referência de células com base no conteúdo de uma sequência de texto.
- &"": acrescentar &"" no final da fórmula garante que a saída final seja tratada como texto, portanto, números pares serão exibidos como texto.
Extraia valores exclusivos de várias colunas com fórmula para Excel 365
O Excel 365 oferece suporte a matrizes dinâmicas, tornando muito mais fácil extrair valores exclusivos de várias colunas:
Insira ou copie a seguinte fórmula em uma célula em branco onde deseja colocar o resultado e clique em Entrar chave para obter todos os valores exclusivos de uma vez. Veja a captura de tela:
=UNIQUE(TOCOL(A2:C9,1))
Extraia valores exclusivos de várias colunas com Kutools AI Aide
Libere o poder do Assistente de IA do Kutools para extrair perfeitamente valores exclusivos de várias colunas no Excel. Com apenas alguns cliques, esta ferramenta inteligente analisa seus dados, identificando e listando entradas exclusivas em qualquer intervalo selecionado. Esqueça o incômodo de fórmulas complexas ou código VBA; aproveite a eficiência de Assistente de IA do Kutools e transforme seu fluxo de trabalho do Excel em uma experiência mais produtiva e livre de erros.
Depois de instalar o Kutools para Excel, clique em Kutools IA > Assessor de IA para abrir o Assistente de IA do Kutools painel:
- Digite seu requisito na caixa de bate-papo e clique em ENVIAR ou pressione Entrar chave para enviar a pergunta;
"Extraia valores exclusivos do intervalo A2:C9, ignorando as células em branco, e coloque os resultados começando em E2:" - Depois de analisar, clique Execute botão para executar. Kutools AI Aide processará sua solicitação usando IA e retornará os resultados na célula especificada diretamente no Excel.
Extraia valores únicos de várias colunas com a Tabela Dinâmica
Se você estiver familiarizado com a tabela dinâmica, poderá extrair facilmente os valores exclusivos de várias colunas com as seguintes etapas:
1. Primeiramente, insira uma nova coluna em branco à esquerda de seus dados, neste exemplo, irei inserir a coluna A ao lado dos dados originais.
2. Clique em uma célula em seus dados e pressione Alt + D teclas, então pressione P chave imediatamente para abrir o Assistente de Tabela Dinâmica e Gráfico Dinâmico, escolha Vários intervalos de consolidação na etapa 1 do assistente, consulte a captura de tela:
3. Então clique Próximo botão, cheque Crie um campo de página única para mim opção na etapa 2 do assistente, consulte a captura de tela:
4. Continue clicando Próximo botão, clique para selecionar o intervalo de dados que inclui a nova coluna esquerda de células e clique em Adicionar botão para adicionar o intervalo de dados ao Todos os intervalos caixa de lista, veja a captura de tela:
5. Depois de selecionar o intervalo de dados, continue clicando Próximo, na etapa 3 do assistente, escolha onde deseja colocar o relatório de tabela dinâmica como desejar.
6. Finalmente, clique em Acabamento para completar o assistente, e uma tabela dinâmica foi criada na planilha atual, então desmarque todos os campos do Escolha os campos para adicionar ao relatório seção, veja a captura de tela:
7. Em seguida, verifique o campo Valor ou arraste o valor para o Linhas rótulo, agora você obterá os valores exclusivos de várias colunas da seguinte maneira:
Extraia valores únicos de várias colunas com código VBA
Com o seguinte código VBA, você também pode extrair os valores exclusivos de várias colunas.
1. Segure o ALT + F11 chaves, e abre o Janela Microsoft Visual Basic for Applications.
2. Clique inserção > Móduloe cole o código a seguir na janela do módulo.
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. Então aperte 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 imagem:
4. E, em seguida, clique em OK, outra caixa de prompt aparecerá para permitir que você escolha um local para colocar o resultado, veja a captura de tela:
5. Clique OK para fechar esta caixa de diálogo, e todos os valores exclusivos foram extraídos de uma vez.
Mais artigos relativos:
- Conte o número de valores únicos e distintos de uma lista
- Supondo que você tenha uma longa lista de valores com alguns itens duplicados, agora você deseja 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, isso significa único valores + primeiros valores duplicados) em uma coluna, como mostrado à esquerda na captura de tela. Neste artigo, irei falar sobre como lidar com esse trabalho no Excel.
- Extraia valores exclusivos com base em critérios no Excel
- Supondo que você tenha o seguinte intervalo de dados que deseja listar apenas os nomes exclusivos da coluna B com base em um critério específico da coluna A para obter o resultado conforme a captura de tela abaixo mostrada. Como você poderia lidar com essa tarefa no Excel de forma rápida e fácil?
- Permitir apenas valores únicos no Excel
- Se você deseja manter apenas valores únicos inseridos em uma coluna da planilha e evitar duplicatas, este artigo apresentará alguns truques rápidos para você lidar com essa tarefa.
- Soma de valores exclusivos com base em critérios no Excel
- Por exemplo, eu 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 a seguir. Como resolver essa tarefa com rapidez e facilidade no Excel?
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
