Cynnwys
Un o'r fideos yr edrychir arno fwyaf ar fy sianel YouTube yw fideo am Flash Fill yn Microsoft Excel. Hanfod yr offeryn hwn yw, os oes angen i chi drawsnewid eich data ffynhonnell rywsut, yna mae angen i chi ddechrau teipio'r canlyniad rydych chi am ei gael yn y golofn gyfagos. Ar ôl sawl cell wedi'u teipio â llaw (fel arfer mae 2-3 yn ddigon), bydd Excel yn “deall” rhesymeg y trawsnewidiadau sydd eu hangen arnoch ac yn parhau â'r hyn rydych chi wedi'i deipio yn awtomatig, gan gwblhau'r holl waith undonog i chi:
Hanfod effeithlonrwydd. Y botwm hud “gwneud pethau'n iawn” rydyn ni i gyd yn ei garu cymaint, iawn?
Mewn gwirionedd, mae analog o offeryn o'r fath yn Power Query - yno fe'i gelwir Colofn o enghreifftiau (Colofn o Enghreifftiau). Mewn gwirionedd, deallusrwydd artiffisial bach yw hwn sydd wedi'i ymgorffori yn Power Query a all ddysgu'n gyflym o'ch data ac yna ei drawsnewid. Gadewch i ni edrych yn agosach ar ei alluoedd mewn sawl senario ymarferol er mwyn deall lle gall fod yn ddefnyddiol i ni mewn tasgau go iawn.
Enghraifft 1. Gludo/torri testun
Gadewch i ni ddweud bod gennym dabl mor “smart” yn Excel gyda data ar weithwyr:
Llwythwch ef i Power Query yn y ffordd safonol - gyda'r botwm O'r Tabl / Ystod tab Dyddiad (Data — O Dabl/Ystod).
Tybiwch fod angen i ni ychwanegu colofn gydag enwau olaf a blaenlythrennau ar gyfer pob gweithiwr (Ivanov SV ar gyfer y gweithiwr cyntaf, ac ati). I ddatrys y broblem hon, gallwch ddefnyddio un o ddau ddull:
- de-gliciwch ar bennawd y golofn gyda'r data ffynhonnell a dewiswch y gorchymyn Ychwanegu colofn o enghreifftiau (Ychwanegu colofn o enghreifftiau);
- dewiswch un neu fwy o golofnau gyda data ac ar y tab Ychwanegu colofn dewis tîm Colofn o enghreifftiau. Yma, yn y gwymplen, gallwch nodi a oes angen dadansoddi pob un o'r colofnau dethol neu ddim ond.
Yna mae popeth yn syml - yn y golofn sy'n ymddangos ar y dde, rydyn ni'n dechrau nodi enghreifftiau o'r canlyniadau dymunol, ac mae'r deallusrwydd artiffisial sydd wedi'i ymgorffori yn Power Query yn ceisio deall ein rhesymeg trawsnewid a pharhau ymhellach ar ei ben ei hun:
Gyda llaw, gallwch chi nodi'r opsiynau cywir mewn unrhyw gelloedd yn y golofn hon, hy nid o reidrwydd o'r brig i lawr ac mewn rhes. Hefyd, gallwch chi ychwanegu neu dynnu colofnau o'r dadansoddiad yn hawdd yn ddiweddarach gan ddefnyddio'r blychau ticio yn y bar teitl.
Rhowch sylw i'r fformiwla ar frig y ffenestr - dyma beth mae Smart Power Query yn ei greu i gael y canlyniadau sydd eu hangen arnom. Dyma, gyda llaw, yw'r gwahaniaeth sylfaenol rhwng yr offeryn hwn a Llenwi ar unwaith yn Excel. Mae llenwi sydyn yn gweithio fel “blwch du” – nid ydynt yn dangos rhesymeg y trawsnewidiadau i ni, ond yn syml yn rhoi canlyniadau parod ac rydym yn eu cymryd yn ganiataol. Yma mae popeth yn dryloyw a gallwch chi bob amser ddeall yn hollol glir beth yn union sy'n digwydd gyda'r data.
Os gwelwch fod Power Query “wedi dal y syniad”, yna gallwch chi wasgu'r botwm yn ddiogel OK neu lwybr byr bysellfwrdd Ctrl+Rhowch - bydd colofn arfer gyda fformiwla a ddyfeisiwyd gan Power Query yn cael ei chreu. Gyda llaw, gellir ei olygu'n hawdd yn ddiweddarach fel colofn reolaidd wedi'i chreu â llaw (gyda'r gorchymyn Ychwanegu Colofn - Colofn Custom) trwy glicio ar yr eicon gêr i'r dde o enw'r cam:
Enghraifft 2: Achos fel mewn brawddegau
Os de-gliciwch ar bennawd y golofn gyda thestun a dewiswch y gorchymyn Trawsnewid (Trawsnewid), yna gallwch weld tri gorchymyn sy'n gyfrifol am newid y gofrestr:
Cyfleus ac oer, ond yn y rhestr hon, er enghraifft, rwyf yn bersonol bob amser wedi bod heb un opsiwn arall - achos fel mewn brawddegau, pan nad yw priflythrennu (cyfalaf) yn dod yn lythyren gyntaf ym mhob gair, ond dim ond y llythyren gyntaf yn y gell, a gweddill y testun pan fydd Hwn yn cael ei arddangos mewn llythrennau bach (bach).
Mae'r nodwedd goll hon yn hawdd i'w gweithredu gyda deallusrwydd artiffisial Colofnau o enghreifftiau – nodwch un neu ddau o opsiynau er mwyn i Power Query barhau yn yr un ysbryd:
Fel fformiwla yma, mae Power Query yn defnyddio criw o swyddogaethau Testun.Upper и Testun.Is, trosi testun i briflythrennau a llythrennau bach, yn y drefn honno, a swyddogaethau Testun.Dechrau и Testun.Mid – analogau o swyddogaethau Excel LEFT a PSTR, yn gallu echdynnu is-linyn o'r testun o'r chwith ac o'r canol.
Enghraifft 3. Amnewid geiriau
Weithiau, wrth brosesu'r data a dderbyniwyd, mae'n dod yn angenrheidiol i aildrefnu'r geiriau yn y celloedd mewn dilyniant penodol. Wrth gwrs, gallwch chi rannu'r golofn yn golofnau geiriau ar wahân gan y gwahanydd ac yna ei gludo yn ôl yn y drefn benodol (peidiwch ag anghofio ychwanegu bylchau), ond gyda chymorth yr offeryn Colofn o enghreifftiau bydd popeth yn llawer haws:
Enghraifft 4: Dim ond rhifau
Tasg hanfodol iawn arall yw tynnu rhifau (rhifau) yn unig allan o gynnwys y gell. Fel o'r blaen, ar ôl llwytho data i Power Query, ewch i'r tab Ychwanegu colofn – Colofn o enghreifftiau a llenwi cwpl o gelloedd â llaw fel bod y rhaglen yn deall beth yn union yr ydym am ei gael:
Bingo!
Unwaith eto, mae'n werth edrych ar frig y ffenestr i wneud yn siŵr bod Query wedi cynhyrchu'r fformiwla'n gywir - yn yr achos hwn mae'n cynnwys ffwythiant Testun. Dewiswch, sydd, fel y gallech ddyfalu, yn tynnu'r nodau a roddwyd o'r testun ffynhonnell yn ôl y rhestr. Yn dilyn hynny, mae'n hawdd golygu'r rhestr hon, wrth gwrs, yn y bar fformiwla os oes angen.
Enghraifft 5: Testun yn unig
Yn yr un modd â'r enghraifft flaenorol, gallwch dynnu allan ac i'r gwrthwyneb - dim ond y testun, dileu pob rhif, atalnodau, ac ati.
Yn yr achos hwn, defnyddir swyddogaeth sydd eisoes gyferbyn o ran ystyr - Text.Remove, sy'n tynnu nodau o'r llinyn gwreiddiol yn ôl rhestr benodol.
Enghraifft 6: Tynnu data o uwd alffaniwmerig
Gall Power Query hefyd helpu mewn achosion anoddach, pan fydd angen i chi dynnu gwybodaeth ddefnyddiol o'r uwd alffaniwmerig mewn cell, er enghraifft, cael rhif y cyfrif o'r disgrifiad o ddiben y taliad ar gyfriflen banc:
Sylwch y gall y fformiwla trosi a gynhyrchir gan Power Query fod yn eithaf cymhleth:
Er hwylustod i'w ddarllen a'i ddeall, gellir ei drawsnewid yn ffurf lawer mwy call gan ddefnyddio gwasanaeth ar-lein rhad ac am ddim. Fformatiwr Ymholiad Pŵer:
Peth handi iawn - parch at y crewyr!
Enghraifft 7: Trosi dyddiadau
Offeryn Colofn o enghreifftiau gellir ei gymhwyso i golofnau dyddiad neu datetime hefyd. Pan fyddwch chi'n nodi digidau cyntaf dyddiad, bydd Power Query yn ddefnyddiol yn dangos rhestr o'r holl opsiynau trosi posibl:
Felly gallwch chi drosi'r dyddiad gwreiddiol yn hawdd i unrhyw fformat egsotig, fel "diwrnod-mis-dydd":
Enghraifft 8: Categoreiddio
Os byddwn yn defnyddio'r offeryn Colofn o enghreifftiau i golofn gyda data rhifol, mae'n gweithio'n wahanol. Tybiwch fod gennym ganlyniadau profion gweithwyr wedi'u llwytho i mewn i Power Query (sgoriau amodol yn yr ystod 0-100) ac rydym yn defnyddio'r graddiad amodol canlynol:
- Meistri – y rhai a sgoriodd fwy na 90
- Arbenigwyr - sgorio o 70 i 90
- Defnyddwyr - o 30 i 70
- Dechreuwyr – y rhai a sgoriodd lai na 30
Os byddwn yn ychwanegu colofn o'r enghreifftiau at y rhestr ac yn dechrau trefnu'r graddiadau hyn â llaw, yna yn fuan iawn bydd Power Query yn codi ein syniad ac yn ychwanegu colofn gyda fformiwla, lle mae gweithredwyr yn nythu i'w gilydd if bydd rhesymeg yn cael ei gweithredu, yn debyg iawn i'r hyn sydd ei angen arnom:
Unwaith eto, ni allwch bwyso ar y sefyllfa i'r diwedd, ond cliciwch ar OK ac yna cywiro'r gwerthoedd trothwy sydd eisoes yn y fformiwla - mae'n gyflymach fel hyn:
Casgliadau
Yn sicr offeryn Colofn o enghreifftiau nad yw’n “bilsen hud” ac, yn hwyr neu’n hwyrach, bydd sefyllfaoedd ansafonol neu achosion wedi’u hesgeuluso’n arbennig o “fferm ar y cyd” yn y data, pan fydd Power Query yn methu ac ni fydd yn gallu gweithio allan yr hyn yr ydym ei eisiau yn gywir i ni. Fodd bynnag, fel offeryn ategol, mae'n dda iawn. Hefyd, trwy astudio'r fformiwlâu a gynhyrchwyd ganddo, gallwch ehangu eich gwybodaeth am swyddogaethau'r iaith M, a fydd bob amser yn ddefnyddiol yn y dyfodol.
- Dosrannu Testun gyda Mynegiadau Rheolaidd (RegExp) mewn Power Query
- Chwiliad testun niwlog yn Power Query
- Flash Llenwch mewn Microsoft Excel