Jak na Excel Obsah cvičení Textové funkce základ Textové základy (2) malá velká pímena Číslo vs Text Pokročilé ukázky Dynamické doplňování (od Excel 2013) 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/ms-excel-funkce-textove/ http://office.lasakovi.com/excel/funkce/funkce-textove-prakticke-priklady-excel/ 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" ##### Sheet/List 2 ##### Teorie Počítání písmen a znaků 1 2 3 4 5 6 7 8 9 10 11 P a v e l L a s á k 5 4 3 2 1 1 2 3 4 5 6 7 8 I v a M a l á 4 3 2 1 1 2 3 4 5 6 7 8 9 10 11 12 13 P a v e l L a 1 2 3 4 5 6 7 8 9 10 I v a M a l á ##### Sheet/List 3 ##### Textové základ http://JakNaExcel.cz/ Spojování textů Teorie pomoc v CZ a EN Úkol Spojit jméno a příjmení CONCATENATE CONCATENATE jméno přijmení Spojeno 1 Spojeno 2 Syntaxe =CONCATENATE(text1;text2;...) Iva Mala Eva Velka CONCAT CONCAT Abrakadabra Nejakedelsi "Pozor od Excel 2016 CONTACT, ale nezafunguje pokud se otevře v Excel 2013!" Tip Titul? Úkol "Vytvoř emailovou adresu, jméno.prijmeni@example.com" jméno přijmení > email iva mala eva velka abrakadabra nejakedelsi Počty znaků Teorie pomoc v CZ a EN Úkol Kolik písmen je v této větě / slově? Příklad Jméno / věta Počet písmen DÉLKA LEN Alfa Syntaxe =DÉLKA(text) Beta Pozor na mezery v textech Gama Alfa Beta Ano to byl on. Úprava počtu znaků Teorie pomoc v CZ a EN Úkol Z telefoního čísla potřebuji posledních 9 znaků (čísel) Příklad ZLEVA ZPRAVA "LEFT, RIGHT" Telefonní číslo Řešení VLEVO VPRAVO "LEFT, RIGHT" 420800111222 Syntaxe =ZPRAVA(text;znaky) +420800111333 Pozor na znaky v textech +420 800 111 555 Úkol Z rč. čísla potřebuji prvních 6 znaků (čísel) Příklad ZLEVA ZPRAVA "LEFT, RIGHT" Rodné číslo Řešení VLEVO VPRAVO "LEFT, RIGHT" 771133/2587 Syntaxe =ZLEVA(text;znaky) 771133/258 Pozor na znaky v textech 77-11-33 Úkol Chci text mezi mezerami Příklad Kód výrobků Řešení 1 Číslo??? ČÁST MID AB-123-CD Syntaxe =ČÁST(text;start;počet_znaků) AK-555-CD BB-123-MM TIP CB-987-CD HODNOTA (VALUE) NUMBERVALUE (NUMBERVALUE) přičíst nula nebo vynásobit jednička Funkce: Informace Záměna znaků Teorie pomoc v CZ a EN Úkol "1) Nahraďte LO hvězdičkou 2) Nahraďte třetí a čtvrté písmeno hvězdičkou" DOSADIT NAHRADIT NAHRADIT REPLACE Slovo Řešení 1 Řešení 2 DOSADIT SUBSTITUTE KOLO KOLOMAZ Syntaxe =DOSADIT(text;starý;nový;instance) KOloMAZ KOabMAZ Syntaxe =NAHRADIT(starý;start;znaky;nový) KOLOKOLO Úkol Odstraňte mezeru(y) pomocí funkce. Všechny! Speciální znak! Slova Řešení 1 Jan Malý Jan Velký 1 0 0 0 Úkol Odstraňte mezeru Příklad pokročilé mezera nemusí být kasiká mezera ;) Slova Řešení 1 DOSADIT SUBSTITUTE Jan Malý Jan Velký 1 000 Hledání Teorie pomoc v CZ a EN Úkol Potřebuji pozici mezery (na kterém znaku se nachází?) Příklad Jméno Řešení 1 NAJÍT FIND Jan Man Syntaxe Jana Malý Abrakadabra Mon Promázavaní mezer Teorie pomoc v CZ a EN Úkol "Odstraňte přebytečné mezery, neboli z více mezer nechat jen jednu" Text Řešení 1 PROČISTIT TRIM A B C Syntaxe A B C Afwdh dh ddj ##### Sheet/List 4 ##### Textové http://JakNaExcel.cz/ Velikost písmen převádění na malá a velká písmena Teorie pomoc v CZ a EN Úkol všechny velké jméno Vše velké VELKÁ UPPER iva MALÁ LOWER eva VELKÁ2 PROPER abrakadabra Úkol všechny malé jméno vše malé ivA eva abrakadABEra Úkol jen první velké jméno Prvmí velké ivA eva abrakadABEra sdf asdg sad Ostatní a jiné Teorie pomoc v CZ a EN jméno přijmení iva mala eva velka abrakadabra nejakedelsi Ostatní a jiné ZVOLIT CHOOSE Roční období léto "=ZVOLIT(2;""jaro"";""léto"";""podzim"";""zima"")" jaro léto "Tip, SVYHLEDAT" podzim zima Vyber číslem 1 2 Tip navíc S datumem a časem > první den následujího týdne (pondělí) "Tuesday, March 19, 2019" "Monday, March 25, 2019" #NÁZEV? "Pavel Lasák, rev 2017" ##### Sheet/List 5 ##### Textové číslo < - > hodnota http://JakNaExcel.cz/ Číslo >Hodnota převádění na malá a velká písmena Funkce v CZ a EN Převést na číslo Není číslo Číslo Číslo HODNOTA VALUE 123 123 HODNOTA.NA.TEXT TEXT >>. Umět vlastní formát 233 233 @ … text 233 233 Číslo Text Text Viz informační funkce 123 123 JE.ČÍSLO ISNUMBER 555 555 přidat desetiná místa 777 777 Tip Funkce vyhledávací "Pavel Lasák, rev 2017" ##### Sheet/List 6 ##### Textové http://JakNaExcel.cz/ Úkol Rozdělit na jméno příjmení … Celé jméno Jméno Příjmení Iniciály Jan Malý Arabela Velká Pavel Lasák Úkol chci najít číslo mezi minusy - pozor musí být fakt číslo ;) Kód výrobků Řešení 1 ABC-123-CD AK47-554445-CDCD ACDC-1-MM CB-987-CD Úkol Rozdělit před a za mínus Údaje Po mínus Před mínus řešení VT-aadsjg 3 VT MTR-gdgsgdA 4 MTR AA-sgjdlg 3 AA RRABC-g wgw q 6 RRABC VT-15547 3 VT Úkol Rozděit na dvě části webová adresa (a co je za lomítkem) základní adresu text za lomítkem řešení http://seo.example.com/umime 23 http://seo.example.com/ 28 5 umime http://nic.example.com/neumime 23 http://nic.example.com/ 30 7 neumime Úkol kolik písmenek a obsahují slova slovo počet a abrakadabra nejkulatoulinkatější aaa necobez něcos ##### Sheet/List 7 ##### Celé Jméno Jméno Příjmení Iniciály HACK - Dynamické doplňování - Ctrl + E "Celé, Jméno" Celé; Jméno Celé_Jméno číslo Jan Malý Jan Malý JM "Jan, Malý" Jan; Malý Jan_Malý 1 Arabela Velká Text do sloupců "Arabela, Velká" Arabela; Velká Arabela_Velká 2 Pavel Lasák "Pavel, Lasák" Pavel; Lasák Pavel_Lasák 3 Jan Vopršálek "Jan, Vopršálek" Jan; Vopršálek Jan_Vopršálek 4 Jana Vopršálková Podklady pro speciální dozazy "Jana, Vopršálková" Jana; Vopršálková Jana_Vopršálková 5 Iva Malá data mají smysl až po příslušném dotazu "Iva, Malá" Iva; Malá Iva_Malá 6 Iva Velká "Iva, Velká" Iva; Velká Iva_Velká 7 Jan Malý "Jan, Malý" Jan; Malý Jan_Malý 8 Jana Velká Viz samostaný sešoit dynamické doplmnění "Jana, Velká" Jana; Velká Jana_Velká 9 Abraka Dabra "Abraka, Dabra" Abraka; Dabra Abraka_Dabra 10 ##### Sheet/List 8 ##### Tvorba řad http://JakNaExcel.cz/ Viz samostaný sešit tvroba řad Viz sešit řady Jak tvořit řady využitím Textových funkcí Poznámka: Funkce KO-1-2-2-18-AB-A List připravený na připadné dotazy specíální kód OK- IDEČKO - DEN - MĚSÍC - ROK poslední dvě čísla- AB - KOD-velkým písmenem idečko den měsíc rok kód Abeceda Abeceda Slovo Den Měsíc 1 1 1 2018 a A A Koruna Pondělí Leden 2 2 2 2018 b B B Koruna Úterý Únor Datum a něco 3 3 3 2018 c C C Koruna Středa Březen Dne-12-mesic-2 4 4 4 2018 d D D Koruna Čtvrtek Duben 5 5 5 2018 e E E Koruna Pátek Květen 6 6 6 2018 f F F Koruna Sobota Červen 7 7 7 2018 g G G Koruna Neděle Červenec 8 8 8 2018 a H H Koruna Pondělí Srpen 9 9 9 2018 b I I Koruna Úterý Září 10 10 10 2018 c J J Koruna Středa Říjen 11 11 11 2018 d K K Koruna Čtvrtek Listopad 12 12 12 2018 e L L Koruna Pátek Prosinec 13 13 1 2018 f M M Koruna Sobota Leden 14 14 2 2018 g N N Koruna Neděle Únor 15 15 3 2018 a O O Koruna Pondělí Březen 16 16 4 2018 b P P Koruna Úterý Duben 17 17 5 2018 c Q Q Koruna Středa Květen 18 18 6 2018 d R R Koruna Čtvrtek Červen 19 19 7 2018 e S S Koruna Pátek Červenec 20 20 8 2018 f T T Koruna Sobota Srpen 21 21 9 2018 g U U Koruna Neděle Září 22 22 10 2018 a V V Koruna Pondělí Říjen 23 23 11 2018 b W W Koruna Úterý Listopad 24 24 12 2018 c X X Koruna Středa Prosinec 25 25 1 2018 d Y Y Koruna Čtvrtek Leden 26 26 2 2018 e Z Z Koruna Pátek Únor 27 27 3 2018 f [ A Koruna Sobota Březen 28 1 4 2018 g \ B Koruna Neděle Duben 29 2 5 2018 a ] C Koruna Pondělí Květen 30 3 6 2018 b ^ D Koruna Úterý Červen 31 4 7 2018 c _ E Koruna Středa Červenec 32 5 8 2018 d ` F Koruna Čtvrtek Srpen ##### Sheet/List 9 ##### Tvorba řad http://JakNaExcel.cz/ Ve spojení s funkcí SOUČIN.SKALÁRNÍ Viz samoastaná kapitola Viz využití v SQL Pozor: Jiné názvy funkcí Velikost písmen ##### Sheet/List 10 ##### Textové úkoly http://JakNaExcel.cz/ Vytvořit emailovou adresu Všechna písmena malá jméno přijmení > email IVa mala Eva velka Anna nejakedelsi Viz předchozí úkoly na jiných listech Prostor pro vaše poznámky ##### Sheet/List 11 ##### Textové http://JakNaExcel.cz/ Odkazy na popis jednotlivých funkcí CONCAT (CONCAT) - Sloučí několik textových řetězců do jednoho ze zadané oblasti - od Excel 2016 CONCATENATE (CONCATENATE) - Sloučí několik textových řetězců do jednoho ČÁST (MID) - počet znaků z textového řetězce od zadané pozice DÉLKA (LEN) - počet znaků textového řetězce. DOSADIT (SUBSTITUTE) - nahradí v textu zadaný řetězec jiným. HLEDAT (SEARCH) - od kolikátého znaku v daném řetězci začíná první výskyt hledaného znaku HODNOTA (VALUE) - nalezne textovou hodnotu HODNOTA.NA.TEXT (TEXT) - zformátuje číslo a převede ho na text KČ (DOLLAR) - převede číslo na text ve formátu měny KÓD (CODE) - číselný kód prvního znaku textového řetězce MALÁ (LOWER) - převede text na malá písmena NAHRADIT (REPLACE) - nahradí znaky uvnitř textu NAJÍT (FIND) - nalezne textovou hodnotu uvnitř jiné OPAKOVAT (REPT) - zopakuje text PROČISTIT (TRIM) - odstraní z textu mezery STEJNÉ (EXACT) - zda jsou dvě textové hodnoty T (T) - převede argumenty na text UNICHAR (UNICHAR) - vrátí znak Unicode - od Excel 2013 VELKÁ (UPPER) - převede text na velká písmena VELKÁ2 (PROPER) - převede první písmeno každého slova na velké VYČISTIT (CLEAN) - odebere z textu netisknutelné znaky ZAOKROUHLIT.NA.TEXT (FIXED) -zformátuje číslo jako text s pevným počtem desetinných míst ZLEVA / VLEVO (LEFT) - první znaky textu zleva ZNAK (CHAR) - vrátí znak určený číslem kódu ZPRAVA / VPRAVO (RIGHT) - vrátí znak určený číslem kódu zprava Pavel Lasák 2017