vsebina
Eden najbolj gledanih videoposnetkov na mojem YouTubovem kanalu je videoposnetek o Flash Fillu v programu Microsoft Excel. Bistvo tega orodja je, da če morate nekako preoblikovati svoje izvorne podatke, morate le začeti vnašati rezultat, ki ga želite dobiti, v sosednji stolpec. Po več ročno vnesenih celicah (običajno sta dovolj 2-3) bo Excel "razumel" logiko transformacij, ki jih potrebujete, in samodejno nadaljeval, kar ste vnesli, ter namesto vas opravil vse monotono delo:
Kvintesenca učinkovitosti. Čarobni gumb »naredi prav«, ki ga imamo vsi tako radi, kajne?
Pravzaprav obstaja analog takega orodja v Power Queryju - tam se imenuje Stolpec iz primerov (Stolpec iz primerov). Pravzaprav je to majhna umetna inteligenca, vgrajena v Power Query, ki se lahko hitro uči iz vaših podatkov in jih nato preoblikuje. Oglejmo si podrobneje njegove zmogljivosti v več praktičnih scenarijih, da bomo razumeli, kje nam lahko koristi pri resničnih nalogah.
Primer 1. Lepljenje/rezanje besedila
Recimo, da imamo v Excelu tako "pametno" tabelo s podatki o zaposlenih:
Naložite ga v Power Query na standarden način – z gumbom Iz tabele/razpona tab datum (Podatki — iz tabele/razpona).
Recimo, da moramo dodati stolpec s priimki in začetnicami za vsakega zaposlenega (Ivanov SV za prvega zaposlenega itd.). Za rešitev te težave lahko uporabite eno od dveh metod:
- z desno miškino tipko kliknite naslov stolpca z izvornimi podatki in izberite ukaz Dodajte stolpec iz primerov (Dodaj stolpec iz primerov);
- izberite enega ali več stolpcev s podatki in na zavihku Dodajanje stolpca izberite ekipo Stolpec iz primerov. Tukaj lahko na spustnem seznamu določite, ali je treba analizirati vse ali samo izbrane stolpce.
Potem je vse preprosto – v stolpec, ki se pojavi na desni, začnemo vnašati primere želenih rezultatov, umetna inteligenca, vgrajena v Power Query, pa poskuša razumeti našo logiko transformacije in nadaljevati sama:
Mimogrede, pravilne možnosti lahko vnesete v katero koli celico tega stolpca, torej ne nujno od zgoraj navzdol in v vrstici. Prav tako lahko pozneje preprosto dodate ali odstranite stolpce iz analize s potrditvenimi polji v naslovni vrstici.
Bodite pozorni na formulo na vrhu okna – to ustvari pametni Power Query, da dobimo rezultate, ki jih potrebujemo. Mimogrede, to je temeljna razlika med tem orodjem in Takojšnje polnjenje v Excelu. Takojšnje polnjenje deluje kot »črna skrinjica« – ne pokažejo nam logike transformacij, ampak preprosto dajo že pripravljene rezultate, ki jih jemljemo za samoumevne. Tukaj je vse pregledno in vedno lahko popolnoma jasno razumete, kaj se točno dogaja s podatki.
Če vidite, da je Power Query "ujel idejo", potem lahko mirno pritisnete gumb OK ali bližnjico na tipkovnici Ctrl+Vnesite – ustvarjen bo stolpec po meri s formulo, ki jo je izumil Power Query. Mimogrede, pozneje ga je mogoče enostavno urejati kot navaden ročno ustvarjen stolpec (z ukazom Dodajanje stolpca – stolpec po meri), tako da kliknete ikono zobnika na desni strani imena koraka:
Primer 2: Primer kot v stavkih
Če z desno miškino tipko kliknete naslov stolpca z besedilom in izberete ukaz Preoblikovanje (preobrazba), potem lahko vidite tri ukaze, odgovorne za spreminjanje registra:
Priročno in kul, toda na tem seznamu mi osebno na primer vedno manjka še ena možnost – primer kot v stavkih, ko velika črka (velika) ne postane prva črka v vsaki besedi, ampak le prva črka v celici, in preostali del besedila, ko je To prikazano z malimi črkami.
To manjkajočo funkcijo je enostavno implementirati z umetno inteligenco Stolpci iz primerov – samo vnesite nekaj možnosti za Power Query, da nadaljujete v istem duhu:
Kot formula tukaj Power Query uporablja kup funkcij Besedilo.Zgornji и Besedilo.Nižje, pretvorbo besedila v velike oziroma male črke in funkcije Besedilo. Začni и Besedilo.Sred – analogi Excelovih funkcij LEFT in PSTR, ki lahko izvlečejo podniz iz besedila z leve in s sredine.
Primer 3. Permutacija besed
Včasih je pri obdelavi prejetih podatkov potrebno preurediti besede v celicah v določenem zaporedju. Seveda lahko stolpec razdelite na ločene besedne stolpce z ločilom in ga nato zlepite nazaj v določenem vrstnem redu (ne pozabite dodati presledkov), vendar s pomočjo orodja Stolpec iz primerov vse bo veliko lažje:
Primer 4: Samo številke
Druga zelo pomembna naloga je izvleči samo številke (številke) iz vsebine celice. Kot prej, po nalaganju podatkov v Power Query pojdite na zavihek Dodajanje stolpca – stolpec iz primerov in ročno izpolnite nekaj celic, da bo program razumel, kaj točno želimo dobiti:
Bingo!
Ponovno je vredno pogledati na vrh okna, da se prepričate, ali je Query pravilno ustvaril formulo – v tem primeru vsebuje funkcijo Besedilo. Izberite, ki, kot morda ugibate, izvleče dane znake iz izvornega besedila glede na seznam. Kasneje lahko ta seznam po potrebi enostavno uredite v vrstici s formulami.
Primer 5: samo besedilo
Podobno kot v prejšnjem primeru lahko izvlečete in obratno – samo besedilo, izbrišete vse številke, ločila ipd.
V tem primeru se uporabi že po pomenu nasprotna funkcija – Text.Remove, ki odstrani znake iz prvotnega niza glede na dani seznam.
Primer 6: Izvleček podatkov iz alfanumerične kaše
Power Query lahko pomaga tudi v težjih primerih, ko morate iz alfanumerične kaše v celici izluščiti koristne informacije, na primer pridobiti številko računa iz opisa namena plačila na bančnem izpisku:
Upoštevajte, da je formula za pretvorbo, ki jo ustvari Power Query, lahko precej zapletena:
Za lažje branje in razumevanje ga je mogoče pretvoriti v veliko bolj razumno obliko z uporabo brezplačne spletne storitve. Oblikovalnik Power Query:
Zelo priročna stvar – spoštovanje ustvarjalcem!
Primer 7: Pretvarjanje datumov
Orodje Stolpec iz primerov lahko uporabite tudi za stolpce datuma ali časa. Ko vnesete prve števke datuma, bo Power Query koristno prikazal seznam vseh možnih možnosti pretvorbe:
Tako lahko preprosto pretvorite izvirni datum v katero koli eksotično obliko, na primer »leto-mesec-dan«:
Primer 8: Kategorizacija
Če uporabimo orodje Stolpec iz primerov v stolpec s številskimi podatki, deluje drugače. Recimo, da imamo rezultate testiranja zaposlenih naložene v Power Query (pogojni rezultati v razponu 0–100) in uporabimo naslednje pogojno stopnjevanje:
- Masters – tisti, ki so dosegli več kot 90
- Strokovnjaki – ocenjeni od 70 do 90
- Uporabniki – od 30 do 70
- Začetniki – tisti, ki so dosegli manj kot 30 točk
Če na seznam dodamo stolpec iz primerov in začnemo te gradacije urejati ročno, bo zelo kmalu Power Query povzel našo idejo in dodal stolpec s formulo, kjer so operatorji ugnezdeni drug v drugega if implementirana bo logika, zelo podobna tisti, ki jo potrebujemo:
Še enkrat, situacije ne morete pritisniti do konca, ampak kliknite OK in nato popravite vrednosti praga, ki so že v formuli – tako je hitreje:
Sklepi
Vsekakor orodje Stolpec iz primerov ni "čarobna tableta" in prej ali slej se bodo pojavile nestandardne situacije ali posebej zanemarjeni primeri "kolektivne kmetije" v podatkih, ko bo Power Query odpovedal in ne bo mogel delati, kar želimo pravilno za nas. Vendar pa je kot pomožno orodje zelo dobro. Poleg tega lahko s preučevanjem formul, ki jih je ustvaril, razširite svoje znanje o funkcijah jezika M, kar vam bo v prihodnosti vedno prišlo prav.
- Razčlenjevanje besedila z regularnimi izrazi (RegExp) v Power Query
- Iskanje mehkega besedila v Power Query
- Flash Fill v Microsoft Excelu