Lihtne esmapilgul probleem, millel pole ilmselge lahendus: eraldage tekstirealt viimane sõna. Noh, või üldjuhul viimane fragment, eraldatuna etteantud eraldusmärgiga (tühik, koma jne). Teisisõnu on vaja rakendada pöördotsingut (otsast algusesse) stringis antud märk ja seejärel eraldage kõik sellest paremal olevad märgid.
Vaatame traditsiooniliselt mitmeid valikuvõimalusi: valemid, makrod ja Power Query kaudu.
Meetod 1. Valemid
Valemi olemuse ja mehaanika mõistmise hõlbustamiseks alustame veidi kaugemalt. Esiteks suurendame oma lähteteksti sõnadevaheliste tühikute arvu näiteks 20 ühikuni. Seda saate teha asendamise funktsiooniga. ASENDA (ASENDAJA) ja antud tähemärgi kordamise funktsioon N korda – KORDA (REPT):
Nüüd lõikame funktsiooni abil saadud teksti lõpust ära 20 tähemärki PAREM (PAREM):
Läheb soojemaks, eks? Funktsiooni abil tuleb eemaldada lisatühikud TRIM (TRIM) ja probleem laheneb:
Ingliskeelses versioonis näeb meie valem välja järgmine:
=TRIM(RIGHT(ASENDAJA(A1;» «;REPT(» «;20));20))
Loodan, et on selge, et põhimõtteliselt ei ole vaja sisestada täpselt 20 tühikut – sobib suvaline arv, kui see on suurem kui lähteteksti pikima sõna pikkus.
Ja kui lähteteksti tuleb jagada mitte tühikuga, vaid mõne muu eraldajaga (näiteks komaga), siis tuleb meie valemit veidi parandada:
2. meetod. Makrofunktsioon
Tekstist viimase sõna või fragmendi eraldamise ülesannet saab lahendada ka makrode abil, nimelt kirjutades Visual Basicus pöördotsingu funktsiooni, mis teeb seda, mida me vajame – otsib antud alamstringi stringist vastupidises suunas – alates lõpust algusesse.
Vajutage klaviatuuri otseteed muu+F11 või nuppu Visual Basic tab arendaja (Arendaja)makroredaktori avamiseks. Seejärel lisage menüü kaudu uus moodul Sisesta – moodul ja kopeerige sinna järgmine kood:
Funktsioon LastWord(txt stringina, valikuline delim stringina = " ", valikuline n täisarvuna = 1) stringina arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) Lõppfunktsioon
Nüüd saate töövihiku salvestada (makrotoega vormingus!) ja kasutada loodud funktsiooni järgmises süntaksis:
=LastWord(txt ; delim ; n)
kus
- txt – lähtetekstiga lahter
- piiritlema - eraldaja (vaikimisi - tühik)
- n – milline sõna tuleks lõpust välja tõmmata (vaikimisi – lõpust esimene)
Kui lähtetekstis tulevikus tehakse muudatusi, arvutatakse meie makrofunktsioon käigu pealt ümber, nagu iga standardne Exceli lehefunktsioon.
3. meetod. Power Query
Toite päring on Microsofti tasuta lisandmoodul andmete importimiseks Excelisse peaaegu igast allikast ja seejärel allalaaditud andmete mis tahes vormis muutmiseks. Selle lisandmooduli võimsus ja lahedus on nii suured, et Microsoft on kõik selle funktsioonid vaikimisi Excel 2016 sisse ehitanud. Exceli 2010–2013 jaoks saab Power Query tasuta alla laadida siit.
Meie ülesanne eraldada Power Query abil viimane sõna või fragment antud eraldaja kaudu on väga lihtne.
Esiteks muudame oma andmetabeli klaviatuuri otseteede abil nutikaks tabeliks. Ctrl+T või käske Avaleht – vorminda tabelina (Avaleht — vorminda tabelina):
Seejärel laadime käsu abil loodud “nutika tabeli” Power Querysse Tabelist/vahemikust (Tabelist/vahemikust) tab kuupäev (kui teil on Excel 2016) või vahekaardil Toite päring (kui teil on Excel 2010–2013):
Avanevas päringuredaktori aknas vahekaardil Transformation (teisendus) vali meeskond Tükeldatud veerg – eraldaja järgi (Veerg jagatud – eraldaja järgi) ja seejärel jääb alles määrata eraldusmärk ja valida suvand Parempoolne eraldajaei lõika mitte kõiki sõnu, vaid ainult viimast:
Pärast klõpsamist nupul OK viimane sõna eraldatakse uude veergu. Ebavajaliku esimese veeru saab eemaldada, paremklõpsates selle päisel ja valides eemalda (Kustuta). Samuti saate tabeli päises ülejäänud veeru ümber nimetada.
Tulemused saab käsu abil lehele tagasi laadida Avaleht — sulge ja laadi — sulge ja laadi… (Avaleht – sulge ja laadi – sulge ja laadi…):
Ja selle tulemusena saame:
Niimoodi – odav ja rõõmsameelne, ilma valemite ja makrodeta, peaaegu ilma klaviatuuri puudutamata 🙂
Kui algne loend tulevikus muutub, piisab paremklõpsamisest või kiirklahvi kasutamisest Ctrl+muu+F5 värskendage meie taotlust.
- Kleepuva teksti jagamine veergudeks
- Teksti sõelumine ja parsimine regulaaravaldistega
- Esimeste sõnade väljavõtmine tekstist funktsiooniga SUBSTITUTE