Turinys
Vienas iš labiausiai žiūrimų vaizdo įrašų mano „YouTube“ kanale yra vaizdo įrašas apie „Flash Fill“ programoje „Microsoft Excel“. Šio įrankio esmė yra ta, kad jei jums reikia kažkaip pakeisti šaltinio duomenis, jums tereikia pradėti vesti rezultatą, kurį norite gauti gretimame stulpelyje. Po kelių rankiniu būdu įvestų langelių (dažniausiai pakanka 2–3), „Excel“ „supras“ jums reikalingų transformacijų logiką ir automatiškai tęs tai, ką įvedėte, atlikdama visus monotoniškus darbus už jus:
Efektyvumo kvintesencija. Stebuklingas mygtukas „daryk tai teisingai“, kurį mes visi taip mėgstame, tiesa?
Tiesą sakant, Power Query yra tokio įrankio analogas - ten jis vadinamas Stulpelis iš pavyzdžių (Stulpelis iš pavyzdžių). Tiesą sakant, tai yra mažas dirbtinis intelektas, integruotas į „Power Query“, kuris gali greitai mokytis iš jūsų duomenų ir juos transformuoti. Pažvelkime atidžiau į jo galimybes keliuose praktiniuose scenarijuose, kad suprastume, kur jis gali būti naudingas atliekant realias užduotis.
1 pavyzdys. Teksto klijavimas/karpymas
Tarkime, kad turime tokią „protingą“ lentelę „Excel“ su duomenimis apie darbuotojus:
Įkelkite jį į „Power Query“ standartiniu būdu – mygtuku Iš lentelės/diapazono kortelė Duomenys (Duomenys – iš lentelės/diapazono).
Tarkime, turime pridėti stulpelį su kiekvieno darbuotojo pavardėmis ir inicialais (pirmojo darbuotojo Ivanovas SV ir kt.). Norėdami išspręsti šią problemą, galite naudoti vieną iš dviejų būdų:
- dešiniuoju pelės mygtuku spustelėkite stulpelio antraštę su šaltinio duomenimis ir pasirinkite komandą Pridėkite stulpelį iš pavyzdžių (Pridėti stulpelį iš pavyzdžių);
- pasirinkite vieną ar daugiau stulpelių su duomenimis ir skirtuke Stulpelio pridėjimas pasirinkti komandą Stulpelis iš pavyzdžių. Čia, išskleidžiamajame sąraše, galite nurodyti, ar reikia analizuoti visus ar tik pasirinktus stulpelius.
Tada viskas paprasta – dešinėje pasirodžiusiame stulpelyje pradedame vesti norimų rezultatų pavyzdžius, o Power Query įmontuotas dirbtinis intelektas bando suprasti mūsų transformacijos logiką ir tęsti toliau savarankiškai:
Beje, teisingas parinktis galite įvesti bet kuriame šio stulpelio langelyje, ty nebūtinai iš viršaus į apačią ir iš eilės. Be to, vėliau galėsite lengvai pridėti arba pašalinti stulpelius iš analizės, naudodami žymimuosius laukelius pavadinimo juostoje.
Atkreipkite dėmesį į formulę lango viršuje – štai ką išmanioji „Power Query“ sukuria, kad gautų mums reikalingus rezultatus. Tai, beje, yra esminis skirtumas tarp šios priemonės ir Momentinis užpildymas programoje Excel. Momentinis užpildymas veikia kaip „juodoji dėžė“ – jie mums neparodo transformacijų logikos, o tiesiog duoda jau paruoštus rezultatus ir mes juos laikome savaime suprantamais dalykais. Čia viskas yra skaidru ir jūs visada galite visiškai aiškiai suprasti, kas tiksliai vyksta su duomenimis.
Jei matote, kad „Power Query“ „pagavo idėją“, galite saugiai paspausti mygtuką OK arba spartusis klavišas "Ctrl"+įeiti – bus sukurtas pasirinktinis stulpelis su Power Query sugalvota formule. Beje, vėliau jį galima lengvai redaguoti kaip įprastą rankiniu būdu sukurtą stulpelį (su komanda Stulpelio pridėjimas – pasirinktinis stulpelis) spustelėdami krumpliaračio piktogramą veiksmo pavadinimo dešinėje:
2 pavyzdys: atvejis kaip sakiniuose
Jei dešiniuoju pelės mygtuku spustelėsite stulpelio antraštę su tekstu ir pasirinkite komandą Transformacija (Transformacija), tada galite pamatyti tris komandas, atsakingas už registro keitimą:
Patogu ir šaunu, bet, pavyzdžiui, šiame sąraše man asmeniškai visada trūko dar vieno varianto – didžiosios ir mažosios raidės kaip sakiniuose, kai didžioji raidė (didžioji raidė) tampa ne pirma kiekvieno žodžio raide, o tik pirmąja raide langelyje, likusį tekstą, kai Tai rodoma mažosiomis (mažosiomis) raidėmis.
Šią trūkstamą funkciją lengva įgyvendinti naudojant dirbtinį intelektą Stulpeliai iš pavyzdžių – tiesiog įveskite kelias Power Query parinktis, kad tęstumėte ta pačia dvasia:
Kaip formulė čia Power Query naudoja daugybę funkcijų Tekstas.Viršutinė и Tekstas.Apatinis, konvertuoja tekstą į didžiąsias ir mažąsias raides, atitinkamai ir funkcijas Tekstas.Pradėti и Tekstas.Vidur – Excel funkcijų KAIRĖS ir PSTR analogai, galintys iš teksto iš kairės ir iš vidurio išgauti poeilutę.
3 pavyzdys. Žodžių permutacija
Kartais, apdorojant gautus duomenis, iškyla poreikis perrikiuoti žodžius langeliuose tam tikra seka. Žinoma, galite atskirtuvu padalyti stulpelį į atskirus žodžių stulpelius ir suklijuoti atgal nurodyta tvarka (nepamirškite pridėti tarpų), bet įrankio pagalba Stulpelis iš pavyzdžių viskas bus daug lengviau:
4 pavyzdys: tik skaičiai
Kita labai svarbi užduotis – iš ląstelės turinio ištraukti tik skaičius (skaičius). Kaip ir anksčiau, įkėlę duomenis į „Power Query“, eikite į skirtuką Stulpelio pridėjimas – stulpelis iš pavyzdžių ir rankiniu būdu užpildykite keletą langelių, kad programa suprastų, ką tiksliai norime gauti:
Bingo!
Vėlgi verta pažvelgti į lango viršų, kad įsitikintumėte, jog užklausa teisingai sugeneravo formulę – šiuo atveju joje yra funkcija Tekstas. Pasirinkite, kuri, kaip galima spėti, pagal sąrašą ištraukia duotus simbolius iš šaltinio teksto. Vėliau šį sąrašą, žinoma, prireikus galima lengvai redaguoti formulės juostoje.
5 pavyzdys: tik tekstas
Panašiai kaip ir ankstesniame pavyzdyje, galima ištraukti ir atvirkščiai – tik tekstą, ištrinant visus skaičius, skyrybos ženklus ir pan.
Šiuo atveju naudojama jau priešingos reikšmės funkcija – Text.Remove, kuri pašalina simbolius iš pradinės eilutės pagal pateiktą sąrašą.
6 pavyzdys: Duomenų ištraukimas iš raidinės ir skaitmeninės košės
Power Query gali padėti ir sunkesniais atvejais, kai reikia ištraukti naudingą informaciją iš raidinės ir skaitmeninės košės langelyje, pavyzdžiui, gauti sąskaitos numerį iš mokėjimo paskirties aprašymo banko ataskaitoje:
Atminkite, kad Power Query sugeneruota konversijos formulė gali būti gana sudėtinga:
Kad būtų lengviau skaityti ir suprasti, jį galima paversti daug protingesne forma naudojant nemokamą internetinę paslaugą. „Power Query“ formatuotojas:
Labai patogus dalykas – pagarba kūrėjams!
7 pavyzdys: datų konvertavimas
Įrankis Stulpelis iš pavyzdžių taip pat gali būti taikomas datos arba datos laiko stulpeliams. Kai įvesite pirmuosius datos skaitmenis, „Power Query“ parodys visų galimų konvertavimo parinkčių sąrašą:
Taigi galite lengvai konvertuoti pradinę datą į bet kokį egzotišką formatą, pvz., "metai-mėnuo-diena":
8 pavyzdys: skirstymas į kategorijas
Jei naudosime įrankį Stulpelis iš pavyzdžių į stulpelį su skaitiniais duomenimis, jis veikia kitaip. Tarkime, kad į „Power Query“ įkėlėme darbuotojo testų rezultatus (sąlyginiai balai nuo 0 iki 100) ir naudojame šią sąlyginę gradaciją:
- Meistrai – surinkę daugiau nei 90 balų
- Ekspertai – surinko nuo 70 iki 90
- Vartotojų – nuo 30 iki 70
- Pradedantieji – surinkę mažiau nei 30 balų
Jei į sąrašą įtrauksime stulpelį iš pavyzdžių ir pradėsime tvarkyti šias gradacijas rankiniu būdu, labai greitai „Power Query“ paims mūsų idėją ir pridės stulpelį su formule, kur operatoriai yra vienas į kitą. if bus įgyvendinta logika, labai panaši į tai, ko mums reikia:
Vėlgi, jūs negalite spausti situacijos iki galo, bet spustelėkite OK o tada jau formulėje pataisykite slenksčių reikšmes – taip greičiau:
Išvados
Be abejo, įrankis Stulpelis iš pavyzdžių nėra „stebuklinga piliulė“ ir anksčiau ar vėliau duomenyse atsiras nestandartinių situacijų ar ypač apleistų „kolūkio“ atvejų, kai „Power Query“ suges ir negalės išsiaiškinti, ko norime. teisingai mums. Tačiau kaip pagalbinė priemonė ji labai gera. Be to, studijuodami jo sugeneruotas formules galite praplėsti savo žinias apie M kalbos funkcijas, kurios visada pravers ateityje.
- Teksto analizavimas naudojant reguliariąsias išraiškas (RegExp) Power Query
- Neaiškia teksto paieška Power Query
- „Flash“ užpildymas „Microsoft Excel“.