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

Jak vlookup a vrátit více hodnot bez duplikátů v aplikaci Excel? 

Někdy můžete chtít vlookup a vrátit více shodných hodnot do jedné buňky najednou. Ale pokud se do vrácených buněk naplní několik opakovaných hodnot, jak byste mohli ignorovat duplikáty a zachovat pouze jedinečné hodnoty při vracení všech odpovídajících hodnot jako následující snímek obrazovky zobrazený v aplikaci Excel?

doc vrátí více jedinečných hodnot 1

Vlookup a vrátit více hodnot shody bez duplikátů pomocí funkce definované uživatelem


Vlookup a vrátit více hodnot shody bez duplikátů pomocí funkce definované uživatelem

Následující kód VBA vám pomůže vrátit více shodných hodnot bez duplikátů, udělejte to takto:

1. Podržte Alt + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do Modul Okno.

Kód VBA: Vlookup a vrátit více jedinečných shodných hodnot:

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. Po vložení kódu klikněte na Tools > Reference v otevřeném Microsoft Visual Basic pro aplikace okno a poté ve vyskakovacím okně Reference - VBAProject v dialogovém okně zaškrtněte Microsoft Scripting Runtime možnost v Dostupné reference seznam, viz screenshot:

doc vrátí více jedinečných hodnot 2

4. Pak klikněte na tlačítko OK zavřete dialogové okno, uložte a zavřete okno s kódem, vraťte se do listu a zadejte tento vzorec: =MultipleLookupNoRept(E2,A2:C17,3) do prázdné buňky, kam chcete výsledek odeslat, stiskněte vstoupit klíč k získání správného výsledku, jak potřebujete. Viz screenshot:

doc vrátí více jedinečných hodnot 3

Poznámka: Ve výše uvedeném vzorci, E2 jsou kritéria, která chcete zobrazit, A2: C17 je rozsah dat, který chcete použít, číslo 3 je číslo sloupce, které obsahuje vrácené hodnoty.


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2019 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Kompletní funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
karta kte 201905

Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50% a omezuje stovky kliknutí myší každý den!
officetab dno
Komentáře (13)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
co kdybych z toho chtěl vytvořit seznam v tabulce místo všech výsledků v jedné buňce?
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Tome,
Pokud chcete místo jedné buňky extrahovat jedinečné hodnoty ze seznamu buněk, může vám pomoci následující vzorec:

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

Zkuste to prosím.
Tento komentář byl moderátorem webu minimalizován
Ahoj Skyyang,

Děkuji mnohokrát za tento vzorec.
Tohle mi funguje. Zpracování velkého souboru dat však trvá dlouho.
Můžeme tento vzorec upravit, aby fungoval o něco rychleji?
Ještě jednou děkuji
Rasike
Tento komentář byl moderátorem webu minimalizován
Ahoj skyyang, co když chceš výsledek jako sloupec?
Tento komentář byl moderátorem webu minimalizován
Existuje způsob, jak přidat mezeru mezi více hodnot načtených ve výsledcích bez zavedení čárky na konec seznamu? Váš výše uvedený výsledek by například vypadal jako: „Emily, James, Daisy, Gary“ namísto takto: „Emily, James, Daisy, Gary“

Pokusil jsem se upravit tuto část kódu VBA: xStr = xStr & xDic.Keys(I) & "," na toto: xStr = xStr & xDic.Keys(I) & ","

Tím se přidala mezera mezi hodnoty, ale také se přidala čárka za poslední hodnotou. "Emily, James, Daisy, Gary,"

Existuje způsob, jak zajistit, aby to fungovalo s mezerou, ale bez další čárky za poslední hodnotou?
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Demetre,
Použijte mezeru k oddělení hodnot, stačí změnit kód vba:
z xStr = xStr & xDic.Keys(i) & "," bude toto: xStr = xStr & xDic.Keys(i) & " "

Zkuste to prosím.
Tento komentář byl moderátorem webu minimalizován
xStr = xStr & xDic.Keys(I) & "," má být toto: xStr = xStr & xDic.Keys(I) & ", "

Existuje způsob, jak nahradit "," pomocí ALT+ENTER v buňce, takže výsledky budou ve stejné buňce, ale na různých řádcích? Musím k tomu zavést další modul VBA a kombinovat je?

Tento kód je také poměrně pomalý při procházení velkých tabulek. Zná někdo nějaké rychlejší řešení?
Tento komentář byl moderátorem webu minimalizován
Ahoj Imre,
Chcete-li oddělit výsledné hodnoty klávesami Alt + Enter, použijte následující uživatelem definovanou funkci:

Funkce MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim xDic jako nový slovník
Dim xRows As Long
Dim xStr jako řetězec
Dim i tak dlouho
On Error Resume Next
xRows = LookupRange.Rows.Count
Pro i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
další
xStr = ""
MultipleLookupNoRept = xStr
Pokud xDic.Count > 0 Pak
Pro i = 0 To xDic.Count - 1
xStr = xStr & xDic.Keys(i) & Chr(10) + Chr(13)
další
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
End If
Debug.Print xStr
End Function

A pak proveďte výše uvedené kroky v tomto článku, nakonec byste po zadání vzorce měli kliknout na Zalomit text na kartě Domů.
Tento komentář byl moderátorem webu minimalizován
Hi

Chtěl jsem z toho vytvořit seznam v tabulce namísto všech výsledků v jedné buňce. Takže jsem použil vzorec podobný níže (to, co jste navrhli)

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

Zpracování velkého souboru dat však trvá dlouho.
Existuje nějaká alternativní metoda, jak to zpracovat rychleji?
Ještě jednou děkuji
Rasike
Tento komentář byl moderátorem webu minimalizován
Ahoj,
zatímco čas multivlooks hodnoty lotu můj list zamrzl. Existují nějaké jiné způsoby, jak multivlookupwithoutrepeation??

a také jsem používal na nové ploše také jeho zamrzání...

moje hodnota dat je kolem 10,000 XNUMX řádků
Tento komentář byl moderátorem webu minimalizován
Ahoj , udělal jsem , jak jsi řekl , a je to skvělé , ale stále to nevyřešilo jeden z mých problémů , co se stane , když u jedinečné hodnoty v každém měsíci ? =MultipleLookupNoRept(E2,A2:C17,3), zkouším E2&1 na leden, ale nefunguje to
Tento komentář byl moderátorem webu minimalizován
Ahoj, Jame,
Mohl byste sem dát svůj problém jako snímek obrazovky, abych pochopil vaše požadavky?
Tento komentář byl moderátorem webu minimalizován
To je skvělé! Jak bych to přizpůsobil, aby se do slovníku nepřidávaly hodnoty null? Pokusil jsem se přidat tučné písmo níže, ale konečný řetězec se stále vrací s instancemi "",.


xRows = LookupRange.Rows.Count
Pro i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue And Not IsEmpty(LookupRange.Columns(1).Cells(i).Value) Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
další

Díky,
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění