sadržaj
Formulacija problema
Pogledajmo lijepo rješenje za jednu od vrlo standardnih situacija s kojima se većina korisnika Excela suoči prije ili kasnije: potrebno je 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 prodajnigdje se nalazi tabela podataka:
Broj redova (redova) u tabelama je, naravno, različit, ali je skup kolona svuda standardan.
Zadatak: prikupiti podatke iz svih fajlova u jednu knjigu sa naknadnim automatskim ažuriranjem prilikom dodavanja ili brisanja gradskih fajlova ili redova u tabelama. Prema konačnoj konsolidovanoj tabeli, tada će biti moguće napraviti bilo kakve izveštaje, pivot tabele, filter-sortiranje podataka, itd. Glavna stvar je da budete u mogućnosti da prikupljate.
Mi biramo oružje
Za rješenje nam je potrebna najnovija verzija Excela 2016 (potrebna funkcionalnost je već ugrađena u njega prema zadanim postavkama) ili prethodne verzije Excela 2010-2013 s instaliranim besplatnim dodatkom power query 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 dalje čitati (šalim se). U starijim verzijama Excela, takav zadatak se može postići samo programiranjem makronaredbe u Visual Basicu (što je vrlo teško za početnike) ili monotonim ručnim kopiranjem (koje traje dugo i stvara greške).
Korak 1. Uvezite jednu datoteku kao uzorak
Prvo, hajde da uvezemo podatke iz jedne radne sveske kao primer, tako da Excel „pokupi ideju“. Da biste to uradili, kreirajte novu praznu radnu svesku i…
- ako imate Excel 2016, otvorite karticu podaci i onda 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 power query i odaberite na njemu Iz datoteke – Iz knjige (Iz datoteke — Iz Excela)
Zatim u prozoru koji se otvori idite u naš folder sa izvještajima i odaberite bilo koji gradski fajl (nije bitno koji, jer su svi tipični). Nakon nekoliko sekundi trebao bi se pojaviti prozor Navigator u kojem na lijevoj strani trebate odabrati list koji nam je potreban (Prodaja), a sa desne strane će se prikazati njegov sadržaj:
Ako kliknete na dugme u donjem desnom uglu ovog prozora Preuzimanje (Učitaj), tada će tabela biti odmah uvezena na list u svom originalnom obliku. Za jedan fajl, ovo je dobro, ali moramo učitati mnogo takvih fajlova, pa ćemo ići malo drugačije i kliknuti na dugme korekcija (Edit). Nakon toga, Power Query uređivač upita bi trebao biti prikazan u posebnom prozoru sa našim podacima iz knjige:
Ovo je veoma moćan alat koji vam omogućava da „dovršite“ tabelu do prikaza koji nam je potreban. Čak bi i površan opis svih njegovih funkcija zauzeo stotinjak stranica, ali, ako vrlo kratko, pomoću ovog prozora možete:
- filtrirati nepotrebne podatke, prazne redove, redove sa greškama
- sortirati podatke po jednoj ili više kolona
- osloboditi se ponavljanja
- podijelite ljepljivi tekst po kolonama (po graničnicima, broju znakova, itd.)
- uredite tekst (uklonite dodatne 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)
- transponovati (rotirati) tabele i proširiti dvodimenzionalne unakrsne tabele u ravne
- dodajte dodatne kolone u tablicu i koristite formule i funkcije u njima koristeći M jezik ugrađen u Power Query.
- ...
Na primjer, dodajmo kolonu s tekstualnim nazivom mjeseca u našu tabelu, kako bi kasnije bilo lakše napraviti izvještaje pivot tablice. Da biste to uradili, kliknite desnim tasterom miša na naslov kolone datumi izaberite komandu Duplikat kolone (duplikat kolone), a zatim kliknite desnim tasterom miša na zaglavlje duplirane kolone koja se pojavljuje i izaberite Komande Transform – Mjesec – Naziv mjeseca:
Za svaki red treba formirati novu kolonu sa tekstualnim nazivima mjeseca. Dvoklikom na naslov kolone možete ga preimenovati Datum kopiranja na udobnije Mjesec, npr.
Ako u nekim kolonama program nije sasvim ispravno prepoznao tip podataka, onda mu možete pomoći klikom na ikonu formata na lijevoj strani svake kolone:
Možete isključiti linije s greškama ili praznim redovima, kao i nepotrebne menadžere ili kupce, koristeći jednostavan filter:
Štaviše, sve izvedene transformacije su fiksirane u desnom panelu, gdje se uvijek mogu vratiti (ukrstiti) 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 naš kreirani zahtjev pretvoriti 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 Prikaz i kliknite na dugme Napredni uređivač (Prikaz — Napredni uređivač). Trebalo bi da se otvori prozor u kojem će sve naše prethodne radnje biti napisane u obliku koda na M jeziku. Imajte na umu da je putanja do datoteke koju smo uvezli za primjer tvrdo kodirana u kodu:
Sada napravimo nekoliko podešavanja:
Njihovo značenje je jednostavno: prvi red (putanja datoteke)=> pretvara našu proceduru u funkciju s argumentom filepath, a ispod mijenjamo fiksnu putanju na vrijednost ove varijable.
Sve. Kliknite na završiti i trebao bi vidjeti ovo:
Ne bojte se da su podaci nestali – zapravo, sve je u redu, sve bi trebalo izgledati ovako 🙂 Uspješno smo kreirali našu prilagođenu funkciju, gdje se cijeli algoritam za uvoz i obradu podataka pamti bez vezivanja za određeni fajl . Ostaje mu dati razumljivije ime (npr getData) na tabli desno u polju Ime i možete požnjeti Početna — Zatvorite i preuzmite (Početna — Zatvori i učitaj). Imajte na umu da je putanja do datoteke koju smo uvezli za primjer tvrdo kodirana u kodu. Vratit ćete se u glavni prozor Microsoft Excela, ali na desnoj strani bi se trebao pojaviti panel sa kreiranom vezom na našu funkciju:
Korak 3. Prikupljanje svih datoteka
Sve najteže je iza, a ono prijatno i lako ostaje. Idite na karticu Podaci – Kreiraj upit – Iz datoteke – Iz mape (Podaci — Novi upit — Iz datoteke — Iz foldera) ili, ako imate Excel 2010-2013, slično kao na kartici power query. U prozoru koji se pojavi navedite mapu u kojoj se nalaze svi naši izvorni gradski fajlovi i kliknite OK. Sljedeći korak bi trebao otvoriti prozor u kojem će biti navedene sve Excel datoteke koje se nalaze u ovoj mapi (i njenim podmapama) i detalji za svaku od njih:
kliknite promjena (Edit) i ponovo ulazimo u poznati prozor uređivača upita.
Sada trebamo dodati još jednu kolonu u našu tablicu s našom kreiranom funkcijom, koja će „povući“ podatke iz svake datoteke. Da biste to učinili, idite na karticu Dodaj kolonu – Prilagođena kolona (Dodaj kolonu — Dodaj prilagođenu kolonu) i u prozoru koji se pojavi unesite našu funkciju getData, navodeći za to kao argument punu putanju do svake datoteke:
Posle klika na OK kreiranu kolonu treba dodati u našu tabelu sa desne strane.
Sada izbrišemo sve nepotrebne kolone (kao u Excelu, pomoću desnog dugmeta miša – ukloniti), ostavljajući samo dodanu kolonu i kolonu sa imenom datoteke, jer će ovo ime (tačnije grad) biti korisno imati u ukupnim podacima za svaki red.
A sada “vau trenutak” – kliknite na ikonu sa vlastitim strelicama u gornjem desnom uglu dodane kolone sa našom funkcijom:
… opozovite izbor Koristite originalno ime kolone kao prefiks (Koristite originalno ime kolone kao prefiks)i kliknite OK. A naša funkcija će učitati i obraditi podatke iz svake datoteke, prateći snimljeni algoritam i prikupljajući sve u zajedničku tablicu:
Za potpunu ljepotu, također možete ukloniti .xlsx ekstenzije iz prve kolone sa nazivima datoteka – standardnom zamjenom sa “ništa” (desni klik na zaglavlje kolone – Zamjenik) i preimenujte ovu kolonu u grad. I također ispravite format podataka u koloni s datumom.
Sve! 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 u trenutni Excel list u formatu “pametna tabela”:
Stvorenu vezu i našu montažnu funkciju ne treba ni na koji način posebno pohranjivati – oni se spremaju zajedno s trenutnom datotekom na uobičajen način.
U budućnosti, sa bilo kakvim promjenama u folderu (dodavanje ili uklanjanje gradova) ili u fajlovima (promjena broja redova), bit će dovoljno da kliknete desnim tasterom miša direktno na tabelu ili na upit u desnom panelu i odaberete komanda Ažurirajte i sačuvajte (Osvježiti) – Power Query će ponovo „obnoviti“ sve podatke za nekoliko sekundi.
PS
Amandman. Nakon ažuriranja iz januara 2017., Power Query je naučio kako sam prikupiti Excel radne knjige, tj. više nema potrebe da pravite posebnu funkciju – to se dešava automatski. Dakle, drugi korak iz ovog članka više nije potreban i cijeli proces postaje znatno jednostavniji:
- Izabrati Kreiraj zahtjev – Iz datoteke – Iz mape – Odaberite mapu – OK
- Nakon što se pojavi lista datoteka, pritisnite promjena
- U prozoru uređivača upita proširite binarnu kolonu dvostrukom strelicom i odaberite naziv lista koji će se uzeti iz svake datoteke
I to je sve! Pjesma!
- Redizajn unakrsne tablice u ravnu pogodnu za izradu stožera
- Izrada animirane mjehuraste karte u Power Viewu
- Makro za sastavljanje listova iz različitih Excel datoteka u jednu