Cuprins
Am scris deja despre cum puteți lipi rapid text din mai multe celule într-una singură și, dimpotrivă, să analizați un șir de text lung în componente. Acum să ne uităm la o sarcină apropiată, dar puțin mai complexă - cum să lipiți textul din mai multe celule atunci când este îndeplinită o anumită condiție specificată.
Să presupunem că avem o bază de date de clienți, unde un nume de companie poate corespunde mai multor e-mailuri diferite ale angajaților săi. Sarcina noastră este să colectăm toate adresele după numele companiilor și să le concatenăm (separate prin virgule sau punct și virgulă) pentru a face, de exemplu, o listă de corespondență pentru clienți, adică obținem ceva de genul:
Cu alte cuvinte, avem nevoie de un instrument care va lipi (lega) textul în funcție de condiție – un analog al funcției SUMMESLI (SUMIF), dar pentru text.
Metoda 0. Formula
Nu foarte elegant, dar cel mai simplu mod. Puteți scrie o formulă simplă care va verifica dacă compania din rândul următor diferă de cea anterioară. Dacă nu diferă, atunci lipiți următoarea adresă separată prin virgulă. Dacă diferă, atunci „resetăm” acumulatul, pornind din nou:
Dezavantajele acestei abordări sunt evidente: din toate celulele coloanei suplimentare obținute, avem nevoie doar de ultimele pentru fiecare companie (galben). Dacă lista este mare, atunci pentru a le selecta rapid, va trebui să adăugați o altă coloană folosind funcția DLSTR (LEN), verificând lungimea șirurilor acumulate:
Acum le puteți filtra pe cele și copia lipirea adresei necesare pentru utilizare ulterioară.
Metoda 1. Macrofuncția lipirii printr-o singură condiție
Dacă lista originală nu este sortată în funcție de companie, atunci formula simplă de mai sus nu funcționează, dar vă puteți descurca cu ușurință cu o mică funcție personalizată în VBA. Deschideți Visual Basic Editor apăsând pe o comandă rapidă de la tastatură Alt + F11 sau folosind butonul Visual Basic fila dezvoltator (Dezvoltator). În fereastra care se deschide, introduceți un nou modul gol prin meniu Inserare – Modul și copiați textul funcției noastre acolo:
Funcția MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " lipirile nu sunt egale între ele - ieșim cu o eroare If SearchRange.Count <> TextRange.Count Apoi MergeIf = CVErr(xlErrRef) Exit Function End Dacă „treceți prin toate celulele, verificați condiția și colectați textul în variabila OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Atunci OutText = OutText & TextRange.Cells(i) & Delimeter În continuare afișez rezultatele fără ultimul delimitator MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funcţie
Dacă reveniți acum la Microsoft Excel, atunci în lista de funcții (buton fx în bara de formule sau fila Formule – Funcția de inserare) va fi posibil să găsim funcția noastră MergeIf în categoria Definit de utilizator (Definit de utilizator). Argumentele funcției sunt următoarele:
Metoda 2. Concatenează textul prin condiție inexactă
Dacă înlocuim primul caracter din a 13-a linie a macrocomenzii noastre = la operatorul de potrivire aproximativă Aprecieri, atunci va fi posibilă efectuarea lipirii printr-o potrivire inexactă a datelor inițiale cu criteriul de selecție. De exemplu, dacă numele companiei poate fi scris în diferite variante, atunci le putem verifica și colecta pe toate cu o singură funcție:
Sunt acceptate metacaracterele standard:
- asterisc (*) – indică orice număr de caractere (inclusiv absența acestora)
- semn de întrebare (?) – reprezintă orice caracter individual
- semnul lire sterline (#) – reprezintă orice cifră (0-9)
În mod implicit, operatorul Like diferențiază majuscule și minuscule, adică înțelege, de exemplu, „Orion” și „orion” ca companii diferite. Pentru a ignora majuscule și minuscule, puteți adăuga linia chiar la începutul modulului în editorul Visual Basic Opțiunea Compara text, care va comuta Like pentru a nu ține seama de majuscule și minuscule.
În acest fel, puteți compune măști foarte complexe pentru verificarea condițiilor, de exemplu:
- ?1##??777RUS – selecția tuturor plăcuțelor de înmatriculare din regiunea 777, începând cu 1
- SRL* – toate companiile al căror nume începe cu SRL
- ##7## – toate produsele cu un cod digital din cinci cifre, unde a treia cifră este 7
- ?????? – toate numele de cinci litere etc.
Metoda 3. Funcția macro pentru lipirea textului în două condiții
În lucrare, poate exista o problemă atunci când trebuie să legați textul mai mult de o condiție. De exemplu, să ne imaginăm că în tabelul nostru anterior a mai fost adăugată o coloană cu orașul, iar lipirea ar trebui efectuată nu numai pentru o anumită companie, ci și pentru un anumit oraș. În acest caz, funcția noastră va trebui să fie ușor modernizată prin adăugarea unei alte verificări de interval:
Funcția MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " ' caractere delimitare (pot fi înlocuite cu spațiu sau ; etc.) e.) „dacă intervalele de validare și de lipire nu sunt egale între ele, ieșiți cu o eroare If SearchRange1.Count <> TextRange.Count Sau SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If „Parcurgeți toate celulele, verificați toate condițiile și colectați textul în variabila OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 și SearchRange2.Cells(i) = Condition2 Apoi OutText = OutText & TextRange.Cells(i) & Delimeter End If Next Afișez rezultatele fără ultimul delimitator MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Se va aplica exact în același mod – doar argumentele trebuie specificate mai mult:
Metoda 4. Gruparea și lipirea în Power Query
Puteți rezolva problema fără programare în VBA, dacă utilizați programul de completare gratuit Power Query. Pentru Excel 2010-2013 poate fi descărcat de aici, iar în Excel 2016 este deja încorporat implicit. Secvența acțiunilor va fi următoarea:
Power Query nu știe cum să lucreze cu tabele obișnuite, așa că primul pas este să ne transformăm tabelul într-unul „inteligent”. Pentru a face acest lucru, selectați-l și apăsați combinația Ctrl+T sau selectați din filă Acasă – Formatați ca tabel (Acasă — Format ca tabel). Pe fila care apare apoi Constructor (Proiecta) puteți seta numele tabelului (am părăsit standardul Tabelul 1):
Acum să încărcăm tabelul nostru în programul de completare Power Query. Pentru a face acest lucru, pe fila Date (dacă aveți Excel 2016) sau în fila Power Query (dacă aveți Excel 2010-2013) faceți clic De la masă (Date - Din tabel):
În fereastra editorului de interogări care se deschide, selectați coloana făcând clic pe antet Despre și apăsați butonul de mai sus grup (A se grupa cu). Introduceți numele noii coloane și tipul operației în grupare - Toate liniile (Toate rândurile):
Faceți clic pe OK și obținem un mini-tabel de valori grupate pentru fiecare companie. Conținutul tabelelor este clar vizibil dacă dați clic stânga pe fundalul alb al celulelor (nu pe text!) în coloana rezultată:
Acum să mai adăugăm o coloană, unde, folosind funcția, lipim conținutul coloanelor Adresă în fiecare dintre mini-tabelele, separate prin virgule. Pentru a face acest lucru, pe fila Adăugați coloana apăsăm Coloană personalizată (Adăugați coloană — coloană personalizată) iar în fereastra care apare, introduceți numele noii coloane și formula de cuplare în limbajul M încorporat în Power Query:
Rețineți că toate funcțiile M sunt sensibile la majuscule și minuscule (spre deosebire de Excel). După ce faceți clic pe OK obținem o nouă coloană cu adrese lipite:
Rămâne să eliminați coloana deja inutilă TabelAddresses (clic dreapta pe titlu) Ștergeți coloana) și încărcați rezultatele în foaie făcând clic pe filă Acasă — Închideți și descărcați (Acasă — Închideți și încărcați):
Nuanță importantă: Spre deosebire de metodele (funcții) anterioare, tabelele din Power Query nu sunt actualizate automat. Dacă în viitor vor exista modificări în datele sursă, atunci va trebui să faceți clic dreapta oriunde în tabelul cu rezultate și să selectați comanda Actualizați și salvați (Reîmprospăta).
- Cum să împărțiți un șir de text lung în părți
- Mai multe moduri de a lipi textul din celule diferite într-una singură
- Utilizarea operatorului Like pentru a testa textul pe o mască