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

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

- foto Luka Vuletich
Ivan Veselinović

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

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

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

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

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

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

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 7. Unos serije podataka za ACT 15

 

Slika 8. Unos serije podataka za ACT 14

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.

Slika 9. Finalni Dashboard

Slika 9. Finalni Dashboard

 

Autor teksta je Ivan Veselinović, Senior Planer Analitičar, Atlantic Grupa (SPP Kafa).

Tekst je preuzet iz Controlling magazina 05.

Autor

Ivan  Veselinović

Senior Planning Analyst

Atlantic Grupa


3 Komentara

  1. Branko :
    01.01.2016 u 21:43|Permalink

    Da li si pokusao da simuliras bazu podataka u Excelu?

    • Savke :
      03.04.2016 u 22:36|Permalink

      Mislis da pravo iz baze podataka eksportuje podatke u excel?

  2. Branko :
    03.01.2016 u 05:47|Permalink

    Dobra ideja, mada nisam siguran kojiko su je razumeli ljudi koji se ne bave programiranjem

Napiši komentar

Newsletter pretplata

Autori Tagovi
  • Aleksandar Duković (4)
  • Aleksandar Nedeljković (1)
  • Aleksandar Nešić (1)
  • Aleksandar Pejčić (1)
  • Aleksandar Predić (2)
  • Aleksandar Teofilović (3)
  • Aleksandra Dutina (2)
  • Aleksandra Janković (1)
  • Aleksandra Paunić (1)
  • Aleksandra Đorđević (2)
  • Almir A. Budalica (19)
  • Ana Petonjić (1)
  • Ana Petrović (1)
  • Andreas Graf (1)
  • Andrej Beslać (1)
  • Andrej Kuku (1)
  • Andrija Pavlović (2)
  • Anja Atanasijević (1)
  • Anja Dadasović (1)
  • Biljana Dimitrijević (1)
  • Biljana Dukovic (1)
  • Biljana Masić (6)
  • Bjarte Bogsnes (1)
  • Bogdan Kravić (1)
  • Bojan Grahovac (1)
  • Bojan Pavlović (1)
  • Bojan Radoš (2)
  • Bojan Radun (1)
  • Bojan Šćepanović (300)
  • Bojana Pejčić (26)
  • Bojana Radović (1)
  • Bojana Vesić Anić (1)
  • Bojana Vesić Antić (1)
  • Branislav Lončar (3)
  • Branislav Vujović (1)
  • Branislav Zobenica (1)
  • Cmilja Tucaković (1)
  • Dalibor Pajić (1)
  • Daniela Laketic (31)
  • Danijela Medić (1)
  • Danijela Popović (1)
  • Danijela Resimić (1)
  • Danijela Tuco (1)
  • Darko Bjelić (2)
  • Darko Samardžija (1)
  • Darko Vlajkovic (27)
  • David Momčilović (1)
  • Davor Sakač (1)
  • Dejan Marković (1)
  • Dejan Đorđević (1)
  • Dejana Manić Kavgić (2)
  • Denis Trbović (3)
  • Desa Ćuk (1)
  • Dimitrije Stojanović (1)
  • Vojislav Marjanović (3)
  • Dragan Radosavljević (2)
  • Dragan Ranisavljević (1)
  • Dragan Vještica (3)
  • Dragana Gondžo (1)
  • Dragana Guzina (1)
  • Dragana Lukić (1)
  • Dragana Nerandžić (1)
  • Dragana Ovčarov (1)
  • Dragana Stojanović (2)
  • Dragana Đorđević (1)
  • Draško Marković (1)
  • Dražan Planinić (1)
  • Dušan Krejaković (1)
  • Excel genije! (151)
  • Fabrizio Renzi (1)
  • Goran Petrović (1)
  • Goran Popović (4)
  • Goran Slijepčević (1)
  • Goran Vasić (1)
  • Goran Vujasinović (1)
  • Gorana Golubovic Vuksanovic (1)
  • Goranka Radojčić (2)
  • Gordana Jankov (1)
  • Gostujuci autor (514)
  • Ilija Vujović (1)
  • Irina Zdravković (2)
  • Istok Pavlović (1)
  • Ivan Anđelković (1)
  • Ivan Bešker (1)
  • Ivan Ćirković (1)
  • Ivan Dačković (1)
  • Ivan Kostadinović (1)
  • Ivan Veselinović (3)
  • Ivan Đurić (1)
  • Ivana Dagović (1)
  • Ivana Jevtić (1)
  • Ivana Milić (1)
  • Ivana Popović (1)
  • Ivana Savović (1)
  • Ivana Višnjić (3)
  • Ivana Vlahović (1)
  • Jasenka Stekić (1)
  • Jelena Jovanović (1)
  • Jelena Lečić Mirčetić (1)
  • Jelena Radovanac (1)
  • Jelena Ristić (1)
  • Jelena Stojmenović (1)
  • Jelena Vučković (1)
  • Jovan Gligorijević (1)
  • Jovan Krstić (1)
  • Jovana Kondić (2)
  • Jovana Stanojčić (1)
  • Jovo Stokić (2)
  • Katarina Ćevriz (2)
  • Kristina Bojović (1)
  • Ksenija Karić (1)
  • Lazar Džamić (1)
  • Leo Pandžić (2)
  • Lidija Latinović (1)
  • Ljubomir Skupek (1)
  • Lucija Vesić (1)
  • Maja Mrkalj (1)
  • Maja Nikčević Rmandić (1)
  • Maja Petrović (1)
  • Maja Šobot (1)
  • Maja Verlašević (1)
  • Maja Zikic (1)
  • Mari Glavonjić (2)
  • Marija Đurović (1)
  • Marija Kostić (1)
  • Marija Rokvić (1)
  • Marija Švigir (32)
  • Marija Tatarević (3)
  • Marijana Krička (1)
  • Marina Mitić Jekić (1)
  • Mariora Andraš Tomić (1)
  • Marko Agatonović (2)
  • Marko Bekić (1)
  • Marko Devrnja (1)
  • Matjaž Fajfar (2)
  • Milan Listeš (2)
  • Milan Maglov (1)
  • Milan Manić (1)
  • Milan Marković (1)
  • Milan Milenković (1)
  • Milan Radivojevic (1)
  • Milan Simić (1)
  • Milan Zaletel (1)
  • Milan Đorđević (1)
  • Mile Mitrović (47)
  • Milena Janjić (3)
  • Milena Nikolov (1)
  • Milena Radosavljević Đorić (1)
  • Milena Rajić (1)
  • Milenko Dželetović (1)
  • Milica Altgelt (1)
  • Milica Ivic (1)
  • Milica Đorđević (1)
  • Miloš Cvetković (7)
  • Miloš Jauković (1)
  • Miloš Jeličić (2)
  • Miloš Nedeljković (1)
  • Miloš Pucarević (1)
  • Miloš Puzić (1)
  • Miloš Simić (1)
  • Miloš Vasić (1)
  • Miloš Zeković (1)
  • Miona Živkov-Ivanišević (4)
  • Mirjana Pašalić (2)
  • Jelena Jovanović (5)
  • Mladen Ranković (1)
  • Natalija Jegdić (1)
  • Natalija Mihajlović (1)
  • Natalija Pešić (2)
  • Natalija Popovic (1)
  • Nataša Kuzmanovski (1)
  • Nataša Stamenković (2)
  • Nataša Žikić Buha (1)
  • Nebojša Vlatković (1)
  • Neda Čičarević Tepić (1)
  • Neda Jovanović (1)
  • Nemanja Knežević (1)
  • Nemanja Nikolić (3)
  • Nenad Dlačić (1)
  • Nenad Kovačević (1)
  • Nenad Radunović (1)
  • Nenad Trajkovski (1)
  • Nevena Radovanović (1)
  • Nevena Vračar (1)
  • Nikola Avram (1)
  • Nikola Cvijanović (1)
  • Nikola Petreski (1)
  • Nikola Stokić (2)
  • Nikola Turkan (1)
  • Nikša Vušurović (1)
  • Olga Mirković (1)
  • Olivera Dostanić (1)
  • Olivera Krneta (1)
  • Olivera Nikodijević (1)
  • Petar Ćurčić (1)
  • Petar Popović (1)
  • Petar Živković (3)
  • Predrag Krasojevic (7)
  • Predrag Kudra (1)
  • Predrag Micić (1)
  • Predrag Petrović (5)
  • Rade Hajder (1)
  • Rade Radanović (2)
  • Rade Stojisavljević (1)
  • Radomir Petronijević (1)
  • Rajko Vasojević (1)
  • Roland Seeliger (1)
  • Sandra Rapo (1)
  • Sanja Jevđenijević (1)
  • Sanja Mrđanov (2)
  • Saša Stamenković (1)
  • Silvia Tomić (1)
  • Slavica Vučetić (1)
  • Slavica Vujičić (1)
  • Slaviša Lečić (2)
  • Slavko Vujnovic (7)
  • Slobodan Anić (2)
  • Slobodan Radoičić (1)
  • Slobodan Roksandić (1)
  • Slobodan Žepinić (1)
  • Snežana Glavonjić (1)
  • Snežana Trajkovski (1)
  • Snježana Pivaš (1)
  • Sonja Nenić Andreev (1)
  • Srdjan Grbić (1)
  • Srđan Grubić (1)
  • Srđan Pavlović (1)
  • Stefanija Georgieva (8)
  • Stevan Ćomić (1)
  • Svetlana Mirković Borčić (1)
  • Svetlana Pajić (1)
  • Svetlana Žikić (2)
  • Tamara Dragašević (1)
  • Tamara Lazić (3)
  • Tamara Matović (2)
  • Tatjana Bolpačić (22)
  • Tatjana Jovanović (1)
  • Tatjana Lukić (2)
  • Tijana Anđelić (1)
  • Tijana Drljević (1)
  • Tijana Rauš (1)
  • Tomislav Mimica (1)
  • Tomo Djekovic (1)
  • Vedran Babik (1)
  • Vedrana Božić (2)
  • Vedrana Vukša (1)
  • Velibor Ilić (1)
  • Veljko Stanojković (1)
  • Veljko Žarić (1)
  • Veroljub Zmijanac (1)
  • Vesna Stojanvić (1)
  • Violeta Kovačević (1)
  • Vladan Buha (1)
  • Vladan Matović (1)
  • Vladimir Petković (2)
  • Vladimir Popović (1)
  • Vladimir Stojković (1)
  • Yeghishe Avagyan (1)
  • Žaklina Teofilović (2)
  • Žarko Milovanović (1)
  • Žarko Savić (1)
  • Željko Ćulibrk (1)
  • Željko Vidojević (1)
  • Živka Mutlak (1)
  • Živorad Radovanović (1)
  • Zoran Blagojević (1)
  • Zoran Daljević (1)
  • Zorica Dinić (1)
  • Zorica Popović (1)
  • Đorđe Koprivica (2)
  • Đorđe Milinković (1)
  • Đorđe Živanović (1)
  • Najnoviji Broj Controlling Magazina

    Controlling magazin #12

    Tema:
    "SUPPLY CHAIN"

    Autori:
    Preko 60 autora: Actavis, Elektromreža Srbije, BASF, Uniqa osiguranje...

    Preuzmi elektronsko izdanje