Sisu
Kui olete regulaaravaldistega vähemalt veidi kursis, ei pea te neid reklaamima. Kui sa ei ole päris teemas, siis regulaaravaldised (Regular Expressions = RegExp = “regexps” = “regulars”) on keel, kus erimärkide ja reeglite abil otsitakse tekstist vajalikud alamstringid, need ekstraheeritakse või asendada muu tekstiga. See on väga võimas ja ilus tööriist, mis on suurusjärgus parem kui kõik muud tekstiga töötamise viisid.
Olen juba üksikasjalikult ja hunniku elust toodud näidetega kirjeldanud, kuidas saab lihtsate makrode abil Excelisse regulaaravaldiste toe lisada – kui te pole seda artiklit lugenud, siis soovitan enne jätkamist kindlasti läbi lugeda. Avastate palju uut, garanteerin 🙂
Lahtiseks jääb aga küsimus – kuidas lisada Power Querysse regulaaravaldiste kasutamise võimalus? Power Query on muidugi iseenesest hea ja suudab tekstiga palju ära teha (lõigata, liimida, puhastada jne), aga kui selle regulaaravaldiste jõuga ületada, oleks see lihtsalt pomm.
Kahjuks pole Power Querys RegExpsiga töötamiseks sisseehitatud funktsioone ning ametlik Microsofti abi ja tehniline tugi vastavad sellele küsimusele eitavalt. Sellest piirangust saab siiski mööda minna 🙂
Meetodi olemus
Põhiideed on lihtne häbistada.
Sisseehitatud Power Query võimaluste loendis on funktsioon Veebileht. Selle funktsiooni kirjeldus Microsofti ametlikul abisaidil on äärmiselt lühike:
Tõlgituna oleks see järgmine: "Tagastab HTML-dokumendi sisu, mis on jaotatud selle komponentstruktuurideks, samuti kogu dokumendi ja selle keha esituse pärast siltide eemaldamist." Nii-nii kirjeldus, ausalt.
Tavaliselt kasutatakse seda funktsiooni veebist andmete importimisel ja see asendatakse automaatselt, näiteks kui valime vahekaardil kuupäev käsk Internetist (Andmed – veebist). Anname funktsioonile argumendiks veebilehe ja see tagastab meile oma sisu tabelite kujul, olles eelnevalt kõik sildid kustutanud.
Mida spikker EI ütle, et lisaks HTML-i märgistuskeelele funktsioon Veebileht toetab JavaScripti skripte, mis on nüüdseks Interneti veebisaitidel üldlevinud. Ja JavaScript on omakorda alati suutnud töötada regulaaravaldistega ja sellel on RegExpsi jaoks sisseehitatud funktsioonid! Nii et Power Query regulaaravaldiste rakendamiseks peame andma Web.Page'i argumendina väikesele JavaScripti programmile, mis teeb Power Query jaoks kogu töö ära.
Kuidas see puhtas JavaScriptis välja näeb
Internetis on JavaScriptis regulaaravaldistega töötamise kohta palju üksikasjalikke õpetusi (näiteks üks, kaks).
Lühidalt ja lihtsustatult näeb JavaScripti kood välja järgmine:
siin:
- var str = 'Maksa vorsti eest arveid 123 ja 789'; – luua muutuja str ja määrake sellele lähtetekst, mida analüüsime.
- var muster = /d+/gi; – luua regulaaravaldis ja panna see muutujasse muster.
Avaldis algab kaldkriipsuga (/).
Väljend ise siin näiteks on d+ tähistab mis tahes numbrijada.
Avaldise järel oleva murdosa kaudu on täiendavad otsinguparameetrid (muutjad) – neid saab määrata suvalises järjekorras:
- g – tähendab globaalset otsingut, st pärast vaste leidmist ei tohiks lõpetada, vaid jätkata otsingut teksti lõpuni. Kui seda modifikaatorit ei määrata, tagastab meie skript ainult esimese vaste (123)
- i - otsige tähtede suur- ja väiketähti arvestamata
- m - mitmerealine otsing (kasutatakse, kui lähtetekst on jagatud mitmeks reale)
- var result = str.match(pattern).join(';'); – sooritage otsing lähtetekstis (str) antud regulaaravaldise (muster) ja asetage tulemused muutujasse kaasa, ühendades need käsu abil semikooloniga liituma
- document.write(tulemus); – kuvab tulemusmuutuja sisu
Pange tähele ka seda, et JavaScripti tekstistringid (v.a regulaaravaldised) on ümbritsetud apostroofidega, mitte jutumärkidega, nagu need on Power Query või VBA puhul.
Väljundis annab see skript meile kõik lähtetekstist leitud numbrid:
123, 789
JavaScripti lühikursus on läbi, tänan teid kõiki. Loodetavasti saite loogikast aru 🙂
Jääb üle kanda see konstruktsioon Power Querysse.
Otsige ja ekstraheerige tekstifunktsiooni Power Query regulaaravaldise järgi
Teeme järgmist:
1. Avage Excel ja looge vahekaardil uus tühi Power Query Andmed – Hangi andmed / Loo päring – Muudest allikatest – Tühi päring (Andmed — hangi andmed / uus päring — muudest allikatest — tühi päring). Kui teil on Exceli 2010–2013 vana versioon ja teil pole sisseehitatud Power Queryt, kuid see installiti eraldi lisandmoodulina, on see kõik vahekaardil Toite päringJa mitte kuupäev.
2. Sisestage avaneva päringuredaktori tühja akna paremas paneelis kohe meie tulevase funktsiooni nimi (näiteks fxRegExpExtract)
3. Läheme vahekaardile Vaade – täiustatud redaktor (Vaade – täiustatud redaktor), kustutame tühja päringu kogu M-koodi ja kleepime sinna oma superfunktsiooni koodi:
Jälgige oma käsi:
Esimesel real ütleme, et meie funktsioonil on kolm tekstiargumenti: txt – analüüsitav originaaltekst, regulaaravaldis - regulaaravaldise muster, piiritlema — eraldusmärk tulemuste kuvamiseks.
Järgmisena kutsume funktsiooni Veebileht, moodustades ülaltoodud argumendis kirjeldatud JavaScripti koodi. Kleepime ja asendame oma muutujate argumendid koodi.
Fragment:
[Andmed]{0}[Lapsed]{0}[Lapsed]{1}[Tekst]{0}
… on vajalik selleks, et "läbi kukkuda" vajalike tulemustega tabelisse. Asi on selles, et funktsioon Veebileht selle tulemusena toodab see mitu pesastatud tabelit, mis kordavad veebilehe struktuuri. Ilma selle M-koodita väljastaks meie funktsioon järgmise:
… ja me peaksime sõna mitu korda klõpsama Tabel, langedes järjestikku veergude kaupa alampesastatud tabelitesse Lapsed:
Kogu selle tsitaadi asemel märgime oma funktsiooni koodis kohe ära, milline pesastatud tabel ja veerg (Tekst) me vajame.
Siin on tegelikult kõik saladused. Jääb üle nupule vajutada lõpp aknas arenenud toimetaja, kuhu sisestasime oma koodi ja saate edasi minna kõige maitsvama juurde – proovige meie funktsiooni tööl.
Siin on paar seemnete näidet.
Näide 1. Konto numbri ja kuupäeva leidmine makse kirjeldusest
Meil on maksete kirjelduse (eesmärgiga) pangaväljavõte, kus tuleb eraldi veergudesse tõmmata tasutud arvete numbrid ja kuupäevad:
Laadime tabeli Power Querysse tavapärasel viisil Andmed – tabelist/vahemikust (Andmed – Tvõimeline/Ringel).
Seejärel lisame arvutatud veeru funktsiooniga via Lisa veerg – helistage kohandatud funktsioonile (Lisa veerg – kutsuge kohandatud funktsioon) ja sisestage selle argumendid:
Regulaaravaldisena (argument regulaaravaldis) mall, mida kasutame:
(d{3,5}|d{2}.d{2}.d{4})
… inimkeelde tõlgituna tähendab:
numbrid 3 kuni 5 numbrit (kontonumbrid)
or
fragmendid kujul "2-bitine arv - punkt - 2-bitine arv - punkt - 4-bitine arv", st kuupäevad kujul PP.KK.AAAA.
Eraldajana (argument piiritlema) sisestage semikoolon.
Pärast klõpsamist nupul OK meie võlufunktsioon analüüsib kõiki lähteandmeid meie regulaaravaldise järgi ja moodustab meile veeru leitud arvete numbrite ja kuupäevadega:
Jääb see käsu abil semikooloniga eraldada Avaleht – jagatud veerg – eraldaja järgi (Avaleht – jagatud veerg – eraldaja järgi) ja me saame, mida tahtsime:
Ilu!
Näide 2: eraldage tekstist e-posti aadressid
Oletame, et meil on lähteandmetena järgmine tabel:
… kust peame sealt leitud meiliaadressid välja tõmbama (selguse huvides tõstsin need tekstis punasega esile).
Nagu eelmises näites, laadime tabeli Power Querysse standardsel viisil kaudu Andmed – tabelist/vahemikust (Andmed – Tvõimeline/Ringel).
Seejärel lisame arvutatud veeru funktsiooniga via Lisa veerg – helistage kohandatud funktsioonile (Lisa veerg – kutsuge kohandatud funktsioon) ja sisestage selle argumendid:
E-posti aadresside sõelumine on keerulisem ülesanne ja selle lahendamiseks on hunnik erineva raskusastmega regulaaravaldisi. Kasutasin ühte lihtsat võimalust – mitte ideaalne, kuid enamikul juhtudel üsna töötav:
[w|.|-]*@w*.[w|.]*
Eraldajana (piiritlema) saate sisestada semikooloni ja tühiku.
Klõpsake OK ja saame veeru e-posti aadressidega, mis on välja võetud algtekstist “puder”:
Maagia!
PS
Nagu öeldakse: "Pole olemas nii head asja, mida ei saaks veelgi paremaks muuta." Power Query on iseenesest lahe ja regulaaravaldistega kombineerituna annab see meile täiesti ebareaalse jõu ja paindlikkuse mis tahes tekstiandmete töötlemisel. Loodan, et Microsoft lisab kunagi Power Query ja Power BI värskendustesse RegExpi toe ning kõik ülaltoodud tantsud tamburiiniga jäävad minevikku. No praegu jah.
Samuti tahan lisada, et regulaaravaldistega on mugav mängida saidil https://regexr.com/ – otse veebiredaktoris. Seal sektsioonis Kogukonna mustrid Igaks puhuks on tohutul hulgal valmis tavahooaegu. Katsetage – kõik regulaaravaldiste võimalused on nüüd Power Querys teie teenistuses!
- Mis on regulaaravaldised (RegExp) ja kuidas neid Excelis kasutada
- Hägune tekstiotsing Power Querys
- Tabelite kokkupanek erinevatest failidest Power Query abil