Vzorec
Vzorec je jakýsi symbolický předpis, který s využitím operátorů a funkcí zpracovává vstupní hodnoty a navrací požadovaný výsledek (do buňky, do proměnné). Se vzorci se potkáte nejčastěji v buňkách listu. Objevují se ale i v definovaných názvech nebo kupříkladu v podmíněném formátování či v programovém kódu.
Zápis vzorce do buňky
1. Předem vybereme jednu či více buněk (pro hromadný zápis).
2. Přímo nebo s využitím řádku vzorců zapíšeme symbol = a doplníme požadovaný vzorec.
3. Zápis potvrdíme stiskem klávesy ENTER (CTRL+ENTER v případě hromadného zápisu do více buněk).
Při hromadném zápisu Excel zohledňuje adresování buněk (relativní, absolutní, smíšené). Chová se stejně, jako kdyby byl vzorec rozkopírován z první buňky do dalších.
Operátory v Excelu
Operátor je matematický pojem, nad jehož definicí si nyní nebudeme lámat hlavu. Přinejmenším všichni známe aritmetické operátory, mezi něž patří operátory pro sčítání, odečítání, násobení a dělení. V zápisu 1 + 2 představují čísla 1 a 2 operandy a znaménko + operátor pro sčítání. To nám z pohledu pojmů stačí.
Operátor přiřazení
symbol = (úvodní znak v zápisu vzorců, ALT+0061)
1 2 3 4 5 6 | =3 =3+5 =A1 =A1+3 =SUMA(A1:A3) ="kolotoč" |
Symbolem = zde přiřazujeme (do buňky) hodnotu, resp. výsledek vzorce či výroku, zkrátka to, co je napravo od něj. V případě zápisu =A1 se jedná o prostý odkaz na jinou buňku (její hodnotu). První a poslední příklad ukazují, jak vypadá přiřazení hodnoty 3 a textu „kolotoč“ vzorcem namísto přímého zápisu.
Pozn.
Vzorec lze uvést i symboly + a -, případně znakem @, pokud za ním bude následovat název funkce. Úvodní symbol = doplní v tomto případě Excel do zápisu sám.
Aritmetické operátory
Sčítání
symbol + (plus, klávesová zkratka ALT+0043 na numerické části klávesnice)
1 2 3 | =1+2 =A1+A2 =A3+3 |
Odečítání
symbol – (minus, ALT+0045)
1 2 3 | =1-2 =A1-A2 =A3-3 |
Stejné znaménko se uplatní i v případě negace výrazu.
1 2 3 | =-1 =-A1 =-(A3+3) |
Pozn.
Možná někteří z vás již narazili kupříkladu na zápis – -, tj. dvojici znamének minus vedle sebe. Asi si stejně jako já vybavíte ze školy, že namísto osamoceného znaménka minus si můžeme představit číslo -1, jímž násobíme bezprostředně následující výraz, tj. zápis -(3+5) značí -1·(3+5) = -1·3 – 1·5 = -3 – 5 = -8. A dvě znaménka vedle sebe? Inu (-1)·(-1)·…, což je zdánlivě zbytečná operace navíc. Nicméně touto technikou se běžně převádí výsledky výroků (PRAVDA, NEPRAVDA) na čísla, jímž odpovídají (1, 0).
Násobení
symbol * (hvězdička, ALT+0042, pravý ALT+-)
1 2 3 | =1*2 =A1*A2 =A3*3 |
Pozn.
V Excelu se nepoužívá znak x (iks). Tomu se vyhýbejte i jinde (byť ho česká norma výjimečně připouští). Zapomeňte i na symbol pro násobek × (ALT+0215, pravý ALT+), tj. pravá závorka), tečku . (ALT+0046), resp. středovou tečku · (ALT+0183).
Tip
Na notebocích bez numerického bloku v pravé části je hvězdička velmi často dostupná pod klávesovou zkratkou Fn+P.
Tip
Tlačítko pro násobení si můžete také přidat na panel nástrojů Rychlý přístup.
Dělení
symbol / (obyčejné lomítko, ALT+0047)
1 2 3 | =1/3 =A1/A2 =A3/3 |
Pozn. Typografie rozlišuje matematické lomítko (1⁄3), jež má větší sklon. V Excelu se ale neuplatní, stejně jako symbol ÷.
Pozn. Na listu Excelu se nepoužívá zpětné lomítko \ (ALT+0092, pravý ALT+Q) pro celočíselné dělení. Jeho funkci přebírá funkce CELÁ.ČÁST. Zpětné lomítko se pro dané účely objevuje pouze coby operátor pod VBA.
Umocnění („iks na ypsilon“)
symbol ^ (stříška, ALT+0094, pravý ALT+š + znak)
1 2 3 | =1^2 =A1^A2 =A3^3 |
Pozn. Pro odmocninu existuje v Excelu funkce ODMOCNINA. Nicméně ta je připravena pouze na druhou odmocninu. V praxi používáme pro jiné případy matematickou poučku.
Pro třetí odmocninu čísla dvě tak zapíšeme
1 | =2^(1/3) |
Procenta
Pokud se v buňce či jejím vzorci objeví zápis 25%, případně, 25 %, Excel hodnotu 25 automaticky vydělí 100 a buňku tzv. naformátuje.
25% … interní hodnota 0,25
25 % … interní hodnota 0,25
=1+25% … interní hodnota 1,25
Pozn.
Osobně toto chování (spojené s formátováním buňky) nemám v oblibě. Spoléhám se raději na klasickou trojčlenku a vlastní formát buňky. V praxi ostatně zápis 25% (bez mezery) značí dvacetipětiprocentní, nikoliv dvacet pět procent (s mezerou).
Tip
Jak říkám, procenta nejlépe umějí počítat ženy. Slevu 10 % z částky 500 Kč lze přepsat jako 0,1 · 500 Kč = 50 Kč (číslo uvádějící procenta dělíme stem a slovní „z“ značí krát). Jinak viz pojmy jako zmíněná trojčlenka či přímá úměra.
Spojování textů (textových řetězců)
Pro spojování textových řetězců s jinými řetězci (nebo čísly) používáme symbol & (ampersand, ALT+0038, pravý ALT+C). Předběžně berte jako fakt, že pokud se má objevit znak/text ve vzorci, musí být uveden v uvozovkách. A jedním dechem dodávám, že mezera je znak jako jakýkoliv jiný.
1 2 3 4 5 6 | ="kolo"&"toč" ="Josef"&" "&"Spejbl" ="Smith"&"&"&"Wesson" =B7&" bodů" ="Průměr hodnot: "&B3 ="Průměr hodnot: "&PRŮMĚR(B3:B10) |
Pozn.
Na chvíli se u příkladů zastavte. Nespěchejte, musíte je dostat pod kůži. Uvědomte si rozdíl mezi uvedením odkazu (adresy buňky) B7 a prostým textem „B7“, mezi operátorem & a textem „&“ (viz Smith & Wesson), mezi textem „Průměr“ a funkcí PRŮMĚR (zde pracující s oblastí B3:B10 uvedenou v závorkách).
Pozn.
Pamatujte si, že výsledkem takových spojení je vždy text, i kdyby elementy pro spojení tvořila jen čísla. Pro spojování řetězců Excel obsahuje i funkci s názvem CONCATENATE (v Excelu 2016 navíc i funkci TEXTJOIN).
Srovnávací (komparační, relační) operátory
symboly =, <, >, <=, >=, <> (< … ALT+0060, > … ALT+0062, případně pravý ALT + stejně označené klávesy)
1 2 3 4 5 6 | =B2=C2 ... rovnost =B2<C2 ... menší než =B2>C2 ... větší než =B2<=C2 ... menší nebo rovno =B2>=C2 ... větší nebo rovno =B2<>C2 ... různé od (tj. "nerovná se") |
Pozn.
Stejná syntaxe se používá i ve VBA. Neuplatňují se zde zápisy typu ==, !, !=, &&, || běžné v jiných jazycích.
Tip
Není vám jasný zápis? První symbol rovnítka vlevo si v duchu zakryjte. V jeho případě se jedná o výše popsaný operátor přiřazení. V uvedených příkladech řešíme nějaký výrok (tvrzení, test, podmínku), jehož výsledkem je pravdivostní hodnota (PRAVDA, NEPRAVDA). Více v článku KDYŽ se řekne Excelu (1).
Pozn.
Pro úplnost dodávám, že ani zde se neuplatní korektní matematické symboly ≤, ≥ a ≠.
Logické operátory
Syntaxe ve vzorcích Excelu neumožňuje používání logických operátorů, mezi něž patří programátorům dobře známý AND, OR, NOT, XOR a další. Ty jsou v klasické podobě dostupné pouze pod VBA a ve vzorcích českého Excelu je zastupují funkce A, NEBO, NE. S funkcí XOR přišel na listu až Excel 2013. Více se jim budeme věnovat jindy.
Operátor oblasti
symbol : (dvojtečka, ALT+0058)
1 2 | =SUMA(A1:A3) =J5:L5*10 |
Dvojtečka vyjadřuje slovní „až, od-do“ (oblast A1 až A3).
Operátor explicitně zadaného průniku
symbol mezera
1 2 3 | =J6:L6 K5:K7 =SUMA(J6:K7 K5:K7) =Liberec leden |
Mezera zde plní význam průsečíku, jinak řečeno průniku (množin). V posledním příkladu se jedná o průnik dvou námi pojmenovaných oblastí.
Operátor sjednocení
symbol ; (středník)
1 | INDEX((J5:L5;J7:L7);1;2;2) |
Pozn.
Středník zde uvedený mezi vnitřními závorkami má přeci jen trochu jiný význam, než ten samý symbol v roli oddělovače argumentů ve funkci.
Priorita operátorů
Dříve vzniklo mnoho debat o tzv. unárním znaménku minus a podivném chování v rámci priority operátorů. Zjednoduším to. Pokud si nechcete přidělávat práci a hledat chyby ve výpočtech, používejte závorky.
24+6/3 versus (24+6)/3
Jestli vás přesto priorita zajímá, pak platí následující pořadí (řazeno od nejvyšší po nejnižší):
: (dvojtečka)
(mezera)
; (středník)
(odkazovací operátory)
– (negace)
% (procenta)
^ (umocnění)
*, / (násobení a dělení)
+, – (sčítání a odčítání)
& (zřetězení)
= <> < > =< >= (relační operátory)
Operátory ve VBA
Operátory ve VBA se hodně podobají těm na listu. Namísto logických funkcí se zde vyskytují logické operátory, objevuje se zpětné lomítko pro celočíselné dělení spolu s operátorem Mod (na listu funkce MOD) vracející zbytek po dělení (modulo), a některá další klíčová slova v roli operátorů, jako je šikovný Like pro práci s textem nebo Is pro testování objektů.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | Sub VBAOperatory() 'aritmetické operátory '+, -, *, /, \, Mod 'sčítání x = 10 + 4 'odčítání x = 10 - 4 'násobení x = 10 * 4 'dělení x = 10 / 4 'celočíselné dělení '10 / 3 = 3 (a zbytek 1) x = 10 \ 3 'zbytek celočíselného dělení '10 / 6 = (1) a zbytek 4 x = 10 Mod 6 'mocnina '4^3, 4 * 4 * 4 x = 4 ^ 3 'logické operátory 'And, Or, Not, Xor, Eqv, Imp 'list: PRAVDA ... 1, NEPRAVDA ... 0 'VBA: True (PRAVDA) ... -1, False ... 0 (nenulová hodnota) Dim b As Boolean b = 0 'False b = 1 'True b = -3 'True m = 3 n = 4 'And ... a zároveň platí, že ... 'tj. konjunkce 'True, jsou-li oba operandy True 'jestliže m = 3 a zároveň n = 4, tak ... If (m = 3) And (n = 4) Then '... End If 'Or ... nebo platí, že ... 'tj. disjunkce 'True, je-li alespoň jeden operand True 'jestliže m = 3 nebo n = 4, tak If (m = 3) Or (n = 4) Then '... End If o = m = 3 'True 'Not 'negace výroku b = False b = Not b 'True 'Xor ... neekvivalence 'True, jsou-li oba operandy rozdílné 'Eqv ... ekvivalence 'True, jsou-li oba operandy stejné 'Imp ... implikace 'True kromě případu, kdy první operand je True a druhý False 'relační operátory '=, <>, <, >, <=, >= 'číselné porovnání p = 0 < 3 'True p = 0 < "3" 'True, řetězec převeden na číslo p = 0 < "3,14" 'True, řetězec převeden na číslo 'p = 0 < "3.14" 'chyba, řetězec nepřeveden na číslo 'p = 0 < "" 'chyba, řetězec nepřeveden na číslo 'řetězcové porovnání r = "král" < "Král" 'False r = "Princ" < "princ" 'True 'operand Null ... výsledek vždy Null s = 0 < Null 'specifické operátory '& ... zřetězení RetezecA = "James " & " " & "Bond" RetezecB = "00" & "7" RetezecC = "Výsledek: " & x RetezecD = 10 & 4 'Like 'test shody řetězce se vzorkem t = "kolotoč" Like "kolo*" 'True 'Is 'test odkazu na stejný objekt v paměti Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Range("A1") Set rng2 = Range("A1") Set rng3 = rng1 u = rng1 Is rng2 'False v = rng1 Is rng3 'True 'priority od nejvyšší po nejnižší: 'mocnina (^) 'negace (–) 'násobení a dělení (*, /) 'celočíselné dělení (\) 'modulo, tj. zbytek po dělení (Mod) 'sčítání a odčítání (+, –) 'řetězení (&) End Sub |