Note: The other languages of the website are Google-translated. Back to English

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 únicos de várias colunas com fórmula de matriz

Aqui está uma fórmula de matriz que também pode ajudá-lo a extrair os valores exclusivos de várias colunas.

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"),)&""

Nota: Na fórmula acima, A2: C9 indica o intervalo de células que você deseja extrair os valores únicos, E1: E1 é a primeira célula da coluna em que você deseja colocar o resultado, $ 2: $ 9 representa as linhas que contêm as células que você deseja usar, e $ A: $ C indica que as colunas contêm as células que você deseja usar. Altere-os para os seus.

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:


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 Seguinte 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 Seguinte 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 Seguinte, na etapa 3 do assistente, escolha onde deseja colocar o relatório de tabela dinâmica como desejar.

6. Finalmente, clique em Terminar 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.


Extraia valores únicos de uma única coluna com um recurso incrível

Às vezes, você precisa extrair os valores exclusivos de uma única coluna, os métodos acima não irão ajudá-lo, aqui, posso recomendar uma ferramenta útil-Kutools for Excel, Com o seu Extraia células com valores únicos (inclua a primeira duplicata) utilitário, você pode extrair rapidamente os valores exclusivos.

NOTAPara aplicar isso Extraia células com valores únicos (inclua a primeira duplicata), em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

1. Clique em uma célula para a qual deseja gerar o resultado. (Nota: Não clique em uma célula na primeira linha.)

2. Então clique Kutools > Fórmula Helper > Fórmula Helper, veja a captura de tela:

3. No Auxiliar de Fórmulas caixa de diálogo, execute as seguintes operações:

  • Selecionar Texto opção do Fórmula para cada ocasião lista suspensa;
  • Então escolha Extraia células com valores únicos (inclua a primeira duplicata) de Escolha uma fromula caixa de listagem;
  • Na direita Entrada de argumentos seção, selecione uma lista de células das quais deseja extrair valores exclusivos.

4. Então clique Ok botão e arraste a alça de preenchimento sobre as células que você deseja listar todos os valores exclusivos até que as células em branco sejam exibidas, veja a captura de tela:

Baixe grátis o Kutools para Excel agora!


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?

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office 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!
parte inferior da aba do escritório
Comentários (31)
Avaliado 5 fora do 5 · classificações 1
Este comentário foi feito pelo moderador no site
Is this formula complete? =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"),)&"",
Este comentário foi feito pelo moderador no site
Isso ainda não foi corrigido :sad:
Este comentário foi feito pelo moderador no site
que perda de tempo..... fórmula NÃO funciona
Este comentário foi feito pelo moderador no site
Obrigada!!! Passei horas tentando fazer isso e descobrir o que aconteceu com o Pivot Wizard (outro artigo).
Este comentário foi feito pelo moderador no site
Estou usando seu código VBA, mas não quero que a caixa apareça. Em vez disso, quero definir exatamente qual intervalo de células usar todas as vezes e exatamente em qual caixa colocar a saída. O intervalo de entrada e a saída estariam em duas planilhas diferentes. como faço para atualizar o VBA para fazer isso? Obrigada!!
Este comentário foi feito pelo moderador no site
Ei! Alguém sabe por que essa fórmula parece levar a um erro após a linha 87? Tipo, funciona perfeitamente e, em um certo ponto, apenas me retorna erros para cada linha .. o que é o pior! Porque estou tão perto de exatamente o que preciso aqui...
Este comentário foi feito pelo moderador no site
=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"),)&"" It doesn't work
Este comentário foi feito pelo moderador no site
oi eu quero extrair células únicas da primeira coluna quando comparo com outras colunas (tenho três colunas desiguais), como posso fazer isso?
Este comentário foi feito pelo moderador no site
oi eu tenho três colunas desiguais e quero extrair células únicas da primeira coluna. como eu posso fazer isso?? desde já, obrigado
Este comentário foi feito pelo moderador no site
Eu amo

Extrair valores exclusivos de várias colunas com tabela dinâmica
Este comentário foi feito pelo moderador no site
Você pode enviar a fórmula correta... a função VBA funciona bem.
Apenas para o meu projeto, prefiro usar a fórmula correta.


obrigado
Este comentário foi feito pelo moderador no site
alguém sabe, para a saída, como torná-lo em várias linhas, mas não em uma linha? (atualmente, o resultado de uma linha é obtido por worksheetfunction.transpose, mas o que eu quero alcançar (como resultado) é que, ao selecionar 3 colunas, o resultado retornado também são 3 colunas, em vez de uma
Este comentário foi feito pelo moderador no site
Esta fórmula de matriz está CORRETA. Dados nas colunas A a C, fórmula do primeiro resultado na célula D2... Esta é diferente de outra fórmula de matriz que a última é copiar a fórmula para baixo e Ctrl+Shift+Enter todas as fórmulas. No entanto, esta fórmula de matriz deve ser feita com Ctrl+Shift+Enter na primeira célula e copie para baixo.
Este comentário foi feito pelo moderador no site
Muchas gracias por la macro!!! me fue muy util
Este comentário foi feito pelo moderador no site
eu ajustei a minha planilha, mas estou retornando apenas o primeiro valor no array definido... o que estou perdendo?
Este comentário foi feito pelo moderador no site
Olá, Cody,
A fórmula acima funciona bem na minha planilha, você poderia dar uma captura de tela do seu problema de dados aqui?
Obrigado!
Este comentário foi feito pelo moderador no site
Em relação à versão da fórmula, você poderia explicar com mais detalhes o que essa parte está fazendo? *100+COLUMN($A:$C),7^8)),"R0C00") Especificamente, quais são os * 100, 7 8 ^e "R0C000" fazendo? Estou entendendo todo o resto, mas não consigo descobrir para que servem.
Este comentário foi feito pelo moderador no site
Um pouco tarde para minha resposta aqui, mas...
ROW($2:$9)*100 - isso está multiplicando o número da linha *100, então se estiver na linha 5, agora o número é 500
COLUMN($A:$C) - isso é adicionado ao número da linha*100, portanto, se for a linha 5 col 2, o número será 502.
7^8)), - isso (eu acho) deve ter um valor máximo para a instrução min anterior.
"R0C00") - formata o texto com base no número. No exemplo, tínhamos 502, então isso dá R5C02 (linha 5, col 02).

Se você tiver muitas colunas, mas não muitas linhas, poderá alterá-lo para ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
Este comentário foi feito pelo moderador no site
Obrigado pelo código. Estou usando o código VBA desta página. Existe uma maneira de adicionar um código de classificação depois que os valores exclusivos forem extraídos para que ele os classifique automaticamente?
Este comentário foi feito pelo moderador no site
podemos criar a função uniqdata em vez da macro?
Este comentário foi feito pelo moderador no site
Olá, İlhan,Se você gosta de uma Função Definida pelo Usuário para criar uma fórmula para resolver este problema, o código abaixo pode ajudá-lo: Após inserir o código, selecione uma lista de células onde deseja colocar os resultados. Então digite esta fórmula:=Únicos(A1:C4)  na barra de fórmulas. Pressione Ctrl + Shift + Enter chaves juntas. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
Este comentário foi feito pelo moderador no site
Czy para żart?
Este comentário foi feito pelo moderador no site
A fórmula de matriz na parte superior está funcionando muito bem quando usada com dados na mesma planilha, no entanto, quando tento usá-la para referenciar os mesmos dados exatos de outra planilha, a fórmula não retorna nada. Não consigo descobrir o porquê. Existe uma limitação com funções de matriz que impede você de fazer referência a intervalos em uma planilha diferente?

Obrigado por qualquer insight que você possa fornecer.
Este comentário foi feito pelo moderador no site
Olá Erin,

Feliz em ajudar. A função INDIRETA nesta fórmula é mais complicada de usar ao fazer referência a dados em outras planilhas. Não é recomendado usar esse recurso ao fazer referência a intervalos em planilhas diferentes.

Por exemplo: Agora os dados estão na Planilha1, quero referenciar o conteúdo da célula C2 da Planilha1 na Planilha2. Primeiro, em quaisquer duas células em Sheet2, como D1 e D2, insira Sheet1 e C2, respectivamente. Neste ponto, insira a fórmula na célula vazia de Sheet2:
=INDIRETO("'"&D1&"'!"&D2), então o conteúdo da célula C2 em Sheet1 pode ser retornado.

Como você pode ver, isso torna as coisas muito mais complexas. Espero que minha explicação possa ajudar. Tenha um bom dia.

Atenciosamente,
Mandy
Este comentário foi feito pelo moderador no site
Olá senhor! O VBA funcionou muito bem, muito obrigado por isso! Eu queria saber, se eu alterar os dados originais, é possível atualizar a coluna com os valores exclusivos automaticamente?
Avaliado 5 fora do 5
Este comentário foi feito pelo moderador no site
Olá Ioannis,

Feliz em ajudar. Depois de alterar os dados originais, o VBA não pode atualizar o resultado automaticamente. E a maneira mais fácil que consigo pensar é pressionar Ctrl + Alt + F9 para atualizar todos os resultados em planilhas em todas as pastas de trabalho abertas. Tenha um bom dia.

Atenciosamente,
Mandy
Este comentário foi feito pelo moderador no site
Obrigado por este grande artigo.

Para as pessoas que estão usando o fórmula de matriz em Excel não inglês deve-se ter um cuidado especial com a string de formato de texto: no seu exemplo: "R0C00".
Para alemão, isso se traduziria em "Z0S00". No entanto, "S" é um caractere especial referente a segundos para formatação de hora. Esse caractere precisa ser escapado e, portanto, a string de formato correta para o Excel alemão é "Z0\S00".

Espero que isso ajude alguém no futuro :-)
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL