8 - Vzorce a funkce

8.

Vzorce a funkce v Excelu

Co již v Excelu umíme?

- pracovat se sešitem (vytvoření listu, pojmenování a barevné označení)

- pracovat s buňkou (označení, kopírování, vkládání, vytváření řady)

- vytvořit tabulku, naformátovat celou tabulku nebo její části

- vytvořit graf (výběr vhodného grafu podle obsahu tabulky)

 

Podle postupu viz níže se naučíme 

- vytvářet vlastní vzorce

- používat základní funkce v Excelu

 

ÚKOLY:

1. Otevři si v program MS Excel on-line sešit z minulé hodiny, vytvoř v něm další 2 listy. Jeden pojmenuj jako VZORCE, druhý pak FUNKCE.

 

2. Vzorce používáme většinou pro sčítání, odčítání, násobení a dělení dat v buňkách. Vzorec vždy začíná znaménkem =, po kterém následují operátory výpočtů +, -, *, / a číselné hodnoty.

Vzorec tedy vytvoříme následovně:

• označíme si buňku, ve které budeme vzorec vytvářet
• do buňky napíšeme znak rovná se =
• dále napíšeme číslo
• pokračujeme vepsáním operátoru, který budeme používat
• opět napíšeme číslo
• vkládáme postupně čísla a mezi nimi operátory tak dlouho, jak potřebujeme
• v případě, že jsme již se vzorcem spokojeni, stiskneme klávesu Enter a výsledek se objeví v buňce, do které jsme vzorec psali

             

 

3. Vypočítej pomocí Excelu následující příklady v rámci jedné buňky:

• =(12543-356+789)*2
• =(789+921-99)/3

 

4. Mnohem častěji se používá ve vzorcích odkaz na buňky, což znamená, že se počítá s hodnotami vloženými do buněk, na které se odkazuje. Zapsat odkaz na buňku je jednoduché – do vzorce napíšeme přímo její adresu. Chceme-li např. v buňce C1 sečíst hodnoty buněk A1 a B1, vzorec bude vypadat takto: =A1+B1

          

 

5. Při sestavování vzorce místo napsání odkazu je výhodnější kliknout myší na buňku, na kterou odkazujeme. Ukážeme si to na následujícím příkladu:

Chceme sečíst hodnoty z buněk B2 a B3 a pak vydělit hodnotou z buňky B4. Postupujeme takto:

• Vložíme data podle obrázku (B2 až B4).
• Hodnoty B2 a B3 chceme sečíst a pak vydělit hodnotou z buňky B4.
• Přejdeme na buňku B6 (do níž chceme vložit výsledné parametry) a napíšeme symbol vzorce = a přidáme levou závorku, takže =(.
• Klikneme na buňku B2. V buňce se objeví odkaz na tuto buňku.
• Napíšeme +.
• Klikneme na buňku B3.
• Uzavřeme závorku a napíšeme symbol pro dělení /.
• Klikneme na buňku B4 a stiskneme „Enter“.

 

6. Opakování - vytvoř tabulku, která má 7 sloupců a 7 řádků (první řádek vznikne sloučením buněk a zarovnáním na střed) - zápis proveď do jednotlivých buněk viz obrázek (tj. ve sloupci B bude Zboží, v řádku č. 4 Rohlík atd.). Proveď formátování podobné jako na obrázku. Šířku sloupců přizpůsob obsahu. Před začátkem práce tě ještě učitel seznamí s nastavením formátu hodnot (datum, měna).

 

7. Dopočítej sloupec F. Víme, že k 1. 1. 2022 bylo skladem 100 ks rohlíků, prodalo se 82 ks, k 3. 1. 2022 zůstalo skladem kolik rohlíků? Vytvoř vzorec. 

Každá tabulka obsahuje velké množství vzorců, proto je výhodné vzorce kopírovat. To provedeme vyplněním oblasti myší (zachycením za úchytný bod v pravé části buňky s vytvořeným vzorcem).

 

8. Dopočítej sloupec H. Víme, že cena za 1 ks rohlíku jsou 2 Kč. Zisk z prodeje musí vycházet z počtu prodaných kusů a ceny za kus. Vytvoř vzorec.

 

9. Absolutní adresa - je to taková adresa, která se při kopírování vzorce do jiné buňky nemění. Tento typ adresy využijeme v případech, kdy chceme, aby zůstala minimálně jedna hodnota ve vzorci stále stejná. Absolutní adresa se zapisuje pomocí znaku $, který vkládáme jak před písmeno buňky, tak také před číslo buňky. Výsledná adresa bude vypadat např. takto: $A$1, $E$7 apod.  

Jako praktický příklad si ukážeme využití absolutní adresy při přepočtu českých korun na eura. Do naší práce si do buňky K2 a L2 vložte aktuální kurz eura viz obrázek. Dále si vytvořte nový sloupec tabulky s názvem Zisk €. Abychom zjistili zisk v eurech, musíme hodnotu zisku v Kč vydělit hodnotou jednoho eura. Vzorec tedy zapíšeme následovně: =H4/$L$2. Tím, že jsme do adresy zapsali znaky pro absolutní hodnotu, se adresa pro tuto hodnotu nebude měnit a bude se měnit pouze adresa zisku z prodeje, která je zapsaná jako relativní (H4 až H8).

 

10. Funkce - 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 naší tabulkou. 

Pod tabulku si napište jako na obrázku 4 položky (Nejvíce prodávané, Nejméně prodávané...).

Klikněte do buňky vedle Nejvíce prodávané a potvrďte příkaz fx v řádku vzorců. Dojde k vyvolání nabídky funkcí. Vyberte MAX, funkce, která vyhledá maximální hodnotu. Po zobrazení okna viz níže je nutné označit myší oblast, ze které se má maximální hodnota najít. Pokračujte tlačítkem OK. Podobně postupujte u vyhledání nejméně prodávané položky (MIN), celkem prodáno (SUMA)...