contents
Youn nan videyo ki pi gade sou chanèl YouTube mwen an se yon videyo sou Flash Fill nan Microsoft Excel. Sans nan zouti sa a se ke si ou bezwen yon jan kanmenm transfòme done sous ou a, Lè sa a, ou jis bezwen kòmanse tape rezilta a ou vle jwenn nan kolòn adjasan a. Apre plizyè selil tape manyèlman (anjeneral 2-3 yo ase), Excel pral "konprann" lojik transfòmasyon ou bezwen yo epi otomatikman kontinye sa ou te tape, ranpli tout travay monotone pou ou:
Quintessence de efikasite. Bouton majik "fè li byen" ke nou tout renmen anpil, pa vre?
An reyalite, gen yon analogue nan zouti sa a nan Power Query - se la li rele Kolòn nan egzanp (Kolòn ki soti nan Egzanp yo). An reyalite, sa a se yon ti entèlijans atifisyèl ki te bati nan Power Query ki ka byen vit aprann nan done ou ak Lè sa a, transfòme li. Ann pran yon gade pi pre nan kapasite li nan plizyè senaryo pratik yo nan lòd yo konprann ki kote li ka itil nou nan travay reyèl.
Egzanp 1. Kole/koupe tèks
Ann di nou gen yon tab "entelijan" nan Excel ak done sou anplwaye yo:
Chaje li nan Power Query nan fason estanda a - ak bouton an Soti nan Tablo / Ranje tab Done (Done - Soti nan Tablo / Ranje).
Sipoze nou bezwen ajoute yon kolòn ak siyati ak inisyal pou chak anplwaye (Ivanov SV pou premye anplwaye a, elatriye). Pou rezoud pwoblèm sa a, ou ka itilize youn nan de metòd:
- klike sou tit la kolòn ak done sous yo epi chwazi kòmandman an Ajoute kolòn nan egzanp (Ajoute kolòn nan egzanp);
- chwazi youn oswa plizyè kolòn ki gen done ak sou tab la Ajoute yon kolòn chwazi yon ekip Kolòn nan egzanp. Isit la, nan lis drop-down la, ou ka presize si tout oswa sèlman kolòn chwazi bezwen analize.
Lè sa a, tout bagay se senp - nan kolòn ki parèt sou bò dwat la, nou kòmanse antre nan egzanp rezilta yo vle, ak entèlijans atifisyèl ki bati nan Power Query eseye konprann lojik transfòmasyon nou an epi kontinye pi lwen poukont li:
By wout la, ou ka antre nan opsyon ki kòrèk yo nan nenpòt selil nan kolòn sa a, sa vle di pa nesesèman tèt-desann ak nan yon ranje. Epitou, ou ka fasilman ajoute oswa retire kolòn nan analiz la pita lè l sèvi avèk kaz ki nan ba tit la.
Peye atansyon sou fòmil ki anlè fenèt la - se sa ki entelijan Power Query kreye pou jwenn rezilta nou bezwen yo. Sa a, nan chemen an, se diferans ki genyen fondamantal ant zouti sa a ak Ranpli enstantane nan Excel. Instant ranpli travay tankou yon "bwat nwa" - yo pa montre nou lojik transfòmasyon yo, men tou senpleman bay rezilta pare yo epi nou pran yo pou yo akòde. Isit la tout bagay se transparan epi ou ka toujou absoliman klèman konprann sa egzakteman k ap pase ak done yo.
Si ou wè ke Power Query "pran lide a", Lè sa a, ou ka san danje peze bouton an OK oswa rakoursi klavye Ctrl+Antre nan – yon kolòn koutim ak yon fòmil envante pa Power Query pral kreye. By wout la, li ka pita fasil modifye kòm yon kolòn regilye manyèlman kreye (ak lòd la Ajoute yon kolòn - Custom kolòn) lè w klike sou ikòn angrenaj ki adwat non etap la:
Egzanp 2: Ka tankou nan fraz yo
Si w klike sou tit la kolòn ak tèks epi chwazi kòmandman an Transfòmasyon (transfòme), Lè sa a, ou ka wè twa kòmandman ki responsab pou chanje rejis la:
Pratik ak fre, men nan lis sa a, pou egzanp, mwen pèsonèlman te toujou manke yon lòt opsyon - ka tankou nan fraz, lè lèt majiskil (majuskil) vin pa premye lèt nan chak mo, men sèlman premye lèt la nan selil la, ak rès tèks la lè Sa a parèt an lèt miniskil (ti).
Karakteristik ki manke sa a fasil pou aplike ak entèlijans atifisyèl Kolòn ki soti nan egzanp – jis antre yon koup nan opsyon pou Power Query kontinye nan menm lespri a:
Kòm yon fòmil isit la, Power Query itilize yon pakèt fonksyon Tèks.Anwo и Tèks.Pi ba, konvèti tèks nan majiskil ak miniskil, respektivman, ak fonksyon Tèks.Kòmanse и Text.Mid – analogue nan fonksyon yo Excel LEFT ak PSTR, kapab ekstrè yon substring nan tèks la soti nan bò gòch la ak nan mitan an.
Egzanp 3. Pèmitasyon mo yo
Pafwa, lè w ap trete done yo resevwa, li vin nesesè pou reranje mo yo nan selil yo nan yon sekans bay yo. Natirèlman, ou ka divize kolòn nan an kolòn mo separe pa separasyon an epi apre kole li tounen nan lòd espesifye (pa bliye ajoute espas), men avèk èd nan zouti a. Kolòn nan egzanp tout bagay pral pi fasil:
Egzanp 4: Nimewo sèlman
Yon lòt travay trè enpòtan se rale sèlman nimewo (nimewo) nan sa ki nan selil la. Menm jan anvan, apre w fin chaje done nan Power Query, ale nan tab la Ajoute yon kolòn - Kolòn ki soti nan egzanp epi ranpli yon koup nan selil manyèlman pou ke pwogram nan konprann sa egzakteman nou vle jwenn:
Bingo!
Yon fwa ankò, li vo gade nan tèt fenèt la pou asire w ke Query te pwodwi fòmil la kòrèkteman - nan ka sa a li gen yon fonksyon. Tèks. Chwazi, ki, jan ou ta ka devine, ekstrè karaktè yo bay nan tèks sous la dapre lis la. Imedyatman, lis sa a, nan kou, ka fasilman modifye nan ba fòmil la si sa nesesè.
Egzanp 5: Tèks sèlman
Menm jan ak egzanp anvan an, ou ka rale soti ak vis vèrsa - sèlman tèks la, efase tout nimewo, mak ponktiyasyon, elatriye.
Nan ka sa a, yo itilize yon fonksyon ki deja opoze nan siyifikasyon - Text.Remove, ki retire karaktè nan fisèl orijinal la dapre yon lis bay yo.
Egzanp 6: Ekstrè done ki sòti nan yon labouyl alfanumerik
Power Query ka ede tou nan ka ki pi difisil, lè ou bezwen ekstrè enfòmasyon itil nan labouyl alfanumerik nan yon selil, pou egzanp, jwenn nimewo kont la nan deskripsyon objektif peman an sou yon deklarasyon labank:
Remake byen ke fòmil konvèsyon Power Query te pwodwi kapab byen konplèks:
Pou fasilite lekti ak konpreyansyon, li ka konvèti nan yon fòm pi plis lisid lè l sèvi avèk yon sèvis gratis sou entènèt. Power Query Formatter:
Bagay trè pratik - respè pou créateur yo!
Egzanp 7: Konvèti dat
Zouti Kolòn nan egzanp ka aplike nan kolòn dat oswa datetime tou. Lè ou antre premye chif yo nan yon dat, Power Query pral montre yon lis tout opsyon konvèsyon posib:
Se konsa, ou ka fasilman konvèti dat orijinal la nan nenpòt fòma ekzotik, tankou "ane-mwa-jou":
Egzanp 8: Kategori
Si nou itilize zouti a Kolòn nan egzanp nan yon kolòn ak done nimerik, li travay yon fason diferan. Sipoze nou gen rezilta tès anplwaye yo chaje nan Power Query (nòt kondisyonèl nan ranje 0-100) epi nou itilize gradyasyon kondisyonèl sa a:
- Masters - moun ki fè nòt plis pase 90
- Ekspè - bay nòt soti nan 70 a 90
- Itilizatè yo - soti nan 30 a 70
- Débutan - moun ki fè nòt mwens pase 30
Si nou ajoute yon kolòn nan egzanp yo nan lis la epi kòmanse fè aranjman pou gradyasyon sa yo manyèlman, Lè sa a, trè byento Power Query pral ranmase lide nou an epi ajoute yon kolòn ak yon fòmil, kote operatè yo enbrike youn nan lòt. if lojik pral aplike, trè menm jan ak sa nou bezwen:
Yon fwa ankò, ou pa ka peze sitiyasyon an nan fen a, men klike sou OK Lè sa a, korije valè papòt yo deja nan fòmil la - li pi vit nan fason sa a:
Konklizyon
Sètènman yon zouti Kolòn nan egzanp se pa yon "grenn majik" epi, pi bonè oswa pita, pral gen sitiyasyon ki pa estanda oswa ka espesyalman neglije nan yon "fèm kolektif" nan done yo, lè Power Query ap echwe epi yo pa yo pral kapab travay sou sa nou vle. kòrèkteman pou nou. Sepandan, kòm yon zouti oksilyè, li trè bon. Anplis de sa, lè w etidye fòmil li te pwodwi yo, ou ka elaji konesans ou sou fonksyon lang M, ki ap toujou itil nan tan kap vini an.
- Analiz tèks ak ekspresyon regilye (RegExp) nan Power Query
- Rechèch tèks flou nan Power Query
- Flash Ranpli nan Microsoft Excel