Okuqukethwe
Uma okungenani ujwayelene kancane nezinkulumo ezivamile, awudingi ukuzikhangisa. Uma ungekho esihlokweni, izinkulumo ezivamile (Regular Expressions = RegExp = “regexps” = “regulars”) ziwulimi lapho, kusetshenziswa izinhlamvu ezikhethekile nemithetho, uchungechunge oluncane oludingekayo luseshwa embhalweni, lukhishwe khona. noma kufakwe omunye umbhalo esikhundleni salokho . Leli ithuluzi elinamandla kakhulu futhi elihle, ukuhleleka kobukhulu obudlula zonke ezinye izindlela zokusebenza ngombhalo.
Sengivele ngichaze ngokuningiliziwe kanye nenqwaba yezibonelo ezivela empilweni ukuthi ungangeza kanjani ukwesekwa kwenkulumo evamile ku-Excel usebenzisa ama-macros alula - uma ungazange ufunde lesi sihloko, ngincoma kakhulu ukuthi usifunde ngaphambi kokuqhubeka. Uzothola izinto eziningi ezintsha, ngiyakuqinisekisa 🙂
Kodwa-ke, umbuzo uhlala uvulekile - indlela yokwengeza ikhono lokusebenzisa izinkulumo ezijwayelekile kumbuzo wamandla? I-Power Query, yebo, inhle ngokwayo futhi ingenza okuningi ngombhalo (ukusika, ukunamathisela, ukuhlanza, njll.), Kodwa uma ungayiwela ngamandla ezinkulumo ezivamile, kungaba ibhomu nje.
Ngeshwa, ayikho imisebenzi eyakhelwe ngaphakathi yokusebenza ne-RegExps ku-Power Query, nosizo olusemthethweni lwe-Microsoft nokusekelwa kobuchwepheshe kuphendula lo mbuzo ngendlela engemihle. Nokho, kukhona indlela ezungeze lo mkhawulo 🙂
Ingqikithi yendlela
Umqondo oyinhloko kulula ukuwuhlazisa.
Ohlwini lwamakhono akhelwe ngaphakathi Ombuzo Wamandla, kunomsebenzi Iwebhusayithi. Incazelo yalo msebenzi esizeni sosizo se-Microsoft sifushane kakhulu:
Uma kuhunyushwa, lokhu kuzoba ukuthi: “Ibuyisela okuqukethwe kwedokhumenti ye-HTML ehlukaniswe izingxenye zayo, kanye nokumelwa kwedokhumenti ephelele kanye nomzimba wayo ngemva kokukhishwa kwamathegi.” Incazelo ye-So-so, ngokungananazi.
Ngokuvamile lo msebenzi usetshenziswa uma ungenisa idatha kuwebhu futhi ushintshwa ngokuzenzakalelayo, isibonelo, uma sikhetha kuthebhu. Idatha Umyalo Kusuka ku-inthanethi (Idatha - Kusuka kuwebhu). Sinikeza umsebenzi ikhasi lewebhu njengengxabano, futhi libuyisela okuqukethwe kwalo kithi ngendlela yamathebula, njengoba lisuse ngaphambilini bonke omaka.
AWUSIZO AKUSHO ukuthi ngaphezu kolimi lwemakhaphu ye-HTML umsebenzi Iwebhusayithi isekela imibhalo ye-JavaScript, manje esitholakala yonke indawo kumawebhusayithi aku-inthanethi. Futhi i-JavaScript, yona, ibilokhu ikwazi ukusebenza ngezinkulumo ezivamile futhi inemisebenzi eyakhelwe ngaphakathi ye-RegExps! Ngakho-ke ukuze sisebenzise izinkulumo ezivamile kokuthi Umbuzo Wamandla, sizodinga ukuphakela imisebenzi ye-Web.Page njengengxabano kuhlelo oluncane lwe-JavaScript oluzokwenza wonke umsebenzi Wombuzo Wamandla.
Ibukeka kanjani kuJavaScript emsulwa
Kunezifundo eziningi ezinemininingwane ekusebenzeni nezinkulumo ezijwayelekile ku-JavaScript ku-inthanethi (isibonelo, eyodwa, ezimbili).
Kafushane futhi lula, ikhodi ye-JavaScript izobukeka kanje:
Lapha:
- var str = 'Khokha izikweletu 123 kanye ne-789 ngesoseji'; – dala okuguquguqukayo str futhi inikeze umbhalo owumthombo esizowuhlaziya.
- var iphethini = /d+/gi; – dala isisho esijwayelekile bese usibeka kokuguquguqukayo iphethini.
Inkulumo iqala ngo-slash (/).
Inkulumo ngokwayo lapha, isibonelo, ithi d+ imele noma yikuphi ukulandelana kwamadijithi.
Ngokusebenzisa ingxenyena ngemva kwenkulumo, kunemingcele yokusesha eyengeziwe (iziguquli) - zingacaciswa nganoma iyiphi indlela:
- g – kusho ukusesha komhlaba wonke, okungukuthi ngemva kokuthola okufanayo, akufanele uyeke, kodwa qhubeka nokusesha kuze kube sekupheleni kombhalo. Uma lesi silungisi singasethiwe, umbhalo wethu uzobuyisela okufanayo kuphela (123)
- i – sesha ngaphandle kokubheka icala lezinhlamvu
- m - ukusesha kwemigqa eminingi (esetshenziswa lapho umbhalo womthombo uhlukaniswa ngemigqa eminingana)
- var result = str.match(pattern).join(';'); - sesha embhalweni owumthombo (str) ngenkulumo evamile enikeziwe (iphethini) bese ubeka imiphumela kokuguquguqukayo umphumela, ezihlanganisa nesemicolon usebenzisa umyalo Ujoyine
- idokhumenti.bhala(umphumela); – veza okuqukethwe kokuguquguquka kwemiphumela
Futhi qaphela ukuthi izintambo zombhalo (ngaphandle kwezinkulumo ezivamile) ku-JavaScript zivalelwe kuma-aphostrophe, hhayi izingcaphuno njengoba ziku-Power Query noma i-VBA.
Kokukhiphayo, lesi sikripthi sizosinika njengomphumela zonke izinombolo ezitholakala kumbhalo womthombo:
123, 789
Isifundo esifushane se-JavaScript sesiphelile, ngiyabonga nonke. Ngethemba ukuthi uzowuthola umqondo 🙂
Kusele ukudlulisa lokhu kwakhiwa kokuthi Umbuzo Wamandla.
Sesha futhi Ukhiphe Umsebenzi Wombhalo Ngokuvezwa Okujwayelekile Embuzweni Wamandla
Senza lokhu okulandelayo:
1. Vula i-Excel bese udala Umbuzo Wamandla omusha ongenalutho kuthebhu Idatha - Thola idatha / Dala isicelo - Kweminye imithombo - Isicelo esingenalutho (Idatha - Thola idatha / Umbuzo omusha - Kweminye imithombo - Umbuzo ongenalutho). Uma unenguqulo endala ye-Excel 2010-2013 kanye Nombuzo Wamandla ongakhelwe kuwo, kodwa wafakwa njengesengezo esihlukile, khona-ke konke lokhu kuzoba kuthebhu. Umbuzo WamandlaFuthi hhayi Idatha.
2. Efasiteleni elingenalutho lomhleli wombuzo elivulekayo, kuphaneli engakwesokudla, faka ngokushesha igama lomsebenzi wethu wesikhathi esizayo (isibonelo, fxRegExpExtract)
3. Asiye kuthebhu Buka – Isihleli Esithuthukile (Buka — Isihleli Esithuthukile), sisula yonke ikhodi engu-M yesicelo esingenalutho futhi sinamathisele ikhodi yomsebenzi wethu omkhulu lapho:
Buka izandla zakho:
Emgqeni wokuqala, sithi umsebenzi wethu uzoba nezimpikiswano ezintathu zombhalo: txt - umbhalo wokuqala uyahlaziywa, regex - iphethini yenkulumo evamile, delim — uhlamvu olunqunyiwe lokubonisa imiphumela.
Okulandelayo sibiza umsebenzi Iwebhusayithi, ukwakha ikhodi ye-JavaScript echazwe ngenhla kumpikiswano yayo. Sinamathisela futhi sishintshe izimpikiswano zethu eziguquguqukayo kukhodi.
Isiqephu:
[Idatha]{0}[Izingane]{0}[Izingane]{1}[Umbhalo]{0}
… iyadingeka “ukuwela” etafuleni ngemiphumela esiyidingayo. Iphuzu liwukuthi umsebenzi Iwebhusayithi ngenxa yalokho, ikhiqiza amathebula amaningana avalelwe aphinda ukwakheka kwekhasi lewebhu. Ngaphandle kwalolu cezu lwe-M-code, umsebenzi wethu uzokhipha lokhu:
… futhi kuzodingeka ukuthi sichofoze igama izikhathi ezimbalwa Ithebula, ngokulandelana "iwela" kumathebula ezingane akhiwe ngamakholomu Izingane:
Esikhundleni sakho konke lokhu okucashuniwe, ngokushesha sibonisa kukhodi yomsebenzi wethu lapho kubekwe ithebula nekholomu (Umbhalo) sidinga.
Lapha, empeleni, zonke izimfihlo. Kusele ukucindezela inkinobho Qeda efasiteleni umhleli othuthukile, lapho sifaka khona ikhodi yethu, futhi ungakwazi ukuqhubeka nokumnandi kakhulu - zama umsebenzi wethu emsebenzini.
Nazi izibonelo zembewu ezimbalwa.
Isibonelo 1. Ukubuyisa inombolo ye-akhawunti kanye nosuku encazelweni yokukhokha
Sinesitatimende sasebhange esinencazelo (inhloso) yezinkokhelo, lapho udinga ukukhipha izinombolo nezinsuku zama-invoyisi akhokhelwe ube amakholomu ahlukene:
Silayisha ithebula kokuthi Umbuzo Wamandla ngendlela evamile Idatha - Kusuka kuThebula/Ububanzi (Idatha - Kusuka ku-Tuyakwazi/Ringelosi).
Bese sengeza ikholomu ebaliwe ngomsebenzi wethu nge Engeza Ikholomu – Shayela Umsebenzi Wangokwezifiso (Engeza Ikholomu — Cela Umsebenzi Wangokwezifiso) bese ufaka izimpikiswano zayo:
Njengenkulumo evamile (ingxabano regex) isifanekiso esisisebenzisayo:
(d{3,5}|d{2}.d{2}.d{4})
… ihunyushelwe olimini lwabantu incazelo:
izinombolo ezisuka ku-3 kuye kwezi-5 (izinombolo ze-akhawunti)
or
izingcezu zefomu elithi “2-bit number – point – 2-bit number – point – 4-bit number”, okungukuthi, izinsuku zefomu elithi DD.MM.YYYY.
Njengomlingiswa ohlukanisayo (ingxabano delim) faka isemicolon.
Ngemuva kokuchofoza OK umsebenzi wethu womlingo uhlaziya yonke idatha yokuqala ngokwenkulumo yethu evamile bese usenzela ikholomu enezinombolo ezitholakele nezinsuku zama-invoyisi:
Kusala ukuyihlukanisa nge-semicolon usebenzisa umyalo Ikhaya - Hlukanisa Ikholomu - Ngu-Delimiter (Ikhaya — Hlukanisa ikholomu — Ngomhlukanisi) futhi sithola ebesikufuna:
Ubuhle!
Isibonelo sesi-2: Khipha amakheli e-imeyili embhalweni
Ake sithi sinethebula elilandelayo njengedatha yokuqala:
… lapho sidinga ukukhipha amakheli e-imeyili atholakala lapho (ukuze kucace, ngiwagqamise ngokubomvu embhalweni).
Njengasesibonelweni sangaphambilini, silayisha itafula kokuthi Umbuzo Wamandla ngendlela evamile nge Idatha - Kusuka kuThebula/Ububanzi (Idatha - Kusuka ku-Tuyakwazi/Ringelosi).
Bese sengeza ikholomu ebaliwe ngomsebenzi wethu nge Engeza Ikholomu – Shayela Umsebenzi Wangokwezifiso (Engeza Ikholomu — Cela Umsebenzi Wangokwezifiso) bese ufaka izimpikiswano zayo:
Ukuhlaziya amakheli e-imeyili kuwumsebenzi onzima kakhulu futhi kunenqwaba yezinkulumo ezijwayelekile zamazinga ahlukahlukene amaphupho amabi ukuze sikuxazulule. Ngisebenzise enye yezinketho ezilula - ayilungile, kodwa isebenza kakhulu ezimweni eziningi:
[w|.|-]*@w*.[w|.]*
Njengesihlukanisi (delim) ungafaka isemikholoni nesikhala.
Chofoza OK futhi sithola ikholomu enamakheli e-imeyili akhishwe embhalweni wokuqala “uphuthu”:
Umlingo!
PS
Njengoba isisho sisho: “Ayikho into enhle kangaka engenziwa ibe ngcono nakakhulu.” Umbuzo Wamandla uzipholile wona wodwa, futhi uma uhlanganiswa nezinkulumo ezivamile, usinika amandla angekho ngokoqobo ngokuphelele kanye nokuguquguquka ekucubunguleni noma iyiphi idatha yombhalo. Ngithemba ukuthi i-Microsoft ngelinye ilanga izokwengeza ukwesekwa kwe-RegExp ku-Power Query kanye nezibuyekezo ze-Power BI futhi yonke imidanso engenhla enethamborini izoba yinto yesikhathi esidlule. Nokho, okwamanje, yebo.
Ngifuna futhi ukwengeza ukuthi kulula ukudlala ngezisho ezijwayelekile kusayithi https://regexr.com/ - khona kanye kumhleli we-inthanethi. Laphaya esigabeni Amaphethini Omphakathi Kunenombolo enkulu yezinkathi ezivamile esezilungile zazo zonke izikhathi. Isilingo - wonke amandla ezinkulumo ezivamile manje asenkonzweni yakho kokuthi Umbuzo Wamandla!
- Yiziphi izinkulumo ezijwayelekile (RegExp) nokuthi zisetshenziswa kanjani ku-Excel
- Ukusesha umbhalo okungaqondakali kokuthi Umbuzo Wamandla
- Ukuhlanganisa amatafula avela kumafayela ahlukene kusetshenziswa Umbuzo Wamandla