Přehled vzorců

Pokud s Excel pro web teprve začínáte , brzy zjistíte, že se jedná o víc než jen o mřížku, ve které zadáváte čísla do sloupců nebo řádků. Ano, můžete použít Excel pro web k vyhledání součtů pro sloupec nebo řádek čísel, ale můžete také vypočítat splátku hypotéky, vyřešit matematické nebo technické problémy nebo najít scénář nejlepšího případu na základě proměnných čísel, která připojíte.

Excel pro web to dělá pomocí vzorců v buňkách. Vzorec provádí výpočty nebo jiné akce s daty na listu. Vzorec vždycky začíná symbolem rovná se (=), za kterým můžou být čísla, matematické operátory (třeba symbol plus nebo mínus) a funkce, které ze vzorců dokážou vytvořit skutečně výkonný nástroj.

Třeba v následujícím vzorci je hodnota 2 násobena hodnotou 3 a k výsledku je přičtená hodnota 5. Výsledkem je hodnota 11.

=2*3+5

Následující vzorec využívá funkci PLATBA, s jejíž pomocí vypočítává splátku hypotéky ve výši 10 736,43 Kč, která je založena na 5% úrokové sazbě (5 % děleno 12 měsíci se rovná měsíční úrokové sazbě) po dobu 30 let (360 měsíců) u půjčky 2 000 000 Kč:

=PLATBA(0,05/12;360;2000000)

Následující přehled obsahuje ukázky typů vzorců, které lze zadat do listu.

=A1+A2+A3 Sečte hodnoty v buňkách A1, A2 a A3.
=ODMOCNINA(A1) Pomocí funkce ODMOCNINA vrátí druhou odmocninu hodnoty v buňce A1.
=DNES() Vrátí aktuální datum.
=VELKÁ("ahoj") Převede text „ahoj“ na „AHOJ“ pomocí funkce listu VELKÁ.
=KDYŽ(A1>0) Testováním buňky A1 určí, zda obsahuje hodnotu větší než 0.

Části vzorce

Vzorec může taky obsahovat některé (nebo všechny) tyto části: funkce, odkazy, operátory a konstanty.

1. Funkce: Funkce PI() vrátí hodnotu čísla pí: 3,142...

2. Odkazy: A2 vrátí hodnotu v buňce A2.

3. Konstanty: Číselné nebo textové hodnoty zadané přímo do vzorce, třeba hodnota 2.

4. Operátory: Operátor ^ (stříška) umocní číslo na zadanou mocninu a operátor * (hvězdička) vypočítá součin čísel.

Použití konstant ve vzorcích

Konstanta je hodnota, která se nevypočítává a zůstává vždycky stejná. Konstantou je třeba datum 10.9.2008, číslo 210 nebo text „čtvrtletní příjmy“. Výraz nebo hodnota, která je výsledkem výrazu, konstantami nejsou. Když se ve vzorci použijí místo odkazů na buňky konstanty (třeba =30+70+110), změní se výsledek jenom v případě, že tento vzorec upravíte.

Použití výpočtových operátorů ve vzorcích

Operátory určují typ výpočtu, který chcete s prvky vzorce provést. Existuje výchozí pořadí výpočtů vycházející z obecných matematických pravidel, ale pomocí závorek lze toto pořadí změnit.

Typy operátorů

Existují čtyři různé typy výpočtových operátorů: aritmetické, porovnávací, odkazovací a operátor zřetězení textu.

Aritmetické operátory

K provádění základních matematických operací, jako je sčítání, odčítání, násobení nebo dělení, kombinování čísel a vytváření číselných výsledků slouží následující aritmetické operátory.

Aritmetický operátor	Význam	Příklad
+ (znaménko plus)	Sčítání	3+3
- (symbol mínus)	Odčítání Negace	3–1 –1
* (hvězdička)	Násobení	3*3
/ (lomítko)	Dělení	3/3
% (značka procent)	Procenta	20%
^ (stříška)	Umocnění	3^2

Relační operátory

Pomocí následujících operátorů můžete porovnat dvě hodnoty. Při porovnání dvou hodnot pomocí těchto operátorů je výsledkem logická hodnota – PRAVDA nebo NEPRAVDA.

