Salt la conținutul principal

Cum se concatenează cu ușurință textul pe baza criteriilor din Excel?

Presupunând că am o coloană de numere de identificare care conține câteva duplicate și o coloană de nume, iar acum vreau să concatenez numele pe baza numerelor de identificare unice, așa cum este afișat ecranul din stânga, pentru a combina rapid textul pe baza criteriilor, cum am putea faceți în Excel?

doc combina textul pe baza criteriilor 1

Concatenează text pe baza criteriilor cu funcția definită de utilizator

Concatenează text pe baza criteriilor cu Kutools pentru Excel


Pentru a combina textul cu numerele ID unice, puteți extrage mai întâi valorile unice și apoi puteți crea o funcție definită de utilizator pentru a combina numele pe baza ID-ului unic.

1. Luați următoarele date ca exemplu, trebuie să extrageți mai întâi numerele de identificare unice, vă rugăm să aplicați această formulă matrice: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Introduceți această formulă într-o celulă goală, de exemplu, D2, apoi apăsați Ctrl + Shift + Enter tastele împreună, vezi captura de ecran:

doc combina textul pe baza criteriilor 2

varful: În formula de mai sus, A2: A15 este lista de date din care doriți să extrageți valori unice, D1 este prima celulă a coloanei pe care doriți să scoateți rezultatul extragerii.

2. Și apoi trageți mânerul de umplere în jos pentru a extrage toate valorile unice până când se afișează goluri, vedeți captura de ecran:

doc combina textul pe baza criteriilor 3

3. În acest pas, ar trebui să creați un fișier Funcție definită de utilizator pentru a combina numele pe baza numerelor de identificare unice, țineți apăsat butonul ALT + F11 tastele și deschide fișierul Microsoft Visual Basic pentru aplicații fereastră.

4. Clic Insera > Moduleși lipiți următorul cod în Module Fereastră.

Cod VBA: concatenează text pe baza criteriilor

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. Apoi salvați și închideți acest cod, reveniți la foaia de lucru și introduceți această formulă în celula E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , vezi captura de ecran:

doc combina textul pe baza criteriilor 4

6. Apoi trageți mânerul de umplere în jos în celulele pe care doriți să le aplicați această formulă și toate numele corespunzătoare au fost combinate pe baza numerelor de identificare, a se vedea captura de ecran:

doc combina textul pe baza criteriilor 5

Sfat:

1. În formula de mai sus, A2: A15 sunt datele originale pe care doriți să le combinați, D2 este valoarea unică pe care ați extras-o și B2: B15 este coloana de nume pe care doriți să o combinați.

2. După cum puteți vedea, am combinat valorile care sunt separate prin virgulă, puteți utiliza orice alte caractere schimbând virgula „,” a formulei după cum aveți nevoie.


Dacă aveţi Kutools pentru Excel, Cu său Rânduri combinate avansate utilitar, puteți concatena rapid și convenabil baza de text pe criterii.

Kutools pentru Excel : cu mai mult de 300 de programe de completare Excel la îndemână, gratuit pentru a încerca fără limitări în 30 de zile.

După instalare Kutools pentru Excel, efectuați următorii pași:

1. Selectați intervalul de date pe care doriți să îl combinați pe baza unei coloane.

2. Clic Kutools > Merge & Split > Rânduri combinate avansate, vezi captura de ecran:

3. În Combinați rândurile pe baza coloanei caseta de dialog, faceți clic pe coloana ID și apoi faceți clic pe Cheia principala pentru a face această coloană drept coloană cheie pe care se bazează datele dvs. combinate, consultați captura de ecran:

doc combina textul pe baza criteriilor 7

4. Apoi faceți clic pe Nume si Prenume coloana pe care doriți să combinați valorile, apoi faceți clic pe Combina și alegeți un separator pentru datele combinate, vedeți captura de ecran:

doc combina textul pe baza criteriilor 8

5. După finalizarea acestor setări, faceți clic pe OK pentru a ieși din dialog, iar datele din coloana B au fost combinate împreună pe baza coloanei cheie A. Vedeți captura de ecran:

doc combina textul pe baza criteriilor 9

Cu această caracteristică, următoarea problemă va fi rezolvată cât mai curând posibil:

Cum să combinați mai multe rânduri într-un singur și să sumați duplicatele în Excel?

Descărcați și proba gratuită Kutools pentru Excel acum!


Kutools pentru Excel: cu mai mult de 300 de programe de completare Excel la îndemână, încercați fără limitări în 30 de zile. Descărcați și proba gratuită acum!

Cele mai bune instrumente de productivitate de birou

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp.  Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...

fila kte 201905


Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
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