Sadržaj
Jedan od najgledanijih videa na mom YouTube kanalu je video o Flash Fillu u Microsoft Excelu. Bit ovog alata je da ako trebate nekako transformirati svoje izvorne podatke, tada samo trebate početi upisivati rezultat koji želite dobiti u susjedni stupac. Nakon nekoliko ručno utipkanih ćelija (obično su dovoljne 2-3), Excel će "shvatiti" logiku transformacija koje trebate i automatski nastaviti ono što ste upisali, dovršavajući sav monoton posao umjesto vas:
Suština učinkovitosti. Čarobni gumb "učini to kako treba" koji svi toliko volimo, zar ne?
Zapravo, postoji analog takvog alata u Power Queryju - tamo se zove Stupac iz primjera (Stupac iz primjera). Zapravo, ovo je mala umjetna inteligencija ugrađena u Power Query koja može brzo učiti iz vaših podataka i zatim ih transformirati. Pogledajmo pobliže njegove mogućnosti u nekoliko praktičnih scenarija kako bismo razumjeli gdje nam može biti od koristi u stvarnim zadacima.
Primjer 1. Lijepljenje/rezanje teksta
Recimo da imamo takvu “pametnu” tablicu u Excelu s podacima o zaposlenicima:
Učitajte ga u Power Query na standardni način – pomoću gumba Iz tablice/raspona kartica Datum (Podaci — iz tablice/raspona).
Pretpostavimo da trebamo dodati stupac s prezimenima i inicijalima za svakog zaposlenika (Ivanov SV za prvog zaposlenika itd.). Da biste riješili ovaj problem, možete koristiti jednu od dvije metode:
- desnom tipkom miša kliknite naslov stupca s izvornim podacima i odaberite naredbu Dodajte stupac iz primjera (Dodaj stupac iz primjera);
- odaberite jedan ili više stupaca s podacima i na kartici Dodavanje stupca izabrati tim Stupac iz primjera. Ovdje u padajućem popisu možete odrediti hoće li se analizirati svi ili samo odabrani stupci.
Zatim je sve jednostavno – u stupac koji se pojavi s desne strane počinjemo unositi primjere željenih rezultata, a umjetna inteligencija ugrađena u Power Query pokušava shvatiti našu logiku transformacije i sama nastaviti dalje:
Usput, ispravne opcije možete unijeti u bilo koju ćeliju ovog stupca, tj. ne nužno odozgo prema dolje i u nizu. Također, kasnije možete jednostavno dodati ili ukloniti stupce iz analize koristeći potvrdne okvire u naslovnoj traci.
Obratite pozornost na formulu na vrhu prozora – to je ono što pametni Power Query stvara kako bismo dobili rezultate koji su nam potrebni. Ovo je, usput, temeljna razlika između ovog alata i Trenutno punjenje u Excelu. Instant punjenje funkcionira poput “crne kutije” – ne pokazuje nam logiku transformacija, već jednostavno daje gotove rezultate koje mi uzimamo zdravo za gotovo. Ovdje je sve transparentno i uvijek možete potpuno jasno razumjeti što se točno događa s podacima.
Ako vidite da je Power Query "uhvatio ideju", tada možete sigurno pritisnuti gumb OK ili tipkovni prečac Ctrl+ući – stvorit će se prilagođeni stupac s formulom koju je izmislio Power Query. Usput, kasnije se može lako uređivati kao uobičajeni ručno kreirani stupac (naredbom Dodavanje stupca – prilagođeni stupac) klikom na ikonu zupčanika s desne strane naziva koraka:
Primjer 2: Padež kao u rečenicama
Ako desnom tipkom miša kliknete naslov stupca s tekstom i odaberete naredbu transformacija (Transformirati), tada možete vidjeti tri naredbe odgovorne za promjenu registra:
Zgodno i cool, ali na ovom popisu, na primjer, meni osobno je uvijek nedostajala još jedna opcija - slučaj kao u rečenicama, kada veliko slovo (veliko) ne postaje prvo slovo u svakoj riječi, već samo prvo slovo u ćeliji, i ostatak teksta kada se Ovo prikazuje malim (malim) slovima.
Ovu značajku koja nedostaje lako je implementirati pomoću umjetne inteligencije Stupci iz primjera – samo unesite nekoliko opcija kako bi Power Query nastavio u istom duhu:
Kao formula ovdje, Power Query koristi hrpu funkcija Tekst.Gornji и Tekst.Donji, pretvaranje teksta u velika i mala slova, te funkcije Tekst. Početak и Tekst.Sred – analozi Excel funkcija LEFT i PSTR, koji mogu izdvojiti podniz iz teksta s lijeve i srednje strane.
Primjer 3. Permutacija riječi
Ponekad, prilikom obrade primljenih podataka, postaje potrebno preurediti riječi u ćelijama u zadanom nizu. Naravno, možete razdvojnikom podijeliti stupac u zasebne stupce riječi i zatim ga ponovno zalijepiti navedenim redoslijedom (ne zaboravite dodati razmake), ali uz pomoć alata Stupac iz primjera sve će biti puno lakše:
Primjer 4: Samo brojevi
Drugi vrlo važan zadatak je izvući samo brojeve (brojeve) iz sadržaja ćelije. Kao i prije, nakon učitavanja podataka u Power Query, idite na karticu Dodavanje stupca – stupac iz primjera i ručno ispunite nekoliko ćelija kako bi program razumio što točno želimo dobiti:
Bingo!
Opet, vrijedi pogledati vrh prozora kako biste bili sigurni da je Query ispravno generirao formulu – u ovom slučaju sadrži funkciju Tekst. Odaberi, koji, kao što možete pretpostaviti, izdvaja zadane znakove iz izvornog teksta prema popisu. Nakon toga se ovaj popis, naravno, može lako uređivati u traci formule ako je potrebno.
Primjer 5: Samo tekst
Slično kao u prethodnom primjeru, možete izvlačiti i obrnuto – samo tekst, brisanje svih brojeva, interpunkcijskih znakova itd.
U ovom slučaju koristi se funkcija koja je već suprotna po značenju – Text.Remove, koja uklanja znakove iz izvornog niza prema zadanom popisu.
Primjer 6: Izdvajanje podataka iz alfanumeričke kaše
Power Query može pomoći i u težim slučajevima, kada iz alfanumeričke kaše u ćeliji trebate izvući korisne podatke, na primjer, dobiti broj računa iz opisa svrhe plaćanja na bankovnom izvodu:
Imajte na umu da formula pretvorbe koju generira Power Query može biti prilično složena:
Radi lakšeg čitanja i razumijevanja, može se pretvoriti u mnogo razumniji oblik korištenjem besplatne online usluge. Power Query Formater:
Vrlo zgodna stvar - svaka čast kreatorima!
Primjer 7: Pretvaranje datuma
Oruđe Stupac iz primjera može se primijeniti i na stupce datuma ili datuma i vremena. Kada unesete prve znamenke datuma, Power Query će korisno prikazati popis svih mogućih opcija pretvorbe:
Dakle, možete jednostavno pretvoriti izvorni datum u bilo koji egzotični format, kao što je "godina-mjesec-dan":
Primjer 8: Kategorizacija
Ako koristimo alat Stupac iz primjera u stupac s numeričkim podacima, radi drugačije. Pretpostavimo da imamo rezultate testiranja zaposlenika učitane u Power Query (uvjetni rezultati u rasponu 0-100) i koristimo sljedeću uvjetnu gradaciju:
- Masters – oni koji su postigli više od 90 bodova
- Stručnjaci – bodovi od 70 do 90
- Korisnici – od 30 do 70
- Početnici – oni koji su postigli manje od 30 bodova
Ako na popis dodamo stupac iz primjera i počnemo ručno slagati ove gradacije, vrlo brzo će Power Query prihvatiti našu ideju i dodati stupac s formulom, gdje su operatori ugniježđeni jedan u drugi if implementirat će se logika, vrlo slična onoj koja nam je potrebna:
Opet, ne možete pritisnuti situaciju do kraja, već kliknite na OK a zatim ispravite vrijednosti praga koje su već u formuli – brže je na ovaj način:
Zaključci
Svakako alat Stupac iz primjera nije "čarobna pilula" i, prije ili kasnije, doći će do nestandardnih situacija ili posebno zanemarenih slučajeva "zajedničke farme" u podacima, kada Power Query neće uspjeti i neće moći razraditi ono što želimo ispravno za nas. Međutim, kao pomoćno sredstvo, vrlo je dobro. Osim toga, proučavanjem formula koje je generirao možete proširiti svoje znanje o funkcijama jezika M, što će vam uvijek dobro doći u budućnosti.
- Raščlanjivanje teksta s regularnim izrazima (RegExp) u Power Queryju
- Pretraživanje neizrazitog teksta u Power Queryju
- Flash Fill u programu Microsoft Excel