Note: The other languages of the website are Google-translated. Back to English

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 Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

  • reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Peste 300 de funcții puternice. Suportă Office / Excel 2007-2021 și 365. Acceptă toate limbile. Implementare ușoară în întreprinderea sau organizația dvs. Funcții complete Probă gratuită de 30 de zile. Garanție de returnare a banilor de 60 de zile.
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!
fundul officetab
Comentarii (38)
Încă nu există evaluări. Fii primul care evaluează!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Aceasta este o soluție excelentă (cod VBA) și mi-a răspuns cerințelor în câteva minute. Voi trimite site-ul dvs. către alții și voi vizita pentru tot ce am nevoie în continuare.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc pentru acest cod. Era EXACT ceea ce aveam nevoie. Mi-ai scutit de mult efort, mulțumesc mult.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Multumesc pentru postare, este exact ceea ce caut. Se pare că nu salvez codul vba corect. Primesc un mesaj de eroare despre numele ambiguu găsit. Orice sugestii sau pas cu pas despre pasul VBA al acestui proiect? Mulțumiri
Acest comentariu a fost redus la minimum de moderatorul de pe site
Va putea acest instrument să gestioneze combinații sensibile cu majuscule și minuscule, cum ar fi jABC 123 abc 345 ABc 678 ABC 912
Acest comentariu a fost redus la minimum de moderatorul de pe site
Acest cod VBA mi-a salvat ziua. Mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Caut o modalitate de a folosi o variantă a acestui cod pentru a crea o listă de variante bazată pe varianta principală. Folosind datele dvs. exemplu, ar trebui să combin coloanele A și B în identificatori unici și apoi să concatenez acești identificatori la fiecare rând pe baza valorii din coloana A, excluzând valoarea din combinația pentru acel rând și restul în sortare alfa comandă: Masterat lista id nume id varianta CN20150012 Lucy CN20150012-Lucy CN20150012-Andy CN20150012-Monica CN20150012-Phiby US20150011 Tommas US20150011-Tommas US20150011-Rose CN20150012 Monica CN20150012-Monica CN20150012-Andy CN20150012-Lucy CN20150012-Phiby CN20150012 Phiby CN20150012-Phiby CN20150012 -Andy CN20150012-Lucy CN20150012-Monica US20150011 Rose US20150011-Rose US20150011-Tommas UK20150014 Peter UK20150014-Peter UK20150014-Anith UK20150014-Kristi UK20150014-Libin JP20150010 Ramon JP20150010-Ramon JP20150010-Brenda JP20150010-James UK20150014 Libin UK20150014-Libin UK20150014-Anith UK20150014 -Kristi UK20150014-Peter UK20150014 Anith UK20150014-Anith UK20150014-Kristi UK20150014-Libin UK20150014-Peter JP20150010 James JP20150010 20150010 James JP20150010-Brenda JP20150010-James JP20150012-Matus CN20150012 Andy CN20150012-Andy CN20150012-Lucy CN20150012-Monica CN20150014-Phiby UK20150014 Matus UK20150010-Matus JP20150010-Brenda JP20150014-James UK20150014 Kristi UK20150014-Kristi UK20150014-Anith UK20150014-Libin UK20150010- Peter JP20150010 Brenda JP20150010-Brenda JP20150010-James JP1000-Ramon Am o coală cu peste 4 de rânduri, fiecare articol vine cu până la XNUMX variante. Încercarea de a face acest lucru manual este imposibil, dar nu pot găsi o soluție care să se potrivească nevoilor mele.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Luând mai mult timp pentru actualizarea aceleiași formule concatenateif(). Am 5000 de rânduri. și au mai mult de 2 ore acum este încă în curs de actualizare :( vreo rezoluție care să o facă să funcționeze rapid?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Explicat în detaliu și ușor de înțeles, m-a ajutat cu adevărat atunci când am fost blocat în exact aceeași situație.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Extrem de util și frumos explicat
Acest comentariu a fost redus la minimum de moderatorul de pe site
Minunat, mulțumesc! Am folosit soluția VBA și a funcționat grozav.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Grozav!!! Mulțumesc foarte mult!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Nu funcționează pentru intervalul de date mari. Am descoperit că intervalul de lucru este doar până la A2:A362. Am fi recunoscători dacă împărtășiți soluția pentru gama de date mari precum A2:A200000 .... Vă mulțumim
Acest comentariu a fost redus la minimum de moderatorul de pe site
Funcționează grozav doar lent. O fac cu 27 de linii de text în Excel, doar porniți-l pentru o preparare și lăsați-l să ruleze
Acest comentariu a fost redus la minimum de moderatorul de pe site
Nu funcționează pentru intervalul de date mari. Am descoperit că intervalul de date de lucru este de până la A2:A362. Am fi recunoscători dacă împărtășiți soluția pentru a acoperi o gamă mai largă de date, cum ar fi A2:A200000 ..... Vă mulțumim
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut! concactenateif este exact ceea ce căutam. Dar, din păcate, nu se poate face să funcționeze. Primește întotdeauna o eroare de compilare: eroare de sintaxă. Vreo idee? În trecut, cu unele module VBA importate, am observat că a trebuit să înlocuiesc "," cu ";" ca și în PC-ul meu, poate din cauza setărilor mele regionale, doar așa funcționează. Folosește cu aviditate sumif-urile încorporate etc. Dar nu pot înțelege unde greșesc pe acesta. O altă posibilitate care îmi vine în minte este faptul că în office 365, „concat” înlocuiește „concactenate”. Puteti ajuta va rog? Mulțumesc anticipat, Yash
Acest comentariu a fost redus la minimum de moderatorul de pe site
Codul folosește niște spații care nu se rup pentru indentare, acestea trec în Excel2016. Greu de descoperit o eroare invizibilă..
Acest comentariu a fost redus la minimum de moderatorul de pe site
Am avut o problemă după ce am lipit acest cod în Excel 2016 - conține spații neregulate (poate spații nedespărțitoare?) care scot la iveală erori de sintaxă care nu sunt evidente, indiferent cât de atent te uiți, deoarece sunt invizibile! Spațiile de indentare sunt problema. Lipiți codul în Word și activați caracterele ascunse pentru a le vedea.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Wow!! Geniu! A mers perfect! Sunt spații care arată ca un caracter diferit. Multumesc mult Dave! Te întrebi cum ți-a venit ideea! De asemenea, mă întreb cum funcționează pentru alți peeps... Oricum, mulțumesc din nou!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Există vreo modalitate de a face asta pe Mac????
Este exact ceea ce am nevoie - vă rog să-mi spuneți (sau dacă vreun software mac pe care îl știți). Mersi
Acest comentariu a fost redus la minimum de moderatorul de pe site
Există o modalitate de a aplica această funcție CONCATENATEIF într-o foaie separată? Funcționează când îl pun în aceeași foaie ca datele de intrare, dar am nevoie de ambele tabele în foi diferite și nu funcționează.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Da, ceea ce vrei să faci este să adaugi funcția la un modul. Accesați editorul VBA, faceți clic dreapta pe „VBAProject” în Project Explorer, mouse-ul peste elementul de meniu „Insert” și în acel submeniu alegeți „Module”. Orice funcții pe care le introduceți acolo vor fi utilizate pe orice foaie din registrul de lucru.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut băieți, am primit o eroare #NAME? când aplic formulele CONCATENATEIF în fișierul excel după ce am setat codul VBA pentru asta, m-ar putea ajuta cineva să o rezolv, mulțumesc mult
Acest comentariu a fost redus la minimum de moderatorul de pe site
Atat de usor, multumesc :)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Este posibil să înlocuiți separatorul de virgulă cu o întrerupere de linie, adică char(10)? Mulţumesc mult.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună David,

Pentru a combina celulele cu întrerupere de linie, următoarea funcție definită de utilizator vă poate ajuta.

Funcția ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult ca șir
La data de eroare CV următoare
Dacă CriteriaRange.Count <> ConcatenateRange.Count Atunci
ConcatenateIf = CVErr(xlErrRef)
Funcția de ieșire
Final, dacă
Pentru I = 1 To CriteriaRange.Count
Dacă CriteriaRange.Cells(I).Value = Condiție Atunci
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
Final, dacă
Apoi eu
Dacă xResult <> "" Atunci
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
Final, dacă
ConcatenateIf_LineBreak = xResult
Funcția de ieșire
Sfârşit Funcţia

După ce lipiți acest cod, aplicați această formulă: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

După ce obțineți rezultatele cu această formulă, ar trebui să faceți clic pe Wrap Text pentru a obține rezultatele corecte de care aveți nevoie.
Acest comentariu a fost redus la minimum de moderatorul de pe site
mulțumesc foarte mult! Acest lucru a fost atât de simplu și a ajutat foarte mult!!
Nu există comentarii postate aici încă
Încărcați mai
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate

Urmărește-ne

Copyright © 2009 - www.extendoffice.com. | Toate drepturile rezervate. Cu sprijinul ExtendOffice. | Harta site-ului
Microsoft și sigla Office sunt mărci comerciale sau mărci comerciale înregistrate ale Microsoft Corporation în Statele Unite și / sau în alte țări.
Protejat de Sectigo SSL