Mukati
Kana iwe uchiziva zvishoma nezve mataurirwo enguva dzose, saka haufanirwe kuashambadza. Kana iwe usiri mumusoro wenyaya, saka mataurirwo enguva dzose (Regular Expressions = RegExp = "regexps" = "regulars") mutauro apo, uchishandisa mavara akakosha uye mitemo, madiki anodiwa anotsvakwa muzvinyorwa, anotorwa. kana kuti kutsiviwa nemamwe mashoko . Ichi chishandiso chine simba kwazvo uye chakanaka, kurongeka kwehukuru hwepamusoro kune dzimwe nzira dzese dzekushanda nemavara.
Ini ndatotsanangura zvakadzama uye neboka remienzaniso kubva kuhupenyu kuti iwe unogona sei kuwedzera tsigiro yekutaura kuExcel uchishandisa macros akareruka - kana usati waverenga chinyorwa ichi, ndinokurudzira zvikuru kuti uverenge usati waenderera. Iwe uchawana zvakawanda zvezvinhu zvitsva, ndinovimbisa 🙂
Nekudaro, mubvunzo unoramba wakavhurika - maitiro ekuwedzera kugona kushandisa nguva dzose matauriro muSimba Query? Power Query, hongu, yakanaka pachayo uye inogona kuita zvakawanda nemavara (kucheka, gluing, kuchenesa, nezvimwewo), asi kana iwe ukagona kuyambuka nesimba rekutaura nguva dzose, ingangove bhomba.
Nehurombo, hapana akavakirwa-mukati mabasa ekushanda neRegExps muSimba Query, uye yepamutemo Microsoft rubatsiro nerutsigiro rwehunyanzvi pindura mubvunzo uyu mune zvakaipa. Nekudaro, pane nzira yakatenderedza iyi yekumisa 🙂
Musimboti wenzira
Pfungwa huru iri nyore kunyadzisa.
Mune runyorwa rweakavakirwa-mukati Power Query kugona, pane basa Web.Page. Tsananguro yebasa iri pane yepamutemo Microsoft saiti yekubatsira ipfupi kwazvo:
Yakashandurwa, izvi zvingave: "Inodzorera zviri mukati megwaro reHTML rakaputsika muzvikamu zvaro, pamwe nekumiririrwa kwegwaro rakazara nemuviri waro mushure mekunge ma tag abviswa." So-so tsananguro, kutaura chokwadi.
Kazhinji basa iri rinoshandiswa pakuunza dhata kubva pawebhu uye rinongotsiviwa otomatiki, semuenzaniso, patinosarudza pane tebhu. Data murayiro Kubva paInternet (Data - Kubva pawebhu). Isu tinopa basa racho peji rewebhu senharo, uye inodzosera zviri mukati medu kwatiri nenzira yematafura, yakambobvisa ma tag ese.
Izvo rubatsiro rusingataure ndechekuti mukuwedzera kune iyo HTML markup mutauro basa Web.Page inotsigira JavaScript zvinyorwa, iyo yava kuwanikwa kwose kwose pamawebsite paInternet. Uye JavaScript, zvakare, yagara ichikwanisa kushanda nemataurirwo enguva dzose uye ine yakavakirwa-mukati mabasa eRegExps! Saka kuti tishandise matauriro enguva dzose muKubvunza Kwesimba, tichada kudyisa Webhu.Page mabasa senharo kuchirongwa chidiki cheJavaScript chinozoita basa rese reMasimba Query.
Zvazvinotaridzika muJavaScript yakachena
Kune akawanda akadzama tutorials ekushanda neanogara achitaurwa muJavaScript paInternet (semuenzaniso, imwe, mbiri).
Muchidimbu uye chakareruka, iyo JavaScript kodhi ichaita seizvi:
Pano:
- var str = 'Bhadhara mabhiri 123 uye 789 yesoseji'; - gadzira shanduko str uye igovere mashoko ari kunobva kwatichaongorora.
- var pattern = /d+/gi; - gadzira chirevo chenguva dzose uye chiise mune shanduko pattern.
Chirevo ichi chinotanga nechidimbu (/).
Chirevo pachacho pano, semuenzaniso, ndechekuti d+ inomirira chero kutevedzana kwemanhamba.
Kuburikidza nechikamu mushure mekutaura, kune mamwe maparamita ekutsvaga (modifiers) - anogona kutsanangurwa mune chero kurongeka:
- g - zvinoreva kutsvaga kwepasirese, kureva kuti mushure mekuwana mutambo, haufanirwe kumira, asi ramba uchitsvaga kusvika pakupera kwechinyorwa. Kana iyi modifier isina kusetwa, saka script yedu inongodzosa mutambo wekutanga (123)
- i - tsvaga pasina hanya nenyaya yetsamba
- m -Kutsvaga kwemitsara yakawanda (inoshandiswa kana chinyorwa chikakamurwa kuita mitsara yakati wandei)
- var result = str.match(pattern).join(';'); - ita tsvagiridzo muzvinyorwa zvekwakabva (str) nekutaura kwagara kuripo (pattern) uye isa mhedzisiro mune shanduko result, achivabatanidza ne semicolon uchishandisa murairo Join
- document.write(mhedzisiro); - ratidza zviri mukati memhedzisiro inoshanduka
Ziva zvakare kuti tambo dzemavara (kusanganisa mataurirwo enguva dzose) muJavaScript akavharirwa muapostrophes, kwete makotesheni sezvaari muPower Query kana VBA.
Pazvinobuda, iyi script ichatipa semhedzisiro nhamba dzese dzinowanikwa muzvinyorwa zvekwakabva:
123, 789
JavaScript short course yapera, ndatenda mose. Ndinovimba iwe unowana iyo logic 🙂
Chasara kuendesa chivakwa ichi kune Power Query.
Tsvaga uye Bvisa Chinyorwa Basa neKugara Kwekutaura mune Simba Query
Isu tinoita zvinotevera:
1. Vhura Excel uye gadzira nyowani isina chinhu Power Query mune iyo tab Dhata - Tora data / Gadzira chikumbiro - Kubva kune mamwe masosi - Chisina chikumbiro (Data - Tora data / Mutsva mutsva - Kubva kune mamwe masosi - isina mubvunzo). Kana iwe uine yekare vhezheni yeExcel 2010-2013 uye Power Query iwe hauna kuvaka-mukati, asi yakaiswa seyakasiyana yekuwedzera-mukati, zvino zvese izvi zvichange zviri pane tab. Simba QueryUye kwete Data.
2. Muhwindo risina chinhu remubvunzo wemupepeti unovhura, mupaneru yekurudyi, pakarepo isa zita rebasa redu remangwana (semuenzaniso, fxRegExpExtract)
3. Handei kune tab Tarisa - Yepamberi Edhita (Ona - Yepamberi Mupepeti), tinodzima iyo yose M-kodhi yechikumbiro chisina chinhu uye tinoisa kodhi yebasa redu repamusoro ipapo:
Tarisa maoko ako:
Mumutsara wekutanga, tinoti basa redu richava nenharo dzemavara matatu: txt - zvinyorwa zvepakutanga zviri kuongororwa, regex - maitiro ekutaura nguva dzose, delim - delimiter character yekuratidza mibairo.
Tevere tinodaidza basa Web.Page, kugadzira JavaScript kodhi inotsanangurwa pamusoro apa mukupokana kwayo. Isu tinoisa uye tinotsiva nharo dzedu dzakasiyana mukodhi.
Chimedu:
[Data]{0}[Vana]{0}[Vana]{1}[Text]{0}
… inodiwa “kudonha” mutafura nemhedzisiro yatinoda. Pfungwa ndeyokuti basa racho Web.Page semhedzisiro, inoburitsa akati wandei matafura matendere anodzokorora chimiro chewebhu peji. Pasina ichi chidimbu cheM-code, basa redu raizoburitsa izvi:
... uye isu taizofanirwa kudzvanya izwi kakawanda tafura, zvichitevedzana "kudonha" mumatafura evana mumakoramu Children:
Panzvimbo pezvese izvi quotation, isu tinobva taratidza mune kodhi yebasa redu iro rakaisa tafura uye koramu (Text) Tinoda.
Pano, chaizvoizvo, zvose zvakavanzika. Inoramba ichidzvanya bhatani pedza pahwindo advanced editor, kwatakaisa kodhi yedu, uye iwe unogona kuenderera kune inonaka kwazvo - edza basa redu pabasa.
Heino mienzaniso miviri yembeu.
Muenzaniso 1. Kutora nhamba yeakaunti uye zuva kubva pakutsanangurwa kwekubhadhara
Tine chirevo chebhangi chine tsananguro (chinangwa) chekubhadhara, kwaunoda kuburitsa nhamba nemazuva emainvoice akabhadharwa mumakoramu akasiyana:
Isu tinorodha tafura muPower Query nenzira yakajairika kuburikidza Data – Kubva Tafura/Range (Data - Kubva kuna Tkugona/Rngirozi).
Zvadaro tinowedzera koramu yakaverengwa nebasa redu kuburikidza Wedzera Column - Call Custom Basa (Wedzera Column - Invoke Custom Function) uye isai nharo dzayo.
Sekutaura kwenguva dzose (nharo regex) template yatinoshandisa:
(d{3,5}|d{2}.d{2}.d{4})
… yakaturikirwa mumutauro wevanhu zvichireva:
nhamba kubva pa3 kusvika ku5 manhamba (nhamba dzeakaundi)
or
zvidimbu zvefomu "2-bit nhamba - poindi - 2-bit nhamba - poindi - 4-bit nhamba", kureva mazuva efomu DD.MM.YYYY.
Semunhu ane delimiter (argument delim) isa semicolon.
Mushure mekunge mavharidza OK basa redu remashiripiti rinoongorora data rese rekutanga zvinoenderana nekutaura kwedu nguva dzose uye inotigadzirira koramu nenhamba dzakawanikwa uye nemazuva emainvoice:
Inoramba ichiiparadzanisa ne semicolon uchishandisa murairo Musha — Split Column — By Delimiter (Kumba - Split column - By delimiter) uye tinowana zvataida:
Runako!
Muenzaniso 2: Bvisa email kero kubva pane zvinyorwa
Ngatitii tine tafura inotevera se data yekutanga:
... kubva kwatinoda kuburitsa kero dzeemail dzinowanikwa ipapo (kujekesa, ndakaasimbisa mune zvitsvuku muzvinyorwa).
Senge mumuenzaniso wapfuura, tinoisa tafura muPower Query nenzira yakajairwa kuburikidza Data – Kubva Tafura/Range (Data - Kubva kuna Tkugona/Rngirozi).
Zvadaro tinowedzera koramu yakaverengwa nebasa redu kuburikidza Wedzera Column - Call Custom Basa (Wedzera Column - Invoke Custom Function) uye isai nharo dzayo.
Kuisa kero dzeemail ibasa rakaoma uye kune boka rezvizhinji zvekutaura kweakasiyana madhigirii ehusiku kuzvigadzirisa. Ndakashandisa imwe yesarudzo dzakareruka - isina kunaka, asi inoshanda muzviitiko zvakawanda:
[w|.|-]*@w*.[w|.]*
Sekupatsanura (delim) unogona kupinda semicolon uye nzvimbo.
Click on OK uye tinowana koramu ine kero dze-e-mail dzakatorwa kubva mumagwaro ekutanga "porridge":
Mashiripiti!
PS
Sezvo chirevo chinoti: "Hakuna chinhu chakanaka chakadaro chisingagone kuitwa nani." Power Query inotonhorera pachayo, uye kana yakasanganiswa neakajairika mataurirwo, inotipa risingaite simba uye kuchinjika mukugadzirisa chero zvinyorwa zvinyorwa. Ndinovimba Microsoft rimwe zuva ichawedzera RegExp rutsigiro muSimba Query uye Power BI inogadziridza uye ese ari pamusoro apa matambiro ane tamborini anozove chinhu chekare. Zvakanaka, ikozvino, hongu.
Ini zvakare ndinoda kuwedzera kuti zviri nyore kutamba nemataurirwo enguva dzose pane saiti https://regexr.com/ - mupepeti yepamhepo chaipo. Ikoko muchikamu Community Patterns Kune huwandu hukuru hwemwaka wakagadzirira-wakagadzirwa wenguva dzese zviitiko. Kuedza - simba rose rekutaura nguva dzose rava pabasa rako muPower Query!
- Ndeapi mataurirwo enguva dzose (RegExp) uye maitiro ekuashandisa muExcel
- Kutsvaga mavara asina kujeka muKubvunza Kwesimba
- Kuunganidza matafura kubva akasiyana mafaera uchishandisa Power Query