Vzdělání Ti pomůže ve všech nepřízních života
Výpočty v Excelu
Doporučená učebnice: www.ssssebrno.cz (Navrátil: S počítačem nejen k maturitě)
E-learning:
https://ki.ujep.cz/data/enastenka/excel.pdf
www.praha10.cz/Portals/0/MS%20Excel%20pokročilí.doc
https://vyuka.ic.cz/tab_proc/index.htm
https://www.spsemoh.cz/vyuka/ms-office/ex1_2007.pdf
https://office.lasakovi.com/excel/prakticke-priklady/
https://excel-navod.fotopulos.net/index.html
https://www.jaknaoffice.cz/7-excel/28-excel-2010/74-funkce-v-excelu/
https://www.pcworld.cz/software/10-tajnych-tipu-pro-efektivnejsi-praci-v-excelu-9-dil-43833
https://sites.google.com/site/krejciladka/
Co máte umět:
- principy funkce a oblasti využívání tabulkových procesorů, struktura tabulky
- editace a plnění buněk
- formátování vzhledu tabulky, slučování buněk, práce s řádky a sloupci
- změna formátu v buňce (obecný formát, měna, datum, procenta, desetinná místa atd.)
- formátování celkového vzhledu tabulky s využitím pokročilých voleb automatického formátu a stylů
- použití podmínek podmíněného formátování
- základní vzorce a funkce (součet, průměr, maximum, minimum, atd.)
- složitější a vnořené funkce
- relativní a absolutní adresace, adresace buněk jiného listu a sešitu
- tvorba a editace grafů, výběr vhodného typu grafu, úpravy jednotlivých oblastí grafu
- pojmy záznam a pole
- filtrování a řazení dat
- formuláře, souhrny, kontingenční tabulky
- zadání ověření rozsahu dat při jejich zadávání a výběr pouze připravených možností z vytvořeného seznamu
- zamknutí buněk a celého sešitu
- export a import dat
- načtení (importovat) tabulky nebo seznamu z jiného formátu (CSV, DBF, PDF)
Výuková videa
- Funkce Hledání řešení
- Goniometrické funkce
- Kombinatorika
- Kontingenční tabulka (malá)
- Kontingenční tabulka (velká)
- Logické a vnořené funkce
- Nastavení data a času
- Práce s listy
- Tisk dokumentu
Prostředí MS Excelu
Pracovní plocha tabulkového procesoru (tabulka) je rozdělena mřížkou na jednotlivé buňky. Každá buňka je v tabulce jednoznačně určena svojí adresou, např. buňka s adresou A1 se nachází ve sloupci A na 1. řádku. Pojmy, které budete používat:
- Panel snadné spuštění
- Pásy karet
- Pole názvů
- Řádek vzorců
- Horizontální a vertikální rozdělení okna
- Lupa
- Různá pracovní zobrazení
- Stavový řádek
- Listy a jejich ovládání
Pomocí záložek aktivního listu (dole) se můžeme přepínat mezi několika tabulkami v jediném souboru (jako bychom do souboru ukládali několik papírů s tabulkami, které leží na sobě).
Nad pracovní oblastí je vstupní řádka která slouží ke vkládání dat a vzorců do buněk. Text a čísla je samozřejmě možné vkládat rovnou do buněk v pracovní oblasti. Vzorec, funkci nebo nějaký komplikovanější údaj zadáváta přes vstupní řádek. Údaje z aktivní buňky lze editovat ve vstupní řádce.
Pohyb po tabulce
Aktivní buňka tabulky je vyznačena silným černým orámováním a zvýrazněním záhlaví řádku a sloupce, ve kterém se nachází.
Chceme-li zvolit jako aktivní jinou buňku, použijeme kurzorové klávesy nebo na danou buňku klikneme levým tlačítkem myši.
Potřebujeme-li se dostat mimo zobrazenou oblast pracovní plochy, použijeme klávesy PgUp a PgDn pro pohyb nahoru/dolu nebo posuvníky. Ctrl +Home, Ctrl+End ...a další přesouvací klávesy známé z Wordu. Na buňku s určitou adresou se také můžeme přesunout pomocí pole názvů oblastí a buněk.
Vkládání dat do tabulky
Do jednotlivých buněk v listu tabulkového sešitu Excel je možné zadávat následující typy informací:
- Číselné hodnoty vkládáte bez mezer, diakritiky a znaků a zobrazujete například: 22 000, 29,95 Kč nebo 33 %
- Textové hodnoty, například Součet, První čtvrtletí nebo 602 00 Brno
- Datum a čas, například únor 2003, 13. 8. 1977, 19.2.2013 obsahují 2 tečky a letopočet
- Různé komentáře v textové podobě a hlasové poznámky
- Vzorce, začínají vždy =. například =B5*1,081 nebo =SUMA(B3:B7)
- Hypertextové odkazy na místa v Internetu nebo na jiné dokumenty (např. www.seznam.cz)
- Grafické objekty, jako například kliparty, skenované fotografie, mapy a ilustrace
Poté, co do buňky vložíte jakýkoli data, proběhne jejich kontrola. Pokud vložíte text, buňka se automaticky zarovná vlevo. Pokud vložíte jakékoli číslo, buňka se opět automaticky zarovná, nyní vpravo. snadno poznáte, které buňky obsahující čísla jsou ve skutečnosti texty.
Dodržujte zásady vkládání dat !!!
Označování a přesuny buněk
Buňky se označují nejčastěji myší. Stačí přidržet levé tlačítko myší a táhnout požadovaným směrem. Alternativou může být označování přes klávesnici, kdy lze použít stisk SHIFT+kurzorové šipky. Pro nesouvislé označování buněk nebo oblastí buněk přidržíte CTRL a klasicky označujete.
Pro přesun buňky nebo skupiny buněk stačí buňku nebo skupinu chytit za hranu a přesunout na požadované místo. Pro přesun na jiný list si lze pomoci přidržením klávesy ALT.
Šířka sloupců, výška řádků
Šířku sloupců a výšku řádků změníte přetažením za přechodové místo mezi sloupcem nebo řádkem. Pro optimalizaci šířky nebo výšky vůči obsahu lze na přechodové místo provést dvojklik
Přesnou výšku řádku nebo šířku sloupce lze provést kliknutím pravou myší na záhlaví řádku nebo sloupce
Přidávání, odebírání řádků a sloupců
Řádky se vždy vkládají nad pozici aktuální buňky, sloupce zase vždy nalevo do pozice aktuální buňky. Lze tak provést např. klávesovou zkratkou CTRL+Plus, pro odstranění zase CTRL+Minus. Jiné varianty těchto operací jsou přes místní nabídku v záhlaví řádku nebo sloupce, případně na kartě Domů, ve skupině Buňky, příkazy Vložit a Odstranit (Home, Cells, Insert and Delete)
Práce s listy
Mezi listy se přepínáte v pravé dolní části obrazovky. Listy lze dvojklikem na název (ouško) přejmenovat, případně pravou myší na
názvu přebarvit přes příkaz Barva Karty. List lze uchopením za jeho název přesunout, případně s přidrženou klávesou CTRL vytvořit jeho identickou kopii. Pro vložení nového listu použijte ikonu na konci seznamu listů nebo klávesovou kombinaci SHIFT+F11
Otevírání souborů a práce s šablonami
Pro otevírání excelového souboru lze použít nabídku Soubor, volbu Otevřít (File, Open), kde lze snadno vybrat příslušný soubor. Pokud máte v úmyslu vytvářet nový excelový soubor, je vhodné nahlédnout do šablon. Šablony jsou připravené vzory dokumentů nebo tabulek, do kterých zadáte vlastní údaje. Do přehledu šablon se dostanete přes nabídku Soubor, volbu Nový a zde vyberete buď z již nainstalovaných šablon, nebo z šablon, které jsou k dispozici on-line na webu Microsoftu.
Práce se Schránkou
Pro kopírování mezi Excelem a dalšími aplikacemi lze použít běžné zkratky CTRL+C (kopírování), CTRL+X (vyjmutí) a CTRL+V (vložení).
Zadávání vzorců
Vzorec je matematický vztah, podle něhož se vypočítává nová hodnota ze stávajících veličin.
Zapíšeme-li do buňky určitý vzorec, zobrazí se v sešitu Excel jeho výsledek. Vzorce mohou obsahovat čísla, matematické operátory, odkazy na buňky a vestavěné funkce, které vypočítávají určitou hodnotu. Každý vzorec v Excelu začíná symbolem rovnítka (=), které vyjadřuje začátek matematického výpočtu.
Operátory:
Excel ukládá do buňky původní podobu vzorce; tu ale uvidíme jen v řádku vzorců, zatímco na místě buňky se ukazuje výsledek. Do vzorců se dají zapisovat :
Matematické operátory, jako sečítání (+), odečítání (-), násobení (*), dělení (/), umocňování (^ ALT+94), průniku.
Textový operátor - & (znak ampersand) - spojuje dvě hodnoty a vytváří jednu textovou hodnotu.
Relační operátory:
> znaménko větší než
< znaménko menší než
>= znaménko větší než nebo rovno
<= znaménko menší než nebo rovno
<> znaménko nerovná se
Operandy ve vzorcích
Vzorce mohou obsahovat také odkazy na buňky – například A1 nebo B5 – které se chovají podobně jako neznámé v matematickém vztahu a jež můžeme kombinovat s ostatními prvky (čísly, operátory a funkcemi). Odkaz na buňku můžeme zadat takto:
- Přímým zápisem textu, například B5
- Zvýrazněním buňky pomocí myši
- Zvýrazněním buňky pomocí klávesnice (například šipkou, se kterou posuneme výběr na buňku)
Takto například zapíšeme jednoduchý součet dvou buněk =B6+C6
Součástí vzorce může být také oblast buněk, např.: A3:A9.
Každý nový odkaz se zvýrazní jinou barvou okraje a přehledně tak naznačuje skladbu celého vzorce; odkazovanou buňku snadno změníme přetažením. Stiskem klávesy Enter dokončíme zadávání vzorce a Excel vrátí do buňky výsledek vzorce.
Dalšími operandy může být číslo, datum, čas, pojmenovaná oblast, 3D oblast, buňky jiné tabulky, ...atd.
Při tvorbě vzorců dodržte následující postup:
- Vyberte buňku, kde potřebujete provést výpočet
- Vložte = (rovná se)
- Označte nejlépe myší číslo (nebo jej zapište z klávesnice nebo uveďte adresu čísla)
- Vložte potřebný aritmetický, porovnávací nebo textový operátor (*,-,+,/,…)
- Označte nejlépe myší druhé číslo (nebo jej zapište z klávesnice nebo uveďte adresu čísla)
- Kroky 4 a 5 opakujte
Výpočet ukončete stisknutím klávesy Enter
Kopírování vzorců
Vzorce se dají kopírovat do plochy buněk několika způsoby.
- Tažením za pravý dolní úchopový bod (ouško) aktivní buňky;
- Označením oblasti buněk s budoucími výpočty, zadáním výpočtu do aktivní buňky a ukončením klávesovou kombinací CTRL + Enter
- Přes schránku, např. za pomoci klávesových zkratek CTRL +C a CTRL +V.
- Dvojklikem na pravý dolní úchopový bod buňky (funguje pouze, pokud se vzorec „dotýká“ vstupní tabulky
Řady v tabulce
Začneme tím, že např. první dvě buňky vyplníme hodnotami (např. 1 a 2). Pak obě označíme (např. známým způsobem myší nebo Shift + šipky). Chytíme myší černý čtvereček v pravém dolním rohu orámování buněk (ouško buňky) a táhneme dolu. Údaje v řadě se (pokud to jde) automaticky doplňují.
Po dokončení tažení si můžeme ještě z místní nabídky vybrat, zda chceme celou řadu vyplnit jednou hodnotou (podle na začátku označených buněk) - Kopírovat buňky nebo zda necháme Excel doplnit za nás hodnoty řady - Vyplnit řady. Také můžeme do buněk zkopírovat jen parametry formátování - Pouze vyplnit formáty, tj. nevkládat data, jen informace o typu a velikosti písma, apod. Také je možné vložit data a formátování nepřenášet - Vyplnit bez formátování.
Údaje z buněk odstraňujeme klávesou Delete. Označíme-li předtím více buněk najednou, zruší se obsah všech označených buněk.
Mezi předdefinované seznamy a řady patří:
- Dny v týdnu
- Zkratky dnů v týdnu
- Měsíce
- Římské číslování v rozsahu I. – XII.
- Datum
- Číslo
Vlastní řady nadefinujete na záložce Soubor - Možnosti - Upřesnění (seznam jmen žáků ve třídě)
Formát dat
Základní údaj (číslo): |
37256,67 |
Formát: |
|
obecný |
37256,67 |
číslo (dvě desetinná místa) |
37256,67 |
číslo (jedno desetinné místo) |
37256,7 |
číslo dvě desetinná místa, oddělovač tisíců) |
37 256,67 |
měna |
37 256,67 Kč |
účetnický |
37 256,67 Kč |
procenta |
3725667,00% |
zlomky |
37256 2/3 |
text |
37256,67 |
vlastní formát |
37 257,– Kč |
datum - dd.mmmm.rrrr |
31. prosinec 2001 |
mmmm rr |
prosinec 01 |
dddd.dd.rr |
31.12.01 |
dd-mm. |
31-12. |
Rychlý způsob volby formátu čísla
Vložení rovnice jako objektu
Pokud píšete běžný text, nemůžete rychle vložit rovnici kliknutím. Zkuste zmáčknout Alt+= aby se editor rovnic v prostředí Excel 2007/20010 aktivoval. Zde napište rovnici pomocí nástrojů, které se vám zpřístupní.
Zápis zlomků a indexy
Asi nejčastější věcí, kterou budeme potřebovat, jsou horní a dolní indexy a psaní zlomků. Stačí napsat a/b a po stisknutí mezerníku se vytvoří zlomek. Zápis n^2 udělá z dvojky horní index a zápis k_i udělá z i dolní index. Nebo můžete číslici zvýraznit a použít formát písma ve Vlastnostech buňky - Písmo.
Pár užitečných tipů
# |
$ |
% |
& |
@ |
< |
> |
\ |
^ |
{ |
} |
€ |
§ |
‰ |
≤ |
≥ |
° |
35 |
36 |
37 |
38 |
64 |
60 |
62 |
92 |
94 |
123 |
125 |
0128 |
0167 |
0137 |
2264 |
2265 |
0176 |
Jak má tabulka vypadat ?
Ideální excelová tabulka by měla mít záhlaví, tedy řádek, který jasně pojmenovává, jaký typ dat se v konkrétním sloupci nachází. Záhlaví by mělo být vždy na jeden řádek. Pokud bude na více, excel jako záhlaví vždy identifikuje pouze řádek první.
Tabulky s daty by mezi sebou neměly mít prázdné řádky nebo sloupce.
Rychlé formátování tabulky
Pro rychlé formátování stačí být kdekoli v tabulce a zvolit příkaz Formátovat jako tabulku který se nachází na kartě Domů ve skupině příkazů Styly
Jak použít formáty tabulek pro data v buňkách listu Excelu ?
Použijete-li příkaz Formátovat jako tabulku, který se nachází v pásu karet na záložce Domů, můžete tak vzhledně naformátovat v Excelu oblast buněk. Tento příkaz přidává k záhlaví sloupců záhlaví a mění chování sloupců i řádků, které s tímto seznamem sousedí. Chcete-li využívat pohodlného a jednoduchého formátování tabulek jedním klepnutím myši, ale přitom současně nechcete využívat žádných jiných funkcí, můžete tyto nadbytečné funkce odebrat.
Konkrétně postupujte tak, že nejprve oblast buněk, kterou budete chtít naformátovat v podobě tabulky, vyberete, a to včetně řádku se záhlavím. Poté na tuto oblast buněk použijte požadovaný formát tabulky, a to pomocí příkazu Formátovat jako tabulku, který se nachází na zmíněném pásu karet na záložce Domů.
Abyste odstranili všechny ostatní funkce, které Excel automaticky přidává do oblasti buněk s daty, ponechte tuto oblast buněk vybranou. Následně klepněte na nabídku Nástroje tabulky a poté na záložku Návrh, kde vyberte položku Převést na rozsah. Když se vás Excel zeptá, zdali chcete převést tabulku na normální oblast, odpovězte. Tato operace odstraní ze záhlaví sloupců filtry a odstraní i veškeré další funkce spojené s tabulkou. Na druhou stranu však formátování ponechá.
Styly buněk
Styl buňky je uložené formátování, často rozsáhlejší, které lze snadno aplikovat výběrem z nabídky. Styly buněk jsou k dispozici na kartě Domů, skupina příkazů Styly, kde je lze vybírat, případně vytvářet vlastní.
Podmíněné formátování
Podmíněné formátování je velmi jednoduchým nástrojem pro zvýrazňování klíčových údajů v buňkách. Pro správné použití je vždy nutné vybrat buňku nebo oblast buněk. Následně stačí na kartě Domů, ve skupině příkazů Styly aktivovat tlačítko Podmíněné formátování
Lze vytvářet vlastní podmínky, které nebudou závislé na obsahu buněk, ale na výsledcích vzorců. Tyto vlastní formáty lze vytvářet přes tlačítko Nové pravidlo v základní nabídce nebo přes Správce pravidel Mezi další praktické vlastnosti patří možnost podmíněně naformátovat (zvýraznit) duplicitní hodnoty a hodnoty nad průměrem a pod průměrem.
Adresace buněk
Při tvorbě vzorců nebo funkcí je nutné znát typy adresování buněk. Tato znalost usnadní a urychlí tvorbu vzorců a umožní jejich kopírování.
Relativní adresování buněk je výchozí. Relativně adresovaný vzorec si při kopírování vezme adresy buněk směrem a vzdáleností jako v 1.buňce.
Absolutně adresovaná buňka se ve vzorci při kopírování nemění. Např. v rozpočtu lze snadno všechny vzorce ovlivnit hodnotou kurzovního lístku nebo jinou konstantou absolutně adresovanou.
Smíšená adresace buněk způsobuje, že při změně buňky ve vzorci se změní pouze jedna část adresy a druhá zůstává konstatní. Měnit se může buď řádek nebo jenom sloupec.
Typ adresace ovlivníte přidáním znaku $ (dolaru). Je možné jej nejjednodušeji přidat funkční klávesou F4. Do buňky vložíte rovnítko, určíte první vstupní buňku, a pokud má být absolutně nebo smíšeně adresovaná, zmáčknete F4. Funkční klávesa F4 funguje jako přepínač.
Tvorba grafů
Po označení vstupní tabulky vytvoříte graf příkazem karty Vlořit - Grafy. Excel otevře nabídku formátu grafu
Nový graf lze vytvořit na aktuálním listu nebo na novém listu. Po vytvoření grafu jej můžete snadno formátovat pomocí Nástrojů grafu na kartách Formát, Rozložení a Návrh.
Pokud jste graf vytvoříte na samostatném listu a potřebujete jej mít jako objekt v existujícím listu nebo naopak, změnu provedete v
pomocí tlačítka Umístění.
Každý graf je tvořen skupinou objektů. Mezi objekty patří osy, legenda, popisky, hodnoty a další prvky. Pokud by vám základní formátování ve stylech grafů nevyhovovalo, lze v grafu vybrat jakýkoli prvek a jednoduše jej změnit. Výběr probíhá nejlépe myší a poté stačí na prvku kliknout pravým tlačítkem a zvolit příkaz Formát a příslušný název prvku (např. Formát osy hodnot, formát legendy,formát popisků, ...).
Kombinace několika grafů v jedné grafické sestavě
Klikněte pravou myší na datovou řadu, kterou potřebujete převést na jiný typ grafu a vyberte příkaz Změnit typ grafu řady
Graf s vedlejší osou Y
Pokud potřebujete graficky zobrazovat hodnotově zásadně odlišné údaje, např. koeficienty vůči zaznamenaným údajům, můžete jednu datovou řadu proporčně zvýšit nebo snížit. Toto provedete měřítkem na vedlejší osy. Klikněte pravou myší na datovou řadu, pro kterou chcete mít popisky na vedlejší ose, z místní nabídky vyberte příkaz Formát datové řady a v následném okně zvolte přepínač Vedlejší osa.
Další analytické nástroje pro grafy
Při odhadování budoucích stavů je možno k jednotlivým datovým řadám grafu přidat spojnici trendu. Stačí vybrat datovou řadu a v Nástrojích grafu, na kartě Rozložení ve skupině příkazů Analýza vybrat ikonu Spojnice trendu.
Při vkládání spojnice trendu je často vhodné v grafu zobrazit rovnici regrese, která trend definuje. Pro její zobrazení klikněte pravou myší na zobrazený trend a vyberte příkaz Formát spojnice trendu. V okně následně zatrhněte volbu Zobrazit rovnici regrese.
Přečtěte si "Grafy Excelu" na stránkách:
https://sites.google.com/site/krejciladka/
Vestavěné funkce v Excelu
Inženýrské
OCT2BIN Převede osmičkové číslo na binární.
OCT2DEC Převede osmičkové číslo na desítkové.
OCT2HEX Převede osmičkové číslo na šestnáctkové
BIN2DEC Převede binární číslo na desítkové
DEC2BIN Převededesítkové číslo na binární
BIN2HEX Převede binární číslo na šestnáctkové
BIN2OCT Převede binární číslo na osmičkové.
HEX2BIN Převede šestnáctkové číslo na binární.
HEX2DEC Převede šestnáctkové číslo na desítkové.
HEX2OCT Převede šestnáctkové číslo na osmičkové.
Finanční
ODPIS.ZRYCH Vrátí odpis aktiva za určité období metodou odpisu s pevným zůstatkem.
BUDHODNOTA Vrátí budoucí hodnotu investice.
PLATBA Vrátí hodnotu pravidelné splátky anuity.
ODPIS.LIN Vrátí lineární odpis aktiva pro jedno období.
ODPIS.NELIN Vrátí směrné číslo ročních odpisů aktiva pro zadané období.
Logické
NE, NEBO, PRAVDA, NEPRAVDA
Podmíněné
SUMIF Sečte buňky vybrané podle zadaných kritérií.
COUNTIF Počet buňek vybrané podle zadaných kritérií
KDYŽ Zkoumá stav (buňky, buněk) daný podmínkou a hlásí je-li podmínka splněna nebo nikoli
Příklady:
=KDYŽ(B2>20;B2*C5;B2*C6) Násobí obsah buňky B2 obsahem C5, pokud je splněna podmínka B2>20
=COUNTIF(B2:B5;">55") Počet buněk v oblasti B2 až B5 obsahujících hodnotu větší než 55
=COUNTIF(B2:B5;"<>"&B4) Počet buněk v oblasti B2 až B5 obsahujících hodnotu, která se nerovná hodnotě 75
=COUNTIF(B2:B5;">=32")-COUNTIF(B2:B5;">85") Počet buněk v oblasti B2 až B5 obsahujících hodnotu větší nebo rovnu 32 a menší nebo rovnu 85
=SUMIF(A2:A5;">160000") Součet hodnot vyšších než 160 000 v oblasti A2:A5
=SUMIF(A2:A5;300000;B2:B5) Součet hodnot oblasti B2:B5 s hodnotou rovnou 300 000 v oblasti A2:A5
=SUMIF(A2:A5;">C2";B2:B5) Součet hodnot B2:B5 s hodnotou vyšší, než je hodnota v buňce C2 oblasti A2:A5
Funkce data a času
DATUM Vrátí pořadové číslo určitého data.
DEN Převede pořadové číslo na den v měsíci.
ROK360 Vrátí počet dní mezi dvěma daty na základě roku s 360 dny.
HODINA Převede pořadové číslo na hodinu.
MINUTA Převede pořadové číslo na minutu.
MĚSÍC Převede pořadové číslo na měsíc.
NETWORKDAYS Vrátí počet celých pracovních dnů mezi dvěma daty.
NYNÍ Vrátí pořadové číslo aktuálního data a času.
DNES Vrátí pořadové číslo dnešního data.
Funkce DENTÝDNE Převede pořadové číslo na den v týdnu.
WEEKNUM Převede pořadové číslo na číslo představující číselnou pozici týdne v roce.
WORKDAY Vrátí pořadové číslo data před nebo po zadaném počtu pracovních dnů.
Funkce ROK Převede pořadové číslo na rok.
Matematické
SUMA, POČET, MAX, MIN, PRÚMĚR
POWER Umocní číslo na zadanou mocninu.
DEGREES Převede radiány na stupně.
LN Vrátí přirozený logaritmus čísla.
LOG Vrátí dekadický logaritmus čísla.
NÁHČÍSLO Vrátí náhodné číslo mezi 0 a 1.
RADIANS Převede stupně na radiány.
RANDBETWEEN Vrátí náhodné číslo mezi zadanými čísly.
ROUNDDOWN Zaokrouhlí číslo dolů, směrem k nule.
ROUNDUP Zaokrouhlí číslo nahoru, směrem od nuly.
SOUČIN Vynásobí argumenty funkce.
SUBTOTAL Vrátí souhrn v seznamu nebo databázi.
SUMA.ČTVERCŮ Vrátí součet druhých mocnin argumentů.
ZAOKR.DOLŮ Zaokrouhlí číslo dolů, směrem k nule.
Funkce SIN, cos, tg, cotg, sinh, cosh.... Tyto funkce znáte z matematiky
Statistické
SUMIF, COUNTIF, MAX, MIN,AVERAGE, MEDIAN, PERCENTIL
Batabázové
DMAX, DMIN, DPOČET, DPRÚMĚR, DSUMA, DZÍSKAT
Přečtěte si "Jak na matematické funkce v Excelu" na stránkách:
https://sites.google.com/site/krejciladka/ na záložce Tabulkový procesor.
Finanční funkce
Budhodnota -Vrátí příští hodnotu investice na základě periodických, konstantních splátek a konstantní úrokové míry
Syntax: BUDHODNOTA(sazba;období;splátka;souč_hod;typ). Význam proměnných:
Sazba je úroková sazba vztažená k úročené periodě.
Pper je celkový počet úročených období anuity.
Splátka je splátka za každé období
Platba - Vypočítá platbu půjčky na základě konstantních plateb a konstantní úrokové sazby
Syntax: PLATBA(sazba;pper;souč_hod;bud_hod;typ). Význam proměnných:
Sazba je úroková sazba dané půjčky.
Pper je počet plateb půjčky.
Souč_hod je aktuální hodnota nebo aktuální celková hodnota budoucích plateb.
Odpis (lineární, zkrácený)
Hledání řešení
Nástroj Hledání řešení se pokouší na principu cyklicky opakujících se smyček (iterací) vypočítat vstupy pro výpočet.
Zjednodušeně řešeno, tento nástroj umí např. odpovědět na otázku, jaká by byla pravidelná výše splátky, jestliže si chceme půjčit např. 1 000 000 Kč. Hledání řešení je velmi praktický nástroj.
Tabulka dat
Nástroj Tabulka lze použít pro tzv. citlivostní analýzu. Tento nástroj dokáže analyzovat jeden nebo více vzorců a zobrazit variantní výsledky k různým krokům vstupů (tzv. citlivostním krokům). Nástroj se hodí při rozsáhlých analýzách, kdy šetří čas tvorby variantních výpočtů. Citlivostní analýzu lze provádět s jednou proměnnou, při které je možno analyzovat jeden nebo více výpočtů, nebo pro dvě proměnné, při které lze ale analyzovat pouze jeden výpočet. Citlivostní analýzu předvádíme mj. na postupech výpočtů ukazatelů rentability a dalších.
Excel nabízí mnoho dalších analytických nástrojů. Tyto nástroje ale nejsou běžně přístupné, je nutné je aktivovat. Aktivaci lze provést přes Možnosti aplikace Excel.
Seznamy a databázové funkce
Práce se seznamy
Seznamy jsou tabulky, typicky uspořádané pro jejichž správu nabízí Excel řadu nástrojů. Musí být souvislé (tj. tabulka nesmí být rozdělena prázdným řádkem nebo prázdným sloupcem). Program si celou oblast vybírá sám (až narazí na prázdný řádek nebo sloupec, považuje ho za konec tabulky).
Většina nástrojů pro práci se seznamy se odvolává na názvy sloupců tabulek (popisky sloupců, které musí být jen v jednom řádku). Některé nástroje (např. řazení, názvy sloupců nevyžaduje).
V nástrojích pro práci se seznamy se používá terminologie převzatá z databázových programů. Tabulkám typu seznam se říká nesprávně databáze, sloupce seznamu se označují jako pole, o řádcích se mluví jako o záznamech, jednotlivé buňky jsou označovány slovem položky a záhlaví pole (první buňka ve sloupci) jako název pole.
Třídění v seznamu
Pokud řadíme databázi podle hodnot jednoho pole (např. podle data narození), stačí umístit kurzor kamkoli do dat příslušného sloupce a na kartě Domů, ve skupině Úpravy, klepnout na tlačítko Seřadit a filtrovat (text, číslo, datum, log. položka)
Složitější řazení (např. skupinky podle bydliště pak potřebujeme seřadit podle prospěch), tedy seřadit podle hodnot více polí, pak na kartě Data, ve skupině Seřadit a filtrovat - Seřadit. Excel otevře dialogové okno se třemi rozbalovacími seznamy. V prvním z nich program zobrazuje názvy polí seznamu. V druhém rozbalovacím seznamu v řádku Seřadit podle vybíráme způsob řazení. Ve třetím určujeme řazení vzestupné nebo sestupné. Další úroveň řazení, otevřeme tlačítkem Přidat úroveň.
Výběry (filtrování) dat seznamu
V tabulce můžeme zobrazit jen ty řádky, ve kterých je potřebná informace. Jejich výběr, umožňují tzv. filtry. Excel nabízí filtry Automatický, Vlastní a Rozšířený na kartě Data.
Automatický filtr umožňuje zobrazit přímo v tabulce jen ty řádky (záznamy), ve kterých jsou námi požadované hodnoty
Pokud chceme použít automatický filtr, umístíme aktivní buňku kamkoliv do seznamu, a na kartě Data, ve skupině Seřadit a filtrovat, klepneme na tlačítko Filtr. Nastavení filtru se projeví tak, že se v každé buňce prvního řádku tabulky objeví rozbalovací šipka.
Rozbalovací šipka v záhlaví tabulky nabízí možnosti řazení (první tři řádky rozbalené nabídky) a/nebo filtrování výběr řádků tabulky. Vybírat řádky můžeme podle hodnot nebo barev buněk tohoto pole. Příkazy, které budou v rozbalené nabídce, se mění podle vlastností (formát buněk) a hodnot buněk (číslo, text, datum) sloupce, ve kterém jsme nabídku rozbalili. Políčko zaškrtáváme/vyprazdňujeme klepnutím myší. Volbu potvrdíme klepnutím na OK (Např. bydliště Brno nebo jiné)
Pokud je nastaven filtr, tisknou se jen zobrazené řádky tabulky, graf se (volitelně) může vytvořit také jen ze zobrazených dat.
Podmínku můžeme také zadat pomocí dalších možností, které najdeme v rozbalené nabídce automatického filtru. Nabízí je řádek Filtry textu/Filtr kalendářních dat/Filtr čísel
Kdybychom např. vybírali ze seznamu osoby narozené po roce 1990, klepli bychom u sloupce Rok narození na rozbalovací šipku, vjeli na řádek Filtry čísel a vybrali srovnání Větší než.
Otevře se dialogové okno, ve kterém bude v prvním poli prvního řádku vybráno srovnání je větší než. Do vedlejšího pole napíšeme hodnotu.
Vypnutím automatického filtru rušíme všechny nastavené podmínky pro výběr záznamů, zobrazí se tedy všechny řádky tabulky (předtím můžete vybraná data kopírovat na do jiného místa tabulky či list).
Rozšířený filtr
Pokud potřebujeme vybrat data z tabulky a vytvořit z nich novou tabulku (vybrat z původní tabulky nejen záznamy ale i pole), můžeme použít Rozšířený filtr. Vyváříme přitom oblast kritérií (kriteriální tabulku).
Oblast kritérií tvoříme mimo původní seznam napíšeme vedle sebe názvy těch polí, podle jejichž hodnot budeme vybírat. Názvy musí být naprosto stejné jako v původním seznamu, proto je lepší je z původní tabulky zkopírovat. Pod názvy polí pak píšeme podmínky pro výběr záznamů. Podmínky, které napíšeme do jednoho řádku, budou platit současně, mezi podmínkami napsanými v různých řádcích bude platit vztah nebo (stačí splnění jedné z nich).
Při zadávání podmínek výběru se používají znaky =, <, > a jejich kombinace <=, >=, <>. Pokud do podmínek píšeme texty, program nerozlišuje velikost písmen. Při testování na rovnost čísel se znaménko = psát nemusí.
Pokud vytvoříme kriteriální tabulku, můžeme přistoupit k výběru. Aktivní buňku umístíme do filtrované tabulky a na kartě Data, ve skupině Seřadit a filtrovat, klepneme na tlačítko Upřesnit . Program otevře dialogové okno Rozšířený filtr, které vyplníme.
Oblast seznamu je tabulka, ze které vybíráme. Pokud jsme před vyvoláním okna umístili aktivní buňku do seznamu, program oblast vybere sám a adresu zapíše.
Do pole Oblast kritérií ukážeme tažením myší připravenou kriteriální tabulku včetně názvů sloupců.
Zrušení filtru) klepneme na kartě Data na tlačítko Vymazat.
Pokud z původního seznamu vybíráme nejen řádky, ale i sloupce, musíme podobně jako u kriteriální tabulky mimo původní seznam zadat názvy vybíraných polí (opět musí být úplně stejné, takže nejlépe zkopírováním). Adresu připravených názvů pak zadáme tažením myší v dialogovém okně Rozšířený filtr do pole Kopírovat do.
Vyhledávání y tabulkách
Přečtěte si "Funkce SVYHLEDAT" na stránkách:
https://sites.google.com/site/krejciladka/
Kontingenční tabulky
Přečtěte si "Vytvoření kontingenční tabulky" a "Kontingenční tabulky - návod" na stránkách:
https://sites.google.com/site/krejciladka/
Databázové funkce
Např. Funkce, která vyhledá největší z čísel v poli databáze vyhovující danému kritériu (např. brňáci):
DMAX(databáze;pole;kritéria)
- Databáze je oblast buněk, která tvoří seznam nebo databázi. První řádek seznamu obsahuje popisky sloupců.
- Pole určuje, který sloupec, který je ve funkci používán. Argument pole může být zadán jako text s popiskem sloupce v uvozovkách, například "Stáří" nebo "Výnos", nebo jako číslo představující umístění sloupce (hodnota 2 je druhý sloupec).
- Kritéria je oblast buněk, která obsahuje zadaná kritéria. Oblast zahrnuje nejméně jeden popisek, sloupce a nejméně jednu buňku pod popiskem sloupce určující podmínku sloupce.
Další databázové funkce:
DMAX(databáze; pole; Kritéria)
DMIN(databáze; pole; Kritéria)
Dpočet(databáze; pole; Kritéria)
DSUMA(databáze; pole; Kritéria)
Dsoučin(databáze; pole; Kritéria)
Dzískat(databáze; pole; Kritéria)
Dprůměr(databáze; pole; Kritéria)
Makra v Excelu
Cvičení
- Vkládání dat do tabulky, řady, vlastní řady
- Formátování tabulky, styly, podmíněné formátování
- Vzorce v tabulce, výpočty rovnic
- Výpočty obvodů, ploch a objemů goniometrických prvků
- Výpočet tabulky "Auto" a funkce KDYŽ
- Podmíněné funkce SUMIF, COUNTIF použité v "Třídnici"
- Absolutní a relativní odkaz ve vzorci. Výpočet "Tržby"
- Vyhledávácí funkce SVYHLEDAT v rozsáhlých tabulkách
- Tvorba grafů goniometrických funkcí, trendy naměřených hodnot
- Některé další funkce: převody jednotek, výpočet soustavy lineárních rovnic...
- Finanční funkce a Hledání řešení a citlivostní analýza
- Seznamy, třídění, databázové funkce, statistika
- Filtrace, vyhledávání, souhrny a výpočty v seznamu
- Kontingenční tabulka při analýze dat
- Praktické úkoly (výpočet ozubení, faktura, mzdy, protokol, výpočet trafa, sklad, ...)
Cvičení 1: Vyzkoušejte předdefinované řady a základní operace s čísly (sum, max, min, ...)
Cvičení 2: Vypočtěte výrazy pro zadané hodnoty a, b, c, d, e, f :
- Vypočtěte si BMI podle vzorce: BMI= váha (v kg)/výška^2 (v metrech)
- V jaký den týdnu jste se narodili, a v jaký den budete slavit 50tiny ?
- Pro zadanou dráhu (m) a čas (s) vypočtěet rychlost v m/s
- Vypočtěte obsahy a obvody kružnice, čtverce, trojúhelníku, koule , kvádru a jiných geometrických těles a ploch.
Cvičení 3
Vypočtěte obvody, plochy a obsahy: kružnice, trojúhelníku, čtverce, obdélníku, krychle, kvádru, jehlanu, koule jsou-li zadané rozměry.
Cvičení 4 - RANDBETWEEN, COUNTIF, SUMIF
Poznámka: Uvedená data jsou smyšlená !!!
Cvičení 5 - KDYŽ
Vypočtěte výkaz firemního automobilu.
Cvičení 5 - Absolutní a relativní odkaz v tržbách, KDYŽ a SVYHLEDAT
Cvičení 6: - Průběhy goniometrických funkcí
- Zobrazte v Excelu průběhy goniometrických funkcí sin x, cosx a funkce y=sin2x-2cosx-1/2x
- Zobrazte průběh funkce, graf formátujte podle zadání (legenda, nadpis, osa x, popisky...)
a zjistěte pro jaké x se rovná 5
Cvičení 7 - Finanční funkce a Hledání řešení
- Vypočtěte jakou částku našetříte, pokud budete měsíčně ukládat...
- Vypočtěte Za jakou dobu splatíte dluh, při konstatních splátkách...
- Vypočtěte jakou částku si můžete vypůjčit, pokud můžete splácet pouze...
- Vypočtěte za jakou dobu odepíšete stroj
Vytvořte tabulky citlivosti na jednu proměnnou:
Cvičení 8 - Práce se seznamy, databázové funkce
Cvičení 9 - Vytvoření kontingenční tabulky
Vytvořte kontingenční tabulku spolužáků ve třídě podle návodu na stránkách:
https://www.youtube.com/watch?feature=player_embedded&v=GmojDuXutc0
Použijte ve výchozí tabulce bydliště, datum narození, věk a průměrný prospěch
Klávesové zkratky
Tab |
přesun z buňky do buňky v rovině řádku směrem doprava |
Shift+Tab |
přesun z buňky do buňky v rovině řádku směrem doleva |
šipka směrem… |
přesun z buňky do buňky daným směrem |
Page Up |
přesun na další stránku směrem nahoru |
Page Dn |
přesun na další stránku směrem dolů |
Alt+Page Up |
přesun na další stránku směrem doleva |
Alt+Page Dn |
přesun na další stránku směrem doprava |
Home |
skok na začátek řádku, do první buňky řádku |
Ctrl+Home |
Excel: skok do první buňky listu (A1) |
|
Access: skok na první pole prvního záznamu |
End |
skok na konec řádku |
Ctrl+End |
Excel: skok do poslední buňky použitého prostoru listu |
|
Access: skok na poslední pole posledního záznamu |
Del, Backspace |
odstranění obsahu buňky |
F1 |
Zobrazení nápovědy (pomocníka) |
F2 |
Přepíná režim v buňce (z buňkového na textový kurzor) |
Shift+F2 |
Vložit, editovat komentář |
F3 |
Vyvolání dialogového okna Vložit název (seznam pojmenovaných buněk a oblastí) |
Shift+F3 |
Vyvolání dialogového okna Vložit funkci |
F4 |
Cyklická záměna adresace (absolutní – smíšená – relativní) |
Alt+F4 |
Zavření aplikace |
Ctrl+F4 |
Zavření aktivního okna |
Shift+F4 |
Vyhledávání následujících výskytů řetězce podle definice (při zavření dialogového okna Hledat) |
Ctrl+Shift+F4 |
vyhledávání předcházejících výskytů řetězce podle definice (při zavření dialogového okna Hledat) |
F6 |
V rozděleném okně (příčkami) přesune kurzor z jednoho okna do druhého ve směru hodinových ručiček |
Shift+F6 |
V rozděleném okně (příčkami) přesune kurzor z jednoho okna do druhého proti směru hodinových ručiček |
Alt+F8 |
Vyvolání dialogového okna Makro |
F9 |
Výsledek vzorce (funkce) přemění na konstantní hodnotu |
Ctrl+F9 |
Minimalizace okna |
Ctrl+F10 |
Maximalizace (obnovení) okna |
Shift+F10 |
Vyvolání místní nabídky |
F11 |
Vložení nového listu do sešitu (s grafem) |
Shift+F11 |
Vložení nového listu do sešitu (s mřížkou) |
Další výpočty
- Jak velkou částku obdržíte za 4 roky, když uložíte na začátku spoření 50 000 Kč a začátkem každého měsíce budete ukládat 2000 Kč. Roční úroková sazba je 10 %. Datovou tabulku pro různou úrokovou sazbu a úložku
- Pořídíte investici za 100 000 Kč se životností 8 let. Kolik budete odepisovat v účetnictví každý rok zrychleným odpisem, má-li být zůstatková hodnota 2 000 Kč. Datovou tabulku
- Budete chtít koupit v Německu osobní počítač, kde stojí 2 500 DM. U nás by stál 60 000 Kč. Doprava bude stát 100 DM, dovozní clo je 5 % a DPH je 22 %. Jaký musí být kurz marky aby se vám dovoz ještě vyplatil.
- Proveďte třídění, filtr, souhrn, přehled nad seznamem "spolužáci"
- Vypočti pro jaké x platí 9x3+7x2-12x=36 a nakreslete graf funkce. Hledání řešení
- Vypočtěte jaký musí být kurz marky aby se vyplatilo zakoupit počítač v Německu, kde jeho cena je 2500 DM, doprava je 100 DM, clo je 5 % z ceny, dph je 22 % z ceny, a kurz marky je 19 Kč. Hledání řešení
- Vytvořte graf s vedlejší osou pro tabulku z příkladu 1. Vytvořte graf funkce sinx a cosx.
- Použijte funkce Když pro zkoušení slovíček z cizího jazyka.
- Co bylo za den když jste se narodili (po, út, ..,ne) ?
- Předveďte všechny řady, které umíte vytvořit tažením myší a nadefinujte jednu vlastní
Zadání samostatné práce
Vypočtěte technické parametry síťového transformátoru podle zadání na stránkách:
https://sites.google.com/site/krejciladka/ záložka Tabulkový procesor, soubor Výpočet transformátoru1.doc