MS Excel – vytváření vzorců, použití jednoduchých funkcí Obsah kapitoly
vytváření jednoduchých vzorců s použitím adres buněk kopírování vzorců s využitím relativních, absolutních a smíšených adres maticové vzorce použití názvů buněk a oblastí buněk při tvorbě vzorců aplikace jednoduchých funkcí a jejich kombinování se vzorci
Studijní cíle
Naučit se efektivnímu způsobu vytváření vzorců v aplikaci MS Excel a jejich kopírování do dalších buněk tabulky. Umět používat jednoduché funkce a kombinovat je ve výpočtech se vzorci.
Doba potřebná ke studiu
cca 2,5 hod. vč. praktických cvičení
Pojmy k zapamatování
vzorec konstanta adresa buňky (relativní, absolutní, smíšená) název buňky resp. oblasti buněk maticový vzorec funkce
Úvod
Kromě zadávání konstatních hodnot do buněk aplikace Excel v textových nebo číselných formátech, jenž jsme se naučili v předchozí části, je často nutno v některých buňkách nadefinovat výpočty s odkazy na jiné buňky. K tomu se v aplikaci Excel používají vzorce a funkce.
Výkladová část
Vytvoření jednoduchého vzorce
Jednoduché vzorce
Vzorec můžeme zapisovat buďto přímo do buňky (viz modrý ovál na obr.) nebo do tzv. řádku vzorců (červený ovál). Řádek vzorců nemusí být vždy zobrazen, jeho zobrazení nebo skrytí lze nastavit pomocí karty Zobrazení – zatržítko Řádek vzorců (viz obr. dole)
1
Zápis vzorce
Každý zápis vzorce musí začínat znakem = (alternativně lze použít i znaky + resp. -, potom ale Excel do zápisu vzorce stejně doplní znak =). Dále lze v zápisu vzorců použít: konstanty - např. =2+3 odkazy na buňky - např. =C2-C3 názvy buněk - např. =Tržby-Náklady operátory - např. + - / ^ & funkce - např. =SIN(A1)
Čísla nebo adresy buněk?
Pokud váháte, zda do vzorce raději použít konstanty (tedy přímo číselné údaje) nebo odkazy na buňky, doporučuji dát přednost odkazům. Tabulky jsou pak variabilnější – při změně zadání stačí přepsat hodnotu ve vstupní buňce a všechny buňky, které se na ní odkazují se přepočtou. Adresu buňky lze do vzorce buď zapsat nebo vložit tak, že na buňku kliknete myší (v režimu tvorby vzorců) – druhý způsob je vhodnější, protože méně často vede k chybě, klikáte přímo na číslo, s nímž se bude ve vzorci pracovat a nemusíte přepisovat adresu buňky.
Typy vzorců
Vzorce lze rozdělit do tří typů podle typu operací a dat, s nimiž se pracuje: aritmetické (pracují s čísly a vracejí číselnou hodnotu) textové (pracují s texty a výsledkem je rovněž text) logické (porovnání výrazů mezi sebou, výsledkem je logická proměnná, tj. pravda nebo nepravda) Tyto typy vzorců lze i vzájemně kombinovat.
Aritmetické vzorce
Aritmetické vzorce Používají se tyto operátory: Znak
Operace
+
Sečítání
–
Odečítání
*
Násobení
/
Dělení
^
Mocnina (na anglické klávesnici nad 6)
Při vyhodnocování výrazu program dodržuje priority operátorů podle matematických pravidel, tzn. nejdříve vypočítá mocniny, poté provede násobení a dělení, nakonec pak sčítání a odčítání. Prioritu danou těmito pravidly můžeme změnit pomocí kulatých závorek (pokud si nejste jisti správností postupu výpočtu, raději použijte „zbytečné“ závorky). Ukázka aritmetického vzorce:
2
Textové vzorce
Textové vzorce Jediný operátor, který se používá ve vzorcích při práci s textem, je znak &. Používá se pro spojování, tzv. zřetězování textů. Na české klávesnici ho napíšeme kombinací kláves Alt Gr+c (Alt vpravo od mezerníku), můžeme použít také kombinaci levý Alt+38 na numerické klávesnici. Příklad textového vzorce: ="Celé jméno je "&A1&" "&B1 Je-li v buňce A1 zadáno Jan a v buňce B1 Novák, výsledkem této operace bude text: Celé jméno je Jan Novák
Poznámka
Logické vzorce
Pokud se ve vzorci sejdou aritmetické a textové operátory, provede program nejdříve všechny aritmetické operace, teprve potom zřetězení.
Logické vzorce Někdy potřebujeme srovnávat hodnoty dvou buněk mezi sebou, nebo obsah buňky s nějakou konstantou. V tomto případě budeme pomocí relačních operátorů vytvářet tzv. logické vzorce. Při vytváření takovýchto výrazů používáme stejné operátory, jako v matematice. Relační operátor
Příklad
= (rovná se)
=A1=B6
> (větší než)
=A1>B6
< (menší než)
=A1
>= (větší než nebo rovno)
=A1>=B6
<= (menší než nebo rovno)
=A1<=B6
<> (nerovná se)
=A1<>B6
Na české klávesnici se dají symboly nerovnosti napsat pomocí klávesy , (čárka) event. . (tečka) za současného držení klávesy Alt umístěné vpravo od mezerníku (někdy označena jako Alt Gr). Můžeme také podržet zmáčknutý levý Alt a numerické klávesnici zapsat 60 (<), resp. 62 (>).
3
Poznámka
Pokud se ve výrazech setkají s logickými operátory i operátory jiných typů vzorců, srovnání (logické operátory) se provádí až jako poslední.
Kopírování vzorců Kopírování vzorců
Při vytváření stejných typů vzorců v určité oblasti buněk (nejčastěji v souvislém sloupci nebo řádku, ale platí to i pro kopírování vzorců do vzdálenějších buněk) se používá vlastností různých adres buněk: použijeme-li ve vzorci nebo funkci relativní adresy buněk (A1), po zkopírování do jiné buňky se tyto adresy upraví podle toho, o kolik pozic vodorovně nebo svisle jsme vzorec resp. funkci zkopírovali
použijeme-li ve vzorci nebo funkci absolutní adresy buněk ($A$1), po zkopírování do jiné buňky se tyto adresy nezmění. Absolutní adresy se tedy používají pro odkaz na stále stejnou buňku. Tento odkaz by bylo možno nahradit ve vzorci přímo konstantou, z důvodů flexibilnosti (snadné „přepočitatelnosti“) tabulky při změně vstupních dat se to však nedoporučuje
méně často se používají smíšené adresy buněk ($A1 nebo A$1). Tyto adresy se při kopírování jedním směrem (vodorovně nebo svisle) upravují a při kopírování druhým směrem se nemění – zafixovaný je ten index, u něhož je značka $. Tyto typy adres se nejčastěji používají u tzv. křížových tabulek
4
Kopírování buněk se vzorci se provádí buď tažením za buňkový úchyt – to se hodi při kopírování vzorce do sousedních buněk:
nebo pomocí schránky (příkazy Kopírovat a Vložit nebo klávesovými zkratkami) – tento způsob se používá zejména pro kopírování na delší vzdálenost nebo do jiného listu či sešitu. Bližší informace o kopírování a přesunu buněk naleznete v (1). Poznámka
Protože znak $ se nachází jen na anglické klávesnici, používá se pro změnu typu adresy buňky funkční klávesa F4, která cyklicky mění typ adresy buňky mezi 4 různými variantami (relativní, absolutní a 2 typy smíšených adres). Změnu typu adresy můžete provést přímo při zadání adresy buňky nebo při opravě vzorce buďto přímo v buňce nebo v řádku vzorců – viz též (1).
Namísto kopírování vzorce můžeme použít postupu, který se používá rovněž k vyplnění shodných hodnot do buněk, tj. označení celé oblasti buněk, zadání vzorce a potvrzení kombinací kláves CTRL+ENTER. Použijeme-li ve vzorcích relativní, absolutní nebo smíšené adresy, ty se při vyplnění oblasti buněk příslušným způsobem upraví (nebo neupraví) podle výše popsaných pravidel. Opravy a mazání vzorců V případě chybně zapsaného výpočtu můžeme vzorec opravit podobně jako jakýkoliv jiný obsah buňky. Po označení buňky s výpočtem můžeme klepnout myší do řádku vzorců a provést opravu tam. Můžeme také na buňku poklepat nebo zmáčknout F2 na klávesnici a opravu provést přímo v buňce. Po klepnutí do řádku vzorců, nebo otevření buňky s výpočtem poklepem (nebo klávesou F2), program zvýrazní jednotlivé adresy ve vzorci odlišnými barvami a stejné barvy použije k vytvoření rámečků kolem buněk, na které se ve výpočtu odkazujeme tak, aby nám usnadnil kontrolu správnosti odkazů. Pokud je ve vzorci např. chybný odkaz na buňku, můžeme ho myší
5
označit jako jakýkoliv jiný text a smazat, nebo rovnou klepnutím ukázat správnou buňku. Chceme-li smazat celý vzorec z buňky nebo více buněk, postupujeme stejně jako při mazání číselných nebo textových hodnot. Poznámka
Využítí názvů buněk a oblastí ve vzorcích
Pokud dojde k přesunutí buněk, na něž se vzorec nebo funkce odkazuje, adresy buněk ve vzorci nebo funkci se automaticky upraví. Problém nastane tehdy, pokud se buňka, na nichž vzorec odkazuje odstraní – ve vzorci se pak objeví chybová hláška #REF!
Využití názvů ve vzorcích V programu MS Excel máme možnost jednotlivé buňky nebo skupiny buněk pojmenovávat. Názvy se pak dají využít k rychlejším přechodům mezi tabulkami nebo pro zvýšení přehlednosti a lepší čitelnost výpočtů. Pokud se název buňky použije ve vzorci, funguje při kopírování vzorce jako absolutní adresa. Pojmenovat označenou oblast buněk můžeme pomocí některých příkazů na kartě Vzorce nebo v poli názvů. Pole názvů je umístěno vlevo od řádku vzorců, a pokud není aktivní buňka pojmenovaná, zobrazuje se v něm adresa aktivní buňky.
Název buňky nebo oblasti buněk vložíme tak, že po označení buňky resp. oblasti zapíšem jednoduše její název do pole vzorců a potvrdíme klávesou ENTER. Poznámka
Název může obsahovat pouze znaky A až Z, číslice 0 až 9, tečku a podtržítko (pozor na mezery, nejsou povoleny). Prvním znakem názvu musí být písmeno nebo podtržítko a názvy nesmí vypadat jako adresy buněk.
Chceme-li název použít ve vzorci nebo ve funkci, stačí jej do vzorce zapsat místo adresy buňky nebo kliknout na pojmenovanou buňku a Excel místo adresy automaticky použije její název. Při zapisování názvu do buňky lze využít funkce tzv. automatického doplňování názvů – viz obr. Začnete-li do buňky psát v režimu vytváření vzorců (tj. po vložení znaménka = resp. + nebo -) jakýkoli text, objeví se bublina s názvy funkcí nebo pojmenovaných buněk,
6
které tímto textem začínají – výběr lze pak potvrdit kliknutím na příslušnou položku.
Další možností je při potřebě vložit název do vzorce použít klávesu F3, která otevře dialogové okno Vložit název se seznamem všech nadefinovaných názvů, a kliknutím na příslušnou položku se název vloží do vzorce:
Výhodou názvů je mj. to, že nemusíte používat různé typy adres, pokud použijete ve vzorci název jedné buňky, Excel ji interpretuje jako pevnou (absolutní) adresu, pokud použijete např. název sloupcové oblasti buněk, Excel ji interpretuje jako smíšené adresy se zafixovaným indexem sloupce apod. – viz též část o maticových vzorcích. Poznámka
Názvy buněk resp. oblastí se používají rovněž pro rychlé nalezení určité (pojmenované) části tabulky. Stačí rozkliknout pole názvů, kde se všechny definované názvy zobrazí a výběrem příslušné položky Excel rychle přejde k dané pojmenované části tabulky.
Odstranění nebo úprava názvu
Pokud potřebujeme název odstranit nebo upravit (např. protože jsme
7
omylem pojmenovali jiné buňky), vybereme na kartě Vzorce tlačítko Správce názvů.
V dialogovém okně vybereme ze seznamu názvů ten odstraňovaný a tlačítkem Odstranit ho odstraníme ze seznamu. V tomto okně můžeme také označené buňky pojmenovávat (tlačítko Nový), event. vytvořené názvy upravovat (tlačítko Upravit).
Na kartě Vzorce je několik dalších voleb, které umožňují s názvy pracovat trochu pokročilejším způsobem. Rozšiřující text
Maticový vzorec, který vrací jeden výsledek
Maticové vzorce Maticový vzorec lze použít k získání jednoho výsledku pomocí několika výpočtů. Tento typ maticového vzorce zjednodušuje model listu nahrazením několika různých vzorců jedním maticovým vzorcem.
Např. vzorec uvedený v příkladě na tomto obrázku spočte celkovou
8
cenu obou druhů zboží pomocí jediného výpočtu. Maticový vzorec, vracející více výsledných hodnot
Maticový vzorec je uzavřen do složených závorek {}, ty ale nelze zadávat ručně, ale po vytvoření vzorce musíte potvrdit zadání kombinací kláves CTRL+SHIFT+ENTER. Druhým typem maticových vzorců jsou takové, které vrací více výsledných hodnot – příklad vidíte na následujícím obrázku:
Odkazy na „externí“ buňky
Odkazy na buňky v jiných listech nebo sešitech Ve výpočtech se často odkazujeme na hodnoty buněk jiných listů téhož sešitu, nebo jiných sešitů. K adrese buňky pak program přidává jméno listu, ev. sešitu a listu, na kterém je odkazovaná buňka. Přímý zápis odkazů je v tomto případě složitější, buňky na jiných listech nebo v jiných sešitech raději vybíráme myší. V případě odkazů do jiných sešitů musí být tedy cílový sešit otevřený. Na tomto obrázku vidíte příklad vzorce odkazujícího na jiné listy téhož sešitu.
V následujícím příkladě vidíte zápis vzorce s odkazy na listy v jiném sešitě.
Podrobněji se o vzorcích s odkazy na „externí“ buňky můžete dozvědět např. v (1).
9
Výkladová část Vestavěné funkce
Použití jednoduchých funkcí ve výpočtech Abychom nemuseli každý výpočet definovat pomocí vzorce, můžeme při výpočtech použít vestavěné funkce, které nám výpočet zjednoduší. Porovnejte např. výpočet součtu několika buněk na následujícím obrázku:
Porovnání výpočtu pomocí vzorce a funce
Na levé straně je výpočet proveden pomocí vzorce, vpravo vidíte tentýž výpočet s využitím funkce SUMA. Obecný zápis funkce
Zápis výpočtu pomocí funkce začíná stejně jako u vzorců znaménkem =, následuje název funkce určující druh výpočtu, a za ním, v kulatých závorkách, tzv. argumenty funkce. Mohou jimi být čísla, texty, logické hodnoty, odkazy na buňky nebo výpočty v podobě námi sestavených vzorců nebo dalších funkcí. Pokud má funkce více argumentů, jsou (podle nastavení systému) od sebe odděleny středníkem. Příklad vidíte na následujícím obrázku:
K vkládání funkcí můžeme využít několika postupů: tlačítko Automatické shrnutí na kartě Domů tlačítko fx na řádku vzorců kartu Vzorce Použítí tlačítka Automatické shrnutí ke vložení základních funkcí
Tlačítko Automatické shrnutí slouží ke vložení základních a nejčastěji používaných funkcí, jako jsou součet, průměr, počet, maximum a minimum.
10
Kliknutním přímo na tlačítko Automatické shrnutí vložíte funkci SUMA, ostatní funkce lze vložit po rozkliknutí tohoto tlačítka. Všechny funkce vložené pomocí tohoto tlačítka mají tu vlastnost, že se automaticky pokusí zjistit rozsah buněk, které zřejmě hodláte zahrnout jako argument funkce (oblast čísel v okolí buňky). Tuto oblast lze samozřejmě upravit buď vytýčením jiné oblasti nebo jednotlivých buněk myší (jsou-li buňky roztroušeny po tabulce, podržte klávesu CTLR), nebo je můžete přepsat v závorce za názvem funkce. Poznámka
Vložení funkce pomocí tlačítka fx
Tlačítko Automatické shrnutí lze také použít tak, že nejprve označíte oblast buněk (např. sloupec) a následně kliknete na vybranou funkci. Excel pak vloží tuto funkci pod vybraný sloupec nebo vpravo od vybraného řádku buněk.
Rychlý přístup ke všem funkcím programu MS Excel nabízí tlačítko Vložit funkci na začátku řádku vzorců. Označíme buňku, ve které má být výsledek, a klepneme na tlačítko. Otevře se dialogové okno Vložit funkci se seznamem všech dostupných funkcí. Funkce jsou podle druhu výpočtu a typu argumentů rozděleny do skupin – kategorií. Při prvním otevření tohoto okna od spuštění programu se budou nabízet funkce Naposledy použité – v tomto seznamu je 10 funkcí, který daný uživatel v Excelu v poslední použil. Při dalším vyvolání tohoto okna se nabízejí funkce naposledy použité kategorie. Pokud mezi funkcemi nabídnuté kategorie nenajdeme potřebnou funkci, vybereme v poli Vybrat kategorii jinou skupinu funkcí. Funkci můžeme vyhledat také pomocí pole Vyhledat funkci, kam stačí zapsat stručný popis (např. jedno nebo několik klíčových slov) a kliknout na tlačítko Přejít – Excel nám nabídne funkce, které danému popisu odpovídají.
Po výběru funkce ze zobrazeného si můžeme v dolní části okna přečíst stručný popis funkce a prohlédnout její přesný zápis (syntaxi). U složitějších funkcí (např. vyhledávací, finanční) si můžeme pomocí odkazu Nápověda k této funkci zobrazit podrobnější nápovědu. Bývá v ní uvedeno několik příkladů použití a podrobný popis argumentů. Výběr funkce potvrdíme tlačítkem OK. Po potvrzení výběru funkce se otevře dialogové okno, do kterého
11
postupně zadáváme tzv. argumenty funkce. Nápověda k jednotlivým argumentům se nám zobrazí ve spodní části dialogového okna po kliknutí do příslušného políčka.
Pole pro zadání argumentů funkce
Nápověda k zadání příslušného argumetu
Výsledek
Pokud jsou odkazy, výrazy a hodnoty zadané do polí jednotlivých argumentů v pořádku, ukazuje program vpravo od pole zadané hodnoty, výsledky výpočtů nebo hodnoty v odkazovaných buňkách, vlevo dole pak výsledek vypočítaný na základě zadaných argumentů. Pokud je zadaný argument nevhodný nebo jsou odkazy nesprávné, bude za řádkem prázdno nebo se tady zobrazí chybové hlášení. Vložení funkce dokončíme klepnutím na OK nebo klávesou ENTER. Výběr funkce pomocí karty Vzorce
Další možnost vložení funkcí nabízí karta Vzorce. V části karty Vzorce, nazvané Knihovna funkcí, nabízí program tlačítka jednotlivých kategorií funkcí. Stačí rozbalit jejich nabídku a funkci vybrat.
Další postup je shodný jako při použití tlačítka fx na řádku vzorců – viz výše. Poznámka
Automatické doplnění názvů funkcí
Součástí karty Vzorce je rovněž tlačítko fx (Vložit funkci) – viz výše.
Další možností vložení funkce je využití vlastnosti automatického
12
doplňování názvů, tj. zadat do buňky znaménko = a začít psát název funkce (nevýhodou je, že v tomto případě musíme dopředu název funkce alespoň přibližně znát). Excel nabídne seznam funkcí (a event. i definovaných názvů), které tímto textem začínají a my si z nich můžeme vybrat. Na obrázku níže vidíte např. postup při zadání funkce Průměr.
V tomto případě se však po volbě názvu funkce automaticky neobjeví dialogové okno pro zadání argumentů funkce. Chceme-li jej zobrazit, musíme následně kliknout na tlačítko fx. Oprava zápisu funkce
Oprava zápisu funkce Pokud se po dokončení vložení funkce ukáže, že jsme některý argument nezadali dobře, můžeme po výběru buňky s vloženou funkcí zápis funkce opravit jako jakýkoliv jiný text v řádku vzorců nebo po poklepu na buňku přímo v ní. Lepší možností, jak opravit chybně zadaný argument, je znovu vyvolat dialogové okno Argumenty funkce a opravu provést v něm. Vybereme buňku s vloženou funkcí a klepneme na tlačítko fx. Program zobrazí okno Argumenty funkce a v něm všechny doposud zadané argumenty, které zde můžeme opravit, vymazat, doplnit apod. V této kapitole se nebudeme věnovat jednotlivým typům funkcí, některé základní funkce použijete v modelovém cvičení a POTu, některé další budou probrány v předmětu Aplikační software 2.
Odkaz
Kompletní seznam funcí a nápovědu k nim naleznete zde.
Shrnutí
Pro výpočty v tabulkách aplikace Excel můžete definovat různé typy vzorců s využitím adres buněk nebo názvů. Některé výpočty není nutno vymýšlet, ale můžete použít celou řadu předdefinovaných funkcí. Pokud vzorce nebo funkce kopírujete do jiných buněk, musíte při jejich tvorbě používat správné typy adres (relativní, absolutní, smíšené). Některé problémy s kopírováním vzorců vám mohou rovněž usnadnit tzv. maticové vzorce.
Kontrolní otázky a úkoly
1. Jaký je rozdíl v použití relativních a absolutních adres buněk ve
13
vzorcích a funkcích? 2. Jaký je nejrychlejší postup, potřebujete-li sečíst několik sloupců tabulky? 3. Jak si zobrazíte okno pro vložení argumentů funkce, pokud se vám automaticky nezobrazí (resp. pokud potřebujete zápis funkce opravit)? 4. Projděte si modelové úlohy, které jsou vystaveny v interaktivní šabloně. Studijní literatura
Odkazy
1. Nováková A., Nulíček V.: Aplikační software 2. Eupress 2008 (skripta). ISBN: 978-80-7408-011-1 2. Bříza V.: Excel 2007 – podrobný průvodce. Grada 2007. ISBN: 978-80-247-1965-8 3. Brož M: MS Excel 2007 – podrobná uživatelská příručka. Computerpress 2007. ISBN: 978-80-251-1822-1 Další informace k tomuto tématu lze získat např. v online nápovědě na webu Office Online:
14
