Lekce 9 - Vzorce a adresace v aplikaci Excel

V předešlém cvičení, Řešené úlohy k 8. lekci Excel pro začátečníky, jsme si procvičili nabyté zkušenosti z předchozích lekcí.

V této lekci se naučíme vytvářet vzorce a řekneme si více o relativní, absolutní a smíšené adrese ve vzorcích.

Vzorce

Excel, jakožto tabulkový procesor, slouží především k vytváření tabulek a k práci s daty. Mezi úkony, které můžeme s daty provádět, patří také výpočty. Výpočty můžeme provádět pomocí vzorců nebo pomocí funkcí. Hlavním rozdílem mezi vzorcem a funkcí je ten, že vzorec si vytváříme sami, zatímco funkce bývá zpravidla předem připravená, což si více přiblížíme v příští lekci.

Vytvoření vzorce

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 nebo adresu buňky
• Pokračujeme vepsáním operátoru, který budeme používat
• Opět napíšeme číslo nebo adresu buňky
• Vkládáme postupně čísla, adresy buněk 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

Adresu buňky do vzorce lze vložit kromě vepsání také kliknutím myši na danou buňku.

Příklad

Příklad využití základních vzorců si ukážeme v upravené tabulce, se kterou jsme již pracovali dříve a týká se skladových zásob potravin v obchodě.

Nejprve chceme zjistit, jaké máme aktuální skladové zásoby. Víme, kolik kusů jednotlivých druhů zboží jsme prodali. Tyto hodnoty si vložíme do nového sloupce tabulky s názvem Počet prodaných ks. Vytvoříme si také ještě jeden další sloupec tabulky, který nazveme Počet ks skladem 22. 8.. V tomto sloupci budeme provádět výpočty.

Víme, že potřebujeme odečíst hodnoty původních skladových zásob a počet prodaných kusů zboží, abychom zjistili aktuální stav skladu. Označíme si tedy buňku, ve které chceme mít výsledek, vepíšeme do ní znak = a dále pokračujeme tak, jak jsme popsali výše s použitím operátoru odečítání.

Po stisknutí klávesy Enter se nám vzorec vloží do všech buněk tabulky v daném sloupci. V případě, že by se vzorec do ostatních buněk automaticky nevložil, můžeme jej zkopírovat a vložit do buňky, kde jej chceme také. Případně si označíme buňku, ve které máme vzorec, a na pravém dolním rohu buňky se nám po najetí myší objeví černý křížek. Tažením myši za tento křížek vložíme vzorce i do dalších buněk.

Křížkem můžeme táhnout nejen směrem dolů, ale i nahoru, případně doleva nebo doprava podle toho, kde chceme vzorce mít.

Dále chceme zjistit, jaký máme zisk z prodeje jednotlivých druhů zboží za jeden den. Vytvoříme si tedy nový sloupec s názvem Zisk z prodeje. Víme, že potřebujeme vynásobit počet prodaných kusů jednotlivých druhů zboží a cenu za kus, abychom zjistili zisk z prodeje. Označíme si tedy buňku, ve které chceme mít výsledek a pokračujeme jako v předchozím případě - avšak tentokrát s použitím operátoru násobení.

Výsledná tabulka bude tedy vypadat takto:

Matematické operace ve vzorcích v aplikaci Excel se řídí stejnými pravidly jako při klasických výpočtech - tedy např. násobení má přednost před sčítáním a je proto potřeba při složitějších výpočtech používat závorky.

Adresace ve vzorcích

Jak jsme si již řekli, adresa buňky je dána souřadnicemi sloupce a řádku. Rozlišujeme adresy relativní, absolutní, smíšené a případně externí.

Relativní adresa

Relativní adresa je taková adresa, která se při kopírování vzorce do jiné buňky automaticky mění. Adresa se mění podle toho, kam vzorec vkládáme. O kolik sloupců či řádků se liší pozice zkopírovaného vzorce od pozice původního vzorce, o tolik sloupců či řádků se liší adresy obsažené ve zkopírovaném vzorci od adres v původním vzorci.

V příkladu, který jsme si ukázali výše, jsme využívali právě tento typ adresace. Např. vzorec v buňce F2 pro výpočet počtu kusů zboží skladem vypadal takto: =D2-E2. Poté, co jsme jej zkopírovali do buňky F3, se automaticky upravily adresy ve vzorci na: =D3-E3 apod.

Absolutní adresa

Absolutní adresa je 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ší tabulky jsme si do buňky L1 vložili aktuální kurz eura. Dále jsme si vytvořili nový sloupec tabulky s názvem Zisk EUR. Víme, že abychom zjistili zisk v eurech, musíme hodnotu v Kč vydělit hodnotou jednoho eura. Vzorec jsme tedy zapsali následovně: =H2/$L$1. 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í (H2, H3, H4 apod.).

Smíšená adresa

Smíšená adresa je taková adresa, v rámci které je použita absolutní adresa buď pouze pro sloupec $E7 nebo pro řádek E$7. Pokud vzorec se smíšenou adresou kopírujeme, mění se pouze relativní část adresy.

Externí adresa

Externí adresa je taková adresa, která odkazuje na buňku na jiném listu sešitu Excel. Tato adresa se nám může hodit v případě, kdy máme např. data pro výpočty na druhém listu sešitu a výpočty provádíme na prvním listu.

Zápis této adresy se provádí tak, že do buňky zapíšeme =, přejdeme na jiný list, kde máme data a klikneme myší na buňku s daty, která chceme použít ve vzorci. Pro potvrzení vzorce stiskneme klávesu Enter a získáme výsledek vzorce. Externí adresa buňky z druhého listu může tedy vypadat např. takto List2!B5.

Externí adresu můžeme využívat také pro výpočty s daty z jiného sešitu aplikace Excel.

V následujícím cvičení, Řešené úlohy k 9. lekci Excel pro začátečníky, si procvičíme nabyté zkušenosti z předchozích lekcí.