Vzorce a funkce v MS Excel - lektornedoma.cz Funkce.pdf · Microsoft Excel Vzorce a funkce v MS...

Microsoft Excel

Vzorce a funkce v MS Excel Verze 2007, 2010, 2013, 2016

Autor: Jaroslav Nedoma

O KURZU

Vzorce a funkce v MS Excel

Vítáme Vás na IT semináři věnujícím se problematice funkcí a usnadnění práce v nejrozšířenějším

tabulkovém kalkulátoru na světě, v aplikaci Microsoft Excel. Jedná se o kurz, který by měl zefektivnit

Vaši každodenní práci s touto aplikací.

Pravidlo zní, stručně, jasně a výstižně seznámit čtenáře s každou kapitolou. V těchto skriptech začneme

s mírným opakováním základů a dostaneme se do oblasti funkcí, které tvoří základ práce v Excelu.

Je nutné poznamenat, že se nejedná o zcela vyčerpávající materiál, ale pouze o doprovodná skripta

ke školení. Předpokládá se tedy, že po absolvování školení budete schopni tato skripta využít na 100 %

bez sebemenší překážky spolu s upřesňujícími poznámkami pořízenými na semináři.

O AUTOROVI

Jaroslav Nedoma

IT lektor Microsoft Office a jednatel společnosti Educio s.r.o. Jaroslav Nedoma úspěšně proškolí každý

rok v průměru přes 1 000 osob v 700 hodinách výuky. Školením se zabývá od roku 2009, kdy začínal

jako lektor kancelářského balíčku MS Office 2003. Dnes školí zaměstnance firem výhradně pro práci

s verzemi MS Office 2013 a 2016. Jako lektor IT kurzů MS Office proškolil přes 100 velkých a malých

firem v rámci celé ČR.

Účastníci kurzů různých věkových skupin se pod vedením pana Nedomy naučili ovládat kancelářské

programy Word, Excel, Outlook, Access nebo PowerPoint a prakticky využívat všechny funkce, které

jim pomáhají řešit úkoly nejen v zaměstnání, ale i v domácnosti a volném čase. Zaměstnavatelé oceňují

zvýšení kvalifikace i efektivity jejich zaměstnanců.

Veškeré připomínky, dotazy, nápady k obsahu těchto skript směřujte přímo na autora přes následující

kontakty:

mob.: +420 724 782 336

e-mail: [email protected]

web: www.lektornedoma.cz

OBSAH

Úvod .........................................................................................................................................................6

Adresace ...................................................................................................................................................7

Relativní ...............................................................................................................................................7

Absolutní .............................................................................................................................................7

Smíšená ...............................................................................................................................................8

Definování názvů ................................................................................................................................... 11

Zmnožený vstup .................................................................................................................................... 14

Tabulka – definovaný seznam ............................................................................................................... 17

Tvorba a vlastnosti tabulky v MS Excel ............................................................................................ 17

Řádek souhrnů a práce s ním ........................................................................................................... 18

Problematika polí v tabulce .............................................................................................................. 19

Převedení dat tabulky na rozsah ...................................................................................................... 20

Maticové vzorce .................................................................................................................................... 21

Výpočet jednoho výsledku ............................................................................................................... 21

Výpočet více výsledků ...................................................................................................................... 22

Úprava maticového vzorce ............................................................................................................... 23

K-násobky oblasti ............................................................................................................................. 23

Součet dvou oblastí (vektorů) .......................................................................................................... 23

Součin dvou matic ............................................................................................................................ 24

Příklad využití maticových vzorců .................................................................................................... 25

Řešení křížových tabulek pomocí matic ........................................................................................... 26

Funkce ................................................................................................................................................... 28

Používání funkcí ................................................................................................................................ 28

Použití průvodce funkcí: ........................................................................................................... 28

Použití karty Vzorce: ................................................................................................................. 29

Přímý zápis funkce do buňky: ................................................................................................... 30

Jak se funkce definuje ............................................................................................................... 31

Syntaxe ..................................................................................................................................... 31

Vnořené funkce ................................................................................................................................ 32

Kategorie funkcí ............................................................................................................................... 32

Matematické funkce .............................................................................................................................. 33

SUMA ................................................................................................................................................ 33

ZAOKROUHLIT .................................................................................................................................. 34

SUBTOTAL ......................................................................................................................................... 35

Statistické funkce .................................................................................................................................. 38

POČET ............................................................................................................................................... 38

POČET2 ............................................................................................................................................. 38

PRŮMĚR ........................................................................................................................................... 39

COUNTIF ........................................................................................................................................... 41

COUNTIFS ......................................................................................................................................... 42

SUMIF ............................................................................................................................................... 43

SUMIFS ............................................................................................................................................. 44

AVERAGEIF ....................................................................................................................................... 44

AVERAGEIFS ...................................................................................................................................... 45

MINIFS .............................................................................................................................................. 45

MAXIFS ............................................................................................................................................. 46

COUNTBLANK ................................................................................................................................... 47

MEDIAN ............................................................................................................................................ 48

Vyhledávací funkce ................................................................................................................................ 49

VYHLEDAT ......................................................................................................................................... 49

SVYHLEDAT ....................................................................................................................................... 50

VVYHLEDAT ...................................................................................................................................... 51

POZVYHLEDAT .................................................................................................................................. 52

INDEX ................................................................................................................................................ 53

NEPŘÍMÝ.ODKAZ .............................................................................................................................. 57

Logické funkce ....................................................................................................................................... 59

KDYŽ ................................................................................................................................................. 59

A ....................................................................................................................................................... 62

NEBO ................................................................................................................................................ 63

IFERROR ............................................................................................................................................ 64

IFNA .................................................................................................................................................. 65

IFS ..................................................................................................................................................... 66

SWITCH ............................................................................................................................................. 67

Informační funkce ................................................................................................................................. 69

Funkce JE… ....................................................................................................................................... 69

ISEVEN .............................................................................................................................................. 70

ISODD ............................................................................................................................................... 70

O.PROSTŘEDÍ .................................................................................................................................... 70

SHEET ................................................................................................................................................ 71

SHETTS .............................................................................................................................................. 71

TYP .................................................................................................................................................... 71

CHYBA.TYP ........................................................................................................................................ 72

Funkce pro práci s datem a časem ........................................................................................................ 73

DNES ................................................................................................................................................. 73

NYNÍ .................................................................................................................................................. 73

DENTÝDNE ........................................................................................................................................ 74

DEN, MĚSÍC, ROK .............................................................................................................................. 75

DATUM ............................................................................................................................................. 76

SEKUNDA, MINUTA, HODINA ........................................................................................................... 77

ČAS ................................................................................................................................................... 78

WEEKNUM ........................................................................................................................................ 79

ISOWEEKNUM .................................................................................................................................. 79

EOMONTH ........................................................................................................................................ 80

NETWORKDAYS ................................................................................................................................ 81

WORKDAY ......................................................................................................................................... 82

Textové funkce ...................................................................................................................................... 83

CONCATENATE ................................................................................................................................. 83

ČÁST.................................................................................................................................................. 84

HLEDAT, NAJÍT .................................................................................................................................. 88

NAHRADIT......................................................................................................................................... 88

NUMBERVALUE ................................................................................................................................ 89

TEXTJOIN .......................................................................................................................................... 90

ZLEVA, ZPRAVA ................................................................................................................................. 91

Databázové funkce ................................................................................................................................ 92

Funkce začínající na písmeno D ........................................................................................................ 92

Chybová hlášení ..................................................................................................................................... 96

Nejčastější klávesové zkratky ................................................................................................................ 97

Speciální znaky při práci s MS Excel ...................................................................................................... 98

Závěr .................................................................................................................................................... 101

Vzorce a funkce v MS Excel 6

ÚVOD

Každý, kdo se chystá používat Excel v denní praxi nejen pro tvorbu tabulek, ale hlavně pro počítání,

vyhledávání a jejich analýzu, nevyhne se dříve nebo později funkcím, se kterými se teprve stává Excel

Excelem.

Většina uživatelů, která se pustí do poznávání funkcí, zůstane u klasických vzorců a funkcí a zapomíná

na další rozměr funkcí v Excelu. Oblasti jako dynamické (neboli strukturované) vzorce nebo maticové

vzorce bývají často oblastí, které se v Excelu přehlíží, nicméně tyto oblasti pomáhají v nejednom směru.

Vše si v rámci tohoto kurzu ukážeme na jednoduchých praktických příkladech, díky kterým by nemělo

dělat problém jejich pochopit význam takových vzorců a snadno je implementovat do reálných příklad

z praxe.

Důležité je v rámci tohoto kurzu porozumět správnému adresování ve vzorcích – ukážeme si všechny

tři typy (relativní, absolutní i smíšené).

Dále bude důležitou kapitolou Definování názvů, díky kterému pochopíte, že vzorce nemusí být jen

o souřadnicích.

Pro mnohé přibydou do současných znalostí dynamické a maticové vzorce, které tu mají také své

kapitoly.

Největší část tohoto materiálu tvoří funkce, které projdeme od těch nejjednodušších až po ty složitější,

jakými jsou vyhledávací, logické či databázové. Každá funkce zde popsána na příkladech.


ADRESACE

Excel využívá v buňkách v základním režimu relativní adresy buněk. Dále zde jsou ale další dvě

možnosti, těmi jsou adresace absolutní a smíšená.

Adresace se tedy rozděluje následovně:

• Relativní

• Absolutní

• Smíšená

RELATIVNÍ

Relativní adresace je běžný zápis vzorečku tak, jak jsme na něj zvyklí. Jako jeden příklad (Obr. 1)

za všechny může posloužit využití jednoduché aritmetiky. Naším úkolem je sečíst dvě sousedící buňky

A1 a B1. Výsledek budeme chtít zapsat do buňky C1 a zápis bude následující: =A1+B1. Na dalším řádku

budeme chtít obdobně posčítat buňky A2 a B2 a výsledek bude patřit do buňky C3. Nyní víme, že nám

stačí technika pravého dolního rohu, za který stačí zatáhnout a výsledek je na světě! Vidíme tedy, že se

automaticky změnila čísla řádků u buněk ve sloupci A i B.

Obr. 1 Relativní adresace

ABSOLUTNÍ

Absolutní adresace se využívá v tabulkách, jejichž hodnoty jsou například závislé na změně jediné

buňky. Jako jednoduchý příklad (Obr. 2) postačí pár čísel sepsaných pod sebou ve sloupečku A a další

jedno číslo napsané v buňce B1 (to bude naše zásadní buňka, na které všechno závisí). Chtěli bychom,

abychom viděli ve sloupečku C pod sebou tolik výsledků, kolik máme zadaných čísel ve sloupečku A.

Výsledky by měly zobrazovat vždy jednotlivé číslo ze sloupečku A vynásobené číslem v buňce B1 tak,

aby jediná změna čísla v buňce B1 vyvolala přepočet všech výsledků ve sloupečku C.

To není možné v případě, kdy bychom použili zápis ve stylu: =A1*B1. V tomto případě by se totiž

jednalo o klasický relativní zápis adresy a došlo by k tomu, že by byl správně pouze jeden (první)

výsledek, a to v buňce C1. Kdybychom začali s tažením za pravý dolní roh v buňce C1, kde je výsledek,

zjistíme, že další výsledky budou nesprávné. V reálu se děje to, že každý řádek má jinou souřadnici čísla

ve sloupečku A (např. A2), ale zároveň i jinou souřadnici čísla ve sloupečku B (např. B2). První posun

z A1 na A2 chceme, ale z B1 na B2 nikoliv. Proto existuje něco, čemu se říká absolutní adresování.

Představme si ho tak, že při psaní prvního vzorečku do buňky C1 chceme zapsat skutečnost, že adresa

buňky B1 má být při tažení za pravý dolní roh tzv. zafixovaná (neměnná). Toho docílíme tak,

že po zapsání vzorce: =A1*B1 klikneme v zápisu vzorce na tu adresu buňky (před písmeno sloupce

nebo číslo řádku nebo za číslo řádku - není rozdíl), která má být zafixovaná (př. B1) a stiskneme


na klávesnici funkční klávesu F4. Automaticky dojde ke změně relativní adresace na absolutní (objeví

se znak dolaru '$' před písmenem sloupce i číslem řádku) a vše je hotovo. Dále proběhne klasické

potvrzení vzorce enterem a můžeme tahat za pravý dolní roh směrem dolů. Výsledek je na světě! Nyní

dochází k tomu, že při tažení zůstává adresa buňky B1 neměnná ve všech dalších vzorečkách. Docílili

jsme toho, že stačí změnit údaj v buňce B1 a změní se všechny přepočty ve sloupečku C.

Obr. 2 Absolutní adresace

SMÍŠENÁ

Smíšená adresace spočívá v tom, že znak dolaru neumístíme zároveň před písmeno sloupce a číslo

řádku, ale jen před jeden z těchto parametrů. Znak dolaru můžeme napsat buď ručně (CTRL+ALT+ů)

nebo budeme funkční klávesu F4 mačkat tak dlouho, dokud se nezobrazí naše požadovaná adresace.

Využití najdeme v rozsáhlejších tabulkách, kdy chceme zamezit tomu, aby se například při zkopírování

vzorečku do strany zamezilo přepsání písmene sloupečku, ale už nechceme, aby se vzoreček zároveň

zablokoval ve smyslu řádků. Jinými slovy chceme, aby nebyl ve svislém směru zablokovaný a v tom

vodorovném ano.

Taková buňka by po zápisu mohla ve vzorečku vystupovat například takto: $A1 (fixovaný pouze

sloupeček – ve smyslu kopírování buňky směrem doprava bude ve vzorci pořád souřadnice sloupečku

A, ale při kopírování směrem dolů nebude fixovaný řádek 1, ale bude se měnit). Proto adresace

smíšená.

Jako jeden příklad za všechny poslouží platová tabulka na Obr. 3. Je zde pouze kousek takové tabulky,

ale přesto bude pochopitelné, co který vzoreček znamená a proč se používá právě smíšená adresace

buněk. Vždy se jedná o zjednodušení práce a tady to platí opět.

Tabulka představuje týdenní platy zaměstnanců podle následujících tří kritérií:

1. Každý týden má jiný počet pracovních dní.

2. Každý zaměstnanec má jinou hodinovou mzdu.

3. Všichni mají fixně stanovenou pracovní dobu 8 hodin denně.


Obr. 3 Smíšená adresace

Přesně toto je hezký příklad použití smíšené adresace. Začneme v buňce E4, kde bychom v samém

závěru měli také skončit, protože jediný vzoreček zapsaný sem by měl vyřešit všechny položky platů

každého ve všech týdnech. A tady se právě ukáže technika smíšené adresace.

Začneme jednoduchou úvahou. Vzorec v buňce E4 bude vycházet z buňky C4, kde je hodinová mzda

paní Bártové, z buňky D4, která říká, kolik pracovních dní má první týden a nakonec z konstanty 8

označující povinnost odpracování zadaného počtu hodin denně. Celá toto úvaha bude obsažena

v následujícím vzorci: =C4*D4*8. Tento vzorec sice po Enteru vrátí správný výsledek, ale pouze za první

týden pro paní Bártovou. Kdybyste nyní zkusili vzorec zkopírovat za pravý dolní roh, zjistili byste,

že každá další buňka prvního týdne obsahuje nulu.

Je potřeba náš první vzoreček v buňce E4 nějak upravit. Když se podíváte na buňku D4 označující počet

pracovních dní, jedná se o buňku sloučenou a tudíž adresa celé této velké obdélníkové buňky je pořád

D4, nikoliv níž D5, D6, … Tuto skutečnost uvedeme ve vzorci zafixováním této pozice naší známou

klávesou F4. Vzorec bude nyní následující: =C4*$D$4*8. Pokud nyní stáhnete za pravý dolní roh dolů,

zjistíte, že už vidíte namísto nul platy všech ostatních.

Problém ale nastává, pokud budete chtít celý tento první týden platů zkopírovat do druhého týdne.

Říkali jsme, že si ve finále přejeme, aby tomu takhle jednoduše bylo a vzoreček přesto fungoval. Nyní

se ale pořád našemu efektu ještě moc neblížíme. Protože jsme zkopírovali náš sloupeček prvního týdne

a celé 2 sloupečky doprava, všechny adresy buněk (konkrétně písmena sloupců) v našem vzorečku se

také změnily, a to o celá 2 pořadí dál v abecedě. Z našich hodinových platů se na místo buňky C4 Excel

odkazuje do buňky E4 (o dvě posunuté pozice), což nechceme. S pozicí $D$4 se nic neděje, protože je

absolutně adresovaná. Ale ani to nechceme, protože by to znamenalo, že se bude plat skládat vždy

z počtu pracovních dní v prvním týdnu.

Vraťme se tedy na úplný začátek do buňky E4. Zde si vzorec upravíme na plně fungující. Buňka D4

nebude celá absolutně adresovaná, jak jsme zjistili před chvilkou. Chceme, aby se při kopírování měnila

pozice sloupečku z D na F, dále na H atd., protože každý týden má pochopitelně jiný počet pracovních

dní. Nejde nám již ale o to, aby se současně měnilo číslo řádku, při tažení směrem dolů a proto uděláme

kompromis: použijeme adresování smíšené, kdy necháme náš dolar naznačující fixaci pouze před

číslem řádku a to je u nás 4. Ve finále bude buňka ve vzorci vypadat následovně: D$4. Této podoby


docílíte buď ruční úpravou, nebo zkusíte metodu, kdy stisknete klávesu F4 víckrát, má totiž čtyři podoby

(relativní, absolutní, smíšené v řádku, smíšené ve sloupci).

Buňka s hodinovou mzdou C4 bude také smíšeně adresovaná, akorát že opačným způsobem. Tady totiž

víme, že ať budeme počítat týden jakýkoliv, vždy má Excel kouknout do hodinové mzdy, která je

ve sloupci C. Pokud budeme ale tahat směrem dolů, má Excel procházet všechny řádky, ne zůstat stát

hned na tom prvním u paní Bártové, protože každý zaměstnanec má jiný plat. Opět vidíme, že chceme

fixovat jen sloupec, kdežto řádky nechat volně průchozí. Ve vzorci bude tedy tento fakt zapsán

takto: $C4.

Celý vzorec v základní buňce E4 má nyní tuto finální podobu: =$C4*D$4*8. Nyní stačí zatáhnout

za pravý dolní roh a máme vyplněný pravdivě celý první týden. Nyní ale můžeme celý tento první týden

zkopírovat a vložit ho do druhého týdne. Zjistíte, že jsou platy vypočítány naprosto správně. Takto

můžete rozkopírovat třeba sto sloupečků vzorců bez jakékoliv úpravy. V tom spočítá výhoda smíšeného

adresování, ale na samém počátku se zkrátka musí přemýšlet!

Postupem času při práci s Excelem zjistíte, že právě adresace je to, s čím budete pracovat nejčastěji,

pokud se rozhodnete tvořit tabulky, které mají být provázané a jejich tvorba má být co nejjednodušší,

čímž se rozumí fakt, že za pomoci jednoduchého kopírování zvládnete tolik práce, jako při novém

pracném tvoření dalších podobných vzorečků.


DEFINOVÁNÍ NÁZVŮ

Vítejte v kapitole, ve které si prozradíme, jakým způsobem si můžeme při adresaci ušetřit práci

v definování vzorečků. Vraťme se na úplný začátek a vzpomeňme si, jak jsme museli v případě součtů

označovat a definovat adresy buněk či oblastí. Takový součet hodnot za sloupeček Praha jsme museli

definovat jako: =SUMA(C3:C6). Pokud bychom chtěli takovýto součet provést kdekoliv v listě, museli

bychom si stále pamatovat odkazy na tyto buňky, které oblast vymezují. Naším úkolem je, ukázat si,

jak pojmenovat takovouto skupinu buněk nějakým názvem a ten pak vesele používat.

Obr. 4 Definované názvy

Ukážeme si následující cestu, jak tak učinit:

1. Označte naši oblast C3 až C6 (oblast hodnot za Prahu).

2. Vzorce – Definované názvy (Obr. 4) – Definovat název

3. V dialogu Nový název (Obr. 5) vidíme již předvyplněný název Praha, který Excel převzal z názvu

sloupečku.

Obr. 5 Nový název

4. Odkaz na je také vyplněný a to přesně tak, jak jsme označili. Ušetřili jsme si tedy práci

s pozdějším označováním.

5. Klikneme na OK.

V dialogu Nový název (Obr. 5) jste si určitě všimli položky Obor. Každý definovaný název je totiž

nějakého určitého oboru. Ten určujeme takový, v jakém rozsahu se rozhodneme s naším názvem

pracovat. Pokud ho chcete využívat pro celý sešit, necháte volbu Sešit. Pokud chcete nechat používat

název pouze pro konkrétní list, volíte tuto volbu. Pak je možné definovat více stejných názvů,

ale pro každý list by takový název znamenal nějakou jinou oblast.


Obr. 6 Použití definovaného názvu ve vzorci

Nyní jste si nadefinovali název Praha. Ukážeme si tedy, jak takový název používat. Z buňky C7 vymažte

původní vzorec a zapište sem: =SUMA(Praha) tak jako na Obr. 6. Po ENTER uvidíte opět stejný výsledek

s tím rozdílem, že teď už si nemusíte pamatovat, jakáže oblast buněk je pro Prahu. Stačí si třeba na

pětistém řádku vzpomenout, že chcete vidět součet za Prahu, tak napíšete tento vzorec. Může také

