Sekretářka společnosti „Naše zahrada“ dostala za úkol označit zboží, na které se vztahuje sleva. Dále potřebuje zjistit pouze příjmení zaměstnanců (pro další statistické zpracování dat) a dále potřebuje zařadit jednotlivé zaměstnance do kategorií dle jejich věku dosaženého v letošním roce. Pro tyto úkoly využila logické funkce, funkce pro práci s datem a časem a funkce pro práci s textem.
Zadání
Otevřete soubor VzorceDalsi.xlsx.
Na listu Sleva zjistěte, na které zboží se vztahuje sleva (Cena v Kč musí být větší než 1000): vložte do sloupce Sleva 5% text ano, pokud bude Cena v Kč > 1000, jinak vložte text ne. Řešte pomocí funkce.
Řešení
Na listu Zaměstnanci zjistěte věk zaměstnance dosažený v aktuálním roce a zařaďte zaměstnance do příslušné věkové skupiny: do 30 let, 31 – 60 let (včetně), nad 60 let. Použijte následující postup:
Řešení
Karta Soubor/Otevřít (nebo tlačítko Otevřít na panelu nástrojů Rychlý přístup, popř. klávesová zkratka Ctrl+O). V dialogovém okně Otevřít nalézt soubor dle zadání, tlačítko Otevřít. Pozn. Sešit lze otevřít také přímo v systému Windows (dvakrát kliknout na ikonu souboru nebo kontextová nabídka ikony/Otevřít).
Zobrazit list dle zadání, do sloupce vložit vzorec s využitím logické funkce KDYŽ (podrobnější popis vkládání vzorců viz příklad 02_01). Pozn. Funkce KDYŽ se používá tehdy, potřebujeme-li do vzorce zabudovat podmínku. Je-li podmínka splněna, vloží do buňky určitou hodnotu, není-li podmínka splněna, vloží hodnotu jinou (závisí na konkrétním řešení). Do pole F4 vložit funkci =KDYŽ(D4>1000;"ano";"ne") – karta Vzorce/skupina Knihovna funkcí/příkaz Logická/Když/ v dialogovém okně Argumenty funkce vyplnit argumenty následujícím způsobem: Podmínka: D4>1000, Ano ano, Ne ne.
Zadání
Pozn. V poli Ano se uvádí hodnota, která bude do buňky vložena v případě platnosti podmínky, v poli Ne se uvádí hodnota, která bude do buňky vložena, když podmínka nebude platit. Vkládáme-li do pole pro argument text, měl by být psán v uvozovkách (pokud uvozovky neuvedeme, Excel je sám doplní). Po zavření dialogového okna budou argumenty funkce vloženy do závorek za názvem funkce a budou odděleny středníkem. Zde lze tyto argumenty dále upravovat při editaci buňky. Zkopírovat vytvořený vzorec do celého sloupce.
Excel nabízí kromě klasických funkcí pro výpočty také funkce pro práci s textem (řetězci). Tyto funkce se používají, potřebujeme-li např. zjistit délku textu v buňce, nalézt určitý znak či oddělit určitou část od zbytku textu (např. název firmy z názvu zboží). Příjmení lze ze sloupce Zaměstnanec vyjmout pomocí několika textových funkcí, pro snadnější orientaci je úkol rozdělen do následujících kroků: zjištění celkové délky textu, zjištění první mezery v textu (mezera odděluje jméno a příjmení), vyjmutí textu od mezery do konce řetězce (získání příjmení.
Zadání
Délku textu lze zjistit pomocí funkce DÉLKA. Do buňky C3 vložit vzorec =DÉLKA(A3) – karta Vzorce/skupina Knihovna funkcí/příkaz Text/Délka/argumentem funkce délka je buňka s textem A3, zkopírovat vzorec do celého sloupce.
Zadání
První pozici mezery v textu lze zjistit např. pomocí funkce HLEDAT. Tato funkce vloží do buňky číslo pozice prvního nalezeného výskytu znaku nebo textového řetězce (v našem případě mezery). Pozn. Lze použít také funkci NAJÍT, která rozlišuje malá a velká písmena. Do buňky D3 vložit vzorec =HLEDAT(" ";A3) – karta Vzorce/skupina Knihovna funkcí/příkaz Text/Hledat/doplnit argumenty funkce: Co: „ “ (mezera napsaná mezerníkem), Kde: A3 (text, ve kterém se mezera bude hledat), Start může zůstat prázdné (pozice, od které se bude hledat – pokud zůstane prázdné, hledá se od začátku řetězce). Do buňky bude vložen vzorec, jeho výsledkem bude číslo odpovídající první pozici, na které se nachází mezera v textu sloupce Zaměstnanec. Zkopírovat vzorec do celého sloupce.
Zadání
Pro zjištění druhého slova z textu lze použít funkci ČÁST. Funkce vyjme určitý počet znaků z textu od zadané pozice. Do buňky E3 vložit vzorec =ČÁST(A3;D3+1;C3) – karta Vzorce/skupina Knihovna funkcí/příkaz Text/Část/doplnit argumenty funkce: Text: A3 (text, ze kterého vybereme příjmení), Start: D3+1 (počáteční pozice, od které příjmení vyjmeme – v našem případě pozice mezery; jelikož mezeru na začátku příjmení nechceme, posuneme pozici o 1 doprava), Počet_znaků: C3 (počet znaků, který z textu vyjmeme – v našem případě nevíme přesně, jak dlouhé bude příjmení u každého zaměstnance, ale využijeme znalosti délky celého textu; pokud počet znaků převyšuje délku textu, bude nadbytečný počet ignorován). Do buňky bude vložen vzorec, jeho výsledkem bude vyjmutý text od první mezery, tedy příjmení. Zkopírovat vzorec do celého sloupce.
Zadání
Pro zjištění věku zaměstnance můžeme využít funkce pro práci s datem a časem. Excel pracuje s datem a časem jako s pořadovým číslem. Datum je určováno jako počet dní od výchozího data 1.1.1900. Čas je vyjádřen pořadovým číslem počítaným od půlnoci do půlnoci a vyjadřuje se za desetinnou čárkou (hodnota z intervalu 0 – 1 určuje čas v rámci jednoho dne). Např. číslo 2,75 reprezentuje 2.1.1900 18:00 hodin. Skutečný tvar data a času je v sešitě dán formátem buňky.
Zadání
Věk lze vypočítat rozdílem mezi dvěma roky. Z data získáme rok pomocí funkce ROK. Rozdíl bude počítán mezi aktuálním rokem a rokem z data narození. Pro zjištění aktuálního data lze použít funkci DNES (zajistíme tak, že výpočet bude vždy aktuální). Jelikož budeme používat ve vzorci dvě funkce (funkce DNES bude vložena na místo argumentu funkce ROK), bude nutné vzorec vepsat ručně. Doporučuje se nejprve vyzkoušet v pokusném sloupci, jak jednotlivé funkce fungují (vložit je pomocí dialogového okna), a teprve potom složit vzorec dohromady. Do buňky F3 vložit vzorec =ROK(DNES())-ROK(B3) - dílčí funkce se nacházejí na kartě Vzorce/skupina Knihovna funkcí/příkaz Datum a čas/ROK a DNES. První funkce lze vložit pomocí nabídek, další bude třeba dopsat ručně. Excel nabízí při psaní vzorců názvy funkcí při napsání počátečního písmene – výběr z tohoto seznamu lze provést dvojím kliknutím myši na funkci. Pozn. Nabízení funkcí – automatické dokončování vzorců lze vypnout – tlačítko Office/příkaz Možnosti aplikace Excel/sekce Vzorce/skupina Práce se vzorci/Automatické dokončování vzorce. Do buňky bude vloženo číslo odpovídající rozdílu aktuálního roku a roku z data narození. Zkopírovat vzorec do celého sloupce.
Zadání
Pro zařazení zaměstnance do věkové skupiny lze použít logickou funkci KDYŽ. Funkce byla podrobněji popsána v bodě 1. V tomto případě půjde o testování více podmínek, funkce KDYŽ bude do sebe vnořena. Pozn. Funkce lze do sebe vnořovat, celkem lze vytvořit v Excelu 2007 až 64 úrovní; ve starších verzích to bylo pouze 7 úrovní). Do buňky G3 vložit vzorec =KDYŽ(F3<31;"do 30";KDYŽ(A(F3>=31;F3<=60);"31 - 60";"nad 60")). Podmínky ve vzorci jsou dány podmínkami ze zadání, ve funkci KDYŽ je jako první argument zadána podmínka, druhý argument je text, který se vloží do buňky, když podmínka platí, třetí argument je text, popř. opět funkce KDYŽ, která se provede, když podmínka neplatí (další testování). V druhé úrovni u funkce KDYŽ potřebujeme zadat dvě podmínky, které platí najednou – je zde využita funkce A, která funguje jako logický operátor and. Po vytvoření vzorce bude do buňky vložen text dle příslušného intervalu, do kterého zaměstnanec dle svého věku patří. Zkopírovat vzorec do celého sloupce.
Zadání
Uložit sešit - karta Soubor/Uložit (nebo tlačítko Uložit na panelu nástrojů Rychlý přístup nebo klávesy Ctrl+S). Zavřít sešit - karta Soubor/Zavřít (nebo tlačítko Zavřít – x v pravém horním rohu okna).
Zadání
