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ý rozdiel medzi vzorcom a funkciou je v tom, ž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 napí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 Počet predaných ks.
Ďalej do tabuľky pridáme nový stĺpec s názvom Počet ks skladom 22. 9., v ktorom budeme vykonávať výpočty.
Aby sme zistili stav skladu k tomuto dátumu, musíme odpočí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 možno 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 uchopí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, do ktorých 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 Zisk z predaja.
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 – tentoraz však 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:
Adresovanie 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 adresovania. Napríklad vzorec v bunke F2 na výpočet
počtu kusov tovaru skladom vyzeral takto: =D2-E2.
Po tom, čo sme ho skopírovali do bunky F3, sa adresy vo vzorci
automaticky upravili na: =D3-E3.
Ak do hárka vložíme nový riadok alebo stĺpec, Excel
automaticky upraví adresy vo vzorcoch tak, aby stále odkazovali na pôvodné
bunky. Ak napríklad vzorec obsahuje oblasť A1:A5 a vložíme
nový riadok dovnútra tejto oblasti, rozsah sa rozšíri tak, aby zahŕňal aj
posunuté pôvodné bunky. Ak však vložíme nový riadok mimo tejto oblasti,
napríklad nad ňu, rozsah sa iba posunie a nerozšíri sa.
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 klávesu ô:
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 doláre. Do našej tabuľky sme si do bunky L1
vložili aktuálny kurz. Ďalej sme si vytvorili nový stĺpec tabuľky s
názvom Zisk USD.
Aby sme zistili zisk v dolároch, musíme hodnotu zisku v eurách
prepočítať pomocou kurzu eura voči doláru. Vzorec teda zapíšeme
nasledovne: =H2/$L$1. Tým, že adresu bunky L1
uvedieme ako absolútnu adresu ($L$1), nebude sa pri kopírovaní
vzorca do ďalších riadkov meniť. Meniť sa bude iba adresa bunky so ziskom z
predaja, ktorá je zapísaná ako relatívna adresa (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 odkazuje na bunku na inom hárku zošita Excelu. 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: Hárok2!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í.
