OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

List faktury

Ako prvé začneme pracovať na liste faktury. Tento list je prepojený s listom data v zošite faktúry.ods, ktorý ste si vytvorili a uložili v minulom diele – teraz si ho otvorite. V dokumente faktúra.ods prejdite teraz na list faktury do bunky A1. Zadajte = a prejdite do dokumentu faktúry.ods a na list data, označte bunku A1 a odsúhlaste Enterom. Teraz máte v bunke A1 na liste faktury nasledovný vzorec:

=file:///C:/firma/databázy/faktúry.ods'#$data.A1
=file:///home/firma/databázy/faktúry.ods'#$data.A1 # pre Linux

Všimnite si, že v zošite pribudol nový list (pre kontrolu vyberte z menu Formát | Tabuľka /alebo List/ položku Zobraziť). Tak isto v Upraviť | Odkazy máte nový odkaz na dokument.

Zobraziť tabuľku

Odkazy

Ak budete mať Calc nastavený podľa nasledujúceho obrázku, tak sa vám bude tento odkaz na dokument aktualizovať automaticky pri otvorení dokumentu: Nástroje | Možnosti | OpenOffice.org Calc | Všeobecné | Aktualizovať odkazy pri otvorení.

Automatické aktualizovanie odkazov

Ide to prepojiť aj inak pomocou DDE (dynamic data exchange). Citát z pomocníka OpenOffice.org: DDE znamená Dynamic Data Exchange – dynamická výmena dát, ktorý je predchodcom OLE – s Object Linking and Embedding (vkladanie a spojovanie objektov). S DDE sú objekty prepojené odkazom súbor, ale nie vložením súboru.

No od tohto riešenia som upustil, lebo toto prepojenie malo problémy s diakritikou.

Aby ste dokončili tento list, musíte si vzorec z A1 prekopírovať do B1 a následne tieto dva vzorce nakopírovať až po riadok 1000 . Ak budete ročne robiť viac faktúr, tak aj ďalej. Týmto máte list faktury hotový.

Dokončenie listu faktúra

Začiatok práce na liste nájdete v minulom diele: OpenOffice.orgvo výrobnej firme 2 – Faktúra 1 . Opäť začneme hlavičkou. Vložte nasledujúce vzorce.

1. Do bunky L1 vzorec =VALUE(J1) – to preto, že v tejto bunke potrebujeme číslo z výsledku vzorca v J1.

2. L2:

   =IF(ISERROR(LARGE(faktury.A2:faktury.A1449;1));0;LARGE(faktury.A2:faktury.A1449;1))
   

   tým „is error“ je ošetrené, ak ešte žiadna faktúra nebola uložená (prvá faktúra).

3. K1 =IF(ISNUMBER(VLOOKUP(VALUE(J1);faktury.A1:A495;1;0));1;0) premenná makra;

4. J1 =IF(L2+1<10;"00"&L2+1;IF(L2+1<100;"0"&L2+1;L2+1)) ; tento vzorec vám do čísla faktúry zadáva poradové číslo v trojmiestnom stave ako text; je to preto, že ak je poradové číslo 1, číslo faktúry by ste mali napríklad 2009031 – sedemmiestne číslo. Ak by ste už mali faktúru s poradovým číslom 10, číslo by vyzeralo nasledovne: 20090310 (osemmiestne). Potrebujete kvôli následnému radeniu súborov a záznamov mať jednotný tvar čísla faktúry, takže bude 10miestne a číslo faktúry s poradovým číslom 1 bude vyzerať 200903001.

5. J2 =MONTH(C3) ; mesiac do čísla faktúry;

6. J3 =YEAR(C3) ; rok do čísla faktúry;

   Toto sú bunky s pomocnými výpočtami pre hlavičku, hlavne pre číslo faktúry a pre premennú do makra, ktoré zisťuje, či už faktúra bola pod daným číslom uložená. Teraz vložíte vzorce do samotnej hlavičky:

7. C1

   =J3&IF(OR(J2=1;J2=2;J2=3;J2=4;J2=5;J2=6;J2=7;J2=8;J2=9);0;"")&J2&IF(J1<9;0&J1;J1)
   

   Kompletný vzorec čísla faktúry - tu je ešte ošetrený mesiac, pokiaľ je jednomiestny.

8. C3 =TODAY() ; toto hádam vie každý (dnes);

9. C4 =C3

10. C5

    =IF(AND(OR(ISTEXT(C1);ISNUMBER(C1));ISNUMBER(C3);ISNUMBER(G12));C3+VLOOKUP(F8;odberatel.A1:G290;7;0);" ") 
    

    Tento vzorec zistí dátum splatnosti, ktorý má mať ten ktorý konkrétny odberateľ, ale len pokiaľ je vyplnený jeden z dátumov a IČO odberateľa (ak by to niekto prepísal ručne a zabudol vyplniť).

Odberateľ

F8=IF(AND(B64=1;TYPE(VLOOKUP(C11;objednavky.A2:B500;2;0))=2);VLOOKUP(C11;objednavky.A2:B500;2;0);"Názov firmy")
F9=VLOOKUP(F8;odberatel.A1:B290;2;FALSE())
F10=VLOOKUP(F8;odberatel.A1:D290;4;FALSE())
G10=VLOOKUP(F8;odberatel.A1:C290;3;FALSE())
H11=IF(LEFT(I12;2)="SK";" ";"NEPLATNÉ DIČ !")
G12=VLOOKUP(F8;odberatel.A1:E290;5;FALSE())
I12=VLOOKUP(F8;odberatel.A1:F290;6;FALSE())

Tu sa pristavím najskôr pri bunke H11 – táto je tu kvôli upozorneniu, že ak DIČ nezačína písmenami „SK“, zobrazí hlášku „NEPLATNÉ DIČ !“ Zobrazenie je ošetrené samotným vzorcom a text (farbu veľkosť a hrúbku) si naformátujte podľa vlastnej chuti). Každý si to ošetrí podľa svojich potrieb (CZ, SK) poprípade cez funkciu OR (Logickéfunkce 2 – AND, OR).

A teraz k bunke F8. Tu je okrem vzorca, ktorý vám vloží názov odberateľa, použitá ešte možnosť vybrať si ho sám zo zoznamu pomocou výberového poľa, ktoré tam dostanete nasledovne. Máte označenú bunku F8, vyberiete v menu Dáta | Platnosť... a vyberiete možnosti Povoliť oblasť buniek, Zobraziť zoznam výberu, Zoradiť položky vzostupne a do zdroja zadáte

$odberatel.$A$1:$A$490

a samozrejme potvrdíte.

K tématu nabízíme videonávod: Calc:Kontrola vstupních dat

Platnosť dát

Týmto ste do tohto zoznamu vložili celý zoznam odberateľov z listu odberatel. Ďalším krokom v hlavičke bude vložiť dve zaškrtávacie polia s názvami Podľa objednávky a Aj ako dodací list.

Zaškrtávacie polia

V minulom diele (OpenOffice.orgvo výrobnej firme 2 – Faktúra 1) ste si vložili ovládací prvok formuláru „zoskupenie“ a teraz budete postupovať presne tak ako pri „zoskupení“, len vyberiete druh ovládacieho prvku „zaškrtávacie políčko“ a vložíte ho na miesto. Potom ho cez kontextové menu upravíte.

Zaškrtávacie políčko

Hlavný rozdiel je v tom, že pri tomto ovládacom prvku si musíte na karte Dáta ovládacieho prvku určiť bunku, s ktorou je prepojený – vo vašom prípade s bunkou B64.

Karta Dáta

Ďalej si na karte Všeobecné musíte určiť názov Podľa objednávky, tlač – nie a trojstav – nie. Názov, farbu atd. si upravte podľa svojej chuti. Toto tlačidlo vám v spolupráci so vzorcami po zaškrtnutí vyplní celú faktúru na jedno kliknutie – samozrejme pokiaľ budete mať objednávku „nahodenú“ v systéme. Takto vložte aj druhé zaškrtávacie políčko Aj ako dodací list a prepojíte ho s bunkou B65.

Hlavičku by ste mali. Tlačítka hore sú prepojené s makrami, neskôr popíšem, ako si ich tam vložíte.

Telo faktúry

Začnite číslom položky, bunka A15 =IF(ISNUMBER(C15);1;" ") a A16 =IF(AND(ISNUMBER(C16);ISNUMBER(A15));A15+1;" "). Vzorec z bunky A16 nakopírujete až po riadok 54, takže po bunku A54. Teraz je čas vložiť si vzorce do buniek s pomocnými výpočtami.

Tieto bunky sa nebudú v tlačovom výstupe zobrazovať, nakoľko v minulom diele ste si určili oblasť tlače a oni sú umiestnené mimo nej.

K15 =IF(ISERROR(VLOOKUP(B15;kalkulacia.$A$1:$BF$1000;57;0)*C15);0;(VLOOKUP(B15;kalkulacia.$A$1:$BF$1000;57;0)*C15))

Prekopírujete opäť po riadok 54. Tento vzorec vám zistí váhu tovaru, ktorý je v riadku faktúry. Nemusíte ho tam však nutne mať, je to dobré kvôli logistike, ak chcete vedieť, koľko bude vážiť tovar z faktúry. Výsledok je zobrazený v bunke J13 =SUM(K15:K54).

Do L15 tovar 1, M15 Množstvo 1, N15 cena1 a O15 % zľavy 1. Toto zas prekopírujete po riadok 54, takže na riadku 54 budete mať Tovar 40. Použijete to vo vzorcoch v tele faktúry.

Tieto názvy musia korešpondovať s hlavičkou v liste objednavky.

P15 =IF($B$64=TRUE();VALUE(VLOOKUP($C$11;objednavky.$A$1:$FK$490;MATCH($O15;objednavky.$A$1:$FK$1;0);0));" ")

Tento vzorec vyhľadá v riadku faktúry zľavu pre konkrétnu objednávku.

Q15 =IF(AND(ISNUMBER(C15);ISNUMBER(E15));C15*E15;" ")

Pomocný výpočet pre cenu bez zľavy. P15 a Q15 tiež prekopírovať po riadok 54, súčet zľavy je v K55 =SUM(Q15:Q54). K zľave ešte poznámočka: v hlavičke faktúry, kde má byť uvedená zľava, t.j. bunka G14, je vzorec =IF(SUM($G$15:$G$53)<>0;"Zľava % ";" ") a H14 =IF(SUM($G$15:$G$53)<>0;"Zľava € ";" "). Takže pokiaľ zľavu nedáte, hlavička stĺpca je prázdna.

Ideme ďalej na telo, na bunku B15:

=IF(AND($B$64=TRUE();ISNUMBER(VLOOKUP($C$11;objednavky.$A$1:$FK$490;MATCH($M15;objednavky.$A$1:$FK$1;0);0));J15=0);VLOOKUP($C$11;objednavky.$A$1:$FK$490;MATCH($L15;objednavky.$A$1:$FK$1;0);0);IF(J15<>0;VLOOKUP(J15;cena.$A$1:$B$1001;2;0);" "))

Tento vzorec textovo povedané robí nasledovné:

• ak zadávate podľa čísla objednávky a je táto objednávka v databáze a nevyplnili ste kód tovaru, tak nájdi názov tohto;

• ale ak nezadávate podľa čísla objednávky, tak pozri, či je vyplnený kód tovaru a nájdi jeho názov; inak nezadaj nič.

V tejto bunke (a nasledujúcich prekopírovaných) je použitá možnosť vybrať si zo zoznamu, presne tak, ako ste to robili pri odberateľovi pomocou menu Dáta | Platnosť... Do zdroja zadáte $kalkulacia.$A$1:$A$3992 . Týmto máte v zozname všetky názvy tovarov, samozrejme ak zadáte tovar, ktorý nie je v zozname, tak vám vyhodí hlášku „neplatná hodnota“; no túto prijmete a pokračujete.

Podobne funguje vzorec aj v bunke C15

=IF(AND($B$64=TRUE();ISNUMBER(VLOOKUP($C$11;objednavky.$A$1:$FK$4990;MATCH($M15;objednavky.$A$1:$FK$1;0);0));J15=0);VLOOKUP($C$11;objednavky.$A$1:$FK$490;MATCH($M15;objednavky.$A$1:$FK$1;0);0);" ")

D15 =IF(ISTEXT(VLOOKUP(B15;kalkulacia.$A$1:$C$999;3;0));VLOOKUP(B15;kalkulacia.$A$1:$C$999;3;0);" ")

Vyhľadá MJ v liste kalkulácia.

E15 =IF(AND($B$64=TRUE();ISNUMBER(VLOOKUP($C$11;objednavky.$A$1:$FK$490;MATCH($N15;objednavky.$A$1:$FK$1;0);0));J15=0);VLOOKUP($C$11;objednavky.$A$1:$FK$490;MATCH($N15;objednavky.$A$1:$FK$1;0);0);IF(J15<>0;VLOOKUP(J15;cena.$A$1:$C$1000;3;0);IF(ISNUMBER(VLOOKUP(B15;cena.$B$1:$C$1000;2;0));VLOOKUP(B15;cena.$B$1:$C$1000;2;0);" ")))

Podobne ako C a D, pretože v prijatej objednávke mohla byť iná cena, ako je práve aktuálna.

F15 =IF(ISNUMBER(C15);0,19;" ")

Pozor – %DPH je priamo obsiahnuté vo vzorci, nakoľko sa až tak často nemení. ;-) Kto však chce, môže si niekde nabok do voľnej bunky napríklad J5 dosadiť výšku DPH a do vzorca to napísať nasledovne: =IF(ISNUMBER(C15);J5;" ")

G15 =IF(AND($B$64=1;ISNUMBER(P15));P15;" ")  % zľavy 1

H 15 =IF(AND(NOT(ISERR(E15*G15<>0));E15*G15<>0);ROUND(G15*Q15;2);" ") zľava 1

Zaokrúhľovanie si samozrejme určite podľa svojich potrieb.

I15 =IF(AND(Q15<>" ";H15<>" ");Q15-H15;IF(Q15<>" ";Q15;" "))

Teraz si označte B15:I15 a prekopírujte po riadok 54 a vzorce v tele máte.

Pätka faktúry

Pojem pätka je iba názvom časti dokumentu pod telom faktúry a nemá nič spoločné s pätkou listu, ktorú si doplníte neskôr pri konečnom formátovaní listu.

Tu sú umiestnené nasledovné výpočty: K55 € bez zľavy vzorec =SUM(Q15:Q54) . Tento výpočet je len pre vašu informáciu.

A56 =IF(B65=TRUE();"Tento daňový doklad slúži zároveň ako dodací list.";" ") 

Tu sa vám zobrazí po zaškrtnutí zaškrtávacieho poľa, ktoré ste si vytvorili v hlavičke, nápis: Tento daňový doklad slúži zároveň ako dodací list.

G55 =IF(SUM($H$15:$H$54)<>0;"Zľava spolu:";" ")

Tu je ošetrené to, že sa vám nezobrazí resp. zobrazí Zľava spolu:, ak máte niekde zľavu poskytnutú.

I55 =IF(SUM(H15:H54)<>0;SUM(H15:H54)*-1;" ")

Samotná bunka pre výpočet a zobrazenie zľavy. V bunkách D58:H62 popíšem len vzorce, nakoľko texty a formát by ste si tu vyplnili už v minulom diele.

F59 =ROUND(K55;2)+I55 výpočet základu DPH 

Toto zaokrúhlenie, ako aj zaokrúhlenia vo všetkých výpočtoch, sú robené pre daňový systém na Slovensku, v Čechách si to musíte prerobiť podľa vašej legislatívy.

H59 =ROUND(F59*0,19;2)

Výpočet výšky DPH

I59 =SUM(F59;H59)

Súčet

F60 =IF(F59=0;0;ROUND((F59*$E$61);2))

Výpočet základu DPH, informatívne v Sk, nakoľko ešte stále sa musí uvádzať (30.03.2009).

H60=IF(H59=0;0;ROUND((H59*$E$61);2))

Výpočet výšky DPH informatívne v Sk.

I60 =IF(I59=0;0;ROUND((I59*$E$61);2))

Súčet informatívne v Sk.

H61 =IF(H62=0;0;ROUND((H62*$E$61);2))

Súčet informatívne v Sk

H62 =I59 

A nakoniec súčet v €

B62 - Tu je použitá ešte možnosť výberu zo zoznamu pomocou výberového poľa, ktoré si urobíte presne tak ako v bunke F8, do zdroja zadáte $fakturanti.A1:A10 a samozrejme potvrdíte.

Teraz si ešte vložíte vyššie spomenuté tlačidlá. Tlačidlo Export do pdf – už z názvu je jasné, na čo vám bude slúžiť. Ako ste určite uhádli, toto tlačidlo vám po zatlačení vyexportuje faktúru do .pdf formátu, a to tak, že spustí makro, ktoré to vykoná.

Makrám sa budeme venovať v nasledujúcom diele.

Tlačidlo Ukončiť slúži na spustenie makra, ktoré vám vyexportuje dáta, resp. ak bola faktúra neskôr zmenená, tak ich aktualizuje, uloží faktúru pod jej číslom do zložky faktúry a zavrie dokument. Kto to nechce robiť pomocou stlačenia tlačidla, je možnosť spustiť makro aj pri zatváraní dokumentu, takže makro sa vám spustí vždy, ak dokument zavriete. Urobíte to nasledovne.

Menu Nástroje | Prispôsobiť | Udalosti a vložiť cestu k makru v udalosti Zatvoriť dokument. Nezabudnite mať na spodku karty vybratú možnosť uložiť v faktúra.ods, nie v aplikácii.

Prispôsobiť udalosť

Tlačidlo Tlač faktúry spustí makro pre tlač, ktoré vytlačí faktúru dva krát (kto chce, bude si môcť pridať počet, aký bude chcieť).

A nakoniec tlačidlo Nový. Pokiaľ ešte nemáte v databáze odberateľa, ktorého chcete zadať pomocou makra spúšťaného týmto tlačidlom, tak ho zadáte do dokumentu kontakty.ods. Následne budú dáta o novom odberateľovi exportované do všetkých dokumentov, v ktorých je uvedený a tento dokument bude uložený. Potom sa zavrie dokument, z ktorého ste makro spustili a otvorí sa čistá faktúra už s aktualizovanou databázou odberateľov, v ktorej bude aj vami vložený odberateľ. Priznám sa, že toto tlačidlo takmer nepoužívam a nechal som ho tu preto, aby som mohol prezentovať ďalšie možnosti.

Tlačidlá si vložte presne tak, ako ste si už predtým vložili ovládacie prvky „zaškrtávacie políčko“ a presne tak ho aj upravte – nezabudnite na karte Všeobecné určiť názov a vybrať možnosť tlač – nie. Jediný rozdiel bude v tom, že neskôr, keď budete mať makrá, na karte Udalosti doplníte pri inicializácii cestu k makru, ktoré sa má spustiť.

Záverečné formátovanie

Kontextové menu Skryť

Označte si v hlavičke riadkov riadky 64 a 65 a v kontextovom menu ich skryte.

Tak isto si môžete skryť aj stĺpce L až Q. Potom si označte oblasť A1:K62 a vyberte v kontextovom menu Formát buniek kartu Ochrana bunky a vyberte možnosť Skryť vzorec. Ďalej vyberte A1:B12 a tak isto ako v prechádzajúcom prípade otvorite kartu Ochrana bunky, tu navyše zadajte ešte možnosť Chránené.

Skryť vzorec a chránené

Toto urobte aj s nasledujúcimi bunkami a oblasťami:

A14:I14, C1, C7:C10, F1:I5, F9:I12, H15:I62, A56, G55, D58:G62

Sformátujte si bunky, v ktorých máte percenta na formát percent. Sformátujte si všetky bunky, v ktorých máte ceny, na formát mena, tj. E15:E54, H15:I54, F59:I59. V oboch prípadoch si nezabudnite vo formáte buniek určiť, koľko desatinných miest chcete zobraziť. Menu aj percentá si sformátujte aj v liste pdf.

Pokiaľ ste si ešte nestihli prehodiť menu v nastaveniach OpenOffice.org, tak to urobíte nasledovne: Nástroje | Možnosti | Nastavenie jazyka | Jazyky | Východzia mena.

Nastavenie meny

Rozdelenie okna

Následne si rozdeľte zvisle okno tak, že si umiestníte kurzor tesne nad zvislý posuvník a pretiahnete myšou rozdelenie okna do riadku 21. Následne si vyberiete Okno | Ukotviť.

Ďalším krokom bude skrytie záložiek listov a hlavičiek stĺpcov a riadkov: v Nástroje | Možnosti | OpenOffice.org Calc | Zobraziť odznačiť Hlavičky stĺpcov/riadkov a Záložky listov a nechať vybratú možnosť Nulové hodnoty.

Možnosti zobrazenia

Na záver si ešte môžete upraviť pätku strany: Formát | Strana. Určite si na karte Okraje nastavte Vzdialenosť k obsahu na „0“ , na karte Päta okraje „0“ a stlačte Upraviť; potom si tam vpíšte napríklad telefónne číslo, e-mail a web firmy.

Štýl strany – okraje

Teraz si vyberte bunku C11, to preto, že ak uložíte dokument ako šablónu, tak sa vám nový dokument vždy otvorí presne tak, ako ste si uložili pôvodný. Vyberte Nástroje | Zamknúť dokument | List a v dialógu Heslo si nezadajte nič, len potvrďte OK (kto chce, môže si dokument samozrejme aj „zaheslovať“).

Štýl strany – päta

Nakoniec si dokument uložte a ak ste to urobili, tak teraz si ho znovu uložte – ale ako šablónu: Súbor | Uložiť ako a vyberte si váš priečinok so šablónami (Firma | Šablóny) a uložte nasledovne: názov súboru faktúra, uložiť vo formáte šablóna tabuľky ODF(.ots).

Štýl strany – upraviť pätu

Teraz si tento súbor zavrite a opäť ho otvorite tak, že naň kliknete. Mal by sa vám otvoriť súbor napríklad „bez názvu1“ - takto vyzerá vaša faktúra. Dalo to dosť práce, no nemusíte sa toho báť, len trochu trpezlivosti a výsledok sa dostaví. Nabudúce si povieme niečo o makrách.

Uložiť ako šablónu