Kako vlookup vrniti več vrednosti v eni celici v Excelu?
Običajno lahko v Excelu, če uporabljate funkcijo VLOOKUP, če obstaja več vrednosti, ki ustrezajo merilom, dobite samo prvo. Toda včasih želite vrniti vse ustrezne vrednosti, ki ustrezajo merilom, v eno celico, kot je prikazano na spodnji sliki zaslona, kako bi to lahko rešili?
Vlookup za vrnitev več vrednosti v eno celico s funkcijo TEXTJOIN (Excel 2019 in Office 365)
- Vlookup za vrnitev vseh ujemajočih se vrednosti v eno celico
- Vlookup za vrnitev vseh ujemajočih se vrednosti brez dvojnikov v eno celico
Vlookup za vrnitev več vrednosti v eno celico z uporabniško določeno funkcijo
- Vlookup za vrnitev vseh ujemajočih se vrednosti v eno celico
- Vlookup za vrnitev vseh ujemajočih se vrednosti brez dvojnikov v eno celico
Vlookup za vrnitev več vrednosti v eno celico s koristno funkcijo
Vlookup za vrnitev več vrednosti v eno celico s funkcijo TEXTJOIN (Excel 2019 in Office 365)
Če imate višjo različico Excela, kot sta Excel 2019 in Office 365, obstaja nova funkcija - BESEDILOs to zmogljivo funkcijo lahko hitro najdete in vrnete vse ujemajoče se vrednosti v eno celico.
Vlookup za vrnitev vseh ujemajočih se vrednosti v eno celico
Prosimo, uporabite spodnjo formulo v prazno celico, kamor želite dati rezultat, nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat, in nato povlecite ročico za polnjenje navzdol do celice, v kateri želite uporabiti to formulo, in dobili boste vse ustrezne vrednosti, kot je prikazano na spodnji sliki zaslona:
Vlookup za vrnitev vseh ujemajočih se vrednosti brez dvojnikov v eno celico
Če želite vrniti vse ujemajoče se vrednosti, ki temeljijo na iskalnih podatkih brez podvojenj, vam lahko pomaga spodnja formula.
Kopirajte in prilepite naslednjo formulo v prazno celico, nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat, nato pa kopirajte to formulo, da zapolnite druge celice, in dobili boste vse ustrezne vrednosti brez dulpicate, kot je prikazano spodaj:
Vlookup za vrnitev več vrednosti v eno celico z uporabniško določeno funkcijo
Zgornja funkcija TEXTJOIN je na voljo samo za Excel 2019 in Office 365, če imate druge nižje različice Excela, uporabite nekaj kod za dokončanje te naloge.
Vlookup za vrnitev vseh ujemajočih se vrednosti v eno celico
1. Držite tipko ALT + F11 in odpre tipko Microsoft Visual Basic za aplikacije okno.
2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.
Koda VBA: Vlookup za vrnitev več vrednosti v eno celico
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. Nato shranite in zaprite to kodo, se vrnite na delovni list in vnesite to formulo: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") v določeno prazno celico, kamor želite umestiti rezultat, nato povlecite ročico za polnjenje navzdol, da dobite vse ustrezne vrednosti v eni želeni celici, glejte posnetek zaslona:
Vlookup za vrnitev vseh ujemajočih se vrednosti brez dvojnikov v eno celico
Če želite prezreti dvojnike v vrnjenih ujemajočih se vrednostih, upoštevajte spodnjo kodo.
1. Držite tipko Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.
Koda VBA: Vlookup in vrni več unikatnih ujemajočih se vrednosti v eno celico
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 vstavitvi kode kliknite Orodja > Reference v odprtem Microsoft Visual Basic za aplikacije okno in nato v izpuščenem Reference - VBAProject pogovorno okno, preverite Izvajanje Microsoftovih skriptov možnost v Razpoložljive reference seznam, glejte posnetke zaslona:
 |
 |
 |
4. Nato kliknite OK da zaprete pogovorno okno, shranite in zaprite okno s kodo, se vrnite na delovni list in vnesite to formulo: =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 za vrnitev več vrednosti v eno celico s koristno funkcijo
Če imate našega Kutools za Excel, Z njegovim Napredne kombinirane vrstice funkcijo, lahko hitro združite ali združite vrstice na podlagi iste vrednosti in opravite nekaj izračunov, kot jih potrebujete.
Po namestitvi Kutools za Excel, naredite naslednje:
1. Izberite obseg podatkov, za katerega želite združiti podatke enega stolpca na podlagi drugega stolpca.
2. Kliknite Kutools > Spoji in razdeli > Napredne kombinirane vrstice, glej posnetek zaslona:
3. V izskočil Napredne kombinirane vrstice pogovorno okno:
- Kliknite ime stolpca ključa, na katerem želite združiti, in nato kliknite Primarni ključ
- Nato kliknite drug stolpec, za katerega želite združiti njegove podatke glede na ključni stolpec, in kliknite Združite izbrati en ločilnik za ločevanje združenih podatkov.
4. Nato kliknite OK in dobili boste naslednje rezultate:
 |
|
 |
Prenesite in brezplačno preizkusite Kutools za Excel zdaj!
Več relativnih člankov:
- Funkcija VLOOKUP z nekaj osnovnimi in naprednimi primeri
- V Excelu je funkcija VLOOKUP zmogljiva funkcija za večino uporabnikov Excela, ki se uporablja za iskanje vrednosti v skrajnem levem delu obsega podatkov in vrnitev ustrezne vrednosti v isti vrstici iz stolpca, ki ste ga določili. Ta vadnica govori o uporabi funkcije VLOOKUP z nekaterimi osnovnimi in naprednimi primeri v Excelu.
- Vrne več ujemajočih se vrednosti na podlagi enega ali več meril
- Običajno je iskanje določene vrednosti in vrnitev ustreznega predmeta za večino od nas enostavno s funkcijo VLOOKUP. Ali ste že kdaj poskusili vrniti več ujemajočih se vrednosti na podlagi enega ali več meril? V tem članku bom predstavil nekaj formul za reševanje te zapletene naloge v Excelu.
- Iskanje in vrnitev več vrednosti navpično
- Običajno lahko s funkcijo Vlookup dobite prvo ustrezno vrednost, včasih pa želite vrniti vse ujemajoče se zapise na podlagi določenega merila. V tem članku bom govoril o tem, kako vlookup in vrnem vse ujemajoče se vrednosti navpično, vodoravno ali v eno samo celico.
- Iskanje in vrnitev več vrednosti s spustnega seznama
- Kako lahko v Excelu preverite in vrnete več ustreznih vrednosti s spustnega seznama, kar pomeni, da ko izberete en element s spustnega seznama, se naenkrat prikažejo vse njegove relativne vrednosti. V tem članku bom predstavil rešitev korak za korakom.
Najboljša pisarniška orodja za produktivnost
Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa. Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...
Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
