Informatika a výpočetní technika

Informatika a výpočetní technika kompendium Pavel Navrátil

Příloha Přehled funkcí v Excelu 2003 F05KAR-Prehled_funkci_Excel2003.pdf

Nakladatelství a vydavatelství

Vzdìlávání, které baví www. computermedi a. cz

R

Informatika a výpočetní technika • kompendium

F

Tabulkový procesor - příloha 05 - Přehled funkcí v Excelu 2003

PŘEHLED FUNKCÍ SEZNAM VZORCŮ A JEJICH VÝKLAD Poznámka: V této příloze NEJSOU popsány všechny funkce Excelu. Příloha je zaměřena hlavně na vzorce, které se v praxi vyskytují nejčastěji. Pohledy na podobu funkcí (vždy prostřední sloupec) jsou otištěny ve verzi vzhledu „Windows XP“. Upozornění: Pokud vytvoříte tabulku v české verzi Excelu a otevře ji někdo v anglické verzi, bude tabulka a tedy i všechny vzorce fungovat i když se budou jmenovat jinak.

Matematické funkce ABS Popis funkce: Vrátí absolutní hodnotu čísla (tj. vždy kladné číslo). Obecný zápis ABS(číslo) Příklad zápisu =ABS(A1) Ukázka v praxi Zdrojové buňky

Pohled na funkci v podobě průvodce

Výsledek funkce

