Jak Na Excel TEST: Závěrečný Excel MUNI - 24.5 Text 7 Tabulka vyp 14 Bodů 30 Vyhl 6 Čas na zpracování 75 minut Obraty 17 KT 6 Pavel Lasák Celkem 30 Konzultant a školitel Microsoft Excel Vaše jméno Vaše UČO www.JakNaExcel.cz ##### Sheet/List 2 ##### Vyhledávací funkce Test Body 6 3.6 https://office.lasakovi.com Muži Ženy " Barva Velikost" modrá bílá červená zelená černá " Barva Velikost" modrá bílá červená zelená černá S 55 61 67 73 74 S 55 61 67 73 74 M 59 65 71 77 78 M 59 65 71 77 78 L 63 69 75 81 82 L 63 69 75 81 82 XL 67 73 79 85 86 XL 67 73 79 85 86 XXL 71 77 83 89 90 XXL 71 77 83 89 90 Úkol (1) Doplnit tabulku o cenu pro muže (Tabulka B7:G12) využitím Vyhledávacích funkcí Velikost Barva Cena S modrá 2 M bílá L červená XL zelená XXL černá M bílá L červená Úkol (2) Doplnit buňku o cenu. Využijte tabulky B7:G12 a využijte vyhledávacích funkcí 4 Pohlaví Velikost Barva Cena Muži S modrá Ženy S modrá Muži M bílá Ženy L červená Žena XXXL červená "Tip: Když jde o ženu tak sloupec je + x buněk vlevo. Tj. lze využít podmínkovou funkci, pro počet." "Tip2: Můžete využít pomocné sloupce, kdy vypočtete ceny pro muže a ženy zvlášť a pak přes podmínku zovlíte vhodnou cenu" ##### Sheet/List 3 ##### Jméno Datum narození Město Den v týdnu Měsíc Rok narození Věk Výplata Osobní ohodnocení Celková výplata 14 body 8.4 Martin 5/22/1991 Praha 1 25000 Michal 4/8/1991 Praha 3 25500 Raushan 5/18/1991 Praha 4 26000 Zuzana 7/13/1999 26500 Osobní ohodnocení [%] z výplaty 10% Alina 4/12/1996 Liberec 27000 Viktoriya 2/25/1994 Brno 27500 Průměrný věk zaměstnanců: Kamilla 9/21/1994 28000 Nejvyšší věk zaměstnance: Nathalie 8/26/1998 Praha 5 28500 Nejnižší věk zaměstnance: Vendula 8/1/1992 Praha 2 29000 Kolik zaměstnanců má rok narození 1995? Roy 10/24/1994 Ostrava 29500 U kolika studentů není vyplněno město? Oleksandr 1/18/1993 Liberec 30000 Úkoly Tomáš 1/25/1991 Brno 30500 1 "1. Vyplňte sloupec ""Den v týdnu"" pomocí funkce, která vrací číselný formát dne v týdnu z hodnot sloupce ""Datum narození"". Tak ať pondělí představuje 1 (jedničku)" Mariia 7/8/1997 31000 1 "2. Vyplněte sloupec ""Měsíc"" pomocí funkce, která vrací textový formát měsíce z hodnot sloupce ""Datum narození"". Např. číslo ""1"" je leden, tip lze využít i formát" Dmitrii 2/26/1994 Praha 1 31500 1 "3. Ze sloupce ""Datum narození"" vyseparujte rok narození (sloupec ""Rok narození"")." Bohdan 9/3/1995 Praha 3 32000 2 4. Vypočítejte věk (sloupec věk) každého zaměstnance. Suhrob 10/24/1992 Brno 32500 1 "5. Částkám ve sloupci (Výplata) přidejte znak měny ""Kč""" Daniil 4/11/2000 Jihlava 33000 1 "6. Vypočítejte ""Osobní ohodnocení"" (sloupec Osobní ohodnocení), které je stanoveno v buňce ""M5"". " Aleksandra 6/25/1999 33500 2 "7. Vypočítejte celkou výplatu (celková výplata), která se skládá z ""Výplaty"" a ""Osobního ohodnocení"". " Natálie 9/20/1995 Praha 1 34000 1 8. Do buňky M8 vypočítejte průměrný věk zaměstnanců. Marie 7/22/1995 Praha 3 34500 1 9. Do buňky M9 vypočítejte nejvyšší věk zaměstnance. Ekaterina 10/5/1998 Praha 1 35000 1 10. Do buňky M10 vypočítejte nejnižší věk zaměstnance. David 6/12/1999 Praha 5 35500 2 11. Do buňky M11 zjištěte u kolika zaměstnanců není vyplněno město. Ladislav 4/30/1997 Praha 2 36000 ##### Sheet/List 4 ##### Textové funkce 7 7 bodu 4.2 nutno využít funkce! rozdělit Rozdělit a získat webovou adresu mezi znaky // a / Příklad http://www.JakNaExcel.cz/umime www.JakNaExcel.cz Tip využijte klidně pomocné sloupce Pozor Opravdu chci jen co je mezi // a / Webová adresa web https://seo.example.com/umime 4 bod http://nic.example.com/neumime https://muni.cz/ https://www.muni.cz/ESF http://www.muni.cz/ESF https://testovaci.dlouha.adresa.muni.cz/ESFyxvv http://www.1.cz/ESF https://www.muni.cz/ESF Pozor https ve http nutno využít funkce! "Zjistěte pomocí funkcí počet písmene ""a"" ve slovech" za využití funkcí Slovo Počet písmen Abrakadabra 3 Nejkulaťoulnkatější Otestovat Aa Nic Pavel Lasák 2016 rev 2019 ##### Sheet/List 5 ##### Vytvořte / vypočtete obraty 17 10.2 Pozor nutno na rozpad kódu využít textové a vyhledávací funkce! Pro data na listě Data dopočtěte obraty (cena * počet ks) využitím vyhledávacích funkcí Rozpadnutí 4 Prodejce 3 "Kód je ve sloupci A, nutný rozpad kódu na listě DATA - využítím textových funkcí" Město 2 Podklady co které znaky znamenají jsou uvedeny níže (list Obraty) Produkt 4 "Pomocí funkci vyplnit sloupce Město, Produkt, Prodejce, Cena, Obrat" Cena 3 Poznámka u názvu celá jména ne pouze zkratku (tj. pro PR .. Praha)! Tj. využít příslušnou vyhledávací funkci) Obrat 1 Tip: pozor na rozpad kódu. Výsledek textových funkcí je text! Zdoj a členění kódu Rozpad kódu ID_PR-H-20130201_01_Fa111 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Id Město Id Skupina Id Prodejce ID Produkt Cena _ 0 1 _ F a 1 1 1 PR Praha H Hokej Fa Fantomas Pepa 111 Helma 251 Produkt OP Opava G Golf Su Superman Jano 112 Hůl 324 Prodejce OV Ostrava P PinkPong Ra Rakosníček Franta 113 Míček 75 pořadi BR Brno F Fotbal Bo Bond James 114 Chránče 784 Den OL Olomouc S Horolezectví Wo Wolker Johny 115 Boty 2257 Měsíc PL Plzeň K Kuželky Al Aladin Velký 116 Lopta 125 Rok ZL Zlín Ba Bathman Malý 117 Pálka 1257 Skupina UH Uherské Hradiště Sp Spiderman Lozicí 118 Kopačky 3578 Město 119 Karabina 872 Jen označení že jde o kód Tabulky formátované jako tabulka! 120 Sedák 1398 121 Osma 350 122 Brusle 2174 123 Trenér 159 124 Kopačák 336 125 Puk 99 126 Lano 5789 127 Síť 4578 128 Kuželka 478 129 Leštidlo 155 130 Bagl 2157 131 Dres 3257 ##### Sheet/List 6 ##### Vytvořte kontingenční tabulku 6 3.6 Požadavek Pro každé řešení vytvořte samostantou kontingenční tabulku na nový list "Listy pojmenujte 01, 02, 03, 04" odpovězte na otázky využitím kontingenček 01 Počet prodejů celkem 1 02 Kdo je nejlepší prodejce (Suma ze zisk)? 1 03 Který výrobek je nejprodávanější 2 04 Kolik zisku je z webu 2 ID "Jméno prodavače" "Druh sportu" "Prodavaný produkt" Způsob prodeje Zisk 1 Fantomas Golf Odpalovací hůl prodejna 66.6 2 Pavel Hokej Golfový míček prodejna 244.8 3 Fantomas Golf Golfový míček prodejna 250.2 4 Fantomas Hokej Chrániče prodejna 10.5 5 Pavel Golf Odpalovací hůl prodejna 61.2 6 Fantomas Hokej Chrániče prodejna 116.6 7 Pavel Golf Golfový míček web 31.5 8 Pavel Golf Odpalovací hůl web 41.4 9 Rakosniček Golf Golfový míček prodejna 48 10 Rakosniček Golf Golfový míček prodejna 55.5 11 Krakonoš Golf Golfový míček prodejna 58.5 12 Fantomas Golf Odpalovací hůl prodejna 206.7 13 Rakosniček Hokej Chrániče prodejna 153.7 14 Pavel Hokej Hokejka prodejna 210.8 15 Fantomas Golf Odpalovací hůl web 500 16 Pavel Hokej Chrániče web 20.6 17 Rakosniček Hokej Chrániče prodejna 10.2 18 Fantomas Golf Odpalovací hůl prodejna 39.6 19 Krakonoš Hokej Puk prodejna 217.5 20 Monika Golf Odpalovací hůl prodejna 61.2 21 Fantomas Hokej Chrániče prodejna 148.5 22 Pavel Hokej Hokejka prodejna 170 23 Krakonoš Hokej Hokejka web 197.2 24 Pavel Hokej Chrániče web 15 25 Rakosniček Hokej Chrániče prodejna 24 26 Pavel Golf Odpalovací hůl prodejna 55.8 27 Alena Hokej Golfový míček prodejna 156.4 28 Monika Hokej Puk prodejna 292.5 29 Krakonoš Golf Odpalovací hůl prodejna 41.4 30 Rakosniček Hokej Chrániče prodejna 10 31 Alena Hokej Puk web 157.5 32 Krakonoš Hokej Hokejka web 142.8 33 Pavel Hokej Chrániče prodejna 47 34 Fantomas Hokej Hokejka prodejna 136 35 Rakosniček Golf Golfový míček prodejna 57 36 Krakonoš Hokej Puk prodejna 270 37 Pavel Golf Odpalovací hůl prodejna 50.4 38 Rakosniček Golf Odpalovací hůl prodejna 46.8 39 Krakonoš Golf Odpalovací hůl web 54 40 Monika Hokej Puk web 270 41 Pavel Golf Odpalovací hůl prodejna 46.8 42 Krakonoš Hokej Puk prodejna 285 43 Pavel Hokej Chrániče prodejna 14 44 Pavel Golf Golfový míček prodejna 30 45 Krakonoš Hokej Puk prodejna 232.5 46 Fantomas Golf Golfový míček prodejna 48 47 Krakonoš Hokej Puk web 292.5 48 Rakosniček Hokej Chrániče web 180.2 49 Pavel Golf Golfový míček prodejna 45 50 Krakonoš Hokej Chrániče prodejna 180.2 51 Rakosniček Hokej Chrániče prodejna 180.2 52 Monika Golf Odpalovací hůl prodejna 57.6 53 Rakosniček Golf Golfový míček prodejna 55.5 54 Fantomas Golf Odpalovací hůl prodejna 195 55 Rakosniček Hokej Chrániče web 180.2 56 Fantomas Golf Golfový míček web 51 57 Krakonoš Hokej Hokejka prodejna 238 58 Pavel Hokej Chrániče prodejna 206.7 59 Pavel Hokej Hokejka prodejna 163.2 60 Pavel Hokej Chrániče prodejna 143.1 61 Monika Hokej Hokejka prodejna 204 62 Pavel Hokej Hokejka prodejna 176.8 63 Monika Golf Odpalovací hůl web 66.6 64 Krakonoš Hokej Puk web 292.5 65 Rakosniček Hokej Chrániče web 180.2 66 Pavel Golf Golfový míček prodejna 45 67 Krakonoš Hokej Chrániče prodejna 180.2 68 Rakosniček Hokej Chrániče prodejna 180.2 69 Monika Golf Odpalovací hůl prodejna 57.6 70 Rakosniček Golf Golfový míček prodejna 55.5 71 Fantomas Golf Odpalovací hůl prodejna 195 72 Rakosniček Hokej Chrániče web 180.2 73 Fantomas Golf Golfový míček web 51 74 Krakonoš Hokej Hokejka prodejna 238 75 Pavel Hokej Chrániče prodejna 206.7 76 Pavel Hokej Hokejka prodejna 163.2 77 Pavel Hokej Chrániče prodejna 143.1 78 Monika Hokej Hokejka prodejna 204 79 Pavel Hokej Hokejka prodejna 176.8 80 Monika Golf Odpalovací hůl web 66.6 81 Pavel Golf Odpalovací hůl prodejna 61.2 82 Fantomas Hokej Chrániče prodejna 116.6 83 Pavel Golf Golfový míček web 31.5 84 Pavel Golf Odpalovací hůl web 41.4 85 Rakosniček Golf Golfový míček prodejna 48 86 Rakosniček Golf Golfový míček prodejna 55.5 87 Krakonoš Golf Golfový míček prodejna 58.5 88 Fantomas Golf Odpalovací hůl prodejna 206.7 89 Rakosniček Hokej Chrániče prodejna 153.7 90 Pavel Hokej Hokejka prodejna 210.8 91 Fantomas Golf Odpalovací hůl web 500 92 Pavel Hokej Chrániče web 20.6 93 Rakosniček Hokej Chrániče prodejna 10.2 94 Fantomas Golf Odpalovací hůl prodejna 39.6 95 Krakonoš Hokej Puk prodejna 217.5 96 Monika Golf Odpalovací hůl prodejna 61.2 97 Fantomas Hokej Chrániče prodejna 148.5 98 Pavel Hokej Hokejka prodejna 170 99 Krakonoš Hokej Hokejka web 197.2 100 Pavel Hokej Chrániče web 15 101 Rakosniček Hokej Chrániče prodejna 24 102 Pavel Golf Odpalovací hůl prodejna 55.8 103 Alena Hokej Golfový míček prodejna 156.4 104 Monika Hokej Puk prodejna 292.5 105 Krakonoš Golf Odpalovací hůl prodejna 41.4 106 Pavel Golf Odpalovací hůl prodejna 61.2 107 Fantomas Hokej Chrániče prodejna 116.6 108 Pavel Golf Golfový míček web 31.5 109 Pavel Golf Odpalovací hůl web 41.4 110 Rakosniček Golf Golfový míček prodejna 48 111 Rakosniček Golf Golfový míček prodejna 55.5 112 Krakonoš Golf Golfový míček prodejna 58.5 113 Fantomas Golf Odpalovací hůl prodejna 206.7 114 Rakosniček Hokej Chrániče prodejna 153.7 115 Pavel Hokej Hokejka prodejna 210.8 116 Fantomas Golf Odpalovací hůl web 500 117 Pavel Hokej Chrániče web 20.6 118 Rakosniček Hokej Chrániče prodejna 10.2 119 Fantomas Golf Odpalovací hůl prodejna 39.6 120 Krakonoš Hokej Puk prodejna 217.5 121 Monika Golf Odpalovací hůl prodejna 61.2 122 Fantomas Hokej Chrániče prodejna 148.5 123 Pavel Hokej Hokejka prodejna 170 124 Krakonoš Hokej Hokejka web 197.2 125 Pavel Hokej Chrániče web 15 126 Rakosniček Hokej Chrániče prodejna 24 127 Pavel Golf Odpalovací hůl prodejna 55.8 128 Alena Hokej Golfový míček prodejna 156.4 129 Monika Hokej Puk prodejna 292.5 130 Krakonoš Golf Odpalovací hůl prodejna 41.4 131 Rakosniček Hokej Chrániče prodejna 10 132 Alena Hokej Puk web 157.5 ##### Sheet/List 7 ##### KÓD Ks Prodejce Město Produkt Cena Obrat ID_OV-S-20130214_10_Su120 32 ID_OV-S-20130214_11_Ra121 24 ID_PR-H-20130216_12_Bo122 20 ID_PR-H-20130216_13_Wo123 45 ID_OV-F-20130216_14_Al124 45 ID_OV-H-20130217_15_Ba125 45 ID_BR-S-20130221_16_Sp126 12 ID_BR-H-20130222_17_Fa127 1 ID_BR-K-20130222_18_Su128 145 ID_OV-K-20130226_19_Ra128 10 ID_OL-G-20130303_20_Bo113 20 ID_OL-K-20130304_21_Wo129 30 ID_PR-G-20130305_22_Al113 12 ID_BR-S-20130306_23_Ba120 15 ID_OP-F-20130307_24_Sp124 18 ID_PL-G-20130308_25_Fa130 6 ID_ZL-F-20130308_26_Su131 12 ID_UH-H-20130309_27_Ra114 49 ID_OP-H-20130310_28_Bo122 51 ID_PL-H-20130310_29_Wo111 14 ID_ZL-P-20130310_30_Al116 87 ID_OP-F-20140214_08_Sp118 8 ID_OP-S-20140214_09_Fa119 24 ID_OV-S-20140214_10_Su120 32 ID_OV-S-20140214_11_Ra121 24 ID_PR-H-20140216_12_Bo122 20 ID_PR-H-20140216_13_Wo123 45 ID_OV-F-20140216_14_Al124 45 ID_OV-H-20140217_15_Ba125 45 ID_BR-S-20140221_16_Sp126 12 ID_BR-H-20140222_17_Fa127 1 ID_BR-K-20140222_18_Su128 145 ID_OV-K-20140226_19_Ra128 10