Přejít k hlavnímu obsahu

Jak snadno zřetězit text na základě kritérií v aplikaci Excel?

Předpokládejme, že mám sloupec čísel ID, který obsahuje nějaké duplikáty a sloupec jmen, a teď chci zřetězit jména na základě jedinečných čísel ID, jak je ukázáno vlevo, rychle kombinovat text na základě kritérií, jak bychom mohli dělat v Excelu?

doc kombinovat text na základě kritérií 1

Zřetězit text na základě kritérií pomocí funkce definované uživatelem

Zřetězte text na základě kritérií pomocí programu Kutools pro Excel


Chcete-li kombinovat text s jedinečnými čísly ID, můžete nejprve extrahovat jedinečné hodnoty a poté vytvořit funkci definovanou uživatelem a kombinovat názvy na základě jedinečného ID.

1. Vezměte si následující data jako příklad, musíte nejprve extrahovat jedinečná čísla ID, použijte tento vzorec pole: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Zadejte tento vzorec do prázdné buňky, například D2, a stiskněte Ctrl + Shift + Enter klíče společně, viz screenshot:

doc kombinovat text na základě kritérií 2

Tip: Ve výše uvedeném vzorci, A2: A15 je rozsah dat seznamu, ze kterého chcete extrahovat jedinečné hodnoty, D1 je první buňka ve sloupci, který chcete vyjmout výsledek extrakce.

2. A potom přetáhněte úchyt výplně dolů a extrahujte všechny jedinečné hodnoty, dokud se nezobrazí mezery, viz screenshot:

doc kombinovat text na základě kritérií 3

3. V tomto kroku byste měli vytvořit a Uživatelem definovaná funkce Chcete-li kombinovat jména na základě jedinečných identifikačních čísel, podržte stisknutou klávesu ALT + F11 klíče a otevře se Microsoft Visual Basic pro aplikace okno.

4, klikněte Vložit > Modula vložte následující kód do Modul Okno.

Kód VBA: zřetězení textu na základě kritérií

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. Poté tento kód uložte a zavřete, vraťte se do svého listu a zadejte tento vzorec do buňky E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , viz screenshot:

doc kombinovat text na základě kritérií 4

6. Poté přetáhněte popisovač výplně dolů do buněk, na které chcete použít tento vzorec, a všechny odpovídající názvy byly kombinovány na základě čísel ID, viz screenshot:

doc kombinovat text na základě kritérií 5

Tip:

1. Ve výše uvedeném vzorci A2: A15 jsou původní data, která chcete kombinovat, D2 je jedinečná hodnota, kterou jste extrahovali, a B2: B15 je sloupec s názvem, který chcete spojit dohromady.

2. Jak vidíte, kombinoval jsem hodnoty oddělené čárkou, můžete použít libovolné další znaky změnou čárky „,“ vzorce podle potřeby.


Pokud máte Kutools pro Excel, S jeho Pokročilé kombinování řádků nástroj, můžete rychle a pohodlně zřetězit textovou základnu podle kritérií.

Kutools pro Excel : s více než 300 praktickými doplňky Excel, můžete si je vyzkoušet bez omezení do 30 dnů.

Po instalaci Kutools pro Excel, proveďte následující kroky:

1. Na základě jednoho sloupce vyberte rozsah dat, který chcete kombinovat.

2, klikněte Kutools > Sloučit a rozdělit > Pokročilé kombinování řádků, viz screenshot:

3. V Kombinujte řádky na základě sloupce V dialogovém okně klikněte na sloupec ID a poté klikněte na Primární klíč Chcete-li tento sloupec vytvořit jako klíčový sloupec, na kterém jsou založena vaše kombinovaná data, podívejte se na snímek obrazovky:

doc kombinovat text na základě kritérií 7

4. A pak klikněte na tlačítko Příjmení sloupec, do kterého chcete hodnoty zkombinovat, klikněte na Kombinovat možnost a vyberte jeden oddělovač pro kombinovaná data, viz screenshot:

doc kombinovat text na základě kritérií 8

5. Po dokončení těchto nastavení klikněte na OK k opuštění dialogu a data ve sloupci B byla zkombinována společně na základě klíčového sloupce A. Viz screenshot:

doc kombinovat text na základě kritérií 9

Díky této funkci bude následující problém vyřešen co nejdříve:

Jak kombinovat více řádků do jednoho a sčítat duplikáty v aplikaci Excel?

Stáhněte si zdarma zkušební verzi Kutools pro Excel!


Kutools pro Excel: s více než 300 praktickými doplňky aplikace Excel, můžete vyzkoušet bez omezení do 30 dnů. Stáhněte si a vyzkoušejte zdarma hned teď!

Nejlepší nástroje pro produktivitu v kanceláři

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

Popis


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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