Analiza transformacijom gotovog Excel izveštaja
Često se dešava da treba da kreiramo analizu na osnovu podataka koji ne pripadaju tradicionalnoj Excel tabeli, već su sami po sebi izveštaj. U tom slučaju potrebno je malo „žongliranja“. Otkad se pojavio Power Query on je u velikoj meri olakšao ovaj proces, ali pošto ne umeju svi da ga koriste mogu da posluže neke nove Excel funkcije za rad sa dinamičkim opsezima…
Za objedinjavanje podataka iz više kolona u jednu možemo da iskoristimo funkciju VSTACK, a ako se podaci nalaze u redovima tu je funkcija HSTACK.
Poći ćemo od izveštaja koji prikazuje prodaju po zemljama i gradovima. Sintetički podatak je naziv zemlje, dok kao analitiku možemo da posmatramo nazive gradova i iznose prodaje prema gradovima.
Kako ove podatke prebaciti u jedinstvenu tabelu gde samo vidimo nazive gradova i vrednosti prodaje? Proces je jako jednostavan, možete da ga završite u dva koraka! U ćeliju A10 upišite formulu:
=VSTACK(A3:A7;C3:C6;E3:E5)
Ova formula će spojiti nazive gradova iz tri kolone u jednu.
Iskopirajte formulu u B10 i dobili ste gotov izveštaj. Sada još samo treba dodati nazive u zaglavlje tabele. Ona je dinamička, ako dođe do promene grada ili iznosa izveštaj će se automatski ažurirati. Ipak, imajte u vidu da smo u formuli uzeli u obzir tačne veličine opsega…
Da se nekim slučajem dogodilo da imamo transponovanu tabelu, u svrhu transformacije bismo iskoristili funkciju HSTACK.
A kako da uradimo suprotno, da podatke iz jedne kolone rasporedimo u matricu koja se sastoji iz više redova ili kolona? U tom slučaju iskoristićemo funkcije WRAPROWS i WRAPCOLS. Ako iz novonastale kolone želimo da rasporedimo nazive gradova prema redovima, tako što ćemo da popunjavamo po 5 naziva po redu, treba napisati formulu:
=WRAPROWS(A10#;5)
Tamo gde nema naziva Excel će prikazati grešku #N/A. Da bismo ovo zamaskirali, dodaćemo treći argument ove funkcije kojim navodimo da, u slučaju da nema vrednosti, upiše prazan string.
=WRAPROWS(A10#;5;””)
Ako želimo da popunjavamo matricu tako što ćemo ređati, iz zadatog opsega, po tri vrednosti po koloni potrebno je napisati formulu:
=WRAPCOLS(A10#;3;””)
Kada prepakujete tabelu možete dalje da iskoristite neku od funkcija za pretraživanje opsega ili uslovnu agregaciju kako biste dobili potpun izveštaj, u skladu sa zadatim propozicijama.