nastat situace, kdy zapomenete, kde oblast pojmenovaná Praha přesně byla. Jinými slovy, z jakých

buněk vychází. Pak stačí kliknout v řádku vzorců na černou šipku vedle znázornění adresy buňky (Obr.

7) a vybrat požadovaný název oblasti (Praha). Excel Vám ihned označí tu oblast, která se takto jmenuje.

Všechny definované názvy najdete ve Správci názvů (Vzorce – Definované názvy – Správce názvů),

kde můžete dosud vytvořené názvy upravovat či mazat.

Obr. 7 Výběr názvu oblasti

Jistě si řeknete, že by bylo nyní nepraktické pojmenovávat každé město takto zvlášť. Bylo by to jednak

neefektivní a zabralo by to zbytečně moc času. Tato situace lze vyřešit následovně:

1. Označíte do jedné skupiny všechna další města i hodnoty k nim (Obr. 8).

2. Vzorce – Definované názvy – Vytvořit z výběru

3. V dialogu Vytvořit názvy z výběru zadáme horní řádek. Ve většině nabídek Excel sám nejlépe

zjistí, kde mohou být popisky, takže i v našem případě není potřeba horní řádek zaškrtávat,

protože by již zaškrtnutý měl být.

4. Potvrzujeme OK.

Obr. 8 Označení buněk k pojmenování

Když se nyní podíváte do Správce názvů, uvidíte všechna tři další nadefinovaná města. České

Budějovice budou pojmenované jako České_Budějovice, protože v definici názvu nesmí být mezera.


Dále si ukážeme, že jméno nemusí mít pouze nějaká buňka či oblast, ale že tím může být i konstanta.

Pro jednoduchou ukázku si nadefinujeme nový název (Vzorce – Definované názvy – Definovat název).

Ten se bude jmenovat euro a do políčka odkaz na (Obr. 5) zadáme konstantu 25 místo celého

původního zobrazení v tomto políčku. Tím jsme nadefinovali, že pokud se někde v sešitu zmíníme

o pojmu euro, znamená to, jako bychom se zmínili o hodnotě 25.

Nyní se podívejme na Obr. 9. Zde je tabulka znázorňující částky v Kč. Vedlejší sloupeček slouží

pro přepočet Kč na €. Sem nyní stačí napsat za rovnítko jen adresu buňky s částkou v Kč (př. D16)

napsat lomítko pro dělení a za něj heslo euro, pod kterým jsme nadefinovali onu konstantní

hodnotu 25.

Obr. 9 Definice názvu konstanty


ZMNOŽENÝ VSTUP

Pokud budete chtít vkládat stejnou hodnotu do více buněk, určitě oceníte klávesovou zkratku

CTRL + Enter. Zatímco samotná klávesa Enter potvrzuje vstup údaje do buňky, kombinace této klávesy

s CTRL přináší výhodu v podobě tzv. Zmnoženého vstupu.

Představme si následující tabulku s našimi dodavateli (Obr. 10). Každý měsíc si k nim zapisujeme počet

objednávek. Z tabulky je patrné, že v každém měsíci neobjednáváme u všech dodavatelů, a proto

zůstávají některé buňky nevyplněné.

Obr. 10 Tabulka s prázdnými buňkami k vyplnění

Chtěli bychom v rychlosti vyplnit všechny prázdné buňky hodnotou 0 bez ohledu na to, kolik prázdných

buněk naše tabulka obsahuje.

Obecný postup je následující:

1. Označíme si celou tabulku (včetně vyplněných i prázdných polí).

2. Domů – Úpravy – Najít a vybrat (ikona dalekohledu nebo lupy – dle verze Excelu) – Přejít na –

jinak…

3. V zobrazeném okně (Obr. 11) označíme volbu Prázdné buňky a okno potvrdíme OK. Ihned si

všimněte, že za Vás Excel označil v tabulce pouze ty buňky, které jsou prázdné – nevyplněné.

4. Nyní již stačí napsat hodnotu, kterou chceme vložit do všech označených buněk (tedy

hodnota 0) a následně stiskneme kombinaci kláves CTRL + Enter.

Máme hotovo, tabulka je vyplněná. Důležité je zmínit, že nezáleží na počtu vyplňovaných buněk – tato

operace Vám vždy zabere stejné množství času.

DŮLEŽITÉ: Po kroku č. 3 a před zadáváním hodnoty v kroku č. 4 již myší nikam neklikáte (do žádné

buňky). Tím by totiž došlo ke zrušení označení všech buněk, které jsou prázdné a tabulka by se

nevyplnila – vyplnila by se pouze buňka, na kterou kliknete! Toto je častá chyba, kterou uživatelé dělají.

Po vyplnění okna (Obr. 11) začínáte rovnou psát zmíněnou hodnotu 0 a pokračujete CTRL + Enter.


Obr. 11 Okno Přejít na – jinak

Obr. 12 Vyplněná tabulka

Tabulku nemusíte vyplňovat pouze konstantními hodnotami (jako nyní hodnotou 0). Může se stát,

že existuje tabulka (Obr. 13), která nemá v určitém sloupci vyplněny všechny hodnoty, a vy požadujete

doplnění prázdných buněk vždy podle buňky, která určuje název celé skupiny.

Konkrétně jde například o tabulku vozů (Obr. 13), ve které chybí v prvním sloupci vždy kromě prvního

záznamu ve skupině vyplněný název značky. To by dělalo následně problém při filtrování záznamů

a jejich analýze pomocí kontingenčních tabulek.


Doplnění sloupce Značka bude vypadat následovně:

• První tři kroky budou shodné s postupem popsaným pro předchozí tabulku.

• Ve čtvrtém kroku napíšeme namísto hodnoty 0 (předchozí příklad) vzorec, který bude

ve tvaru: =A2. Vzorec se začne zapisovat automaticky do buňky A3 (první prázdná). Vzorcem

tak říkáme, že chceme do buňky A3 opsat to, co je v buňce A2. Nesmíme však potvrdit klávesou

Enter, ale stiskneme kombinaci kláves CTRL + Enter.

• Když si nyní prohlédnete tabulku, bude již vyplněná pro všechny značky.

