Kućni proračun - Kućni budžet tablica u Excelu

Ova web stranica je nastavak prethodnog tutorijala vezanog za "Kućni budžet - Kućni proračun". U ovom tutorijalu neću objašnjavati formule detaljno jer sam većinu formula pojasnio u prethodnom tutorijalu. Naglasit ću samo neke novije elemente koji su implementirani u Excel aplikaciju.

Prvi radni list (WorkSheet) koji se nalazi u radnoj knjizi (Workbook) je "upute za korištenje". Na mjenu pročitajte osnovne upute kako koristiti Excel aplikaciju kućnog proračuna. U toku korištenja ove Excel aplikacije pozicionirajte pokazivač miša na svaku ćeliju koja sadrži komentar da bi ste se upoznali sa informacijama za dotičnu ćeliju ili stupac. Svaka ćelija koja u gornjem desnom kutu ima crveni trokutić sasdrži komentar.

Kategorije i vrste troškova kućnog proračuna-budžeta

Na radnom listu "imenovani-podaci" nalaze se tablice u kojima sam definirao

• Kategorije troškova kućnog budžeta
• Vrste troškova kućnog budžeta
• Vrste prihoda (financijskog ulaza na stanje) kućnog budžeta
• Nazivi mjeseca u godini za koju vodimo evidenciju kućnog budžeta

Ove tablice definirane su kao stvarne Excel Table i one su "dinamičke", što znači da vi možete dodavati i mijenjati nove stavke u svakoj tablici a Excel će automatski proširiti range za imenovani raspon podataka. Pogledajte Name Manager i definirane raspone i ćelije u njemu. U tablicama nalaze se dinamični padajući izbornici koji su definirani preko Data Validation naredbe.
To znači da kada vi dodate novu stavku u nekoj kategoriji na radnim listovima 1-2015......12-2015 automatski će se naziv troška ili kategorije pojaviti u padajućoj listi. Ja ovdje nisam kreirao zavisne padajuće liste ali vi možete ako želite prema tutorijalu na dotičnom linku (izrada zavisne padajuće liste2 ).

VAŽNO!: Ovaj radni list je vrlo važan i bitan jer se na njemu definiraju osnovni pojmovi koji se linkaju na druge radne listove i često se pozivaju u tablicama ostalih radnih listova. Ovakav način organizacije je dobar (po mom mišljenju) zbog toga što na jednom mjestu možemo promijeniti određeni pojam, naziv ili stavku a Excel će automatski izvršiti promjenu na svim ostalim radnim listovima koji su povezani sa dotičnim pojmom. Naravno ja nisam išao u detaljno automatiziranje pa neće baš svaka promjena odgovarati (to ostavljam vama za istraživanje :-)

NAPOMENA: U koliko na ovom radnom listu želite promijeniti nazive mjeseca u godini u rasponu ćelija 'O1:Z1' tada istu promjenu morate izvršiti i u Name Manageru tj. umjesto 'siječanj' editirati novi naziv 'januar' i editirati formule u ćelijama 'O2:Z4' da bi dobili iste rezultate tj. nazive radnih listova koje koristi Excel funkcija INDIRECT.

Mjesečna evidencija kućnog budžeta ili kućnog proračuna - Kalkulator za kućni budžet

Radni listovi pod nazivima 1-2015.....12-2015 su mjesečne evidencije. Ova Excel aplikacija razlikuje se od prethodne po tome što se ovdje evidencija vodi na mjesečnoj bazi a u prethodnom tutorijalu na godišnjoj bazi za sve mjesece u godini. Možda se pitate zašto sam baš ovako postavio nazive radnih listova (Sheet Tab Name). Jednostavno iz razloga da se kasnije može automatizirati izmjena broja godine (promijeniti u slijedeću godinu) a broj ispred godine je broj mjeseca. Upravo ovaj broj mjeseca je bitan da bi pomoću formule u ćeliji C3 na svakom radnom listu mjeseca prikazao naziv radnog lista (Sheet Name).

