KAKO NAPRAVITI DINAMIČKE IZVEŠTAJE U EXCELU?, IVAN VESELINOVIĆ, SENIOR PLANER ANALITIČAR, ATLANTIC GRUPA (SPP KAFA)

Ivan Veselinović

Kada pravim mesečne izveštaje često imam potrebu da dinamički menjam podatke. Na primer, upoređujem actual vs budget, actual vs last year, forecast vs budget i slično. Osim toga, sve to moram da radim brzo. U nastavku ću vam opisati primer tabele i Dashboarda, gde dinamički mogu da menjam podatke. Glavna funkcija koju sam koristio je OFFSET.

Moja početna tabela prikazuje trendove prodaje po godinama i mesecima. Potrebno je da modifikujem tabelu:

dodajem totale YTD (year to date) i MAT (moving annual total) koji zavise od varijable MESEC u polju C2
dodajem indekse i apsolutne razlike u zavisnosti od varijabli „Glavni period“, „Uporedni period 1“ i „Uporedni period 2“, u ćelijama D4, D5, D6
pravim grafikon koji će prikazivati trend prodaje za dva perioda u zavisnosti od varijabli iz D4 i D5, kao i apsolutne razlike između uporednih perioda.

Slika 1. Početna tabela

Većinu izmena ću napraviti uz pomoć funkcije OFFSET u kombinaciji sa drugim funkcijama (SUM, MATCH, COLUMNS). Rešenje se sastoji iz dve faze:

1. izračunavanje YTD, MAT, Index-a i apsolutnih razlika

2. izrada dinamičkih grafikona.

1.IZRAČUNAVANJE YTD, MAT, INDEX-A I APSOLUTNIH RAZLIKA

Prvo treba da izračunam ACT 06 YTDJul. Potrebno je sabrati prodaju za prvih „x“ meseci, pri čemu je „x“ promenljiva koja može imati vrednost od 1 do 12 i zavisi od izabranog meseca iz polja$C$2. U ovom slučaju imamo x=7, jer imamo sedam meseci („jul“). Formula koja računa 1.508 u ćeliji O2 predstavljena je na slici 2.

Slika 2. Računica ACT 06 YTD Jul

Formula za YTD radi sledeće: sumira oblast (SUM(OFFSET…) sa početkom u ćeliji koja se nalazi nula redova ispod referentne ćelije $B12 (rows=0), a jednu kolonu desno od $B12 (cols=1); neka visina te oblasti bude jedan red (height=1), a širina neka bude jednaka poziciji koju promenljiva iz polja $C$2 zauzima u nizu vrednosti iz oblasti$C$10:$N$10 (width=MATCH($C$2;$C$10:$N$10;0), a to je jednako 7). Važno je da se ispravno fiksiraju ćelije, kako bi formulu kopiranjem mogli da primenimo na sve godine.

Sada treba da izračunam ACT 15 MAT Jul. Ovde treba dodati i vrednosti iz prethodnog reda. I u ovom primeru „x“ će označavati redni broj meseca (x = 7), dok će „y“ biti jednako y = 12 – x. Tada znamo da nam treba zbir prodaje za prvih „x“ meseci tekuće godine i za poslednjih „y“ meseci prethodne godine. Formula koja računa 3.006 u ćeliji P21 prikazana je na slici 3.

Slika 3. Računica ACT 15MAT Jul

Očigledno je da je prvi deo MAT formule isti kao YTD, tako da ću objašnjavati samo drugi deo, počevši od IFERROR. Referentna ćelija će ostati ista kao za YTD (reference=$B21), što znači da je početak naše nove oblasti u prethodnom redu u odnosu na referentnu ćeliju (rows=-1), jer uzimamo vrednosti iz prethodne godine; a kolona od koje počinjemo je 8 polja desno od referentne vrednosti, odnosno x+1 (cols=MATCH($C$2;$C$10:$N$10;0)+1); kao i u prethodnom primeru visina oblasti je jedan red (hight=1), dok širina iznosi pet kolona, koliko nam je ostalo od avgusta do kraja godine, odnosno 12–x (width=12-MATCH($C$2;$C$10:$N$10;0)). Funkciju IFERROR ubacio sam da bih predupredio grešku u slučaju da računam MAT dec, jer je tada y=12-12=0, a to je nemoguće, jer opseg ne može da ima širinu nula.

Treća formula je Indeks ACT 15/ACT14*100. Princip je isti, samo je razlika u tome što nema sabiranja, već je rezultat OFFSET-a samo jedna ćelija, kao što je prikazano na slici (height=1;width=1). Formula koja računa 103 u ćeliji D23 prikazana je na slici 4.

Slika 4. Računica Indeks ACT 15/ACT14

Ovde ću objasniti samo treći argument OFFSET-a – „cols“. Koristio sam funkciju COLUMNS, koja daje broj kolona u označenom opsegu. Ovde je poenta da pravilno fiksiramo oblast: prvo preko F4 fiksiramo sve, a zatim izbrišemo dolar ispred E(COLUMNS($C$10:E$10)). Tada će se razvlačenjem formule udesno povećavati i broj kolona za jedan.

I na kraju, moja finalna dinamička tabela izgleda kao na slici 5.

Slika 5. Dinamička tabela

2.IZRADA DINAMIČKIH GRAFIKONA

Sada treba da kreiram i dinamički grafikon. U zavisnosti od promene ulaznih parametara (D4:D5), menjaće se i serije podataka na grafikonu. Na primer, u ćeliji D5 sam uneo ACT 14 i na grafikonu će se pojaviti linija sa podacima za ACT 14. Kada bismo umesto ACT 14 izabrali BUD 15, promenio bi se i grafikon.

Slika 6. Dinamički grafikon

Prvi korak je definisanje imenovane oblasti. Imenovana oblast se menja promenom određenih ulaznih promenljivih, u našem slučaju vrednosti iz polja D4 i D5. Imenovane oblasti kreiramo na sledeći način: FormulasàNameManageràNew, upišemo naziv i označimo oblast. Za „dinamičke“ oblasti je isti postupak, osim poslednjeg koraka. Tada pozivamo u pomoć funkciju OFFSET. Njen zadatak je da nađe red širine 12 kolona koji počinje „x“ redova ispod referentnog polja $B$10 i jednu kolonu desno od$B$10. Dakle, jedina promenljiva je „rows“, koju ćemo lako izračunati uz pomoć MATCHfunkcije.

=OFFSET(Prodaja!$B$10;MATCH(Prodaja!$D$4;Prodaja!$B$11:$B$21;0);1;1;12)

Po istom principu treba uraditi i za liniju dva – umesto polja D4, treba uzeti D5.

Sada idemo na kreiranje dinamičkog grafikona. Prvo kreiramo prazan grafikon linijskog tipa (Chart Tools à Design à Select Data). Posle toga počinjemo da dodajemo serije podataka. Kod označavanja Series name označite polje D4, a za Series values upišite spojeno: naziv radnog lista, !, naziv imenovane oblasti (Slika 7). Isti postupak ponoviti i za drugu seriju (Slika 8).

Slika 7. Unos serije podataka za ACT 15

Slika 8. Unos serije podataka za ACT 14

Rezultat će biti potpuno dinamički grafikon koji će prikazivati podatke koje vi želite, jednostavnim izborom uporednih perioda iz padajuće liste. Uz dodatna sređivanja, Dashboard bi mogao da izgleda kao na slici 9.