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

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 več vrednosti v eno celico z uporabniško določeno funkcijo

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:

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

Opomba: V zgornji formuli A2: A11 iskalno območje vsebuje podatke iskanja, E2 je iskalna vrednost, C2: C11 je obseg podatkov, iz katerega želite vrniti ujemajoče se vrednosti, ","je ločilo za ločevanje več zapisov.

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:

=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, ""))

Opomba: V zgornji formuli A2: A11 iskalno območje vsebuje podatke iskanja, E2 je iskalna vrednost, C2: C11 je obseg podatkov, iz katerega želite vrniti ujemajoče se vrednosti, ","je ločilo za ločevanje več zapisov.

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:

Opomba: V zgornji formuli A2: A11 iskalno območje vsebuje podatke iskanja, E2 je iskalna vrednost, C2: C11 je obseg podatkov, iz katerega želite vrniti ujemajoče se vrednosti, ","je ločilo za ločevanje več zapisov.

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:

Opomba: V zgornji formuli A2: C11 je obseg podatkov, ki ga želite uporabiti, E2 je iskalna vrednost, število 3 je številka stolpca, ki vsebuje vrnjene vrednosti.

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.

Opomba:Če želite uporabiti to Napredne kombinirane vrstice, najprej bi morali prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.

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 orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2021 in 365. Podpira vse jezike. Enostavna uvedba v vašem podjetju ali organizaciji. 30-dnevna brezplačna preizkusna različica vseh funkcij. 60-dnevna garancija vračila denarja.
zavihek kte 201905

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!
dno pisarniške mize
Komentarji (43)
Ocene še ni. Bodite prvi in ​​ocenite!
Ta komentar je moderator na spletnem mestu minimiziral
Kako naj prilagodim to formulo, da loči vsako vrnjeno vrednost, razen ", " in vrne samo edinstvene vrednosti?
Ta komentar je moderator na spletnem mestu minimiziral
Hvala za kodo!!

Kar se tiče nadomestnih znakov, je rešitev z uporabo INSTR

