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

Hogyan lehet vlookup segítségével több értéket visszaadni egy Excel cellában?

Általában az Excelben a VLOOKUP függvény használatakor, ha több érték is megfelel a feltételeknek, akkor csak az elsőt kaphatja meg. De néha vissza szeretné adni az összes megfelelő értéket, amely megfelel a kritériumoknak, egy cellába, a következő képernyőkép szerint. Hogyan tudná megoldani?

Vlookup több érték visszaadásához egy cellába a TEXTJOIN funkcióval (Excel 2019 és Office 365)

Vlookup több érték visszaadásához egy cellába a Felhasználó által definiált funkcióval

Vlookup segítségével több értéket adhat vissza egy cellába egy hasznos funkcióval


Vlookup több érték visszaadásához egy cellába a TEXTJOIN funkcióval (Excel 2019 és Office 365)

Ha az Excel magasabb verziója van, például az Excel 2019 és az Office 365, van egy új funkció - SZÖVEG, ezzel a hatékony funkcióval gyorsan megtekintheti a vlookup programot, és az összes megfelelő értéket visszaadhatja egy cellába.

Vlookup, hogy az összes megfelelő értéket egy cellába adja vissza

Kérjük, alkalmazza az alábbi képletet egy üres cellába, ahová be szeretné helyezni az eredményt, majd nyomja meg a gombot Ctrl + Shift + Enter gombokat együtt kapva az első eredményt, majd húzza lefelé a kitöltő fogantyút arra a cellára, amelyet használni szeretne ezzel a képlettel, és megkapja az összes megfelelő értéket az alábbi képernyőképen:

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

Jegyzet: A fenti képletben A2: A11 a keresési tartomány tartalmazza a keresési adatokat, E2 a keresési érték, C2: C11 az az adattartomány, amelyből vissza szeretné adni az egyező értékeket, ","az elválasztó a több rekord elválasztására.

Vlookup, hogy az összes egyező értéket ismétlés nélkül egy cellába adja vissza

Ha a keresési adatok alapján az összes egyező értéket vissza szeretné adni duplikációk nélkül, akkor az alábbi képlet segíthet.

Kérjük, másolja és illessze be az alábbi képletet egy üres cellába, majd nyomja meg az gombot Ctrl + Shift + Enter kulcsokat együtt kapva az első eredményt, majd másolja ezt a képletet, hogy kitöltse a többi cellát, és az összes megfelelő értéket megkapja a hibás értékek nélkül, az alábbi képernyőképen:

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

Jegyzet: A fenti képletben A2: A11 a keresési tartomány tartalmazza a keresési adatokat, E2 a keresési érték, C2: C11 az az adattartomány, amelyből vissza szeretné adni az egyező értékeket, ","az elválasztó a több rekord elválasztására.

Vlookup több érték visszaadásához egy cellába a Felhasználó által definiált funkcióval

A fenti TEXTJOIN függvény csak az Excel 2019 és az Office 365 esetén érhető el, ha más alacsonyabb Excel verziók is vannak, akkor néhány kódot kell használnia a feladat befejezéséhez.

Vlookup, hogy az összes megfelelő értéket egy cellába adja vissza

1. Tartsa lenyomva a ALT + F11 gombokat, és ez megnyitja a Microsoft Visual Basic for Applications ablak.

2. Kattints betétlap > Modulok, és illessze be a következő kódot a Modul ablak.

VBA kód: Vlookup több érték visszaadásához egy cellába

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. Ezután mentse el és zárja be ezt a kódot, menjen vissza a munkalapra, és írja be ezt a képletet: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") egy adott üres cellába, ahová el szeretné helyezni az eredményt, majd húzza lefelé a kitöltő fogantyút, hogy az összes megfelelő értéket egy kívánt cellába kapja, lásd: képernyőkép

Jegyzet: A fenti képletben A2: A11 a keresési tartomány tartalmazza a keresési adatokat, E2 a keresési érték, C2: C11 az az adattartomány, amelyből vissza szeretné adni az egyező értékeket, ","az elválasztó a több rekord elválasztására.

Vlookup, hogy az összes egyező értéket ismétlés nélkül egy cellába adja vissza

Ha figyelmen kívül akarja hagyni a visszaküldött megfelelő értékek duplikátumait, kérjük, tegye az alábbi kódot.

1. Tartsa lenyomva a Alt + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Kattints betétlap > Modulok, és illessze be a következő kódot a Modul ablak.

VBA kód: Vlookup és több egyedi egyeztetett értéket adhat vissza egy cellába

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. A kód beillesztése után kattintson a gombra Eszközök > Referenciák a nyitottban Microsoft Visual Basic for Applications ablakot, majd beugrott Referenciák - VBAProject párbeszédpanelen ellenőrizze Microsoft Scripting Runtime lehetőség a Elérhető referenciák lista mező, lásd a képernyőképeket:

4. Ezután kattintson OK a párbeszédpanel bezárásához mentse el és zárja be a kódablakot, térjen vissza a munkalapra, és írja be ezt a képletet: =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:

Jegyzet: A fenti képletben A2: C11 a használni kívánt adattartomány, E2 a keresési érték, a szám 3 az oszlop száma, amely tartalmazza a visszaadott értékeket.

Vlookup segítségével több értéket adhat vissza egy cellába egy hasznos funkcióval

 Ha megvan a mi Kutools az Excel számára, Annak Haladó kombinált sorok funkcióval gyorsan egyesítheti vagy kombinálhatja a sorokat ugyanazon érték alapján, és elvégezhet néhány számítást, amire szüksége van.

Jegyzet:Ennek alkalmazásához Haladó kombinált sorok, először is le kell töltenie a Kutools az Excel számára, majd gyorsan és egyszerűen alkalmazza a funkciót.

Telepítése után Kutools az Excel számára, kérjük, tegye a következőket:

1. Válassza ki azt az adattartományt, amelyhez az egyik oszlop adatait egy másik oszlop alapján kívánja kombinálni.

2. Kattints Kutools > Egyesítés és felosztás > Haladó kombinált sorok, lásd a képernyőképet:

3. A kiugrott Haladó kombinált sorok párbeszédablak:

  • Kattintson az összevonni kívánt kulcsoszlop nevére, majd kattintson a gombra Elsődleges kulcs
  • Ezután kattintson egy másik oszlopra, amelynek adatait a kulcsoszlop alapján kívánja egyesíteni, majd kattintson a gombra Kombájn válasszon egy elválasztót az egyesített adatok elválasztására.

4. Ezután kattintson OK gombra, és a következő eredményeket kapja:

Töltse le és ingyenes próbaverziót Kutools for Excel Now!


Relatívabb cikkek:

  • VLOOKUP funkció néhány alapvető és haladó példával
  • Az Excelben a VLOOKUP függvény az Excel legtöbb felhasználójának hatékony funkciója, amelyet arra használnak, hogy az adattartomány bal szélén lévő értéket keressen, és az Ön által megadott oszlopból adja vissza a megfelelő értéket ugyanabban a sorban. Ez az oktatóanyag a VLOOKUP függvény használatáról szól az Excel néhány alapvető és haladó példájával.
  • Több egyező értéket adhat vissza egy vagy több feltétel alapján
  • Normális esetben a VLOOKUP funkció használatával a legtöbbünk számára könnyű megkeresni egy adott értéket és visszaadni az egyező elemet. De próbált már valaha több egyező értéket visszaadni egy vagy több kritérium alapján? Ebben a cikkben bemutatok néhány képletet ennek az összetett feladatnak az Excelben történő megoldására.
  • Vlookup és több érték visszaadása függőlegesen
  • Normál esetben a Vlookup függvény használatával megkapja az első megfelelő értéket, de néha az összes megfelelő rekordot vissza szeretné adni egy adott kritérium alapján. Ebben a cikkben arról fogok beszélni, hogy hogyan lehet az összes egyező értéket függőlegesen, vízszintesen vagy egyetlen cellába visszaadni.
  • Vlookup és több értéket ad vissza a legördülő listából
  • Az Excelben hogyan lehetne vlookup-ot létrehozni és több megfelelő értéket visszaadni egy legördülő listából, ami azt jelenti, hogy ha egy elemet választ a legördülő listából, akkor annak összes relatív értéke egyszerre jelenik meg. Ez a cikk lépésről lépésre bemutatom a megoldást.

A legjobb irodai termelékenységi eszközök

A Kutools for Excel megoldja a legtöbb problémát, és 80% -kal növeli a termelékenységet

  • újrafelhasználás: Gyorsan helyezze be összetett képletek, diagramok és bármi, amit korábban használt; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
  • Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
  • Cellák / sorok / oszlopok egyesítése az adatok elvesztése nélkül; Osztott cellák tartalma; Kombinálja a duplikált sorokat / oszlopokat... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
  • Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
  • Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
  • Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
  • Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
  • Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
  • Több mint 300 hatékony funkció. Támogatja az Office / Excel 2007-2021 és 365 verziókat. Minden nyelvet támogat. Könnyű üzembe helyezés vállalatában vagy szervezetében. Teljes funkciók 30 napos ingyenes próbaverzió. 60 napos pénzvisszafizetési garancia.
kte lap 201905

