Seznam funkcí pro kurz EXCEL II
Jaroslav Nedoma
2010
Funkce EXCEL II
© Jaroslav Nedoma
Obsah ÚVOD ........................................................................................ 3 SUMA ........................................................................................ 4 PRŮMĚR.................................................................................... 6 MIN ........................................................................................... 8 MAX ........................................................................................10 POČET .....................................................................................12 POČET2 ...................................................................................14 ZAOKROUHLIT.........................................................................16 COUNTIF .................................................................................18 SVYHLEDAT .............................................................................22 POZVYHLEDAT ........................................................................27 SUBTOTAL ...............................................................................32 KDYŽ........................................................................................35 SUMIF .....................................................................................38 CONCATENATE .......................................................................40 ČÁST ........................................................................................43 Speciální znaky .......................................................................45 Klávesové zkratky ...................................................................46
2
Funkce EXCEL II
© Jaroslav Nedoma
ÚVOD Autor zpracoval následující podklady jako pomůcku pro zvládnutí problematiky některých funkcí tabulkového editoru MS EXCEL. Tento materiál je určen pro účastníky kurzu MS Excel II. 2003 a 2007. Každý, kdo prošel tímto kurzem, se dozvěděl o všech následujících funkcích a tento materiál je jen jakýmsi přehledem, který by měl dopodrobna vysvětlit veškeré definice a zápis funkcí.
3
Funkce EXCEL II
© Jaroslav Nedoma
SUMA Suma je nejpoužívanější funkcí Excelu. Je definovaná jako součet několika čísel nebo celé oblasti čísel. Syntaxe: =SUMA(číslo1;číslo2;číslo3;…) -
-
V tomto případě bychom psali pouze konkrétní adresy buněk, které chceme sčítat (př. A2, B5, C10). Syntaxe by potom byla ve tvaru: „=SUMA(A2;B5;C10)“. Tento případ vidíme na následujícím obrázku. Sčítáme čísla: 10, 3 a 5. Výsledkem tedy bude číslo 18 v buňce, kam píšeme vzorec, tj. D4.
4
Funkce EXCEL II
© Jaroslav Nedoma
=SUMA(oblast1;oblast2;oblast3;…) -
-
-
Oblastí se rozumí napsání dvou buněk, které budou definovat horní pravý roh a levý dolní roh tabulky, kde se nacházejí hodnoty, které chceme sčítat. Vzorec bychom tedy napsali např. ve tvaru: „=SUMA(A2:B5)“. Posčítají se nám tedy všechny číselné položky, které se v této oblasti nacházejí (viz následující obrázek). Výsledkem nám tedy bude číslo 52 v buňce C3.
Můžeme sčítat i více než jen jednu oblast čísel. Oblastí může být více. Stačí jednotlivé oblasti oddělit středníkem. Např. „=SUMA(A2:B5;C5:D7)“.
5
Funkce EXCEL II
© Jaroslav Nedoma
PRŮMĚR Průměr je funkce, která je svou definicí podobná funkci SUMA. Definujeme totiž opět buď jen číselné buňky, které chceme zprůměrovat nebo celou oblasti Syntaxe: =PRŮMĚR(číslo1;číslo2;číslo3;…) -
-
-
Adresy buněk, které se zapíší do závorek místo položek „číslo 1; číslo 2; číslo 3; …“ nám říkají, že právě z nich budeme počítat průměrnou hodnotu (aritmetický průměr). Jestli-že chceme průměrovat např. hodnoty v buňkách A2, B5, C2, syntaxe zápisu funkce by vypadala následovně: „=PRŮMĚR(A2;B5;C2)". Tento příklad je zřetelný z následujícího obrázku, kdy do buňky s adresou B7 vypisujeme vzorec ve tvaru viz předchozí odstavec. Výsledkem bude číslo 7.
6
Funkce EXCEL II
© Jaroslav Nedoma
=PRŮMĚR(oblast;oblast2;oblast3;…) -
Oblastí můžeme definovat opět více, nemusí to být vždy jedna. Do funkce můžu např. kombinovat oblasti i jednotlivá čísla pro spočtení jednoho celkového průměru. To si ukážeme na následujícím příkladu.
-
Zde chceme průměrovat dvě oblasti a jedno číslo. Všechny tyto prvky můžeme napsat do jediného vzorce. Ten píšeme např. do buňky A6 jako na obrázku. První oblastí se rozumí čísla, která jsou v buňkách A1 až A4 (to zapíšeme jako A1:A4, tudíž místo slova až napíšeme dvojtečku). Pokračujeme oddělením argumentů (oblastí), což se provádí středníkem a začneme definovat další oblast (B1:B4) a za poslední středník napíšeme pouze adresu jediné buňky C1, ve které se nachází číslo 6.
Celý vzorec v buňce A6 bude vypadat následovně: „=PRŮMĚR(A1:A4;B1:B4;C1)“.
7
Funkce EXCEL II
© Jaroslav Nedoma
MIN Funkce s klíčovým slovem MIN je praktickou jednoduchou funkcí, která najde v dané oblasti nejmenší hodnotu, kterou nám vypíše jako výsledek. Opět můžeme do závarky jako argumenty definovat oblasti i jednotlivé buňky. Syntaxe: =MIN(číslo1;číslo2;číslo3;…) -
Opět se dostáváme k nejjednoduššímu typu definice, který je totožný jako u funkcí SUMA a PRŮMĚR. Rozdíl tedy tvoří klíčové slovo, které je zde MIN. Do závorky tedy opět definujeme jednotlivé položky, ze kterých chceme najít minimální hodnotu. V následujícím příkladu chceme najít nejmenší hodnotu z buněk A1, C3, D4 a E5 a výsledek chceme zobrazit v buňce A6, kam píšeme následující vzorec: „=MIN(A1;C3;D4;E5)“. Výsledkem nám zde bude číslo 6, protože je ze všech nejmenší.
8
Funkce EXCEL II
© Jaroslav Nedoma
=MIN(oblast;oblast2;oblast3;…) -
Oblastně můžeme vybírat minimum např. z buněk A1 až A6. Opět platí, že místo slovíčka „až“ dáme do vzorce znak dvojtečky. Z tohoto příkladu by jsme chtěli výsledek zobrazit např. do buňky A8, kam píšeme vzorec: „=MIN(A1:A6)“. Výsledkem je nám opět nejmenší číslo, kterým je 1 v buňce A6. Výsledek chceme vidět v buňce A8 kam píšeme vzorec.
-
Opět zde platí pravidlo, že můžu do závorky definovat za středníky opět další oblast nebo jen jedno číslo, ze kterého chci minimum hledat.
9
Funkce EXCEL II
© Jaroslav Nedoma
MAX Funkce, jejíž klíčové slovo nese označení MAX, je určena pro vyhledání nejvyšší hodnoty v námi definované oblasti nebo opět jen vybraných číselných buněk. Syntaxe: =MAX(číslo1;číslo2;číslo3;…) -
-
Jedná se o funkci opačnou k funkci MIN, takže je definice shodná až na klíčové slovo. K ukázce využijeme předchozí příklad u funkce MIN. Nyní budeme ale využívat funkci MAX pro nalezení nejvyšší číselné hodnoty. Zápis funkce bude vypadat ve tvoru: „=MAX(A1;C3;D4;E5)“ a výsledkem nám bude číslo 34, které se nachází v buňce C3. Výsledek chceme opět vidět v buňce s adresou A6.
10
Funkce EXCEL II
© Jaroslav Nedoma
=MAX(oblast;oblast2;oblast3;…) -
-
K ukázce hledání maxima z oblasti využijeme opět příklad oblastního hledání funkce MIN. Funkce se opět zachová opačně. Funkce bude zapsána ve tvaru: „=MAX(A1:A6)“. Nejvyšším číslem je 67 v buňce A4. Výsledek chceme zobrazit v buňce A8.
Při hledání nejvyššího čísla z více oblastí oddělíme do závorky více oblastí středníkem.
11
Funkce EXCEL II
© Jaroslav Nedoma
POČET Funkce POČET je určena pro toho, kdo potřebuje spočítat, kolik buněk v námi definované oblasti obsahuje nějakou číselnou hodnotu. Syntaxe: =POČET(hodnota1;hodnota2;hodnota3;…) -
-
-
Například budeme chtít hlídat, v kolika z námi definovaných buněk se nachází číselná hodnota. V případě, že buňka z našeho hlídaného výběru bude prázdná nebo bude naplněna textem (nebude se jednat o číslo) nebude započtena. V následujícím příkladu zapisujeme vzorec do buňky A4, kde očekáváme výsledek a funkce bude mít v závorce argumenty (buňky), které chceme hlídat (A1, B2, C1, D2 a E1).
Z předchozího příkladu po stisknutí enteru za vzorce uvidíme, že je výsledek 4. Jedná se tedy o odpověď, že pouze 4 z našich pěti vybraných buněk jsou číselně
12
Funkce EXCEL II
© Jaroslav Nedoma
naplněny. „Ahoj“ je text a proto ho funkce vynechala. Jakmile ale text přepíšeme na číslo, již bude započítáno. =POČET(oblast1;oblast2;oblast3;…) -
-
-
V tomto případě budeme chtít např. hlídat větší oblast buněk a opět sledovat kolik z nich je číselně naplněných. Pro následující příklad budeme zapisovat do buňky A9, kam očekáváme výsledek, vzorec: „=POČET(A1:B7)“.
V tomto případě bude výsledek v buňce A9 roven 10. Funkce nám totiž přeskočí prázdné buňky (viz A4) a buňky obsahující text (viz A5, B3 a B6).
13
Funkce EXCEL II
© Jaroslav Nedoma
POČET2 Funkce POČET2 je podobnou funkcí k funkci POČET. Rozdíl mezi nimi je ale patrný. Zatímco funkce POČET kontroluje, kolik buněk v našem výběru je číselně naplněných, funkce POČET2 se stará o to, zda jsou naše buňky vůbec nějak naplněné. Nezajímá ji tedy, jestli je tam číslo nebo text. Oba tyto údaje bere jako buňku, která není prázdná. Nedělá tedy rozdíl mezi textovou a číselnou hodnotou. Syntaxe: =POČET2(hodnota1;hodnota2;hodnota3;…) -
K názornému příkladu uvedeme stejnou tabulku jako u funkce POČET. Budeme (až na klíčové slovo) zapisovat stejný vzorec, protože budeme vycházet ze stejných dat. Rozdíl bude samozřejmě v zapsání funkce. Nebude se jednat o POČET, ale o POČET2. Ve finální verzi bude vzorec v buňce A4 následující: „=POČET2(A1;B2;C1;D2;E1)“.
14
Funkce EXCEL II
-
© Jaroslav Nedoma
Nyní nám již vyjde výsledek 5, protože funkce započítala všechny námi vybrané buňky, které nejsou prázdné (takže i text „ahoj“).
=POČET2(oblast1;oblast2;oblast3;…) -
Opět jsme použili tabulku, kterou využívala předchozí funkce POČET. Zde ale opět v buňce A9 uvidíme jiný výsledek. Tentokrát to bude 13. Jediná buňky, která se nám nezapočítá, bude buňka A4, která je prázdná (textově ani číselně není naplněná).
15
Funkce EXCEL II
© Jaroslav Nedoma
ZAOKROUHLIT Jestliže budeme chtít jakoukoli číselnou hodnotu zaokrouhlit jak na desítky, stovky, setiny, tisíciny, celá čísla aj. použijeme stejnojmennou funkci ZAOKROUHLIT. Jakékoliv jiné zaokrouhlování, které spočívá např. v ručním stahování desetinných míst, působí na oko stejně, ale ve finále si Excel pořád stažená desetinná místa pamatuje pro další výpočty, kdežto u funkce ZAOKROUHLIT je jakoby zapomene (číslo bude pro další výpočty použito již jako zaokrouhlené a dál se tedy s ním tak počítá). Syntaxe: =ZAOKROUHLIT(číslo;číslice) -
-
-
Funkce ZAOKROUHLIT má dva argumenty. „Číslo“ je adresa buňky, v níž je číslo, které chceme zaokrouhlovat. Dále napíšeme středník a funkci zajímá ještě další argument, kterým je „číslice“. Budeme chtít například zaokrouhlit číslo 123,56 na celé číslo bez desetinných míst. To provedeme jednoduchým zapsáním funkce (viz následující obrázek).
Výsledkem nám bude zaokrouhlené číslo 124.
16
Funkce EXCEL II
© Jaroslav Nedoma
-
To samé číslo můžeme chtít zaokrouhlit jen na jedno desetinné místo. To provedeme následujícím způsobem:
-
Nyní už bude výsledkem 123,6. Tzn., že pokud budeme chtít zaokrouhlovat na libovolný počet desetinných míst, napíšeme jako argument kladnou číslici. Co když ale budeme chtít číslo zaokrouhlit například na desítky? Pak musí být číslice záporná. Vše ukážeme na následujícím příkladu:
-
-
Výsledkem bude číslo 120. Pakliže budeme chtít zaokrouhlovat na stovky, použijeme číslo -2 atd.
17
Funkce EXCEL II
© Jaroslav Nedoma
COUNTIF Prošli jsme si funkci POČET, ale pořád nám ještě nemusí stačit v případech, když nás zajímá, např. kolik čísel větších jak 10 je v naší oblasti buněk. Pak už by byla funkce POČET krátká. Funkce COUNTIF je tedy jakási rozšířená funkce POČET a její překlad z angličtiny je „počítej když“. Takže je rozšířena o jakési kritérium. Vše si rozebereme na následujících příkladech. Syntaxe: =COUNTIF(oblast;kritérium) -
-
-
Funkce COUNTIF sestává ze dvou argumentů. Prvním je „oblast“. Tu už zadávat umíme. Jedná se o oblast, kterou budeme chtít sledovat a počítat, kolik je v ní číselných hodnot, ale pozor! Hodnot, které splňují nějakou podmínku. Pro nás je podmínka např. taková, že všechna čísla musí být větší než 10. Na obrázku (viz níže) vidíme tabulku, kde jsou v buňkách A1 až A7 nějaká čísla a v buňce A9 chceme vidět výsledek, který nám ukáže, kolik čísel je zde větších než 10, což je naše kritérium. Zápis je tedy ve tvaru: „=COUNTIF(A1:A7;“>10“)“. Kritérium tedy musí být v uvozovkách. Neplatí to až na výjimky, které si rozebereme dále.
18
Funkce EXCEL II
-
-
© Jaroslav Nedoma
Nemuseli bychom například chtít čísla větší než 10, ale čísla, která jsou rovna např. číslu 12. Takovéto kritérium je již jedno jak zapíšete. Zda s uvozovkami nebo bez nich.
Výsledkem nám bude číslo 1, protože v celé oblasti se nachází číslo 12 pouze jednou.
19
Funkce EXCEL II
-
-
-
© Jaroslav Nedoma
Dále můžeme tuto funkci využít i pro hledání textových řetězců. Například bychom mohli chtít zjistit, kolikrát se v nějaké oblasti opakuje nějaká položka. V následujícím příkladu chceme zjistit, kolikrát se nachází v seznamu jméno „Jana“. Zápis bude ve tvaru: „=COUNTIF(A1:A7;“Jana“)“.
Výsledkem bude číslo 3, protože se Jana v seznamu objevila hned třikrát. Dále si někdo řekne, že bude chtít hledat, kolik číselných buněk v naší oblasti je větších než třeba číslo v nějaké jiné buňce. Pro vysvětlení máme sloupec čísel v oblasti A1 až A7 a do buňky C7 zadáme třeba číslo 5 a chceme se zeptat, kolik čísel v oblasti A1 až A7 je větších než to, které je v buňce C7. Pak nestačí vzorec zapsaný ve tvaru: „=COUNTIF(A1:A7;“>C7“)“. Tento vzorec by byl špatně a funkce by nic nespočíta-
20
Funkce EXCEL II
© Jaroslav Nedoma
la. Pro takovéhle těžší podmínky musíme využít upravenější definice pro zadávání kritéria. Celý vzorec by vypadal následujícím způsobem: „=COUNTIF(A1:A7;“>“&C7)“. Takže v uvozovkách zůstane již jen znak „<“ nebo „>“ a před adresou buňky je znak „&“ určený pro adresaci. Napíšeme ho přes kombinaci pravý alt a C (AltGr+C).
21
Funkce EXCEL II
© Jaroslav Nedoma
SVYHLEDAT Když se budeme v Excelu bavit o vyhledávacích funkcích, určitě nezapomeneme na funkci SVYHLEDAT. Tato funkce je již od těch předchozích těžší na definici, ale pro pochopení má vše svou logickou vazbu a tak není o nic složitější ji používat. Syntaxe: =SVYHLEDAT(hledat;tabulka;sloupec;[typ]) -
-
-
Funkce SVYHLEDAT očekává na rozdíl od těch předchozích čtyři argumenty. Tři jsou povinné a jeden nepovinný (poslední – typ). „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. Oba případy si ukážeme níže. 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 náhodně vybere, 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á Ex-
22
Funkce EXCEL II
-
-
© Jaroslav Nedoma
cel 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. K tomu je právě druhá možnost. o Log. 0 (NEPRAVDA) – jestliže dosadíme za argument „typ“ nulu, funkci tím sdělíme, že hledaná data nejsou seřazena vzestupně a funkce se podle toho zachová (projde všechna hledaná data). Jestliže nebude nalezena v tabulce přesná
23
Funkce EXCEL II
-
© Jaroslav Nedoma
shoda, vypíše chybovou hodnotu #N/A, žádnou přibližnou hodnotu již, jako možnost log. 1, hledat nebude. Vše si ukážeme na praktickém příkladu ve dvou verzích. Např. máme najít v následující tabulce, kterému jménu přísluší nejvyšší číslo. Do buňky A8 jsme si pomocí funkce MAX (kterou již známe) našli maximální hodnotu z oblasti buněk A1 až A6, což je v našem případě 20. Funkce by měla zápis: „=MAX(A1:A6)“. No a právě toto číslo bychom chtěli funkcí SVYHLEDAT najít a očekávali bychom do buňky B8 přímo jmenovitou odpověď, u koho se toto maximální číslo nachází. Začali bychom tedy zápisem funkce do buňky B8: „SVYHLEDAT(A8;A1:B16;2;0)“. o „Hledat“ – najít chceme maximální hodnotu, která se nachází v buňce A8, tak proto píšeme jako první A8 a následně středník. Tím ukončím zadávání parametru „hledat“. o „Tabulka“ – maximální hodnotu má funkce hledat v oblasti od A1 až A6, ale musíme k této oblasti přidat ještě příslušné textové odpovědi ze sousedního sloupce. Celá oblast tabulky tedy bude vypadat definicí A1:B6 a poté zápisem středníku pro ukončení parametru „tabulka“.
24
Funkce EXCEL II
© Jaroslav Nedoma
o „Sloupec“ – zadáváme číslo 2, protože odpověď k nalezenému maximálnímu číslu z prvního sloupečku najdeme ve sloupečku druhém. Opět pokračujeme středníkem. o „Typ“ – zde je patrné, že je log. 0 potřeba zadat z důvodu řazení hledaných dat (nejsou seřazena vzestupně). Proto za posledním středníkem najdeme v zápisu log. 0.
-
-
-
Funkce SVYHLEDAT nám nyní najde jmenovitě „Šárku“ protože maximální hodnota se nachází právě u ní. Toto jde udělat i způsobem druhým (bez mezikroku pro nalezení maximální hodnoty – to můžeme udělat vše ve funkci SVYHLEDAT tzv. vnořením). Zápis funkce by vypadal následovně: „=SVYHLEDAT(MAX(A1:A6);A1:B6;2;0)“.
25
Funkce EXCEL II
-
© Jaroslav Nedoma
Tím jsme nahradili odkaz na nějakou buňku (A8) zapsáním přímo patřičné funkce do parametru „hledat“.
26
Funkce EXCEL II
© Jaroslav Nedoma
POZVYHLEDAT Funkci POZVYHLEDAT jsme probírali pouze na nějakých seminářích. Proto se může stát, že ji vidíte poprvé. Nicméně, není od věci se s touto funkcí seznámit. Část názvu napovídá, že se bude jednat o vyhledávací funkci. Má za úkol vyhledat pozici nějaké hledané hodnoty v určité prohledávané oblasti. Často se používá ve spojení s jinou funkcí, což si ukážeme dále na praktickém příkladu.
Syntaxe: =POZVYHLEDAT(co;prohledat;[shoda]) -
-
Funkce POZVYHLEDAT má tři argumenty. K zadání funkce jsou povinné dva z nich a třetí (shoda) je nepovinný. Prvním argumentem je „co“. Sem dosadíme to, čeho pozici chceme hledat (číslo, text nebo přímo odkaz na nějakou buňku). Druhým argumentem je „prohledat“. Jedná se o souvislou oblast, ve které se bude hledaný prvek „co“ vyhledávat. Argument „shoda“ je číslo -1, 0 nebo 1. Shoda určuje, jakým způsobem má aplikace Microsoft Excel porovnávat hledanou hodnotu s hodnotami v prohledávané oblasti. Jestliže nezadáme ani jednu ze tří hodnot, bude brána v potaz automaticky hodnota 1. o 1 – jestliže zadáme za argument „shoda“ jedničku, funkce POZVYHLEDAT začne v hledané oblasti hledat největší hodnotu, která je menší nebo rovna hledané
27
Funkce EXCEL II
-
-
© Jaroslav Nedoma
hodnotě „co“. Hodnoty argumentu „prohledat“ musí být přitom seřazeny vzestupně (-2; -1; 0; 1; 2;...; A-Z). o 0 – jestliže zadáme za argument „shoda“ nulu, funkce POZVYHLEDAT najde první hodnotu, která se přesně shoduje s hledanou hodnotou „co“. Hodnoty argumentu prohledat přitom nemusí být nijak seřazeny. o -1 – jestliže zadáme za argument „shoda“ mínus jedničku, funkce POZVYHLEDAT najde nejmenší hodnotu, která je větší nebo rovna hledané hodnotě „co“. Hodnoty argumentu „prohledat“ přitom musí být seřazeny sestupně (2; 1; 0; -1; -2;...; Z-A). Se všemi způsoby zadávání argumentů „shoda“ se nyní seznámíme. V následující tabulce si ukážeme všechny tři způsoby na hledání jména „Dana“ v tabulce se jmény. Tabulka se jmény (argument „prohledat“) je seřazena podle abecedy (vzestupně). Prvním argumentem „co“ je buňka A8 (Dana). To platí pro všechny tři zápisy funkce. Stejně tak je pro všechny zápisy stejný argument „prohledat“ (oblast buněk se jmény A1 až A5). Rozdíl bude pouze v použití tří hodnot v argumentu „shoda“. Zatímco při použití 1 a 0 funkce vrátila správný výsledek (3 – Dana je skutečně jako třetí), při použití -1 se vyskytla chybová hláška #N/A, protože pro použití -1 není splněna podmínka, že tabulka se jmény je seřazena sestupně (od Z po A).
28
Funkce EXCEL II
-
-
© Jaroslav Nedoma
To samé si můžeme ukázat v tabulce, kde budou figurovat čísla. Seznam čísel (argument „prohledat“ je seřazen sestupně), proto bude při hledání desítky možné použít do argumentu „shoda“ pouze 0 (přesná hodnota) nebo -1 (data seřazená sestupně). Obě tyto hodnoty zajistí správný výsledek 4. Chtěli jsme hledat to, co je v buňce A8 a to je číslo 10, proto je výsledkem 4 (čtvrtá pozice v hledané oblasti). Hodnota 1 tentokrát ukáže chybovou hlášku #N/A, kde je jasné, že není opět splněna podmínka. Jednoduše chceme hledat v tabulce seřazené sestupně, ale Excelu jedničkou říkáme, že je tabulka seřazena vzestupně, v tom je celá chyba.
29
Funkce EXCEL II
-
-
© Jaroslav Nedoma
Na začátku kapitolky funkce POZVYHLEDAT jsme si říkali, že se jedná o funkci, která se často pojí s jinými funkcemi. My si nyní ukážeme praktický příklad vyhledávání, k jehož správnému chodu je zapotřebí právě dvou funkcíPOZVYHLEDAT a SVYHLEDAT. Jedná se o tabulku s různými hodnotami. Sloupečky jsou pojmenované třemi měsíci v roce (leden, únor, březen). Řádky nesou jména některých osob. Našim úkolem je vyřešit situaci, kdy máme v políčku „Výsledek vyhledávání“ zobrazit vždy aktuální hodnotu podle toho, jaké jméno a jaký měsíc napíše uživatel do dvou kolonek k tomu určených. Celý zápis funkce bude vypadat přesně tak, jako na následujícím obrázku. Když jsme si ukazovali princip funkce SVYHLEDAT (str. 22), říkali jsme, že funkce potřebuje vždy vědět, ze kterého sloupečku chceme získat odpověď. V tomhle případě je ale dopředu nezjistitelné, ze kterého sloupečku bude odpověď za konkrétní jméno zobrazována. To určí až uživatel, kdy zadá například ná-
30
Funkce EXCEL II
© Jaroslav Nedoma
zev měsíce Únor a v tomto okamžiku teprve víme, že chceme odpověď získávat ze sloupečku měsíce Únor, čili v tabulce sloupečku třetího. Když se ale měsíc změní na Březen, už se jedná o čtvrtý sloupeček. Toto proměnné číslo získává právě funkce POZVYHLEDAT (vyhledává pozici zadaného měsíce v horním řádku tabulky) a předá ho funkci SVYHLEDAT na pozici argumentu „sloupec“. Jedná se tedy o jakousi funkci vnořenou, bez které by funkce SVYHLEDAT nefungovala tak jak má, protože by vždy mohla hledat pouze a jen vždy z jednoho sloupečku.
31
Funkce EXCEL II
© Jaroslav Nedoma
SUBTOTAL Funkce SUBTOTAL je zvláštní funkcí, která nám pomáhá počítat s filtrovanými daty. Často stojíme před problémem, kdy tvoříme nějakou databázovou tabulku (seznam) a potřebujeme například spočítat součet všech právě zfiltrovaných položek. To by byl ale pomocí klasické součtové funkce SUMA problém, protože tato funkce by nám počítala stále veškerý obsah (tzn. i položky nezfiltrované). Funkce SUBTOTAL ovšem počítá právě jen položky zfiltrované a v tom je její výhoda v používání u filtrů. Nejedná se pouze o součet ale o dalších deset funkcí, které uvedeme v tabulce dále (str. 28).
Syntaxe: =SUBTOTAL(konstanta;odkaz1;odkaz2;odkaz3;…) -
Funkce SUBTOTAL obsahuje dohromady jedenáct funkcí, které lze používat pro práci s filtry. První argument „konstanta“ je číslo od jedné do jedenácti, které právě definuje onu podfunkci funkce SUBTOTAL. Přehled těchto konstant najdete v následující tabulce (str. 28).
Argument „odkaz“ je pro nás definice oblasti z naší databázové tabulky, ze které chceme vycházet pro výpočet nějakou z oněch 11 funkcí.
32
Funkce EXCEL II
© Jaroslav Nedoma
TABULKA KONSTANT FUNKCE SUBTOTAL KONSTANTA 1 2 3 4 5 6 7 8 9 10 11 -
-
FUNKCE PRŮMĚR POČET POČET2 MAX MIN SOUČIN SMODCH.VÝBĚR SMODCH SUMA VAR.VÝBĚR VAR
V následujícím příkladu se vžijeme do role majitele autobazaru a vyfiltrovali jsme si pouze auta značky Škoda Octavia červené barvy a zajímá nás jaká je hodnota těchto dvou vozů, které nám z celkové tabulky 259 automobilů zůstali. Proto použijeme funkci SUBTOTAL. Použitím funkce SUMA by jsme totiž sčítali vždy celou oblast všech 259 automobilů. My chceme znát součet jen námi vyfiltrovaných dvou vozů. Funkce bude mít následující zápis: „=SUBTOTAL(9;D2:D259)“. Prvním argumentem je konstanta 9, protože chceme pracovat s funkcí součtu (SUMA). Dále následuje již oblast celé tabulky od začátku D2 do konce D259. Nyní uvidíme po enteru součet pouze dvou námi vyfiltrovaných vozů. Nyní, když uděláme filtr dle jiných parametrů, bude se vždy počítat součet jen
33
Funkce EXCEL II
© Jaroslav Nedoma
námi vyfiltrovaných položek, což je podstata funkce SUBTOTAL.
-
Nyní víme, jak se funkce SUBTOTAL definuje a kdy se používá, takže nám nebrání nic v tom udělat například průměrnou hodnotu filtrovaných položek, hledat minimální a maximální hodnotu a mnoho jiných funkcí.
34
Funkce EXCEL II
© Jaroslav Nedoma
KDYŽ Nyní se dostáváme k funkci logické, která je podstatou pro často důležité rozhodování. Několikrát bychom chtěli udělat nějakou operaci, když se něco stane a v opačném případě nic nedělat nebo udělat něco jiného.
Syntaxe: =KDYŽ(podmínka;ano;ne) -
-
Tuto funkci si můžeme představit ze tří částí, přesně tolik má totiž argumentů. Prvním argumentem je „podmínka“. Funkce tedy nedělá nic jiného než tuto podmínku vyhodnotí. Pokud podmínka platí, tak se stane to, co napíšeme, aby se stalo do druhého argumentu funkce „ano“. V opačném případě se stane to, co bude napsáno v argumentu posledním, „ne“. Můžeme si ukázat některé z často využívaných příkladů. Tím prvním může být tabulka, ve které budeme sledovat příjmy a výdaje nějaké firmy. Pro zjednodušení je tabulka níže pouze jednořádková. V posledním sloupečku bychom chtěli vidět textovou odpověď „zisk“ nebo „ztráta“ podle toho, zda jsou příjmy vyšší než výdaje nebo naopak. To bychom zapsali následujícím způsobem (viz tabulka str. 31).
35
Funkce EXCEL II
© Jaroslav Nedoma
„=KDYŽ(B2>C2;“Zisk“;“Ztráta“)“ -
-
Vzorec jednoduše říká: Pokud jsou příjmy vyšší než výdaje, napiš slovo „Zisk“, v opačném případě napiš „Ztráta“. Zároveň vidíme, že ve vzorci se textové odpovědi uvozují do uvozovek. V tomto případě uvidíme jako odpověď slovo „Zisk“, protože jsou příjmy vyšší než výdaje. Dále bychom mohli uvést jednoduchý příklad, kdy chceme dělit dvě čísla, ale zároveň víme, že dělit nulou nelze a tudíž chceme tuto možnost obejít, kdyby se náhodou jako dělitel objevila nula. V následující tabulce máme v buňce A3 číslo 10 a v buňce B3 máme číslo 5. Do buňky C3 budeme psát následující vzorec: „=KDYŽ(B3<>0;“A3/B3;“Nulou nelze dělit“). Jednoduše jsme nadefinovali, že pokud bude dělitel v buňce B3 nenulový, proběhne klasické dělení a v buňce C3 uvidíme výsledek (jako nyní) a když bude dělitel v buňce B3 nula, v buňce C3 nebude výsledek, ale věta s informací, že dělit nulou nelze.
36
Funkce EXCEL II -
© Jaroslav Nedoma
Do funkce KDYŽ může být vnořena spousta dalších funkcí. Vše je již na nás, podle čeho se chceme řídit. Například, když se podmínka splní, můžeme počítat průměr nějaké oblasti, když ale splněna nebude, napíše se nám třeba jen chybová hláška nebo se provede výpočet jiné funkce. To je vše již jen na naší tvořivosti a vynalézavosti.
37
Funkce EXCEL II
© Jaroslav Nedoma
SUMIF Tato funkce není nic jiného než podmíněný součet. Pro klasický součet jsme používali funkci SUMA, ale jestliže bychom chtěli počítat pouze nějaké položky dle jiných údajů, už by byla funkce SUMA nedostačující. Vše si ukážeme na následujícím příkladu, na kterém všechno lépe pochopíme.
Syntaxe: =SUMIF(oblast;kriteria;součet) -
-
-
Funkce obsahuje tři argumenty. První argument „oblast“ je vyhodnocovaná oblast buněk, podle které se bude podmíněná funkce řídit. Druhým parametrem je „kriteria“, což je podmínka, která určuje sčítání buněk a posledním argumentem je „součet“, do kterého zadáme oblast, ze které se po splnění podmínky budou sčítat určité buňky. Vše bude nejlépe pochopitelné na následujícím příkladu. Máme k dispozici několik položek zboží a každé zboží spadá do jiné kategorie (číselně 1 nebo 2). My bychom chtěli posčítat zvlášť zboží, které spadá do kategorie 1 a zvlášť to, které spadá do kategorie 2. Nyní se podíváme na následující tabulku a začneme psát vzorec pro funkci SUMIF, která za nás tento problém vyřeší.
38
Funkce EXCEL II
-
-
© Jaroslav Nedoma
Součet 1 u nás znamená součet všech položek, které spadají do kategorie 1 (položky v buňkách A3, A4, A7 a A9). Zápis funkce bude ve tvaru: „=SUMIF(B3:B9;1;A3:A9)“. Funkci jsme neřekli nic jiného, než že se má podívat do sloupečků kategorií (B3:B9) zjistit, kde je jednička (1) a potom posčítat položky v oblasti A3:A9, které jsou na stejném řádku jako kategorie 1. Součet 2 je součet všech položek, které spadají do kategorie 2 (položky v buňkách A5, A6 a A8). Zápis funkce bude ve tvaru: „=SUMIF(B3:B9;2;A3:A9)“. Analogie principu je stejná jako u výpočtu součtů kategorie 1.
39
Funkce EXCEL II
© Jaroslav Nedoma
CONCATENATE CONCATENATE je funkce, kterou využíváme, jestli-že chceme spojit více textových buněk do buňky jediné. Mimo to můžeme do funkce vkládat i uměle vytvořený text, což nám urychlí práci například při tvorbě věty, která má být kombinací textu a hodnot v nějakých buňkách.
Syntaxe: =CONCATENATE(text1;text2;text3;…) -
-
Funkce obsahuje argumenty „text“, které po nás chtějí jediné, oddělit od sebe jednotlivé položky z více buněk, které chceme spojit do jediné buňky. Jako praktický příklad se nám může jevit situace, kdy do jedné buňky (C1) budeme chtít spojit jméno, které se nachází v jedné buňce (A1) a příjmení, které se nachází ve druhé buňce (B1). Vše vidíme na následujícím zobrazení v tabulce. Setkáme se tu s problémem, že když bychom zapsali funkci do buňky C1 stylem: „=CONCATENATE(A1;B1)“, uvidíme celé jméno bez mezery, tj. JménoPříjmení (př. JanaNováková). My ale mezeru do jména chceme zavést a proto využijeme jeden textový řetězec funkce CONCATENATE právě pro tvorbu mezery. Jak? Uplně jednoduše. Říkali jsme si, že každý uměle vytvořený text, což je i pouhá mezera, se do funkce vkládá jako další textové pole a zároveň musí být v uvozovkách.
40
Funkce EXCEL II
© Jaroslav Nedoma
Kompletní vzorec bude tedy nyní vypadat: „=CONCATENATE(A1;“ “;B1)“. Neboli jsme Excelu řekli: „Vlož to, co je v buňce A1, udělej mezeru a vlož to, co je v buňce B1. Výsledek tedy bude nyní v již správném tvaru: Jméno Příjmení (př. Jana Nováková).
-
-
Jako další ukázka by nám mohl postačit následující příklad. Chtěli bychom napsat následující větu: „Celkem se prodalo zboží za …… Kč“, kde se bude položka v korunách měnit podle aktuálního součtu. V buňkách A1 až A7 máme hodnoty, které chceme sčítat a na základě tohoto součtu chceme zapříčinit změnu věty v buňce A9. Začneme tedy psát: „=CONCATENATE(„Celkem se prodalo zboží za „;SUMA(A1:A7);“ Kč“)“. Jednoduše, první textové pole představuje část věty „Celkem se prodalo zboží za“, druhé textové pole vychází z vnořené funkce SUMA, která mi bude každou chvilkou přepočítávat součet oblasti buněk A1:A7 a poslední třetí textové pole je mezera zároveň symbol měny Kč.
41
Funkce EXCEL II
-
© Jaroslav Nedoma
Tímto způsobem by se daly vnořovat do této funkce ještě další funkce, které by byly její součástí jako například v předchozím příkladu (SUMA). Dala by se takto vkládat například i logická funkce KDYŽ, která by nám ještě podmínkou tuto funkci větvila.
42
Funkce EXCEL II
© Jaroslav Nedoma
ČÁST ČÁST je funkce opačná k funkci CONCATENATE. Nespojuje, nýbrž rozděluje jednu buňku do více buněk nebo vyjme do nějaké buňky pouze požadovanou část. To je vše jen na nás.
Syntaxe: =ČÁST(text;start;počet_znaků) -
-
Funkce tedy obsahuje tři argumenty. Prvním je „text“. Za tuto položku dosadíme adresu buňky, jejíž obsah chceme dělit. Po středníku funkce očekává další argument, kterým je „start“. To znamená číselně, na kterém místě má excel začít s oddělováním znaků a posledním argumentem „počet_znaků“ určíme, kolik znaků má excel z dané buňky oddělit (opět číselně). Na příkladu si můžeme ukázat, jakým způsobem funkce pracuje. V buňce A1 máme například jméno i příjmení. Pro práci v jiné buňce (př. A3) bychom ale rádi uvítali jen příjmení dotyčného. Musíme tedy začít psát do buňky A3 následující vzorec: „=ČÁST(A1;7;10)“. Jméno i příjmení je v buňce A1, proto píšeme za argument „text“ adresu buňky A1. Příjmení začíná až na 7 pozici (pozor, počítá se i mezera – prázdný znak), proto píšeme 7 za argument „start“ a příjmení obsahuje 10 znaků, takže za poslední argument „počet_znaků“ píšeme 10.
43
Funkce EXCEL II
© Jaroslav Nedoma
Po stisknutí klávesy ENTER vidíme výsledek. Tím bude pouze příjmení (př. Doležalová).
-
Další příklady této funkce jsou zbytečné, protože po předchozím příkladu by jste měli být schopni rozdělit jakoukoli buňku na více částí.
44
Funkce EXCEL II
© Jaroslav Nedoma
Speciální znaky Úkolem této dodatkové kapitoly je seznámit Vás s napsáním jednotlivých speciálních znaků užívaných v této příručce pro správný chod všech funkcí.
Znak
Napsání na klávesnici
;
klávesa pod Esc
“
SHIFT + ů
:
SHIFT + .
<
AltGr + ,
>
AltGr + .
&
AltGr + C
45
Funkce EXCEL II
© Jaroslav Nedoma
Klávesové zkratky Zde je ještě výčet nejpoužívanějších klávesových zkratek v Excelu. Zkratka
Činnost
CTRL + A
Označit vše
CTRL + C
Kopírovat
CTRL + X
Vyjmout
CTRL + V
Vložit
CTRL + B
Tučné písmo
CTRL + I
Kurzíva
CTRL + U
Podtržené písmo
CTRL + P
Tisk
CTRL + O
Otevřít dokument
CTRL + U
Uložit
CTRL + Z
Krok zpět
ALT + F4
Zavřít
46