• Poté ještě doporučuji převést celý první sloupeček na hodnoty (nyní jsou totiž ve většině buněk

vzorce, které nebudou dělat dobrotu v momentě, kdy budeme chtít data kopírovat jinam nebo

je jinak třídit. Sloupeček tedy stačí označit, zkopírovat a následně Vložit jinak… - Hodnoty.

Tento postup byl detailně popsán v kapitole věnované vkládání jiným způsobem.

Obr. 13 Doplnění chybějících podle názvů skupiny záznamů – v tomto případě podle značky vozu


TABULKA – DEFINOVANÝ SEZNAM

Tabulka (někdy označovaná jako dynamická tabulka či strukturovaný seznam) je nový pojem, který

vznikl v Excelu od verze 2007. Neznamená to ale, že by ve starších verzích tento nástroj chyběl.

Původně nesl označení Definovaný seznam. Postupně se však tento nástroj vyvíjel a dnes nabízí více

možností.

Kdybychom se přenesli opět k naší tabulce autobazaru, nabízí se tu krásný příklad na využití tabulky.

Vžijme se do situace, kdy v bazaru přibude vůz, další den se tři vozy prodají (řádky ubydou) následně

zase šest dalších přijede atd. Narážím tu na problém, kdy nemohu v dynamicky měnící se tabulce nikdy

předvídat, kolik řádků (záznamů) bude mít a tudíž bych musel při každé změně přepsat v Excelu

všechny vzorce a funkce, které jsem měl předtím připravené přímo na míru původní tabulce. Vzorce

by při doplnění nových záznamů na konec tabulky totiž již neplatili a nezahrnovali by nová data. Proto

je tu nástroj s názvem tabulka, který vzorce umí „natahovat“ dle počtu záznamů automaticky, o tom

ale později.

TVORBA A VLASTNOSTI TABULKY V MS EXCEL

Jak takovou tabulku v MS Excel vytvořit? Postup je následující:

• Klikneme kamkoliv do naší databáze.

• Vložení – Tabulka (Obr. 14)

• Excel automaticky detekuje celou databázi a zeptá se Vás, zda tabulka obsahuje záhlaví.

• Klikneme na OK a tabulka je vytvořena.

Obr. 14 Tabulka

První, čeho si všimneme, je změna vzhledu, který je tím nejmíň podstatným. Samozřejmě se nechá

změnit. O tom níže. Dále se v tabulce začal používat automatický filtr (objevily se šipky v záhlaví

databáze). Pokud se budeme pohybovat po tabulce směrem dolů a dostaneme se do situace, kdy se

schová první řádek se záhlavím, všimněme si, že Excel nahradil původní záhlaví sloupečků (A, B, C, …)

za přesné pojmenování názvů polí (Obr. 15), což je další pomůcka pro usnadnění v orientaci.

Obr. 15 Záhlaví tabulky


Každá tabulka má řadu vlastností. Ihned po jejím vytvoření se nahoře na pásu karet objeví karta Návrh,

řazená jako poslední. Ta bude vždy viditelná jen za předpokladu, že máme kurzor umístěný uvnitř naší

databáze, jinak řečeno, jen v případě, že s ní pracujeme. Jedná se tedy o dynamickou kartu. Na této

kartě se nacházejí všechny důležité vlastnosti tabulky:

• Název tabulky – důležitý parametr, pomocí kterého můžeme v budoucnu počítat s daty z této

tabulky v jakémkoli vzorečku. Stačí znát tedy jen její název. Ten si můžeme libovolně změnit.

Tabulka se bude jmenovat například Autobazar.

• V skupině Styly tabulky si můžeme změnit dosavadní vzhled za jiný nabízený.

• Ve skupině Možnosti stylů tabulek si můžeme změnit grafické zobrazení celé tabulky možností

První sloupec, Poslední sloupec, Pruhované sloupce a Pruhované řádky dle vlastního výběru.

Další důležitou volbou je Řádek souhrnů, který je detailněji probírán v následující podkapitole.

ŘÁDEK SOUHRNŮ A PRÁCE S NÍM

Pokud aktivujete Řádek souhrnů (Obr. 16), Excel Vás odkáže na konec tabulky, kde vytvoří řádek

začínající heslem Celkem a informací o počtu záznamů. V našem autobazaru informuje o stavu

258 vozů.

Obr. 16 Řádek souhrnů

Pokud nyní budeme chtít vidět přehlednou statistiku o průměrném počtu najetých kilometrů všech

vozů, součet cen aj., můžeme učinit tak, že klikneme vždy do prázdného políčka v řádku souhrnů, kde si

výsledek představujeme a z připravené nabídky pouze vybereme funkci, která má být pro daný sloupec

použita. Pod sloupcem Najeto KM zvolíme například funkci Průměr (Obr. 17), pod sloupcem Cena

funkci Součet atd. Ihned jsme informováni o výsledku. Ten ještě ke všemu není fixní, ale proměnlivý

(závislý na počtu záznamů v tabulce).

Obr. 17 Výběr funkce v řádku souhrnů

Pokud se podíváte například do výsledku představující průměr najetých kilometrů, zjistíte, že zde Excel

umístil funkci SUBTOTAL, která má dva argumenty. První je číslo, které zastupuje funkci, která se má

ve sloupci provádět (101 – Průměr). Druhý argument, pro nás nyní ten sledovaný, neobsahuje jako

v klasickém případě oblast ohraničenou počáteční a koncovou buňkou, tedy C2:C259, ale obsahuje

v hranatých závorkách název pole, Najeto KM. To určuje, že se nebude průměrovat předem vymezená


oblast, ale taková oblast, která tam skutečně v tomto okamžiku je. To je hlavní význam tabulky v Excelu.

Přidáte-li do takovéto tabulky jeden řádek (záznam) a vyplníte jej, Excel automaticky tyto položky

započítává v řádku souhrnů bez potřeby vzorce nějak upravovat.

Pokud budete chtít přidat do tabulky nový záznam, nemusíte nutně podstupovat kroky k přidání

nového řádku. Stačí, pokud kliknete na buňku posledního záznamu, poslední položky (u nás položka

„Žlutá“ v posledním záznamu vozu „Porsche“). Nyní stisknu klávesu TAB a nový řádek je na světě.

PROBLEMATIKA POLÍ V TABULCE

Každý sloupec tabulky Autobazar je zároveň jedním polem. Každé pole má svoje specifická data a může

se stát, že bych rád pracoval s těmito daty v jiných vzorcích než jen v řádku souhrnů. I to je v Excelu

možné, stačí jen vědět, jak v takových vzorcích postupovat při zápisu.

Vzorce s využitím polí

V jakékoliv prázdné buňce na listu nebo dokonce kdekoliv v sešitu je možné počítat s daty z tabulky

Autobazar. Například můžu chtít spočítat, kolik aut z tabulky je levnějších než 100 000 Kč. Účelem této

kapitoly není podrobně rozebírat, co dělá jaká funkce Excelu, takže nám bude pro tuto chvíli stačit,

když zde uvedu funkci s názvem COUNTIF, která nám dokáže odpovědět na výše položenou otázku.

Díky polím bude výsledek této funkce stále aktuální nezávisle na změně počtu vozů v autobazaru. Zápis

do buňky bude vypadat následovně:

=COUNTIF(Autobazar[Cena];“<100000“).

Ze zápisu je patrné, že čerpáme z tabulky pojmenované jako Autobazar a hranaté závorky v těsné

blízkosti za ní (Obr. 18) vymezují pole Cena. Kritérium je potom nastavená tak, aby funkce započetla

pouze ty ceny, které odpovídají podmínce menší než sto tisíc korun. Výsledkem bude 32 vozů.

Obr. 18 Zápis funkce s polem tabulky


PŘEVEDENÍ DAT TABULKY NA ROZSAH

Může nastat situace, kdy chceme převést naší tabulku Autobazar na klasický rozsah, tedy na databázi,

jakou byla tabulka před naším zásahem. Postup, jak tak učinit je následující:

• Kliknout kamkoliv do tabulky.

• Návrh – Nástroje – Převést na rozsah (Obr. 19)

Nyní ale tabulka ztrácí své vlastnosti a usnadnění a při každé změně v ní musím počítat s určitými

úpravami vzorců.

Obr. 19 Převedení tabulky na rozsah


MATICOVÉ VZORCE

Maticový vzorec tvoříme tehdy, jestliže chceme provést několik výpočtů bez použití mnoha mezikroků.

Tento typ vzorců tvoříme stejně jako jiné typy vzorců s tím rozdílem, že potvrzení vzorce neukončujeme

klasicky klávesou ENTER, ale kombinací tří kláves CTRL + SHIFT + ENTER.

Při tvorbě maticového vzorce musíme vědět, zda očekáváme jeden nebo více výsledků.

VÝPOČET JEDNOHO VÝSLEDKU

Na následujícím příkladu si ukážeme první variantu – výpočet jednoho výsledku. V tabulce na Obr. 20

vidíme, jakou sazbou na hodinu je ohodnocen každý zaměstnanec a kolik hodin ve firmě odpracoval.

Obr. 20 Výpočet jediného výsledku - maticově

Úkolem zaměstnavatele (náš úkol) je vypočítat celkovou částku, která bude potřeba k pokrytí

měsíčních platů všech zaměstnanců.

Maticový vzorec nám zde poslouží k tomu, abychom si ulehčili práci a nemuseli počítat nejprve

u každého zaměstnance jeho plat zvlášť a poté všechny platy sečíst. Představte si, že bychom nejprve

museli např. do buňky D2 vepsat vzorec:

=B2*C2

což by odpovídalo platu pana Novotného a pak tento vzorec kopírovat na další zaměstnance. Poté

bychom teprve provedli (např. do buňky B6 jako na Obr. 20) součet všech těchto dílčích platů pomocí

funkce součtu:

=SUMA(D2:D4)

Veškerý tento postup jde vyřešit právě maticovým vzorcem.

Namísto výpočtu dílčích platů u každého zaměstnance se přesuneme již nyní přímo do buňky B6. Do ní

zapíšeme vzorec:

=SUMA(B2:B4*C2:C4)


ale nepotvrzujeme klávesou ENTER, ale již výše zmiňovanou trojkombinací CTRL + SHIFT + ENTER. Nyní

Vám Excel vypočetl výši celkových měsíčních nákladů na platy všech zaměstnanců, a když teď kliknete

do buňky B6 a podíváte se do řádků vzorců, zobrazí se Váš zadaný vzorec navíc ve složených závorkách:

{=SUMA(B2:B4*C2:C4)}

Tyto závorky nám říkají, že se jedná o maticový vzorec.

VÝPOČET VÍCE VÝSLEDKŮ

Následující příklad (na Obr. 21) počítá s tím, že chceme právě spočítat jen dílčí platy jednotlivých

zaměstnanců. Od maticového vzorce se tedy očekává více výsledků. Když se podíváme na Obr. 21

a víme, že chceme zjistit plat každého zaměstnance zvlášť, je jasné, že chceme násobit hodinovou mzdu

s počtem odpracovaných hodin. Mohli bychom tedy např. do buňky D2 psát vzorec pro součin

=B2*C2

a dále ho zkopírovat pro ostatní položky. To samé se dá ale vytvořit i pomocí jednoho vzorce

(maticového) bez následného kopírování.

Obr. 21 Výpočet více výsledků - maticově

Jak postupovat nyní? Nejprve označíme oblast, ve které naše výsledky očekáváme (klikneme levým

tlačítkem myši do buňky D2, levé tlačítko myši držíme a myší táhneme přes oblast buněk, ve které

chceme zobrazit naše dílčí výsledky, tlačítko myši pustíme a započneme psát vzorec:

=B2:B4*C2:C4

a potvrdíme trojkombinací CTRL + SHIFT + ENTER. Nyní uvidíme všechny naše potřebné výsledky

vypočtené, a když se podíváme do některého z nich, uvidíme v řádku vzorců maticový vzorec opět

ve složených závorkách:

{=B2:B4*C2:C4}


ÚPRAVA MATICOVÉHO VZORCE

Často se nám stane, že jsme náš vzorec nenapsali přesně podle našich představ anebo ho chceme

jednoduše z jiných důvodů opravit.

Postup při úpravě maticového vzorce je následující:

1. Klikneme na jednu buňku, ve které se nachází maticový vzorec.

2. Přesuneme se do řádku vzorců, ve kterém se nám maticový vzorec zobrazí (klikneme

do tohoto řádku – po kliknutí nám zde zmizí složené závorky).

3. Upravíme vzorec dle našich potřeb.

4. Potvrdíme trojkombinací kláves: CTRL + SHIFT + ENTER.

K-NÁSOBKY OBLASTI

Maticové vzorce využijeme také v případě, kdy se chystáme provádět nějakou matematickou operaci,

ve které pracuje vzájemně nějaká oblast buněk s konstantou.

Na Obr. 22 je vidět, že jsou konstantou k násobené hodnoty z oblasti vektoru. Abychom nemuseli

využívat absolutní adresování nebo vypisovat každý vzorec zvlášť, můžeme použít maticový vzorec.

Obr. 22 K-násobek oblasti

Nejprve si opět označíme oblast, ve které očekáváme výsledky (na Obr. 22 se jedná o oblast C2 až C4).

Poté začneme psát vzorec. V případě násobení (viz Obr. 22) je to následující:

=B2:B4*A2

a nesmíme zapomenout potvrdit jako maticový vzorec, tj. nám známým stiskem tří kláves.

SOUČET DVOU OBLASTÍ (VEKTORŮ)

Na Obr. 23 v následujícím příkladu je vidět, že chceme sečíst vektor A a vektor B neboli dvě oblasti

do dalšího sloupce.

Obr. 23 Součet dvou oblastí (vektorů)


Opět tedy začneme označením oblasti, do které chceme vložit výsledek a začneme psát vzorec:

=A2:A4+B2:B4

který potvrdíme CTRL + SHIFT + ENTER.

Výsledná oblast bude tedy součtem předchozích dvou. Stejným způsobem můžeme oblasti i odečítat.

SOUČIN DVOU MATIC

Proto, abychom mohli sčítat dvě matice (souvislé pravoúhlé oblasti), musíme znát základní definici

součinu matic. Ta nám říká, že počet sloupců v první matici musí být roven počtu řádků v druhé matici.

Jinak nelze matice vzájemně násobit.

Funkce pro tento výpočet se jmenuje SOUČIN.MATIC. Jako své argumenty očekává jednotlivé matice.

Nelze zde napsat prosté násobení, protože to není součin matic.

Vše si ukážeme na následujícím příkladu, který je vyobrazen na Obr. 24.

Obr. 24 Součin dvou matic

Na Obr. 24 vidíme dvě matice, které splňují naše předchozí pravidlo. Počet sloupců první matice A

(3 sloupce) odpovídá počtu řádků druhé matice B (3 řádky). Výsledkem nám tedy bude součin těchto

dvou matic ve dvou sloupcích a 5 řádcích (viz obr. 11).

Nejprve začneme označením oblasti výsledku. V tomto případě se jedná o oblast H2 až I6. Po označení

této oblasti začneme psát vzorec:

=SOUČIN.MATIC(A2:C6;E2:F4)

a potvrdíme trojkombinací kláves. Ze zadávání vzorce je patrné, že každou oblast (matici) musíme

oddělit středníkem.

Tato kapitola byla jakýmsi odbočením do oblasti funkcí, kterým se věnuje jiná publikace, ale byla zde

zmíněna z důvodu, že do oblasti matic patří.


PŘÍKLAD VYUŽITÍ MATICOVÝCH VZORCŮ

Zadání:

V tabulce (Obr. 25) máme prodejce, kteří prodávají produkty uvedené na prvním řádku. Na druhém

řádku je jejich cena za jednotku (ks či kg). Naším úkolem je vypočíst do žlutých polí ve sloupci I celkovou

tržbu každého z prodejců.

Obr. 25 Tabulka Prodejci

Řešení:

Kdybychom řešili příklad klasickým vzorcem, měl by pro prodejkyni Simonu následující podobu:

=C2*C3+D2*D3+E2*E3+F2*F3+G2*G3+H2*H3

Jak vidíte sami, vzorec je zbytečně dlouhý a byl by samozřejmě ještě delší, pokud bychom neprodávali

6 produktů, ale např. 100 či více. Proto použijeme maticový vzorec. Ten bude mít vždy stejnou délku

nehledě na počet prodávaných produktů.

1. Označíme buňku I3 (ve sloupci Celkem pro Simonu).

2. Napíšeme rovná se (znak „=“).

3. Označíme buňky C2 až H2 (jednotkové ceny) – tento rozsah rovnou zafixujeme. Opatříme znaky

„$“ – jedná se o oblast, která bude ve vzorci společná pro všechny prodejce a při tažení vzorce

směrem dolů by nemělo docházet k posunu oblasti s těmito cenami – viz kapitola „Absolutní

adresování“.

4. Napíšeme krát (znak „*“).

5. Označíme buňky C3 až H3 (počet prodaných jednotek za Simonu).

6. Potvrdíme stiskem tří kláves CTRL + SHIFT + ENTER.

7. Nyní vzorec zkopírujeme do všech žlutých polí (pro všechny zaměstnance).

Vzorec (Obr. 26Obr. 28) bude tedy vypadat následovně (po jeho potvrzení se zobrazí v řádku vzorců i

složené závorky na jeho začátku i konci):

{=SUMA($C$2:$H$2*C3:H3)}


Obr. 26 Tabulka Prodejci s maticovým vzorcem

V případě řešení tohoto příkladu by bylo možné využít i funkce SOUČIN.SKALÁRNÍ, která by v tomto

případě nepotřebovala maticové potvrzení tří kláves, ale potvrdila by se jako obyčejný vzorec. Její zápis

by byl v případě prvního řádku výpočtu u prodejkyně Simony následovně:

=SOUČIN.SKALÁRNÍ($C$2:$H$2;C3:H3)

Je však nutné poznamenat, že se v této kapitole jedná o použití maticových vzorců, proto je zde

upřednostněn první (maticový) zápis.

ŘEŠENÍ KŘÍŽOVÝCH TABULEK POMOCÍ MATIC

Zadání:

Máme tabulku (Obr. 27), ve které hraje hlavní roli výpočet platů našich zaměstnanců v jednotlivých

měsících v roce. Ve sloupci B je vždy vedle jména zaměstnance jeh hodinové ohodnocení v Kč

a na řádku č. 18 jsou pod názvy měsíců počty pracovních dní, které musí naši zaměstnanci odpracovat.

Každý z nich přitom musí dodržet 8 odpracovaných hodin denně. Jaká bude celková odměna každého

z nich v každém měsíci, když pomineme přesčasy, dovolené, nemoci a svátky?

Obr. 27 Křížová tabulka Platy


Řešení:

1. Označíme všechny žluté buňky (pravoúhlá oblast, do které chceme vypočítat platy).

2. Napíšeme rovná se (znak „=“).

3. Označíme buňky B19 až B26 (sloupec hodinových mezd).


5. Napíšeme 8 (počet hodin denně).


7. Označíme buňky C18 až N18.

8. Potvrdíme stiskem tří kláves CTRL + SHIFT + ENTER.

Vzorec (Obr. 28) bude tedy vypadat následovně (po jeho potvrzení se zobrazí v řádku vzorců i složené

závorky na jeho začátku i konci):

{=B19:B26*8*C18:N18}

To je celý postup. Máme hotovo. Nyní máme vypočteno tolik platů, kolik buněk jsme dopředu označili

před tvorbou vzorce.

Obr. 28 Křížová tabulka Platy s maticovým vzorcem

Tento příklad bychom opět mohli řešit klasickým vzorcem a nikoliv maticově. V tom případě bychom

pak museli řešit tzv. „fixování“ jednotlivých souřadnic buněk. Potom bychom předem neoznačovali

všechny buňky pro získání výsledků, ale pouze první (v našem případě buňku u Michala v lednu – tedy

buňku C19) a do ní bychom napsali jednoduchý vzorec =B19*8*C18. Abychom vzorec mohli přetáhnout

ve sloupci směrem dolů a po řádcích směrem doprava, je zapotřebí zafixovat ho zafixovat za pomoci

smíšené adresace, o které byla zmínka v kapitole „Adresace“. Výsledná podoba vzorce by byla:

=$B19*8*C$18


FUNKCE

Funkce v Excelu mají jediný cíl – zjednodušit práci při výpočtech a vyhledávání v tabulkách. Díky

funkcím zvládneme operace, které by nám mnohdy trvaly dlouho, nebo takové operace, které by

mnohdy nebyly ani v našich silách (viz přiřazování hodnot z databáze o tisících řádcích).

POUŽÍVÁNÍ FUNKCÍ

Funkci můžeme v Excelu použít jedním z následujících postupů:

Použití průvodce funkcí:

1. Klikneme do buňky, do které chceme psát vzorec.

2. Klikneme na ikonu fx, která leží na řádku vzorců (Obr. 29).

Obr. 29 Řádek vzorců - fx

3. Spustí se dialogové okno Vložit funkci (Obr. 30).

4. Vybereme si funkci, kterou chceme použít (funkce jsou zařazeny do jednotlivých kategorií).

Pokud již funkce v Excelu používáme, jsou nám tyto funkce nabízeny v kategorii Naposledy

použité.

5. Po výběru funkce klikneme na OK.

6. Spustí se dialogové okno Argumenty funkce (Obr. 31).

7. V tomto okně vyplníme všechna pole určená pro jednotlivé argumenty námi vybrané funkce.

Všechny argumenty, které jsou povinné, jsou označeny tučně. Nepovinné argumenty tučné

nejsou – znamená to, že je nemusíme zadávat (na Obr. 31 jsou zadány i nepovinné argumenty

funkce KDYŽ – jedná se o argumenty Ano a Ne – definují, co se má stát v případě, že je

podmínka splněná a co v případě, že splněná není – pokud by nebyly tyto argumenty vyplněny,

Excel by namísto výsledků Zisk a Ztráta zobrazoval logické hodnoty PRAVDA či NEPRAVDA).

8. Vyplněné okno potvrdíme stiskem tlačítka OK.


Obr. 30 Vložit funkci

Obr. 31 Argumenty funkce

Použití karty Vzorce:


2. Klikneme na kartu Vzorce a ve skupině Knihovna funkcí klikneme na jednu ze skupin funkcí

(např. Logické) a vybereme jednu z nabízených funkcí.


Obr. 32 Karta Vzorce - skupina Knihovna funkcí

3. Následně postupujeme úplně stejně jako v předchozím případě, neboť se otevře okno

pro zadání argumentů funkce (Obr. 31).

Přímý zápis funkce do buňky:


2. Začneme psát vzorec obsahující funkci přímo za znaménko rovná se (znak „=“).

3. Stačí, když začneme psát za rovnítko název funkce (v případě funkce KDYŽ např. jen KD…)

a Excel již sám zobrazuje nabídku s funkcemi, které začínají na tato písmena. Pro doplnění

celého názvu z nabídky musíme buď na název 2× kliknout levým tlačítkem myši nebo

kurzorovými šipkami funkci označit a stisknout klávesu TAB (pozor, zde se často chybuje

a namísto TAB uživatelé stisknou klávesu ENTER, která však vzorec předčasně potvrdí

a „vyskočí“ z funkce pryč).

4. Do závorek definujeme jednotlivé argumenty, se kterými nám pomáhá malá nápověda

zobrazená pod právě vytvářeným vzorcem. Nesmíme zapomínat vkládat mezi argumenty

středník (znak „;“), který argumenty odděluje (viz pravidla dále).

5. Vzorec potvrdíme klávesou Enter.

Obr. 33 Přímý zápis funkce do buňky

Pokud budeme tvořit jednoduché vzorce (Obr. 33), není třeba psát konečnou závorku. Tu po stisku

klávesy ENTER doplní Excel za Vás. Je třeba si však dávat pozor na koncové závorky vždy,

když používáme ve vzorci více funkcí (viz vnořená funkce dále).


Jak se funkce definuje

Každý zápis funkce musí obsahovat:

1. Znak „=“ (rovná se).

2. Název funkce (tedy např. SUMA, KDYŽ, SVYHLEDAT, …).

3. Otevírací závorku „(“.

4. Argumenty, které vstupují do funkce (např. funkce SUMA má jako argumenty čísla, která

potřebujeme sečíst, případně celé oblasti čísel).

5. Argumenty ve funkcích oddělujeme středníkem „;“.

6. Zavírací závorku „)“. V tomto případě lze u jednoduchých vzorců zápis potvrdit i bez ní a Excel

se postará o její doplnění na konec zápisu.

Syntaxe

Syntaxe vyjadřuje požadavky jednotlivých funkcí na argumenty, které mají být do funkce zadány tak,

aby funkce dělala to, co má. Syntaxe rovněž říká, v jakém pořadí mají být jednotlivé argumenty

do funkce zadány a jaké argumenty jsou povinné a jaké nepovinné.

Příklad syntaxe funkce SVYHLEDAT:

=SVYHLEDAT(hledat;tabulka;sloupec;[typ])

• Funkce SVYHLEDAT, na základě které je demonstrována ukázka zápisu výše, obsahuje

4 argumenty (hledat, tabulka, sloupec, typ).

• První tři argumenty (hledat, tabulka, sloupec) jsou povinné.

• Poslední čtvrtý argument (typ) je nepovinný. Poznáme to tak, že je uveden v nápovědě

v hranatých závorkách [typ]. Znamená to tedy, že ho zadávat nemusíme. I v takovém případě

funkce bude fungovat. Nicméně takový argument znamená jisté upřesnění, díky kterému

se funkce může zachovat jinak, než bez něj. Tato znalost nám zatím postačí k pochopení

syntaxe jednotlivých funkcí. O konkrétních funkcích (včetně funkce SVYHLEDAT) budou

pojednávat následující kapitoly, které uvedou použití s vysvětlením na konkrétních příkladech.


VNOŘENÉ FUNKCE

Princip vnořené funkce spočívá ve využití jedné funkce, která potřebuje pro své fungování hodnotu

(argument), kterou nemáme k dispozici a musíme si ji nejprve vypočítat – vypočítáme ji tedy

prostřednictvím jiné (druhé) funkce, která se stane funkcí vnořenou.

Pro jednodušší pochopení předchozí definice poslouží následující příklad.

Zadání:

Potřebujeme zaokrouhlit součet dat z celé tabulky na celé číslo.

Řešení:

Abychom nemuseli nejprve spočítat součet funkcí SUMA a poté do jiné buňky zvlášť zaokrouhlovat

pomocí funkce ZAOKROUHLIT, máme možnost funkci SUMA vnořit do funkce ZAOKROUHLIT a vytvořit

tak jednu vnořenou funkci, která vrátí jediný výsledek (bez mezivýsledku v podobě nezaokrouhleného

součtu). Výsledek bude vypočtený za pomoci následujícího zápisu:

=ZAOKROUHLIT(SUMA(A2:A50);0)

Část SUMA(A2:A50) v předchozím vzorci zastupuje vnořenou funkci. Jako každá jiná funkce má své

závorky a v nich své argumenty. Tímto postupem tak ušetříme jeden mezivýsledek, který bychom

samostatně k ničemu nepotřebovali.

KATEGORIE FUNKCÍ

V Excelu narazíme na dělení jednotlivých funkcí do následujících skupin:

• Matematické a trigonometrické

• Statistické

• Vyhledávací

• Logické

• Informační

• Funkce pro práci s datem a časem

• Textové

• Databázové

• Finanční

• Kompatibilní – pro práci s funkcemi v nových verzích z dřívějších verzí Excelu

• Další specifické (web, konstrukce, datová krychle)

V následujících kapitolách projdeme ty nejdůležitější funkce z MS Excel. Je jich samozřejmě mnohem

více, ale jedná se pak o funkce, které používá menšina uživatelů.


MATEMATICKÉ FUNKCE

SUMA

Účel funkce SUMA:

• Sečte všechna čísla v zadané oblasti buněk.

Syntaxe funkce SUMA:

=SUMA(číslo1;[číslo2];…)

Popis jednotlivých argumentů funkce SUMA:

• Číslo1 – jedná se o oblast buněk (např. A1:B50), o jednu buňku (např. A1) nebo název oblasti

(např. tržby).

• Funkce obsahuje další nepovinné argumenty Číslo2 až Číslo255 – můžeme tak definovat

až 255 čísel či oblastí, které chceme sečíst.

Příklad použití funkce SUMA:

Potřebujeme sečíst hodnoty ve sloupcích „Praha“ a „Pardubice“ v následující tabulce prodejů

elektroniky (Obr. 34).

Obr. 34 Tabulka prodeje elektroniky


Použijeme následující vzorec:

=SUMA(C3:C12;F3:F12)

V tomto vzorci můžeme číst znak dvojtečky jako AŽ a znak středníku jako A (součet buněk C3 až C12

a k tomu buněk F3 až F12).

Pokud bychom měli jednotlivé sloupce definované jako názvy oblastí, mohl by vzorec vypadat také

takto:

=SUMA(Praha;Pardubice)

Tip:

Pokud máme kurzor pod sloupcem čísel, která chceme sečíst, můžeme použít klávesovou zkratku

Alt + = (Alt + znak „=“). Ta vloží do buňky funkci SUMA a rovnou navrhne oblast, kterou chceme sčítat

(buňky nad tou aktuální, případně vedle aktuální). Stačí stisknout ENTER a součet je hotový.

Pro vložení funkce SUMA můžeme využít také nástroj Automatické shrnutí, který je umístěný na kartě

Domů ve skupině Úpravy (Obr. 35).

Obr. 35 Skupina nástrojů Úpravy na kartě Domů

ZAOKROUHLIT

Účel funkce ZAOKROUHLIT:

• Funkce zaokrouhlí hodnotu na zadaný počet číslic (daný počet desetinných míst

či zaokrouhlení tzv. před desetinnou čárkou – doleva).

Syntaxe funkce ZAOKROUHLIT:

=ZAOKROUHLIT(číslo;číslice)

Popis jednotlivých argumentů funkce ZAOKROUHLIT:

• Číslo – jedná se o číslo, které chceme zaokrouhlit.

• Číslice – určuje, jak se má zadané číslo v předchozím argumentu zaokrouhlit.

Příklad použití funkce ZAOKROUHLIT:

V tabulce (Obr. 36) provedeme součet a budeme chtít tento součet zaokrouhlit na několik různých

způsobů (celé číslo, 1 desetinné místo, na stovky, na celé číslo dolů i nahoru).


Obr. 36 Zaokrouhlování

Pro zaokrouhlení součtu do buňky C14 na celé číslo použijeme následující vzorec:

=ZAOKROUHLIT(C13;0)

Nula za středníkem zde říká, že chceme zaokrouhlit na 0 desetinných míst.

Pro zaokrouhlení součtu do buňky C15 na 1 desetinné místo:

=ZAOKROUHLIT(C13;1)

Pro zaokrouhlení součtu do buňky C16 na stovky:

=ZAOKROUHLIT(C13;-2)

Důležité je zde znaménko mínus, které říká, že budeme zaokrouhlovat od desetinné čárky o 2 místa

doleva (čili stovky).

Tip:

Pokud budeme chtít zaokrouhlovat vždy dolů, použijeme funkci ROUNDDOWN. V případě, že budeme

chtít zaokrouhlovat vždy nahoru, využijeme funkci ROUNDUP. V obou případech se jedná o funkce se

stejnou syntaxí jako v případě funkce ZAOKROUHLIT, takže se nemusíme obávat dalších komplikací.

Pro zaokrouhlení součtu do buňky C17 na celé číslo dolů:

=ROUNDDOWN(C13;0)

Pro zaokrouhlení součtu do buňky C18 na celé číslo nahoru:

=ROUNDUP(C13;0)

SUBTOTAL

Účel funkce SUBTOTAL:

• Funkce SUBTOTAL se zabývá souhrnem dat v seznamu nebo databázi. Nutno podotknout,

že se nejedná o funkci, která musí za každou cenu sčítat. Jedná se o funkci, která umí mimo

jiné i průměrovat, počítat, hledat minimum, maximum, atd.

• Její hlavní přednost je ta, že umí pracovat pouze s viditelnými daty – tzn., daty, která zůstala

zobrazeny po použití filtru v nějaké tabulce.


Syntaxe funkce SUBTOTAL:

=SUBTOTAL(konstanta funkce;odkaz1;[odkaz2];…)

Popis jednotlivých argumentů funkce SUBTOTAL:

• Konstanta funkce – jedná se o číslo 1 až 11 nebo 101 až 111, které určuje, jaká funkce bude

v rámci souhrnu použita. Funkcí je možné použít celkem 11. Ve stovkové řadě se jedná

o stejné funkce s tím rozdílem, že funkce ignoruje řádky, které jsou ručně skryté, kdežto

v klasické řady jsou tyto řádky zahrnovány do souhrnů. Jednotlivé konstanty jsou uvedeny

v tabulce níže (Tab. 1).

• Odkaz1 – oblast, u které chceme vypočítat souhrn.

• Odkaz2 až Odkaz254 – další oblasti, pro které chceme počítat souhrn (nepovinné).

Tab. 1 Konstanty funkcí ve funkci SUBTOTAL

Konstanta funkce (zahrnuje i ručně skryté hodnoty)

Konstanta funkce (ignoruje i ručně skryté hodnoty)

Funkce

1 101 PRŮMĚR

2 102 POČET

3 103 POČET2

4 104 MAX

5 105 MIN

6 106 SOUČIN

7 107 SMODCH.VÝBĚR

8 108 SMODCH

9 109 SUMA

10 110 VAR.VÝBĚR

11 111 VAR

Příklad použití funkce SUBTOTAL:

Budeme používat tabulku autobazaru Obr. 37, ve které budeme chtít rovněž vytvářet součty najetých

kilometrů a průměrné ceny, ale vždy pouze u vozů, které budou v tabulce viditelné (jinými slovy

vyfiltrované). Původní tabulka má rozsah 315 záznamů.

Například vyfiltrujeme všechny vozy značky Porsche a hned budeme chtít znát výše zmíněné dva

souhrny. Pokud bychom použili klasické funkce SUMA a PRŮMĚR, bohužel by byly výsledky počítány

v rámci celé tabulky (za všechny značky – i přesto, že jsou záznamy vyfiltrované) a to by bylo špatně.

Proto použijeme funkci SUBTOTAL, která právě v těchto případech jako jediná ze všech funkcí spočítá

pouze viditelné hodnoty.


Obr. 37 Tabulka autobazaru pro výpočet průměru a součtu

Jak tedy napsat vzorec, který bude sčítat ujeté kilometry u vozů, které jsou aktuálně v tabulce

autobazaru vyfiltrované?

=SUBTOTAL(9;C2:C315)

Ve vzorci si všimněme konstanty s č. 9, která dle tabulky konstant (Tab. 1) říká, že se jedná o součet.

Průměrná cena vozů, které budou v tabulce vyfiltrované, vypočítáme následovně:

=SUBTOTAL(1;D2:D315)

Jednička znamená průměr. Funkce tedy spočítá průměrnou hodnotu v označené oblasti.

Důležité je, aby byla oblast vždy označena celá – v našem druhém případě cen se jedná o oblast D2

až D315, ve které se nacházejí všechny ceny. Kdybychom nejprve provedli filtr a teprve následně psali

funkci SUBTOTAL a označili myší oblast cen, zcela jistě nebude oblast kompletní a při dalším filtrování

by nemusel výsledek odpovídat. Proto nejprve napišme vzorec a potom filtrujme. Vyvarujeme se tak

zbytečných chyb.


STATISTICKÉ FUNKCE

POČET

Účel funkce POČET:

• Funkce POČET umí spočítat, kolik buněk v označené oblasti obsahuje číselné hodnoty.

Syntaxe funkce POČET:

=POČET(hodnota1;[hodnota2];…)

Popis jednotlivých argumentů funkce POČET:

• Hodnota1 – odkaz na buňku nebo oblast, ve které chceme spočítat číselné hodnoty.

• Hodnota2 až Hodnota255 – další odkazy na buňky, ve kterých chceme počítat číselné

hodnoty. Tyto argumenty jsou již nepovinné.

Příklad použití funkce POČET:

Budeme chtít spočítat počet číselných hodnot v následující tabulce odměn a postihů (Obr. 38). Jinými

slovy chceme zjistit, kolikrát se v tabulce vyskytuje odměna nebo postih.

Obr. 38 Tabulka odměn a postihů pro počty

Vzorec bude vypadat následovně:

=POČET(O3:Z10)

POČET2

Účel funkce POČET2:

• Funkce POČET2 umí spočítat, kolik buněk v označené oblasti je neprázdných (znamená to,

že funkce počítá jak číselné hodnoty, tak text).


Syntaxe funkce POČET2:

=POČET2(hodnota1;[hodnota2];…)

Popis jednotlivých argumentů funkce POČET2:

• Stejné jako v případě funkce POČET.

Příklad použití funkce POČET2:

Kdybychom chtěli vypočítat, kolik buněk v předchozí tabulce (Obr. 38) je číselně naplněných a použili

bychom funkci POČET2, nedošli bychom ke správné hodnotě, neboť stavy, kdy nedošlo k odměnám

nebo postihům, jsou proškrtnuty a tím je v buňce vepsaný textový znak. Funkce POČET2 tak spočítá jak

číselné, tak textové hodnoty. Kdyby byla tabulka namísto proškrtnutých buněk ponechána s prázdnými

poli, mohli bychom funkci POČET2 použít a vykazovala by tak stejný výsledek jako funkce POČET.

Funkci POČET2 budeme používat vždy, když budeme chtít počítat textem naplněné buňky. Úkolem

tedy bude spočítat, kolik jmen obsahuje následující tabulka (Obr. 39):

Obr. 39 Obchodníci


=POČET2(A2:A56)

PRŮMĚR

Účel funkce PRŮMĚR:

• Funkce PRŮMĚR spočítá aritmetický průměr ze zadaných oblastí hodnot.

Syntaxe funkce PRŮMĚR:

=PRŮMĚR(číslo1;[číslo2];…)


Popis jednotlivých argumentů funkce PRŮMĚR:


(např. tržby).


až 255 čísel či oblastí, které chceme sečíst.

Příklad použití funkce PRŮMĚR:

Rádi bychom spočítali průměrnou hodnotu za platy v následující tabulce (Obr. 40) za celý rok

a následně průměr za měsíce leden, únor, duben, červen a červenec.

Obr. 40 Platová tabulka

Vzorec pro výpočet průměrného platu za celý rok bude následující:

=PRŮMĚR(C2:N60)

Pokud by byla oblast platů pojmenovaná (např. platy), mohl by vzorec vypadat následovně:

=PRŮMĚR(platy)

Vzorec pro výpočet průměrného platu za měsíce leden, únor, duben, červen a červenec bude

následující:

=PRŮMĚR(C2:D60;F2:F60;H2:I60)


COUNTIF

Účel funkce COUNTIF:

• Funkce COUNTIF dokáže spočítat, kolikrát se v dané oblasti nachází hodnota, která je zadána

jako kritérium.

• Funkci použijeme vždy, když se ptáme otázkou: „Kolikrát je něco někde?“

Syntaxe funkce COUNTIF:

=COUNTIF(oblast;kritérium)

Popis jednotlivých argumentů funkce COUNTIF:

• Oblast – oblast buněk, na které se bude stahovat výpočetní kritérium.

• Kritérium – podmínka, která upřesní, jaká data chceme spočítat (z těch, která jsme označili

jako oblast).

Příklad použití funkce COUNTIF:

Úkolem bude spočítat, kolik hodnot z následující tabulky (Obr. 41) je kladných – tedy, kolikrát jsme

za celý rok dali odměnu.

Obr. 41 Tabulka odměn a postihů

Vzorec bude vypadat následovně (v podmínce, která řeší nerovnost, jsou důležité uvozovky):

=COUNTIF(O3:Z10;">0")

Pokud bychom chtěli vypočítat, kolikrát se v tabulce nachází hodnota, která je přesně rovna 5148,

zapíšeme vzorec:

=COUNTIF(O3:Z10;5148)


V tabulce autobazaru (Obr. 37) bychom chtěli vypočítat, kolikrát se v ní nachází značka „Škoda“. Vzorec

by vypadal následovně:

=COUNTIF(A2:A315;"Škoda")

Dále bychom chtěli vypočítat, kolikrát se v tabulce autobazaru vyskytuje vůz, jehož název značky začíná

na písmeno F. Vzorec bude vypadat následovně:

=COUNTIF(A2:A315;"F*")

Hvězdička zde zastupuje nekonečnou posloupnost jakýchkoliv znaků. Říkáme tedy, že chceme v oblasti

A2 až A315 spočítat názvy začínající na F, za kterým může být cokoliv.

Pokud bychom chtěli upřesnit, že vůz začínající na písmeno F musí být navíc dlouhý celkem 5 znaků,

vzorec by byl následující:

=COUNTIF(A2:A315;"F????")

Otazník zde zastupuje právě jeden jakýkoliv znak.

V posledním příkladu budeme chtít spočítat vozy, které jsou levnější nebo stejně drahé jako zadaná

hodnota v buňce G2 (Obr. 42).

Obr. 42 Tabulka autobazaru s kritériem ceny

Vzorec bude následující:

=COUNTIF(D2:D315;"<="&G2)

COUNTIFS

Účel funkce COUNTIFS:

• Funkce COUNTIFS dokáže spočítat, kolikrát se v dané oblasti nachází hodnota, která

je ohraničena až 127 podmínkami.


Syntaxe funkce COUNTIFS:

COUNTIFS(oblast_kritérií1;kritérium1;[oblast kritérií2;kritéria2];…)

Popis jednotlivých argumentů funkce COUNTIFS:

• Jedná se o stejné argumenty jako u funkce COUNTIF s tím rozdílem, že lze dvojici oblasti

a kritéria využít až 127 krát. Je tak možné využít více než jednu podmínku, jako je tomu

u funkce COUNTIF.

Příklad použití funkce COUNTIFS:

V tabulce autobazaru (Obr. 42) budeme chtít spočítat, kolik vozů se nachází v cenovém rozmezí

od 100 000 Kč do 200 000 Kč.


=COUNTIFS(D2:D315;">=100000";D2:D315;"<=200000")

Oblast pro výpočet je stejná jak pro jednu, tak pro druhou podmínku (jedná se o rozsah). Přesto musí

být oblast zadána dvakrát. Vždy se totiž jedná o dvojice argumentů (oblast, kritérium).

SUMIF

Účel funkce SUMIF:

• Funkce SUMIF sečte v určité oblasti ty hodnoty, které splňují zadanou podmínku (např. pouze

kladné hodnoty, záporné hodnoty, hodnoty vyšší než zadaná podmínka atd.).

Syntaxe funkce SUMIF:

=SUMIF(oblast;kritérium;[součet])

Popis jednotlivých argumentů funkce SUMIF:


• Kritérium – podmínka, která upřesní, jaká data chceme sečíst (z těch, která jsme označili jako

oblast).

• Součet – jedná se o nepovinný argument, který je využit v momentě, kdy sčítaná oblast není

stejná s oblastí, na kterou se vztahuje kritérium.

Příklad použití funkce SUMIF:

V tabulce odměn a postihů (Obr. 41) budeme chtít sečíst výši vyplacených odměn (kladných hodnot).


=SUMIF(O3:Z10;">0")

Pokud budeme chtít v tabulce autobazaru (Obr. 42) provést součet cen za značku Alfa Romeo, bude

vzorec funkce SUMIF vypadat následovně:

=SUMIF(A2:A315;"Alfa Romeo";D2:D315)


SUMIFS

Účel funkce SUMIFS:

• Funkce SUMIFS umí sečíst hodnoty na základě až 127 podmínek.

Syntaxe funkce SUMIFS:

=SUMIFS(oblast;oblast_kritérií1;kritéria1;[oblast_kritérií2;kritéria2];...)

Popis jednotlivých argumentů funkce SUMIFS:

• Oblast – oblast, která zahrnuje hodnoty, které budou v konečném důsledku sečteny.

• Oblast_kritérií1 – oblast buněk, na které se bude stahovat výpočetní kritérium.

• Kritéria1 – podmínka, která upřesní, jaká data chceme sečíst (z těch, která jsme označili jako

oblast_kritérií1).

• Oblast_kritérií2 až Oblast_kritérií127 – další oblasti, které rozšiřují možnost kritérií. Jedná se

o nepovinné argumenty.

• Kritéria2 až Kritéria127 – další podmínky, které rozšiřují možnosti. Jedná se o nepovinné

argumenty.

Příklad použití funkce SUMIFS:

Za úkol bychom měli sečíst ceny vozů (Obr. 42), které jsou značky Ford černé barvy. Vzorec bude

následující:

=SUMIFS(D2:D315;A2:A315;"Ford";E2:E315;"černá")

AVERAGEIF

Účel funkce AVERAGEIF:

• Funkce AVERAGEIF dokáže vypočítat průměr na základě kritéria.

Syntaxe funkce AVERAGEIF:

=AVERAGEIF(oblast;kritérium;[oblast_pro_průměr])

Popis jednotlivých argumentů funkce AVERAGEIF:


• Kritérium – podmínka, která upřesní, jaká data chceme průměrovat (z těch, která jsme

označili jako oblast).

• Oblast_pro_průměr – jedná se o nepovinný argument, který je využit v momentě,

kdy průměrovaná oblast není stejná s oblastí, na kterou se vztahuje kritérium.

Příklad použití funkce AVERAGEIF:

Budeme chtít v tabulce autobazaru (Obr. 42) vypočítat průměrnou cenu vozů, které mají najeto méně

než 100 000 km. Vzorec pak bude následující:

=AVERAGEIF(C2:C315;">100000";D2:D315)


AVERAGEIFS

Účel funkce AVERAGEIFS:

• Funkce AVERAGEIFS rozšiřuje funkci AVERAGEIF o možnost využití až 127 podmínek.

Syntaxe funkce AVERAGEIFS:

=AVERAGEIFS(oblast_pro_průměr;oblast_kritérií1;kritéria1;

[oblast_kritérií2;kritéria2];...)

Popis jednotlivých argumentů funkce AVERAGEIFS:

• Oblast_pro_průměr – oblast, která zahrnuje hodnoty, které budou v konečném důsledku

průměrovány.

• Oblast_kritérií1 – oblast buněk, na které se bude stahovat výpočetní kritérium.

• Kritéria1 – podmínka, která upřesní, jaká data chceme průměrovat (z těch, která jsme

označili jako oblast_kritérií1).

• Oblast_kritérií2 až Oblast_kritérií127 – další oblasti, které rozšiřují možnost kritérií. Jedná se

o nepovinné argumenty.

• Kritéria2 až Kritéria127 – další podmínky, které rozšiřují možnosti. Jedná se o nepovinné

argumenty.

Příklad použití funkce AVERAGEIFS:

V tabulce autobazaru (Obr. 42) budeme chtít vypočítat průměrnou cenu vozů, které jsou značky „Ford“

černé barvy. Vzorec bude následující:

=AVERAGEIFS(D2:D315;A2:A315;“Ford“;E2:E315;“černá“)

MINIFS

Účel funkce MINIFS:

• Funkce najde minimální hodnotu z buněk určených zadanými podmínkami (až 127). Funkce

MINIFS nemá dvojici v podobě funkce MINIF jako tomu bylo v případě předchozích

podmíněných funkcí.

• Tato funkce je dostupná až ve verzi Excelu 2016. V předchozích verzích ji nelze použít.

Syntaxe funkce MINIFS:

=MINIFS(oblast_minimum;oblast_kritérií1;kritéria1;


Popis jednotlivých argumentů funkce MINIFS:

• Argumenty odpovídají povaze předchozích funkcí SUMIFS či AVERAGEIFS s tím rozdílem,

že nesčítáme ani neprůměrujeme, ale hledáme minimální hodnotu.


Příklad použití funkce MINIFS:

V tabulce autobazaru (Obr. 42) budeme chtít najít nejlevnější vůz, který je značky „Ford“ černé barvy.


=MINIFS(D2:D315;A2:A315;"Ford";E2:E315;“černá“)

Tip:

Pokud bychom chtěli najít náhradu za funkci MINIFS v předchozích verzích Excelu, byla by jí následující

maticová konstrukce:

{=MIN(KDYŽ(A2:A315="Ford";KDYŽ(E2:E315="černá";D2:D315)))}

Protože je předchozí vzorec maticový, je třeba ho potvrdit trojkombinací kláves CTRL + SHIFT + ENTER.

Složené závorky (znaky „{“ a „}“) se tak doplní sami (viz kapitola o maticových vzorcích).

MAXIFS

Účel funkce MAXIFS:

• Funkce najde maximální hodnotu z buněk určených zadanými podmínkami (až 127). Funkce

MAXIFS nemá dvojici v podobě funkce MAXIF jako tomu bylo v případě předchozích

podmíněných funkcí.


Syntaxe funkce MAXIFS:

=MAXIFS(oblast_maximum;oblast_kritérií1;kritéria1;


Popis jednotlivých argumentů funkce MAXIFS:

• Argumenty odpovídají povaze předchozích funkcí SUMIFS či AVERAGEIFS s tím rozdílem,

že nesčítáme ani neprůměrujeme, ale hledáme maximální hodnotu.

Příklad použití funkce MAXIFS:

V tabulce autobazaru (Obr. 42) budeme chtít najít nejdražší vůz, který je značky Ford černé barvy.


=MAXIFS(D2:D315;A2:A315;“Ford“;E2:E315;“černá“)

Tip:

Stejně jako u funkce MINIFS, tak i zde se nabízí náhrada v podobě maticového vzorce, který bude

funkční pro dřívější verze Excelu:

{=MAX(KDYŽ(A2:A315="Ford";KDYŽ(E2:E315="černá";D2:D315)))}


COUNTBLANK

Účel funkce COUNTBLANK:

• Funkce COUNTBLANK spočítá, kolik buněk je v označené oblasti prázdných.

Syntaxe funkce COUNTBLANK:

=COUNTBLANK(oblast)

Popis jednotlivých argumentů funkce COUNTBLANK:

• Oblast – oblast buněk, ve které se mají počítat prázdné buňky.

Příklad použití funkce COUNTBLANK:

V tabulce odměn a postihů (Obr. 43) bychom chtěli spočítat, kolikrát se nic nestalo (tedy nebyla uznána

odměna, ani přiznán postih) – jedná se tak o počet prázdných buněk.

Obr. 43 Počítání prázdných polí


=COUNTBLANK(O3:Z10)

Tip:

Pokud umíte používat funkci COUNTIF a nechcete kvůli podobným zadáním s prázdnými buňkami

používat COUNTBLANK, je možné použít alternativní řešení:

=COUNTIF(O3:Z10;"")

Prázdné uvozovky říkají: „Počítej nic“ (tedy prázdné buňky).


MEDIAN

Účel funkce MEDIAN:

• Funkce MEDIAN dokáže spočítat střední hodnotu v oblasti číselných hodnot.

Syntaxe funkce MEDIAN:

=MEDIAN(číslo1;[číslo2];…)

Popis jednotlivých argumentů funkce MEDIAN:


(např. tržby).


až 255 čísel či oblastí, ve kterých chceme hledat medián.

Příklad použití funkce MEDIAN:

V tabulce platů (Obr. 40) budeme chtít najít medián. Vzorec bude následující:

=MEDIAN(C2:N60)

Pokud bude hodnot lichý počet, bude medián hodnota, která leží přesně uprostřed (hodnoty musí být

samozřejmě seřazeny vzestupně či sestupně).

Pokud budeme mít sudý počet hodnot, bude medián spočítaný jako aritmetický průměr prostředních

dvou hodnot.

V porovnání s funkcí PRŮMĚR je MEDIAN v případě platů vypovídající hodnota, neboť ho nerozhodí

mimořádné výkyvy extrémů (mimořádně nízké či vysoké platy – ty potom dosti ovlivňují průměrnou

hodnotu).


VYHLEDÁVACÍ FUNKCE

Vyhledávací funkce jsou takové funkce, které umí pomoci při hledání ve velkých tabulkách či například

automaticky vyplňovat formulář vycházející ze zdrojové databáze.

VYHLEDAT

Účel funkce VYHLEDAT:

• Funkci VYHLEDAT použijeme v případě, kdy chceme prohledat jeden řádek či sloupec a najít

hodnotu na stejné pozici ve druhém řádku nebo sloupci.

• Výhoda této funkce na rozdíl od následujících funkcí je, že se nemusí zdrojová oblast s cílovou

nacházet na stejných řádcích či ve sloupcích, protože se nejedná o jednu, ale o dvě nezávislé

oblasti. Proto mohou být vůči sobě posunuty nebo může být zdroj na jednom listu a cíl

na listu jiném.

• Nevýhodou této funkce vůči ostatním je, že musí být vždy data, podle kterých hledáme

výsledek, seřazena vzestupně (čísla) nebo podle abecedy od A do Z (text).

• Funkce nebere ohled na velikost písmen.

Syntaxe funkce VYHLEDAT:

=VYHLEDAT(co;hledat;[výsledek])

Popis jednotlivých argumentů funkce VYHLEDAT:

• Co – hodnota, kterou má funkce najít v prvním vektoru (sloupci či řádku).

• Hledat – oblast, ve které funkce najde hodnotu zadanou v prvním argumentu Co.

• Výsledek – oblast, ve které funkce najde výslednou hodnotu na základě nalezené pozici

v oblasti Hledat. Jedná se o nepovinný argument, protože funkce může být použita pouze

pro nalezení hodnoty v seznamu dat a pak postačí pouze první dva argumenty.

Příklad použití funkce VYHLEDAT:

Budeme chtít najít plat konkrétní osoby podle příjmení v dané tabulce (Obr. 44). Příjmení hledané

osoby budeme zadávat do buňky E2.


Obr. 44 Tabulka pro vyhledávání


=VYHLEDAT(E2;A2:A60;C2:C60)

Nevýhodou může být oblast jmen, která musí být seřazena dle abecedy od A po Z. To je požadavek,

který nelze vždy splnit.

SVYHLEDAT

Účel funkce SVYHLEDAT:

• Funkci SVYHLEDAT použijeme, když budeme chtít najít na základě nějaké položky jinou

položku, kterou nalezneme v jiném sloupci ve stejném řádku.

• V praxi se jedná o tabulky, které mají data strukturovaná ve sloupcích (např. jména

ve sloupci, platy ve sloupci – data jsou ve sloupcích – jedná se o 90 % tabulek).

• Písmeno S v názvu funkce SVYHLEDAT znamená svisle.

Syntaxe funkce SVYHLEDAT:

=SVYHLEDAT(hledat;tabulka;sloupec;[typ])

Popis jednotlivých argumentů funkce SVYHLEDAT:

• Hledat – jedná se o první údaj v závorce za klíčovým slovem funkce. Zamysleme se nad tím.

Kdybychom chtěli někde něco hledat, musíme nejprve vědět, co máme hledat. To samé platí

i u Excelu. Nejprve mu touto formou musíme sdělit, co chceme hledat, ať už je to prostá

adresa buňky nebo přímo další vnořená funkce. Měli bychom vědět, že tento parametr, který

chceme hledat, musí být v následující tabulce pouze jednou. Při více výskytech si funkce

vybere první z nich, což nám nemusí vždy vyhovovat.

• Tabulka – jedná se o parametr, který funkci řekne, v jaké oblasti má naši položku hledat.

Pozor!!! Do této oblasti musíme zahrnout nejen buňky, kde má Excel naši položku najít,

ale zároveň ještě sloupeček, ze kterého očekáváme odpověď.


• Sloupec – zde zadáváme číslo sloupce v naší vybrané tabulce, ze kterého očekáváme

odpověď neboli vyhledanou hodnotu či text. Pozor!!! Nejedná se o číslo sloupce od začátku

listu, ale od začátku námi vybrané tabulky dat v této funkci.

• Typ – tento parametr je uveden v hranaté závorce. Tzn., že je nepovinný. Funkce se bez něj

zkrátka obejde. Tento parametr může nabývat dvou hodnot a to logické 0 (NEPRAVDA)

a logické 1 (PRAVDA). Jestliže nezadáme ani log. 0 nebo log. 1 je to to samé, jako kdybychom

zadali log. 1.

o Log. 1 (PRAVDA) – jestliže dosadíme za argument „typ“ jedničku, funkci tím sdělíme,

že jsou data seřazena vzestupně a funkce bude vyhledávat přesnou nebo přibližnou

shodu našeho hledaného prvku. Pozor ale na vzestupné řazení hledaných hodnot.

Jakmile nebudou hledané položky vzestupně seřazené, nemusí funkce SVYHLEDAT

správně fungovat.

o Log. 0 (NEPRAVDA) – jestliže dosadíme za argument „typ“ nulu, funkci tím sdělíme,

že hledaná data nemusí být seřazena vzestupně a funkce se podle toho zachová

(projde všechna hledaná data). Jestliže nebude nalezena v tabulce přesná shoda,

vypíše chybovou hodnotu #N/A, žádnou přibližnou hodnotu již, jako možnost log. 1,

hledat nebude.

o Pokud neuvedeme ani jeden z těchto dvou typů, Excel použije log. 1 (PRAVDA)

a dojde tak k přibližnému hledání.

Příklad použití funkce SVYHLEDAT:

V tabulce jmen a platů (Obr. 44) budeme chtít najít plat dané osoby, jejíž jméno budeme zapisovat

do buňky E2. Vzorec bude následující:

=SVYHLEDAT(E2;A2:C60;3;NEPRAVDA)

Tabulka se jmény nemusí být seřazena dle abecedy. Třetí argument (číslice 3) uvádí pořadí sloupečku,

ve kterém leží výsledek, který chceme vyhledat.

Všimněte si, že v rámci hledání nijak nepotřebujeme křestní jméno, přesto jej funkce v druhém

argumentu obsahuje. Označená tabulka musí být totiž vždy bez přerušení (tedy v našem případě je

označená od prvního příjmení až po poslední plat).

Funkce SVYHLEDAT neumí hledat hodnoty nalevo od hledané hodnoty. Proto by v našem případě nešlo

vyhledat příjmení na základě platu. Našli bychom plat, ale příjmení je od něj nalevo – v těchto

případech nelze funkci SVYHLEDAT použít a řešení tohoto problému je nahrazeno dvojicí funkcí

POZVYHLEDAT a INDEX (viz dále).

VVYHLEDAT

Účel funkce VVYHLEDAT:

• Funkci VVYHLEDAT použijeme, když budeme chtít najít na základě nějaké položky, kterou

nalezneme v prvním řádku jinou položku, kterou nalezneme v jiném řádku ve stejném

sloupci.

• V praxi se jedná o tabulky, které mají data strukturovaná v řádcích (např. jména v řádku,

platy v řádku – data jsou v řádcích – opak funkce SVYHLEDAT).


• Písmeno V v názvu funkce VVYHLEDAT znamená vodorovně.

Syntaxe funkce VVYHLEDAT:

=VVYHLEDAT(hledat;tabulka;řádek;[typ])

Popis jednotlivých argumentů funkce VVYHLEDAT:

• Popis se shoduje s funkcí SVYHLEDAT s tím rozdílem, že zde neřešíme data ve sloupcích,

ale v řádcích.

Příklad použití funkce VVYHLEDAT:

V tabulce jmen a platů, která je tentokráte v horizontálním zobrazení (Obr. 45) budeme chtít najít plat

dané osoby, jejíž jméno budeme zapisovat do buňky A5.

Obr. 45 Horizontálně uspořádaná data


=VVYHLEDAT(A5;B1:Z3;3;NEPRAVDA)

Tabulka se jmény nemusí být seřazena dle abecedy. Třetí argument (číslice 3) uvádí pořadí řádku,

ve kterém leží výsledek, který chceme vyhledat.

POZVYHLEDAT

Účel funkce POZVYHLEDAT:

• Funkce POZVYHLEDAT umí vyhledat pozici (proto předpona POZ) hledané hodnoty v zadané

oblasti.

• Tato funkce se většinou nepoužívá samostatně, ale jako součást jiné funkce (jedná se pak

o funkci vnořenou).

Syntaxe funkce POZVYHLEDAT:

=POZVYHLEDAT(co;prohledat;[shoda])

Popis jednotlivých argumentů funkce POZVYHLEDAT:

• Co – jedná se o údaj, který chceme najít v oblasti, kterou budeme prohledávat ve druhém

argumentu.

• Prohledat – oblast, ve které chceme najít hodnotu zadanou v předchozím argumentu. Oblast

musí být vždy jednorozměrná (označíme tedy buď jeden řádek, nebo jeden sloupec,

ve kterém se má hodnota najít).

• Shoda – nepovinný argument, který může nabývat tří hodnot:

o 0 – přesné hledání, funkce najde přesnou shodu a data přitom nemusí být nijak

seřazeny.


o 1 – přibližné hledání, funkce najde nejvyšší hodnotu, která je menší nebo rovna

hledané hodnotě, data navíc musí být seřazeny vzestupně.

o -1 – přibližné hledání, funkce najde nejmenší hodnotu, která je větší nebo rovna

hledané hodnotě, data navíc musí být seřazeny sestupně.

o Pokud neuvedeme ani jednu z těchto tří hodnot, bude automaticky použita hodnota

1 – přibližné hledání (menší než nebo rovno hledané hodnotě).

Příklad použití funkce POZVYHLEDAT:

V tabulce platů a jmen (Obr. 44) budeme chtít vyhledat pozici paní Novákové:

=POZVYHLEDAT("Nováková";A2:A60;0)

Funkce vyhledá pozici paní Novákové. Jako poslední argument byla zadána hodnota 0, kterou si

vyžadujeme přesné hledání v tabulce. Pokud by v tabulce paní Nováková nefigurovala, funkce by

skončila chybovou hodnotou #N/A (#NENÍ_K_DISPOZICI). V případě, že bychom poslední argument

úplně vynechali, hledala by funkce přibližnou hodnotu – pokud by paní Nováková v tabulce nebyla,

přesto by funkce vrátila její pozici (pozici přibližnou – dohledalo by se jiné příjmení dle přibližné shody

podle abecedy).

INDEX

Účel funkce INDEX:

• Funkce INDEX lze použít dvěma způsoby. První způsob umí vyhledávat data pouze v jedné

tabulce, druhý způsob potom ve více tabulkách.

• Hlavní význam funkce spočívá v tom, že umí vyhledat v zadané oblasti několikátou hodnotu,

která leží na určitém řádku, případně i v určitém sloupci. Dá se tedy použít také pro křížové

vyhledávání.

Syntaxe funkce INDEX (pro oba způsoby):

=INDEX(pole;řádek;[sloupec])

INDEX(pole;řádek;[sloupec];[oblast])

Popis jednotlivých argumentů funkce INDEX:

• Pole – jedná se o oblast jednoho řádku, jednoho sloupce nebo oblast několika řádků

a sloupců, ve které je hodnota, kterou chceme vyhledat.

• Řádek – jedná se o číslo řádku, na kterém leží v označené oblasti hodnota, kterou hledáme.

• Sloupec – jedná se o číslo sloupce, ve kterém leží v označené oblasti hodnota, kterou

hledáme. Sloupec je nepovinný argument (v případě, že máme jako pole označený pouze

jeden sloupec, není třeba číslo sloupce uvádět – bylo by zbytečné – jednalo by se o sloupce

č. 1).

• Oblast – pokud zadáme v rámci prvního argumentu více oblastí (více polí), potom v rámci

tohoto argumentu zvolíme, v kolikátém poli chceme hledat výsledek.


Příklad použití funkce INDEX:

Za úkol máme v tabulce platů vyhledat plat paní Čápové. Tabulka je však na rozdíl od předchozích

tabulek strukturovaná tak, že sloupec s platy leží před sloupcem příjmení (Obr. 46). Proto není možné

použít funkci SVYHLEDAT – to neumí hledat zprava doleva. Proto přichází na řadu funkce INDEX.

Obr. 46 Tabulka s hledáním doleva

Funkce INDEX označí jako pole všech možných výsledků sloupec platů (sloupec H) a vyhledat chceme

takový plat, který leží na stejném řádku, jako příjmení paní Čápové. V tuto chvíli můžeme využít znalosti

z předchozí kapitoly o funkci POZVYHLEDAT, která tuto pozici umí bez problémů najít. Stane se tak

funkcí vnořenou, bez které by funkce INDEX nemohla tento příklad vyřešit. Vzorec bude následující:

=INDEX(H2:H60;POZVYHLEDAT("Čápová";I2:I60;0))

Funkce POZVYHLEDAT tedy vypočítá, kolikátá je paní Čápová a tato hodnota bude použita ve funkci

INDEX jako číslo řádku, na kterém leží plat, který chceme vyhledat v oblasti, kterou jsme označili hned

na začátku definice funkce INDEX.


Nyní si ukážeme křížovou tabulku (tabulka, která má záhlaví v řádku i ve sloupci – Obr. 47). Do buňky

D9 budeme zadávat jméno prodejce, do buňky D10 jeden z dvanácti měsíců v roce a v buňce D12

bychom chtěli vidět výsledek, který bude vyhledaný v tabulce výše na průsečíku řádku se zadaným

jménem prodejce a sloupce se zadaným měsícem.

Budeme potřebovat opět funkci POZVYHLEDAT jako vnořenou pomocnou funkci, která dokáže

vypočítat pozici řádku, na které se nachází jméno, které hledáme pozici sloupce, ve kterém je měsíc,

který hledáme. Tyto dva mezivýsledky spojíme ve funkci INDEX a dojdeme k řešení. Jedná se pouze

o jednu z několika možných cest.


Obr. 47 Křížová tabulka

Vzorec pro výpočet buňky D12 bude následující:

=INDEX(B2:M7;POZVYHLEDAT(D9;A2:A7;0);POZVYHLEDAT(D10;B1:M1;0))

Nyní jsme tedy ve funkci INDEX využili jak indexu řádku, tak sloupce, neboť se jedná o křížovou tabulku.

Dojít ke stejnému výsledku bychom mohli rovněž zápisem následujících vzorců:

=SVYHLEDAT(D9;A2:M7;POZVYHLEDAT(D10;A1:M1;0);NEPRAVDA)

=VVYHLEDAT(D10;B1:M7;POZVYHLEDAT(D9;A1:A7;0);NEPRAVDA)


Další příklad obsahuje dvě provizní tabulky (Obr. 48).

Ukážeme si zjednodušený vzorec, který by uměl vyhledat konkrétní provizi na základě zadání

jednotlivých argumentů. V praxi bychom předpokládali, že argumenty řádek, sloupec a oblast budou

počítány jinou vnořenou funkcí. Pro přehlednost je náš vzorec doplněn pouze konstantními

hodnotami:

=INDEX((G4:H10;J4:K9);4;2;1)

Vzorec obsahuje dvě oblasti, které jsou zvlášť uzavřené v závorce. Ve vzorci je definované, že chceme

vyhledat hodnotu, která leží na 4. řádku, ve 2. sloupci v oblasti číslo 1 (tedy první zadané).


Obr. 48 Provizní tabulky


Poslední příklad bude maticový a to znamená, že více komplikovaný než předchozí příklady. V tabulce

platů (Obr. 49) bychom chtěli vyhledat plat podle příjmení i jména zároveň. Příjmení budeme zadávat

do buňky C64 a jméno do buňky D64.

Obr. 49 Maticové hledání

Řešení v buňce C66 bude vypadat následovně:

{=INDEX(C2:C60;POZVYHLEDAT(C64&D64;A2:A60&B2:B60;0))}


Ve vzorci je použitý znak „&“, který spojuje více hodnot do jednoho argumentu. Nejprve spojujeme

buňku se vstupem pro příjmení s buňkou se vstupem pro jméno a v dalším argumentu potom musíme

ve stejném pořadí spojit celé sloupce těchto hodnot.

Co kdybychom chtěli vzorec ještě více vylepšit. Pokud bychom v našem zadávacím poli zadali příjmení

i jméno, choval by se vzorec jako doposud. Pokud bychom ale jméno nezadali (buňka D64 by zůstala

prázdná), chtěli bychom, aby vzorec vyhledal plat pouze podle příjmení. Vzorec by vypadal následovně:

{=KDYŽ(JE.PRÁZDNÉ(D64);SVYHLEDAT(C64;A2:C60;3;NEPRAVDA);

INDEX(C2:C60;POZVYHLEDAT(C64&D64;A2:A60&B2:B60;0)))}

Mohli bychom to také pojmout jiným řešením. Úvaha by byla taková, že pokud v hledání dvojice

příjmení a jméno nenajdeme nikoho a vzorec by skončil chybou, pokusil by se vzorec ještě najít plat

jen podle příjmení. V takovém případě bychom mohli využít funkci IFERROR, o které bude zmínka

v kapitole o logických funkcích. Řešení by bylo následující:

{=IFERROR(INDEX(C2:C60;POZVYHLEDAT(C64&D64;A2:A60&B2:B60;0));

SVYHLEDAT(C64;A2:C60;3;NEPRAVDA))}

Červeně je vyznačena vnořená funkce, která bude počítat v momentě, kdy nedojde k chybě (tedy bude

nalezena dvojice příjmení a jména). Modře je vyznačena vnořená funkce, která bude hledat plat pouze

podle příjmení (to když nebude nalezena dvojice zadaného příjmení a jména nebo když bude buňka

D64 pro zadávání jména prázdná).

NEPŘÍMÝ.ODKAZ

Účel funkce NEPŘÍMÝ.ODKAZ:

• Funkci lze použít, pokud se chceme odkázat do konkrétní oblasti tzv. nepřímo – tedy

nejčastěji přes jinou buňku, ve které leží název dané oblasti.

Syntaxe funkce NEPŘÍMÝ.ODKAZ:

=NEPŘÍMÝ.ODKAZ(odkaz;[a1])

Popis jednotlivých argumentů funkce NEPŘÍMÝ.ODKAZ:

• Odkaz – jedná se o odkaz na buňku, která obsahuje vzorec či definovaný název, přes který se

chceme odkázat do nějaké oblasti.

• A1 – jedná se o nepovinný argument, který upřesňuje, o jaký styl notace Excelu se jedná:

o PRAVDA – styl notace A1

o NEPRAVDA – styl notace R1C1

o Pokud tento argument přeskočíme a nezadáme ho, je to stejné, jako kdybychom

zadali PRAVDA.

Příklad použití funkce NEPŘÍMÝ.ODKAZ:

Budeme mít k dispozici křížovou tabulku (Obr. 47) z předchozích kapitol. Náš úkol bude dokonce stejný,

jako v předchozích případech – tedy najít, jaká hodnota leží na průsečíku zadaného jména do buňky D9

a měsíce do buňky D10.


Funkce NEPŘÍMÝ.ODKAZ předpokládá, že pracujeme s názvy, které jsme si předem definovali jako

oblasti (viz kapitola Definování názvů).

Vzorec v buňce D12 bude následující:

=NEPŘÍMÝ.ODKAZ(D9) NEPŘÍMÝ.ODKAZ(D10)

V tomto vzorci je skutečně mezera. Mezera ve vzorci znamená průnik. V tomto případě hledáme průnik

(to, co se leží na průsečíku) oblastí, jejichž názvy jsou zadané v buňkách D9 (jméno) a D10 (měsíc).

Výsledek bude stejný jako další ze tří postupů, které jsme si v této publikaci představili.

Nevýhodou však je, že musíme mít nadefinované názvy. Výhodou je zase naopak jednoduchý zápis

funkce oproti vnořeným vyhledávacím funkcím.


LOGICKÉ FUNKCE

Obor logických funkcí v Excelu nám pomáhá tvořit vzorce s podmínkami – takové vzorce, které se před

vykonáním nějaké operace rozhodnou, zda je splněna zadaná podmínka či ne a na základě toho

provedou jednu či druhou operaci.

KDYŽ

Účel funkce KDYŽ:

• Funkce KDYŽ je nejdůležitější logickou funkcí v Excelu, která se dokáže rozhodnout – čili

na základě podmínky rozdělit program na dvě části (co se má stát, když podmínka bude

splněná a co se má stát v případě, že nikoliv).

Syntaxe funkce KDYŽ:

=KDYŽ(podmínka;[ano];[ne])

Popis jednotlivých argumentů funkce KDYŽ:

• Podmínka – jedná se o podmínku, která je důležitá pro výsledek funkce – na základě

vyhodnocení této podmínky se přistoupí buď k argumentu Ano (v případě, že je podmínka

splněna) či Ne (v případě, že podmínka splněna není).

• Ano – pokud podmínka platí, provede se operace, která je zapsána do tohoto argumentu.

• Ne – pokud podmínka neplatí, provede se operace, která je zapsána do tohoto argumentu.

Argumenty Ano a Ne jsou nepovinné. Pokud je nezadáme, funkce bude namísto hodnot uvedených

v těchto argumentech odpovídat na vyhodnocení podmínky stavy PRAVDA, NEPRAVDA.

Příklad použití funkce KDYŽ:

V tabulce příjmů a výdajů (Obr. 50) chceme do sloupce „Výsledek“ zobrazit hodnotu „Zisk“ v případě,

že příjmy převýšili výdaje a v opačném případě chceme zobrazit hodnotu „Ztráta“.

Vzorec v buňce D2 (za rok 2000) bude následující:

=KDYŽ(B2>C2;"Zisk";"Ztráta")

Pokud tedy hodnota v buňce B2 převýší hodnotu v buňce C2, zapíše se hodnota „Zisk“, v opačném

případě „Ztráta“. Všimněme si, že není třeba zadávat dvě podmínky (jednu pro zisk a druhou

pro ztrátu). Pokud podmínka neplatí, provede se to, co je uvedeno jako poslední argument.


Obr. 50 Zisk a ztráta


Druhým příkladem bude jednoduché dělení. Jenže se na určitých řádcích stává, že dochází k chybě díky

tomu, že dělíme nulou, což je chybná matematická operace (Obr. 51).

Obr. 51 Dělení nulou


Vzorec, který bude kontrolovat, zda se nenachází ve sloupci C prázdná buňka (nebo nula = to samé),

bude vypadat následovně:

=KDYŽ(C2<>0;B2/C2;"Vyplň pole Ks")

Výsledek bude vypadat takto:

Obr. 52 Ošetření dělení nulou


Třetí příklad bude kombinovat funkci KDYŽ s vyhledávací funkcí SVYHLEDAT. Zadání bude vyhledat

provizi (do sloupce D) u obchodníků, kteří za daný měsíc prodali zboží v určité hodnotě (sloupec C).

Obchodníky rozdělíme do dvou skupin – ty, co jsou u společnosti méně než 3 roky a ty, co jsou

u společnosti déle než 4 roky. Každá z těchto skupin má jiné provizní podmínky (Obr. 53).

Obr. 53 Provize u obchodníků

Příklad lze řešit s použitím funkcí KDYŽ a SVYHLEDAT dvojím způsobem. Závisí jen na tom, kterou funkci

zvolíme jako hlavní (začneme s ní) a kterou jako vnořenou. Všechny vzorce níže napsané budou hledat

provizi do buňky D3 k prvnímu obchodníkovi (pan Benson).


1. Zvolíme jako hlavní funkci KDYŽ a vnořenou SVYHLEDAT:

=KDYŽ(B2<=3;SVYHLEDAT(C3;G4:H10;2);SVYHLEDAT(C3;J4:K9;2))

Všimněme si, že jsme potřebovali dvě vnořené funkce SVYHLEDAT. Jednu pro hledání v první tabulce

provizí a druhou pro hledání provizí v té druhé. Ani v jedné z funkcí SVYHLEDAT jsme nezadávali

poslední nepovinný argument typ, neboť chceme hledat provize tzv. přibližně (mohli bychom tedy

zadat poslední argument jako log. 1 nebo PRAVDA, ale je to stejné, jako když argument zcela

vynecháme).

2. Zvolíme jako hlavní funkci SVYHLEDAT a vnořenou KDYŽ:

=SVYHLEDAT(C3;KDYŽ(B2<=3;G4:H10;J4:K9);2)

Z tohoto příkladu je zřejmé, že velikost a obtížnost vzorců závisí také na jejich logickém uspořádání

(jaká funkce dostane přednost před jinou. Efektivnější je tedy postup číslo 2.

Obě tabulky v obou vzorcích by bylo třeba v praxi zafixovat (znakem „$“), abychom mohli vzorec

zkopírovat do ostatních řádků.

A

Účel funkce A:

• Funkce A vyhodnocuje, zda jsou všechny zadané podmínky splněny (zda jsou pravdivé).

Pokud jsou, výsledek takové funkce je PRAVDA. Stačí, aby nebyla jedna ze zadaných

podmínek splněná a výsledkem je logický stav NEPRAVDA.

• Tato funkce není ve většině případů používána samostatně, ale jako pomocná funkce

(vnořená) ve funkci KDYŽ, která běžně disponuje možností pouze jedné podmínky v jedné

funkci. Funkce A tak umožní zadat až 255 podmínek.

Syntaxe funkce A:

=A(logická1;[logická2];…)

Popis jednotlivých argumentů funkce A:

• Logická1 – podmínka, která je vyhodnocena jako PRAVDA nebo NEPRAVDA (platí, neplatí).

• Logická2 až Logická255 – nepovinné argumenty, které umožní vyhodnotit najednou

pravdivost až 255 podmínek, které musí být současně splněny.

Příklad použití funkce A:

V tabulce obchodníků (Obr. 54) budeme chtít zjistit, komu dáme odměnu a komu ne. V případě, že je

obchodník u společnosti více než 1 rok a zároveň splnil prodeje s hodnotou vyšší nebo rovnou

200 000 Kč, získává odměnu. V opačném případě ne. Obě podmínky tedy musí být splněny.

Vzorec v buňce D3 bude mít následující podobu:

=KDYŽ(A(B3>1;C3>=200000);"Ano";"Ne")


Obr. 54 Podmínka s funkcí A

NEBO

Účel funkce NEBO:

• Funkce NEBO vyhodnocuje, zda je alespoň jedna ze zadaných podmínek splněna (zda je

pravdivá). Pokud je, výsledek takové funkce je PRAVDA. Pokud není ani jedna z uvedených

podmínek splněna, je výsledkem logický stav NEPRAVDA.

• Tato funkce není ve většině případů používána samostatně stejně jako funkce A, ale používá

se jako pomocná funkce (vnořená) ve funkci KDYŽ, která běžně disponuje možností pouze

jedné podmínky v jedné funkci. Funkce NEBO tak umožní zadat až 255 podmínek.

Syntaxe funkce NEBO:

=NEBO(logická1;[logická2];…)

Popis jednotlivých argumentů funkce NEBO:

• Logická1 – podmínka, která je vyhodnocena jako PRAVDA nebo NEPRAVDA (platí, neplatí).

• Logická2 až Logická255 – nepovinné argumenty, které umožní vyhodnotit najednou

pravdivost až 255 podmínek. Stačí, aby byla jednu z až 255 podmínek splněna a celý vzorec je

vyhodnocen jako PRAVDA.

Příklad použití funkce NEBO:

V tabulce obchodníků (Obr. 55) budeme chtít zjistit, komu dáme odměnu a komu ne. V případě, že je

obchodník u společnosti více než 1 rok nebo splnil prodeje s hodnotou vyšší nebo rovnou 200 000 Kč,

získává odměnu. V opačném případě ne. Stačí tedy, aby pro dosažení odměny splnil jeden nebo druhý

požadavek (či oba zároveň).

Vzorec v buňce D3 bude mít následující podobu:

=KDYŽ(NEBO(B3>1;C3>=200000);"Ano";"Ne")


Obr. 55 Podmínka s funkcí NEBO

IFERROR

Účel funkce IFERROR:

• Funkce IFERROR umí rozvětvit program na dvě části v případě, že dojde ve výpočtu vzorce

k chybě. Nutné je podotknout, že se počítá jakákoliv chyba (může se jednat o dělení nulou,

o nenalezení správné hodnoty, o chybný název ve vzorci, …).


Syntaxe funkce IFERROR:

=IFERROR(hodnota;hodnota_v_případě_chyby)

Popis jednotlivých argumentů funkce IFERROR:

• Hodnota – hodnota či vzorec, který bude proveden v momentě, kdy nebude jeho výpočet

vykazovat chybu.

• Hodnota_v_případě_chyby – hodnota či vzorec, který bude proveden v případě chyby

předchozího argumentu.

Příklad použití funkce IFERROR:

Mohli bychom opět zopakovat příklad, ve kterém jsme ukazovali dělení nulou a jeho ošetření (Obr. 51).

Nyní použijeme pro řešení vzorec s funkcí IFERROR:

=IFERROR(B2/C2;"Vyplň pole Ks")

Další typický příklad na funkci IFERROR byl použit v kapitole vyhledávacích funkcí u funkce INDEX.


Tip:

Pokud používáte starší verzi Excelu než 2007, můžete obejít použití funkce IFERROR alternativním

řešením, a to použitím funkcí KDYŽ a JE.CHYBHODN (tato funkce bude probrána v kapitole informační

funkce). Zápis alternativního vzorce k příkladu dělení nulou by byl následující:

=KDYŽ(JE.CHYBHODN(B2/C2);"Vyplň pole Ks";B2/C2)

IFNA

Účel funkce IFNA:

• Funkce IFNA je funkcí, která reaguje na rozdíl od funkce IFERROR pouze na chybu #N/A

(#NENÍ_K_DISPOZICI). Použijeme jí tedy, pokud chceme ve vzorci reagovat pouze na tyto

chyby.


Syntaxe funkce IFNA:

=IFNA(hodnota;hodnota_pokud_na)

Popis jednotlivých argumentů funkce IFNA:

• Hodnota – hodnota či vzorec, který bude proveden v momentě, kdy nebude jeho výpočet

vykazovat chybu #N/A (#NENÍ_K_DISPOZICI).

• Hodnota_pokud_na – hodnota či vzorec, který bude proveden v případě chyby #N/A

(#NENÍ_K_DISPOZICI) předchozího argumentu.

Příklad použití funkce IFNA:

Použijeme opět tabulku platů (Obr. 44) a budeme chtít hledat plat pana Nedomy. Ten v tabulce není,

a proto by funkce vrátila chybový stav #N/A (#NENÍ_K_DISPOZICI). Ošetřit bychom ho mohli několika

způsoby, ale nyní k tomu použijeme funkci IFNA, která je novinkou verze 2016 a slouží právě

k odchycení tohoto druhu chyb.

Vzorec by vypadal následovně:

=IFNA(SVYHLEDAT("Nedoma";A2:C60;3;NEPRAVDA);"Jméno tam není")

Tip:

V přechozích verzích Excelu by se tato funkce dala nahradit následujícím zápisem:

=KDYŽ(JE.NEDEF(SVYHLEDAT("Nedoma";A2:C60;3;NEPRAVDA));

"Jméno tam není";

SVYHLEDAT("Nedoma";A2:C60;3;NEPRAVDA))


IFS

Účel funkce IFS:

• Nejeden uživatel si někdy v minulosti posteskl, jak by se hodila funkce KDYŽ, která by uměla

pracovat jednoduše s více podmínkami pro různé stavy. Funkce IFS rozšiřuje možnosti použití

funkce KDYŽ, která umí pracovat s víceúrovňovým systémem podmínek jen za předpokladu,

že budeme používat funkci ve funkci (vnořenou funkci).

• Jednoduše s funkcí IFS sestavíme vzorec bez nutnosti vnoření jiné logické funkce, který

dokáže rozvětvit řešení na více než dva stavy.


Syntaxe funkce IFS:

=IFS(logický_test1;podmínka1;[logický_test2;podmínka2];…)

Popis jednotlivých argumentů funkce IFS:

• Logický_test1 – libovolná hodnota nebo výraz, který testujeme.

• Podmínka1 – hodnota nebo vzorec, který se provede v případě, že je první logický test

plněný.

• Logický_test2 až Logický_test127 – nepovinné argumenty pro možnost dalšího testování.

• Podmínka2 až Podmínka127 – nepovinné argumenty pro možnosti dalšího vyhodnocení

testů.

Příklad použití funkce IFS:

V naší tabulce obchodníků (Obr. 56) budeme chtít vyplácet odměny podle výše prodeje. Stanovíme

jednotlivé podmínky pro splnění konkrétní výše odměn:

• Do 100 000 Kč = odměna 1 000 Kč.

• Do 200 000 Kč = odměna 2 000 Kč.

• Do 300 000 Kč = odměna 3 000 Kč.

• Více než 300 000 Kč = odměna 5 000 Kč.

Obr. 56 Odměny obchodníků


Vzorec zapsaný do buňky D3 bude vypadat následovně:

=IFS(C3<=100000;1000;C3<=200000;2000;C3<=300000;3000;C3>300000;5000)

Tip:

V dřívějších verzích Excelu by šel tento příklad řešit vnořením několika funkcí KDYŽ do sebe:

=KDYŽ(C3<=100000;1000;KDYŽ(C3<=200000;2000;KDYŽ(C3<=300000;3000;

5000)))

V případě vnoření je třeba uzavřít tolik závorek, kolik funkcí bylo použito.

SWITCH

Účel funkce SWITCH:

• Funkce SWITCH porovnává jednu hodnotu se seznamem hodnot a vrací výsledek, který

odpovídá první shodné hodnotě. Pokud nenajde žádnou shodnou hodnotu, je možné vrátit

jinou volitelnou hodnotu.


Syntaxe funkce SWITCH:

=SWITCH(výraz;hodnota1;výsledek1;[výchozí_nebo_hodnota2;výsledek2],…)

Popis jednotlivých argumentů funkce SWITCH:

• Výraz – výraz, který se bude porovnávat se všemi následujícími hodnotami (Hodnota1

až Hodnota127).

• Hodnota1 – první hodnota, která se porovná se zadaným výrazem.

• Výsledek1 – výsledek zobrazený v případě, že je Hodnota1 shodná s výrazem.

• Výchozí_nebo_hodnota2 až Výchozí_nebo_hodnota127 – nepovinné argumenty – možnost

zadání až 127 hodnot k porovnání, případně zadání poslední hodnoty, která se zobrazí

v momentě, kdy neodpovídá porovnání žádná z uvedených hodnot.

• Výsledek2 až Výsledek126 – nepovinné argumenty – výsledky zobrazené v případě,

že konkrétní hodnota byla shodná s výrazem.

Příklad použití funkce SWITCH:

Našim obchodníkům (Obr. 57) budeme chtít v jejich začátcích vypomoci finanční podporou, aby se

v rámci nové pozice lépe adaptovali. Proto navrhneme následující finanční pásma podle doby jejich

působení u naší společnosti:

• 1 rok = podpora 5 000 Kč.

• 2 roky = podpora 3 000 Kč.

• 3 roky = podpora 1 000 Kč

• Více než 3 roky = žádná podpora.


Obr. 57 Ukázka funkce SWITCH

Buňka D3 bude obsahovat následující vzorec:

=SWITCH(B3;1;5000;2;3000;3;1000;0)

Určitě je možné nahradit tuto funkci několika jinými. Nicméně zápis této funkce je jedním z nejkratších.


INFORMAČNÍ FUNKCE

FUNKCE JE…

Velkou skupinou tvořící informační funkce jsou funkce začínající na JE. Jedná se o všechny funkce

začínající na slovo JE a pokračující za tečkou dalším názvem, který specifikuje danou otázku.

Účel funkcí JE…:

• Funkce začínající na „JE.“ mají za úkol informovat o stavu hodnoty v buňce na základě

několika možných otázek.

• Funkce vrací hodnotu PRAVDA nebo NEPRAVDA podle toho, zda buňka obsahuje

či neobsahuje danou informaci.

Syntaxe funkcí JE…:

• =JE...(hodnota)

Popis jednotlivých argumentů funkcí JE…:

• Hodnota – jedná se o argument, kterým se odkazujeme do buňky (např. A1).

Přehled funkcí JE…:

• JE.ČISLO – pokud je v buňce číslo, vrátí logickou hodnotu PRAVDA, jinak NEPRAVDA.

• JE.TEXT – pokud je v buňce text, vrátí logickou hodnotu PRAVDA, jinak NEPRAVDA.

• JE.CHYBA – pokud je v buňce chyba kromě chyby #N/A (#NENÍ_K_DISPOZICI), vrátí logickou

hodnotu PRAVDA, jinak NEPRAVDA.

• JE.CHYBHODN – pokud je v buňce jakákoliv chyba, vrátí logickou hodnotu PRAVDA, jinak

NEPRAVDA.

• JE.PRÁZDNÉ – pokud je buňka prázdná, vrátí logickou hodnotu PRAVDA, jinak NEPRAVDA.

• JE.LOGHODN – pokud je v buňce logická hodnota PRAVDA nebo NEPRAVDA, vrátí logickou


• JE.NEDEF – pokud je v buňce chybová hodnota #N/A (#NENÍ_K_DISPOZICI), vrátí logickou


• JE.NETEXT – pokud je buňka cokoliv kromě textu, vrátí logickou hodnotu PRAVDA, jinak

NEPRAVDA.

• JE.ODKAZ – pokud buňka obsahuje platný odkaz, vrátí logickou hodnotu PRAVDA, jinak

NEPRAVDA.

Příklady použití funkcí JE…:

Příklady již byly uvedeny v kombinaci s vyhledávacími a logickými funkcemi v předchozích kapitolách.


ISEVEN

Účel funkce ISEVEN:

• Funkce ISEVEN zjistí, zda je hodnota v buňce sudé číslo a pokud ano, vrátí logický stav

PRAVDA, v opačném případě NEPRAVDA.

Syntaxe funkce ISEVEN:

=ISEVEN(číslo)

Popis jednotlivých argumentů funkce ISEVEN:

• Číslo – odkaz do buňky, ve které chceme zjistit, zda obsahuje sudé číslo.

ISODD

Účel funkce ISODD:

• Funkce ISODD zjistí, zda je hodnota v buňce liché číslo a pokud ano, vrátí logický stav

PRAVDA, v opačném případě NEPRAVDA.

Syntaxe funkce ISODD:

=ISODD(číslo)

Popis jednotlivých argumentů funkce ISODD:

• Číslo – odkaz do buňky, ve které chceme zjistit, zda obsahuje liché číslo.

O.PROSTŘEDÍ

Účel funkce O.PROSTŘEDÍ:

• Funkce O.PROSTŘEDÍ informuje o stavu MS Excel, systému a dalších parametrech.

Syntaxe funkce O.PROSTŘEDÍ:

=O.PROSTŘEDÍ(typ)

Popis jednotlivých argumentů funkce O.PROSTŘEDÍ:

• Typ – argument, prostřednictvím kterého zadáme požadavek na zjištění konkrétní informace

o pracovním prostředí:

o „ADRESSÁŘ“ – aktuální adresář nebo složka.

o „POČETSOUB“ – počet aktivních listů v otevřených sešitech.

o „PŮVOD“ - vrátí absolutní odkaz na buňku v levém horním rohu viditelné části okna

podle aktuální pozice posunu kurzoru jako text s označením $A:. Tato hodnota

zajišťuje kompatibilitu s Lotusem1-2-3 verze 3.x.

o „OSVERZE“ – verze operačního systému.

o „PŘEPOČET“ – nastavený režim přepočítávání (Automatické nebo Ruční).

o „VYDÁNÍ“ – verze MS Excel.

o „SYSTÉM“ – název prostředí (Macintosh = „mac“; Windows = „pcdos“).


SHEET

Účel funkce SHEET:

• Funkce vrátí číslo listu, na který se odkazujeme.

Syntaxe funkce SHEET:

=SHEET([hodnota])

Popis jednotlivých argumentů funkce SHEET:

• Hodnota – jedná se o nepovinný argument odkazu do buňky v jakémkoliv listu. Pokud

argument nezadáme, vrátí funkce číslo listu, na kterém tuto funkci zapíšeme.

SHETTS

Účel funkce SHEETS:

• Funkce vrátí počet listů, na které se odkazujeme.

Syntaxe funkce SHEETS:

=SHEETS([odkaz])

Popis jednotlivých argumentů funkce SHEETS:

• Odkaz – jedná se o nepovinný argument odkazu do buňky v jakémkoliv listu či listech. Pokud

argument nezadáme, vrátí funkce číslo listu, které odpovídá na otázku, kolik listů obsahuje

aktuální sešit Excelu.

TYP

Účel funkce TYP:

• Funkce vrátí typ hodnoty. Tato funkce se používá, pokud chování některé funkce závisí

na typu hodnoty v určité buňce.

• Vrácená hodnota může nabývat 5 možností:

o 1 – číslo

o 2 – text

o 4 – logická hodnota

o 16 – chybová hodnota

o 64 – pole

Syntaxe funkce TYP:

=TYP(hodnota)

Popis jednotlivých argumentů funkce TYP:

• Hodnota – odkaz do buňky, ve které chceme zjišťovat typ dat.


CHYBA.TYP

Účel funkce CHYBA.TYP:

• Funkce vrátí číslo odpovídající jedné z chybových hodnot aplikace MS Excel nebo vrátí

chybovou hodnotu #N/A (#NENÍ_K_DISPOZICI), pokud žádná chyba neexistuje. Funkci

CHYBA.TYP lze použít ve funkci KDYŽ při hledání chyb. Místo chybových hodnot mohou být

vraceny textové řetězce, například zpráva.

• Vrácená hodnota může nabývat 9 možností:

o 1 – #NULL!

o 2 – #DIV/0!

o 3 – #HODNOTA!

o 4 – #REF!

o 5 – #NÁZEV?

o 6 – #NUM!

o 7 – #N/A (#NENÍ_K_DISPOZICI)

o 8 – #NAČÍTÁNÍ_DAT

o #N/A (#NENÍ_K_DISPOZICI) – cokoliv jiného, jen ne chyba.

Syntaxe funkce CHYBA.TYP:

=CHYBA.TYP(chyba)

Popis jednotlivých argumentů funkce CHYBA.TYP:

• Chyba – odkaz do buňky, ve které chceme zjišťovat číslo chyby.


FUNKCE PRO PRÁCI S DATEM A ČASEM

DNES

Účel funkce DNES:

• Funkce DNES vrací do buňky aktuální datum.

• Funkci tedy použijeme v případě, kdy požadujeme, abychom měli v buňce vždy aktuální

datum (např. ke dni tisku).

Syntaxe funkce DNES:

=DNES()

Popis jednotlivých argumentů funkce DNES:

• Funkce DNES je skutečně funkce bez argumentů. Někdy také označována jako funkce nultého

řádu (bez potřeby cokoliv zadávat). Funkce je totiž systémová – datum si zjišťuje ze systému

sama.

Příklad použití funkce DNES:

Do libovolné buňky zapíšeme vzorec:

=DNES()

NYNÍ

Účel funkce NYNÍ:

• Funkce NYNÍ vrací do buňky aktuální datum i čas

Syntaxe funkce NYNÍ:

=NYNÍ()

Popis jednotlivých argumentů funkce NYNÍ:

• Funkce NYNÍ je stejně jako funkce DNES bez argumentů.

Příklad použití funkce NYNÍ:

Do libovolné buňky zapíšeme vzorec:

=NYNÍ()

Tip:

Ne vždy se v buňce zobrazí i zmiňovaný čas. Vše je závislé na nastaveném formátu buňky. Pokud

bychom chtěli vidět datum i čas je třeba nastavit formát dd.mm.rrrr hh:mm:ss (tedy – den.měsíc.rok

hodina:minuta:sekunda) ve vlastním formátu buněk.


DENTÝDNE

Účel funkce DENTÝDNE:

• Funkce umí zjistit, o jaký den v týdnu se jedná na základě zadání data.

Syntaxe funkce DENTÝDNE:

=DENTÝDNE(pořadové;[typ])

Popis jednotlivých argumentů funkce DENTÝDNE:

• Pořadové – jedná se o argument, který požaduje odkaz na buňku, která obsahuje kalendářní

datum.

• Typ – jedná se o číslo určující typ návratové hodnoty. V praxi to znamená, že Excel umí

přizpůsobit návratové hodnoty například podle lokalizace. V Anglii, kde začíná týden nedělí,

mají pro tento den vyhrazenu hodnotu 1 a pro pondělí 2. Náš kalendář začíná v pondělí,

a proto chceme vidět pondělí jako 1 a ne jako 2. Excel nabízí několik typů:

o 1 – čísla 1 (neděle) až 7 (sobota)

o 2 – čísla 1 (pondělí) až 7 (neděle)

o 3 – čísla 0 (pondělí) až 6 (neděle)

o a další, které zde nebudeme prezentovat.

o Pokud chceme používat klasickou českou lokalizaci, využijeme jako druhý argument

zástupný typ 2. Pokud bychom tento nepovinný argument nezadali, stane se to samé,

jako kdybychom zadali 1 (tedy anglický kalendář).

Příklad použití funkce DENTÝDNE:

V databázi evidence prodejů (Obr. 58) budeme chtít zjišťovat z kalendářních dat (sloupec B) dny

v týdnu do sloupce C. Dny v týdnu by měly odpovídat české lokalizaci, a proto budeme využívat

nepovinný argument, který nastavíme na hodnotu 2.

Obr. 58 Evidence prodejů – sloupec Den v týdnu

Pro první záznam do buňky C5 zapíšeme vzorec:

=DENTÝDNE(B5;2)


Tip:

Pokud budete chtít zobrazit den v týdnu textově, není nic jednoduššího, než použít funkci

HODNOTA.NA.TEXT, kterou bychom mohli definovat takto:

=HODNOTA.NA.TEXT(B5;“dddd“)

Kde B5 je souřadnice, ve které se nachází datum a druhý argument je formát zobrazení – v našem

případě vypsání dne v týdnu textově celým slovem (např. pondělí). Tři „déčka“ by zobrazily den

ve zkratce (např. pondělí = po).

DEN, MĚSÍC, ROK

Účel funkce DEN, MĚSÍC, ROK:

• Funkce DEN umí vytknout z kalendářního data pouze část vyjadřující den a tu uložit jako číslo.

• Funkce MĚSÍC umí vytknout z kalendářního data pouze část vyjadřující měsíc a tu uložit jako

číslo.

• Funkce ROK umí vytknout z kalendářního data pouze část vyjadřující rok a tu uložit jako číslo.

Syntaxe funkce DEN, MĚSÍC, ROK:

=DEN(pořadové_číslo)

=MĚSÍC(pořadové_číslo)

=ROK(pořadové_číslo)

Popis jednotlivých argumentů funkce DEN, MĚSÍC, ROK:

• Pořadové_číslo – jedná se o argument, který požaduje odkaz na buňku, která obsahuje

kalendářní datum. Tento argument je stejný pro všechny tři funkce.

Příklad použití funkce DEN, MĚSÍC, ROK:

V tabulce evidence prodejů (Obr. 59) budeme chtít pracovat s daty v rámci kontingenční tabulky,

ve které budeme chtít zobrazit prodeje po desetiletích. Protože kontingenční tabulka neumí

seskupovat data po x letí, musíme nejprve připravit sloupeček, do kterého vytkneme ze všech

kalendářních dat ve sloupečku B pouze roky v podobě čísel. Čísla pak v kontingenční tabulce lze

seskupovat a provádět rozsahy.

Vzorec v buňce C5 bude následující:

=ROK(B5)


Obr. 59 Evidence prodejů - sloupec Rok

DATUM

Účel funkce DATUM:

• Funkce DATUM umí poskládat kalendářní datum ze tří nezávislých údajů (den, měsíc, rok),

které jsou v různých buňkách.

Syntaxe funkce DATUM:

=DATUM(rok;měsíc;den)

Popis jednotlivých argumentů funkce DATUM:

• Rok – odkaz do buňky, která obsahuje číslo, které zastupuje rok.

• Měsíc – odkaz do buňky, která obsahuje číslo, které zastupuje měsíc.

• Den – odkaz do buňky, která obsahuje číslo, které zastupuje den.

Příklad použití funkce DATUM:

Z databáze jsme získali tři sloupce (Obr. 60), které zastupují kalendářní datum. Den, měsíc i rok jsou

však zvlášť a my bychom chtěli spojit tyto tři hodnoty v řetězec kalendářního data v tečkové konvenci.


Obr. 60 Den, měsíc, rok a datum

Zapíšeme tedy do buňky D2 vzorec s funkcí DATUM:

=DATUM(C2;B2;A2)

SEKUNDA, MINUTA, HODINA

Účel funkce SEKUNDA, MINUTA, HODINA:

• Funkce SEKUNDA umí vytknout z času pouze část vyjadřující sekundu a tu uložit jako číslo.

• Funkce MINUTA umí vytknout z času pouze část vyjadřující minutu a tu uložit jako číslo.

• Funkce HODINA umí vytknout z času pouze část vyjadřující hodinu a tu uložit jako číslo.

Syntaxe funkce SEKUNDA, MINUTA, HODINA:

=SEKUNDA(pořadové_číslo)

=MINUTA(pořadové_číslo)

=HODINA(pořadové_číslo)

Popis jednotlivých argumentů funkce SEKUNDA, MINUTA, HODINA:

• Pořadové_číslo – jedná se o argument, který požaduje odkaz na buňku, která obsahuje čas.

Tento argument je stejný pro všechny tři funkce.


ČAS

Účel funkce ČAS:

• Funkce ČAS umí poskládat čas ze tří nezávislých údajů (hodina, minuta, sekunda), které jsou

v různých buňkách.

Syntaxe funkce ČAS:

=ČAS(hodina;minuta;sekunda)

Popis jednotlivých argumentů funkce ČAS:

• Hodina – odkaz do buňky, která obsahuje číslo, které zastupuje hodinu.

• Minuta – odkaz do buňky, která obsahuje číslo, které zastupuje minutu.

• Sekunda – odkaz do buňky, která obsahuje číslo, které zastupuje sekundu.

Příklad použití funkce ČAS:

Z databáze jsme získali tři sloupce (Obr. 61), které zastupují čas. Hodina, minuta a sekunda jsou však

zvlášť a my bychom chtěli spojit tyto tři hodnoty v čas s dvojtečkovou konvencí.

Obr. 61 Hodina, minuta, sekunda a čas

Zapíšeme tedy do buňky D2 vzorec s funkcí ČAS:

=ČAS(A2;B2;C2)


WEEKNUM

Účel funkce WEEKNUM:

• Funkce WEEKNUM zjistí ze zadaného kalendářního data číslo týdne v roce.

Syntaxe funkce WEEKNUM:

=WEEKNUM(pořad_číslo;[typ])

Popis jednotlivých argumentů funkce WEEKNUM:

• Stejné jako u funkce DENTÝDNE.

ISOWEEKNUM

Účel funkce ISOWEEKNUM:

• Funkce ISOWEEKNUM zjistí ze zadaného kalendářního data číslo týdne v roce dle ISO.

• Tato funkce je dostupná až od verze Excelu 2013. V předchozích verzích ji nelze použít.

Syntaxe funkce ISOWEEKNUM:

=ISOWEEKNUM(datum)

Popis jednotlivých argumentů funkce ISOWEEKNUM:

• Datum – datum, na základě kterého chceme zjistit číslo týdne v roce dle ISO.

Rozdíl výsledků s použitím funkce WEEKNUM a ISOWEEKNUM:

V následující ukázce je zobrazena tabulka (Obr. 62), která obsahuje dvě kalendářní data. V buňce A2 se

nachází datum 01. 01. 2016 (pátek) a v buňce A3 je datum 04. 01. 2016 (pondělí). Ve sloupcích B a C

jsou použity dvě výše zmíněné funkce pro výpočet čísla týdne v roce.

Správný výpočet (dle ISO) je ve sloupci C. Funkce WEEKNUM bere první týden v roce za ten, ve kterém

se objeví datum první den v roce.

Obr. 62 Číslo týdne v roce


Tip:

Pokud bychom chtěli použít ISO funkci i v dřívějších verzích Excelu, je zapotřebí složitého postupu

a logických vazeb.

=CELÁ.ČÁST((A2-SUMA(MOD(DATUM(ROK(A2-MOD(A2-2;7)+3);1;2);

{1E+99;7})*{1;-1})+5)/7)

Není možné obejít řešení tak, že od výsledku funkce WEEKNUM pouze odečteme jedničku, protože to

nebude vždy pravda. V momentě, kdy bude 1. 1. pondělí, dostaneme se do situace, kdy bude funkce

WEEKNUM i ISOWEEKNUM vykazovat stejnou hodnotu (číslo týdne 1).

EOMONTH

Účel funkce EOMONTH:

• Funkce EOMONTH umí vrátit datum, který zastupuje poslední den v měsíci, který vychází

ze zadaných argumentů.

Syntaxe funkce EOMONTH:

=EOMONTH(začátek;měsíce)

Popis jednotlivých argumentů funkce EOMONTH:

• Začátek – jedná se o kalendářní datum.

• Měsíce – počet měsíců, o které chceme provést posun oproti začátku zadanému

v předchozím argumentu.

Příklad použití funkce EOMONTH:

Za úkol budeme mít vypsat poslední datum v měsíci dubnu 2016.

Použijeme následující vzorec:

=EOMONTH(“1.4.2016“;0)

Vzorec bude obsahovat v rámci prvního argumentu jakýkoliv dubnový datum a druhý argument bude

obsahovat nulu, protože se nechceme ze zadaného měsíce nikam posouvat.


NETWORKDAYS

Účel funkce NETWORKDAY:

• Vrátí počet celých pracovních dní mezi dvěma zadanými daty.

Syntaxe funkce NETWORKDAY:

=NETWORKDAYS(začátek;konec;[svátky])

Popis jednotlivých argumentů funkce NETWORKDAY:

• Začátek – počáteční kalendářní datum.

• Konec – koncové kalendářní datum.

• Svátky – nepovinný argument, kterým můžeme specifikovat, které dny chceme přeskočit

(nebudou se tak počítat do rozsahu stejně jako soboty a neděle).

Příklad použití funkce NETWORKDAY:

V zadání je, že projekt musíme stihnout dokončit do 20. 12. 2016. Dnes je 14. 11. 2016. Kolik pracovních

dní na projekt máme, pokud víme, že je 17. 11. státní svátek a 12. – 14. 12. nemůžeme na projektu

pracovat? (Obr. 63)

Obr. 63 Projekt - počet pracovních dní

V buňce B10 je použit následující vzorec:

=NETWORKDAYS(B1;B2;B5:B8)


WORKDAY

Účel funkce WORKDAY:

• Vrátí datum konce projektu podle zadaného počtu dní jeho trvání.

Syntaxe funkce WORKDAY:

=WORKDAY(začátek;dny;[svátky])

Popis jednotlivých argumentů funkce WORKDAY:

• Začátek – počáteční kalendářní datum.

• Dny – počet dní potřebných na projekt.

• Svátky – nepovinný argument, kterým můžeme specifikovat, které dny chceme přeskočit

(nebudou se tak počítat do rozsahu stejně jako soboty a neděle).

Příklad použití funkce WORKDAY:

Obr. 64 Projekt - datum ukončení

V buňce B8 bude napsán tento vzorec:

=WORKDAY(B3;B1;B5:B6)


TEXTOVÉ FUNKCE

Textové funkce, jak již název napovídá, mají za úkol pracovat s textem po všech možných stránkách.

Excel umí údaj z jedné buňky rozdělit do více buněk nebo naopak údaje z více buněk spojit do buňky

jediné. Funkcí pro práci s textem najdeme v Excelu opět spoustu.

CONCATENATE

Účel funkce CONCATENATE:

• Spojí více textových řetězců do jednoho.

Syntaxe funkce CONCATENATE:

=CONCATENATE(text1;[text2];…)

Popis jednotlivých argumentů funkce CONCATENATE:

• Text1 – odkaz do buňky nebo vložená hodnota.

• Text2 až Text255 – nepovinné argumenty – možnost spojit až 255 hodnot do jedné.

Příklad použití funkce CONCATENATE:

V tabulce zaměstnanců potřebujeme spojit do jednoho textu jméno i příjmení, které je v současnosti

rozděleno do dvou buněk (Obr. 65).

Obr. 65 Spojení textových řetězců

Vzorec v buňce C2 bude následující:

=CONCATENATE(A2;" ";B2)


Tip:

Pokud se Vám nezamlouvá dlouhý název funkce CONCATENATE, je možné tuto funkci obejít použitím

znaku &, který má v Excelu funkci spojování řetězců. Zápis by vypadal následovně:

=A2&" "&B2

Od Excelu verze 2016 lze zároveň používat funkci CONCAT, která má kromě kratšího názvu i jednodušší

definici. Od verze Excelu 2016 se rovněž tuto funkci snaží Microsoft prosazovat jako náhradu funkce

CONCATENATE a samotnou funkci CONCATENATE přeřadil do funkcí, které jsou v Excelu pouze

pro zachování kompatibility s předchozími verzemi. Řešení našeho příkladu za pomoci funkce CONCAT

by bylo následující:

=CONCAT(A2;" ";B2)

Zápis " " ve všech vzorcích výše znamená, že chceme vkládat mezi příjmení a jméno mezeru.

Používání funkce CONCAT je jednodušší hlavně v případech, kdy spojujeme více textových polí

najednou, neboť nemusíme každé zvlášť označovat, ale můžeme označit celek jako pole. Následující

vzorec by spojil údaje z 10 buněk do jedné:

=CONCAT(A1:A10)

ČÁST

Účel funkce ČÁST:

• Funkce ČÁST umí vytknout z buňky pouze určitou část textového řetězce.

Syntaxe funkce ČÁST:

=ČÁST(text;start;počet_znaků)

Popis jednotlivých argumentů funkce ČÁST:

• Text – odkaz do buňky, která obsahuje textový řetězec.

• Start – místo, na kterém leží první znak, od kterého budeme chtít oddělovat (počítáno

od začátku řetězce – první znak má č. 1).

• Počet_znaků – počet znaků, které chceme od startovní pozice oddělit.

Příklad použití funkce ČÁST:

V tabulce zaměstnanců (Obr. 66) máme sloupeček „Č. op.“, který obsahuje data, která se skládají

s číselné a textové části. My bychom chtěli do buňky oddělit pouze textovou část, přičemž víme,

že číselná část je dlouhá vždy 6 znaků a za ní je mezera o délce 1 znak. Textová část je dlouhá 2 znaky.

Víme tedy vše podstatné.

Vzorec pro oddělení textové části do buňky H2 bude následující:

=ČÁST(G2;8;2)


Obr. 66 Použití funkce ČÁST

Příklad použití funkce ČÁST:

Nyní budeme pracovat s tabulkou celých jmen (Obr. 67), která nám byla zaslána jako export z databáze.

Když se na tabulku podíváme, obsahuje nesystematická data (obsahují mezery před jménem, někdy

za (nejsou vidět) a mezi jménem a příjmením je často více než jedna mezera.

Obr. 67 Export z databáze s mezerami

My si tedy data nejprve „očistíme“ – zbavíme přebytečných mezer. K tomu je dobrá textová funkce

s názvem PROČISTIT. Je jednoduchá na definici. Nejprve přidáme mezi sloupec „Celé jméno“ a „Jméno“

ještě jeden nový, do kterého budeme data přenášet bez mezer. V buňce B2 bude následující vzorec:

=PROČISTIT(A2)


Tento vzorec zkopírujeme dolů pro všechna jména a výsledek (Obr. 68) je perfektní (již žádné mezery

navíc).

Obr. 68 Výsledek bez přebytečných mezer

Nyní přejdeme k řešení problémy – tedy rozdělit řetězec na dvě části.

Do sloupce C chceme vložit pouze jména. Jméno začíná v buňce B2 na prvním znaku a končí jeden znak

před mezerou. Jenže jak najít mezeru? Pomocí funkce HLEDAT nebo NAJÍT. Liší se akorát tím, že funkce

HLEDAT nerozlišuje velikost písmen a funkce NAJÍT rozlišuje, ale v případě mezery nás tento fakt

netrápí – mezera není malá ani velká, je prostě jen jedna ☺. Vzorec v buňce C2 pro oddělení celého

jména bude vypadat následovně:

=ČÁST(B2;1;HLEDAT(“ “;B2)-1)

Funkce HLEDAT potřebuje znát k definici dvě věci – co hledat a kde to najít. Obojí jsme jí prozradili.

Odečítáme ještě jedničku, protože funkce HLEDAT najde pozici mezery a ta je vždy jeden znak

za posledním znakem jména.

Nyní potřebujeme oddělit příjmení. Příjmení začíná jeden znak za mezerou, ale stejně se opět setkáme

s jedním problémem. Jak zjistit délku příjmení? Na pomoc si zavoláme funkci DÉLKA. Ta umí zjistit, kolik

znaků celkem obsahuje naše buňka s celým jménem. Když od této hodnoty odečteme pozici znaku

mezery, získáme délku příjmení (Obr. 69).

Vzorec pro oddělení příjmení do buňky D2 bude vypadat následovně:

=ČÁST(B2;HLEDAT(“ “;B2)+1;DÉLKA(B2)-HLEDAT(“ “;B2))


Obr. 69 Rozbor celého jména pomocí funkcí

Samozřejmě lze výsledek vyřešit i jiným postupem. Kdo nepotřebuje vzorce, vystačí si s nástrojem Text

do sloupců na kartě Data.

Kdo má verzi 2016, může dokonce k výslednému rozdělení přijít tak jednoduše, že začne pouze

do buňky C2 zapisovat první jméno (Marie) a do buňky C3 zapíše náznak dalšího jména (Simona) – stačí

pouze první písmeno „S“ – Excel již pozná, že bychom takto opisovali nejspíše celý seznam jmen

a na jeden stisk klávesy Enter za nás doplní celý sloupec (). Stejně zafunguje i další sloupec pro příjmení.

Obr. 70 Automatické doplnění v Office 2016


HLEDAT, NAJÍT

Účel funkce HLEDAT a NAJÍT:

• Funkce umí najít pozici konkrétního znaku či znaků v buňce, na kterou se odkazujeme.

• Liší se akorát tím, že funkce HLEDAT nerozlišuje velikost písmen a funkce NAJÍT rozlišuje.

Syntaxe funkce HLEDAT a NAJÍT:

=HLEDAT(co;kde;[start])

=NAJÍT(co;kde;[start])

Popis jednotlivých argumentů funkce HLEDAT a NAJÍT:

• Co – znak nebo znaky, jejichž pozici chceme hledat.

• Kde – odkaz na buňku, ve které chceme znak hledat.

• Start – nepovinný argument, kterým můžeme posunout začátek prohledávání v buňce

na jinou pozici než 1 (od začátku).

NAHRADIT

Účel funkce NAHRADIT:

• Funkce NAHRADIT umožní nahradit starý znak v textovém řetězci za jiný.

Syntaxe funkce NAHRADIT:

=NAHRADIT(starý;start;znaky;nový)

Popis jednotlivých argumentů funkce NAHRADIT:

• Starý – odkaz na buňku, která obsahuje starý znak, který budeme nahrazovat.

• Start – pozice, na které se nachází znak, který budeme nahrazovat.

• Znaky – počet znaků od startovní pozice, které budeme nahrazovat.

• Nový – nový znak, kterým nahradíme znak starý.

Příklad použití funkce NAHRADIT:

V tabulce jmen (Obr. 71) budeme chtít nahradit znak středníku (znak „;“), který odděluje jméno

od příjmení za znak mezery.

Vzorec v buňce E2 bude vypadat následovně:

=NAHRADIT(D2;HLEDAT(";";D2);1;" ")

Argument pro zjištění startovní pozice jsme museli dopočítat pomocí již známé funkce HLEDAT, která

zjistí pozici středníku v textovém řetězci.


Obr. 71 Nahrazení středníků mezerami

NUMBERVALUE

Účel funkce NUMBERVALUE:

• Funkce NUMBERVALUE dokáže převést text na číslo ve správném formátu. Jedná se hlavně

o případy, ve kterým převádíme externí data do Excelu a po exportu s nimi nemůžeme

pracovat jako s čísly z různých důvodů (jiná lokalizace – záměna desetinné tečky za čárku

apod.)


Syntaxe funkce NUMBERVALUE:

=NUMBERVALUE(text;[oddělovač_desetin];[oddělovač_skupin])

Popis jednotlivých argumentů funkce NUMBERVALUE:

• Text – buňka obsahující hodnotu, kterou chceme převádět na číslo.

• Oddělovač_desetin – definujeme, jakým znakem je v původní buňce oddělena desetinná

část.

• Oddělovač_skupin – definujeme, jakým znakem je v původní buňce oddělena skupina tisíců.

Příklad použití funkce NUMBERVALUE:

Máme převést textové hodnoty (Obr. 72) na číselné s danými oddělovači (tečka odděluje desetinnou

složku a mezera odděluje skupinu tisíců).

Vzorec v buňce B2 bude následující:

=NUMBERVALUE(A2;".";" ")


Obr. 72 Použití funkce NUMBERVALUE

TEXTJOIN

Účel funkce TEXTJOIN:

• Funkce TEXJOIN vytvoří seznam textových řetězců s pomocí oddělovače.


Syntaxe funkce TEXTJOIN:

=TEXTJOIN(oddělovač;ignorovat_prázdné;text1;[text2];…)

Popis jednotlivých argumentů funkce TEXTJOIN:

• Oddělovač – jedná se o znak, kterým budou odděleny jednotlivé textové řetězce.

• Ignorovat_prázdné – volba PRAVDA (ignoruje prázdné buňky) nebo NEPRAVDA (zahrnuje

prázdné buňky).

• Text1 – pole textových řetězců.

• Text2 až Text252 – další pole řetězců – nepovinné argumenty.

Příklad použití funkce TEXTJOIN:

Máme databázi zaměstnanců (Obr. 73) a chceme jí připravit pro import do databázového systému,

který očekává jednotlivé pole oddělené za pomocí středníku.

Obr. 73 Databáze zaměstnanců

Zapíšeme následující vzorec (pro první záznam):

=TEXTJOIN(";";NEPRAVDA;A2:L2)

Výsledek vypadá následovně (Obr. 74):


Obr. 74 Výsledek funkce TEXTJOIN

ZLEVA, ZPRAVA

Účel funkce ZLEVA, ZPRAVA:

• Funkce ZLEVA umí oddělit z buňky určitý počet znaků zleva.

• Funkce ZPRAVA umí oddělit z buňky určitý počet znaků zprava.

Syntaxe funkce ZLEVA, ZPRAVA:

=ZLEVA(text;[znaky])

=ZPRAVA(text;[znaky])

Popis jednotlivých argumentů funkce ZLEVA, ZPRAVA:

• Text – odkaz na buňku, se kterou chceme pracovat.

• Znaky – počet znaků, které chceme oddělit zprava či zleva (dle funkce).


DATABÁZOVÉ FUNKCE

Databázové funkce mají za úkol spočítat výsledek nějaké operace na základě tabulky kritérií. Tabulka

kritérií obsahuje veškeré podmínky pro výpočet z databáze. Tyto podmínky pak pomáhají funkcím

začínajícím zpravidla na písmeno „D“ vypočítat správný výsledek.

FUNKCE ZAČÍNAJÍCÍ NA PÍSMENO D

Účel funkcí začínajících na písmeno D:

Tab. 2 Databázové funkce

Databázová funkce Význam

DSUMA Sčítá databázová data na základě kritéria

DPRŮMĚR Průměruje databázová data na základě kritéria

DMIN Hledá nejmenší hodnotu v databázových datech na základě kritéria

DMAX Hledá největší hodnotu v databázových datech na základě kritéria

DPOČET Počet číselných buněk v databázi na základě kritéria

DPOČET2 Počítá, kolik buněk je v databázi naplněných na základě kritéria

Syntaxe funkcí začínajících na písmeno D:

=DSUMA(databáze;pole;kritéria)

Výše je syntaxe funkce DSUMA. Ať se však jedná o jakoukoliv funkci s písmenem D na začátku,

je syntaxe naprosto stejná, proto nejsou další funkce rozepisovány.

Popis jednotlivých argumentů funkcí začínajících na písmeno D:

• Databáze – oblast celé tabulky (databáze) včetně záhlaví.

• Pole – sloupec, který budeme chtít sčítat (v případě funkce DSUMA). Je možné jej označit

číselně (př. 4) nebo vypsat jeho název do uvozovek (př. „Cena“).

• Kritéria – jedná se o naší nově vytvořenou pomocnou tabulku kritérií. Opět včetně záhlaví.

Příklad použití funkcí začínajících na písmeno D:

Budeme uvažovat databázi autobazaru, který obsahuje vozy různých značek, modelů, barev a obsahuje

informace o počtu najetých kilometrů a o cenách vozů (Obr. 75). Vedle této databázi si vytvoříme

tzv. tabulku kritérií – jinými slovy překopírujeme záhlaví z původní tabulky tak, aby vedle vznikla

tabulka jiná – v tomto případě pro podmínky neboli kritéria.

Obr. 75 Databáze autobazaru s přípravou tabulky kritérií


Nyní vyplníme tabulku kritérií určitými požadavky na výpočty. Například budeme požadovat:

• Vůz značky Škoda s cenou do 120 000 Kč.

NEBO

• Vůz značky Volkswagen, model Golf nebo Passat, který bude buď stříbrné nebo modré barvy

a s nájezdem nižším nebo rovným 150 000 km.

NEBO

• Vůz značky Ford s cenou do 170 000 Kč a nájezdem do 80 000 km.

NEBO

• Vůz jakékoliv značky s cenou do 100 000 Kč a nájezdem do 90 000 Km.

Zápis těchto požadavků bude v tabulce kritérií následující (Obr. 76):

Obr. 76 Tabulka kritérií

Vše, co má platit současně (logické A), musí být uvedeno na stejném řádku. Veškeré další možnosti

(tedy logické NEBO) jsou uvedeny na dalších řádcích pod sebou. Úplně stejná pravidla platí

i pro rozšířený filtr.

Nyní budeme chtít na základě těchto kritérií zjistit počet vozů, které odpovídají zadání, průměrnou

cenu takového vozu, nejlevnější a nejdražší vůz:

Počet vozů:

=DPOČET(A1:E315;4;G1:K8)

Průměrná cena vozu:

=DPRŮMĚR(A1:E315;4;G1:K8)

Nejlevnější vůz:

=DMIN(A1:E315;4;G1:K8)


Nejdražší vůz:

=DMAX(A1:E315;4;G1:K8)

Nyní tedy známe odpovědi na jednotlivé otázky:

• Počet vozů: 22.

• Průměrná cena vozu: 110 672 Kč.

• Nejlevnější vůz: 30 000 Kč.

• Nejdražší vůz: 347 758 Kč.

Ve všech funkcích jsou naprosto stejné argumenty. U průměru, nejlevnějšího a nejdražšího auta je

jasné, že musíme vycházet ze sloupce ceny (tedy čtvrtý). U prvního vzorce bychom mohli vycházet

z jakéhokoliv sloupce z pěti, protože jsou všechny informace o každém vozu evidovány. Kdybychom

vozy nepočítali podle cen, jako nyní, ale například podle názvu značky, museli bychom funkci DPOČET

vyměnit za funkci DPOČET2, která umí počítat i textová pole.

Kdybychom namísto počítání chtěli vozy odpovídající podmínkám zobrazit, museli bychom použít

rozšířený filtr – karta Data – skupina Seřadit a filtrovat – nástroj Upřesnit (Obr. 77).

Obr. 77 Data - Seřadit a filtrovat – Upřesnit

Zobrazí se dialogové okno, které vyplníme následovně (Obr. 78):

Obr. 78 Rozšířený filtr - dialogové okno

Po stisknutí tlačítka OK dojde k vyfiltrování vozů, které odpovídají našim podmínkám (Obr. 79).


Obr. 79 Vozy odpovídající kritériím


CHYBOVÁ HLÁŠENÍ

Při řešení vzorců a funkcí se Vám může stát, že uvidíte místo výsledku chybové hlášení. Úkolem této

kapitoly je seznámit Vás se všemi chybovými hlášeními a prozradit, co které znamená. Vše je

pro přehlednost vypracováno do následující tabulky (Tab. 3).

Sloupce A a B simulují dvě hodnoty, se kterými se pracuje podle vzorce, který je zobrazený ve sloupci

Vzorec. Po potvrzení takového vzorce vyskočí namísto výsledku chybové hlášení (sloupec Chyba).

Sloupec Popis pak popisuje, proč k chybě došlo.

Tab. 3 Chybová hlášení

A B Chyba Vzorec Popis

10 0 #DIV/0! =A/B Nelze dělit nulou nebo prázdnou

buňkou

10 ahoj #HODNOTA! =A+B Nelze počítat s textovou hodnotou

10 0 #NÁZEV? =MINIMUM(A;B) Chybný název funkce nebo

argumentu

-1 #NUM! =LN(A) Nedefinovaná operace v matematice

nebo příliš velké či malé číslo

20 10 #N/A =POZVYHLEDAT(1;A:B;0)

Nenalezení dat při vyhledávání nebo

vynechání povinného argumentu

funkce

10 20 #REF! =SVYHLEDAT(20;A;2;0)

Hledaný výsledek je mimo oblast

nebo jsme špatně zkopírovali

vzoreček rovněž s odkazem mimo

oblast

#NULL! Př. =A2:A4 B2:B4 Prázdná množina (průnik, který

neexistuje)

1000 2000 ####### =A*B Výsledek je delší, než je šířka sloupce


NEJČASTĚJŠÍ KLÁVESOVÉ ZKRATKY

ZÁKLADNÍ OPERACE SE SCHRÁNKOU

Ctrl + C Kopírovat Ctrl + V Vložit Ctrl + X Vyjmout

ZÁKLADNÍ KLÁVESOVÉ ZKRATKY PRO OZNAČOVÁNÍ A POHYB V TABULKÁCH

Ctrl + A Označí aktuální oblast (při

druhém stisku označí celý list) Ctrl + ← Posun v tabulce úplně vlevo Ctrl + Shift + ←

Označí řádek od aktuální

buňky až úplně vlevo

Ctrl + * Označí aktuální oblast Ctrl + ↑ Posun v tabulce úplně nahoru Ctrl + Shift + ↑ Označí sloupec

od aktuální buňky nahoru

Ctrl + . Posun mezi rohovými buňkami

v označené oblasti buněk Ctrl + → Posun v tabulce úplně doprava Ctrl + Shift + →

Označí řádek od aktuální

buňky až úplně doprava

F8 Aktivuje rozšířený výběr

(lze vybírat bez nutnosti myši). Ctrl + ↓ Posun v tabulce úplně dolů Ctrl + Shift + ↓

Označí sloupec

od aktuální buňky dolů

Shift +

mezerník Označí řádek

Ctrl +

mezerník Označí sloupec

Ctrl + Shift +

mezerník Označí celý list

KLÁVESOVÉ ZKRATKY VYUŽÍVANÉ PŘI PRÁCI S NÁSTROJI

Ctrl + F Najít Ctrl + O Otevřít Ctrl + B Tučné písmo

Ctrl + H Nahradit Ctrl + S Uložit Ctrl + I Kurzíva

Ctrl + Z Krok zpět Ctrl + G (F5) Přejít na Ctrl + U Podtržené písmo

Ctrl + P Tisknout Alt + F8 Zobrazit makra Shift + F11 Nový list

Ctrl + T Vložit tabulku Alt + F11 Spustit editor maker VBA Ctrl + N Nový sešit

KLÁVESOVÉ ZKRATKY VYUŽÍVANÉ PŘI PRÁCI SE VZORCI

F3 Použít název ve vzorci F4

Změna typu odkazu

(absolutní, relativní,

smíšený) - $

F9 Přepočítat celý sešit

Ctrl + F3 Správce názvů Ctrl + , Zobrazit vzorce Shift + F9 Přepočítat celý list

Shift + F3 Vložit funkci Alt + = Vloží funkci SUMA Ctrl + ; Vloží aktuální datum

Ctrl + Enter

Vložit do označených buněk

stejný obsah (zmnožený

vstup)

Alt + Enter Řádkovat v rámci jedné

buňky

Ctrl + Shift +

Enter

Potvrzení maticového

vzorce (kolem vzniknou

složené závorky { } )


SPECIÁLNÍ ZNAKY PŘI PRÁCI S MS EXCEL

AltGr + X # AltGr + C & AltGr + š ^

AltGr + , < AltGr + F [ AltGr + B {

AltGr + . > AltGr + G ] AltGr + N }

ZÁKLADNÍ TABULKOVÉ FUNKCE

MATEMATICKÉ

=SUMA(číslo1;[číslo2];…) Sečte všechna čísla v oblasti buněk.

=SOUČIN(číslo1;[číslo2];…) Vynásobí všechna čísla v oblasti buněk.

=SOUČIN.SKALÁRNÍ(pole1;[pole2];…) Vrátí součet součinů odpovídajících hodnot v jednotlivých oblastech.

=ODMOCNINA(číslo) Vrátí druhou odmocninu čísla.

=POWER(číslo;exponent) Umocní číslo na zadaný exponent (stejné jako ^ - např. 3^2 = tři na druhou = 9).

=ZAOKROUHLIT(číslo;číslice) Zaokrouhlí číslo na zadaný počet číslic (0 = celé číslo, 1 = jedno desetinné, -2 = na stovky).

=ROUNDUP(číslo;číslice) Zaokrouhlí číslo nahoru směrem od nuly.

=ROUNDDOWN(číslo;číslice) Zaokrouhlí číslo dolů směrem k nule.

=ZAOKR.NAHORU(číslo;významnost) Zaokrouhlí číslo nahoru na nejbližší násobek zadané hodnoty významnosti.

=ZAOKR.DOLŮ(číslo;významnost) Zaokrouhlí číslo dolů na nejbližší násobek zadané hodnoty významnosti.

=USEKNOUT(číslo;[desetiny]) Zkrátí číslo na celé číslo odstraněním desetinné nebo zlomkové části čísla.

=ZAOKROUHLIT.NA.LICHÉ(číslo) Zaokrouhlí kladné číslo nahoru a záporné číslo dolů na nejbližší liché celé číslo.

=ZAOKROUHLIT.NA.SUDÉ(číslo) Zaokrouhlí kladné číslo nahoru a záporné číslo dolů na nejbližší sudé celé číslo.

=SUBTOTAL(funkce;odkaz1;…) Na filtrovaná data v argumentu odkaz1 aplikuje funkci zadanou prvním argumentem funkce.

STATISTICKÉ

=POČET(hodnota1;[hodnota2];…) Vrátí počet buněk obsahujících čísla.

=POČET2(hodnota1;[hodnota2];…) Vrátí počet buněk, které nejsou prázdné (počítá čísla i text).

=MIN(číslo1;[číslo2];…) Vrátí minimální hodnotu z označených buněk.

=MAX(číslo1;[číslo2];…) Vrátí maximální hodnotu z označených buněk.

=PRŮMĚR(číslo1;[číslo2];…) Vrátí průměrnou hodnotu (aritmetický průměr) označené oblasti.

=COUNTIF(oblast;kritérium) Vrátí počet buněk v zadané oblasti, které splňují požadované kritérium.

=COUNTIFS(oblast_kritérií;kritérium;…) Určí počet buněk na základě dané sady podmínek nebo kritérií.

=SUMIF(oblast;kritéria;[součet]) Sečte buňky vybrané podle zadaných kritérií.

=SUMIFS(oblast_součtu;oblast_kritérií;kritérium;…) Sečte buňky určené zadanou sadou kritérií.

=AVERAGEIF(oblast;kritérium;oblast_pro_průměr) Zjistí průměrnou hodnotu (aritmetický průměr) buněk určených daným

kritériem.

=AVERAGEIFS(oblast_pro_průměr;oblast_kritérií;kritérium;…) Zjistí průměrnou hodnotu (aritmetický průměr) buněk určených sadou kritérií.

=COUNTBLANK(oblast) Vrátí počet prázdných buněk v zadané oblasti.


DATUM A ČAS

=DNES() Vrátí aktuální datum. Jedná se o funkci bez argumentů.

=NYNÍ() Vrátí aktuální datum a čas. Jedná se o funkci bez argumentů.

=DEN(pořadové_číslo) Vrátí den v měsíci z vybraného data (číslo od 1 do 31).

=MĚSÍC(pořadové_číslo) Vrátí číslo měsíce z vybraného data (od 1 – leden do 12 – prosinec).

=ROK(pořadové_číslo) Vrátí rok z vybraného data (od 1900 do 9999).

=DATUM(rok;měsíc;den) Sestaví do jedné buňky datum ze tří dílčích částí – rok, měsíc, den.

=HODINA(pořadové_číslo) Vrátí hodiny z vybraného času (od 0 do 23).

=MINUTA(pořadové_číslo) Vrátí minuty z vybraného času (od 0 do 59).

=SEKUNDA(pořadové_číslo) Vrátí sekundy z vybraného času (od 0 do 59).

=ČAS(hodina;minuta;sekunda) Sestaví do jedné buňky čas ze tří dílčích částí – hodina, minuta, sekunda.

=DENTÝDNE(pořadové;[typ])

Vrátí číslo 1 až 7, které určuje pořadí dne v týdnu. Typ určuje systém pořadí jednotlivých

dní v týdnu. Např. typ 1 vyjadřuje týden začínající nedělí, typ 2 týden od pondělí (český

týden), …

=WEEKNUM(pořad_číslo;[typ]) Vrátí číslo týdne v roce.

=ISOWEEKNUM(datum) Vrátí číslo týdne ISO v roce pro dané datum (správné číslo dle kalendáře).

=NETWORKDAYS(začátek;konec;[svátky]) Vrátí počet celých pracovních dnů mezi dvěma zadanými daty. Svátky – seznam

kalendářních dat, které má Excel v počítání přeskočit.

=NETWORKDAYS.INTL(začátek;konec;[víkend];

[svátky])

Oproti předchozí funkci umí navíc definovat, jaké dny má Excel považovat za víkend

(např. 1 – sobota, neděle; 2 – neděle, pondělí).

=WORKDAY(začátek;dny;[svátky]) Vrátí číslo požadovaného data před nebo po zadaném počtu pracovních dní.

=WORKDAY.INTL(začátek;dny;[víkend];[svátky]

)

Vrátí číslo dat před nebo po zadaném počtu pracovních dní s parametry víkendu.

VYHLEDÁVACÍ

=VYHLEDAT(co;hledat;[výsledek]) Vyhledá požadovanou hodnotu v matici hodnot či sloupci zařazeném do argumentu výsledek.

=SVYHLEDAT(hledat;tabulka;sloupec;[typ]) Vyhledá hodnotu v krajním levém sloupci tabulky a vrátí hodnotu ze zadaného sloupce (pořadí)

ve stejném řádku. Parametr typ určí, zda se hodnota dohledá přesně nebo přibližně.

=VVYHLEDAT(hledat;tabulka;řádek;[typ]) Prohledá horní řádek tabulky hodnot a vrátí hodnotu ze zadaného řádku obsaženou ve stejném

sloupci. Parametr typ určí, zda se hodnota dohledá přesně nebo přibližně.

=POZVYHLEDAT(co;prohledat;[shoda]) Vrátí relativní polohu položky matice, která odpovídá hledané hodnotě.

=INDEX(pole;řádek;[sloupec]) Vrátí hodnotu, která leží v konkrétním řádku, případně i konkrétním sloupci pole.

=NEPŘÍMÝ.ODKAZ(odkaz;[a1]) Vrátí odkaz určený textovým řetězcem. Nepovinný argument umožní zajistit notaci R1C1.

=ZVOLIT(index;hodnota1;[hodnota2];…) Na základě indexu vybere jednu z uvedených hodnot. Např. index č. 1 odpovídá hodnota1.

LOGICKÉ

=KDYŽ(podmínka;[ano];[ne]) Ověří, zda je podmínka splněna a vrátí jednu hodnotu pro případ, že splněna je a jinou

v případě, že splněna není.

=A(logická1;[logická2];…) Ověří, zda jsou všechny zadané podmínky splněny současně. Jedná se o stav PRAVDA.

=NEBO(logická1;[logická2];…) Ověří, zda platí alespoň jedna z uvedených podmínek. V tom případě se jedná o stav PRAVDA.

=IFERROR(hodnota;hodnota_v_případě_chyby) Pokud je výsledek vzorce chybný, vypíše funkce hodnotu, která je uvedena v argumentu

hodnota_v_případě_chyby. Pokud je v pořádku, vypíše hodnotu argumentu hodnota.

DATABÁZOVÉ

=DSUMA(databáze;pole;kritéria) Sečte čísla ve sloupci databáze, která splňují zadaná kritéria.

=DPRŮMĚR(databáze;pole;kritéria) Vrátí průměr hodnot ve sloupci databáze, které splňují zadaná kritéria.

=DSOUČIN(databáze;pole;kritéria) Vynásobí hodnoty ve sloupci databáze, které splňují zadaná kritéria.

=DPOČET(databáze;pole;kritéria) Vrátí počet buněk obsahujících čísla ve sloupci databáze, která splňují zadaná kritéria.


=DPOČET2(databáze;pole;kritéria) Vrátí počet neprázdných buněk ve sloupci databáze, které splňují zadaná kritéria.

=DMIN(databáze;pole;kritéria) Vrátí minimální hodnotu ve sloupci databáze, která splňuje zadaná kritéria.

=DMAX(databáze;pole;kritéria) Vrátí maximální hodnotu ve sloupci databáze, která splňuje zadaná kritéria.

TEXTOVÉ

=CONCATENATE(text1;[text2];…) Složí několik textových řetězců do jednoho. Stejného výsledku dosáhnete použitím znaku &.

=ČÁST(text;start;počet_znaků) Vrátí část znaků z textového řetězce při zadání počáteční pozice znaku a počtu znaků.

=DÉLKA(text) Vrátí počet znaků textového řetězce.

=HLEDAT(co;kde;[start]) Vrátí číslo prvního nalezeného výskytu znaku. Velikost písmen není rozlišována.

=NAJÍT(co;kde;[start]) Vrátí číslo prvního nalezeného výskytu znaku. Velikost písmen je rozlišována.

=HODNOTA(text) Převede textový řetězec představující číslo na číslo.

=HODNOTA.NA.TEXT(hodnota;formát) Převede hodnotu na text v zadaném formátu.

=NAHRADIT(starý;start;znaky;nový) Nahradí část textového řetězce jiným textovým řetězcem.

=PROČISTIT(text) Odstraní všechny přebytečné mezery (na začátku, na konci, mezi slovy ponechá pouze jednu).

=VYČISTIT(text) Odstraní z textu všechny netisknutelné znaky.

=STEJNÉ(text1;text2) Ověří, zda jsou dva textové řetězce stejné a vrátí logickou hodnotu PRAVDA. Rozlišuje velikosti.

=ZLEVA(text;znaky) Vrátí zadaný počet znaků z textového řetězce od jeho počátku.

=ZPRAVA(text;znaky) Vrátí zadaný počet znaků z textového řetězce od jeho konce.

INFORMAČNÍ

=ISEVEN(číslo) Vrátí logickou hodnotu PRAVDA, pokud je číslo sudé.

=ISODD(číslo) Vrátí logickou hodnotu PRAVDA, pokud je číslo liché.

=JE.ČISLO(hodnota) Vrátí logickou hodnotu PRAVDA, pokud je hodnota číslo.

=JE.CHYBA(hodnota) Vrátí logickou hodnotu PRAVDA, pokud dojde k chybové hodnotě, kromě #NENÍ_K_DISPOZICI.

=JE.CHYBHODN(hodnota) Vrátí logickou hodnotu PRAVDA, pokud dojde k libovolné chybě.

=JE.LOGHODN(hodnota) Vrátí logickou hodnotu PRAVDA, pokud se jedná o logickou hodnotu PRAVDA nebo NEPRAVDA.

=JE.NEDEF(hodnota) Vrátí logickou hodnotu PRAVDA, pokud se jedná o chybovou hodnotu #NENÍ_K_DISPOZICI.

=JE.NETEXT(hodnota) Vrátí logickou hodnotu PRAVDA, pokud v buňce není text.

=JE.ODKAZ(hodnota) Vrátí logickou hodnotu PRAVDA, pokud je v buňce odkaz.

=JE.PRÁZDNÉ(hodnota) Vrátí logickou hodnotu PRAVDA, pokud je buňka prázdná.

=JE.TEXT(hodnota) Vrátí logickou hodnotu PRAVDA, pokud je v buňce text.

=O.PROSTŘEDÍ(typ) Vrátí informaci o aktuálním pracovním prostředí.

=TYP(hodnota) Vrátí celé číslo představující datový typ hodnoty (1 = číslo; 2 = text; 4 = logická hodnota;

16 = chybová hodnota; 64 = matice).

FINANČNÍ

=PLATBA(sazba;pper;souč_hod;[bud_hod];[typ]) Vypočítává platbu půjčky na základě konstantních plateb a konstantní úrokové sazby.

=BUDHODN(sazba;pper;splátka;[souč_hodn];[typ]) Vrátí budoucí hodnotu investice vypočtenou na základě splátek a úrokové sazby.

=PLATBA.ÚROK(sazba;za;pper;souč_hod;

[bud_hodn];[typ])

Vrátí výšku úroku v určitém období vypočtenou na základě pravidelných konstantních

splátek a konstantní úrokové sazby.

=PLATBA.ZÁKLAD(sazba;za;pper;souč_hodn;

[bud_hodn];[typ])

Vrátí hodnotu splátky jistiny pro zadanou investici vypočtenou na základě pravidelných

konstantních splátek a konstantní úrokové sazby.

=PLATBA.OBDOBÍ(sazba;splátka;souč_hodn;

[bud_hodn];[typ])

Vrátí počet období pro investici vypočítaný na základě pravidelných konstantních splátek

a konstantní úrokové sazby.


ZÁVĚR

Dostali jsme se až na samotný závěr publikace. Naučili jsme se pracovat s důležitými funkcemi Excelu.

Možnosti Excelu zde samozřejmě nekončí. Pokud máte zájem dozvědět se ještě více, budu jenom rád,

pokud s Vaší zvědavostí zamíříte na jeden z navazujících kurzů Excelu, ve kterých se již pracuje s větším

množstvím dat, které se dále analyzují například formou kontingenčních tabulek a citlivostních analýz.

Veškeré další podrobnosti můžete sledovat na www.lektornedoma.cz v PREMIUM sekci, kde se budou

objevovat stále nové podklady k problematice MS Office. Přístup do PREMIUM sekce získáte

po vyplnění referenčního dotazníku na adrese http://lektornedoma.cz/dotaznik. Děkuji za jeho

vyplnění. Díky Vašim podmětům mohu zlepšovat kvalitu mých seminářů.

Přeji hodně úspěchů při práci s Excelem a jeho nástroji.

Sestava skript ze dne: 2016-11-14.

Doporučeno používat pro verzi MS Office 2007 – 2016 (určité funkce lze snad využít pouze ve verzi

2013 či 2016 – vždy je však tato poznámka u funkce uvedena).

Date post:	06-Feb-2018
Category:	Documents
Upload:	phungkien
View:	288 times
Download:	6 times

Vzorce a funkce v MS Excel - lektornedoma.cz Funkce.pdf · Microsoft Excel Vzorce a funkce v MS...

Documents