Az Office fül a füles felületet hozza az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
officetab alja
A megjegyzések rendezése szerint
Hozzászólások (43)
Még nincs értékelés. Legyen Ön az első, aki értékel!
A weboldal moderátora ezt a megjegyzést minimalizálta
Hogyan állíthatnám be ezt a képletet úgy, hogy az egyes visszaadott értékeket elkülönítsem, kivéve a ", " értéket, és csak egyedi értékeket adjon vissza?
A weboldal moderátora ezt a megjegyzést minimalizálta
Köszi a kódot!!

Ami a helyettesítő karaktereket illeti, egy megoldás az INSTR használata

Cserélheti az [ If rng = pValue Then ] értéket az [ InStr(1, rng.Value, pValue) Then ]-re, és ha nem szeretné, hogy a kis- és nagybetűk legyenek megkülönböztetve, akkor az [ InStr(1, rng.Value, pValue, vbTextCompare) Ezután ]
A weboldal moderátora ezt a megjegyzést minimalizálta
Köszönöm a fenti VBA kódot. Meg tudná mondani, hogyan kell az eredményeket egy új sorba írni a cellában, pl. Alt-Enter 300 400 1000 1300
A weboldal moderátora ezt a megjegyzést minimalizálta
Köszönjük a fenti kód megosztását. Már több hónapja használom, de ma úgy tűnik, hogy nem működik. Üres cellákat kapok a szokásos hiba helyett, amikor adatokat kell visszaküldeni. Bármi hozzáfűznivaló?
A weboldal moderátora ezt a megjegyzést minimalizálta
Csodálatos munka. Pontosan azt kaptam, amit akartam!!! Szeretem !!
A weboldal moderátora ezt a megjegyzést minimalizálta
Szia! Nagyon lenyűgözött a munka, és olyan könnyű létrehozni egyet ennek a funkciónak a használatához. azonban további támogatásra van szükségem. Az én ? az, hogy hogyan tudok számot kiválasztani a vlookup tömbben több cellát tartalmazó cellából. azaz Ha az A1 cella = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banán Tehát hogyan választhatok ki 100-at a táblatömb C oszlopából a megfelelő D1 = alma származtatásához. Kérjük, vegye figyelembe, hogy a keresési értékemben és a táblázattömbben 7 jegyű számok vannak, amelyeket ";" választ el. Nagyon hálás lennék, ha meg tudná oldani ezt, és segítene sok időt megtakarítani.
A weboldal moderátora ezt a megjegyzést minimalizálta
Köszönjük a VBA-kódot. Pontosan azt kaptam, amit akarok! Csak az "rng.Offset(0, pIndex - 1)" kódot módosítottam "rng.Offset(0, pIndex - 2)" értékre. Így a MYVLOOKUP képes jobbról balra keresni.
A weboldal moderátora ezt a megjegyzést minimalizálta
Pontosan ezt kerestem, és nem gondoltam arra, hogy saját UDF-et készítsek. Azonban nem fog pontosan úgy működni, mint a VLOOKUP. Ha a keresett karakterlánc nem csak az első oszlopban található, akkor az átadott eredeti tartományon kívül eső adatokat adhat. Név Szám Más név A tartományon kívül eső oszlop sikeresen teljesítve Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Ha a fenti táblázat A1:D7 cellák, ha csak A1:C7 adta meg, akkor A "MYVLOOKUP" függvény 1 1 2 2 5 5 értéket ad vissza, amikor várhatóan 1 2 5. Az alábbi változtatások kijavítják a problémát: Funkció MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310Updated6' /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Érték = pÉrték Akkor xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'Minden rng in pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
A weboldal moderátora ezt a megjegyzést minimalizálta
Ez remekül működik, de segítségre van szükségem a paranccsal kapcsolatban, hogy eltávolíthassam az ismétlődéseket az eredményekből. De komolyan, remek munka.
A weboldal moderátora ezt a megjegyzést minimalizálta
Ez remekül működik, de továbbra is segítségre van szükségem a parancs funkcióval kapcsolatban, hogy eltávolíthassam az ismétlődéseket az eredményekből.
A weboldal moderátora ezt a megjegyzést minimalizálta
Értesítsen, vagy kövesse a megjegyzéseket
A weboldal moderátora ezt a megjegyzést minimalizálta
Ne adj vissza semmit! a MYLOOKUP alkalmazása után nem ad eredményt, csak üres.
A weboldal moderátora ezt a megjegyzést minimalizálta
Szia, jól működik. Azt szeretném tenni, hogy a kódot úgy alakítsam át, hogy az értékeket "///"-vel vagy bármilyen más jelölővel válassza el (technikai okokból nem akarok egyetlen karakterelválasztót). Azt is észrevettem, hogy ez a képlet nem működik wild card esetén. Tudom, hogy túl sokat kérdezek, de nem működik, mivel a vlookup nem működik, ha a =myvlookup("*"&E6&"*",$A$2:$C$15,2) kifejezésre keresek, amit megtenne/megtenne. Valami segítség?
A weboldal moderátora ezt a megjegyzést minimalizálta
Fel a fejjel. Kitaláltam, hogyan lehet bármilyen elválasztót beszerezni a kimenetbe. Kezdetleges. De rájöttem. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Az utolsó és legkívánatosabb dolog azonban az, hogy lehetővé teszi, hogy helyettesítő karakterekkel dolgozzon a keresési feltételekben. Még egyszer köszönöm ezt a gyönyörű és zseniális megoldást. Rendkívül hasznos. Most már csak azt szeretném elérni, hogy a makró futhasson, és állandóan telepíthető legyen az excelembe, függetlenül attól, hogy mit csinálok, hogy használhassam, amikor szükségem van rá. És helyettesítő karakterek! Nagyon szépen köszönöm. Már csak a helyettesítő karakterek használata van hátra.
A weboldal moderátora ezt a megjegyzést minimalizálta
Egyedi rekordhoz az alábbiakat használhatja: (más felhasználói kóddal módosítva) Funkció MYVLOOKUP (pValue As String, pWorkRng As Range, pWorkRng As Range, pIndex As Long) 'Frissítés 20150310 'Frissítve 6. június 9. Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 16 To Rows If pWorkRng.Cells(i, 1).Value = pValue Akkor xResult = xResult & "," & pWorkRng.Cells(i, 1).Eltolás(1, pIndex - 0) Vége, ha következő InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 1 Akkor sTemp = sTemp & sElválasztó & varSection End If Next varSection MYVLOOKUP = Mid(sDelimiter, Lenction)( +s0Temp)
A weboldal moderátora ezt a megjegyzést minimalizálta
Ez tökéletesen működött, de eltartott egy ideig, amíg a Function megfelelően működött a 20 lapos, 50 XNUMX sorosnál több soros táblázatomon. Most az a NAGY kérdés, hogy hogyan vegyük ezt a körülhatárolt karakterláncot, majd hogyan használjuk az egyes bejegyzéseket Index/Match (nem az Index/Match-el párosult, de gyorsabbnak tűnik) keresési értékként egy másik adatkészletben, visszaadva az összes eredmény SUM értékét egy cellába. . Az a forgatókönyvem, hogy van egy egyszeri megrendelésem, amely több számlával rendelkezik. A MYVLOOKUP funkciója kiválóan működik az összes számla egy cellában történő jelentésére. Amit most szeretnék tenni, az az, hogy minden összefűzött bevallást a jelentett cellával, átpörgessem a tömböt, és összesítsem az egyes számlák fizetési összegét a képlet cellájába. Nagyra értékelek minden segítséget, amit ezzel kapcsolatban felajánlhat, és köszönöm a MYVLOOKUP funkciót!
A weboldal moderátora ezt a megjegyzést minimalizálta
Bármit is csinálok, mindig kapok #értéket! eredmény helyett. A vlookup jól működik, tehát az adatok működnek. Már követte a makrók engedélyezésének folyamatát. Sőt, mindent egyetlen lapra kombináltam. Bármilyen ötletet??
A weboldal moderátora ezt a megjegyzést minimalizálta
Remek makró, hasznos. De tudni kell, hogy módosítható-e 2 feltétel ellenőrzéséhez, és talál-e valaki úgy, hogy a helyettesítő karakterek működjenek rajta. Valami segítség?
A weboldal moderátora ezt a megjegyzést minimalizálta
Van mód az eredmény módosítására úgy, hogy 1000 1000 -1000 helyett például 1,000/1,000/(1,000) jelenjen meg?
A weboldal moderátora ezt a megjegyzést minimalizálta
Remek funkció, bár a 100,000 XNUMX rekordon keresztüli darabolás egy kicsit soknak bizonyul szegény laptopomnak, egy éjszaka alatt futni kell!
A weboldal moderátora ezt a megjegyzést minimalizálta
Ez csodálatos, köszönöm!
Még senki sem írt megjegyzést
Több ingatlan
Hagyja meg észrevételeit
Feladás vendégként
×
Értékeld ezt a bejegyzést:
0   Karakterek
Javasolt helyek

Kövess minket

Copyright © 2009 - www.extendoffice.com. | Minden jog fenntartva. Powered by ExtendOffice. | Oldaltérkép
A Microsoft és az Office logó a Microsoft Corporation védjegyei vagy bejegyzett védjegyei az Egyesült Államokban és / vagy más országokban.
Sectigo SSL védi