Cynnwys
Os ydych chi o leiaf ychydig yn gyfarwydd ag ymadroddion rheolaidd, yna nid oes angen i chi eu hysbysebu. Os nad ydych yn hollol yn y pwnc, yna mae ymadroddion rheolaidd (Mynegiadau Rheolaidd = RegExp = “regexps” = “rheolaidd”) yn iaith lle, gan ddefnyddio nodau a rheolau arbennig, y chwilir am yr is-linynnau angenrheidiol yn y testun, cânt eu hechdynnu. neu gael testun arall yn ei le. Mae hwn yn arf pwerus a hardd iawn, sy'n fwy na'r holl ffyrdd eraill o weithio gyda thestun.
Rwyf eisoes wedi disgrifio'n fanwl a chyda llawer o enghreifftiau o fywyd sut y gallwch chi ychwanegu cefnogaeth mynegiant rheolaidd i Excel gan ddefnyddio macros syml - os nad ydych wedi darllen yr erthygl hon, rwy'n argymell yn fawr eich bod yn ei darllen cyn parhau. Byddwch yn darganfod llawer o bethau newydd, rwy'n gwarantu 🙂
Fodd bynnag, mae'r cwestiwn yn parhau i fod yn agored - sut i ychwanegu'r gallu i ddefnyddio ymadroddion rheolaidd yn Power Query? Mae Power Query, wrth gwrs, yn dda ar ei ben ei hun a gall wneud llawer gyda thestun (torri, gludo, glanhau, ac ati), ond pe gallech ei groesi â phŵer ymadroddion rheolaidd, dim ond bom fyddai hynny.
Yn anffodus, nid oes unrhyw swyddogaethau adeiledig ar gyfer gweithio gyda RegExps yn Power Query, ac mae cymorth swyddogol a chymorth technegol Microsoft yn ateb y cwestiwn hwn yn negyddol. Fodd bynnag, mae ffordd o gwmpas y cyfyngiad hwn 🙂
Hanfod y dull
Mae'r prif syniad yn syml i'w warth.
Yn y rhestr o alluoedd Power Query adeiledig, mae yna swyddogaeth Tudalen we. Mae'r disgrifiad o'r swyddogaeth hon ar wefan gymorth swyddogol Microsoft yn gryno iawn:
Wedi'i gyfieithu, dyma fyddai: "Yn dychwelyd cynnwys y ddogfen HTML wedi'i rannu'n strwythurau cydrannol, yn ogystal â chynrychiolaeth o'r ddogfen gyflawn a'i chorff ar ôl i'r tagiau gael eu tynnu." Felly disgrifiad, a dweud y gwir.
Fel arfer defnyddir y swyddogaeth hon wrth fewnforio data o'r we ac fe'i hamnewidir yn awtomatig, er enghraifft, pan fyddwn yn dewis ar y tab Dyddiad Gorchymyn O'r Rhyngrwyd (Data - O'r we). Rydyn ni'n rhoi tudalen we i'r swyddogaeth fel dadl, ac mae'n dychwelyd ei chynnwys i ni ar ffurf tablau, ar ôl clirio'r holl dagiau o'r blaen.
Yr hyn NAD yw'r help yn ei ddweud yw hynny yn ychwanegol at yr iaith marcio HTML swyddogaeth Tudalen we cefnogi sgriptiau JavaScript, sydd bellach yn hollbresennol ar wefannau ar y Rhyngrwyd. Ac mae JavaScript, yn ei dro, bob amser wedi gallu gweithio gydag ymadroddion rheolaidd ac mae ganddo swyddogaethau adeiledig ar gyfer RegExps! Felly i weithredu ymadroddion rheolaidd yn Power Query, bydd angen i ni fwydo'r swyddogaethau Web.Page fel dadl i raglen JavaScript fach a fydd yn gwneud yr holl waith ar gyfer Power Query.
Sut olwg sydd arno mewn JavaScript pur
Mae yna lawer o sesiynau tiwtorial manwl ar weithio gydag ymadroddion rheolaidd yn JavaScript ar y Rhyngrwyd (er enghraifft, un, dau).
Yn fyr ac wedi'i symleiddio, bydd y cod JavaScript yn edrych fel hyn:
yma:
- var str = 'Talu biliau 123 a 789 am selsig'; – creu newidyn str a phennu iddo'r testun ffynhonnell y byddwn yn ei ddadansoddi.
- patrwm var = /d +/gi; – creu mynegiant rheolaidd a'i roi mewn newidyn patrwm.
Mae'r mynegiant yn dechrau gyda slaes (/).
Y mynegiant ei hun yma, er enghraifft, yw d+ yn sefyll am unrhyw ddilyniant o ddigidau.
Trwy'r ffracsiwn ar ôl y mynegiant, mae paramedrau chwilio ychwanegol (addaswyr) - gellir eu nodi mewn unrhyw drefn:
- g – yn golygu chwiliad byd-eang, hy ar ôl dod o hyd i gyfatebiaeth, ni ddylech stopio, ond parhau â'r chwiliad tan ddiwedd y testun. Os nad yw'r addasydd hwn wedi'i osod, yna bydd ein sgript yn dychwelyd y gêm gyntaf yn unig (123)
- i – chwiliwch heb ystyried achos llythyrau
- m - chwiliad aml-linell (a ddefnyddir pan rennir y testun ffynhonnell yn sawl llinell)
- var canlyniad = str.match(patrwm).join(';'); – gwneud chwiliad yn y testun ffynhonnell (str) gan y mynegiant rheolaidd a roddir (patrwm) a rhowch y canlyniadau mewn newidyn arwain, eu cydgatenu â hanner colon gan ddefnyddio'r gorchymyn ymuno
- document.write(canlyniad); - arddangos cynnwys y newidyn canlyniad
Sylwch hefyd fod llinynnau testun (ac eithrio ymadroddion rheolaidd) yn JavaScript wedi'u hamgáu mewn collnodau, nid dyfyniadau fel y maent yn Power Query neu VBA.
Yn yr allbwn, bydd y sgript hon yn rhoi'r holl rifau a geir yn y testun ffynhonnell i ni o ganlyniad:
123, 789
Mae cwrs byr JavaScript drosodd, diolch i chi gyd. Gobeithio y cewch chi'r rhesymeg 🙂
Mae angen trosglwyddo'r gwaith adeiladu hwn i Power Query.
Chwilio a Dethol Swyddogaeth Testun yn ôl Mynegiant Rheolaidd mewn Ymholiad Pŵer
Rydym yn gwneud y canlynol:
1. Agorwch Excel a chreu Ymholiad Pŵer gwag newydd yn y tab Data - Cael data / Creu cais - O ffynonellau eraill - Cais gwag (Data - Cael data / Ymholiad newydd - O ffynonellau eraill - Ymholiad gwag). Os oes gennych chi hen fersiwn o Excel 2010-2013 a Power Query nad oes gennych chi wedi'i gynnwys, ond fe'i gosodwyd fel ychwanegiad ar wahân, yna bydd hyn i gyd ar y tab Ymholiad PwerAc nid Dyddiad.
2. Yn ffenestr wag golygydd yr ymholiad sy'n agor, yn y panel ar y dde, rhowch enw ein swyddogaeth yn y dyfodol ar unwaith (er enghraifft, fxRegExpExtract)
3. Gadewch i ni fynd i'r tab Gweld - Golygydd Uwch (Gweld - Golygydd Uwch), rydym yn dileu cod M cyfan y cais gwag ac yn gludo cod ein uwchswyddogaeth yno:
Gwyliwch eich dwylo:
Yn y llinell gyntaf, dywedwn y bydd gan ein swyddogaeth dair dadl destun: txt – y testun gwreiddiol yn cael ei ddadansoddi, regex - patrwm mynegiant rheolaidd, delim — nod amffinydd ar gyfer dangos canlyniadau.
Nesaf rydym yn galw'r swyddogaeth Tudalen we, gan ffurfio'r cod JavaScript a ddisgrifir uchod yn ei ddadl. Rydym yn gludo ac yn amnewid ein dadleuon newidiol i'r cod.
Darn:
[Data]{/0}[Plant]{/0}[Plant]{/1}[Text]{0}
… i “syrthio” i'r tabl gyda'r canlyniadau sydd eu hangen arnom. Y pwynt yw bod y swyddogaeth Tudalen we o ganlyniad, mae'n cynhyrchu sawl tabl nythu sy'n ailadrodd strwythur tudalen we. Heb y darn hwn o god M, byddai ein swyddogaeth yn allbynnu hyn:
…a byddai'n rhaid clicio'r gair sawl gwaith Tabl, yn olynol “syrthio trwy” i mewn i dablau nythu plant mewn colofnau Plant:
Yn lle'r holl ddyfyniad hwn, rydym yn nodi ar unwaith yng nghod ein swyddogaeth pa dabl a cholofn nythu (Testun) angen.
Yma, mewn gwirionedd, mae'r holl gyfrinachau. Mae'n parhau i fod i wasgu'r botwm Gorffen yn y ffenestr uwch-olygydd, lle gwnaethom fewnosod ein cod, a gallwch symud ymlaen i'r mwyaf blasus - rhowch gynnig ar ein swyddogaeth yn y gwaith.
Dyma ychydig o enghreifftiau o hadau.
Enghraifft 1. Adalw rhif y cyfrif a'r dyddiad o'r disgrifiad o'r taliad
Mae gennym gyfriflen banc gyda disgrifiad (diben) o daliadau, lle mae angen i chi dynnu rhifau a dyddiadau anfonebau a dalwyd mewn colofnau ar wahân:
Rydyn ni'n llwytho'r bwrdd i mewn i Power Query yn y ffordd safonol drwodd Data – O Dabl/Amrediad (Data - Gan Tgallu/Rangel).
Yna rydym yn ychwanegu colofn wedi'i gyfrifo gyda'n swyddogaeth trwy Ychwanegu Colofn - Ffoniwch Swyddogaeth Custom (Ychwanegu Colofn - Galw Swyddogaeth Custom) a nodwch ei ddadleuon:
Fel mynegiant rheolaidd (dadl regex) templed rydyn ni'n ei ddefnyddio:
(d{3,5}|d{2}.d{2}.d{4})
… wedi'i gyfieithu i iaith ddynol sy'n golygu:
rhifau o 3 i 5 digid (rhifau cyfrif)
or
darnau o’r ffurflen “Rhif 2-did – pwynt – rhif 2-did – pwynt – rhif 4-did”, hynny yw, dyddiadau'r ffurflen DD.MM.YYYY.
Fel cymeriad amffinydd (dadl delim) mynd i mewn i hanner colon.
Ar ôl clicio ar OK mae ein swyddogaeth hud yn dadansoddi'r holl ddata cychwynnol yn ôl ein mynegiant rheolaidd ac yn ffurfio colofn i ni gyda'r niferoedd a ddarganfuwyd a dyddiadau'r anfonebau:
Mae'n parhau i fod i'w wahanu gan hanner colon gan ddefnyddio'r gorchymyn Cartref—Colofn Hollt—Gan Amffinydd (Cartref - Colofn wedi'i hollti - Yn ôl amffinydd) ac rydyn ni'n cael yr hyn roedden ni ei eisiau:
Harddwch!
Enghraifft 2: Tynnwch gyfeiriadau e-bost o'r testun
Tybiwch fod gennym y tabl canlynol fel data cychwynnol:
… o ble mae angen i ni dynnu allan y cyfeiriadau e-bost a geir yno (er eglurder, amlygais nhw mewn coch yn y testun).
Fel yn yr enghraifft flaenorol, rydym yn llwytho'r tabl i mewn i Power Query yn y ffordd safonol trwy Data – O Dabl/Amrediad (Data - Gan Tgallu/Rangel).
Yna rydym yn ychwanegu colofn wedi'i gyfrifo gyda'n swyddogaeth trwy Ychwanegu Colofn - Ffoniwch Swyddogaeth Custom (Ychwanegu Colofn - Galw Swyddogaeth Custom) a nodwch ei ddadleuon:
Mae dosrannu cyfeiriadau e-bost yn dasg anoddach ac mae yna griw o fynegiadau rheolaidd o wahanol raddau o hunllef i'w datrys. Defnyddiais un o'r opsiynau syml - ddim yn ddelfrydol, ond yn gweithio'n iawn yn y rhan fwyaf o achosion:
[w|.|-]*@w*.[w|.]*
Fel gwahanydd (delim) gallwch fynd i mewn i hanner colon a gofod.
Cliciwch ar OK ac rydym yn cael colofn gyda chyfeiriadau e-bost wedi'u tynnu o'r testun gwreiddiol “uwd”:
Hud!
PS
Fel y dywed y dywediad: “Nid oes y fath beth da na ellid ei wneud hyd yn oed yn well.” Mae Power Query yn cŵl ar ei ben ei hun, ac o'i gyfuno ag ymadroddion rheolaidd, mae'n rhoi pŵer a hyblygrwydd cwbl afrealistig i ni wrth brosesu unrhyw ddata testun. Rwy'n gobeithio y bydd Microsoft rywbryd yn ychwanegu cefnogaeth RegExp mewn diweddariadau Power Query a Power BI a bydd yr holl ddawnsiau uchod gyda thambwrîn yn dod yn beth o'r gorffennol. Wel, am y tro, ie.
Rwyf hefyd am ychwanegu ei bod yn gyfleus chwarae gydag ymadroddion rheolaidd ar y wefan https://regexr.com/ - yn union yn y golygydd ar-lein. Yno yn yr adran Patrymau Cymunedol Mae yna nifer enfawr o dymhorau rheolaidd parod ar gyfer pob achlysur. Arbrawf - mae holl bŵer ymadroddion rheolaidd bellach yn eich gwasanaeth yn Power Query!
- Beth yw ymadroddion rheolaidd (RegExp) a sut i'w defnyddio yn Excel
- Chwiliad testun niwlog yn Power Query
- Cydosod tablau o wahanol ffeiliau gan ddefnyddio Power Query