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

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

Vítam vás u ďalšej lekcie e-learning kurzu programu Microsoft Excel, ktorý je určený pre začiatočníkov. V minulej lekcii, Riešené úlohy k 8. lekcii Excel pre začiatočníkov , sme si ukázali prácu so SmartArt, WordArt, textovým poľom a povedali sme si viac o rovniciach a symboloch. V tejto lekcii sa naučíme vytvárať vzorce a povieme si viac o relatívnej, absolútnej a zmiešané adrese vo vzorkách.

Vzorca

Excel, ako tabuľkový procesor, slúži predovšetkým na vytváranie tabuliek ak práci s dátami. Medzi úkony, ktoré môžeme s dátami vykonávať, patrí tiež výpočty. Výpočty môžeme vykonávať pomocou vzorcov alebo pomocou funkcií. Hlavným rozdielom medzi vzorcom a funkciou je ten, že vzorec si vytvárame sami, zatiaľ čo funkcia býva spravidla vopred pripravená, čo si viac priblížime v nasledujúcej lekcii.

Vytvorenie vzorca

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

Vzorec teda vytvoríme nasledovne:

• Označíme si bunku, v ktorej budeme vzorec vytvárať
• Do bunky napíšeme znak rovná sa =

• Ďalej napíšeme číslo alebo adresu bunky
• Pokračujeme vpísaním operátora, ktorý budeme používať
• Opäť napíšeme číslo alebo adresu bunky
• Vkladáme postupne čísla, adresy buniek a medzi nimi operátormi tak dlho, ako potrebujeme

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

Adresu bunky do vzorca je možné vložiť okrem vpísanie tiež kliknutím myši na danú bunku.

Príklad

Príklad využitia základných vzorcov si ukážeme v upravenej tabuľke, s ktorou sme už pracovali skôr a týka sa skladových zásob potravín v obchode.

Najprv chceme zistiť, aké máme aktuálne skladové zásoby. Vieme, koľko kusov jednotlivých druhov tovaru sme predali. Tieto hodnoty si vložíme do nového stĺpca tabuľky s názvom Počet predaných ks. Vytvoríme si tiež ešte jeden ďalší stĺpec tabuľky, ktorý nazveme Počet ks skladom 22. 8.. V tomto stĺpci budeme vykonávať výpočty.

Vieme, že potrebujeme odpočítať hodnoty pôvodných skladových zásob a počet predaných kusov tovaru, aby sme zistili aktuálny stav skladu. Označíme si teda bunku, v ktorej chceme mať výsledok, vpíšeme do nej znak = a ďalej pokračujeme tak, ako sme popísali vyššie s použitím operátora odčítanie.

Po stlačení klávesu Enter sa nám vzorec vloží do všetkých buniek tabuľky v danom stĺpci. V prípade, že by sa vzorec do ostatných buniek automaticky nevložil, môžeme ho skopírovať a vložiť do bunky, kde ho chceme tiež. Prípadne si označíme bunku, v ktorej máme vzorec, a na pravom dolnom rohu bunky sa nám po nabehnutí myšou objavia čierny krížik. Ťahaním myši za tento krížik vložíme vzorca aj do ďalších buniek.

Krížikom môžeme ťahať nielen smerom dole, ale aj hore, prípadne doľava alebo doprava podľa toho, kde chceme vzorca mať.

Ďalej chceme 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. Vieme, že potrebujeme vynásobiť počet predaných kusov jednotlivých druhov tovaru a cenu za kus, aby sme zistili zisk z predaja. 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ýsledná tabuľka bude teda vyzerať takto:

Matematické operácie vo vzorkách v programe Excel sa riadi rovnakými pravidlami ako pri klasických výpočtoch - teda napr. Násobenie má prednosť pred sčítaním a je preto potrebné pri zložitejších výpočtoch používať zátvorky.

Adresácia vo vzorkách

Ako sme si už povedali, adresa bunky je daná súradnicami stĺpca a riadku. Rozlišujeme adresy relatívna, absolútna, 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í. Adresa sa mení podľa toho, kam vzorec vkladáme. O koľko stĺpcov či riadkov sa líšia pozície skopírovaného vzorce od pozície pôvodného vzorca, o toľko stĺpcov či riadkov sa líšia adresy obsiahnuté vo 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ácia. Napr. vzorec v bunke F2 pre výpočet počtu kusov tovaru na sklade vyzeral takto: =D2-E2. Potom, čo sme ho skopírovali do bunky F3, sa automaticky upravili adresy vo vzorci na: =D3-E3 pod.

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 ako pred písmeno bunky, tak aj pred číslo bunky. Výsledná adresa bude vyzerať napr. Takto: $A$1, $E$7 pod.

Ako praktický príklad si ukážeme využitie absolútne adresy pri prepočte slovenských korún na eurá. Do našej tabuľky sme si do bunky L1 vložili aktuálny kurz eura. Ďalej sme si vytvorili nový stĺpec tabuľky s názvom Zisk EUR. Vieme, že aby sme zistili zisk v eurách, musíme hodnotu v Sk vydeliť hodnotou jedného eura. Vzorec sme teda zapísali nasledovne: =H2/$L$1. Tým, že sme do adresy zapísali znaky pre absolútnu hodnotu, sa adresa pre túto hodnotu nebude meniť a bude sa meniť len adresa zisku z predaja, ktorá je zapísaná ako relatívna (H2, H3, H4 a pod.).

Zmiešaná adresa

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

Externý adresa

Externá adresa je taká adresa, ktorá odkazuje na bunku na inom liste zošita Excel. Táto adresa sa nám môže hodiť v prípade, kedy máme napr. Dáta pre výpočty na druhom liste zošita a výpočty robíme na prvom liste.

Zápis tejto adresy sa vykonáva tak, že do bunky zapíšeme =, prejdeme na iný list, kde máme dáta a klikneme myšou na bunku s dátami, ktoré chceme použiť vo vzorci. Pre potvrdenie vzorca stlačíme kláves Enter a získame výsledok vzorca. Externé adresa bunky z druhého listu môže teda vyzerať napr. Takto List2!B5.

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

To je pre dnešok všetko. V tejto lekcii sme si ukázali tvorbu vzorcov a povedali sme si viac o relatívnych, absolútnych a zmiešaných adresách. V budúcej lekcii, Riešené úlohy k 9. lekcii Excel pre začiatočníkov , sa budeme venovať funkciám.

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