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 vrátí všechny odpovídající hodnoty do jedné buňky
- Vlookup vrátí všechny odpovídající hodnoty bez duplikátů do jedné buňky
Vlookup pro vrácení více hodnot do jedné buňky pomocí funkce definované uživatelem
- Vlookup vrátí všechny odpovídající hodnoty do jedné buňky
- Vlookup vrátí všechny odpovídající hodnoty bez duplikátů do jedné buňky
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:
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:
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:
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:
Vlookup pro vrácení více hodnot do jedné buňky s užitečnou funkcí
Pokud máte naše Kutools for 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.
Po instalaci Kutools for 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 a vyzkoušejte zdarma Kutools for Excel Nyní !
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ší nástroje pro produktivitu v kanceláři
Rozšiřte své excelentní dovednosti pomocí Kutools for Excela zažijte efektivitu jako nikdy předtím. Kutools for Excel Nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab Přináší rozhraní s kartami do Office 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!
