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

Como vlookup e retornar vários valores sem duplicatas no Excel? 

Às vezes, você pode querer vlookup e retornar vários valores correspondentes em uma única célula de uma vez. Mas, se houver alguns valores repetidos preenchidos nas células retornadas, como você poderia ignorar as duplicatas e apenas manter os valores exclusivos ao retornar todos os valores correspondentes conforme a captura de tela a seguir mostrada no Excel?

doc retorna vários valores únicos 1

Vlookup e retorna vários valores correspondentes sem duplicatas usando a função definida pelo usuário


Vlookup e retorna vários valores correspondentes sem duplicatas usando a função definida pelo usuário

O seguinte código VBA pode ajudá-lo a retornar vários valores correspondentes sem duplicatas, faça o seguinte:

1. Segure o Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Módulo Janela.

Código VBA: Vlookup e retorna vários valores correspondentes exclusivos:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Após inserir o código, clique em Ferramentas > Referências no aberto Microsoft Visual Basic para Aplicações janela e, em seguida, no Referências - VBAProject caixa de diálogo, verifique Tempo de execução de scripts da Microsoft opção no Referências Disponíveis caixa de lista, veja a captura de tela:

doc retorna vários valores únicos 2

4. Então clique OK para fechar a caixa de diálogo, salve e feche a janela de código, retorne à planilha e insira esta fórmula: =MultipleLookupNoRept(E2,A2:C17,3) em uma célula em branco onde você deseja produzir o resultado, pressione Entrar chave para obter o resultado correto conforme necessário. Veja a imagem:

doc retorna vários valores únicos 3

Nota: Na fórmula acima, E2 são os critérios que você deseja ver, A2: C17 é o intervalo de dados que você deseja usar, o número 3 é o número da coluna que contém os valores retornados.


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-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. 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 (13)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
e se eu quisesse criar uma lista em uma tabela a partir disso, em vez de todos os resultados em uma célula?
Este comentário foi feito pelo moderador no site
Olá, Tom,
Se você deseja extrair os valores exclusivos em uma lista de células em vez de uma célula, a seguinte fórmula pode ajudá-lo:

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Por favor tente.
Este comentário foi feito pelo moderador no site
Olá Skyyang,

Muito obrigado por esta fórmula.
Isso funciona para mim. No entanto, está demorando muito para processar um grande conjunto de dados.
Podemos modificar esta fórmula para trabalhar este pouco mais rápido?
obrigado novamente
Rasike
Este comentário foi feito pelo moderador no site
Oi skyyang e se você quiser o resultado como uma coluna?
Este comentário foi feito pelo moderador no site
Existe uma maneira de adicionar um espaço entre os vários valores recuperados nos resultados sem introduzir uma vírgula no final da lista? Por exemplo, seu resultado acima seria exibido como: "Emily, James, Daisy, Gary" em vez de: "Emily, James, Daisy, Gary"

Eu tentei editar esta parte do código VBA: xStr = xStr & xDic.Keys(I) & "," para ser isto: xStr = xStr & xDic.Keys(I) & ", "

Isso adicionou o espaço entre os valores, mas também adicionou uma vírgula após o último valor. "Emily, James, Daisy, Gary,"

Existe uma maneira de fazê-lo funcionar com o espaço, mas sem a vírgula extra após o último valor?
Este comentário foi feito pelo moderador no site
Olá, Demétre,
Use o espaço para separar os valores, basta alterar o código vba:
de xStr = xStr & xDic.Keys(i) & "," para ser isto: xStr = xStr & xDic.Keys(i) & " "

Por favor tente.
Este comentário foi feito pelo moderador no site
xStr = xStr & xDic.Keys(I) & "," para ser isto: xStr = xStr & xDic.Keys(I) & ", "

Existe uma maneira de substituir "," por ALT + ENTER na célula, para que os resultados fiquem na mesma célula, mas em linhas diferentes? Preciso introduzir módulo VBA adicional para isso e combiná-los?

Além disso, esse código é bastante lento ao fazer loops em tabelas enormes. Alguém conhece alguma solução mais rápida?
Este comentário foi feito pelo moderador no site
Olá, Imre,
Para separar os valores do resultado pelas teclas Alt + Enter, aplique a seguinte Função Definida pelo Usuário:

Função MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim xDic como novo dicionário
Escurecer xRows por muito tempo
Dim xStr As String
Dim i tanto tempo
On Error Resume Next
xRows = LookupRange.Rows.Count
Para i = 1 Para xLinhas
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
Se acabar
Seguinte
xStr = ""
MultipleLookupNoRept = xStr
Se xDic.Count > 0 Then
Para i = 0 Para xDic.Count - 1
xStr = xStr & xDic.Teclas(i) & Chr(10) + Chr(13)
Seguinte
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
Se acabar
Debug.Print xStr
Função final

E, em seguida, faça as etapas acima neste artigo, finalmente, depois de inserir a fórmula, você deve clicar em Wrap Text na guia Home.
Este comentário foi feito pelo moderador no site
Hi

Eu queria criar uma lista em uma tabela a partir disso, em vez de todos os resultados em uma célula. Então eu usei uma fórmula semelhante abaixo (o que você sugeriu)

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

No entanto, isso está levando muito tempo para processar a partir de um grande conjunto de dados.
Existe algum método alternativo para processar isso mais rápido?
obrigado novamente
Rasike
Este comentário foi feito pelo moderador no site
Oi,
enquanto o tempo de multivlooks de valor de lote minha planilha ficou travada. existem outras maneiras de multivlookupwithoutrepeation????

e também eu usei no novo desktop também está ficando travado apenas ...

meu valor de dados é de cerca de 10,000 linhas
Este comentário foi feito pelo moderador no site
Olá, eu fiz como você disse e é ótimo, mas ainda não resolveu um dos meus problemas, o que acontece quando você tem um valor único em cada mês? =MultipleLookupNoRept(E2,A2:C17,3), tento E2&1 para janeiro, mas não funciona
Este comentário foi feito pelo moderador no site
Olá, Jaime,
Você poderia dar o seu problema como uma captura de tela aqui, para que eu possa entender suas necessidades?
Este comentário foi feito pelo moderador no site
Isso é ótimo! Como eu adaptaria isso para não adicionar valores nulos ao dicionário? Eu tentei adicionar o negrito abaixo, mas a string final ainda está retornando com "", instances.


xRows = LookupRange.Rows.Count
Para i = 1 Para xLinhas
Se LookupRange.Columns(1).Cells(i).Value = Lookupvalue e não IsEmpty(LookupRange.Columns(1).Cells(i).Value) Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
Se acabar
Seguinte

Obrigado,
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos