Radek Jureček & Excel


index.htm
novinky.htm
tpv/index.htm
menphra/index.htm
doplnky.htm
navody.htm
fotogalerie.htm
odkazy.htm
kniha.htm
kontakt.htm



 

 

Domácí účetnictví v Excelu

Často dostávám dotazy na vytvoření domácího účetnictví v Excelu a proto jsem se rozhodl napsat tento příklad.

Existuje mnoho způsobů jak udělat domácí účetnictví, tohle je jen jedna z možností.

Vyměnný odkaz: Domácí účetnictví zdarma online

Začneme s přípravou pomocných tabulek.

Na listu Účet vytvoříme tabulku účtů

A vytvoříme dynamicky pojmenovanou oblast s názvem Účet

Na listu Členění vytvoříme tabulku s členěním

A vytvoříme dynamicky pojmenovanou oblast s názvem Členění

Potom vložíme nový list, který nazveme Hospodaření a vytvoříme záhlaví dle obrázku

Pokud budeme požadovat ověření vstupních dat (každý se někdy splete, překlikne), použijeme ověření dat pomocí příkazu Data - Ověření

Označíme oblast buněk ve sloupci B a v ověření dat nastavíme ověřovací kritérium na Datum je mezi

do pole počáteční datum zadáme tento vzorec

=DATUM(ROK(DNES());MĚSÍC(DNES())-2;DEN(DNES()))

který nám vrátí datum s odečtením dvou měsíců

do pole koncové datum zadáme tento vzorec

=DATUM(ROK(DNES());MĚSÍC(DNES())+2;DEN(DNES()))

který nám vrátí datum s přičtením dvou měsíců

Tím dostaneme ověření vstupních dat, které nám nedovolí zadat datum mimo stanovený rozsah, tj. pro datum 16.3.2008 nám dovolí zadat datum od 16.1.2008 do 16.5.2008. Můžeme si nastavit jiný rozsah, který nám bude vyhovovat.

Označíme oblast buněk ve sloupci C a v ověření dat nastavíme ověřovací kritérium na Seznam

A do zdroje vypíšeme Příjem;Výdej;Zůstatek

Označíme oblast buněk ve sloupci D a v ověření dat nastavíme ověřovací kritérium na Seznam

A do zdroje zadáme pojmenovanou oblast Účet

Označíme oblast buněk ve sloupci E a v ověření dat nastavíme ověřovací kritérium na Seznam

A do zdroje zadáme pojmenovanou oblast Účet

Označíme oblast buněk ve sloupci F nastavíme formát čísla např.:

Označíme oblast buněk ve sloupci F tak, aby byla aktivní buňka F5 a v ověření dat nastavíme ověřovací kritérium na Vlastní

kde do pole Vzorec zadáme

=KDYŽ(NEBO(A(C5="Výdej";F5<0);A(C5="Příjem";F5>0));1;0)=1

Toto ověření nám nedovolí zadat záporná čísla, pokud máme ve sloupci Operace Příjem a kladná čísla, pokud máme ve sloupci Operace Výdej

Ještě vytvoříme dynamicky pojmenovanou oblast s názvem Hospodařením, která nám bude automatický měnit oblast dat podle počtu vložených údajů.

a můžeme zadávat data, např.:

Do prvních řádků zadáme počáteční stavy účtů.

Spustíme průvodce kontingenční tabulkou.

V prvním kroku necháme vybránu možnost Seznam nebo databáze Microsoft Excel

V druhém kroku vybereme pomocí funkční klávesy F3 pojmenovanou oblast Hospodaření

posledním kroku určíme umístění kontingenční tabulky a použijeme volbu Rozvržení..

A z pravé strany přetáhneme Datum, Účet a Členění do Řádkového pole, Operaci do Sloupcového pole a Částku do Datového pole.

Dostaneme kontingenční tabulku v tomhle tvaru

Potom buňkový kurzor postavíme na libovolnou buňku obsahující datum a zadáme příkaz Data - Skupina a přehled - Seskupit

Dáme seskupit Datum, vybereme Měsíce a Roky

Mezivýsledek

Řádkové pole Roky přesuneme myší do stránkového pole a vybereme rok 2008

V operaci necháme zatrhnuto Příjem a výdej

Dále nastavíme automatický formát tabulky

Výsledek:

Další možný způsob rozvržení kontingenční tabulky

Další kontingenční tabulka - stavy účtů

A další, kde vidíme průběžné výdaje za rok 2008

Kontingenční graf

Po změně nebo přidání dat musíme kontingenční tabulky aktualizovat pomocí příkazu Data - Aktualizovat

Vzorový sešit ke staženi (zip)

Poslední úprava: 11.03.2013