Ugrás a tartalomra

Hogyan lehet összefűzni a szöveget az Excel kritériumai alapján?

Ha feltételezem, hogy van egy azonosítószámú oszlopom, amely tartalmaz néhány duplikátumot és egy oszlop nevet, most szeretném összefűzni a neveket az egyedi azonosítószámok alapján, a bal oldali képernyőképen, hogy gyorsan összefoghassuk a szöveget kritériumok alapján, hogyan lehetne csinálni az Excel-ben?

A doc egyesíti a szöveget az 1. kritérium alapján

Összevonja a szöveget kritériumok alapján a felhasználó által definiált funkcióval

Szöveg összefűzése kritériumok alapján a Kutools for Excel segítségével


A szöveg és az egyedi azonosító számok kombinálásához előbb kivonhatja az egyedi értékeket, majd létrehozhat egy felhasználó által definiált függvényt, hogy az egyedi azonosító alapján egyesítse a neveket.

1. Vegyük például a következő adatokat: előbb ki kell vonni az egyedi azonosító számokat, kérjük, alkalmazza ezt a tömbképletet: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Írja be ezt a képletet egy üres cellába, például a D2-be, majd nyomja meg a gombot Ctrl + Shift + Enter gombok együtt, lásd a képernyőképet:

A doc egyesíti a szöveget az 2. kritérium alapján

típus: A fenti képletben A2: A15 az a lista adattartomány, amelyből egyedi értékeket szeretne kinyerni, D1 az oszlop első cellája, ahová ki szeretné tenni a kivonási eredményt.

2. Ezután húzza lefelé a kitöltő fogantyút az összes egyedi érték kibontásához, amíg üresek nem jelennek meg, lásd a képernyőképet:

A doc egyesíti a szöveget az 3. kritérium alapján

3. Ebben a lépésben létre kell hoznia a Felhasználó által definiált funkció a nevek egyedi azonosító számok alapján történő kombinálásához tartsa lenyomva a ALT + F11 gombokat, és ez megnyitja a Microsoft Visual Basic for Applications ablak.

4. Kattints betétlap > Modulok, és illessze be a következő kódot a Modulok Ablak.

VBA kód: összefűzi a szöveget kritériumok alapján

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

5. Ezután mentse el és zárja be ezt a kódot, menjen vissza a munkalapra, és írja be ezt a képletet az E2 cellába, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , lásd a képernyőképet:

A doc egyesíti a szöveget az 4. kritérium alapján

6. Ezután húzza le a kitöltő fogantyút azokra a cellákra, amelyeken alkalmazni kívánja ezt a képletet, és az összes megfelelő nevet egyesítették az azonosító számok alapján, lásd a képernyőképet:

A doc egyesíti a szöveget az 5. kritérium alapján

Tipp:

1. A fenti képletben A2: A15 az az eredeti adat, amely alapján össze akarsz kapcsolni, D2 a kivont egyedi érték, és B2: B15 a név oszlop, amelyet össze akarsz kapcsolni.

2. Amint láthatja, vesszővel elválasztott értékeket egyesítettem, bármilyen más karaktert használhat a képlet vesszőjének szükség szerinti megváltoztatásával.


Ha van Kutools az Excel számára, Annak Haladó kombinált sorok segédprogram segítségével gyorsan és kényelmesen összefűzheti a szövegalapot a kritériumok alapján.

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, tegye a következőket:

1. Válassza ki az egyesíteni kívánt adattartományt egy oszlop alapján.

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

3. Az Kombinálja a sorokat az oszlop alapján párbeszédpanelen kattintson az ID oszlopra, majd a gombra Elsődleges kulcs hogy ez az oszlop legyen az a kulcsoszlop, amelyre az összevont adatok alapulnak, lásd a képernyőképet:

A doc egyesíti a szöveget az 7. kritérium alapján

4. Kattintson a gombra Név oszlopot, amelybe az értékeket egyesíteni szeretné, majd kattintson Kombájn opciót, és válasszon egy elválasztót az egyesített adatokhoz, lásd a képernyőképet:

A doc egyesíti a szöveget az 8. kritérium alapján

5. Miután elvégezte ezeket a beállításokat, kattintson a gombra OK a párbeszédablakból való kilépéshez, és a B oszlopban lévő adatokat az A kulcsoszlop alapján egyesítettük. Lásd a képernyőképet:

A doc egyesíti a szöveget az 9. kritérium alapján

Ezzel a szolgáltatással a következő probléma a lehető leghamarabb megoldódik:

Hogyan kombinálhat több sort egybe, és összegezheti a duplikátumokat az Excelben?

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 (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Great function! Is there a way to maintain the format in the cell it's concatenating data from? i.e. $45.07, $555.34, $0.00, $0.25, -$12.25 I've figured out how to stack them with wrap text and CHAR(10) in place of "," but having trouble keeping the format. I will be using this for a mail merge in Word.
This comment was minimized by the moderator on the site
Hello, Laurie,If you want to keep the cell formatting when concatenating the data, you can apply the Advanced Combine Rows feature of Kutools for Excel, in the dialog box, after finishing the settings, you just need to check Use formatted values option, and all the data formatting will be kept as you need.
You can download Kutools for Excel and free trial 30-day.
This comment was minimized by the moderator on the site
Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
This comment was minimized by the moderator on the site
Hi, Hossain,May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".=SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
Please try, thank you!
This comment was minimized by the moderator on the site
It worked like a charm sir. Thank you so much.
This comment was minimized by the moderator on the site
Great function, exactly what I needed! Works like a charm
This comment was minimized by the moderator on the site
Hi,

Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


Thank you in advance. :)
This comment was minimized by the moderator on the site
Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
This comment was minimized by the moderator on the site
BTW i used the VBA solution
This comment was minimized by the moderator on the site
Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
This comment was minimized by the moderator on the site
How can I ignore blank cells? mine currently displays this:

";;;;;;;;;"

I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
This comment was minimized by the moderator on the site
Hello, Chantelle
When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
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
If ConcatenateRange.Cells(i).Value <> "" Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
thank you very much! This was so simple and helped a lot!!
This comment was minimized by the moderator on the site
Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
This comment was minimized by the moderator on the site
Hello, David,

To combine the cells with line break, the following User Defined Function may help you.

Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
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 & vbCrLf & ConcatenateRange.Cells(I).Value
End If
Next I
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Exit Function
End Function

After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations