Jak na Excel Obsah cvičení KDYŽ - teorie včetně vnořování a logických funkcí KDYŽ - teorie (2) - výpočty "KDYŽ - teorie A, NEBO" * + ;) KDYŽ - jedna podmmínka >> řešení viz skrytý list KDYŽ více podmínek >> řešení viz skrytý list KDYŽ vnořování >> řešení viz skrytý list KDYŽ - datum a čas "KDYŽ - A, NEBO více" KDYŽ - speciální znaky Úkoly Odkaz na více informací o funkcích logických Pavel Lasák "Lektor, expert na Microsoft Excel, držitel prestižního ocenění Microsoftu MVP v České republice" 5002722 Další informace ke cvičení: http://office.lasakovi.com/excel/funkce/kdyz-funkce-logicka-excel/ http://office.lasakovi.com/excel/funkce/ms-excel-funkce-logicke/ http://office.lasakovi.com/excel/funkce/ms-excel-funkce-cz-en/ http://office.lasakovi.com/excel/zaklady/on-line-kurz-zdarma/ "Copyright, Pavel Lasák 2017 - revize 06/2018 rev 04/2020 rev 05/2023, 09/2023" ##### Sheet/List 2 ##### KDYŽ (IF) - Teorie základ https://office.lasakovi.com Složka 03 - Funkce Soubor 03d - Funkce - Logicke.xlsx List Teorie Základy Pomůcka názvy funkcí česky anglicky KDYŽ IF pro podmínky Operátory = > < <> >= <= Vnořování ##### Sheet/List 3 ##### Teorie pouze podmínky https://office.lasakovi.com Složka 03 - Funkce Soubor 03d - Funkce - Logicke.xlsx List Teorie operátory Základy Výsledek Funkce PRAVDA #NÁZEV? NEPRAVDA #NÁZEV? PRAVDA #NÁZEV? Hodnota1 Hodnota 2 Výsledek Funkce Poznámka 1 1 PRAVDA #NÁZEV? čísla se rovnají 1 1 NEPRAVDA #NÁZEV? 1 není větší jak 1 2 1 PRAVDA #NÁZEV? 2 je větší jak 1 1 1 NEPRAVDA #NÁZEV? číslo vs text 1 1 PRAVDA #NÁZEV? 1 je větší rovna 1 1 1 NEPRAVDA #NÁZEV? 1 není rovno 1 2 1 PRAVDA #NÁZEV? 2 není rovno 1 ##### Sheet/List 4 ##### Logicé funkce A + NEBO http://office.lasakovi.com/ Složka 03 - Funkce Soubor 03d - Funkce - Logicke KDYŽ.xlsx Tip pokročlé A - AND ….. * NEBO - OR … + A (AND) A (AND) A (AND) * argument 1 argument 2 výsledek argument 1 argument 2 argument 3 argument 4 výsledek argument 1 argument 2 Výsledek Funkce PRAVDA PRAVDA PRAVDA PRAVDA PRAVDA PRAVDA PRAVDA PRAVDA PRAVDA PRAVDA 1 #NÁZEV? PRAVDA NEPRAVDA NEPRAVDA PRAVDA PRAVDA PRAVDA NEPRAVDA NEPRAVDA PRAVDA NEPRAVDA 0 #NÁZEV? NEPRAVDA PRAVDA NEPRAVDA PRAVDA PRAVDA NEPRAVDA PRAVDA NEPRAVDA NEPRAVDA PRAVDA 0 #NÁZEV? NEPRAVDA NEPRAVDA NEPRAVDA PRAVDA PRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA 0 #NÁZEV? PRAVDA NEPRAVDA PRAVDA PRAVDA NEPRAVDA PRAVDA NEPRAVDA PRAVDA NEPRAVDA NEPRAVDA NEBO (OR) PRAVDA NEPRAVDA NEPRAVDA PRAVDA NEPRAVDA NEBO (OR) + PRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA argument 1 argument 2 výsledek NEPRAVDA PRAVDA PRAVDA PRAVDA NEPRAVDA argument 1 argument 2 výsledek Funkce PRAVDA PRAVDA PRAVDA NEPRAVDA PRAVDA PRAVDA NEPRAVDA NEPRAVDA PRAVDA PRAVDA 2 #NÁZEV? PRAVDA NEPRAVDA PRAVDA NEPRAVDA PRAVDA NEPRAVDA PRAVDA NEPRAVDA PRAVDA NEPRAVDA 1 #NÁZEV? NEPRAVDA PRAVDA PRAVDA NEPRAVDA PRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA PRAVDA 1 #NÁZEV? NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA PRAVDA PRAVDA NEPRAVDA NEPRAVDA NEPRAVDA 0 #NÁZEV? NEPRAVDA NEPRAVDA PRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA PRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA http://office.lasakovi.com/ Revize 09/2017 ##### Sheet/List 5 ##### KDYŽ (IF) https://office.lasakovi.com Složka 03 - Funkce Soubor 03d - Funkce - Logicke.xlsx List KDYŽ - Základ Výpočty na základě jedné podmínky Pomůcky Úkol Vypočíst odměnu. Pokud vydělal (vydělala) víc než 1000 Kč odměna 100 Kč. Jak funguje funkce KDYŽ (IF) Otázka Je zde nějaký problém? Víme vše? Tip: "Lze vytvořit dynamicky, přes pomocnou tabulku?" Jméno Příjmy Výdaje Příjmy - Výdaje Odměna Odměna Pomocná tabulka pravidla Zobrazit pomocnou tabulku Eva 1700 152 1548 ODMĚNA 100 Jan 500 240 260 VYDĚLEK 1000 Iva 1475 168 1307 ODMĚNA 2 0 Ida 350 350 0 Pepa 2000 1000 1000 Pomůcka názvy funkcí Operátory česky anglicky =KDYŽ(podmínka;ano;ne) =IF(podmínka;ano;ne) = > < <> >= <= =KDYŽ(E12>1000; 100;0) =KDYŽ(E12>$J$16; $J$15;$J$17) << dynamické odkazy pomocná tabulka "= KDYŽ ( Zisk/ztráta bude větší než 1000, ANO pak odměna 100, NE tak 0 Kč)" Klávesnice Psaní znamének pravý Alt a ? nebo : http://office.lasakovi.com/ Revize 09/2017 ##### Sheet/List 6 ##### KDYŽ (IF) Jméno Příjmy Výdaje Zisk/ztráta Odměna Odměna Chybně poslední argument Odměna špatné odkazy ! Pokud vydělal víc než 1000 Kč odměna 100 Kč Eva 1700 152 1548 100 100 100 100 ODMĚNA 100 Jan 500 240 260 0 0 0 1000 VYDĚLEK 1000 Iva 1475 168 1307 100 100 100 0 Operátory ODMĚNA 2 0 Ida 350 350 0 0 0 0 0 = Pepa 2000 1000 1000 0 0 0 0 > < <> >= <= http://office.lasakovi.com/ Revize 06/2018 ##### Sheet/List 7 ##### KDYŽ (IF) více podmínek http://office.lasakovi.com/ Více podmínek - teorie Musí být splněny všechny podmínky nebo jen stačí jedna? Pomůcka názvy funkcí česky anglicky NEBO OR minimálně jedna splněna A AND všechny podmínky splněny Výpočty na základě více podmínek - splněny všechny Úkol "Pokud vydělal(a) sloupec zisk víc než 250 Kč a je žena odměna 100 Kč, jinak 0 Kč." Jméno Zisk Pohlaví Kuřák Pomoc Odměna Odměna 1 Odměna 2 Pomocná tabulka Jan 250 M kuřák ODMĚNA 100 Ida 10 F nekuřák ODMĚNA 2 0 Eva 251 F nekuřák VYDĚLEK 250 Iva 450 F kuřák Pohlaví F Ivo 450 M nekuřák =A(podmínka1; podmínka 2; ...) =AND(podmínka1; podmínka 2; ...) "=A(C12>250;D12=""F"")" Další možností k řešení: * Vnořit funkci vnořenou * Funkci přes pomocnou tabulku Výpočty na základě více podmínek - splněny minimálně jedna Úkol Pokud vydělal(a) sloupec zisk víc než 250 Kč nebo je žena odměna 100 Kč jinak 0 Kč Jméno zisk Pohlaví kuřák Pomoc Odměna odměna 1 odměna 2 Dynamicky Jan 250 M kuřák ODMĚNA 100 Ida 10 F nekuřák ODMĚNA 2 0 Eva 251 F nekuřák VYDĚLEK 250 Iva 450 F kuřák Pohlaví F Ivo 450 M nekuřák =NEBO(podmínka1; podmínka 2; ...) =OR(podmínka1; podmínka 2; ...) "=NEBO(C12>250;D12=""F"")" TIP Náhrada funkcí matematickými symboly česky anglicky Matematicky NEBO OR + A AND * Jméno zisk Pohlaví kuřák Odpracováno Věk Datum Jan 250 M kuřák 20 40 9/12/2023 Ida 10 F nekuřák 25 42 9/22/2023 Eva 251 F nekuřák 15 46 10/2/2023 Iva 450 F kuřák 10 30 10/12/2023 Ivo 450 M nekuřák 26 50 10/22/2023 Musí být splněny tři z F Zisk > 250 nekuřák Odpracováno >= 15 věk < 40 http://office.lasakovi.com/ "Revize 2018, rev 2020" ##### Sheet/List 8 ##### KDYŽ (IF) více podmínek http://office.lasakovi.com/ Více podmínek - teorie NEBO OR minimálně jedna splněna A AND všechny podmínky splněny Úkol Dynamicky "Pokud vydělal(a) víc než 250 Kč a je žena odměna 100 Kč, jinak 0 Kč" ODMĚNA 100 Jméno zisk Pohlaví kuřák Pomoc Odměna odměna 1 odměna 2 ODMĚNA 2 0 Jan 250 M kuřák NEPRAVDA 0 0 0 VYDĚLEK 250 Ida 10 F nekuřák NEPRAVDA 0 0 0 Pohlaví F Eva 251 F nekuřák PRAVDA 100 100 100 Iva 450 F kuřák PRAVDA 100 100 100 Ivo 450 M nekuřák NEPRAVDA 0 0 0 =A(podmínka1; podmínka 2; ...) En: AND "=A(C12>250;D12=""F"")" Úkol Pokud vydělal(a) víc než 250 Kč nebo je žena odměna 100 Kč jinak 0 Kč Dynamicky ODMĚNA 100 Jméno zisk Pohlaví kuřák Pomoc Odměna odměna 1 odměna 2 ODMĚNA 2 0 Jan 250 M kuřák NEPRAVDA 0 0 0 Ida 10 F nekuřák PRAVDA 100 100 0 VYDĚLEK 250 Eva 251 F nekuřák PRAVDA 100 100 100 Pohlaví F Iva 450 F kuřák PRAVDA 100 100 100 Ivo 450 M nekuřák PRAVDA 100 100 0 =NEBO(podmínka1; podmínka 2; ...) En: OR "=NEBO(C12>250;D12=""F"")" http://office.lasakovi.com/ Revize 2018 ##### Sheet/List 9 ##### KDYŽ (IF) - vnořování Pomůcky https://office.lasakovi.com/ Výpočty na základě více podmínek - splněny všechny Jak funguje vnořování funkce KDYŽ (IF) Úkol Přiřaďte studentům známku za předpokladu Pomocná tabulka od 90 bodů (včetně) známka …. A A 90 od 80 ………. B B 80 od 60 …… .. C C 60 jinak ………… E E Jméno Body Známka Ivo 91 Jan 90 Eva 89 Pepa 75 Emo 50 Řešení nápověda A "=KDYŽ(C13>=90;""A"";KDYŽ(C13>=80;""B"";KDYŽ(C13>=60;""C"";""E"")))" CZ "=IF(C13>=90;""A"";IF(C13>=80;""B"";IF(C13>=60;""C"";""E"")))" EN TIP "Používat Alt + Enter V buňce provede nový řádek" "=KDYŽ(C13>=90;""A""; KDYŽ(C13>=80;""B""; KDYŽ(C13>=60;""C"";""E"") ) )" http://office.lasakovi.com/ "Rev 05/2020, 09/2021" ##### Sheet/List 10 ##### KDYŽ (IF) - vnořování Úkol Přiřaďte studentům známku za předpokladu od 90 bodů (včetně) známka …. A od 80 ………. B Jméno Body Známka od 60 …… .. C Ivo 91 A jinak ………… E Jan 90 A Eva 89 B Pepa 75 C Ondra 60 C xxx 10 E řešení nápověda "=KDYŽ(F7>=90;""A"";KDYŽ(F7>=80;""B"";KDYŽ(F7>=60;""C"";""E"")))" http://office.lasakovi.com/ ##### Sheet/List 11 ##### KDYŽ ve spojení s datumem (časem) http://office.lasakovi.com/ Úkol: Co bylo dnes zaplaceno Jméno Zaplaceno Pohlaví Bylo zaplaceno? Dnešní den 10/2/2023 Jan 9/30/2023 M Ida 10/1/2023 F Poznámka: Eva 10/2/2023 F Podrobněji o funkcích datum a čas v sekci o funkcích pro datum a čas Iva 10/3/2023 F Ivo 10/4/2023 M Je faktura po splatnosti? ID faktura Datum splatnosti Zaplaceno Je po splatnosti? AB1 9/26/2023 ANO AB2 9/29/2023 NE Dnešní den 10/2/2023 AB3 10/1/2023 ANO AB4 10/1/2023 NE AB5 10/2/2023 ANO Poznámka: AB6 10/2/2023 NE Pokud nebyla zaplacena a den splatnosti je menší než dnešní den AB7 10/4/2023 NE AB8 10/4/2023 ANO AB9 10/8/2023 ANO Bylo vytvořeno dopoledne Úkol Čas provedení Dopoledne? Dopoledne? Poznámka: snídaně 7:50 Potřebujeme zjistit zda byl kol vytvořen před polednem (12:00) svačina 10:12 oběd 12:05 čas 12:00 kafe 15:10 večeře 19:02 http://office.lasakovi.com/ ##### Sheet/List 12 ##### KDYŽ náhrada a více dalších možností http://office.lasakovi.com/ Zákazník Platí včas Počet let Výsledek sleva Úkol sleva Kontrola A ANO 10 ; 10% 10% B ANO 11 Platí včas + do 10 let zákazník 5% 10% C ANO 5 Neplatí včas + 10 (včetně) a více let zákazník 2% 5% D NE 11 Ostatní 0% 2% E NE 10 2% F NE 5 0% Náhrada logických funkcí NEBO (OR) a A (AND) Zákazník Platí včas Počet let ANO? Úkol Funkce AND A Náhrada * A ANO 10 A #NÁZEV? A #NÁZEV? B ANO 11 A A C ANO 5 N N D NE 28 A A E NE 30 A A F NE 5 N N Zákazník Platí včas Počet let ANO? Úkol A ANO 10 N B ANO 11 b) nad 10 a pod 30 A C ANO 5 N D NE 28 A E NE 30 N F NE 5 N ##### Sheet/List 13 ##### KDYŽ náhrada a více dalších možností http://office.lasakovi.com/ Musí být splněno F nad 32 let auto Brno "M, F" Pohlaví Město Věk Předmět Postředek Výsledek Funkce M Brno 32 M auto 3 #NÁZEV? M Opava F 1 #NÁZEV? F Brno 35 M auto 5 #NÁZEV? F Čj 1 #NÁZEV? X 0 #NÁZEV? ##### Sheet/List 14 ##### KDYŽ ve spojení se speciálními znaky http://office.lasakovi.com/ Uvozovky Úkol Úkol je v buňce uvozovka? Znak Výsledek Funkce * NEPRAVDA #NÁZEV? """" PRAVDA #NÁZEV? """""" NEPRAVDA #NÁZEV? "*""""" NEPRAVDA #NÁZEV? Uvozovky dvoje Úkol Úkol je v buňce dvojita uvozovka? Znak Výsledek Funkce """""" PRAVDA #NÁZEV? """" NEPRAVDA #NÁZEV? """a""" NEPRAVDA #NÁZEV? """""""" NEPRAVDA #NÁZEV? """A" PRAVDA AB NEPRAVDA """1""" PRAVDA """2""" NEPRAVDA NEPRAVDA ##### Sheet/List 15 ##### KDYŽ - úkoly http://office.lasakovi.com/ Přidejte známky 1) Ručně vnořením funkcí 2) Využitím tabulky ve vnořené funkci 3) Využít funkcí SVYHLEDAT (až budete mít za sebou) Jméno Body Známka Využijte pomocné tabulky Ivo 91 90 A Jan 90 80 B Eva 89 70 C Pepa 75 60 D 50 E F Poznámka 90 (tj. včetně) a více má A atd. Pokročilé Co když nepůjde o číslo? Využijte pomocné tabulky Jméno Body Známka 90 A Ivo 91 80 B Jan 80 70 C Eva 70 60 D Pepa 55 50 E F Poznámka 90 (tj. včetně) a více má A atd. ##### Sheet/List 16 ##### Další informace http://office.lasakovi.com/ A (AND) BYCOL (BYCOL) BYROW (BYROW) "IFERROR (IFERROR) od ver. 2007, někdy CHYBHODN" IFNA (IFNA) od verze 2013 IFS (IFS) od verze 2016 z balíku (Office 365) KDYŽ (IF) LAMBDA (LAMBDA) LAMBDA (LAMBDA) - prakticky LET (LET) NE (NOT) NEBO (OR) NEPRAVDA (FALSE) PRAVDA (TRUE) REDUCE (REDUCE) SCAN (SCAN) SWITCH (SWITCH) - od verze Excel 2016 (z balíku Office 365) XOR (XOR) - od verze Excel 2013 Pavel Lasák rev 09 / 2023