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 - Evidencija troškova i vođenje budžeta u Excelu, Kako voditi kućni budžet, Evidencija osobnih financijskih troškova - planiranje i vođenje kućnog budžeta, Planiranje troškova firme ili obrta ili tvrtke, Home budget , Vođenje i evidencija kućnog proračuna

Planiranje, evidencija i vođenje
troškova financijskog budžeta
Vođenje i evidencija kućnog proračuna


Kućni budžet - Kućni proračun - Kako planirati, voditi evidenciju financijskih troškova budžeta

Kada je u pitanju vođenje i planiranje evidencije troškova budžeta svjesni smo da na internetu ima dosta primjera, no evo još jedan tutorijal sa primjerom kojega možete downloadati i svim funkcionalnim formulama.
U ovom tutorijalu prikazati ću kako možemo planirati, voditi, evidentirati financijske troškove dostupnih novčanih sredstava u Excelu. Nadam se da znate da na više načina možemo planirati osobne troškove i voditi evidenciju potrošenih novčanih sredstava bilo da se radi o kućnom budžetu, budžetu obrta ili udruge ili sportskog kluba i slično. Isto tako možemo planirati troškove i evidenciju na godišnjoj ili mjesečnoj bazi (mogućnosti su raznolike)

Ja ću u ovom primjeru prikazati jedan od načina planiranja i vođenja financijskih troškova kućnog budžeta. Ovaj primjer možete iskoristit i za druge svrhe u kojima su osnove

  • Ulaz novčanih sredstava
  • Izlazni troškovi prema namjeni
  • Stanje financija na raspolaganju

Na slici ispod uočite radne listove unutar radne knjige koje sam isplanirao za vođenje kućnog budžeta u Excelu. U samom početku krenuo sam od imenovanja nekih raspona ćelija da bi ih mogao kasnije koristiti u padajućim izbornicima. Tako uočite da sam imenovao naziv raspona za; 'mjesece' i 'godine'. Dakle poželjno je imenovati određene raspone ćelija na jednom mjestu i onda ih pozivati i korisitit na ostalim radnim listovima. Ovo je dobro zato što u slučaju da trebamo neki naziv izmjeniti, izmjenu izvršimo na jednom mjestu a Excel izvrši kalkulaciju i napravi ostale izmjene na drugim radnim listovima.

Imenovani rasponi podataka zavođenje kućnog budžeta

Radni list za 2015 godinu - evidencija unosa troškova po mjesecima i datumima

Na ovom radnom listu za 2015 godinu uočite slijedeće (slika ispod):

U ćeliji 'A1' nalazi se slijedeća formula: =MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256)
Ova formula automatski povlači naziv radnog lista

U ćeliama 'C1:N1' linkani su nazivi troškova sa radnog lista "imenovanja". Sjećate se da sam napomenuo da na jednom mjestu imamo nazive troškova i samo ih linkamo na ostale radne listove.

U stupcu 'A' ćeliji 'A2' nalazzi se slijedeća formula: =TEXT(IF(B2<>"";B2;"");"MMMM")
Ova formula kao rezultat vraća naziv mjeseca u godini na osnovu upisanog datuma u ćeliji 'B2'. Ovu formulu kopiramo prema dolje i možemo zaključati stupac 'A' po potrebi radi sigurnosti da ga ne slučajno ne izmjenimo jer sadrži formulu a ovaj stupac 'A' možemo i sakriti da nam vizualno ne smeta.

U ćeliju 'B2' upisujemo datum kada je nmastao financijski trošak

U ćelijama 'C2:K2' upisujemo određeni novčani iznos za određenu stavku troška.

U ćeliji 'L2' nalazi se formula koja zbraja sve vrijednosti troškova za dotični datum (formulu kopiramo prema dolje do željenog zadnjeg reda)
=SUM(C2:K2)

U ćeliju 'M2' upisujemo novčani iznos prihoda tj. ulaz na financijsko stanje (plaća, najam, loto dobitak :-)

U ćeliji 'N2' nalazi se formula koja zbraja sve vrijednosti troškova za dotični datum u Row2
=SUM(M2-L2)

U ćeliji 'N3' nalazi se formula koja pribraja ulaz novčane vrijednosti sa stanjem prethodnog datuma i od ovog zbroja oduzima novi trošak iz istog reda. (formulu kopiramo prema dolje do željenog zadnjeg reda)
=(M3+N2)-L3

Na ovom radnom listu uočite da su istom bojom označeni redovi koji pripadaju istom mjesecu u godini. Ovo sam riješio koristeći "Conditional Formatting" ali više o tome u narednom dijelu tutorijala.

evidencija unosa financijskih troškova budžeta po datumima u toku godine

Bojanje redova iz istog mjeseca u toku godine pomoću uvjetnog oblikovanja

Radni list za godinu 2016 identičan je radnom listu za godinu 2015. Ovaj radni list iskoristiti ću da vam objasnim kako sam koristio "Uvjetno oblikovanje u Excelu 2013" da bih obojao ili označio sve redove koji pripadaju istom mjesecu u godini.

Bojom označeni redovi koji pripadaju istom mjesecu u godini

Označavanje redova za isti naziv mjeseca u godini na slici iznad urađeno je pomoću Uvjetnog oblikovanja koristeći imenovane ćelije za određeni mjesec. Na slici ispod uočite radnje slijedećim redoslijedom:

Na radnom listu "imenovanja" svaki mjesec posebno imenovao sam određenim nazivom (siječanj=Month1, veljača=Month2, ožujak=Month3 itd...)

Na radnim listovima "2015" i "2016" selektirao sam raspon ćelija 'A2:N700' što možete uočiti na slici ispod u uokvirenom dijelu koji prikazuje dijalog prozor "Conditional Formatting Rules Manager". Prilikom dodavanja svakog novog pravila postavio sam formulu (vidi uokvireni dio na slici ispod) dijalog prozor "Edit Formatting Rule" =IF($A2=Month1;TRUE;FALSE) i postavio format boje za dotično pravilo.

Ovdje želim napomenuti kada kreirate jedan radni list npr. za 2015 godinu, na završetku godine kopirate dotični radni list i preimenujete ga u 2016 godinu. Obrišete nepotrebne podatke trošškova koje ste upisivali, pregledate ostale formule i nastavite voditi kućni budžet u novoj godini.

Znači ovakvih pravila ima dvanaest za svaki mjesec jedno pravilo i različita boja. Koristeći CF možemo vizualno uočiti ako smo zaboravili upisati neki datum i trošak jer će nam Excel bojom prikazati kojem mjesecu pripada bez obzira da li se određeni datum nalazi iza već upisanih prethodnih mjeseci. Ovo može biti slučaj da nam supružnik, djelatnik, šef, ili već bilo tko stvori trošak koji nismo na vrijeme evidentirali. Ovakav slučaj možete vidjeti na radnom listu '2015' za mjesec "siječanj" (druga slika po redu iznad). Nemojte se brinuti za radni list "sumarum" jer će Excel uzeti u obzir i ovaj trošak koji nije po redoslijedu što možete vidjetiu na zadnjoj slici ovog tutorijala.

Označavanje redova bojom u Excelu ako je ispunjen uvjet

Pivot Tablica za vođenje i evidenciju troškova kućnog proračuna ili budžeta

Na radnom listu "grafikoni" uočite tablicu u rasponu 'A1:D14'. Ova tablica je izvor podataka za dva grafikona (bar i pie). Naravno vama ostaje za izbor vrsta grafikona. Također uočite Pivot Table pomoću koje vrlo brzo možemo doći do određenih podataka. Uz to ako još koristite Slicers u Pivot Table tada nema kraja željenim rezultatima.

Ukratko ću prikazati formule u tablici (crvena slova)

U ćeliji 'A1' nalazi se linkana formula koja povlači godinu sa radnog lista "sumarum": =sumarum!$A$1

U ćeliji 'A2' nalazi se formula koja fiksira adresu i rezultat formule, dotična formula vraća broj godine iz ćelije 'A1' ali prilagođen za korištenje u formuli uz funkciju INDIRECT. ="'"&A1&"'!" (ćelija je vizualno formatirana fontom bijele boje i neću je posebno objašnjavati jer sam je pojasnio u dijelu tutorijala vezanom za radni list "sumarum")

U ćeliji 'A3' nalazi se formula koja kopira godinu iz ćelije A2: =$A$2 (ćelija je vizualno formatirana fontom bijele boje, ova formula kopirana je prema dolje do zadnjeg reda)

U ćeliji 'B2' nalazi se link-formula koja kopira nazive mjeseca sa radnog lista "imenovanja": =imenovanja!A5 (ova formula kopirana je prema dolje do zadnjeg reda). Ova ćelija 'B2' imenovana je nazivom ShName2 jer se ova ćelija koristi u daljnjim kalkulacijama i formulama u stupcu C i D.

U ćeliji 'C2' nalazi se formula =SUMIF(INDIRECT(ShName2&"A2:L700");B2;INDIRECT(ShName2&"L2:L700"))
Ova formula kao što sam rekao koristi ćeliju 'B2' u kalkulaciji da bi vratila rezultat za određenu godinu i dotični mjesec a to je u ovom slučaju SUM ili Zbroj svih troškova za dotični mjesec. (ova formula kopirana je prema dolje do zadnjeg reda)

U ćeliji 'D2' nalazi se formula =SUMIF(INDIRECT(ShName2&"A2:M700");B2;INDIRECT(ShName2&"M2:M700"))
Ova formula kao što sam rekao koristi ćeliju 'B2' u kalkulaciji da bi vratila rezultat za određenu godinu i dotični mjesec a to je u ovom slučaju ULAZ (prihod) svih financija za dotični mjesec. (ova formula kopirana je prema dolje do zadnjeg reda)

PivotTable and Home Budget

VAŽNO!

U ovom radnom listu "grafikon" korištena je VBA makronaredba za automatsko osvježavanje (auto refresh) Pivot Tablice. Ako ovu makronaredbu i dalje želite koristiti obavezno datoteku (Excel aplikaciju proračuna) snimite sa extenzijom *.XLSM ili snimite kao *.XLS ali u slučaju da datoteku snimate za Excel 2003 tada je potrebno umjesto IFERROR funkcije koristiti kombinaciju IF/ISERROR Excel funkcija, inače može doći do nekompatibilnosti između dvije verzije Excela 2013 i Excela 2003 što može uzrokovati jako dugačku formulu ali nije nemoguće da funkcionira kako treba.

VBA makronaredba za automatsko osvježavanje PivotTable prilikom izmjene podataka i prijelaza sa radnog lista na radni list i vraćanja na radni list na kojem se nalazi PivotTable. Vba makronaredbu treba kopirati u Sheet Module "grafikon" i ona se automatski pokreće bez vaše intervencije.

Private Sub Worksheet_Activate()
Sheets("grafikon").PivotTables("PivotTable1").RefreshTable
End Sub

Brzi pregled evidencije troškova budžeta i ispis na pisač

Prvi radni list "sumarum" prikazuje rezultate za jedan mjesec (u ovom slučaju za siječanj-2015). Na radnom listu "sumarum" u ćeliji 'A1' sa izbornika biramo godinu a u ćeliji 'B1' izabiremo naziv mjeseca za koji želimo prikazati podatke evidencije. Također uočite grafikone koji prikazuju troškove po mjesecima u toku izabrane godine.

Na ovom radnom listu definirana je Print_Area za ispis tablice u rasponu "A1:zadnji popunjeni red". Dakle ne moramo se brinuti kod ispisa na papir, Excel će automatski postaviti područje ispisa (Print Area) i ignorirati prazne redove tablice. U drugom redu nalazi se zbroj za svaki stupac pojedinačno. Pomoću ovog zbroja možemo uočiti koliko smo za dotičnu stavku u određenom mjesecu potrošili novaca.

U trećem redu nalaze se nazivi određenih stavki za koje imamo financijske izdatke tj. troškove. Ovi nazivi linkani su sa radnog lista "imenovanja" i ako ih želite izmjeniti tada izmjene izvršite na radnom listu "imenovanja". Vidi sliku iznad i uoči plavkastom bojom ćelije za nazive, a isto tako i nazive "trošak, ulaz, stanje".

Sliku grafikona koji se prikazuju na desnoj strani radnog lista u odnosu na tablicu je kreiran pomoću Camera Tool a u stvarnosti se nalaze na radnom listu "grafikoni".

U ćeliji 'A4' nalazi se slijedeća ARRAY formula (formula mora biti u jednoj liniji)

=IF(IFERROR(INDEX(INDIRECT(ShName&"B$2:B$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW
(INDIRECT(ShName&"$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));"")="";"";IFERROR
(INDEX(INDIRECT(ShName&"B$2:B$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW(INDIRECT(ShName&"
$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));""))

U formuli iznad uočite boldani dio formule ShName.
Ovo je imenovani naziv za ćeliju 'M1' u kojoj se nalazi formula ="'"&A1&"'!"
Ova formula je skrivena jer sam postavio "bijela slova" a formula fiksira adresu i rezultat formule. Ova formula kao rezultat vraća naziv radnog lista koji povlači iz ćelije 'A1' ali tako ovaj naziv možemo koristiti u formuli INDIRECT(ShName&"B$2:B$700") ili INDIRECT(ShName&"$A$2:$A$700") itd.

Rezultat formule ="'"&A1&"'!" je '2015'! tj. onaj broj koji se nalazi u ćeliji 'A1'. Ovim načinom rada smo si osigurali da formula u tablici koristi naziv radnog lista. Ako promijenimo naziv radnog lista automatski će nam rezultat biti ispravan jer se Excel veže na imenovanu ćeliju ShName. Pogledajte tutorijal Kako fiksirati adresu ćelije i oblikovati je za daljnju kalkulaciju i korištenje u INDIRECT funkciji.

U ćeliji 'B4' nalazi se slijedeća ARRAY formula (formula mora biti u jednoj liniji). Uočite razliku između prethodne formule i ove ispod. Znači, dio formule koji je obojan plavom bojom vezan je za stupac (kolonu) na radnom listu za dotičnu godinu iz ćelije 'A1'. Dakle u formuli ispod funkcija INDIRECT plave boje vraća podatke iz stupca 'C' iz radnog lista '2015'!.

=IF(IFERROR(INDEX(INDIRECT(ShName&"C$2:C$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW
(INDIRECT(ShName&"$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));"")="";"";IFERROR
(INDEX(INDIRECT(ShName&"C$2:C$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW(INDIRECT(ShName&
"$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));""))

Ovu formulu možemo kopirati iz ćelije 'A4' u desnu stranu ali u svakom stupcu 'B4:M4' moramo ručno izvršiti ispravku dijela formule koji je obojan plavom bojom. Tako će formula uvijek vraćati podatke iz odgovarajućeg stupca. Kada završimo uređivanje formula u četvrtom retku tada selektiramo raspon 'A4:M4' i kopiramo prema dolje do zadnjeg željenog reda.

Kućni budžet u Excelu

I na kraju evo onoga što ste nestrpljivo čekali :-)
Besplatan DOWNLOAD datoteke iz primjera ovog tutorijala možete skinuti na dotičnom linku. 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". BTW: Ja se nadam da uz ovoliko pisanja nisam negdje pogriješio ali i ako jesam već ćete se vi snaći jer imate završeni primjer koji možete prilagoditi svojim potrebama :D
VAŽNO! ZIP datoteku i Excel aplikaciju iz primjera ovog tutorijala ZABRANJENO je distribuirati na drugim web stranicama, portalima, forumima i sl. za download (skidanje). (© - copyright)

Evo još jedan tutorijal za vođenje: Kućni budžet - Kućni proračun - Home Budget .......... Next

     

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