Formula u ćeliji 'C3' je slijedeća

=MID(CELL("filename";C3);FIND("]";CELL("filename";C3))+1;256)

Nadalje ova ćelija 'C3' poslužila mi ja kao izvor podataka da bi u ćeliji 'B1' u prvom redu prikazao naziv mjeseca i dotičnu godinu.

Formula u ćeliji 'B1' je slijedeća: (ova formula mora biti u jednoj liniji ili jednom redu)

=CHOOSE(IF(C3<>"";IF(FIND("-";C3)>2;LEFT(C3;2);LEFT(C3;1));"");siječanj;veljača;ožujak;travanj;svibanj;lipanj;srpanj;
kolovoz;rujan;listopad;studeni;prosinac)&" - " &IF(C3<>"";IF(FIND("-";C3)>2;RIGHT(C3;4);RIGHT(C3;4));"")

Formula iznad za uvjet u ćeliji 'C3' pronalazi potrebne podatke u argumentima i vraća onaj naziv mjeseca koji odgovara uvjetima. Ovi nazivi mjeseci u godini koje možete uočiti u formuli iznad su u stvari definirani u Name Manageru jer sam na prvom radnom listu "imenovani-podaci" imenovao ćeliju za svaki naziv mjeseca nazivom dotičnog mjeseca. Ovo je važno upravo zato ako bi ste htjeli preimenovati nazive mjeseca na neki drugi jezik.

U stupcima 'B' i 'H' kao i još nekim ćelijama za "ulaz", "izlaz" i "stanje" postavljene su formule, pa zato vodite računa da ih ne obrišete. Radni list je zaštićen od izmjena određenih ćelija ali bez passworda (lozinke). Ako znate što radite i sigurni ste da nećete neku od formula obrisati slobodno otključajte radni list klikom na "Unprotect Sheet" naredbu.

Nadalje, na ovom radnom listu postvljena je automatska definicija područja ispisa (Print_Area) tako da prilikom ispisa na printer Excel automatski postavlja područje ispisa bez praznih redova. Kada selektirate neku od ćelija u koju želite upisati podatak ili npr. datum automatski se pojavljuje okvir sa upozorenjem ili uputom o upisu vrste podatka. Nisam išao u detaljiziranje poput ograničenja tj. zabrana upisa nedefiniranog formata podatka u ćeliji ali vi možete sami ako želite uz tutorijal na dotičnom linku. Nadam se da je ovo dovoljno za opis ovog radnog lista, sve ćete vidjeti u samoj Excel datoteci koju možete downloadati (skinuti) sa ovog tutorijala.

Rekapitulacija evidencije i vođenja kućnog budžeta na godišnjoj razini

Jedan od interesantnih radnih listova ove Excel aplikacije je "rekapitulacija". Ovaj radni list sadrži puno tablica, tj. po jedna tablica za svaku kategoriju troškova koju možete proširiti po potrebi tako da kliknete dswnom tipkom miša unutar tabčlice na predzadnji red pa izaberete naredbu Insert => Table Rows Above, ali tada kopirajte formule u stupcima 'B:N'. Radni list je zaštićen ali nema lozinke (passworda). Dovoljno je kliknuti na Review => Unprotect Sheet i možete vršiti izmjene po želji. Ovaj radni list povlači sve pojmove i podatke sa radnog lista "imenovani-podaci" i radnih listova za evidenciju troškova po mjesecima (1-2015.....12-2015).

Ovaj radni list i podaci na njemu osnova su za grafikone i Pivot Table na drugim radnim listovima; "PivotTable-PRIHOD" i "Pivot-Godišnje-stanje". Naravno ja sam ove pivot tablice kreirao samo radi primjera a vi možete kreirati pivot tablice prema svojim željama dodajući nove radne listove.

Ovdje još želim napomenuti dvije važne formule u stupcima 'A' i 'B:M'.

Vrijednost za određene kategorije PRIHODA

Formula u stupcu 'A' pretražuje kategorije i vrste troškova na radnom listu "imenovani-podaci" i kao rezultat vraća vrstu troška za određenu kategoriju (koje su složene u odvojenim tablicama).

npr: Formula u ćeliji 'A6' je slijedeća

=IFERROR(INDEX('imenovani-podaci'!$F$2:$H$50;SMALL(IF('imenovani-podaci'!$F$2:$H$50=$A$4;ROW('imenovani-podaci'!$F$2:$H$50));ROW(2:2))-1;COLUMN(C2));"")

Ova formula pretražuje kategorije prihoda na radnom listu "" imenovani-podaci" i vraća prvu kategoriju koju pronađe u rasponu. Uvjet za kategoriju nalazi s eu ćečliji 'A4'. Formula se kopira prema dolje pa tako imamo prikazane sve kategorije prihoda. Ako dodamo novu kategoriju u stupcu 'H' na radnom listu "imenovani podaci" Excel će automatski prikazati ovdje novu kategoriju prihoda.

Isto tako za vrste troškova imamo formulu u ćeliji 'A21' koju kopiramo prema dolje do zadnjeg reda dotične tablice vrste troška "Automobili i prijevoz".

Formula u ćeliji 'A21' je slijedeća

=IFERROR(INDEX('imenovani-podaci'!$B$2:$C$491;SMALL(IF('imenovani-podaci'!$B$2:$C$491=$A$20;ROW('imenovani-podaci'!$B$2:$C$491));ROW(1:1))-1;COLUMN(B1));"")

Vrijednosti po vrstama troškova

Formule u stupcima 'B:M' vraćaju iznos troška za svaku 'vrstu troška' u određenoj kategoriji, ove vrijednosti formula povlači iz radnih listova koji su vezani za određeni mjesec u dotičnoj godini. Tako npr za vrste troškova koji su vezani za automobil i prijevoz u tablici možemo vidjeti koliko smo novčanih sredstava potrošili na određene vrste troškova automobila u toku godine ali razvrstano po mjesecima.

ARRAY Formula u ćeliji 'B21' je slijedeća

=IF(IF([@[Automobil i prijevoz]]="";"";SUMIF(INDIRECT('imenovani-podaci'!O$4&"$E$8:$F$100");[@[Automobil i prijevoz]];INDIRECT('imenovani-podaci'!O$4&"$G$8:$G$100")))=0;"";IF([@[Automobil i prijevoz]]="";"";SUMIF(INDIRECT('imenovani-podaci'!O$4&"$E$8:$F$100");[@[Automobil i prijevoz]];INDIRECT('imenovani-podaci'!O$4&"$G$8:$G$100"))))

ili

=IF(IF(Table17[[#This Row];[Automobil i prijevoz]]="";"";SUMIF(INDIRECT('imenovani-podaci'!O$4&"$E$8:$F$100");Table17[[#This Row];[Automobil i prijevoz]];INDIRECT('imenovani-podaci'!O$4&"$G$8:$G$100")))=0;"";IF(Table17[[#This Row];[Automobil i prijevoz]]="";"";SUMIF(INDIRECT('imenovani-podaci'!O$4&"$E$8:$F$100");Table17[[#This Row];[Automobil i prijevoz]];INDIRECT('imenovani-podaci'!O$4&"$G$8:$G$100"))))

Ovu ARRAY formulu iznad završavate je sa Ctrl+Shift+Enter i kopirate je u desnu stranu do 'M' stupca pa zatim redak 'B:M' kopirate prema dolje do zadnjeg reda dotične tablice.
Ovdje u formuli obratite pažnju na dio formule [@[Automobil i prijevoz]]
koja može biti i ovakvog oblika izgleda Table17[[#This Row];[Automobil i prijevoz]]
Sve zavisi koju verziju Excela koristite. Ova dva oblika formule su u stvari uvjeti i vraćaju isti rezultat a u stvari to je uvjet u cjelokupnoj formuli. Umjesto ove formule (da nije kreirana stvarna tablica) možemo pisati A21 što je isto kao i gore navedene dvije formule (zelene boje). Ova formula nalazi se na više mjesta u cjelokupnoj formuli a ona je u stvari uvjet koji se nalazi u ćeliji 'A21' tj. vrsta troška prema kojem pretražujemo raspon podataka na određenom radnom listu-mjeseca da bi vratili vrijednost za pronađeni uvjet u dotičnom mjesecu.

Ovo je uvjet tj. funkcija u formuli IF ali vezana za INDIRECT('imenovani-podaci'!O$4&"$E$8:$F$100"), koja je vezana za naziv radnog lista dotičnog mjeseca i na osnovu podatka iz 'O4' sa radnog lista "imenovani-podaci" vraća naziv radnog lista na kojem pretražujemo podatke tj. za određeni mjesec. I tako redom za sve tablice koje se nalaze na ovom radnom listu "rekapitulacija". NAPOMENA: Nemojte mijenjati nazive radnih listova na kartici ili tabu radnog lista (Sheet Name Tab) jer nisam išao tako duboko da bi sve automatizirao :-)

Prijelaz u novu godinu i iskorištavanje već korištenog predloška

U situaciji kada ste završili jednu godinu i želite nastaviti voditi evidenciju u novoj godini učinite slijedeće. Arhivirajte završenu godinu i kopirajte je pod drugim nazivom. Otvorite aplikaciju za novu godinu i izbrišite sve prihode i troškove na radnim listovima za svaki mjesec. Ne dirajte formule.
S obzirom da su nam radni listovi naziva protekle godine moramo preimenovati imena radnih listova za mjesečne evidencije, bilo manualno ili u jednom koraku koristeći VBA makronaredbu (preimenovanje naziva radnih listova u jednom potezu).

Kako pratiti financijske troškove kućanstva pomoću Pivot Table u Excelu

Vjerojatno ste se pitali zbog čega je datoteka snimljena u *.xlsm formatu. U VBE Excela sam postavio VBA makronaredbu koja automatski osvježava (refresh) PivotTable kada prelazimo sa jednog na drugi radni list. Dakle ako želite koristiti ovu mogućnost tada ovu Excel datoteku trebate snimiti u formatu *.xlsm za Excel 2007 i noviji .

Ili ako želite snimite je u *.xls formatu za Excel 2003 ali tada vam ne mogu sa sigurnošću reći da li će aplikacija raditi kako treba zbog kompatibilnosti različitih verzija Excela kao i zbog formula i funkcija koje su upotrijebljene u aplikaciji. U ovom slučaju bi trebalo pregledati sve formule i funkcionalnost aplikacije i eventualno izmjeniti neke formule.

Slijedeći radni list je "PivotTable-PRIHOD" na kojem se nalazi pivot tablica u kojoj vizualno možemo vidjeti prihode ili financijske ulaze na stanje našeg kućnog proračuna ili kućnog budžeta za cijelu godinu po mjesečnim ulazima. Na slici ispod uočite dvije Pivot Table i grafikon (Chart) gdje vizualno možemo vidjeti ulaze pa čak i mjesec u kojem padamo u minus ako planiramo određene iznenadne troškove. Ova pivot tablica je samo primjer a vi možete sami kreirati Pivot Table za vaše potrebe.

Ako želite koristiti napredne elemente Pivot Table tada probajte koristiti SLICERS uz Pivot Table u Excelu.

I na kraju evo DOWNLOAD datoteke "Home Budget " iz primjera ovog tutorijala. Obratite pažnju da su neki radni listovi zaštićeni ali bez passworda. Da bi ih otključali potrebno je kliknuti na "Unprotect Sheet".

VAŽNO! ZIP datoteku iz primjera ovog tutorijala kao i Excel aplikaciju ZABRANJENO je distribuirati na drugim web stranicama, portalima, forumima i sl. za download (skidanje). (© - copyright)

Za pregled prethodne verzije "Kućnog budžeta" ili "Kućnog proračuna" kliknite na sličicu: