Contents [show]
Ek het reeds geskryf hoe jy teks van verskeie selle vinnig in een kan plak en omgekeerd 'n lang teksstring in komponente kan ontleed. Kom ons kyk nou na 'n noue, maar effens meer komplekse taak - hoe om teks uit verskeie selle te plak wanneer 'n sekere gespesifiseerde voorwaarde nagekom word.
Kom ons sê dat ons 'n databasis van kliënte het, waar een maatskappy se naam kan ooreenstem met verskeie verskillende e-posse van sy werknemers. Ons taak is om al die adresse volgens maatskappyname te versamel en hulle aaneen te koppel (geskei deur kommas of kommapunte) om byvoorbeeld 'n poslys vir kliënte te maak, maw uitset iets soos:
Met ander woorde, ons het 'n instrument nodig wat die teks sal plak (skakel) volgens die toestand - 'n analoog van die funksie SUMMESLI (SUMIF), maar vir teks.
Metode 0. Formule
Nie baie elegant nie, maar die maklikste manier. Jy kan 'n eenvoudige formule skryf wat sal kyk of die maatskappy in die volgende ry van die vorige een verskil. As dit nie verskil nie, plak dan die volgende adres vas, geskei deur 'n komma. As dit verskil, "terugstel" ons die opgehoopte, en begin weer:
Die nadele van hierdie benadering is voor die hand liggend: uit al die selle van die bykomende kolom wat verkry is, benodig ons net die laastes vir elke maatskappy (geel). As die lys groot is, moet u nog 'n kolom met die funksie byvoeg om dit vinnig te kan kies DLSTR (LEN), kontroleer die lengte van die opgehoopte snare:
Nou kan jy die een uitfiltreer en die nodige adresgom kopieer vir verdere gebruik.
Metode 1. Makrofunksie van gom deur een voorwaarde
As die oorspronklike lys nie volgens maatskappy gesorteer is nie, werk die bogenoemde eenvoudige formule nie, maar jy kan maklik rondkom met 'n klein pasgemaakte funksie in VBA. Maak die Visual Basic Editor oop deur 'n sleutelbordkortpad te druk Alt + F11 of deur die knoppie te gebruik Visual Basic tab ontwikkelaar (Ontwikkelaar). Voeg 'n nuwe leë module deur die kieslys in die venster wat oopmaak Voeg in – Module en kopieer die teks van ons funksie daarheen:
Funksie MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings is nie gelyk aan mekaar nie - ons gaan uit met 'n fout As SearchRange.Count <> TextRange.Count Dan MergeIf = CVErr(xlErrRef) Verlaat Funksie Einde As 'deur al die selle gaan, gaan die toestand na en versamel die teks in die veranderlike OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Volgende i 'vertoon resultate sonder laaste delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funksie
As jy nou terugkeer na Microsoft Excel, dan in die lys van funksies (knoppie fx in die formulebalk of oortjie Formules – Voeg funksie in) sal dit moontlik wees om ons funksie te vind MergeIf in kategorie Gebruiker gedefinieerd (Gebruiker gedefinieerd). Die argumente vir die funksie is soos volg:
Metode 2. Koppel teks volgens onpresiese toestand
As ons die eerste karakter in die 13de reël van ons makro vervang = aan die benaderde wedstrydoperateur soos, dan sal dit moontlik wees om gom uit te voer deur 'n onpresiese passing van die aanvanklike data met die seleksiekriterium. Byvoorbeeld, as die maatskappy se naam in verskillende variante geskryf kan word, kan ons dit alles met een funksie nagaan en versamel:
Standaard jokertekens word ondersteun:
- asterisk (*) – dui enige aantal karakters aan (insluitend hul afwesigheid)
- vraagteken (?) – staan vir enige enkele karakter
- pondteken (#) – staan vir enige een syfer (0-9)
By verstek is die Like-operateur hooflettergevoelig, dws verstaan byvoorbeeld “Orion” en “orion” as verskillende maatskappye. Om geval te ignoreer, kan jy die reël heel aan die begin van die module in die Visual Basic-redigeerder byvoeg Opsie Vergelyk teks, wat Like sal verander om hoofletteronsensitief te wees.
Op hierdie manier kan u baie komplekse maskers saamstel om toestande na te gaan, byvoorbeeld:
- ?1##??777RUS – seleksie van alle nommerplate van die 777-streek, begin met 1
- LLC* – alle maatskappye wie se naam met LLC begin
- ##7## – alle produkte met 'n vyf-syfer digitale kode, waar die derde syfer 7 is
- ???? – alle name van vyf letters, ens.
Metode 3. Makrofunksie om teks onder twee toestande te plak
In die werk kan daar 'n probleem wees wanneer jy die teks meer as een voorwaarde moet koppel. Kom ons stel ons byvoorbeeld voor dat daar in ons vorige tabel nog een kolom met die stad bygevoeg is, en gom moet nie net vir 'n gegewe maatskappy uitgevoer word nie, maar ook vir 'n gegewe stad. In hierdie geval sal ons funksie effens gemoderniseer moet word deur nog 'n reekskontrole daarby te voeg:
Funksie MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'afgrenskarakters (kan vervang word met spasie of ; ens.) e.) 'as die validerings- en gomreekse nie gelyk aan mekaar is nie, gaan uit met 'n fout As SearchRange1.Count <> TextRange.Count Of SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Verlaat funksie Einde If 'gaan deur alle selle, kontroleer alle toestande en versamel die teks in die veranderlike OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'vertoon resultate sonder laaste delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Eindfunksie
Dit sal op presies dieselfde manier toegepas word – net argumente moet nou meer gespesifiseer word:
Metode 4. Groepering en gom in Power Query
Jy kan die probleem oplos sonder om in VBA te programmeer, as jy die gratis Power Query-byvoeging gebruik. Vir Excel 2010-2013 kan dit hier afgelaai word, en in Excel 2016 is dit reeds by verstek ingebou. Die volgorde van aksies sal soos volg wees:
Power Query weet nie hoe om met gewone tabelle te werk nie, so die eerste stap is om ons tabel in 'n "slim" een te verander. Om dit te doen, kies dit en druk die kombinasie Ctrl+T of kies op die oortjie Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel). Op die blad wat dan verskyn konstruktor (Ontwerp) jy kan die tabelnaam instel (ek het die standaard verlaat Tabel 1):
Kom ons laai nou ons tabel in die Power Query-byvoeging. Om dit te doen, op die blad data (as jy Excel 2016 het) of op die Power Query-oortjie (as jy Excel 2010-2013 het) klik Van die tafel af (Data – Uit tabel):
In die navraagredigeerdervenster wat oopmaak, kies die kolom deur op die opskrif te klik maatskappy en druk die knoppie hierbo groep (Groepeer volgens). Voer die naam van die nuwe kolom en die tipe bewerking in die groepering in – Alle lyne (Alle rye):
Klik OK en ons kry 'n mini-tabel van gegroepeerde waardes vir elke maatskappy. Die inhoud van die tabelle is duidelik sigbaar as jy links-kliek op die wit agtergrond van die selle (nie op die teks nie!) in die gevolglike kolom:
Kom ons voeg nou nog een kolom by, waar ons, met behulp van die funksie, die inhoud van die adreskolomme in elk van die mini-tabelle vasplak, geskei deur kommas. Om dit te doen, op die blad Voeg kolom by ons druk Gepasmaakte kolom (Voeg kolom by – Pasgemaakte kolom) en in die venster wat verskyn, voer die naam van die nuwe kolom en die koppelingsformule in in die M-taal wat in Power Query ingebou is:
Let daarop dat alle M-funksies hooflettergevoelig is (anders as Excel). Nadat u op OK ons kry 'n nuwe kolom met geplakte adresse:
Dit bly om die reeds onnodige kolom te verwyder Tafeladresse (regskliek op titel) Vee kolom uit) en laai die resultate op na die blad deur op die oortjie te klik Tuis — Maak toe en laai af (Tuis — Maak toe en laai):
Belangrike nuanse: Anders as die vorige metodes (funksies), word tabelle van Power Query nie outomaties opgedateer nie. As daar in die toekoms enige veranderinge in die brondata sal wees, moet u enige plek in die resultatetabel regsklik en die opdrag kies Dateer op en stoor (Verfris).
- Hoe om 'n lang teksstring in dele te verdeel
- Verskeie maniere om teks uit verskillende selle in een te plak
- Gebruik die Like-operateur om teks teen 'n masker te toets