Sisu
Kirjutasin juba sellest, kuidas saate kiiresti teksti mitmest lahtrist ühte liimida ja vastupidi, pikka tekstistringi komponentideks sõeluda. Nüüd vaatame lähemalt, kuid veidi keerulisemat ülesannet – kuidas liimida teksti mitmest lahtrist, kui teatud tingimus on täidetud.
Oletame, et meil on klientide andmebaas, kus üks ettevõtte nimi võib vastata mitmele erinevale selle töötajate meilile. Meie ülesandeks on koguda kõik aadressid firmanimede kaupa ja liita need (eraldatuna koma või semikooloniga), et koostada näiteks klientidele meililisti ehk saada väljundiks midagi sellist:
Teisisõnu vajame tööriista, mis liimib (linkib) teksti vastavalt tingimusele – funktsiooni analoogi SUMMESLI (SUMIF), aga teksti jaoks.
0. meetod. Valem
Mitte väga elegantne, kuid kõige lihtsam viis. Võite kirjutada lihtsa valemi, mis kontrollib, kas järgmisel real olev ettevõte erineb eelmisest. Kui see ei erine, siis liimige järgmine aadress, eraldades komaga. Kui see erineb, siis "lähtestame" kogunenud, alustades uuesti:
Selle lähenemisviisi puudused on ilmsed: kõigist saadud täiendava veeru lahtritest vajame iga ettevõtte jaoks ainult viimaseid (kollane). Kui loend on suur, peate nende kiireks valimiseks funktsiooni abil lisama veel ühe veeru DLSTR (LEN), kontrollides kogunenud stringide pikkust:
Nüüd saate need välja filtreerida ja kopeerida vajaliku aadressi liimimise edasiseks kasutamiseks.
Meetod 1. Ühe tingimuse järgi liimimise makrofunktsioon
Kui esialgne loend ei ole ettevõtte järgi sorteeritud, siis ülaltoodud lihtne valem ei tööta, kuid VBA-s saate hõlpsalt liikuda väikese kohandatud funktsiooniga. Avage Visual Basicu redaktor, vajutades kiirklahvi Alt + F11 või kasutades nuppu Visual Basic tab arendaja (Arendaja). Avanevas aknas sisestage menüü kaudu uus tühi moodul Sisesta – moodul ja kopeerige sinna meie funktsiooni tekst:
Funktsioon MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " liimimised ei ole üksteisega võrdsed – väljume veaga, kui SearchRange.Count <> TextRange.Count Siis MergeIf = CVERr(xlErrRef) Välju Funktsioonist End Kui 'käige läbi kõik lahtrid, kontrollige tingimust ja koguge tekst muutujasse OutText For i = 1 To SearchRange. Lahtrite arv, kui Otsinguvahemik. Lahtrid(i) meeldib tingimusel, siis OutText = OutText & TextRange.Cells(i) & Delimeter Järgmine i 'kuvan tulemused ilma viimase eraldajata MergeIf = Vasak(Väljutekst, Len(Väljutekst) - Len(Delimeeter)) Lõpp funktsiooni
Kui naasete nüüd Microsoft Excelisse, siis funktsioonide loendis (nupp fx valemiribal või vahekaardil Valemid – Sisesta funktsioon) on võimalik leida meie funktsioon Ühenda Kui kategoorias Kasutaja määratletud (kasutaja määratud). Funktsiooni argumendid on järgmised:
Meetod 2. Keerake tekst kokku ebatäpse tingimusega
Kui asendame esimese tähemärgi oma makro 13. real = ligikaudsele tikuoperaatorile nagu, siis on võimalik liimida algandmete ebatäpse sobitamise teel valikukriteeriumiga. Näiteks kui ettevõtte nime saab kirjutada erinevates variantides, siis saame neid kõiki kontrollida ja koguda ühe funktsiooniga:
Toetatud on standardsed metamärgid:
- tärn (*) – tähistab suvalist arvu märke (ka nende puudumist)
- küsimärk (?) – tähistab mis tahes üksikut tähemärki
- naelamärk (#) – tähistab mis tahes ühte numbrit (0-9)
Vaikimisi on Like-operaator tõstutundlik ehk mõistab näiteks “Orion” ja “orion” erinevate ettevõtetena. Suur- ja suurtähtede ignoreerimiseks saate Visual Basicu redaktoris lisada rea mooduli algusesse Valik Võrdle teksti, mis muudab Meeldib tõstutundlikuks.
Nii saate tingimuste kontrollimiseks koostada väga keerukaid maske, näiteks:
- ?1##??777RUS – kõigi 777 piirkonna numbrimärkide valik, alates 1
- LLC* – kõik ettevõtted, mille nimi algab LLC-ga
- ##7## – kõik tooted viiekohalise digitaalse koodiga, kus kolmas number on 7
- ?????? – kõik viietähelised nimed jne.
Meetod 3. Makrofunktsioon teksti liimimiseks kahel tingimusel
Töös võib tekkida probleem, kui peate teksti linkima rohkem kui ühe tingimuse. Näiteks kujutame ette, et meie eelmises tabelis lisati veel üks veerg linnaga ja liimimine peaks toimuma mitte ainult antud ettevõtte, vaid ka linna kohta. Sel juhul tuleb meie funktsiooni veidi moderniseerida, lisades sellele veel ühe vahemiku kontrolli:
Funktsioon MergeIfs(Tekstivahemik vahemikuna, otsinguvahemik1 vahemikuna, tingimus1 stringina, otsinguvahemik2 vahemikuna, tingimus2 stringina) Dim Delimeter As String, i As Long Delimeter = ", " 'eraldusmärgid (saab asendada tühikuga või ; jne). e.) 'kui valideerimis- ja liimimisvahemikud ei ole üksteisega võrdsed, väljuge veaga If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVERr(xlErrRef) Välju Funktsioonist End If 'käige läbi kõik lahtrid, kontrollige kõiki tingimusi ja koguge tekst muutujasse OutText For i = 1 To SearchRange1.Cells.Count Kui SearchRange1.Cells(i) = Tingimus1 Ja SearchRange2.Cells(i) = Tingimus2 Siis OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'kuvan tulemused ilma viimase eraldajata MergeIfs = Vasak(Väljutekst, Len(Väljutekst) - Len(Delimeeter)) Lõppfunktsioon
Seda rakendatakse täpselt samamoodi – ainult argumente tuleb nüüd rohkem täpsustada:
4. meetod. Rühmitamine ja liimimine Power Querys
Saate probleemi lahendada ilma VBA-s programmeerimata, kui kasutate tasuta Power Query lisandmoodulit. Excel 2010-2013 jaoks saab selle alla laadida siit ja Excel 2016-s on see juba vaikimisi sisse ehitatud. Toimingute jada on järgmine:
Power Query ei tea, kuidas tavaliste tabelitega töötada, nii et esimene samm on muuta meie tabel nutikaks. Selleks valige see ja vajutage kombinatsiooni Ctrl+T või valige vahekaardilt Avaleht – vorminda tabelina (Avaleht — vorminda tabelina). Vahekaardil, mis seejärel kuvatakse Ehitaja (Kujundus) saate määrata tabeli nime (ma jätsin standardi Tabel 1):
Nüüd laadime oma tabeli Power Query lisandmoodulisse. Selleks vahekaardil kuupäev (kui teil on Excel 2016) või vahekaardil Power Query (kui teil on Excel 2010–2013) Laualt (Andmed – tabelist):
Avanevas päringuredaktori aknas valige veerg, klõpsates päisel Ettevõte ja vajutage ülalolevat nuppu Grupp (Rühmita). Sisestage rühmitusse uue veeru nimi ja toimingu tüüp – Kõik read (Kõik read):
Klõpsake nuppu OK ja saame iga ettevõtte jaoks rühmitatud väärtuste minitabeli. Tabelite sisu on selgelt nähtav, kui klõpsate vasaku klahviga lahtrite valgel taustal (mitte tekstil!) tekkinud veerus:
Nüüd lisame veel ühe veeru, kuhu liimime funktsiooni abil iga minitabeli veergude aadressi sisu, eraldades need komadega. Selleks vahekaardil Lisage veerg vajutame Kohandatud veerg (Lisa veerg – kohandatud veerg) ja ilmuvas aknas sisestage Power Querysse sisseehitatud M-keeles uue veeru nimi ja sidumisvalem:
Pange tähele, et kõik M-funktsioonid on tõstutundlikud (erinevalt Excelist). Pärast klõpsamist OK saame uue veeru liimitud aadressidega:
Jääb eemaldada niigi mittevajalik veerg Tabeliaadressid (paremklõps pealkirjal) Kustuta veerg) ja laadige tulemused lehele, klõpsates vahekaardil Avaleht — sulgege ja laadige alla (Kodu – sulgege ja laadige):
Oluline nüanss: erinevalt eelmistest meetoditest (funktsioonidest) ei värskendata Power Query tabeleid automaatselt. Kui tulevikus tehakse lähteandmetes muudatusi, peate paremklõpsama tulemuste tabelis suvalises kohas ja valima käsu Värskenda ja salvesta (Värskenda).
- Kuidas jagada pikka tekstistringi osadeks
- Mitmed viisid teksti liimimiseks erinevatest lahtritest üheks
- Operaatori Like kasutamine teksti testimiseks maskiga