Relační operátor	Význam	Příklad
= (symbol rovná se)	Je rovno	A1=B1
> (symbol větší než)	Větší než	A1>B1
< (symbol menší než)	Menší než	A1<B1
>= (symbol větší než nebo rovno)	Větší než nebo rovno	A1>=B1
<= (symbol menší než nebo rovno)	Menší než nebo rovno	A1<=B1
<> (symbol není rovno)	Není rovno	A1<>B1

Operátor zřetězení textu

Pokud chcete zřetězením (spojením) jednoho nebo několika textových řetězců vytvořit jediný textový řetězec, použijte operátor &.

Textový operátor	Význam	Příklad
&	Spojuje (řetězí) dvě hodnoty a vytváří jednu souvislou textovou hodnotu.	"Sklep"&"mistr" vrátí výsledek "Sklepmistr"

Odkazovací operátory

Pokud chcete kombinovat oblasti buněk pro výpočty, použijte následující operátory.

Odkazovací operátor	Význam	Příklad
: (dvojtečka)	Operátor oblasti, který vytváří jeden odkaz na všechny buňky mezi dvěma odkazy včetně těchto odkazů.	B5:B15
; (středník)	Operátor sjednocení, který kombinuje více odkazů do jednoho odkazu	SUMA(B5:B15;D5:D15)
(mezera)	Operátor průniku, který vytváří jeden odkaz na buňky společné dvěma odkazům.	B7:D7 C6:C8

Pořadí, ve kterém Excel pro web provádí operace ve vzorcích

V některých případech může pořadí provádění výpočtu ovlivnit návratovou hodnotu vzorce. Je proto důležité rozumět způsobu, jakým se pořadí provádění výpočtu určuje, a tomu, jak se dá změnou tohoto pořadí dosáhnout požadovaných výsledků.

Pořadí výpočtu

Vzorce počítají hodnoty v určitém pořadí. Vzorec vždycky začíná symbolem rovná se (=). Excel pro web interpretuje znaky, které následují za symbolem rovná se, jako vzorec. Za symbolem rovná se následují prvky, se kterými se má počítat (operandy), třeba konstanty nebo odkazy na buňky. Ty jsou oddělené výpočtovými operátory. Excel pro web vypočítá vzorec zleva doprava podle konkrétního pořadí pro jednotlivé operátory ve vzorci.

Priorita operátorů

Pokud zkombinujete několik operátorů do jednoho vzorce, provede Excel pro web operace v pořadí uvedeném v následující tabulce. Pokud vzorec obsahuje operátory se stejnou prioritou , například pokud vzorec obsahuje operátor násobení i dělení, Excel pro web vyhodnotí operátory zleva doprava.

Operátor	Popis
: (dvojtečka) (jedna mezera) ; (středník)	Odkazovací operátory
–	Zápor (třeba -1)
%	Procento
^	Umocnění
* a /	Násobení a dělení
+ a -	Sčítání a odčítání
&	Spojuje dva řetězce textu (zřetězení).
= < > <= >= <>	Porovnání

Použití závorek

Pokud chcete změnit pořadí vyhodnocení, uzavřete do závorek tu část vzorce, kterou chcete vypočítat jako první. Například následující vzorec vytvoří číslo 11, protože Excel pro web provádí před sčítáním násobení. Tento vzorec vynásobí 2 krát 3 a potom k výsledku přičte 5.

=5+2*3

Naproti tomu pokud syntaxi změníte pomocí závorek, Excel pro web sečte hodnoty 5 a 2 a výsledek vynásobí 3, aby se vytvořilo číslo 21.

=(5+2)*3

V následujícím příkladu Excel pro web závorky, které ohraničují první část vzorce, nejprve vypočítat B4+25 a potom výsledek vydělit součtem hodnot v buňkách D5, E5 a F5.

=(B4+25)/SUMA(D5:F5)

Použití funkcí a vnořených funkcí ve vzorcích

Funkce jsou předdefinované vzorce provádějící výpočty pomocí určitých hodnot, nazývaných argumenty, v určitém pořadí nebo struktuře. Pomocí funkcí se dají dělat jednoduché nebo složité výpočty.

Syntaxe funkce

Na následujícím příkladu použití funkce ZAOKROUHLIT pro zaokrouhlení čísla v buňce A10 je znázorněna syntaxe funkce.

