Izradite tablice s različitim zaglavljima iz više knjiga

Formulacija problema

Imamo nekoliko datoteka (u našem primjeru - 4 komada, u općem slučaju - koliko god želite) u jednoj mapi Izvješća:

Unutra ove datoteke izgledaju ovako:

Pri čemu:

• Uvijek se poziva list s podacima koji nam je potreban Fotografije, ali može biti bilo gdje u radnoj knjizi.
  
• S onu stranu lista Fotografije Svaka knjiga može imati druge listove.
• Tablice s podacima imaju različit broj redaka i mogu započeti drugim redom na radnom listu.
• Nazivi istih stupaca u različitim tablicama mogu se razlikovati (npr. Količina = Količina = Kol).
• Stupci u tablicama mogu biti raspoređeni različitim redoslijedom.

Zadatak: prikupiti podatke o prodaji iz svih datoteka s lista Fotografije u jednu zajedničku tablicu kako bi se na njoj naknadno gradila sažeta ili bilo koja druga analitika.

Korak 1. Priprema direktorija naziva stupaca

Prvo što treba učiniti je pripremiti referentnu knjigu sa svim mogućim opcijama za nazive stupaca i njihovo ispravno tumačenje:

Ovaj popis pretvaramo u dinamičku "pametnu" tablicu pomoću gumba Oblikuj kao tablicu na kartici Naslovna (Početna — Formatiraj kao tablicu) ili tipkovni prečac Ctrl+T i učitajte ga u Power Query s naredbom Podaci – iz tablice/raspona (Podaci — iz tablice/raspona). U novijim verzijama Excela preimenovan je u S lišćem (S lista).

U prozoru uređivača upita Power Query tradicionalno brišemo korak Promijenjena vrsta i umjesto njega dodajte novi korak klikom na gumb f_xu traci formule (ako nije vidljiv, možete ga omogućiti na kartici pregled) i tamo unesite formulu u ugrađeni Power Query jezik M:

=Table.ToRows(Izvor)

Ova naredba će pretvoriti onu učitanu u prethodnom koraku izvor referentnu tablicu u popis koji se sastoji od ugniježđenih popisa (List), od kojih je svaki zauzvrat par vrijednosti Bilo je-postalo iz jedne linije:

Ova vrsta podataka trebat će nam malo kasnije, kada budemo masovno preimenovali zaglavlja iz svih učitanih tablica.

Nakon dovršetka pretvorbe odaberite naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… i vrsti uvoza Samo stvorite vezu (Početna — Zatvori&Učitaj — Zatvori&Učitaj u… — Samo stvori vezu) i vratite se u Excel.

Korak 2. Učitavamo sve iz svih datoteka kako jest

Učitajmo sada sadržaj svih naših datoteka iz mape – za sada, kako jest. Odabir timova Podaci – Dohvati podatke – Iz datoteke – Iz mape (Podaci — Dohvati podatke — Iz datoteke — Iz mape) a zatim mapu u kojoj su naše izvorne knjige.

U prozoru pregleda kliknite pretvoriti (Transformirati) or Promijeniti (Uredi):

Zatim proširite sadržaj svih preuzetih datoteka (Binarni) gumb s dvostrukim strelicama u naslovu stupca Sadržaj:

Power Query na primjeru prve datoteke (Vostok.xlsx) će nas pitati za naziv lista koji želimo uzeti iz svake radne bilježnice – odaberite Fotografije i pritisnite OK:

Nakon toga će se (zapravo) dogoditi nekoliko događaja koji nisu očiti korisniku, čije su posljedice jasno vidljive na lijevoj ploči:

1. Power Query će uzeti prvu datoteku iz mape (imat ćemo je Vostok.xlsx — vidjeti Primjer datoteke) kao primjer i uvozi njegov sadržaj stvaranjem upita Pretvori oglednu datoteku. Ovaj upit će imati nekoliko jednostavnih koraka kao što su izvor (pristup datoteci) navigacija (izbor listova) i eventualno podizanje naslova. Ovaj zahtjev može učitati podatke samo iz jedne određene datoteke Vostok.xlsx.
   
2. Na temelju ovog zahtjeva kreirat će se s njim povezana funkcija Pretvori datoteku (označeno karakterističnom ikonom f_x), gdje izvorna datoteka više neće biti konstanta, već varijabilna vrijednost – parametar. Dakle, ova funkcija može izvući podatke iz bilo koje knjige koje ubacimo u nju kao argument.
   
3. Funkcija će se primijeniti na svaku datoteku (Binary) iz stupca Sadržaj – za to je zaslužan korak Poziv prilagođene funkcije u našem upitu koji dodaje stupac na popis datoteka Pretvori datoteku s rezultatima uvoza iz svake radne knjige:

   

4. Dodatni stupci se uklanjaju.
5. Sadržaj ugniježđenih tablica se proširuje (korak Prošireni stupac tablice) – i vidimo konačne rezultate prikupljanja podataka iz svih knjiga:

   
   

Korak 3. Brušenje

Prethodna snimka zaslona jasno pokazuje da se izravna montaža "kakva jest" pokazala loše kvalitete:

• Stupci su obrnuti.
• Mnogo dodatnih redaka (praznih i ne samo).
• Zaglavlja tablice ne percipiraju se kao zaglavlja i miješaju se s podacima.
  

Sve te probleme možete vrlo lako riješiti – samo podesite upit Pretvori oglednu datoteku. Sve prilagodbe koje napravimo automatski će pasti u pridruženu funkciju Pretvori datoteku, što znači da će se koristiti kasnije prilikom uvoza podataka iz svake datoteke.

Otvaranjem zahtjeva Pretvori oglednu datoteku, dodajte korake za filtriranje nepotrebnih redaka (na primjer, po stupcu Column2) i podizanje naslova pomoću gumba Koristite prvi redak kao zaglavlja (Koristite prvi red kao zaglavlja). Stol će izgledati mnogo bolje.

Kako bi stupci iz različitih datoteka kasnije automatski stali jedan pod drugi, moraju imati isti naziv. Takvo masovno preimenovanje možete izvršiti prema prethodno kreiranom imeniku s jednim retkom M-koda. Pritisnimo tipku ponovno f_x u traci formule i dodajte funkciju za promjenu:

= Table.RenameColumns(#”Povišena zaglavlja”, zaglavlja, MissingField.Ignore)

Ova funkcija preuzima tablicu iz prethodnog koraka Povišena zaglavlja i preimenuje sve stupce u njemu prema ugniježđenom popisu pretraživanja Naslovi. Treći argument MissingField.Ignore je potreban kako se na onim naslovima koji su u imeniku, ali nisu u tablici, ne bi pojavila pogreška.

Zapravo, to je sve.

Vraćajući se na zahtjev Izvješća vidjet ćemo potpuno drugačiju sliku – puno ljepšu od prethodne:

• Što je Power Query, Power Pivot, Power BI i zašto ih Excel korisnik treba
  
• Prikupljanje podataka iz svih datoteka u određenoj mapi
• Prikupljanje podataka sa svih listova knjige u jednu tablicu