COS Popis funkce: Vrátí kosinus čísla. Obecný zápis COS(číslo) Příklad zápisu =COS(A1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

SIN Popis funkce: Vrátí sinus čísla. Obecný zápis SIN(číslo) Příklad zápisu =SIN(C5) Ukázka v praxi stejná jako u funkce COS.

TG Popis funkce: Vrátí tangens čísla. Obecný zápis TG(číslo) Příklad zápisu =TG(C5) Ukázka v praxi stejná jako u funkce COS.

CELÁ.ČÁST Popis funkce: Vrátí celé číslo zaokrouhlené na nejbližší nižší číslo dolů. Např. pokud bude vzorec aplikován na číslo 1,86, vzorec vrátí 1. Obecný zápis CELÁ.ČÁST(číslo). R

2

Tabulkový procesor - přehled funkcí v Excelu 2003

F Příklad zápisu =CELÁ.ČÁST(A1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

LN Popis funkce: Vrátí přirozený logaritmus čísla. Obecný zápis LN(číslo) Příklad zápisu =LN(A1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

ODMOCNINA Popis funkce: Vrátí odmocninu čísla na zadaný exponent. Obecný zápis ODMOCNINA(číslo) Příklad zápisu =ODMOCNINA(A1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

POWER Popis funkce: Vrátí mocninu čísla. Obecný zápis POWER(číslo;mocnina) Příklad zápisu =POWER(A1;B1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

Do dialogu Číslo v průvodci se zadává adresa buňky čísla, které bude mocněno a do dialogu Mocnina se zadává adresa buňky, která obsahuje samotnou mocninu.

SOUČIN Popis funkce: Vynásobí mezi sebou zadané hodnoty (těch může být max. 30). Obecný zápis SOUČIN(číslo1;číslo2;číslo3;…) Příklad zápisu =COS(A1;A2;B1)

R

3


F Ukázka v praxi Zdrojové buňky


Výsledek funkce

SUBTOTAL Popis funkce: Funkce z rozsáhlejšími možnostmi. Jedná se o funkci, která „přebírá“ podle zadaného kódu výpočet některé z jedenácti jinak samostatných funkcí. Podle toho jaký kód bude zadán, taková funkce bude provedena. Kódy mohou být následující: 1 PRŮMĚR vrátí průměrnou hodnotu ze seznamu zadaných buněk 2 POČET vrátí počet buněk obsahujících čísla ze zadaného seznamu 3 POČET2 vrátí počet neprázdných buněk seznamu (mohou obsahovat cokoliv) 4 MAX vrátí maximální hodnotu v seznamu zadaných buněk 5 MIN vrátí minimální hodnotu v seznamu zadaných buněk 6 SOUČIN vynásobí vzájemně zadané buňky 7 SMODCH.VÝBĚR odhadne směrodatnou odchylku 8 SMODCH vypočítá směrodatnou odchylku 9 SUMA sečte hodnoty ze zadaných buněk 10 VAR.VÝBĚR vypočte odhad rozptylu 11 VAR vypočte rozptyl Obecný zápis SUBTOTAL(kód;hodnota;hodnota;…) Příklad zápisu =SUBTOTAL(9;A1:A3) Ukázka v praxi Zdrojové buňky


Výsledek funkce

Přesto, že funkce SUBTOTAL vlastně supluje jiné funkce, používá se proto, že pomocí čísla je možné zadat, jaké funkce bude suplovat. Toho občas uživatelé využívají při složitějším programování rozsáhlých a složitých tabulek.

SUMA Popis funkce: Sečte oblast buněk. Jedná se o nejpoužívanější funkci Excelu. Má i samostatnou ikonu na panelu nástrojů. Obecný zápis SUMA(oblast) Příklad zápisu =SUMA(A1:A4)

R

4




Výsledek funkce

Do dialogu Číslo v průvodci se zadává adresa buňky čísla, které bude mocněno a do dialogu Mocnina se zadává adresa buňky, která obsahuje samotnou mocninu.

SUMIF Popis funkce: V zadané oblasti provede součet pouze těch čísel, která splňují zadané kritérium. Ostatní čísla v oblasti sečtena nebudou. Obecný zápis SUMIF(oblast;kritéria;součet) Příklad zápisu =SUMIF(A1:A4;“>10“;B1:B5) Ukázka v praxi Zdrojové buňky


Výsledek funkce

Na ukázce je funkce nastavena tak, že ze zadané oblasti (A1:A4) bude sčítat pouze ty buňky, jejichž obsah je větší než číslo 10.

ZAOKROUHLIT Popis funkce: Zaokrouhlí desetinné číslo na zadaný počet desetinných míst. Obecný zápis ZAOKROUHLIT(číslo;početmíst) Příklad zápisu =ZAOKROUHLIT(A1,2) Ukázka v praxi Zdrojové buňky


Výsledek funkce

Statistické funkce COUNTBLANK Popis funkce: Zjistí a vrátí počet prázdných buněk v zadané oblasti buněk. Obecný zápis COUNTBLANK(oblast) Příklad zápisu =COUNTBLANK(B1:B6)

R

5




Výsledek funkce

COUNTIF Popis funkce: Zjistí a vrátí počet buněk, které splňují zadané kritérium (podmínku). Obecný zápis COUNTIF(oblast;kritérium) Příklad zápisu =COUNTIF(A1:A5;“>4“) Ukázka v praxi Zdrojové buňky


Výsledek funkce

MAX Popis funkce: Vrátí maximální hodnotu ze zadaného seznamu buněk. Obecný zápis MAX(oblast) Příklad zápisu =MAX(A1:D1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

MIN Popis funkce: Vrátí minimální hodnotu ze zadaného seznamu buněk. Obecný zápis MIN(oblast) Příklad zápisu =MIN(A1:D1) Ukázka v praxi je stejná jako u funkce MAX

MODE Popis funkce: Vrátí hodnotu, která se v zadaném seznamu buněk objevuje nejčastěji. Podmínkou je, aby se v zadané oblasti alespoň jedna hodnota vyskytovala nejméně 2x. Jinak funkce vrátí N/A. Obecný zápis MODE(oblast) Příklad zápisu =MODE(A1:C3)

R

6




Výsledek funkce

POČET Popis funkce: Vrátí počet buněk, které v zadaném seznamu buněk obsahují číslo. Buňky s textem se do počtu nezapočítávají. Obecný zápis POČET(oblast) Příklad zápisu =POČET(A1:A5) Ukázka v praxi Zdrojové buňky


Výsledek funkce

POČET2 Popis funkce: Vrátí počet buněk, které v zadaném seznamu nejsou prázdné. V tomto případě se do počtu započítávají i buňky obsahující text. Obecný zápis POČET2(oblast) Příklad zápisu =POČET2(A1:A5) Ukázka v praxi Zdrojové buňky


Výsledek funkce

PRŮMĚR Popis funkce: Vrátí aritmetický průměr čísel ze zadané oblasti buněk. Obecný zápis PRŮMĚR(oblast) Příklad zápisu =PRŮMĚR(A1:A4) Ukázka v praxi Zdrojové buňky


Výsledek funkce

R

7


F Databázové funkce Logika zadávání databázových funkcí je mírně odlišná od způsobu, jakým se funkce zadávají běžně. Databázové funkce vždy musí operovat s nějakou databází, tj. tabulkou s daty. Dále je nutné vytvořit kritéria, opět nejlépe v rámci nějaké tabulky. Všechny vysvětlované funkce této oblasti se proto budou vztahovat na následující tabulku. Horní tabulka tvoří hlavní databázi, na kterou se databázové funkce budou vztahovat. Spodní tabulka (se stejnou hlavičkou) slouží pro funkce výpočtu z hlavní (horní) databázové tabulky. Pozor, pomocnou tabulku s kritérii není nutné vytvářet, pokud má funkce prohledávat celou databázi a z ní vytvořit výsledek. V takovém případě ale na výpočty postačí i klasické funkce, takže funkce databáze by postrádaly smysl. Kdy tedy použít databázové funkce? Například tehdy, když budete v databázi stromů chtít zjistit, jaký je nejvyšší výdělek z jabloně. Nebo pokud budete chtít zjistit průměrné stáří všech broskvoní. Databázové funkce se hodí tam, kde potřebujete v databázi vyhledat jednu položku, která se zde vyskytuje vícekrát a s touto pracovat. Tyto operace lze mimo jiné nahradit i filtry.

DMAX Popis funkce: Vrátí maximální hodnotu ze sloupce ze zadané databáze, které splňují požadovaná kritéria. Obecný zápis DMAX(databáze;sloupec;kritéria) Příklad zápisu =DMAX(A1:D10;2;A14:A16) Ukázka v praxi Pohled na funkci v podobě průvodce

Výsledek funkce

Na příkladu byla jako databáze definovaná celá tabulka (A1:D10). Hledat se bude ve druhém sloupci (pole = stáří stromu). Kritériem jsou stromy jabloň a hrušeň, což je vidět v tabulce s kritérii, která je umístěna pod tabulkou s daty (proto jsou zadány rozsahy buněk A14:B16).

DMIN, DPOČET, DPRŮMĚR, DSUMA, … Všechny ostatní funkce databáze se zadávají naprosto stejně jako uvedená funkce DMAX. Jejich význam je rovněž stejný jako u funkcí statistických, pouze se vztahují na celou databázovou tabulku.

Datumové a časové funkce DNES Popis funkce: Vrátí do buňky aktuální datum. Funkce nevyžaduje žádné argumenty. Obecný zápis DNES() Příklad zápisu =DNES() R

8


F Ukázka v praxi Pohled na funkci v podobě průvodce

Výsledek funkce

NYNÍ Popis funkce: Vrátí do buňky aktuální čas. Funkce nevyžaduje žádné argumenty. Čas se v buňce automaticky aktualizuje i po vložení, tj. v buňce je zobrazen stále aktuální čas. Obecný zápis NYNÍ() Příklad zápisu =NYNÍ() Ukázka v praxi Pohled na funkci v podobě průvodce

Výsledek funkce

Vyhledávací funkce Vyhledávací funkce jako takové nevypočítají žádnou hodnotu. Jsou určeny především pro zjištění informace o buňce, tj. o pozici buňky, řádku, sloupce apod. Tento typ funkcí se používá většinou v souvislosti s jinými funkcemi nebo jako součást makra či složitějšího projektu. Například funkce pro zjištění pozice buňky by v klasické tabulce asi příliš užitku nepřinesla.

INDEX Popis funkce: Vrátí obsah buňky na přesně stanovenou pozici. Funkce vyžaduje přesnou specifikaci pozice buňky, jejíž obsah má být zadán (může být i obecná, odkazující se na výsledek jiné funkce). Obecný zápis INDEX(oblast;řádek;sloupec) Příklad zápisu =INDEX(A1:A4;3;1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

POZVYHLEDAT Popis funkce: Vyhledá a vrátí číslo řádku ze seznamu, na kterém se nachází zadaná hodnota. Máte-li sloupec, který obsahuje spoustu čísel a chcete-li zjistit, na kterém řádku se nachází jedno konkrétní číslo, pak použijte tuto funkci. Obecný zápis POZVYHLEDAT(hodnota;oblast) Příklad zápisu =POZVYHLEDAT(10;A1:A4) R

9




Výsledek funkce

Funkce pro práci s texty Textové funkce jsou určené pro práci s textem. Nezapomínejte ale na to, že i textové funkce potřebují buňku, ve které budou provedeny. Jinými slovy, není možné například sloučit obsah dvou buněk do jedné, aniž byste nevyužili další buňky pro vzorec s touto funkcí.

CONCATENATE Popis funkce: Sloučí jednotlivé textové položky do jednoho obsahu. Obecný zápis CONCATENATE(buňka1;buňka2, …) Příklad zápisu =CONCATENATE(A1;B1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

DÉLKA Popis funkce: Vrátí délku řetězce v zadané buňce. Obecný zápis DÉLKA(buňka) Příklad zápisu =DÉLKA(A1) Ukázka v praxi Zdrojové buňky


Výsledek funkce

HLEDAT Popis funkce: Najde a zobrazí pozici prvního písmene, které se shoduje se zadaným vzorem. Hledání probíhá zleva doprava. Velká a malá písmena se nerozlišují. Obecný zápis HLEDAT(text;buňka) Příklad zápisu =HLEDAT(„Karel“;A1) R

10




Výsledek funkce

STEJNÉ Popis funkce: Porovná dva řetězce v určených buňkách. Jako výsledek zobrazí buď „PRAVDA“ v případě, že se texty v buňkách shodují a nebo „NEPRAVDA“ v případě, že se texty neshodují. Obecný zápis

STEJNÉ(buňka1;buňka2)

Příklad zápisu

=STEJNÉ(A1;A2)

Ukázka v praxi Zdrojové buňky


Výsledek funkce

Logické funkce Logické funkce používají logické hodnoty. Jedná se především o hodnoty PRAVDA a NEPRAVDA. Pozor, pokud do buňky napíšete text PRAVDA, nebude Excel brát tuto buňku jako buňku textovou s textem PRAVDA, ale jako buňku logickou s hodnotou PRAVDA. Rozdíl je v tom, že text „pravda“ sám o sobě nic nevyjadřuje, zatím co logická hodnota NEPRAVDA může být výsledkem vyhodnocení nějaké funkce, event. podkladem pro zpracování v dalším vzorci.

A Popis funkce: Vrátí výsledek logické funkce AND. Jinými slovy tato funkce se používá pro vyhodnocení výsledků jiných funkcí, jejichž výsledkem je buď hodnota PRAVDA nebo NEPRAVDA. Pokud všechny buňky, se kterými tato funkce pracuje mají hodnotu PRAVDA, pak funkce také vrátí hodnotu PRAVDA. Pokud se alespoň v jedné ze všech buněk, se kterými tato funkce pracuje, vyskytne jakákoliv jiná hodnota než PRAVDA, pak tato funkce vrátí hodnotu NEPRAVDA. Tato funkce se dobře hodí např. pro kompletní analýzu výsledků apod. Obecný zápis A(buňka1; buňka2; buňka3; …) Příklad zápisu =A(A1;A2;A3) Ukázka v praxi Zdrojové buňky


Výsledek funkce

R

11


F KDYŽ Popis funkce: Funkce KDYŽ je podmínka. Jedná se o funkci, která se na základě vypočtených hodnot a nastavených vztahů „sama“ dokáže rozhodnout kterým ze dvou směrů bude pokračovat. Obecný zápis KDYŽ(podmínka;ano;ne) Příklad zápisu =KDYŽ(A1=5;“dostačující“;“není v pořádku“) Ukázka v praxi Zdrojové buňky


Výsledek funkce

V uvedeném příkladu se měli tři děti složit na dárek, který stojí 40,- Kč. Podmínka „spočítala a napsala“, zda děti na dárek mají nebo ne.

NEBO Popis funkce: Vrátí výsledek logické funkce OR. Jinými slovy, tato funkce se používá pro vyhodnocení výsledků jiných funkcí, jejichž výsledkem je buď hodnota PRAVDA nebo NEPRAVDA. Pokud alespoň jedna buňka ze seznamu buněk má hodnotu PRAVDA, pak tato funkce také vrátí hodnotu PRAVDA. Pokud ale všechny buňky mají hodnotu NEPRAVDA, pak také vrátí hodnotu NEPRAVDA. Obecný zápis NEBO(buňka1; buňka2; buňka3;…) Příklad zápisu =NEBO(A1;A2;A3;A4) Ukázka v praxi Zdrojové buňky


Výsledek funkce

Informační funkce POLÍČKO Popis funkce: Zobrazí informace o buňce. Je možné nechat zobrazit informaci o formátu buňky, barvě pozadí, adrese, šířce atd. Vše pomocí speciálních parametrů. Obecný zápis POLÍČKO(buňka) Příklad zápisu = POLÍČKO(A1) Parametry mohou být mimo jiné následující:

12

adress

odkaz na první oblasti

col

vrátí číslo sloupce buňky

protect

vrátí 1, pokud je buňka zamčena a 0, pokud buňka není zamčena

row

číslo řádku buňky v oblasti

width

šířka sloupce zaokrouhlená na celé číslo

R




Výsledek funkce

O.PROSTŘEDÍ Popis funkce: Podá informaci o prostředí, ve kterém momentálně pracujete. Ve funkci je nutné zadat parametr, který určuje, co za informaci chcete získat. Obecný zápis O.PROSTŘEDÍ(parametr) Příklad zápisu = O.PROSTŘEDÍ(parametr) Parametry mohou být mimo jiné následující: directory

cesta k aktuálnímu adresáři (resp. složce)

memavail

množství volné použitelné paměti

numfile

počet aktivních listů

osversion

verze aktuálního operačního systému

recalc

způsob nastaveného přepočítávání tabulky (ruční nebo automatické)

Ukázka v praxi Pohled na funkci v podobě průvodce

Výsledek funkce

Upozornění: Mnohé funkce jsou tak jednoúčelové, že si možná položíte otázku, k čemu taková funkce může být využita (např. vyhledání prvního znaku v zadaném řetězci). Faktem je, že právě ty funkce, které jsou úzce jednoúčelové se často používají jako součásti složitějších funkcí a výpočtů či analýz a úplně nejčastěji jako součásti maker.

R

13

Informatika a výpočetní technika

Recommend Documents