Ugrás a tartalomra

Hogyan lehet több megfelelő értéket megtekinteni és összefűzni az Excelben?

Mint mindannyian ismertük, a VLOOKUP Az Excel funkció segíthet számunkra egy érték megkeresésében és a megfelelő adatok visszaküldésében egy másik oszlopba, de általában csak akkor kaphatja meg az első relatív értéket, ha több egyező adat van. Ebben a cikkben arról fogok beszélni, hogy hogyan lehet több megfelelő értéket csak egy cellában vagy egy függőleges listában összefűzni és összefűzni.

Vlookup és több egyező értéket ad vissza függőlegesen a képlettel

Vlookup és összefűz több egyező értéket egy cellában a felhasználó által definiált funkcióval

Keressen fel és kapcsoljon össze több egyező értéket egy cellában a Kutools for Excel segítségével


Tegyük fel, hogy a következő adattartománnyal rendelkezem: tömbképletet alkalmazhat ahhoz, hogy az összes megfelelő értéket függőlegesen megkapjam egy adott érték alapján, amint az a következő képernyőkép látható.

doc vlookup összefűzés 1

1. Írja be ezt a képletet: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") egy üres cellába, ahová be akarja helyezni az eredményt, például E2, majd nyomja meg a gombot Ctrl + Shift + Enter kulcsok együtt kapják meg a relatív értékalapot egy adott kritériumhoz, lásd a képernyőképet:

doc vlookup összefűzés 2

Megjegyzések: A fenti képletben:

A1: A16 az az oszloptartomány, amely tartalmazza a keresni kívánt értéket;

D2 jelzi a vlookup kívánt értéket;

B1: B16 az az oszloptartomány, amelyből vissza szeretné adni a megfelelő adatokat;

$ 1: $ 16 jelzi a sorok referenciáját a tartományon belül.

2. Ezután válassza ki az E2 cellát, és húzza le a kitöltő fogantyút a cellákig, amíg üres cellákat nem kap, és az összes megfelelő érték felkerül az oszlopba a következő képernyőkép szerint:

doc vlookup összefűzés 3


Ahelyett, hogy a relatív értékeket függőlegesen kapná meg, néha azt szeretné, hogy az egyező cellákba illessze az értékeket, és összefűzze azokat egy külön elválasztóval. Ebben az esetben a következő Felhasználó által definiált funkció tehet Önnek szívességet.

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 Modulok Ablak.

VBA kód: Vlookup és összefűz több egyező értéket egy cellában

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
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: = cusvlookup (D2, A1: B16,2) egy üres cellába, ahová be akarja helyezni az eredményt, és nyomja meg a gombot belép kulcs, az összes, egy adott adaton alapuló megfelelő értéket egy térelválasztóval ellátott cellába helyezték vissza, lásd a képernyőképet:

doc vlookup összefűzés 4

Megjegyzések: A fenti képletben: D2 jelzi a keresni kívánt cellaértékeket, A1: B16 az az adattartomány, amelybe be akarja tölteni az adatokat, a szám 2 az az oszlopszám, ahonnan az egyező értéket vissza kell adni, megváltoztathatja a tézisek hivatkozásait az Ön igényeinek megfelelően.


Ha van Kutools az Excel számára, Annak Haladó kombinált sorok funkcióval könnyedén elvégezheti ezt a munkát. Ez a szolgáltatás segíthet az összes egyező érték kombinálásában egy meghatározott elválasztókkal ugyanazon adatok alapján egy másik oszlopban.

Kutools az Excel számára : több mint 300 praktikus Excel-bővítménnyel, ingyenesen, korlátozás nélkül, 30 nap alatt kipróbálható.

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 meg szeretné kapni a megfelelő értékeket az adott adatok alapján.

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

3. Az Haladó kombinált sorok párbeszédpanelen kattintson az oszlop nevére, amely alapján kombinálni kívánja, majd kattintson a gombra Elsődleges kulcs gomb, lásd a képernyőképet:

doc vlookup összefűzés 6

4. Ezután kattintson egy másik oszlop nevére, amelyre vissza szeretné adni az egyező értékeket, majd kattintson a gombra Kombájn egy elválasztó kiválasztásához az egyesített értékek elkülönítéséhez lásd a képernyőképet:

doc vlookup összefűzés 7

5. Kattintson a gombra Ok gombra, az azonos értékeken alapuló összes megfelelő értéket egyesítettük egy adott elválasztóval, lásd a képernyőképeket:

doc vlookup összefűzés 8 2 doc vlookup összefűzés 9

 Töltse le és ingyenes próbaverziója a Kutools for Excel programhoz most!


Kutools az Excel számára: több mint 300 praktikus Excel-bővítménnyel, ingyenesen, korlátozás nélkül, 30 nap alatt kipróbálható. Töltse le és ingyenes próbaverziót most!

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

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...

kte lap 201905


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!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there any way to get the unique "name" for "class1"
This comment was minimized by the moderator on the site
Hello, sym-john,
Maybe the below article can solve your problem, please view it:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
This comment was minimized by the moderator on the site
This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

E.g:

Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
This comment was minimized by the moderator on the site
you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
This comment was minimized by the moderator on the site
The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
This comment was minimized by the moderator on the site
Hi, jeff,
Thanks for your sharing, you must be a warmhearted man.
This comment was minimized by the moderator on the site
I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

Thank you so much for your help!!
This comment was minimized by the moderator on the site
Is there a way to delete the duplicate values in the concatenate?
This comment was minimized by the moderator on the site
Hello, Jacob,
May be the following article can help you to solve your problem.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
This comment was minimized by the moderator on the site
you can add two extra condition to skip blank cells and to skip duplicates:For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Next i
This comment was minimized by the moderator on the site
This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
This comment was minimized by the moderator on the site
Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :) Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(0, pIndex - 1) If Left(xResult, 1) = ";" Then xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
This comment was minimized by the moderator on the site
Make if condition for result if empty.

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
If Not result = "" Then
result = result & " " & x.Offset(0, indexcol - 1)
Else
result = x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
This comment was minimized by the moderator on the site
When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
This comment was minimized by the moderator on the site
How to get the result. Please help. data data1 result a 1 a1 b 2 a2 c b1 b2 c1 c2
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations