Základní škola a mateřská škola Bzenec

1. Základní škola a mateřská škola Bzenec Číslo projektu: CZ.1.07/1.4.00/21.2769 Číslo a název šablony klíčové aktivity: III/2: využívání ICT – inovace Vypracoval/a: Mgr. Jana Presová Ověřil/a: Mgr. Jana Presová

2. Název výukového materiálu: Vytváříme tabulky a grafy Vzdělávací obor: výpočetní technika Tematický okruh: pracujeme v tabulkovém procesoru MS Excel Téma: III. Jednoduché vzorce a adresování buněk Stručná anotace: Prezentace shrnující zásady vytváření jednoduchých vzorců obsahujících základní matematické operace a správné adresování buněk použitých ve vzorcích.

3. Kapitola III.

4. Jednoduché vzorce Než se pustíme do těžkých vzorců, nejprve se seznámíme s vytvářením těch nejjednodušších vzorců. Ty jsou tvořeny základními matematickými operacemi, a to sčítáním, odčítáním, násobením a dělením. Vzorce jsou rovnice, které automaticky provádějí výpočty se zadanými hodnotami.

5. Co platí? • vzorec musí vždy začínat znakem "=" • dále se ve vzorcích mohou vyskytnout závorky • příklady jednoduchých vzorců: =A1+A2 … součet obsahů buněk A1 a A2 =5*A1–A2 … obsah buňky A1 se vynásobí 5 a odečte se obsah buňky A2 =5*(A1–A2) … od A1 se odečte A2 a rozdíl se vynásobí 5 • jakmile ukončíme zápis vzorce, v buňce se objeví vypočítaná hodnota

6. Kde je vzorec? • v buňkách vidíme výsledky vzorců • pokud je nějaká buňka aktivní, vzorec je vidět v řádku vzorců - editačním řádku (jinak není poznat, zda jsme do dané buňky napsali číslo nebo vzorec) • pokud chceme vidět vzorce, lze si je zapnout přes Nástroje – Možnosti – zde zaškrtnutím políčka Zobrazení vzorců nastavíme to, že místo výsledků budou vidět vkládané vzorce ve verzi 2007 je to ukryto pod Vzorci, Zobrazit vzorce

7. Sčítání a odčítání • k vytváření vzorců použijeme tabulku, kterou máme již vytvořenou, např. Barvy aut • do prázdné buňky pod pondělím si chceme nechat spočítat počet projetých aut – tedy do této buňky musíme vložit vzorec pro sčítání: klikneme do buňky B9 a napíšeme vzorec =B4+B5+B6+B7+B8 po stisknutí klávesy Enter se do buňky B9 napíše výsledný součet

8. Sčítání a odčítání • totéž uděláme i v buňce C9, D9, E9, F9, G9 a H9 • v buňce C10 budeme chtít nechat zobrazit, o kolik více projelo aut v úterý než v pondělí, tedy od součtu úterních aut odečteme pondělní počet aut (viz. obrázek) • zároveň nám Excel vždy barevně zvýrazní všechny buňky, které se vzorci vyskytují, to nám umožňuje rychlejší kontrolu správnosti zapsaného vzorce

9. Násobení a dělení • násobení … znak * (je na numerické klávesnici) • dělení … znak / • postup je stejný jako u sčítání a odčítání, např. v tabulce Nákupy chceme mít ve sloupečku D vypočítané celkové ceny za jednotlivé položky, do buňky D4 vepíšeme vzorec =B4*C4 • ostatní - obdobně

10. Násobení a dělení • pokud bychom chtěli např. v buňce B8 mít vypočítané kolikrát je autíčko dražší než tužka, použijeme k tomu dělení a napíšeme do dané buňky vzorec = B4/B7 • znak : se pro dělení nevyužívá, v Excelu má úplně jiný význam, ale o tom až u funkcí!

11. Vzorce • všechny operace lze libovolně kombinovat, tak jako v matematice, mohou se používat závorky, můžeme obsah buněk násobit čísly, … • např.: =B5–2*B8 =D6+3*(C2-D4) =E1-B2/C7 =C5*(C7-C4+2*C2)-(3*C1+C6)/8 • do vzorce nikdy nepíšeme obsah buňky (konkrétní číslo), ale jen její adresu, protože pokud změníme číslo v buňce, Excel okamžitě všechny vzorce přepočítá

12. Oprava vzorce • Pokud ve vzorci máme chybu, můžeme ji kdykoliv opravit: • rozklikneme vzorec (2xklikneme do buňky) • opravíme chybu buď přímo v buňce nebo v řádku vzorců, kde je vidět celý vzorec

13. Zjednodušení • abychom nemuseli stejný vzorec psát pořád dokola, můžeme si zjednodušit práci kopírováním • Excel při kopírování automaticky opravuje adresy buněk ve vzorcích (při kopírování dolů mění řádky, při kopírování do boku mění sloupce

14. Jak na to? • Ctrl+C a Ctrl+V • Najedeme myší na pravý spodní roh buňky se vzorcem a potáhneme za něj (dolů, do boku, …) tam, kam se mají vzorce vkládat • 2x na ten roh klikneme (ne vždy funguje tento způsob funguje!!!)

15. Chybové hlášení • pokud vzorec napíšeme špatně, v buňce se místo výsledku objeví chybové hlášení, např.: • #DIV/0! – dělení nulou nebo buňkou, která neobsahuje žádnou hodnotu • #HODNOTA! - ve funkci byl použit nepřípustný argument,(např. výpočet má být proveden s buňkou, ve které je zapsán text místo čísla, …) • #NÁZEV! - ve vzorci nebo funkci byl použit výraz, který Excel nezná (např. překlep, špatně vybraná oblast, chybí uvozovky, …)

16. ####### - zobrazí se v buňce, když sloupec není dostatečně široký, tj. není místo pro zobrazení výsledného čísla • #N/A – hodnota není pro vzorec k dispozici (not available), setkáme se s tím např. u vyhledávacích funkcí • #REF! - ve funkci či vzorci použijeme adresa buňky, která byla odstraněna • #NUM! - vzorec nebo funkce obsahuje neplatné číselné hodnoty (např. odmocnina ze záporného čísla, …) • #NULL! -tato chyba se objeví např. při průniku dvou oblastí, které nemají nic společného

17. Adresy buněk - relativní • při kopírování vzorců se nám automaticky přepisují ve vzorcích adresy buněk, protože používáme relativní adresy • poznáme je podle zápisu, např. A1, D28, AA12, DF245,… • ale ne vždy se nám to hodí, aby se adresy buněk měnili, proto používáme i jiné typy adresování

18. Adresy buněk - absolutní • při kopírování vzorců se nám adresa buňky, která je zadaná absolutně, nezmění • poznáme je podle zápisu, např. $A$1, $D$28, $AA$12, $DF$245,… • symbol dolaru $ – označuje absolutní adresu • vyskytuje se jak u označení sloupce tak i řádku

19. Adresy buněk - smíšené • symbol $ – je jen u řádku nebo sloupce a používáme to tehdy, kdy povolíme při kopírování vzorců buď změnu řádku nebo sloupce • při kopírování vzorců se nám adresy buněk mění jen částečně • poznáme je podle zápisu, např. A$1, $D28,…

20. Příklad použití smíšené adresy • např. při výpočtu zisku z prodaného zboží (viz. obrázek) Do spodní tabulky chceme dostat tržbu za prodané zboží v jednotlivých městech a měsících. Do buňky B20 vzorec pro násobení cena x počet prodaných kusů. Cena je ve sloupci B při kopírování dolů se bude ve vzorci měnit číslo řádku – to může při kopírování doprava se změní písmena sloupců (B na C, C na D) a to se u ceny nesmí stát proto použijeme smíšenou adresu!! =$B4*C4

21. Příklad použití absolutní adresy • např. při převodu korun na eura – kurz si napíšeme do jedné buňky, a touto hodnotou budeme chtít vynásobit několik buněk s cenami v korunách, proto bude ve vzorci tato buňka zadána absolutně zisk zisk =(B4*C4)*$B$8 =(B4*E4)*$B$8 V závorce je výpočet ceny 500 prodaných kusů, pak je to vynásobeno buňkou s DPH, která se při kopírování nesmí měnit