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, hogy az összes megfelelő értéket egy cellába adja vissza
- Vlookup, hogy az összes egyező értéket ismétlés nélkül egy cellába adja vissza
Vlookup több érték visszaadásához egy cellába a Felhasználó által definiált funkcióval
- Vlookup, hogy az összes megfelelő értéket egy cellába adja vissza
- Vlookup, hogy az összes egyező értéket ismétlés nélkül egy cellába adja vissza
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:
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:
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
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:
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.
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ója a Kutools for Excel programhoz most!
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 hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz 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!
