10. diel - Vzorce a adresácia v programe Excel

V predchádzajúcom cvičení, Riešené úlohy k 9. lekcii Excel pre začiatočníkov, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

V tomto tutoriáli základov Excelu sa naučíme vytvárať vzorce a povieme si viac o relatívnej, absolútnej a zmiešanej adrese vo vzorcoch.

Vzorce v Exceli

Excel ako tabuľkový procesor slúži predovšetkým na vytváranie tabuliek a prácu s dátami. Medzi úlohy, ktoré môžeme s dátami vykonávať, patria aj výpočty. Výpočty môžeme vykonávať pomocou vzorcov alebo funkcií.

Hlavným rozdielom medzi vzorcom a funkciou je to, že vzorec si vytvárame sami, zatiaľ čo funkcia býva spravidla vopred pripravená.

Vytvorenie vzorca

Vzorce používame väčšinou na sčítanie, odčítanie, násobenie a delenie dát v bunkách. Vzorec vždy začína znamienkom =, po ktorom nasledujú operátory výpočtov +, -, *, / a číselné hodnoty:

Vzorec vytvoríme nasledovne:

• Označíme bunku, do ktorej chceme vzorec vložiť.
• Do bunky napíšeme znak rovná sa =.
• Zadáme číslo alebo adresu bunky.
• Pokračujeme vpísaním operátora, ktorý budeme používať.
• Opäť napíšeme číslo alebo adresu bunky.
• Postupne vkladáme čísla, adresy buniek a medzi nimi operátory tak dlho, ako potrebujeme.

Jednoduchý vzorec vyzerá napríklad takto:

V prípade, že sme už so vzorcom spokojní, stlačíme kláves Enter a výsledok sa zobrazí v bunke, do ktorej sme vzorec písali:

Praktické použitie vzorcov

Príklad využitia základných vzorcov si ukážeme na upravenej tabuľke, s ktorou sme pracovali už skôr a v ktorej evidujeme zásoby potravín v obchode.

Výpočet stavu zásob

Najprv vypočítame stav skladových zásob k dátumu 22/9. Vieme, koľko kusov jednotlivých druhov tovaru bolo predaných. Tieto hodnoty zapíšeme do nového stĺpca tabuľky s názvom Quantity sold.

Ďalej do tabuľky pridáme nový stĺpec s názvom Stock – 22 Sep, v ktorom budeme vykonávať výpočty.

Aby sme zistili stav skladu k tomuto dátumu, musíme odčítať počet predaných kusov od pôvodného počtu kusov na sklade. Označíme bunku, v ktorej má byť výsledok, zapíšeme znak = a potom zadáme vzorec s použitím operátora odčítania:

Adresu bunky je možné do vzorca zadať buď ručne, alebo ju vložiť kliknutím myšou na príslušnú bunku.

Po stlačení klávesu Enter sa vzorec uloží do aktuálnej bunky. Ak chceme rovnaký vzorec použiť aj v ďalších riadkoch, môžeme ho skopírovať a vložiť do ďalších buniek. Alebo označíme bunku so vzorcom a myšou chytíme malý štvorček v pravom dolnom rohu označenej bunky. Pri držaní ľavého tlačidla myši ťaháme štvorček nadol, čím sa postupne označia bunky, kam sa vzorec po uvoľnení tlačidla myši automaticky skopíruje.

Týmto spôsobom môžeme kopírovať vzorce nielen smerom nadol, ale aj nahor, prípadne doľava alebo doprava podľa toho, kam ich chceme vložiť.

Výpočet zisku z predaja

Teraz potrebujeme zistiť, aký máme zisk z predaja jednotlivých druhov tovaru za jeden deň. Vytvoríme si teda nový stĺpec s názvom Profit.

Aby sme zistili zisk z predaja, potrebujeme vynásobiť počet predaných kusov jednotlivých druhov tovaru a cenu za kus. Označíme si teda bunku, v ktorej chceme mať výsledok, a pokračujeme ako v predchádzajúcom prípade – avšak tentoraz s použitím operátora násobenia:

