List export
V liste si najskôr vyplňte hlavičku napríklad tak, ako je vo vzorovom súbore objednávka1.ods.
Táto hlavička je totožná aj v súbore objednávky.ods, ten si môžete zároveň vytvoriť a uložiť ho do priečinku databázy, ktorý bol spomenutý v prvej časti seriálu. Do tohto súboru sa vám budú neskôr exportovať záznamy z novovytvorených objednávok/cenových ponúk a upravovať záznamy z už uložených no zmenených objednávok . Tento súbor má dva listy a to s názvom objednavky a ponuky.
Ak už v prvom riadku máte popisky, tak môžete vložiť vzorce do druhého riadku nasledovne:
Ako ste si všimli, popisky v prvom riadku vám korešpondujú s popiskami na liste objednávka.
Vzorce:
A2=IF(objednávka.C7=0;C2;objednávka.C7) B2=objednávka.F4 C2=objednávka.L1+1 D2=objednávka.C6 E2=objednávka.H55 F2=objednávka.H57 G2=objednávka.B54
Až sa dostanete k bunke H2, čo je stĺpec Tovar1, táto hodnota zodpovedá prvej hodnote v tele objednávky a to v bunke B12, takže vzorec je =objednávka.B12.
I2=objednávka.D12 J2=objednávka.C12 K2=objednávka.I12
Tieto štyri za sebou nasledujúce stĺpce Tovar1, Množstvo1, cena1 a %zľavy1, vám reprezentujú jeden riadok dát z tela listu objednávka.
Nasledovne budete postupovať až po %zľavy40 podobne ako vo faktúre (diel 2).
List spotreba
Tento list je pomocný list pre list výroby.
Hlavička tabuľky je v siedmom riadku a popisy musia byť presne také, ako vo vzorovom dokumente, nakoľko ich používajú vzorce na vyhľadávanie. Tieto popisky korešpondujú s popismi stĺpcov v liste kalkulácia.
Vzorce:
A8=objednávka.B12 B8=objednávka.D12
Tieto vzorce prekopírujte po riadok 47. Do buniek A48 a B48 si opäť vložíte tie isté vzorce, takže v A48=objednávka.B12 a v B48=objednávka.D12 a tieto si prekopírujete do ďalších 40 riadkov. Takto to urobíte 12 krát, takže skončíte na riadku 487.
Teraz ďalšie vzorce:
C8=IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(C$7;$kalkulácia.$A$1:$AY$1;0);0);0) D8=IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(D$7;$kalkulácia.$A$1:$AY$1;0);0);0) E8=IF(ISERR(IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(E$7;$kalkulácia.$A$1:$AY$1;0);0);0)*B8);0;IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(E$7;$kalkulácia.$A$1:$AY$1;0);0);0)*B8)
Tieto tri bunky si označte a prekopírujte po riadok 47.
C48=IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(F$7;$kalkulácia.$A$1:$AY$1;0);0);0) D48=IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(G$7;$kalkulácia.$A$1:$AY$1;0);0);0) E48=IF(ISERR(IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(H$7;$kalkulácia.$A$1:$AY$1;0);0);0)*B8);0;IF(AND($B8>0;$B8<>" ");VLOOKUP($A8;$kalkulácia.$A$1:$AY$995;MATCH(H$7;$kalkulácia.$A$1:$AY$1;0);0);0)*B8)
Tieto vzorce prekopírujte do ďalších 40 riadkov. Ako vidíte, sú takmer totožné s predošlými, len s rozdielom vo funkcii MATCH(E$7;$kalkulácia.$A$1:$AY$1;0);0);0)
, kde sa mení kritérium vyhľadávania, takže v týchto 40 riadkoch vám vypočíta ďalšiu sadu popisov a to materiál 2, hrúbka 2 a mn 2. Takto budete postupovať opäť 12 krát, posledný vzorec bude v riadku 487.
Do buniek AN7 až AN18 si napíšte text MJ 1 až MJ 12, toto opäť korešponduje s hlavičkami stĺpcov v liste kalkulácia.
AM8=IF(A8<>" ";VLOOKUP(A8;$kalkulácia.$A$1:$AY$995;MATCH($AN$7;$kalkulácia.$A$1:$AY$1;0);0);0)
Prekopírovať po riadok 47.
AM48=IF(A48<>" ";VLOOKUP(A48;$kalkulácia.$A$1:$AY$995;MATCH($AN$8;$kalkulácia.$A$1:$AY$1;0);0);0)
Tak isto prekopírovať ďalších 40 riadkov. Zmena vo vzorci je znova vo funkcii MATCH($AN$8;...
, kedy už hľadáme hodnotu MJ 2. Postup, ako ste už isto dedukovali, opakujte 12 krát a skončíte na riadku 487. Týmto je aj list spotreba kompletný.
List výroby
Tento list slúži na určenie druhu a množstva materiálov potrebných k výrobe jednotlivých výrobkov. Obsahuje okrem výpočtov aj dve tabuľky sprievodcu dátami, z ktorých jedna slúži ako podklad výpočtov a druhá je sumár materiálov. Máte tu ešte ovládací prvok – tlačidlo s názvom Späť na objednávku, ku ktorému bude priradené makro pre návrat na list objednávka.
Nakoľko je z tohto listu aj tlačový výstup, formátovanie a vyplnenie hlavičky je podľa nasledovného obrázku.
Oblasť tlače v tomto liste je $A$1:$J$58 a vložíte si ju ako v diele 2.
Vzorce:
A4=objednávka.F4 -odberateľ G3=NOW() - dátum G4=IF(objednávka.C6<>0;objednávka.C6;" ") - termín G5=IF(objednávka.C7<>0;objednávka.C7;" ") - číslo objednávky
Bunky A9 až J48 sú podmienene formátované tak, aby sa vám zobrazilo ohraničenie presne podľa toho, kam až siahajú vyplnené dáta.
Dosiahnete to nasledovne:
Musíte si vytvoriť štýl, ktorý potom aplikujete v podmienenom formátovaní na bunku. Vyberte Formát | Štýly a Formátovanie | Nový štýl z výberu a zadajte si názov štýlu, čo chcete vytvoriť. Napríklad ohraničená.
Potom si vytvorený štýl označíte v zozname štýlov a vyberiete upraviť.
Tu si určite okraje a potvrďte.
Teraz už máte urobený nový štýl s názvom ohraničená.
Označte si bunku A9 a vyberte vo Formát | Podmienené formátovanie. Do podmienky 1 zadajte vzorec IF($'list výroby'.$S4>0;1;0)
a štýl bunky vyberte z roletového menu ten, ktorý ste práve vytvorili, takže ohraničená.
Teraz si prekopírujte bunku A9 v riadku po bunku J9 , týmto ste si skopírovali podmienené formátovanie.
Teraz si vyplňte ďalšie vzorce.
A9=O4 B9=IF(ISTEXT($A9);VLOOKUP($A9;objednávka.$B$12:$E$51;3;0);" ") C9=IF(ISTEXT($A9);VLOOKUP($A9;objednávka.$B$12:$E$51;4;0);" ") D9=P4 E9=IF(Q4<>0;Q4;" " F9=IF(AND(S4>0;R4="m2");ROUNDUP(S4/2;1);" ")
Označte A9:J9 a skopírujte po riadok 48, čím ste zároveň so vzorcami skopírovali aj podmienené formátovanie.
Prvú tabuľku sprievodcu dátami si vytvorte nasledovne:
Označte si oblasť A7:AM487 v liste spotreba a spustite sprievodcu dátami - Dáta | Sprievodca dátami | Spustiť. Definujte riadkové a dátové polia, zrušte súčty stĺpcov a riadkov a zadajte výsledok do $'list výroby'.$O$1
, podľa nasledovného obrázku.
Teraz si vyvolajte vo vytvorenej tabuľke kontextové menu, zvoľte filter a nastavte ho podľa nasledovného obrázku.
Pokiaľ pracujete s plechmi, alebo s materiálom, ktorý je na tabuli, tak si vytvorte druhú tabuľku z oblasti A8:F48, definujte ju a vytvorte filter podľa nasledovného obrázku.
Sprievodca dátami a filter tabuľka 2
Samozrejme dá sa to aplikovať aj na inú výrobu, len napríklad tam nemusíte mať počet tabúľ, prispôsobte si to presne podľa toho, s čím pracujete.
Tým by bol list výroby kompletný.
List kód
Tento list je pomocný list pre výpočty v liste objednávka. Doplňte si hlavičky stĺpcov podľa vzorového súboru.
Vzorce:
A2=kalkulácia.B2 B2=kalkulácia.A2
Tieto dve bunky si označte a vzorce prekopírujte po riadok 1001.
V oblasti C2:C1001 je vzorec poľa {=VLOOKUP(A2:A997;$cena.$A$2:$B$5000;2;0)}
. Oblasť D2:D1001 vzorec poľa {=VLOOKUP(A2:A997;$cena.$A$2:$C$5000;3;0)}
. Oblasť E2:E1001 vzorec poľa {=VLOOKUP(A2:A1001;kalkulácia.B2:E1001;4;0)}
.
F2=D2-E2 prekopírovať po riadok 1001
Oblasť G2:G1001 vzorec poľa {=VLOOKUP($A$2:$A$1001;kalkulácia.$B$2:$F$1001;5;0)}
H2=F2+G2
List kód máte kompletný.
Listy s dátami
Sú to nasledovné listy, v ktorých si vyplňte hlavičky stĺpcov podľa vzorového dokumentu objednávka1: odberateľ, kalkulácia, cena, fakturanti. Do týchto listov sa vám pomocou makier nakopírujú dáta zo zdrojových dokumentov, podobne ako v dokumente faktúra.
List číslo objednávky
Vyplňte si hlavičky stĺpcov. Otvorte si dokument objednávky.ods, ktorý máte v priečinku databázy.
Prejdite späť na dokument objednávka1, na list číslo objednávky, bunka A2 zadajte =. Prejdite na dokument objednávky a označte bunku C2 v liste objednávky a potvrďte klávesom [Enter].
Ďalej v dokumente objednávka1, v liste číslo objednávky, v bunke B2 zadajte=.
Prejdite na dokument objednávky a označte bunku C2 v liste ponuky a potvrďte.
Týmto ste vložili do dokumentu ďalšie dva listy. Postup je podobný, ako v dokumente faktúra (diel 3).
Označte si bunky A2 a B2 a prekopírujte po riadok 1000.
List číslo objednávky je kompletný.
List pdf
Tento list vám bude slúžiť na export objednávky/cenovej ponuky do súboru .pdf (napríklad pokiaľ chcete list odosielať aj e-mailom) a z tohto listu je taktiež aj tlačový výstup.
List si naformátujte podľa vzorového dokumentu.
Označte oblasť A1:J58, Formát | Oblasti tlače | Pridať.
Vzorce:
C1=objednávka.C1 C4=objednávka.C4
Ďalej postupujte tak, ako ste to robili v dokumente faktúra (diel 2). Je zrejmé, že bunky korešpondujú s bunkami v liste objednávka. Po vyplnení máte list pdf kompletný.
Na záver, pretože nemáte nikde v dokumente uvedené dáta o vašej firme, si ešte ešte upravte formát strany nasledovne: Formát | Strana | Päta | Upraviť.
A ešte hlavičku: Formát | Strana | Hlavička | Upraviť.
Nezabudnite si dokument uložiť a nabudúce ideme na makrá pre dokument objednávka.