FUNKCE SVYHLEDAT() Název školy
Obchodní akademie, Vyšší odborná škola a Jazyková škola s právem státní jazykové zkoušky Uherské Hradiště
Název DUMu
Funkce SVYHLEDAT()
Autor Datum
Ing. Bc. Martin Šimůnek 23. 12. 2013
Stupeň a typ vzdělávání Odvětví / Vzdělávací oblast Vzdělávací obor Tematická oblast Druh učebního materiálu Cílová skupina Anotace a popis způsobu použití ve výuce
Střední odborné vzdělávání -- dle RVP.cz --- Obchodní akademie / Informační technologie -Tabulkový kalkulátor MS Excel Výukový materiál Žák, 15 – 19 let Výukový materiál lze použít k frontální prezentaci učitelem, případně jako materiál pro samostudium. Je vhodné posluchačům vypomoci s řešením praktických úloh.
Vybavení, pomůcky Klíčová slova
-
Číslo projektu Název šablony
CZ.1.07/1.5.00/34.0534 III/2 – Inovace a zkvalitnění výuky prostřednictvím ICT
ZLÍNSKÝ KRAJ
ÚVOD V předchozím materiálu jsme si ukázali, co jsou to funkce v Excelu. Použili jsme záměrně jednoduché funkce. Nyní si předveďme příklad sofistikovanější funkce, konkrétně SVYHLEDAT().
AŤ KALKULÁTOR PRACUJE ZA VÁS! Nekopírujte — počítejte! • V dobře navrženém sešitu je každá hodnota napsána jen jednou. • Pokud ji potřebujeme zobrazit na více místech, neopisujeme ji (ani nekopírujeme), ale doplníme ji pomocí vzorce.
Příklad • • • •
Mám list se seznamem oddělení a list se seznamem zaměstnanců. U každého oddělení je uvedeno jeho číslo a název. U každého zaměstnance mám mimo jiné číslo oddělení, pod které spadá. Pokud chci v tabulce zaměstnanců i název oddělení, nebudeme ho kopírovat! Necháme ho automaticky doplnit.
PROČ JE CTRL-C A CTRL-V ŠPATNĚ? Co když se název oddělení změní? • Představte si, že chceme změnit název oddělení 5 z předchozího příkladu.
Správná varianta — vzorce • Pokud doplňujeme do všech dalších tabulek název oddělení vzorcem, stačí přepsat název oddělení v listu Oddělení. • Do všech dalších listů se správný název automaticky promítne pomocí vzorce.
Špatně — Ctrl-C a Ctrl-V • Pokud bychom pro přenášení názvu oddělení do dalších listů použili kopírování, museli bychom nyní nový název oddělení překopírovat na všechna místa, kam jsme předtím kopírovali předchozí název. • A kdo si to má pamatovat, že? ;)
SYNTAXE SVYHLEDAT (1) SVYHLEDAT(klíč; tabulka; sloupec; hledat_přesně) Klíč • • • •
Je text, podle kterého najdeme v tabulce správný řádek. V našem příkladu je to ID oddělení, jehož název hledáme. Klíč hledáme vždy v prvním sloupci vyhledávací tabulky. Klíč je třeba dodržet přesně včetně mezer. Ideálně je klíč číslo, ale lze i jinak.
(Vyhledávací) tabulka • Tady uvedeme rozsah buněk s tabulkou, ve které hledáme. Tedy například s tabulkou všech oddělení.
Sloupec • Pomocí klíče najdeme řádek se správným oddělením. Ale jaký údaj o hledaném oddělení nás vlastně zajímá? • Sloupec udává číslo sloupce, ve kterém je hledaná buňka v prohledávané tabulce.
SYNTAXE SVYHLEDAT (2) SVYHLEDAT(klíč; tabulka; sloupec; hledat_přesně) Hledat_přesně • My budeme psát zatím vždy „PRAVDA“ nebo „1“, protože hledáme podle zadaného klíče.
Hledat_přesně — varianta NEPRAVDA • Variantu s „NEPRAVDA“ použijeme, pokud chceme vyhledávat data z nějakého rozsahu. • Například pokud chceme k ceně dohledat slevu s tím, že: • pro cenu od 1 do 500 Kč je sleva 0%, • pro cenu od 500 do 1000 Kč je sleva 5 % • a od 1000 Kč sleva 10 %.
ŘEŠENÍ PŘÍKLADU =SVYHLEDAT(C2; $F$2:$G$4; 2; NEPRAVDA) Klíč — C2 • Číslo oddělení, které hledáme, je v buňce C2.
Tabulka — $F$2:$G$4 • Odkaz zadáváme absolutně, popis sloupců nezadáváme.
Sloupec — 2 • Chceme název oddělení, což je 2. sloupec vyhledávací tabulky.
Hledat_přesně — varianta NEPRAVDA • Hledáme přesnou shodu.
Vyhledávací tabulka
ÚKOLY — SPOČÍTEJTE V TABULCE Doplňte v listu KnihaJizd ve cvičném sešitu: • • • • •
Typ vozidla podle SPZ. Cenu za km podle typu. Spočtěte odpovídající cenu jízdy. Tabulka vozidel je v listu Vozidla. Tabulka cen je v listu CenaZaKm.
Dohledejte slevu podle kategorie a spočtěte cenu po slevě: • Tabulka slev je v listu Slevy.
ÚKOLY: ČÍSELNÉ ŘEŠENÍ Řešení včetně použitých vzorců je k dispozici učiteli v excelové tabulce: reseni.xlsx
POUŽITÉ ZDROJE 1)
FOTOPULOS, Fotis. Excel návod zdarma [online]. (c) 2009 [cit. 2013-12-20]. Dostupné z: http://excel-navod.fotopulos.net/index.html
2)
FRENCH, Ted. ABOUT.COM. Spreadsheets [online]. (c) 2013 [cit. 2013-12-21]. Dostupné z: http://spreadsheets.about.com/od/excel101/ss/enter_data.htm
METODICKÁ DOPORUČENÍ Zdůrazněte: • Podle posledního parametru rozlišujeme dva režimy funkce SVYHLEDAT, které jsou dosti odlišné. (Doporučujeme učit každý režim zvlášť.) • Číslo sloupce je relativní vůči vyhledávací tabulce, nikoli absolutní v listu. (Pokud vyhledávací tabulka začíná na sloupci C daného listu, pak číslo sloupce 2 znamená, že se bude hledat ve sloupci D daného listu.) • Pokud vzorce kopírujeme, odkaz na vyhledávací tabulku obvykle uvádíme jako absolutní odkaz.
Nechte žáky zkoušet prakticky: • Žáci si potřebují procvičit zápis vzorců sami. • Rozkopírujte jim prázdnou tabulku s úkolem (můžete ji vyvěsit na web, do sdílené složky, do školního e-learningového systému,…). • Klaďte důraz na to, aby si vzorečky žáci sami napsali do vlastní excelové tabulky a aby jim fungovaly.