[ Če rng = pValue Then ] lahko zamenjate z [ InStr(1, rng.Value, pValue) Then ] in če ne želite, da je občutljiv na velike in male črke, uporabite [ InStr(1, rng.Value, pValue, vbTextCompare) Nato ]
Ta komentar je moderator na spletnem mestu minimiziral
Hvala za zgornjo kodo VBA. Ali mi lahko poveste, kako naj se rezultati vnesejo v novo vrstico v celici, npr. Alt-Enter 300 400 1000 1300
Ta komentar je moderator na spletnem mestu minimiziral
Hvala, ker ste delili zgornjo kodo. To uporabljam že nekaj mesecev, danes pa se zdi, da ne deluje. Dobim prazne celice namesto običajne napake, ko je treba vrniti podatke. Kakšne misli?
Ta komentar je moderator na spletnem mestu minimiziral
Odlično delo.. Dobil sem točno to, kar želim !!! Všeč mi je !!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, res sem navdušen nad delom in tako enostavno ga je ustvariti za uporabo te funkcije. vendar potrebujem dodatno podporo. moj ? je to, kako lahko izberem številko iz celice z več celicami v mojem nizu vlookup. tj. Če je celica A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banana Torej, kako lahko izberem 100 iz mojega stolpca matrike tabele C, da izpeljem ustrezen D1 = jabolko. Zelo bi bil hvaležen, če lahko to rešite in mi pomagate prihraniti veliko časa.
Ta komentar je moderator na spletnem mestu minimiziral
Hvala za kodo VBA. Dobil sem točno to, kar želim! Spremenil sem samo kodo " rng.Offset(0, pIndex - 1) " v " rng.Offset(0, pIndex - 2) ". Tako lahko MYVLOOKUP išče od desne proti levi.
Ta komentar je moderator na spletnem mestu minimiziral
To je točno tisto, kar sem iskal in nisem razmišljal o tem, da bi samo naredil lastno UDF. Vendar ne bo deloval tako kot VLOOKUP. Če niz, ki ga iščete, ni samo v prvem stolpcu, vam lahko da podatke izven prvotnega posredovanega obsega. Ime Številka Drugo ime Stolpec ni v dosegu opravil Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Če bi bila zgornja tabela celice A1:D7, če ste opravili samo A1:C7 vaš Funkcija "MYVLOOKUP" vrne 1 1 2 2 5 5, ko bi pričakovali, da bo vrnila 1 2 5. Spodnje spremembe odpravljajo težavo: Funkcija MYVLOOKUP(pValue kot niz, pWorkRng kot obseg, pIndex kot dolgo) "Posodobitev 20150310 6" /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Zatemni vrstice tako dolgo, i As Long Rows = pWorkRng.Rows.Count Za i = 1 Do vrstic Če pWorkRng.Cells(i, 1). Vrednost = pVrednost Nato xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) Konec, če Naprej i 'Za vsak rng v pWorkRng ' Če je rng = pValue Potem ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' Konec, če 'Naslednji MYVLOOKUP = xRezult Končna funkcija
Ta komentar je moderator na spletnem mestu minimiziral
To deluje odlično, vendar potrebujem pomoč z ukazom za odstranitev dvojnikov iz rezultatov. Resno, odlično delo.
Ta komentar je moderator na spletnem mestu minimiziral
To deluje odlično, vendar še vedno potrebujem pomoč pri ukazni funkciji za odstranitev dvojnikov iz rezultatov.
Ta komentar je moderator na spletnem mestu minimiziral
Obvesti me o nadaljnjih komentarjih
Ta komentar je moderator na spletnem mestu minimiziral
Nič ne vrnite! po uporabi MYLOOKUP brez rezultata, ampak prazno.
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, dobro deluje. Kar bi rad naredil, je prilagodil kodo, da loči rezultate vrednosti z "///" ali katerim koli drugim označevalcem (iz tehničnih razlogov ne želim samo enega ločevalnika znakov). Prav tako sem opazil, da ta formula ne deluje z nadomestno kartico. Vem, da sprašujem preveč, vendar ne, saj vlookup lahko deluje, ko iščem =myvlookup("*"&E6&"*",$A$2:$C$15,2), kar bi naredil/lahko naredil. Kakšna pomoč?
Ta komentar je moderator na spletnem mestu minimiziral
Glave gor. Ugotovil sem, kako dobiti ločilo v tem izhodu. Njegova rudimentarna. Ampak sem ugotovil. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Zadnja in najbolj zaželena stvar pa je omogočanje dela z nadomestnimi znaki v iskalnih kriterijih. Še enkrat hvala za to lepo in briljantno rešitev. Izjemno koristno. Zdaj želim samo, da se makro zažene in ga trajno namesti v moj excel, ne glede na to, kaj počnem, da ga lahko uporabljam, ko je treba. In nadomestni znaki! Najlepša hvala. Nadomestni znaki so vse, kar je preostalo.
Ta komentar je moderator na spletnem mestu minimiziral
Če želite dobiti edinstven zapis, lahko uporabite spodaj: (spremenjeno z drugo uporabniško kodo) Funkcija MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Posodobitev 20150310' Posodobljeno 6/9/16 Jay Coltrain 'Dim rng As Range Zatemni xRezultat kot niz xResult = "" Zatemni vrstice tako dolgo, i Kot dolge vrstice = pWorkRng.Rows.Count za i = 1 do vrstic Če pWorkRng.Cells(i, 1).Value = pValue Potem je xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) Konec, če Naslednji i Zatemni varSection kot varianta Dim sTemp Kot niz Zatemni sDelimiter Kot string sDelimiter = "," Za vsak varSection v Split(xResult, sDelimiter) Če InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Potem sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) End Function) +
Ta komentar je moderator na spletnem mestu minimiziral
To je delovalo odlično, vendar sem potreboval nekaj časa, da sem funkcija pravilno delovala v moji preglednici z 20 zavihki, 50k+ vrsticami. Zdaj je VELIKO vprašanje, kako vzeti ta razmejen niz in nato uporabiti vsak vnos kot vrednost za iskanje/ujemanje (ni poročeno z indeksom/ujemanje, vendar se zdi hitrejše) v drugem naboru podatkov, ki vrne vrednost SUM vseh vrnitev v eno celico. . Moj scenarij je, da imam posamezno naročilo, ki ima več računov. Vaša funkcija MYVLOOKUP deluje odlično in poroča o vseh računih v eni celici. Zdaj želim vzeti vsak povezan donos s sporočeno celico, zavrteti skozi to matriko in sešteti zneske plačila vsakega računa nazaj v celico formule. Cenim vsako pomoč, ki jo lahko ponudite pri tem, in hvala za funkcijo MYVLOOKUP!
Ta komentar je moderator na spletnem mestu minimiziral
Ne glede na to, kaj počnem, vedno dobim #vrednost! vrnjeno namesto rezultata. vlookup deluje čisto v redu, tako da podatki delujejo. Že sledil postopek omogočanja makrov. Vse sem celo združila v en list. Kaj idej??
Ta komentar je moderator na spletnem mestu minimiziral
Odličen makro, uporaben. Vendar moram vedeti, ali ga je mogoče spremeniti, da preveri 2 kriterija in ali je kdo vseeno našel, da bi nadomestni znaki delovali na tem. Kakšna pomoč?
Ta komentar je moderator na spletnem mestu minimiziral
Ali obstaja način za spremembo rezultata, tako da bi namesto prikaza 1000 1000 -1000 prikazal na primer 1,000/1,000/(1,000)?
Ta komentar je moderator na spletnem mestu minimiziral
Odlična funkcija, kljub temu, da se prebiranje 100,000 zapisov izkaže za veliko za moj ubogi prenosnik, ga bom moral pustiti, da deluje čez noč!
Ta komentar je moderator na spletnem mestu minimiziral
To je neverjetno, hvala!
Tu še ni objavljenih komentarjev
Obremenitev Več
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL