Sisu
Minu YouTube'i kanali üks vaadatumaid videoid on video Flash Filli kohta Microsoft Excelis. Selle tööriista olemus seisneb selles, et kui teil on vaja oma lähteandmeid kuidagi ümber kujundada, peate lihtsalt hakkama külgnevasse veergu sisestama soovitud tulemust. Pärast mitut käsitsi sisestatud lahtrit (tavaliselt piisab 2–3-st) saab Excel "mõista" vajalike teisenduste loogikat ja jätkab automaatselt sisestatut, tehes kogu monotoonse töö teie eest ära:
Efektiivsuse kvintessents. Maagiline nupp „tee õigesti”, mis meile kõigile nii väga meeldib, eks?
Tegelikult on Power Querys sellise tööriista analoog olemas – seal seda nimetatakse Veerg näidetest (Veerg näidetest). Tegelikult on see Power Querysse sisse ehitatud väike tehisintellekt, mis saab teie andmetest kiiresti õppida ja neid seejärel muuta. Vaatame lähemalt selle võimalusi mitmes praktilises stsenaariumis, et mõista, kus see meile reaalsetes ülesannetes kasulik võib olla.
Näide 1. Teksti liimimine/lõikamine
Oletame, et meil on Excelis selline "tark" tabel töötajate andmetega:
Laadige see Power Querysse standardsel viisil – nupuga Tabelist/vahemikust tab kuupäev (Andmed – tabelist/vahemikust).
Oletame, et peame iga töötaja jaoks lisama veeru perekonnanimede ja initsiaalidega (esimese töötaja jaoks Ivanov SV jne). Selle probleemi lahendamiseks võite kasutada ühte kahest meetodist:
- paremklõpsake lähteandmetega veeru pealkirja ja valige käsk Lisa näidetest veerg (Lisa näidetest veerg);
- valige üks või mitu andmetega veergu ja vahekaardil Veeru lisamine vali meeskond Veerg näidetest. Siin saate ripploendis määrata, kas analüüsida tuleb kõiki või ainult valitud veerge.
Siis on kõik lihtne – paremale ilmuvasse veergu hakkame sisestama soovitud tulemuste näiteid ja Power Querysse sisseehitatud tehisintellekt püüab mõista meie transformatsiooniloogikat ja jätkata omaette:
Muide, õiged valikud saate sisestada selle veeru mis tahes lahtrisse, st mitte tingimata ülalt alla ja reas. Samuti saate hiljem hõlpsasti analüüsi veerge lisada või sealt eemaldada, kasutades tiitliribal märkeruutusid.
Pöörake tähelepanu akna ülaosas olevale valemile – see on see, mida nutikas Power Query loob vajalike tulemuste saamiseks. See, muide, on põhimõtteline erinevus selle tööriista ja Kohene täitmine Excelis. Kiirtäitmine toimib nagu “must kast” – need ei näita meile teisenduste loogikat, vaid annavad lihtsalt valmis tulemused ja me võtame neid iseenesestmõistetavana. Siin on kõik läbipaistev ja alati saab täiesti selgelt aru, mis andmetega täpselt toimub.
Kui näete, et Power Query sai idee kinni, võite julgelt nuppu vajutada OK või klaviatuuri otsetee Ctrl+sisene – luuakse Power Query leiutatud valemiga kohandatud veerg. Muide, seda saab hiljem hõlpsasti redigeerida tavalise käsitsi loodud veeruna (käsuga Veeru lisamine – kohandatud veerg), klõpsates sammu nimest paremal asuval hammasrattaikoonil:
Näide 2: Juhtum nagu lausetes
Kui teete tekstiga veeru pealkirjal paremklõpsu ja valite käsu Transformation (teisendus), siis näete kolme registri muutmise eest vastutavat käsku:
Mugav ja lahe, aga näiteks selles nimekirjas on mul isiklikult alati puudu olnud veel ühest võimalusest – suur- ja suurtäht nagu lausetes, kui suurtäht (suurtäht) ei muutu mitte iga sõna esimeseks, vaid ainult lahtri esimeseks täheks ja ülejäänud tekst, kui See kuvatakse väikeste (väikeste) tähtedega.
Seda puuduvat funktsiooni on tehisintellektiga lihtne rakendada Veerud näidetest – samas vaimus jätkamiseks sisestage Power Query jaoks paar valikut:
Valemina kasutab Power Query hunnikut funktsioone Tekst.Ülemine и Tekst.Alumine, teisendab teksti vastavalt suur- ja väiketähtedeks ning funktsioone Tekst.Alusta и Tekst.Kesk – Exceli funktsioonide LEFT ja PSTR analoogid, mis suudavad tekstist välja võtta alamstringi vasakult ja keskelt.
Näide 3. Sõnade permutatsioon
Mõnikord on vastuvõetud andmete töötlemisel vaja lahtrites olevaid sõnu etteantud järjestuses ümber paigutada. Muidugi saab veeru eraldaja abil eraldi sõnaveergudeks jagada ja siis määratud järjekorras tagasi liimida (ärge unustage tühikuid lisada), kuid tööriista abil Veerg näidetest kõik on palju lihtsam:
Näide 4: ainult numbrid
Teine väga oluline ülesanne on tõmmata lahtri sisust välja ainult numbrid (numbrid). Nagu varemgi, minge pärast andmete Power Querysse laadimist vahekaardile Veeru lisamine – näidete veerg ja täitke paar lahtrit käsitsi, et programm mõistaks, mida täpselt tahame saada:
Bingo!
Jällegi tasub vaadata akna ülaosa veendumaks, et päring genereeris valemi õigesti – antud juhul sisaldab see funktsiooni Tekst. Valige, mis, nagu arvata võib, võtab lähtetekstist välja antud märgid vastavalt loendile. Edaspidi saab seda loendit vajaduse korral muidugi valemiribal hõlpsasti redigeerida.
Näide 5: ainult tekst
Sarnaselt eelmise näitega saab välja tõmmata ja vastupidi – ainult tekst, kõigi numbrite kustutamine, kirjavahemärgid jne.
Sel juhul kasutatakse juba tähenduselt vastupidist funktsiooni – Text.Remove, mis eemaldab algsest stringist märgid etteantud loendi järgi.
Näide 6: Andmete eraldamine tähtnumbrilisest pudrust
Power Query aitab ka raskematel juhtudel, kui on vaja lahtris olevast tähtnumbrilisest pudrust kasulikku infot ammutada, näiteks saada kontonumber pangaväljavõttel olevast makse eesmärgi kirjeldusest:
Pange tähele, et Power Query loodud teisendusvalem võib olla üsna keeruline:
Lugemise ja mõistmise hõlbustamiseks saab selle tasuta võrguteenuse abil muuta palju mõistlikumaks vormiks. Power Query vormindaja:
Väga käepärane asi – respekt tegijatele!
Näide 7: Kuupäevade teisendamine
Vahend Veerg näidetest saab rakendada ka kuupäeva või kuupäeva ja kellaaja veergudele. Kui sisestate kuupäeva esimesed numbrid, kuvab Power Query kõigi võimalike teisendusvalikute loendi.
Nii saate hõlpsalt teisendada algse kuupäeva mis tahes eksootilisesse vormingusse, näiteks "aasta-kuu-päev":
Näide 8: Kategoriseerimine
Kui me kasutame tööriista Veerg näidetest arvandmetega veerule, töötab see teisiti. Oletame, et meil on Power Querysse laaditud töötajate testitulemused (tingimuslikud hinded vahemikus 0–100) ja kasutame järgmist tingimuslikku gradatsiooni:
- Meistrid – need, kes saavutasid rohkem kui 90 punkti
- Eksperdid – hinded 70–90
- Kasutajaid - 30 kuni 70
- Algajad – need, kes saavutasid alla 30 punkti
Kui lisame nimekirja näidetest veeru ja hakkame neid gradatsioone käsitsi järjestama, siis varsti võtab Power Query meie idee üles ja lisab valemiga veeru, kus operaatorid üksteise sisse pesastuvad if loogikat rakendatakse, mis on väga sarnane sellele, mida me vajame:
Jällegi ei saa te olukorda lõpuni vajutada, vaid klõpsake edasi OK ja seejärel parandage läviväärtused juba valemis – nii on kiirem:
Järeldused
Kindlasti tööriist Veerg näidetest ei ole "võlupill" ja varem või hiljem tuleb andmetesse ebastandardseid olukordi või eriti tähelepanuta jäetud "kolhoosi" juhtumeid, mil Power Query ebaõnnestub ja ei suuda välja töötada seda, mida me tahame. meie jaoks õigesti. Abivahendina on see aga väga hea. Lisaks saate tema loodud valemeid uurides laiendada oma teadmisi M-keele funktsioonide kohta, mis tulevad tulevikus alati kasuks.
- Teksti sõelumine regulaaravaldistega (RegExp) Power Querys
- Hägune tekstiotsing Power Querys
- Flash Täitke Microsoft Excelis