V tomto príklade počítame zámerne zložitejším vzorcom, aby sme si ukázali použitie zátvoriek, ktoré fungujú rovnako ako v matematike. Napríklad násobenie má teda prednosť pred sčítaním, a preto je pri zložitejších výpočtoch potrebné použiť zátvorky. V našom vzorci sa potom najprv vypočíta výraz v zátvorkách – rozdiel medzi stavom k 21/9 a k 22/9, čím získame počet predaných kusov (táto hodnota je uvedená v stĺpci E). Až potom sa výsledok násobí cenou za kus.

Výsledná tabuľka po doplnení štýlu bude vyzerať takto:

Adresácia vo vzorcoch

Ako sme si už povedali, adresa bunky je daná súradnicami stĺpca a riadka. Rozlišujeme adresy relatívne, absolútne, zmiešané a prípadne externé.

Relatívna adresa

Relatívna adresa je taká adresa, ktorá sa pri kopírovaní vzorca do inej bunky automaticky mení podľa toho, kam vzorec vkladáme. O koľko stĺpcov či riadkov sa líši pozícia skopírovaného vzorca od pozície pôvodného vzorca, o toľko stĺpcov či riadkov sa líšia adresy obsiahnuté v skopírovanom vzorci od adries v pôvodnom vzorci.

V príklade, ktorý sme si ukázali vyššie, sme využívali práve tento typ adresácie. Napríklad vzorec v bunke F2 na výpočet počtu kusov tovaru skladom vyzeral takto: =D2-E2. Potom, čo sme ho skopírovali do bunky F3, sa automaticky upravili adresy vo vzorci na: =D3-E3.

Absolútna adresa

Absolútna adresa je taká adresa, ktorá sa pri kopírovaní vzorca do inej bunky nemení. Tento typ adresy využijeme v prípadoch, keď chceme, aby zostala minimálne jedna hodnota vo vzorci stále rovnaká.

Absolútna adresa sa zapisuje pomocou znaku $, ktorý vkladáme pred písmeno bunky aj pred číslo bunky. Výsledná adresa vyzerá napríklad takto: $A$1, $E$7 a pod.

Na klávesnici znak dolára $ napíšeme pomocou pravého Alt a písmena ô:

Excel ponúka klávesovú skratku F4, ktorá pri úprave vzorca pomáha rýchlo meniť typ adresy bunky. Podrobnejšie sa jej budeme venovať v kurze Microsoft Excel pre pokročilých.

Ako praktický príklad si ukážeme využitie absolútnej adresy pri prepočte eur na české koruny. Do našej tabuľky sme si do bunky L1 vložili aktuálny kurz eura v českých korunách (Kč/€). Ďalej sme si vytvorili nový stĺpec tabuľky s názvom Profit CZK.

Aby sme zistili zisk v českých korunách, musíme hodnotu v eurách vynásobiť kurzom eura (Kč/€). Vzorec teda zapíšeme nasledovne: =H2*$L$1. Tým, že adresy bunky L1 uvedieme ako absolútne, nebude sa pri kopírovaní vzorca meniť a zmení sa iba adresa zisku z predaja, ktorá je zapísaná ako relatívna (H2):

Zmiešaná adresa

Zmiešaná adresa je taká adresa, v rámci ktorej je použitá absolútna adresa buď iba pre stĺpec ($E7), alebo pre riadok (E$7). Ak vzorec so zmiešanou adresou kopírujeme, mení sa iba relatívna časť adresy.

Externá adresa

Externá adresa potom odkazuje na bunku na inom hárku excelového zošita. Táto adresa sa nám môže hodiť v prípade, keď máme napríklad dáta na výpočty na druhom hárku zošita a výpočty vykonávame na prvom hárku.

Externú adresu vytvoríme tak, že do bunky zapíšeme =, prejdeme na iný hárok, kde máme dáta, a klikneme myšou na bunku s dátami, ktorú chceme použiť vo vzorci. Na potvrdenie vzorca stlačíme kláves Enter a získame výsledok vzorca. Externá adresa bunky z druhého hárka vyzerá napríklad takto Sheet2!B5.

Externú adresu môžeme využívať aj na výpočty s dátami z iného zošita aplikácie Excel.

V nasledujúcom cvičení, Riešené úlohy k 10. lekcii Excel pre začiatočníkov, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.