Příklady: Obsah

Vzorce – ukázka dalšího využití funkcí

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 VzorceDlasi.ods.

  1. 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.

  2. Na listu Zaměstnanci vložte do sloupce Příjmení pouze příjmení zaměstnanců, použijte funkce pro práci s textem:

    1. Do pomocného sloupce délka vložte délku textového řetězce ve sloupci Zaměstnanec.

    2. Do pomocného sloupce mezera vložte pozici první mezery v textovém řetězci ve sloupci Zaměstnanec.

    3. Do sloupce Příjmení vložte druhé slovo ze sloupce Zaměstnanec, pro výpočet použijte hodnoty z pomocných sloupců délka a mezera.

  3. 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:

    1. Do sloupce Věk doplňte věk zaměstnanců dosažený k aktuálnímu datu.

    2. Do sloupce Věková skupina doplňte text do 30, 31 - 60 a nad 60 dle dosaženého věku zaměstnanců.

  4. Sešit uložte a uzavřete.

Řešení:

Otevřete soubor dle zadání například dvojitým kliknutím na něj v Průzkumníkovi nebo zkratkou Ctrl+O v programu LibreOffice Calc.

  1. Zobrazit list dle zadání, do sloupce vložit vzorec s využitím logické funkce IF. Pozn. Funkce IF 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 =IF(D4>1000;"ano";"ne"). Pro vložení funkce můžete použít také Průvodce funkcí stisknutím ikony v Liště vzorců. Požadovanou funkci IF najdete v kategorii Logické. Pozn. V poli Potom se uvádí hodnota, která bude do buňky vložena v případě platnosti podmínky, v poli Jinak 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. 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.

  2. Calc 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í).

    1. Délku textu lze zjistit pomocí funkce LEN. Do buňky C3 vložit vzorec =LEN(A3). Pro vložení lze opět využít Průvodce funkcí stisknutím ikony v Liště vzorců. Požadovanou funkci LEN najdete v kategorii Text. Argumentem funkce je buňka s textem A3, zkopírovat vzorec do celého sloupce.

    2. První pozici mezery v textu lze zjistit např. pomocí funkce FIND. 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). Do buňky D3 vložit vzorec =FIND(" ";A3). Vybrat požadovanou buňku, spustit Průvodce funkcí, kategorie Text, funkce FIND, doplnit argumenty funkce: hledaný text: " " (mezera napsaná mezerníkem mezi uvozovky), text: A3 (text, ve kterém se mezera bude hledat), pozice 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.

    3. Pro zjištění druhého slova z textu lze použít funkci RIGHT. Funkce vyjme určitý počet znaků z textu zprava. Do buňky E3 vložit vzorec =RIGHT(A3;C3-D3). Vybrat požadovanou buňku, spustit Průvodce funkcí, kategorie Text, funkce FIND, doplnit argumenty funkce: text: A3 a počet: C3-D3. 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.

  3. Pro zjištění věku zaměstnance můžeme využít funkce pro práci s datem a časem. Skutečný tvar data a času je v sešitě dán formátem buňky.

    1. Věk lze vypočítat rozdílem mezi dvěma roky. Z data získáme rok pomocí funkce YEAR. 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 TODAY (zajistíme tak, že výpočet bude vždy aktuální). Jelikož budeme používat ve vzorci dvě funkce (funkce TODAY bude vložena na místo argumentu funkce YEAR), doporučujeme zapsat funkci ručně do buńky: =YEAR(TODAY())-YEAR(B3). Chcete-li použít Průvodce funkcí je vhodné postupovat takto: vybrat buňku F3, pustit Průvodce funkcí (ikona fx), kategorie Datum a čas, funkce YEAR, tlačítko Další>>, v poli číslo stisknout tlačítko fx pro vložení další zanořené funkce, kategorie Datum a čas, funkce TODAY, tlačítko Další>>, kliknout do pole Vzorec a stisknout tlačítko mínus -, v levém sloupci vybrat funkci YEAR, stisknout tlačítko Další>>, do pole číslo vepsat B3, stiknout OK. 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.

    2. Pro zařazení zaměstnance do věkové skupiny lze použít logickou funkci IF. Funkce byla podrobněji popsána v bodě 1. V tomto případě půjde o testování více podmínek, funkce IF bude do sebe vnořena. Do buňky G3 vložit vzorec =IF(F3<31;"do 30";IF((F3>=31) AND (F3<=60);"31 - 60";"nad 60")). Podmínky ve vzorci jsou dány podmínkami ze zadání, ve funkci IF 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 IF, která se provede, když podmínka neplatí (další testování). V druhé úrovni u funkce IF potřebujeme zadat dvě podmínky, které platí najednou – je zde využita funkce AND, která funguje jako logický operátor. 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.

  4. Soubor uložte (Ctrl+S) a zavřete (Ctrl+W).