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

Jak vlookup vrátit více hodnot v jedné buňce v aplikaci Excel?

Normálně v aplikaci Excel, pokud používáte funkci VLOOKUP, pokud existuje více hodnot, které odpovídají kritériím, můžete získat pouze první. Ale někdy chcete vrátit všechny odpovídající hodnoty, které splňují kritéria, do jedné buňky, jak ukazuje následující snímek obrazovky, jak byste to mohli vyřešit?

Vlookup pro vrácení více hodnot do jedné buňky pomocí funkce TEXTJOIN (Excel 2019 a Office 365)

Vlookup pro vrácení více hodnot do jedné buňky pomocí funkce definované uživatelem

Vlookup pro vrácení více hodnot do jedné buňky s užitečnou funkcí


Vlookup pro vrácení více hodnot do jedné buňky pomocí funkce TEXTJOIN (Excel 2019 a Office 365)

Pokud máte vyšší verzi Excelu, jako je Excel 2019 a Office 365, je tu nová funkce - TEXTJOIN, s touto výkonnou funkcí můžete rychle vlookup a vrátit všechny odpovídající hodnoty do jedné buňky.

Vlookup vrátí všechny odpovídající hodnoty do jedné buňky

Použijte následující vzorec do prázdné buňky, kam chcete umístit výsledek, a stiskněte Ctrl + Shift + Enter společně získáte první výsledek a poté přetáhněte popisovač výplně dolů do buňky, kterou chcete použít tento vzorec, a získáte všechny odpovídající hodnoty, jak je uvedeno níže:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Poznámka: Ve výše uvedeném vzorci A2: A11 je rozsah vyhledávání obsahuje data vyhledávání, E2 je vyhledávací hodnota, C2: C11 je rozsah dat, ze kterého chcete vrátit odpovídající hodnoty, “,"je oddělovač pro oddělení více záznamů.

Vlookup vrátí všechny odpovídající hodnoty bez duplikátů do jedné buňky

Pokud chcete vrátit všechny odpovídající hodnoty založené na vyhledávacích datech bez duplikátů, může vám pomoci následující vzorec.

Zkopírujte a vložte následující vzorec do prázdné buňky a stiskněte Ctrl + Shift + Enter klíče společně, abyste získali první výsledek, a poté zkopírujte tento vzorec, abyste vyplnili další buňky, a získáte všechny odpovídající hodnoty bez dulpikátů, jak je uvedeno níže:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Poznámka: Ve výše uvedeném vzorci A2: A11 je rozsah vyhledávání obsahuje data vyhledávání, E2 je vyhledávací hodnota, C2: C11 je rozsah dat, ze kterého chcete vrátit odpovídající hodnoty, “,"je oddělovač pro oddělení více záznamů.

Vlookup pro vrácení více hodnot do jedné buňky pomocí funkce definované uživatelem

Výše uvedená funkce TEXTJOIN je k dispozici pouze pro Excel 2019 a Office 365, pokud máte jiné nižší verze aplikace Excel, měli byste k dokončení tohoto úkolu použít některé kódy.

Vlookup vrátí všechny odpovídající hodnoty do jedné buňky

1. Podržte ALT + F11 klíče a otevře se Microsoft Visual Basic pro aplikace okno.

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

Kód VBA: Vlookup pro vrácení více hodnot do jedné buňky

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Poté tento kód uložte a zavřete, vraťte se do listu a zadejte tento vzorec: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") do konkrétní prázdné buňky, kam chcete umístit výsledek, pak přetáhněte úchyt výplně dolů a získejte všechny odpovídající hodnoty v jedné buňce, kterou chcete, viz screenshot:

Poznámka: Ve výše uvedeném vzorci A2: A11 je rozsah vyhledávání obsahuje data vyhledávání, E2 je vyhledávací hodnota, C2: C11 je rozsah dat, ze kterého chcete vrátit odpovídající hodnoty, “,"je oddělovač pro oddělení více záznamů.

Vlookup vrátí všechny odpovídající hodnoty bez duplikátů do jedné buňky

Chcete-li ignorovat duplikáty ve vrácených shodných hodnotách, postupujte podle níže uvedeného kódu.

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 Okno modulu.

Kód VBA: Vlookup a vrácení více jedinečných shodných hodnot do jedné buňky

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    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 screenshoty:

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,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

Poznámka: Ve výše uvedeném vzorci A2: C11 je rozsah dat, který chcete použít, E2 je vyhledávací hodnota, číslo 3 je číslo sloupce, které obsahuje vrácené hodnoty.

Vlookup pro vrácení více hodnot do jedné buňky s užitečnou funkcí

 Pokud máte naše Kutools pro Excel, S jeho Pokročilé kombinování řádků můžete rychle sloučit nebo kombinovat řádky na základě stejné hodnoty a provést některé výpočty podle potřeby.

Poznámka:Použít toto Pokročilé kombinování řádkůNejprve byste si měli stáhnout soubor Kutools pro Excela poté tuto funkci rychle a snadno aplikujte.

Po instalaci Kutools pro Excel, postupujte následovně:

