Lekce 10 - Vzorce a adresace v aplikaci Excel

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

V tomto tutoriálu základů Excelu se naučíme vytvářet vzorce a řekneme si více o relativní, absolutní a smíšené adrese ve vzorcích.

Vzorce v Excelu

Excel, jakožto tabulkový procesor, slouží především k vytváření tabulek a 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 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á.

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 vytvoříme následovně:

• Označíme buňku, do které chceme vzorec vložit.
• Do buňky napíšeme znak rovná se =.
• Zadáme čí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.

Jednoduchý vzorec vypadá například takto:

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:

Praktické použití vzorců

Příklad využití základních vzorců si ukážeme na upravené tabulce, se kterou jsme pracovali již dříve a ve které evidujeme zásoby potravin v obchodě.

Výpočet stavu zásob

Nejprve vypočítáme stav skladových zásob k datu 22. 9.. Víme, kolik kusů jednotlivých druhů zboží bylo prodáno. Tyto hodnoty zapíšeme do nového sloupce tabulky s názvem Počet prodaných ks.

Dále do tabulky přidáme nový sloupec s názvem Počet ks skladem 22. 9., ve kterém budeme provádět výpočty.

Abychom zjistili stav skladu k tomuto datu, musíme odečíst počet prodaných kusů od původního počtu kusů na skladě. Označíme buňku, ve které má být výsledek, zapíšeme znak = a poté zadáme vzorec s použitím operátoru odečítání:

Adresu buňky lze do vzorce zadat buď ručně, nebo ji vložit kliknutím myší na příslušnou buňku.

Po stisknutí klávesy Enter se vzorec uloží do aktuální buňky. Chceme-li stejný vzorec použít i v dalších řádcích, můžeme jej zkopírovat a vložit do dalších buněk. Nebo označíme buňku se vzorcem a myší chytneme malý čtvereček v pravém dolním rohu označené buňky. Za držení levého tlačítka myši táhneme čtvereček dolů, čímž se postupně označí buňky, kam bude vzorec po uvolnění tlačítka myši automaticky zkopírován.

Tímto způsobem můžeme kopírovat vzorce nejen směrem dolů, ale i nahoru, případně doleva nebo doprava podle toho, kam je chceme vložit.

Výpočet zisku z prodeje

Nyní potřebujeme 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.

Abychom zjistili zisk z prodeje, potřebujeme vynásobit počet prodaných kusů jednotlivých druhů zboží a cenu za kus. 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 tomto příkladu počítáme záměrně složitějším vzorcem, abychom si ukázali použití závorek, které fungují stejně jako v matematice. Například násobení má tedy přednost před sčítáním a je proto potřeba při složitějších výpočtech použít závorky. V našem vzorci se pak nejprve vypočítá výraz v závorkách – rozdíl mezi stavem k 21. 9. a k 22. 9., čímž získáme počet prodaných kusů (tato hodnota je uvedena ve sloupci E). Teprve poté se výsledek násobí cenou za kus.

Výsledná tabulka po doplnění stylu bude vypadat takto:

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í 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říklad 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.

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 vypadá například takto: $A$1, $E$7 apod.

Na klávesnici znak dolaru $ napíšeme pomocí pravého Alt a písmene ů:

Excel nabízí klávesovou zkratku F4, která při úpravě vzorce pomáhá rychle měnit typ adresy buňky. Podrobněji se jí budeme věnovat v kurzu Microsoft Excel pro pokročilé.

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.

Abychom zjistili zisk v eurech, musíme hodnotu v Kč vydělit hodnotou jednoho eura. Vzorec tedy zapíšeme následovně: =H2/$L$1. Tím, že adresy buňky L1 uvedeme jako absolutní, nebude se při kopírování vzorce měnit a změní se pouze adresa zisku z prodeje, která je zapsaná jako relativní (H2):

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 pak odkazuje na buňku na jiném listu sešitu Excelu. Tato adresa se nám může hodit v případě, kdy máme například data pro výpočty na druhém listu sešitu a výpočty provádíme na prvním listu.

Externí adresu vytvoříme tak, že do buňky zapíšeme =, přejdeme na jiný list, kde máme data a klikneme myší na buňku s daty, kterou 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 vypadá například 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 10. lekci Excel pro začátečníky, si procvičíme nabyté zkušenosti z předchozích lekcí.