Sadržaj
Formulacija problema
Pogledajmo lijepo rješenje za jednu od vrlo standardnih situacija s kojima se većina korisnika programa Excel prije ili kasnije susreće: trebate brzo i automatski prikupiti podatke iz velikog broja datoteka u jednu konačnu tablicu.
Pretpostavimo da imamo sljedeću mapu koja sadrži nekoliko datoteka s podacima iz gradova podružnica:
Broj datoteka nije bitan i može se promijeniti u budućnosti. Svaka datoteka ima list pod nazivom Prodajegdje se nalazi tablica podataka:
Broj redaka (narudžbi) u tablicama je, naravno, različit, ali skup stupaca je svugdje standardan.
Zadatak: prikupiti podatke iz svih datoteka u jednu knjigu s naknadnim automatskim ažuriranjem prilikom dodavanja ili brisanja gradskih datoteka ili redaka u tablicama. Prema konačnoj konsolidiranoj tablici tada će biti moguće izraditi bilo kakva izvješća, zaokretne tablice, podatke za filtriranje sortiranja itd. Glavna stvar je biti u mogućnosti prikupljati.
Biramo oružje
Za rješenje nam je potrebna najnovija verzija programa Excel 2016 (potrebna funkcionalnost već je ugrađena u njega prema zadanim postavkama) ili prethodne verzije programa Excel 2010-2013 s instaliranim besplatnim dodatkom Upit snage od Microsofta (preuzmite ga ovdje). Power Query je super fleksibilan i super moćan alat za učitavanje podataka u Excel iz vanjskog svijeta, zatim njihovo uklanjanje i obradu. Power Query podržava gotovo sve postojeće izvore podataka – od tekstualnih datoteka do SQL-a, pa čak i Facebooka 🙂
Ako nemate Excel 2013 ili 2016, onda ne možete čitati dalje (šalim se). U starijim verzijama Excela takav se zadatak može izvršiti samo programiranjem makronaredbe u Visual Basicu (što je vrlo teško za početnike) ili monotonim ručnim kopiranjem (koje traje dugo i generira pogreške).
Korak 1. Uvezite jednu datoteku kao uzorak
Prvo, uvezimo podatke iz jedne radne knjige kao primjer, tako da Excel “pokupi ideju”. Da biste to učinili, izradite novu praznu radnu knjigu i…
- ako imate Excel 2016, otvorite karticu Datum a zatim Kreiraj upit – iz datoteke – iz knjige (Podaci — Novi upit — Iz datoteke — Iz Excela)
- ako imate Excel 2010-2013 s instaliranim dodatkom Power Query, otvorite karticu Upit snage i odaberite na njemu Iz datoteke – Iz knjige (Iz datoteke — Iz Excela)
Zatim u prozoru koji se otvori idite u našu mapu s izvješćima i odaberite bilo koju od gradskih datoteka (nije važno koju, jer su sve tipične). Nakon nekoliko sekundi trebao bi se pojaviti prozor Navigator, gdje trebate odabrati list koji nam je potreban (Prodaja) s lijeve strane, a njegov sadržaj će se prikazati s desne strane:
Ako kliknete na gumb u donjem desnom kutu ovog prozora Preuzimanje (Opterećenje), tada će se tablica odmah uvesti na list u izvornom obliku. Za jednu datoteku, ovo je dobro, ali moramo učitati mnogo takvih datoteka, pa ćemo ići malo drugačije i kliknuti gumb Korekcija (Uredi). Nakon toga, Power Query uređivač upita bi trebao biti prikazan u posebnom prozoru s našim podacima iz knjige:
Ovo je vrlo moćan alat koji vam omogućuje da "doradite" tablicu do prikaza koji nam je potreban. Čak i površan opis svih njegovih funkcija zauzeo bi stotinjak stranica, ali, ako vrlo kratko, pomoću ovog prozora možete:
- filtrirati nepotrebne podatke, prazne retke, retke s greškama
- sortirati podatke po jednom ili više stupaca
- osloboditi se ponavljanja
- podijeliti ljepljivi tekst po stupcima (po razdjelnicima, broju znakova itd.)
- složite tekst (uklonite suvišne razmake, ispravite velika i mala slova itd.)
- pretvoriti tipove podataka na sve moguće načine (pretvoriti brojeve poput teksta u normalne brojeve i obrnuto)
- transponirati (rotirati) tablice i proširiti dvodimenzionalne križne tablice u ravne
- dodajte dodatne stupce u tablicu i koristite formule i funkcije u njima pomoću jezika M ugrađenog u Power Query.
- ...
Na primjer, dodajmo stupac s tekstualnim nazivom mjeseca u našu tablicu, kako bi kasnije bilo lakše graditi izvješća zaokretne tablice. Da biste to učinili, desnom tipkom miša kliknite naslov stupca podacii odaberite naredbu Duplicirani stupac (Dvostruki stupac), a zatim desnom tipkom miša kliknite zaglavlje dupliciranog stupca koji se pojavi i odaberite Naredbe Transformacija – Mjesec – Naziv mjeseca:
Treba formirati novi stupac s tekstualnim nazivima mjeseci za svaki redak. Duplim pritiskom na naslov stupca možete ga preimenovati Kopiraj datum na udobniji Mjesec, npr.
Ako u nekim stupcima program nije točno prepoznao vrstu podataka, možete mu pomoći klikom na ikonu formata s lijeve strane svakog stupca:
Pomoću jednostavnog filtra možete isključiti retke s pogreškama ili prazne retke, kao i nepotrebne upravitelje ili kupce:
Štoviše, sve izvršene transformacije fiksirane su na desnoj ploči, gdje se uvijek mogu vratiti (križ) ili promijeniti svoje parametre (zupčanik):
Lagano i elegantno, zar ne?
Korak 2. Pretvorimo naš zahtjev u funkciju
Kako bismo naknadno ponovili sve transformacije podataka napravljene za svaku uvezenu knjigu, moramo pretvoriti naš stvoreni zahtjev u funkciju, koja će se zatim primijeniti na sve naše datoteke. To je zapravo vrlo jednostavno.
U uređivaču upita idite na karticu Pogled i kliknite gumb Napredni uređivač (Prikaz — napredni uređivač). Trebao bi se otvoriti prozor u kojem će biti ispisane sve naše prethodne akcije u obliku koda na M jeziku. Imajte na umu da je put do datoteke koju smo uvezli za primjer čvrsto kodiran u kodu:
Sada napravimo nekoliko prilagodbi:
Njihovo značenje je jednostavno: prvi red (putanja datoteke)=> pretvara našu proceduru u funkciju s argumentom put datoteke, a ispod mijenjamo fiksni put do vrijednosti ove varijable.
Svi. Kliknite na završiti i trebao bi vidjeti ovo:
Nemojte se bojati da su podaci nestali – zapravo, sve je u redu, sve bi trebalo izgledati ovako 🙂 Uspješno smo napravili našu prilagođenu funkciju, gdje se cijeli algoritam za uvoz i obradu podataka pamti bez vezivanja za određenu datoteku . Ostaje mu dati razumljivije ime (npr getData) na ploči s desne strane u polju Ime i možete žeti Početna — Zatvori i preuzmi (Početna — zatvori i učitaj). Imajte na umu da je put do datoteke koju smo uvezli za primjer čvrsto kodiran u kodu. Vratit ćete se u glavni prozor programa Microsoft Excel, ali s desne strane bi se trebala pojaviti ploča sa stvorenom vezom na našu funkciju:
Korak 3. Prikupljanje svih datoteka
Sve najteže je iza, ostaje ono ugodno i lagano. Idi na karticu Podaci – Stvori upit – Iz datoteke – Iz mape (Podaci — Novi upit — Iz datoteke — Iz mape) ili, ako imate Excel 2010-2013, slično kartici Upit snage. U prozoru koji se pojavi odredite mapu u kojoj se nalaze sve naše izvorne gradske datoteke i kliknite OK. Sljedeći korak trebao bi otvoriti prozor u kojem će biti navedene sve Excel datoteke pronađene u ovoj mapi (i njezinim podmapama) i detalji za svaku od njih:
Kliknite Promijeniti (Uredi) i opet dolazimo u poznati prozor uređivača upita.
Sada trebamo dodati još jedan stupac u našu tablicu s našom stvorenom funkcijom, koja će "povući" podatke iz svake datoteke. Da biste to učinili, idite na karticu Dodaj stupac – prilagođeni stupac (Dodaj stupac — Dodaj prilagođeni stupac) i u prozor koji se pojavi unesite našu funkciju getData, specificirajući mu kao argument puni put do svake datoteke:
Nakon što kliknete OK kreirani stupac treba dodati u našu tablicu s desne strane.
Sada izbrišite sve nepotrebne stupce (kao u Excelu, desnom tipkom miša – ukloniti), ostavljajući samo dodani stupac i stupac s nazivom datoteke, jer će taj naziv (točnije grad) biti korisno imati u ukupnim podacima za svaki red.
A sada “vau trenutak” – kliknite na ikonu s vlastitim strelicama u gornjem desnom kutu dodanog stupca s našom funkcijom:
… odznačiti Koristite izvorni naziv stupca kao prefiks (Koristite izvorni naziv stupca kao prefiks)i kliknite OK. A naša funkcija će učitati i obraditi podatke iz svake datoteke, slijedeći snimljeni algoritam i skupljajući sve u zajedničku tablicu:
Za potpunu ljepotu, također možete ukloniti ekstenzije .xlsx iz prvog stupca s nazivima datoteka – standardnom zamjenom s “ništa” (desni klik na zaglavlje stupca – Zamjena) i preimenujte ovaj stupac u Grad. Također ispravite format podataka u stupcu s datumom.
Svi! Kliknite na Početna – Zatvori i učitaj (Početna — Zatvori i učitaj). Svi podaci prikupljeni upitom za sve gradove bit će učitani na trenutnu Excel tablicu u formatu “pametne tablice”:
Stvorenu vezu i našu funkciju sklopa ne treba ni na koji način zasebno spremati – spremaju se zajedno s trenutnom datotekom na uobičajeni način.
Ubuduće, kod bilo kakvih promjena u mapi (dodavanje ili uklanjanje gradova) ili u datotekama (promjena broja redaka), bit će dovoljno kliknuti desnom tipkom miša izravno na tablicu ili na upit u desnom panelu i odabrati naredba Ažurirajte i spremite (Osvježiti) – Power Query će ponovno “obnoviti” sve podatke za nekoliko sekundi.
PS
Amandman. Nakon ažuriranja iz siječnja 2017., Power Query je naučio kako sam prikupljati Excel radne knjige, tj. više nema potrebe za izradom posebne funkcije – to se događa automatski. Dakle, drugi korak iz ovog članka više nije potreban i cijeli proces postaje osjetno jednostavniji:
- Odaberite Kreiraj zahtjev – Iz datoteke – Iz mape – Odaberite mapu – U redu
- Nakon što se pojavi popis datoteka, pritisnite Promijeniti
- U prozoru uređivača upita proširite stupac Binarno s dvostrukom strelicom i odaberite naziv lista koji će se uzeti iz svake datoteke
I to je sve! Pjesma!
- Redizajn crosstab-a u ravan primjeren za izradu pivot tablica
- Izrada animiranog mjehurićastog grafikona u Power Viewu
- Makro za sastavljanje listova iz različitih Excel datoteka u jednu