KIVZD cvien 3 Tom Potuk 3 D tabulka

KIV/ZD cvičení 3 Tomáš Potužák

3 D tabulka I • Tabulka v rámci jednoho listu 2 D – Má řádky (čísla) a sloupce (písmena) • Listů obvykle více možnost využít ve vzorcích 3 rozměry • Příklad na 3 D tabulku – součet studentů – V listech uložena data o počtu studentů třech fakult, každý list jeden rok – Spočítat celkové součty studentů

3 D tabulka II • 3 D tabulka s počty studentů

3 D tabulka III • Pro součet potřeba provést: – Do příslušné buňky vložit funkci =SUMA( – Kliknout na záložku prvního listu – Kliknout na pole, které chceme do součtu – Držet [Shift] a kliknout na poslední list – Dopsat uzavírací závorku – Psaní vzorec ukončit stisknutím [Enter]

3 D tabulka IV • Výsledný vzorec možno nakopírovat do ostatních buněk Hotovo

3 D tabulka V – časté chyby • Související data musí být na všech listech ve stejných buňkách • Listy musí tvořit souvislou oblast

3 D tabulka VI – časté chyby • Listy musí být správně seřazené – Nesmí mezi nimi být nesouvisející list

Adresování R 1 C 1 I • Jiný způsob adresování buněk – Nepoužívá se příliš často – Vhodnější programování ve Visual. Basic (v Excelu možno) – Lze použít ke zmatení nezkušených uživatelů • Číslo po R udává řádku, číslo po C sloupec • Musí se zapnout (implicitně adresování A 1) – Soubor Možnosti Vzorce Styl odkazu R 1 C 1

Adresování R 1 C 1 II

Adresování R 1 C 1 III • Odkazy mohou být relativní, absolutní nebo smíšené (stejně jako adresování A 1) • Relativní odkaz adresování R 1 C 1 – Čísla uzavřená v hranatých závorkách, – Mohou být kladná nebo záporná – Místo 0 se závorka i číslo zcela vypustí – Udávají počet buněk od buňky se vzorcem – Např. R[1]C[-3] (ukazuje o 1 buňku níže a 3 vlevo), RC[3] (stejný řádek), R[-1]C (stejný sloupec), R[1]C[6]

Adresování R 1 C 1 IV • Smíšený, absolutní odkaz adresování R 1 C 1 – Absolutní část odkazu není uzavřena v hranatých závorkách – Číslo v absolutní části udává číslo řádky či sloupce od začátku tabulky (může být tedy pouze kladné) – Např. R 1 C[-3], R[-4]C 1 (smíšené) nebo R 9 C 1 (absolutní) • Přepínání při psaní vzorce – Stejně jako u adresování A 1 – klávesa [F 4]

Pojmenované oblasti I • Buňce nebo oblasti lze přiřadit jméno – Lze použít ve vzorci místo adresy – Může být přehlednější • Podmínky pojmenování – Nesmí obsahovat mezeru a začínat číslem – Nesmí se shodovat s názvem funkce nebo „R“ a „C“, maximální délka 255 znaků – Není citlivé na velikost písmen – Je vhodné mít v pojmenování systém

Pojmenované oblasti II • Pojmenování buňky/oblasti – Označit buňku či oblast – Vzorce Definované názvy Definovat název – Vyplnit název oblasti a zda je platná pro celý sešit nebo jen konkrétní list – Potvrdit Ok

Pojmenované oblasti III • Správa názvů – Vzorce Definované názvy Správce názvů – Seznam vytvořených pojmenování – Možnost přidání, úprav, mazání

Pojmenované oblasti IV • Použití názvů – Napsat vytvořený název přímo do vzorce místo adresy buňky či oblasti • Příklad – Mám oblast buněk obsahující známky (B 1: B 28) pojmenovanou jako „znamky“ – Průměr známek pak mohu spočítat jako =PRŮMĚR(B 1: B 28) nebo jako =PRŮMĚR(znamky)

Nepřímý odkaz • Funkce, převede řetězec na odkaz – NEPŘÍMÝ. ODKAZ(odkaz; a 1) – Umožňuje např. poskládat odkaz z částí zadaných uživatelem – a 1 je nepovinná logická hodnota udávající styl odkazu (PRAVDA pro A 1, NEPRAVDA pro R 1 C 1); implicitně předpokládá A 1 – Např. V buňce A 1 je „B“, buňce A 2 je „ 5“ funkce =NEPŘÍMÝ. ODKAZ(A 1&A 2) vrátí odkaz na buňku B 5

Převod vzorce na hodnotu • Místo vzorce lze do buňky uložit výsledek – Může se hodit při kopírování – Může urychlit výpočty (vzorce jsou nahrazeny statickými hodnotami) – pouze při složitých výpočtech a velkém množství dat • Postup – Při psaní vzorce nebo úpravě vzorce (dvojklik na buňku nebo [F 2]) – Stisknout [F 9] a následně [Enter]

Chyby při výpočtech I • #DĚLENÍ_NULOU! – Dříve #DIV/0! – Pokus o dělení nulou (např. =20/0) • #NULL! – Prázdný průnik dvou oblastí (např. A 1: B 2 C 3: D 4) • #NÁZEV? – Nesprávný název buňky, funkce (např. SUMMA) • #HODNOTA! – Nesprávný parametr (např. text místo čísla)

Chyby při výpočtech II • #NENÍ_K_DISPOZICI! – Dříve #N/A! – Odkaz na neexistující hodnotu • #ČÍSLO! – Dříve #NUM! – Nesprávné použití čísla • #ODKAZ! – Dříve #REF! – Odkaz na neexistující buňku

Chyby při výpočtech III • ###### – Hodnota se nevejde do buňky – Zvětšit šířku buňky • S chybami lze pracovat s využitím několika funkcí – Odchycení chyby – Zobrazení jiného textu a podobně

Funkce pro práci s chybami I • CHYBA. TYP(chyba) – Vrátí číslo chyby – chyba nebo odkaz na buňku obsahující chybu • JE. CHYBA(hodnota) – Vrátí PRAVDA, pokud je hodnota (odkaz na buňku) chybová – Kromě chyby #NENÍ_K_DISPOZICI!

Funkce pro práci s chybami II • JE. CHYBAODN(hodnota) – Vrátí PRAVDA, pokud je hodnota (odkaz na buňku) chybová – Funguje pro libovolnou chybovou hodnotu • CHYBHODN(hodnota; hodnota_při_chybě) – Pokud je hodnota (odkaz na buňku) chybná, vrátí hodnota_při_chybě – Jinak vrátí přímo hodnotu buňky

Základní funkce I • Matematické funkce – ABS(číslo) – absolutní hodnota čísla – SIN(úhel) – sinus úhlu (v radiánech) – COS(úhel) – cosinus úhlu (v radiánech) – TG(úhel) – tangens úhlu (v radiánech) – RADIANS(úhel ve stupních) – převedení úhlu ve stupních na radiány – DEGREES(úhel v radiánech) – převedení úhlu v radiánech na stupně

Základní funkce II • Matematické funkce – LOG(číslo) – dekadický logaritmus čísla – LN(číslo) – přirozený logaritmus čísla – ODMOCNINA(číslo) – druhá odmocnina z čísla – POWER(číslo; exponent) – libovolná mocnina (celý exponent) či odmocnina (zlomkový/ desetinný exponent) z čísla – číslo ^ exponent – libovolná mocnina (celý exponent) či odmocnina (zlomkový/desetinný exponent) z čísla (operátor ^)

Základní funkce III • Zaokrouhlování – ZAOKROUHLIT(číslo; počet_míst) – zaokrouhlení čísla na požadovaný počet míst (zbytek desetinných míst se zahodí a už se s ním nepočítá) • POZOR! Změna formátu buňky – Lze nastavit, kolik desetinných míst se zobrazí v buňce to není zaokrouhlování, MS Excel stále počítá se všemi číslicemi (tj. na 15 desetinných míst)

Základní funkce IV • Práce s texty (řetězci) – DÉLKA(řetězec) – délka (počet znaků) řetězce (textu) – ČÁST(řetězec; počátek; počet_znaků) – podřetězec (část) řetězce, začínající znakem s číslem počátek a s požadovaným počtem znaků, znaky očíslovány zleva doprava od 1 – CONCATENATE(řetězec 1; řetězec 2; …) – spojení dva a více řetězců do jednoho – řetězec 1 & řetězec 2 – stejné ↑ (operátor &)

Základní funkce V • Práce s oblastmi buněk – SUMA(oblast 1; oblast 2; …) – součet čísel v oblastech (např. SUMA(A 1; A 3: B 6; 2)) – PRŮMĚR(oblast 1; oblast 2; …) – aritmetický průměr čísel v oblastech – MIN(oblast 1; oblast 2; …) – minimum (nejmenší číslo, text nejblíže A, nejnižší datum, …) – MAX(oblast 1; oblast 2; …) – maximum – POČET(oblast 1; oblast 2, …) – počet neprázdných buněk s čísly – POČET 2(oblast 1; oblast 2, …) – počet neprázdných buněk

Základní funkce VI • Počet buněk odpovídající kritériu – COUNTIF(oblast; kritérium) – počet neprázdných buněk v oblasti odpovídající kritériu – kritérium – zapsané přímo nebo obsažené v odkazované buňce – číslo, výraz, text, např. 32, “Ahoj“, “>32“ – Např. COUNTIF(A 1: B 5; “<3“) – vrátí počet buněk menších než 3 v oblasti A 1: B 5

Základní funkce VII • COUNTIF(oblast; kritérium)

Základní funkce VIII • Součet buněk odpovídající kritériu – SUMIF(oblast; kritérium; součet) – součet neprázdných buněk v oblasti oblast odpovídající kritériu v oblasti součet (stejný tvar) – kritérium – stejné jako u COUNTIF – Splnění kritéria se hledá v oblasti oblast, na odpovídajících místech v oblasti součet se sečou čísla výsledek SUMIF – SUMIF(oblast; kritérium) – stejné, ale pro hledání kritéria i sčítání se použije oblast

Základní funkce IX • SUMIF(oblast; kritérium; součet)

Základní funkce X • Větvení, jedna ze dvou hodnot – KDYŽ(podmínka; ano; ne) – Vrátí hodnotu ano, pokud je podmínka splněna (PRAVDA), jinak vrátí hodnotu ne – ano, ne – konstanty, odkazy na buňky, výrazy – např. “Ano“, A 1, 1+A 1, … – podmínka – logický výraz (libovolně složitý, výsledkem je PRAVA nebo NEPRAVDA) – Lze zanořovat, např. KDYŽ(A 1=1; “výborně“; KDYŽ(A 1=2; “chvalitebně“; “dobře“))

Základní funkce XI • Logický součin („a zároveň“) – Funkce A(podmínka 1; podmínka 2; …) – Všechny podmínky splněny (jejich výsledek je PRAVDA) celkový výsledek PRAVDA, jinak NEPRAVDA – Stačí najít jednu NEPRAVDU a celý výsledek je nepravda – Např. A(3>1; 5*3 >12) = PRAVDA, A(NEPRAVDA; B 4<5) = NEPRAVDA

Základní funkce XII • Logický součet („nebo“) – Funkce NEBO(podmínka 1; podmínka 2; …) – Alespoň jedna podmínka splněna celkový výsledek PRAVDA, pouze pokud všechny podmínky nesplněny NEPRAVDA – Stačí najít jednu PRAVDU a celý výsledek je PRAVDA – Např. NEBO(3<1; 5*3<12) = NEPRAVDA, NEBO(PRAVDA; B 4<5) = PRAVDA

Základní funkce XIII • Negace – Funkce NENÍ(logická_hodnota) – Do verze MS Excel 2007 (včetně) funkce NE(logická_hodnota)) – Změní hodnotu PRAVDA na NEPRAVDA a naopak – Např. NENÍ(NEPRAVDA) = PRAVDA, NENÍ(3<>4) = (3=4) = NEPRAVDA

Vyhledávací funkce I • SVYHLEDAT(hledat; tabulka; sloupec; typ) – V 1. sloupci oblasti tabulka vyhledá hodnotu – Pokud ji najde, vrátí hodnotu ve stejném řádku a sloupci sloupec (pořadí sloupce v oblasti tabulka číslovaný od 1 odleva) – typ je nepovinná logická hodnota udávající, zda použít přesnou shodu (NEPRAVDA) nebo přibližnou (PRAVDA), výchozí je přibližná • VVYHLEDAT(hledat; tabulka; řádek; typ) – Podobné, ale pro řádky

Vyhledávací funkce II • POZVYHLEDAT(co; prohledat; shoda) – Vyhledá relativní pozici (odshora nebo odleva) hodnoty co v řádku nebo sloupci prohledat (oblast) – shoda – nepovinná hodnota, určuje, jak se porovnává – 1 – najde nejvyšší hodnotu rovno nebo menší než co (implicitní – seřazeno vzestupně); -1 – najde nejnižší hodnotu rovno nebo větší než co (seřazeno sestupně); 0 – přesná shoda s co (řazení nutné)

Vyhledávací funkce III • INDEX(pole; řádek; sloupec) – Vrátí hodnotu na zadaném řádku a/nebo sloupci oblasti pole – Pokud je oblast pole jednorozměrná (jeden řádek či jeden sloupec), udává se jen řádek nebo jen sloupec – Pozice jsou číslovány od 1 odleva/odshora