1. Struktura. Struktura funkce začíná znaménkem rovná se (=) následovaným názvem funkce, levou závorkou, argumenty funkce oddělené čárkami a pravou závorkou.

2. Název funkce. Pokud chcete zobrazit seznam dostupných funkcí, klikněte na buňku a stiskněte shift+F3.

3. Argumenty. Argumenty můžou být čísla, text, logické hodnoty jako PRAVDA nebo NEPRAVDA, matice, chybové hodnoty jako #N/A nebo odkazy na buňky. Argument, který určíte, musí pro tento argument vytvořit platnou hodnotu. Argumenty můžou být také konstanty, vzorce nebo jiné funkce.

4. Popis argumentu. Při zadávání funkce se zobrazí popis se syntaxí a argumenty. Zadejte například =ZAOKROUHLOVAT( a zobrazí se popis. Popisy se zobrazují jenom pro předdefinované funkce.

Zadání funkcí

Když vytváříte vzorec obsahující funkci, můžete pomocí dialogového okna Vložit funkci zadat funkce listu. Při zadávání funkce do vzorce se v dialogovém okně Vložit funkci zobrazuje název funkce, jednotlivé argumenty, popis funkce a jednotlivých argumentů, aktuální výsledek funkce a aktuální výsledek celého vzorce.

Pokud si chcete usnadnit vytváření a úpravy vzorců a minimalizovat překlepy a syntaktické chyby, použijte funkci Automatické dokončování vzorce. Po zadání = (rovnítko) a počátečních písmen nebo aktivační události zobrazení Excel pro web pod buňkou zobrazí dynamický rozevírací seznam platných funkcí, argumentů a názvů, které odpovídají písmenům nebo aktivační události. Položku z tohoto rozevíracího seznamu pak můžete vložit do vzorce.

Vnoření funkcí

V určitých případech může být potřeba použít určitou funkci jako jeden z argumentů jiné funkce. Následující vzorec třeba používá vnořenou funkci PRŮMĚR a porovnává výsledek s hodnotou 50.

Vnořené funkce

1. Funkce PRŮMĚR a SUMA jsou vnořené ve funkci KDYŽ.

Platné vrácení Pokud se jako argument použije vnořená funkce, musí vnořená funkce vrátit stejný typ hodnoty, který argument používá. Pokud například argument vrátí hodnotu PRAVDA nebo NEPRAVDA, musí vnořená funkce vrátit hodnotu PRAVDA nebo NEPRAVDA. Pokud funkce ne, zobrazí Excel pro web #VALUE! chybovou hodnotu #HODNOTA!.

Omezení úrovní vnoření: Vzorec může obsahovat až sedm úrovní vnořených funkcí. Když se jedna funkce (budeme ji nazývat funkce B) použije jako argument v jiné funkci (budeme ji nazývat A), chová se funkce B jako funkce druhé úrovně. Třeba funkce PRŮMĚR a SUMA jsou obě funkcemi druhé úrovně, pokud se použijí jako argumenty funkce KDYŽ. Funkce vnořená uvnitř funkce PRŮMĚR by byla funkcí třetí úrovně a tak dál.

Použití odkazů ve vzorcích

Odkaz identifikuje buňku nebo oblast buněk na listu a říká Excel pro web, kde hledat hodnoty nebo data, které chcete ve vzorci použít. Pomocí odkazů můžete v jednom vzorci použít data obsažená v různých částech listu nebo hodnotu jedné buňky použít v několika vzorcích. Můžete taky odkazovat na buňky v jiných listech stejného sešitu nebo na jiné sešity. Odkazy na buňky v jiných sešitech se nazývají propojení nebo externí odkazy.

Odkazy typu A1

Výchozí styl odkazování: Ve výchozím nastavení používá Excel pro web styl odkazu A1, který odkazuje na sloupce s písmeny (A až XFD, celkem 16 384 sloupců) a odkazuje na řádky s čísly (1 až 1 048 576). Tato písmena a čísla se nazývají záhlaví řádků a sloupců. Odkaz na buňku vytvoříte zadáním písmene sloupce následovaného číslem řádku. Třeba B2 odkazuje na buňku v průsečíku sloupce B a řádku 2.

Cíl odkazu	Odkaz
Buňka ve sloupci A a řádku 10	A10
Oblast buněk ve sloupci A a řádcích 10 až 20	A10:A20
Oblast buněk v řádku 15 a sloupcích B až E	B15:E15
Všechny buňky v řádku 5	5:5
Všechny buňky v řádcích 5 až 10	05:10
Všechny buňky ve sloupci H	H:H
Všechny buňky ve sloupcích H až J	H:J
Oblast buněk ve sloupcích A až E a řádcích 10 až 20	A10:E20

Vytvoření odkazu na jiný list: V následujícím příkladu vypočítá funkce listu PRŮMĚR průměrnou hodnotu oblasti B1:B10 v listu s názvem Marketing ve stejném sešitu.

1. Odkazuje na list s názvem Marketing.

2. Odkazuje na oblast buněk v rozmezí B1 až B10 (včetně).

3. Oddělí odkaz na list od odkazu na oblast buněk.

Rozdíl mezi absolutními, relativními a smíšenými odkazy

Relativní odkazy Relativní odkaz na buňku ve vzorci, třeba A1, je založen na relativním umístění buňky obsahující vzorec a buňky, na kterou odkaz odkazuje. Když se změní umístění buňky, která obsahuje vzorec, změní se i odkaz. Pokud vzorec zkopírujete nebo vyplníte do více řádků nebo sloupců, odkaz se automaticky upraví. Ve výchozím nastavení používají nové vzorce relativní odkazy. Pokud třeba zkopírujete nebo vyplníte relativní odkaz z buňky B2 do buňky B3, změní se vzorec automaticky z hodnoty =A1 na hodnotu =A2.

Absolutní odkazy Absolutní odkaz na buňku ve vzorci, třeba $A$1, vždycky odkazuje na buňku v určitém umístění. Pokud se změní umístění buňky, která obsahuje vzorec, zůstane absolutní odkaz stejný. Pokud odkaz zkopírujete nebo vyplníte do více řádků nebo sloupců, absolutní odkaz se neupraví. Ve výchozím nastavení se v nových vzorcích používají relativní odkazy, možná tedy bude třeba je přepnout na absolutní odkazy. Když třeba zkopírujete nebo vyplníte absolutní odkaz z buňky B2 do buňky B3, zůstane v obou buňkách stejná hodnota =$A$1.

Smíšené odkazy Smíšený odkaz obsahuje absolutní hodnotu ve sloupci a relativní na řádku nebo absolutní hodnotu na řádku a relativní ve sloupci. Absolutní odkaz na sloupec má tvar $A1, $B1 atd. Absolutní odkaz na řádek má tvar A$1, B$1 atd. Když se změní pozice buňky obsahující vzorec, změní se relativní odkaz a absolutní odkaz zůstane zachován. Pokud vzorec zkopírujete nebo vyplníte do více řádků nebo sloupců, upraví se automaticky relativní odkaz a absolutní odkaz zůstane beze změn. Když třeba zkopírujete nebo vyplníte smíšený odkaz z buňky A2 do buňky B3, bude upraven z hodnoty =A$1 na hodnotu =B$1.

Styl prostorového odkazu

Pohodlné odkazování na více listů Pokud chcete analyzovat data ve stejné buňce nebo oblasti buněk na více listech sešitu, použijte prostorový odkaz. Ten obsahuje odkaz na buňku nebo oblast, kterému předchází rozsah názvů listů. Excel pro web používá všechny listy uložené mezi počátečním a koncovým názvem odkazu. Vzorec =SUMA(List2:List13!B5) například sečte všechny hodnoty obsažené v buňce B5 na všech listech mezi listem 2 a listem 13 (včetně).

Pomocí prostorových odkazů můžete odkazovat na buňky v jiných listech, definovat názvy a vytvářet vzorce pomocí následujících funkcí: SUMA, PRŮMĚR, AVERAGEA, POČET, POČET2, MAX, MAXA, MIN, MINA, SOUČIN, SMODCH.P, SMODCH.VÝBĚR.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA a VARPA.
Prostorové odkazy se nedají použít v maticových vzorcích.
Prostorové odkazy se nedají použít s Operátor průniku (jednoduchá mezera) ani ve vzorcích s Implicitní průsečík.

Co se stane po přesunutí, zkopírování, vložení nebo odstranění listů Následující příklady vysvětlují, co se stane po přesunutí, zkopírování, vložení nebo odstranění listů, které jsou zahrnuty v prostorovém odkazu. V příkladech se pomocí vzorce =SUMA(List2:List6!A2:A5) sčítají buňky A2 až A5 v listech 2 až 6.

Vložení nebo kopírování Pokud vložíte nebo zkopírujete listy mezi List2 a List6 (koncové body v tomto příkladu), Excel pro web zahrne do výpočtů všechny hodnoty v buňkách A2 až A5 z přidaných listů.
Odstranit Pokud odstraníte listy mezi listy List2 a List6, odebere Excel pro web jejich hodnoty z výpočtu.
Přesunout Pokud listy mezi listy List2 a List6 přesunete do umístění mimo oblast odkazovaných listů, odebere Excel pro web jejich hodnoty z výpočtu.
Přesunutí koncového bodu Pokud přesunete List2 nebo List6 na jiné místo ve stejném sešitu, Excel pro web upraví výpočet tak, aby vyhovoval novému rozsahu listů mezi nimi.
Odstranění koncového bodu Pokud odstraníte List2 nebo List6, Excel pro web upraví výpočet tak, aby odpovídal rozsahu listů mezi nimi.

Odkazy typu R1C1

Můžete taky použít styl odkazu, ve kterém jsou očíslované řádky i sloupce listu. Styl odkazu R1C1 slouží k výpočtu pozic řádků a sloupců v makrech. Ve stylu R1C1 Excel pro web označuje umístění buňky s "R" následovaným číslem řádku a "C" následovaným číslem sloupce.

Odkaz	Význam
R[-2]C	Relativní odkaz na buňku o dva řádky výše ve stejném sloupci
R[2]C[2]	Relativní odkaz na buňku o dva řádky níže a dva sloupce doprava
R2C2	Absolutní odkaz na buňku ve druhém řádku a druhém sloupci
R[-1]	Relativní odkaz na celý řádek nad aktivní buňkou
R	Absolutní odkaz na současný řádek

Při záznamu makra Excel pro web zaznamenává některé příkazy pomocí stylu odkazu R1C1. Pokud například zaznamenáte příkaz, například kliknutím na tlačítko Automatické shrnutí vložíte vzorec, který přidá oblast buněk, Excel pro web vzorec zaznamená pomocí odkazů ve stylu R1C1, nikoli ve stylu A1.

Použití názvů ve vzorcích

Můžete vytvořit definované názvy, které budou představovat buňky, oblasti buněk, vzorce, konstanty nebo Excel pro web tabulky. Název je výstižná zkratka, která usnadňuje pochopení účelu odkazu na buňku, konstantu, vzorec nebo tabulku, jejichž význam nemusí být na první pohled jasný. V příkladu jsou uvedené často používané názvy a způsob, jakým zlepšují srozumitelnost zápisu a usnadňují pochopení vzorců.

Typ příkladu	Příklad použití oblastí namísto názvů	Příklad použití názvů
Odkaz	=SUMA(A16:A20)	=SUMA(Prodej)
Konstanta	=SOUČIN(A12;9,5%)	=SOUČIN(Cena;DPH)
Vzorec	=TEXT(SVYHLEDAT(MAX(A16;A20);A16:B20;2;NEPRAVDA);"m/dd/rrrr")	=TEXT(SVYHLEDAT(MAX(Prodej);InfoOProdeji;2;NEPRAVDA);"m/dd/rrrr")
Tabulka	A22:B25	=SOUČIN(Cena;Tabulka1[Daňová sazba])

Typy názvů

Můžete vytvářet a používat několik typů názvů.

Definovaný název: Název, který představuje buňku, oblast buněk, vzorec nebo konstantní hodnotu. Můžete vytvořit vlastní definovaný název. Excel pro web také někdy vytvoří definovaný název, například když nastavíte oblast tisku.

Název tabulky Název tabulky Excel pro web, což je kolekce dat o určitém předmětu, která je uložená v záznamech (řádcích) a polích (sloupcích). Excel pro web při každém vložení Excel pro web tabulky vytvoří výchozí Excel pro web název tabulky "Tabulka1", "Tabulka2" atd. Tyto názvy ale můžete změnit, aby byly smysluplnější.

Vytváření a zadávání názvů

Název vytvoříte pomocí příkazu Vytvořit jméno z výběru. Názvy můžete pohodlně vytvářet z existujících popisků řádků a sloupců pomocí výběru buněk na listu.

Poznámka: Ve výchozím nastavení používají názvy absolutní odkazy na buňky.

Název můžete zadat následujícími způsoby:

Psaní Název se dá napsat, třeba jako argument vzorce.
Použití funkce Automatické dokončování vzorce: Můžete použít rozevírací seznam Automatické dokončování vzorce, ve kterém jsou automaticky uvedeny platné názvy.

Použití maticových vzorců a konstant

Excel pro web nepodporuje vytváření maticových vzorců. Můžete zobrazit výsledky maticových vzorců vytvořených v desktopové aplikaci Excel, nemůžete je ale upravit ani přepočítat. Pokud máte desktopovou aplikaci Excel, můžete s maticemi pracovat, když kliknete na Otevřít v aplikaci Excel.

Následující maticový příklad vypočítá součet hodnot v matici s cenami cenných papírů a akcií, aniž by se k výpočtu a zobrazení jednotlivých hodnot u každé akcie použil řádek buněk.

Maticový vzorec vypočítávající jeden výsledek

Když zadáte vzorec ={SUMA(B2:D2*B3:D3)} jako maticový vzorec, vynásobí se u každé akcie jejich počet a cena a výsledky těchto výpočtů se následně sečtou.

Výpočet více výsledků: Některé funkce listu vrátí matice hodnot nebo vyžadují matice hodnot jako argument. Pokud chcete pomocí maticového vzorce vypočítat víc výsledků, je třeba zadat matici do oblasti buněk, která má stejný počet řádků a sloupců jako maticové argumenty.

V následujícím příkladu určuje funkce LINTREND lineární hodnoty údajů o prodeji v dané řadě tří údajů o prodeji (sloupec B) pro řadu tří měsíců (sloupec A). Vzorec je zadán do tří buněk ve sloupci C (C1:C3) proto, aby se zobrazily všechny jeho výsledky.

Maticový vzorec vypočítávající několik výsledků

Když zadáte vzorec =LINTREND(B1:B3;A1:A3) ve formě maticového vzorce, budou na základě tří údajů o prodeji a tří měsíců vytvořeny tři samostatné výsledky (22 196, 17 079 a 11 962).

Použití maticových konstant

Do běžného vzorce můžete zadat odkaz na buňku obsahující hodnotu nebo samotnou hodnotu nazývanou také jako konstanta. Do maticového vzorce můžete podobně zadat odkaz na matici nebo zadat matici hodnot obsažených v buňkách nazývanou také jako maticová konstanta. Do maticových vzorců lze zadat konstanty podobně jako do běžných vzorců, maticové konstanty je však třeba zadat v určitém formátu.

Maticové konstanty můžou obsahovat čísla, text, logické hodnoty (třeba PRAVDA nebo NEPRAVDA) nebo chybové hodnoty (třeba #N/A). Jedna maticová konstanta může obsahovat různé typy hodnot – třeba {1;3;4|PRAVDA;NEPRAVDA;PRAVDA}. Čísla v maticových konstantách můžou být v celočíselném, desetinném nebo matematickém formátu. Text musí být uzavřen v uvozovkách, třeba "Úterý".

Maticové konstanty nemůžou obsahovat odkazy na buňky, sloupce nebo řádky nestejné délky, vzorce ani zvláštní znaky $ (znak dolaru), závorky a % (znak procent).

Při formátování maticových konstant nezapomeňte:

Uzavřít je do složených závorek ( { } ).
Hodnoty v různých sloupcích oddělit středníky (;). Pokud chcete třeba znázornit hodnoty 10, 20, 30 a 40, zadáte {10;20;30;40}. Tato maticová konstanta se označuje jako matice 1 krát 4 a je ekvivalentem odkazu na 1 řádek krát 4 sloupce.
Hodnoty v různých řádcích oddělit znakem (|). Pokud chcete třeba znázornit hodnoty 10, 20, 30 a 40 v jednom řádku a hodnoty 50, 60, 70 a 80 v řádku bezprostředně pod ním, zadáte maticovou konstantu 2 krát 4: {10;20;30;40|50;60;70;80}.