1. Vyberte rozsah dat, do kterého chcete zkombinovat data jednoho sloupce na základě jiného sloupce.

2, klikněte Kutools > Sloučit a rozdělit > Pokročilé kombinování řádků, viz screenshot:

3. Ve vyskočené Pokročilé kombinování řádků dialogové okno:

  • Klikněte na název klíčového sloupce, podle kterého se bude kombinovat, a poté klikněte Primární klíč
  • Poté klikněte na jiný sloupec, jehož data chcete kombinovat na základě klíčového sloupce, a klikněte na Kombinovat vyberte jeden oddělovač pro oddělení kombinovaných dat.

4. Pak klikněte na tlačítko OK tlačítko a získáte následující výsledky:

Stáhněte si zdarma zkušební verzi Kutools pro Excel!


Více relativních článků:

  • Funkce VLOOKUP s některými základními a pokročilými příklady
  • V aplikaci Excel je funkce VLOOKUP výkonnou funkcí pro většinu uživatelů aplikace Excel, která slouží k vyhledání hodnoty v levé části datového rozsahu a vrácení odpovídající hodnoty ve stejném řádku ze zadaného sloupce. Tento kurz hovoří o tom, jak používat funkci VLOOKUP s některými základními a pokročilými příklady v aplikaci Excel.
  • Vrátit více hodnot shody na základě jednoho nebo více kritérií
  • Za normálních okolností je vyhledání konkrétní hodnoty a vrácení odpovídající položky pro většinu z nás snadné pomocí funkce VLOOKUP. Zkusili jste ale někdy vrátit více hodnot shody na základě jednoho nebo více kritérií? V tomto článku představím několik vzorců pro řešení tohoto složitého úkolu v aplikaci Excel.
  • Vlookup a vrátit více hodnot vertikálně
  • Za normálních okolností můžete použít funkci Vlookup k získání první odpovídající hodnoty, ale někdy chcete vrátit všechny odpovídající záznamy na základě konkrétního kritéria. V tomto článku budu hovořit o tom, jak vlookup a vrátit všechny odpovídající hodnoty svisle, vodorovně nebo do jedné buňky.
  • Vlookup a návrat více hodnot z rozevíracího seznamu
  • Jak v aplikaci Excel můžete vlookup a vrátit více odpovídajících hodnot z rozevíracího seznamu, což znamená, že když vyberete jednu položku z rozevíracího seznamu, zobrazí se všechny její relativní hodnoty najednou. V tomto článku představím řešení krok za krokem.

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-2021 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Plné 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 snižuje stovky kliknutí myší každý den!
officetab dno
Komentáře (43)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Jak bych upravil tento vzorec tak, aby oddělil každou vrácenou hodnotu kromě "," a vrátil pouze jedinečné hodnoty?
Tento komentář byl moderátorem webu minimalizován
Díky za kód!!

Pokud jde o zástupné znaky, řešením je použití INSTR

