Kompletní příklad - Šablona faktury

3.6.3 Kompletní příklad – Šablona faktury

Ing. Marek Laurenčík

V této kapitole si ukážeme, jak se dá využít aplikace MS Excel při práci v každodenním životě. Podrobně si projdeme postup jak vytvořit šablonu pro vystavování faktur s údaji o odběratelích a položkách faktury, které budou propojeny s databází těchto údajů (odběratelů a produktů).

Naším cílem bude vytvořit si šablonu faktury, která se bude z velké části sama vyplňovat a zpracovávat hodnoty do ní zadané. Především půjde o vyplnění odběratele pouze pomocí zadání jeho ID, stejně tak bude provedeno i zadávání zboží do faktury, automatický výpočet DPH a její sumarizace a vkládání data splatnosti.

Pro ukázku si můžete již hotovou šablonu otevřít ze souboru 3_6_3_faktura3.xlt:

• šablona má již vyplněnou hlavičku dodavatele

• datum vystavení i zdanitelného plnění se zobrazilo automaticky – dnešní, datum splatnosti je přednastaveno s dvoutýdenní splatností

• odběratele zadáme z rozvíracího seznamu v poli Název, když do uvedeného pole ťuknete myší a zvolíte jednoho z možných odběratelů

• jednotlivé fakturované položky (v řádcích pod sebou) zadáme z rozvíracího seznamu pole Položka

• v poli Platba zvolíte druh platby z rozvíracího seznamu

• ostatní položky včetně čísla faktury je možno doplnit ručně

Po vyplnění máte hotovou fakturu dle příkladu:

Jen pro informaci a pozdější nastavování – zdroj možných odběratelů a položek dodávky máme na skrytých listech „Odběratelé“ a „Zboží a služby“, které si můžete zobrazit pomocí příkazu Formát/List/Zobrazit …

Jako základ pro naší fakturu použijeme šablonu, která je dodávána spolu s aplikací MS Excel. Stáhnout si ji ale můžeme i ze souboru 3_6_3_faktura1.xls. Tuto šablonu si otevřeme a postupně budeme šablonu upravovat a přidávat do ní funkce, které budou třeba.

Úkony, které detailně popisují jiné části této knihy si nebudeme popisovat podrobně. Jejich popis a další příklady použití (v případě problému s uvedenou funkcí) lze dohledat v příslušných kapitolách této knihy.

1. Jako první si připravíme zdroj pro databáze zboží (produktů nebo služeb) a odběratelů. Vložíme si do souboru dva nové prázdné listy Zboží a Odběratelé.

   

2. Na listu Zboží vytvoříme seznam nabízeného zboží. Vytvoříme si sloupce „Kód“, „Popis“, „Cena za kus (bez DPH)“ a „Sazba DPH“. Získáme tedy tabulku podle již vyplněného vzoru:

   

3. Na listu Odběratelé si vytvoříme seznam odběratelů. Založíme sloupce „ID“, „Název“, „Ulice“, „PSČ“, „Město“, „IČO“, „DIČ“, „Telefon“, „Fax“ a „Bankovní účet“. Získáme tedy tabulku podle již vyplněného vzoru:

   

4. Nyní si naplníme obě tabulky vlastními údaji, nebo (pro účely vyzkoušení) použijeme data v souboru 3_6_3_data.xls, který obsahuje zmíněné 2 listy, s několika řádky dat. Pokud chceme použít tato data, jednoduše si listy zkopírujeme do souboru se šablonou (3_6_3_sablona.xls).

5. Nyní si upravíme sekci Různé:

   • do buňky M16, který reprezentuje datum vystavení, vložíme aktuální datum pomocí funkce DNES. Tedy do buňky vložíme vzorec „=DNES()“

   • dále do buňky M17, který definuje datum zdanitelného plnění vložíme stejné datum jako je datum vystavení a tedy použijeme vzorec „=M16“.

   • do následující buňky M18, která obsahuje datum splatnosti nastavíme pomocí vzorce „=M16+14“ 14denní splatnost.

   Dostali jsme tedy buňky M17 a M18 závislé na buňce M16, do které se automaticky vkládá aktuální datum. Nakonec nám ještě zbývá nastavit formát datum všem třem buňkám, podle vaší volby. Získáme tedy přibližně tento stav.

   

6. Dalším naším úkolem bude vyplnění odběratele. Nejprve nastavíme pomocí funkce Data/Ověření rozbalovací seznam pro pole Název ve hlavičce Odběratele:

   • Označíme si oblast buněk s názvem na listu Odběratelé (v našem případě B2:B7) a pomocí funkce Vložit/Název/Definovat jej nazveme Nazev.

   • Pomocí funkce Data/Ověření nastavíme povolení zápisu do buňky D13 pouze hodnot z naší pojmenované oblasti – povolíme seznam se zdrojem seznamu „=Nazev“:

     

7. Nyní nastavíme automatické vyhledávání ostatních údajů odběratele na základě názvu v buňce D13:

   • přesuneme se do buňky D14 a použijeme vyhledávací funkci SVYHLEDAT, která zajišťuje vyplnění hodnoty buňky podle názvu odběratele v naší tabulce. Do buňky zapíšeme vzorec
     SVYHLEDAT($D$13;Odběratelé!B:J;2;0)

   • musíme zajistit, když na daném místě v tabulce není žádný údaj, aby bylo zrušeno zobrazování chybového údaje „N/A“ (při tisku by tyto údaje na faktuře byly zcela nevhodné). To zajistíme pomocí funkce KDYŽ, kdy budeme testovat výsledek naší funkce SVYHLEDAT a v případě, že bude výsledkem chybová hodnota N/A, nezobrazí tuto chybu, ale pouze prázdnou buňku. Výsledný vzorec může vypadat například takto
     =KDYŽ(JE.NEDEF(SVYHLEDAT($D$13;Odběratelé!B:J;2;0));“ “;SVYHLEDAT($D$13;Odběratelé!B:J;2;0))

8. Stejným postupem vložíme vzorce i do dalších buněk v hlavičce odběratele (D15,D16,D17,D18,F16,F17,F18). Například pro buňku D18 (IČO) bude vypadat zápis:
   =KDYŽ(JE.NEDEF(SVYHLEDAT($D$13;Odběratelé!B:J;50));“ “;SVYHLEDAT($D$13;Odběratelé!B:J;5;0))

9. Funkčnost ověříme volbou z rozbalovacího seznamu libovolného odběratele a zkontrolováním správného vyplnění ostatních buněk:

   

10. Obdobný postup použijeme pro úpravy oblasti Položky faktury, kdy se v šabloně v budoucnu bude vyplňovat pouze pomocí rozbalovacího seznamu „Položka“ a počet kusů.

    • Označíme si oblast buněk se zbožím listu Zboží a služby nazveme jej „zbozi“.

    • Pomocí funkce Data/Ověření nastavíme povolení zápisu do buněk D21:D37 ze seznamu: „=zbozi“.

    • Následně v buňkách H21:H37 a J31:J37 použijeme opět funkci SVYHLEDAT pro nalezení příslušných údajů podle zadané položky. Vzorový zápis pro buňku H21:
      =KDYŽ(JE.NEDEF(SVYHLEDAT($D21;´Zboží a služby´!B:D;2;0)=1)“ “;SVYHLEDAT($D21;´Zboží a služby´!B:D;2;0))

    Abychom vzorec mohli kopírovat, je v něm třeba uzamknout hodnotu sloupce u buňky, kam se bude zapisovat kód zboží – proto je použit zápis $D21.

11. Pro kontrolu dalších výpočtů si zadáme do řádku jedna (řádek 21 v Excelu) ve faktuře libovolnou položku ze seznamu a do počtu kusů ve stejném řádku si zadáme např. číslo 2. Měli bychom vidět následující výsledek:

    

12. Nyní si doplníme vzorce pro výpočet DPH a celkové ceny včetně DPH. Do sloupce DPH použijeme funkci, která nám vynásobí počet kusů, cenu za kus bez DPH a sazbou DPH. Výsledný vzorec je tedy „=F21*H21*J21“.

13. Následně vzorec ještě musíme ošetřit opět proti nezobrazování chybové hodnoty v případě, že v řádku není žádné zboží. K tomu…