Pravděpodobně hlavní funkcí Excelu je možnost provádět výpočty. Jednoduché tabulky se mohou vytvářet také např. ve Wordu, ale tam prakticky nelze provádět výpočty (sice tam lze doplnit např. součet, ale výsledek se bohužel neaktualizuje při změně hodnot). Na výpočty je prostě nejlepší Excel.
Výpočty se provádějí samozřejmě s čísly, některé operace je ale možné provádět s texty (textové funkce, spojování textů pomocí &). Občas může dojít také k záměně, např. u čísel nula – písmeno O, desetinná čárka –
tečka. Proto je vhodné provádět kontrolu na obsah, např.:
- vybrat buňky a zkontrolovat informace na stavovém řádku
- použít kontrolní funkci JE.ČISLO (ISNUMBER v anglické verzi) – viz dále
K výpočtům v Excelu také patří množství různých pomůcek, které velmi usnadňují práci.
Obecně mohou být v Excelu tyto druhy výpočtů.
- vzorce – např.: =(A1+A2)*1,21
- funkce – např.: =SUMA(J9:J23;J25:J28)
Každý výpočet musí začínat znakem rovná se a v buňce nesmí být nastavený textový formát.Funkce lze ještě rozdělit na základní (nejčastěji součet a průměr) a všechny další – celkem obsahuje Excel přibližně 400 funkcí.
Ve vzorcích se používají základní matematické operátory (+ – * /), konstanty a adresy buněk, kulaté závorky ( ) a některé speciální operátory (mocnina: ^, spojení textů: & ).
Zápis výpočtu může obsahovat i kombinaci výpočtů:
=SUMA(J9:J23;J25:J28) * 1,21
Vzorce – pravidla zápisu
Vzorce mohou být jednoduché, například sečtení dvou čísel, nebo velmi složité. K výpočtům používají data z ostatních buněk a výsledek zobrazí v buňce, kde je zapsán vzorec.
Při vytváření vzorce se musí dodržovat několik pravidel:
- zápis každého vzorce musí začínat znakem „=“. Jestliže nenapíšeme =, bude Excel považovat vzorec za obyčejný text,
- parametry funkcí musí být uzavřené v kulatých závorkách a odděleny oddělovačem,
- funkce musí být opatřena kulatými závorkami i v případě, že nepoužívá argumenty. Například funkci, která vrací číslo π, zapíšeme ve tvaru PI(),
- vzorec musí být zapsán syntakticky správně, jinak Excel nedovolí ukončit editaci buňky (např. nesmí chybět závorka),
- při psaní vzorců si uvědomte, že platí priorita matematických operací. To, co děláme v praxi podvědomě, musíme počítači přikázat naprosto přesně,
- nejsme-li si jisti, vnutíme programu své pořadí operací pomocí kulatých závorek. Jiné závorky než kulaté nepoužíváme, mohou mít jiné funkce.
Vzorce – složení
Vzorec vložený do buňky se může skládat z následujících prvků:
- operátory – jsou to + pro sčítání (součet), – pro odečítání (rozdíl), * pro násobení (součin), / pro dělení (podíl), ^ pro mocninu,
- odkazy na buňku – jsou to jména buněk nebo rozsahů. Mohou odkazovat na buňky v daném pracovním listu, v jiném pracovním listu nebo dokonce v jiném pracovním sešitu,
- funkce – jsou to připravené funkce např. SUM, SIN nebo PRŮMĚR a jejich argumenty,
- závorky – ovlivňují pořadí, v jakém se budou jednotlivé části vzorce řešit.
Při výpočtu vzorce je daná priorita matematických operací, která určuje pořadí výpočtu.
- závorky
- umocňování
- násobení, dělení
- sčítání, odčítání
Vytvoření vzorce pro součet dvou buněk:
- každý vzorec začíná znakem „rovná se“. Napíšeme znak do buňky. Pokud na prvním místě bude jiný znak, bude Excel považovat vložený řetězec znaků za text (=).
- vložíme odkaz na první buňku, kterou chceme sečíst (=A1).
- napíšeme znaménko „plus“ (=A1+).
- vložíme odkaz na druhou buňku, kterou chceme přičíst. (=A1+A2).
- potvrdíme klávesou Enter (zobrazí se výsledek).
Po stisku klávesy Enter se vzorec změnil na konkrétní hodnotu. Ve skutečnosti však buňka i nadále obsahuje vzorec. Jestliže změníme jakékoliv číslo, s nímž vzorec kalkuluje, okamžitě po změně bude vzorec přepočítán a zobrazena aktuální hodnota. Tato schopnost činí z Excelu mocný nástroj zejména při výpočtech rozsáhlých a provázaných tabulek, kde jsou na jeden výsledek vázány další výpočty a na tento zase další výsledek atd.
Samotný vzorec lze prohlédnout pomocí řádku vzorců. Nastavíme kurzor na vzorec a jeho obsah zobrazí řádek vzorců. Obsah vzorce je možné kdykoliv upravit po stisku klávesy F2.
Relativní a absolutní odkaz
Aby měl vzorec smysl, používá odkazy na proměnné v buňkách, např. =C5*A1. Zapsání adres ve tvaru A1; B4; C3 je nejobvyklejší způsob zápisu. Nejprve píšeme název sloupce a pak okamžitě a bez mezery číslo řádku.
Excel používá absolutní a relativní adresování buněk. U relativního adresování se adresy (odkazy) na buňky mění podle toho, jak se mění poloha buňky. Změna polohy buňky absolutní odkaz neovlivní.
Excel umožňuje vytvářet smíšené odkazy.
- Relativní odkaz – pokud vytvoříme vzorec, jsou odkazy na buňky nebo oblasti obvykle založeny na jejich umístění vzhledem k buňce, která obsahuje daný vzorec. Pokud přesuneme vzorec s relativními odkazy do jiné buňky, upraví aplikace Excel odkazy ve vzorci tak, aby odkazovaly na jiné buňky vzhledem k novému umístění vzorce. Pokud přesuneme buňku, na kterou jsou vytvořeny odkazy na jinou pozici, Excel všechny odkazy automaticky předefinuje.
- Absolutní odkaz – u absolutního adresování se adresa definuje bez možnosti jakékoliv pozdější automatické změny. Absolutní adresování spočívá v přidání znaku $ před prvek, který potřebujeme absolutně adresovat ($C$3).
- Smíšený odkaz – adresuje absolutně jen jednu souřadnici. Má-li být absolutně adresován sloupec, přidá se $ před sloupec ($C3), pokud řádek, přidá se $ před číslo řádku (C$3).
Při změně relativních odkazů na absolutní a naopak vybereme buňku obsahující vzorec. Na řádku vzorců vybereme odkaz, který chceme změnit, a potom stiskneme klávesu F4. Opakovaným stisknutím klávesy F4 budeme přepínat mezi různými kombinacemi absolutních a relativních odkazů.
Kontrola výpočtů, zabezpečení a zacyklení
Z mnoha důvodů je rozumné si udělat kontrolu výpočtů, především po jejich kopírování nebo protažení. Máme tyto možnosti:
- klik do buňky s výpočtem: zápis výpočtu se zobrazí v řádku vzorců a tam je možné ho i upravit
- dvojklik do buňky s výpočtem: zápis výpočtu je v buňce, odkazované buňky jsou zobrazené barevně. Pozor
- pokud výpočet neopravuji, zápis potvrďte (Enter) nebo opusťte bez uložení úprav (Esc)
- pokud se výpočet týká součtu, průměru nebo počtu buněk, můžeme jednoduše využít Excel jako kalkulačku: vybrat buňky a zkontrolovat výsledek a stavový řádek
Další metody jsou trochu pokročilejší a lze je použít jednak pro kontrolu výpočtů, ale také třeba pro zjištění,z jakých buněk se vlastně počítá výsledek:
- Předchůdci: vyberte buňku s výsledkem a použijte Vzorce – Předchůdci (ve skupině Závislosti vzorců). Předchůdce označí Excel šipkami. Ty lze potom odebrat pomocí sousední volby Odebrat šipky.
- Následníci: opak k předchůdcům – vyberte buňky s hodnotou a použijte Vzorce – Následníci (ve skupině Závislosti vzorců). Excel zobrazí šipkami, kde se s touto buňkou počítá.
- Zobrazení vzorců: použijte Vzorce – Zobrazit vzorce (ve skupině Závislosti vzorců). Excel přepne zobrazení výsledků výpočtů a jejich zápis, současně vypne také číselné formáty. Je to třeba také jednoduchý způsob, jak zjistit, kde jsou výpočty a hodnoty (konstanty).
Zabezpečení výpočtů
Vhodným zabezpečením listu je jeho zamknutí (Revize – Zamknout list). Současně se zamknutím listu je také možné skrýt zobrazení zápisu výpočtu: Formát buňky – karta Zámek – Skrýt vzorce. Toto se projeví ale až po zamknutí listu.
Cyklický odkaz
Při zápisu výpočtu se může objevit chyba Cyklický odkaz. Znamená to, že se výpočet v buňce odkazuje na tuto samotnou buňku. Nejčastěji k tomu dojde chybným výběrem oblasti např. pro součet, kdy vybereme i další buňku. Výpočet je nutné opravit, popř. odstranit a vytvořit znovu.
Cvičení
C1 – Jednoduché vzorce
- Vytvořte tabulku podle vzoru:
C2 – Vzorce, tahák na mocniny
Použij vhodný vzorec/funkci pro výpočet hodnot a doplň údaje v tabulkách.
| mocnina / čísla | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| první | ||||||||||
| druhá | ||||||||||
| třetí | ||||||||||
| čtvrtá | ||||||||||
| pátá |
C3 – Vzorce, Analýza prodeje CD
| Ukazatel | Prodej | Cena | Tržby |
| Leden | 250 | 16 | 4000 |
| Únor | 280 | 16 | 4480 |
| Březen | 320 | 16 | 5120 |
| Duben | 450 | 16 | 7200 |
| Květen | 350 | 17 | 5950 |
| Červen | 390 | 17 | 6630 |
| Červenec | 430 | 18 | 7740 |
| Srpen | 400 | 18 | 7200 |
| Září | 270 | 18 | 4860 |
| Součet | |||
| Minimum | |||
| Maximum | |||
| Průměr |
C4 – Vzorce, Skok do dálky
Na adrese: K:\ProŽáky\Bečvářová Silvie\Podklady\EXCEL je dostupný excelový soubor 02-vzorce-skok-do-dalky.Stáhněte si jej a doplňte chybějící vzorce.
C5 – Vzorce, Zboží a dph
Na adrese: K:\ProŽáky\Bečvářová Silvie\Podklady\EXCEL je dostupný excelový soubor 02-vzorce-tabulka-zbozi.Stáhněte si jej a vypracujte chybějící vzorce.
C6 – Vzorce, Průměr známek
Na adrese: K:\ProŽáky\Bečvářová Silvie\Podklady\EXCEL je dostupný excelový soubor 02-vzorce-prumer-zaku. Stáhněte si jej a vypracujte chybějící vzorce.
C7 – Vzorce, Elektroměr
Na adrese: K:\ProŽáky\Bečvářová Silvie\Podklady\EXCEL je dostupný excelový soubor 02-vzorce-elektromer. Stáhněte si jej a vypracujte chybějící vzorce.
Další zdroje: