logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali - Kućni proračun ili kućni budžet Evidencija kućnog proračuna, Kako pratiti financijske troškove kućanstva, Evidencija osobnih financijskih troškova i planiranje i vođenje kućnog budžeta, Kategorije i vrste troškova kućnog proračuna ili budžeta, Kalkulator za kućni budžet, Kućni budžet tablica u Excelu, Mjesečna evidencija kućnog budžeta ili kućnog proračuna Home Budget in Excel

Kućni budžet - Kućni proračun
(Home Budget)


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.

Kategorije i vrste troškova kućnog proračuna ili kućnog budžeta u Excelu

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.

Kronološko popunjavanje mjeseca za koji evidentiramo troškove kućnog proračuna u Excelu

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 :-)

Godišnja rekapitulacija mjesečne evidencije troškova kućnog proračuna u Excelu

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.

Pivot Table i grafikon mjesečnih troškova kućnog budžeta u Excelu

Slijedeći radni list je također vezan za statistiku i pregled planiranih i ostvarenih troškova evidencije i vođenja kućnog proračuna ili kućnog budžeta na kojem možemo vidjeti odnos između ulaza financijskih sredstava i izlaznih troškova po mjesecima u dotičnoj godini.

U stupcu 'B' pivot tablice uočite grafikon na kojem se može vidjeti odnos PRIHOD minus RASHOD novčanih sredstava kao i totalni trošak za dotični mjesec. Pivot Table daje nam velike mogućnosti kod vizualnih pregleda i statistike ulaznih i izlaznih troškova. Pivot tablicu možemo iskoristiti i za pregled troškova po pojedinoj vrsti financijskog troška ako u nju uključimo podatke koji su vezani za mjesece u godini i određenu vrstu troška.

Tako npr. možemo kreirati pivot tablicu u kojoj možemo vidjeti koliko mjesečno ili godišnje trošimo npr. na razne grickalice, žvakaće gume, eventualno ostale manje potrebne prehrambene ili druge proizvode. Ove statistike ostavljam vama za istraživanje.

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

Pivot tablica i dijagram evidencije troškova u odnosu na prihode kućnog budžeta

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)
Da bi otvorili Excel datoteku za rad potrebno je upisati password koja glasi "ic.ims.hr" bez navodnika.

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

     

COPYRIGHT © - 2005 - 2020 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela