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

Como extrair dinamicamente uma lista de valores exclusivos de um intervalo de colunas no Excel?

Para um intervalo de colunas em que os valores mudam regularmente, e você sempre precisa obter todos os valores exclusivos do intervalo, independentemente de como ele mudou. Como fazer uma lista dinâmica de valores únicos? Este artigo mostrará como lidar com isso.

Extraia dinamicamente uma lista de valores únicos de um intervalo de colunas com fórmula
Extraia dinamicamente uma lista de valores únicos de um intervalo de colunas com o código VBA


Extraia dinamicamente uma lista de valores únicos de um intervalo de colunas com fórmula

Como mostrado na captura de tela abaixo, você precisa extrair dinamicamente uma lista de valores exclusivos do intervalo B2: B9. Tente a seguinte fórmula de matriz.

1. Selecione uma célula em branco, como D2, insira a fórmula abaixo nela e pressione o botão Ctrl + mudança + Entrar chaves simultaneamente. (B2: B9 são os dados da coluna dos quais você deseja extrair os valores únicos, D1 é a célula acima onde sua fórmula está localizada)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Continue selecionando a célula D2 e ​​arraste a alça de preenchimento para baixo para obter todos os valores exclusivos do intervalo especificado.

Agora todos os valores exclusivos no intervalo de colunas B2: B9 são extraídos. Quando os valores neste intervalo mudaram, a lista de valores exclusivos será alterada dinamicamente imediatamente.

Selecione e destaque facilmente todos os valores únicos em um intervalo no Excel:

O Selecione Células Duplicadas e Únicas utilidade de Kutools for Excel pode ajudá-lo a selecionar e destacar facilmente todos os valores exclusivos (incluindo as primeiras duplicatas) ou os valores exclusivos que aparecem apenas uma vez, bem como valores duplicados conforme necessário, conforme a captura de tela abaixo.
Baixe Kutools para Excel agora! (30-dia de trilha livre)


Extraia dinamicamente uma lista de valores únicos de um intervalo de colunas com o código VBA

Você também pode extrair uma lista de valores exclusivos dinamicamente de um intervalo de colunas com o seguinte código VBA.

1. Pressione outro + F11 simultaneamente para abrir o Microsoft Visual Basic para Aplicações janela.

2. No Microsoft Visual Basic para Aplicações janela, clique em inserção > Módulo. Em seguida, copie e cole o código VBA abaixo no Módulo janela.

Código VBA: extraia uma lista de valores exclusivos de um intervalo

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Nota: No código, D2 é a célula em que você localizará a lista de valores exclusivos. Você pode alterá-lo conforme necessário.

3. Volte para a planilha, clique inserção > Formas > retângulo. Veja a imagem:

4. Desenhe um retângulo em sua planilha e digite algumas palavras que você precisa para exibir nela. Em seguida, clique com o botão direito e selecione Atribuir Macro no menu do botão direito. No Atribuir Macro caixa de diálogo, selecione o CriarListaÚnica no Nome da macro e, em seguida, clique no OK botão. Veja a imagem:

5. Agora clique no botão retângulo, um Kutools for Excel caixa de diálogo aparece, selecione o intervalo contém valores exclusivos que você precisa extrair e, em seguida, clique no OK botão.

De agora em diante, você pode repetir a etapa 5 acima para atualizar a lista de valores exclusivos automaticamente.


Artigos relacionados:


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 (35)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Obrigado pelo tutorial. Usando o método de fórmula, como você alteraria a fórmula se quisesse adicionar um qualificador de categoria? Digamos que na coluna C você distingue se o item é uma fruta ou um vegetal. Como você alteraria o código para classificar apenas as frutas exclusivas e excluir os vegetais? Eu tentei substituir COUNTIF por COUNTIFS, usando o segundo critério de countifs de (LIST RANGE,"CATEGORY") mas ele retorna em branco. Eu precisaria expandir minha matriz e incorporar PROCV?
Este comentário foi feito pelo moderador no site
Eu sou decente no Excel, mas estou realmente tentando entender como e por que a fórmula acima funciona (funciona para o que estou usando, mas devo entender o porquê). Às vezes fico um pouco confuso usando arrays, então qualquer explicação em termos idiotas seria extremamente útil.
Este comentário foi feito pelo moderador no site
Esta fórmula está desatualizada e não funciona. Eu literalmente apenas configurei essa planilha exata do Excel para ver se eu poderia fazer essa fórmula funcionar e isso não acontece.
Este comentário foi feito pelo moderador no site
Oi Cara,
Qual versão do Office você usa?
Este comentário foi feito pelo moderador no site
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - achei que funciona em outro site...

Use Ctrl+Shift+Enter para obter a função de matriz (chaves). Arraste, copie e cole as fórmulas até que o #NA seja mostrado. Meu conjunto de dados estava na Coluna-Q, foi comparado para ver se existia na lista de exclusivos na Coluna-V, que se estende continuamente ao longo dessa mesma coluna.
Este comentário foi feito pelo moderador no site
Dia bom.
Liste todos os valores exclusivos da coluna Q com a fórmula abobv e, em seguida, use sua fórmula =IF(D2=V1,"Correspondência","Sem correspondência") para comparar se os únicos na coluna Q se comparam à coluna V na mesma linha .
Este comentário foi feito pelo moderador no site
Olá, e obrigado pela sua ajuda.

Eu preciso exatamente dessa funcionalidade, mas minha lista de "valores exclusivos" precisa se estender por colunas em vez de linhas, para que a lista em expansão nas linhas não funcione para mim.

Como posso modificar esta fórmula para fazer com que a lista de "valores únicos" se expanda à medida que a arrasto pelas colunas?

Desvio()?
Transpor()?
Indirect() com uma string de referências absolutas concatenadas com uma referência à coluna em vez da linha?


Thanks again.
Este comentário foi feito pelo moderador no site
Caro Ryan,
Esta fórmula =SEERRO(ÍNDICE($B$2:$B$9, CORRESP(0,CONT.SE($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter pode ajudá-lo a resolver o problema.
Veja abaixo a captura de tela:
Este comentário foi feito pelo moderador no site
Além disso, por qualquer motivo, a fórmula original forneceu:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

retorna um aviso de "referência circular" e não calcula.
Este comentário foi feito pelo moderador no site
Caro Ryan,
Qual versão do Office você usa? A fórmula funciona bem no meu Office 2016 e 2013.
Este comentário foi feito pelo moderador no site
Já aconteceu isso antes - minha correção foi que eu estava inserindo a fórmula na célula D1 (equivalente na planilha que estava usando). Seja qual for a célula a que $D:$1 corresponde, você precisa inseri-la na célula abaixo - D2. Desculpe se não é por isso que você recebeu o erro
Este comentário foi feito pelo moderador no site
Alguma dica sobre como fazer com que a opção VBA funcione com o Excel 2016 para macOS? Eu segui os passos; no entanto, quando executo a macro, nada acontece. Obrigado!
Este comentário foi feito pelo moderador no site
Daer Jones,
Por favor, tente o código VBA abaixo e deixe-me saber se ele funciona para você. Obrigada!

Sub CriarListaÚnica()
Dim xRng como intervalo
Dim xLastRow Tão Longo
Escurecer xLastRow2 enquanto
Dim I como inteiro
' Em Erro Continuar Próximo
Set xRng = Application.InputBox("Por favor, selecione o intervalo:", "Kutools for Excel", Selection.Address, , , , , 8)
Se xRng não for nada, saia do sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicatas Colunas:=1, Cabeçalho:=xlNão
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
Para I = 1 Para xLastRow2
If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
Se acabar
Seguinte
End Sub
Este comentário foi feito pelo moderador no site
Olá Cristal,
Estou tentando usar a versão VB da lista de valores exclusivos e estou com um problema.
O intervalo do qual desejo criar uma coluna de valores exclusivos são todas as fórmulas que se referem a guias diferentes.
Como se obtém o valor para transferir em vez da fórmula?
Este comentário foi feito pelo moderador no site
Querido Mike,
Por favor, converta suas referências de fórmula para absolutas e, em seguida, aplique o script VB.
Este comentário foi feito pelo moderador no site
Eu tenho o mesmo problema, exceto que minha fórmula se refere a nomes de colunas e não pode converter em absoluto.
Como faço para alterar o vba para colar os valores e não a fórmula?
Este comentário foi feito pelo moderador no site
Como você adicionaria vários critérios, por exemplo, se você só quisesse adicionar à lista dinâmica se a data fosse apenas 9/12?

Estou tentando "&" na fórmula MATCH, mas não está funcionando.

Por exemplo, com base no seu exemplo:
=SEERRO(ÍNDICE($B$2:$B$9, CORRESP(0 & B4,CONT.SE($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Isso gera um erro ou cria duplicatas.

Como alternativa, li que "+" pode funcionar, embora não consiga fazê-lo funcionar. Ou usando PEQUENO.

Idéias?
Este comentário foi feito pelo moderador no site
Prezado Zac,
Desculpe não poder ajudar com isso, você pode postar sua pergunta em nosso fórum: https://www.extendoffice.com/forum.html para obter mais suportes do Excel de nosso profissional.
Este comentário foi feito pelo moderador no site
Como você adicionaria uma segunda variável? Por exemplo, quero todos os elementos exclusivos em uma coluna que também compartilhem um valor semelhante em outra coluna. No seu exemplo, imagine uma 3ª coluna intitulada "Departamento" que teria valores como produto, carne, etc. Eu sei que são todos Produtos, mas espero que você entenda meu ponto. Você modificaria a fórmula CountIF para COUNTIFS ou a modificaria de outra maneira?
Este comentário foi feito pelo moderador no site
Oi Matt
Por favor, tente esta fórmula =SE(ISNA(PROCV(A2,$C$2:$C$13,1,FALSO)),"Sim","").
Supondo que as duas listas comparadas sejam a Coluna A e a Coluna C, se os valores únicos ficarem apenas na Coluna A mas não na Coluna C, será exibido Sim na coluna B; enquanto se não retornar nada na Coluna B, significa que o valor correspondente permanece na Coluna A e na Coluna C.
Este comentário foi feito pelo moderador no site
Obrigado pela resposta .. mas quente para retirar esse valor exclusivo se for exibido SIM .. você poderia me aconselhar a fórmula para puxar o valor exclusivo em coluna diferente.
Este comentário foi feito pelo moderador no site
Se eu fizer isso para uma planilha do Excel de mil linhas na versão mais recente do Excel em um Mac, ela nunca retornará. A primeira linha funciona, mas quando eu duplico, o Excel entra em um modo de computação que não retorna valores há mais de duas horas.

Alguma ideia de como fazer isso para listas grandes (até 2k linhas) que retornarão 50 ou 60 valores exclusivos?

Eu zombei disso no aplicativo "Numbers" e está funcionando perfeitamente lá, levando apenas alguns minutos para calcular. Está demorando tanto no Excel que me pergunto se ele será concluído. Estou pensando em deixá-lo "executar" durante a noite para ver o que vai acontecer.
Este comentário foi feito pelo moderador no site
Verifique suas opções de cálculo. Ele precisa ser definido como automático. Arquivo > Opções > Fórmulas > Opções de cálculo > Cálculo da pasta de trabalho (seleção automática)
Este comentário foi feito pelo moderador no site
Estou tentando arrastar a fórmula além dos meus dados reais para que eu possa inserir conjuntos de dados de tamanhos diferentes e não precisar ajustar nada. No entanto, a última linha após o término dos meus dados reais sempre retorna um "0". Estou usando os valores exclusivos para outra coisa em uma coluna adjacente, e o 0 faz com que o último valor seja repetido (quando excluo o 0, o valor não é mais repetido). Alguma idéia de como corrigir isso? Também estou usando o Office 365 Business
Este comentário foi feito pelo moderador no site
Olá, obrigado pela sua ajuda.
Agora, como posso ter meus valores também classificados em ordem alfabética? (Não quero usar o filtro na minha tabela master)
Devo usar um COUNTIFS em vez de COUNTIF?
Por favor ajude
Este comentário foi feito pelo moderador no site
Oi Alexis,
Desculpe, não posso ajudar a classificar o valor extraído em ordem alfabética ao mesmo tempo com a fórmula. Obrigado pelo seu comentário.
Este comentário foi feito pelo moderador no site
Estou usando essa fórmula =SEERRO(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") que é ótima para uma coluna, mas meus dados estão espalhados por várias colunas e linhas. Posso editar a fórmula para incluir toda a área? Meus dados vivem de AC4 a AR60...
Este comentário foi feito pelo moderador no site
Eu tento o código VBA e a fórmula. O código VBA está funcionando muito bem mas não consigo manter um arquivo com macro. Mas o problema é que não consigo fazer a fórmula funcionar. Alguém teve uma ideia? Obrigada
Este comentário foi feito pelo moderador no site
Oi Charlotte
Obrigado pelo seu comentário. Você pode manter o arquivo com macro para uso futuro salvando a pasta de trabalho como uma pasta de trabalho habilitada para macro do Excel.
Para o problema da fórmula, você poderia fornecer uma captura de tela dos seus dados? Obrigado pelo seu comentário.
Este comentário foi feito pelo moderador no site
Muito obrigado
Este comentário foi feito pelo moderador no site
como fazer o código vba funcionar para um intervalo onde foi usada outra fórmula?na coluna BI tem uma fórmula, referente as colunas D e E.
Se eu usar aplicar o código na coluna L (digamos), (obviamente, modificando corretamente as células do código) a macro retorna a fórmula aplicada nas colunas M e N... Funciona, então, mas não como eu quero! Como manter os valores na coluna B? obrigado
Este comentário foi feito pelo moderador no site
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief é het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje com unieke waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 criterium filteren. Ook daar zijn wel weer oplossingen voor, maar dat is wat complexer.
Este comentário foi feito pelo moderador no site
Eu gostaria de poder fazer exatamente a mesma coisa, exceto usar dois intervalos de coluna separados (B2:B9) e (D2:D9) isso é possível?
Este comentário foi feito pelo moderador no site
Oi Anthony,
Você pode colocar os resultados na mesma coluna dos dados originais. Como a coluna B neste caso.
Mas você precisa fazer referência à célula superior da célula de resultado na fórmula da seguinte maneira.
=SEERRO(ÍNDICE($B$2:$B$9, CORRESP(0,CONT.SE($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
Este comentário foi feito pelo moderador no site
com este procedimento de filtro se hace de forma muy rapida

1.PT ESTE EJEMPLO los datos a remove los duplicados estan en la col A de la fila 59 a la 239
2. defina um critério de filtragem de filtro neste caso no fila d56 o título de mismo de removedor duplicados e o d57 la dejo em branco
3. una vez ejecutado se muestran los dados en la fila destino, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57"), CopyToRange:=Range("D59"), Unique:=True
Não há comentários postados aqui ainda
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