Můžete nahradit [ If rng = pValue Then ] za [ InStr(1, rng.Value, pValue) Then ] a pokud nechcete, aby se rozlišovala malá a velká písmena, použijte [ InStr(1, rng.Value, pValue, vbTextCompare) Potom ]
Tento komentář byl moderátorem webu minimalizován
Děkujeme za výše uvedený kód VBA. Můžete mi říct, jak udělat, aby se výsledky zapsaly na nový řádek v buňce, např. Alt-Enter 300 400 1000 1300
Tento komentář byl moderátorem webu minimalizován
Děkujeme za sdílení výše uvedeného kódu. Používám to již několik měsíců, ale dnes se mi zdá, že to nefunguje. Dostávám prázdné buňky místo obvyklé chyby, když existují data, která mají být vrácena. Nějaké nápady?
Tento komentář byl moderátorem webu minimalizován
Skvělá práce. Mám přesně to, co chci!!! Miluji to !!
Tento komentář byl moderátorem webu minimalizován
Ahoj, ta práce mě opravdu ohromila a je tak snadné ji vytvořit pro použití této funkce. nicméně potřebuji další podporu. Můj ? je to, jak mohu vybrat číslo z buňky s více buňkami v poli vlookup. tj. Pokud buňka A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = jablko D2 = banán Jak tedy vybrat 100 ze sloupce C pole tabulky, abych odvodil odpovídající D1 = jablko Všimněte si prosím, že ve vyhledávací hodnotě a poli tabulky mám 7místná čísla, která jsou oddělena „;“. Opravdu bych ocenil, kdybyste to mohli vyřešit a pomohli mi ušetřit spoustu času.
Tento komentář byl moderátorem webu minimalizován
Děkujeme za kód VBA. Mám přesně to, co chci! Upravil jsem pouze kód " rng.Offset(0, pIndex - 1) " na " rng.Offset(0, pIndex - 2) " . Stejně tak je MYVLOOKUP schopen vyhledávat zprava doleva.
Tento komentář byl moderátorem webu minimalizován
To je přesně to, co jsem hledal a nenapadlo mě udělat si vlastní UDF. Nebude však fungovat přesně jako VLOOKUP. Pokud hledaný řetězec není pouze v prvním sloupci, mohl by vám poskytnout data mimo původní předaný rozsah. Jméno Číslo Jiné jméno Sloupec není v rozsahu prošel Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Pokud výše uvedená tabulka byly buňky A1:D7, pokud jste prošli pouze A1:C7 vaše Funkce "MYVLOOKUP" vrátí 1 1 2 2 5 5, když byste očekávali, že vrátí 1 2 5. Níže uvedené změny problém řeší: Funkce MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Aktualizace 20150310 'Aktualizováno 6 /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rings as Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Hodnota = pValue Then xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'For Every rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Další MYVLOOKUP = xResult End Function
Tento komentář byl moderátorem webu minimalizován
Funguje to skvěle, ale potřebuji pomoc s příkazem k odstranění duplikátů z výsledků. Ale vážně, skvělá práce.
Tento komentář byl moderátorem webu minimalizován
Funguje to skvěle, ale stále potřebuji pomoc s funkcí příkazu k odstranění duplikátů z výsledků.
Tento komentář byl moderátorem webu minimalizován
Informujte mě o následných komentářích
Tento komentář byl moderátorem webu minimalizován
Nic nevracejte! po aplikaci MYLOOKUP nedává žádný výsledek, ale je prázdný.
Tento komentář byl moderátorem webu minimalizován
Ahoj, funguje to dobře. Co bych chtěl udělat, je upravit kód tak, aby odděloval vaše výsledky hodnot pomocí "///" nebo jakékoli jiné značky (z technických důvodů nechci pouze jeden oddělovač znaků). Také jsem si všiml, že tento vzorec nefunguje s divokou kartou. Vím, že žádám příliš mnoho, ale nefunguje to, protože vlookup může fungovat, když hledám =myvlookup("*"&E6&"*",$A$2:$C$15,2), což by udělal/mohl udělat. Nějaká pomoc?
Tento komentář byl moderátorem webu minimalizován
Hlavy vzhůru. Přišel jsem na to, jak v tom výstupu získat jakýkoli oddělovač. Jeho základní. Ale přišel jsem na to. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Poslední a nejžádanější věcí je umožnit práci se zástupnými znaky ve vyhledávacích kritériích. Ještě jednou děkuji za toto krásné a geniální řešení. Velmi užitečné. Teď jen chci, aby se makro spustilo a bylo trvale nainstalováno v mém excelu bez ohledu na to, co dělám, abych ho mohl použít, když budu potřebovat. A zástupné znaky! Děkuji mnohokrát. Zástupné znaky jsou vše, co zbývá udělat.
Tento komentář byl moderátorem webu minimalizován
Chcete-li získat jedinečný záznam, můžete použít níže: (upraveno odkazem na jiný uživatelský kód) Funkce MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Ztlumit xResult As String xResult = "" Ztlumit řádky jako dlouhé, i jako dlouhé řádky = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," For Each varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Tento komentář byl moderátorem webu minimalizován
Fungovalo to perfektně, ale trvalo mi nějakou dobu, než jsem zprovoznil funkci v mé tabulce s 20 kartami a více než 50 XNUMX řádky. Nyní je VELKÁ otázka, jak vzít tento oddělený řetězec a pak použít každou položku jako vyhledávací hodnotu Index/Match (není spojena s Index/Match, ale zdá se rychlejší) v jiné datové sadě a vrátit hodnotu SUM všech návratů do jedné buňky. . Můj scénář je, že mám jednu objednávku, která má více faktur. Vaše funkce MYVLOOKUP funguje skvěle a hlásí všechny faktury v jedné buňce. Co teď chci udělat, je vzít každý zřetězený výnos s nahlášenou buňkou, protočit toto pole a sečíst částky platby každé faktury zpět do buňky vzorce. Oceňuji jakoukoli pomoc, kterou s tím můžete nabídnout, a děkuji za funkci MYVLOOKUP!
Tento komentář byl moderátorem webu minimalizován
Ať dělám, co dělám, vždy dostanu #value! vrátil místo výsledku. vlookup funguje dobře, takže data fungují. Již proběhl proces povolení maker. Dokonce jsem vše spojil do jednoho listu. Nějaké nápady??
Tento komentář byl moderátorem webu minimalizován
Skvělé makro, užitečné. Potřebuji však vědět, zda je možné jej upravit tak, aby zkontroloval 2 kritéria a zda se přesto našel někdo, aby na něm fungovaly zástupné znaky. Nějaká pomoc?
Tento komentář byl moderátorem webu minimalizován
Existuje způsob, jak upravit výsledek, takže místo zobrazení 1000 1000 -1000 by se zobrazilo například 1,000 1,000/1,000 XNUMX/(XNUMX XNUMX)?
Tento komentář byl moderátorem webu minimalizován
Skvělá funkce, ale prokousat se 100,000 XNUMX záznamy je pro můj ubohý notebook trochu moc, budu ho muset nechat běžet přes noc!
Tento komentář byl moderátorem webu minimalizován
To je úžasné, děkuji!
Zatím zde nejsou žádné komentáře
Načíst další
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL