Funkce v Excelu
Pro výpočty v Excelu se používají vzorce a funkce. Funkce jsou předem připravené a jedná se vlastně také o vzorce, které používáme k výpočtům.
Funkce je vždy tvořena názvem a argumenty funkce. Např. pokud máme funkci SUMA, která nám sčítá hodnoty, její tvar může vypadat např. takto: =SUMA(G2:G6). V tomto případě je tedy názvem funkce "SUMA" a argumentem je oblast buněk G2:G6, v rámci které dojde k součtu všech hodnot zapsaných v buňkách.
Vložení funkce
Funkce můžeme vkládat několika různými způsoby.
Jeden z nich je následovný:
- Označíme si buňku, do které chceme funkci vložit
- V levé části vedle Řádku vzorců klikneme na tlačítko Vložit funkci
- Otevře se nám dialogové okno Vložit funkci, v rámci kterého si zvolíme funkci, kterou chceme použít
- Klikneme na zvolenou funkci a dále na tlačítko OK (případně dvakrát klikneme na zvolenou funkci)
- Otevře se nám dialogové okno Argumenty funkce, ve kterém zvolíme jednotlivé argumenty
- Po zvolení jednotlivých argumentů klikneme na tlačítko OK a funkce se nám vloží
V dialogovém okně Vložit funkci můžeme po kliknutí na jednotlivé funkce vidět v dolní části okna jejich popis, což nám může ulehčit rozhodnutí, kterou funkci použít.
Vkládat funkce můžeme také na kartě Vzorce ve skupině Knihovna funkcí pomocí tlačítka Vložit funkci nebo AutoSum.
Kategorie funkcí
Jak jsme si mohli všimnout při vkládání funkce v dialogovém okně Vložit funkci, můžeme vybírat z několika kategorií funkcí. Mezi tyto kategorie patří:
- Finanční - používáme pro výpočty, které se týkají obchodu a peněz - např. výpočet úroku nebo výpočty s cennými papíry
- Datum a čas - používáme pro výpočty, které se týkají práce s daty a časem - např. počet pracovních dní mezi 2 daty, vrácení aktuální hodnoty data a času nebo vrácení čísla týdne v roce
- Mat. a trig. - používáme pro výpočty, které se provádějí pomocí matematických a trigonometrických funkcí - např. součet, cosinus a sinus úhlu, faktoriál čísla nebo odmocnina
- Statistické - používáme pro statistické výpočty - např. pravděpodobnost výsledku pokusu nebo počet buněk v oblasti splňující požadované kritérium
- Vyhledávací - používáme pro vyhledávání hodnot - např. převod vodorovné oblasti buněk na svislou, vyhledání požadované hodnoty v matici nebo vrácení počtu řádků či sloupců
- Databázové - používáme pro souhrn dat splňující určité podmínky - např. vrácení maximální hodnoty záznamů databáze nebo vynásobení hodnoty záznamů databáze
- Textové - používáme pro práci s textovými řetězci - např. převedení čísla na text nebo vrácení počtu znaků textového řetězce
- Logické - používáme pro testování podmínek - např. ověření zda je podmínka splněna nebo vrácení logických hodnot PRAVDA nebo NEPRAVDA
- Informace - používáme pro ověření uložených typů dat - např. ověření zda je číslo sudé nebo zda hodnota odkazuje na prázdnou buňku
- Inženýrské
- Datová krychle
- Kompatibilita
- Webové
Excel nabízí opravdu velké množství funkcí, proto je vhodné si je důkladněji projít a zjistit, které chceme při práci s Excelem nejčastěji používat.
Příklady:
Vzhledem k tomu, že funkcí je v aplikaci opravdu mnoho, ukážeme si v následujícím příkladu alespoň některé základní funkce tak, abychom lépe porozuměli mechanismu, jak fungují. Budeme pracovat s tabulkou, kterou máme připravenou z minulých lekcí.
Nejprve chceme zjistit celkový zisk z veškerého prodaného zboží. Tento výpočet provedeme pomocí funkce SUMA následovně:
- Označíme si buňku G4, kterou máme ve sloupci s názvem Celkem
- V levé části vedle Řádku vzorců klikneme na tlačítko Vložit funkci
- Otevře se nám dialogové okno Vložit funkci, v rámci kterého si zvolíme kategorii funkcí Mat. a trig. a v této nabídce dvakrát klikneme na funkci SUMA
- Otevře se nám dialogové okno Argumenty funkce, ve kterém zkontrolujeme, zda je správně zadaná oblast buněk - v našem případě potřebujeme mít pro Číslo1 zadanou oblast buněk C4:F4
- Výběr funkce a oblasti buněk potvrdíme kliknutím na tlačítko OK
- V buňce G4 vidíme výsledek použité funkce, tj.
součet všech chlapců v jednotlivých ročnících
Kopírování funkcí funguje na stejném principu jako kopírování vzorců.
Podobně doplníme i součty žáků ve sloupcích.
Vyzkoušej i další funkce - průměr, minimum, maximum.
Žáci psali test, odpovídali na 10 otázek. V tabulce je správná odpověď označená /, špatná X, otázka bez odpovědi 0. Za správnou odpověď jsou 2 body, za špatnou se 1 bod odčítá, otázka bez odpovědi je hodnocena 0 body. Každý má bonus 10 bodů, to znamená, že nikdo nemůže získat záporný počet bodů, maximum je 30 bodů.
Hodnocení: do 90 % - 1, do 75 % - 2, do 50 % - 3, do 25 % - 4, méně než 25 % - 5.
Doplň všechny údaje do tabulky.
Ke každé otázce doplň počet otázek správných, špatných a bez odpovědi.
Řešení:
Doplníme funkce a vzorce na prvním řádku tabulky a potom vše zkopírujeme na ostatní řádky.
1. U každého žáka určíme počet správných a špatných odpovědí i počet nezodpovězených otázek - funkce COUNTIF (vrátí počet buněk v dané oblasti, které splňují dané kritérium).
Oblast - vybereme buňky na řádku u prvního jména, kritérium "/" (všechny znaky kromě čísel musí být psány v uvozovkách) v 1. sloupci, v 2. sloupci stejný výběr, kritérium "X", ve třetím sloupci stejný výběr, kritérium 0.
Výběr správné funkce umožňuje nabídka Vyhledat funkci - zde se zadá požadovaná vlastnost funkce, v našem případě počet a po kliknutí na tlačítko Přejít se zobrazí funkce odpovídající požadavku.
2. Pro výpočet bodů musíme vytvořit vzorec. Každý dostane 10 bodů jako bonus, za každou správnou odpověď se 2 body přičítají, za špatnou odpověď se 1 bod odečte. Nezodpovězená otázka bodový zisk neovlivní. Do buňky pro body zapíšeme vzorec: =10+2*L4-M4, kde v buňce L4 je počet správných odpovědí a v buňce M4 počet špatných odpovědí.
3. Pro výpočet procent je třeba zadat vzorec - počet bodů děleno celkový možný počet bodů krát 100: =O4/30*100, formát buňky upravíme na 1 desetinné místo.
4. Pro známku platí: jedničku dostane, když má 90 a více procent, dvojku do 75 procent, trojku do 50 procent, čtverku do 25 procent, při zisku méně než 25 % dostane 5.
Použijeme opakovaně funkci KDYŽ:
Podmínka - počet procent v buňce P4 je větší nebo roven 90 zapíšeme: P4>=90
Ano - pokud ano, dostane 1 - zapíšeme 1
Ne - potom může dostat další známky, proto použijeme znovu funkci KDYŽ v levém horním rohu a podobně pokračujeme dál.
Podmínka - počet procent v buňce P4 je větší nebo roven 75 zapíšeme: P4>=75
Ano - pokud ano, dostane 2 - zapíšeme 2
Ne - může dostat další známky, proto použijeme znovu funkci KDYŽ
Podmínka - počet procent v buňce P4 je větší nebo roven 50 zapíšeme: P4>=50
Ano - pokud ano, dostane 3 - zapíšeme 3
Ne - může dostat ještě 4 nebo 5, proto použijeme znovu funkci KDYŽ
Podmínka - počet procent v buňce P4 je větší nebo roven 25 zapíšeme: P4>=25
Ano - pokud ano, dostane 4 - zapíšeme 4
Ne - může dostat už jen 5, zapíšeme 5.
5. Na určení pořadí použijeme funkci RANK.
Pro výběr správné funkce využijeme nabídky Vyhledat funkci - zde se zadáme název počet a po kliknutí na tlačítko Přejít se zobrazí funkce odpovídající požadavku.
O pořadí bude rozhodovat celkový počet bodů.
Za Číslo zvolíme buňku na prvním řádku ve sloupci Body. Odkazovat se budeme na celý sloupec Body - označíme celý sloupec. Protože budeme do dalších buněk funkci kopírovat, musíme ohraničení oblasti označit jako absolutní adresu (pro všechny řádky jsou hranice oblasti stejné), doplníme do adresy buněk znak $ - použijeme klávesovou zkratku Fn+F4.
Pokud budeme řadit od nejvyššího počtu bodů po nejnižší (sestupně), do Pořadí nepíšeme nic nebo 0. Pokud bychom chtěli řadit vzestupně (od nejnižší hodnoty po největší), do Pořadí se zapíše číslo 1 nebo jiné (ne 0). Pokud by měli někteří stejný počet bodů, zapíše se pořadí oběma stejné, a dále pak pokračuje číslo o počet stejných pozic větší, např. 1, 2, 2, 4, ...
6. Pro určení, zda žák prospěl (P) či neprospěl (N), využijeme funkci KDYŽ.
Žák neprospěl, když dostal za 5, v ostatních případech prospěl.
Podmínku zapíšeme: Q4=5 (odkaz na buňku se známkou). Pokud je podmínka splněna, do řádku Ano se zapíše "N" (jde o text, nezapomeňte uvozovky), do řádku Ne "P".
7. Všechny vzorce a funkce zkopírujeme do ostatních řádků - označíme všechny doplněné údaje, přejdeme na pravý dolní okraj poslední buňky a po objeveníkřížku přetáhneme přes všechny nevyplněné buňky.
Průměr, minimum, maximum a počet odpovědí doplníme zadáním funkcí popsaných výše.
Vyplněná tabulka vypadá takto:
Úlohy na procvičení:
Úkol
Otevři si tabulku Excelu z minulého úkolu k návštěvnosti kinosálů v obchodním centru a tabulku doplň těmito údaji:
- celkový počet návštěvníků v jednotlivých dnech
- celkový počet návštěvníků za týden ve všech kinosálech
- celkový počet návštěvníků za týden v každém kinosále
- průměrný počet návštěvníků za den v každém kinosále i ve všech dohromady
- nejmenší a největší počet návštěvníků pro jednotlivé kinosály i pro všechny kinosály dohromady za celý týden
- pro jednotlivé kinosály zapiš pořadí podle návštěvnosti v jednotlivých dnech
- pro všechny kinosály zapiš pořadí podle návštěvnosti v jednotlivých dnech
- pořadí jednotlivých kinosálů podle průměrné návštěvnosti
- pořadí kinosálů podle částky vybrané na vstupném za celý týden
