V této lekci se podíváme na textové funkce. S jejich pomocí můžete texty slučovat, rozdělovat, nahrazovat slova jinými a provádět podobná kouzla.
V přehledu všech tabulkových funkcí je najdete pod skupinou Textové.
Opět si můžete otevřít moji tabulku, uložit si kopii na svůj Disk a zkoušet.
Jako první skupinu si ukážeme funkce, které umí texty rozdělit a zase spojit do jednoho textu. Ukázky najdete v tabulce.
SPLIT()
Funkce SPLIT() rozdělí text na jednotlivé dílky. Funkce má povinné 2 parametry, první je samotný text, druhý parametr udává podle jakého znaku (nebo skupiny znaků) chceme text rozdělit.
Text může být ve funkci zadaný přímo, třeba
=SPLIT("pondělí,úterý";",")
nebo to samozřejmě může být odkaz na buňku tabulky nebo pojmenovanou oblast
=SPLIT(B2; ",")
Takto jsme v tabulce dny v týdnu rozdělili v buňce C2.
Pokud budeme mít dny v týdnu oddělené čárkou a mezerou jako v buňce B3 a rozdělíme je stejným vzorcem
=SPLIT(B3;",")
pak to dopadne, jak vidíte na řádku 3, pondělí je v pořádku, ale zbývající dny mají na začátku mezeru a museli bychom použít další vzorce abychom mezeru odstranili.
Můžeme si ale pomoci tak, že text rozdělíme podle dvou znaků ", " (čárka a mezera)
Vzorce je v buňce C4
=SPLIT(B4;", ")
a vidíte, že výsledek na řádku 4 už vypadá lépe.
Jak si poradíme s textem v buňce B5, kde jak vidíte jsou některé dny odděleny jen čárkou a jindy čárkou a mezerou?
Jedna možnost je rozdělit text podle čárky a dalšími vzorci se zbavit mezer. Já jsem použil funkci SUBSTITUTE(), kterou si podrobněji vysvětlíme dál. Funkce z původního textu vyhodí mezery a výsledek poté rozdělíme funkcí SPLIT().
Samotná funkce SUBSTITUTE() vypadá takto
=SUBSTITUTE(B6;" ";"")
a výsledný vzorec v buňce C6 pak je
=SPLIT(SUBSTITUTE(B6;" ";""); ",")
Vidíte, že výsledné rozdělení na řádku 6 už je bez mezer na začátku.
Funkce SPLIT() je použitelná i v případě, že v textu bude několik různých oddělovačů.
V buňkách B7 a B8 jsou některé dny oddělené čárkou a jiné středníkem.
Vidíte, že vzorec v buňce B7 dny rozdělí špatně, dny oddělené středníkem neoddělí.
Pokud ale v buňce B8 použijeme vzorec
=split(B8;",;")
text se nám rozdělí správně.
Uplatnil se zde třetí parametr, který jsme sice neuvedli, ale jeho defaultní hodnota je TRUE a znamená, že se bude text rozdělovat podle všech znaků v druhém parametru.
Funkce tedy bude text rozdělovat jak podle čárek, tak podle středníků.
Pokud bychom třetí parametr nastavili na FALSE, pak by funkce rozdělovala text jen tam, kde by se vyskytla kombinace znaků ,; (čárka středník). Vzorec vidíte v buňce C9
=SPLIT(B9;",;";false)
a vidíte, že text se nerozdělil vůbec, protože se v něm kombinace ,; nikde nevyskytuje.
Ukážeme si ještě čtvrtý parametr, který je rovněž nepovinný a taky je defaultně nastavený na TRUE. Určuje, jestli se z výsledku rozdělení odstraní výsledky, které bude tvořit prázdný text.
Ukázka je na řádku 10, vidíte, že dny se správně rozdělily i když mezi pondělí a úterý jsou omylem vložené 2 čárky. Prázdné texty funkce vyřadila.
Pokud nastavíme 4. parametr na FALSE (buňka B11), vzorec tedy vypadá takto
=SPLIT(B11 ; "," ; true ; false)
pak bude prázdný text mezi pondělím a úterým zahrnutý do výsledků dělení.
Pokud byste funkci SPLIT() používali například pro dělení dat z CSV souboru, nezapomeňte čtvrtý parametr nastavit na FALSE. Ukázku jak dělení v obou případech dopadne najdete na listu CSV.
JOIN()
Funkce JOIN() dělá opak než SPLIT(), čili spojuje několik textů dohromady. První parametr je text, který se umístí mezi jednotlivé spojované části textu. Všechny další parametry jsou jednotlivé části, které chceme spojit. Mohou obsahovat přímo text, nebo to může být pole, odkaz na buňku, rozsah, nebo na pojmenovaný rozsah. Nevím jestli jsem to již někde zmiňoval, pole se ve vzorcích zapisuje ve tvaru například {1;2;5;7}. Pokud pole necháme spojit funkcí JOIN(), vzorec může vypadat třeba =JOIN(", ";{1;2;5;7}) V tabulce je v buňce B12, výsledkem JOIN() je samozřejmě text, takže čísla v poli se převedou na text. Podobně můžeme spojit texty v nějaké oblasti, třeba dny v týdnu, které jsme si funkcí SPLIT() rozdělili a máme je v buňkách C2:I2 můžeme vzorcem =JOIN("," ; C2:I2) zase spojit dohromady, vzorec je v buňce B13. Pozor, oblast kterou JOIN() umí spojit musí být jeden řádek nebo sloupec. Do buňky B14 jsem napsal vzorec =JOIN(", " ; C13:D14) který by měl sloučit texty první druhý třetí čtvrtý ale můžete se přesvědčit (buňka B14), že skončí chybou.
Pokud bychom texty z těchto čtyř buněk chtěli sloučit, museli bychom vzorec zapsat takto
=JOIN(", " ; C13:D13; C14:D14)
Výsledek vidíte v buňce B15.
Pokud chceme jednotlivé texty spojit přímo, pak můžeme jako první parametr uvést prázdný text (buňka B16)
=JOIN("";C16:G16)
nebo můžeme první parametr úplně vynechat a vzorec zapsat takto (buňka B17)
=join(;C16:G16)
V těchto případech, kdy texty spojujeme bez oddělovače, se JOIN() chová stejně jako funkce CONCATENATE(), kterou si ukážeme dále.
TEXTJOIN()
Funkce víceméně shodná s JOIN(). Rozdílem je, že druhý parametr TRUE nebo FALSE určuje, jestli ze slučování mají být vyloučené prázdné položky. Jinak první parametr stejně jako u JOIN() určuje oddělovač a třetí a všechny další parametry jsou části, které se budou slučovat.
CONCATENATE()
Funkce spojuje texty uvedené v jednotlivých parametrech do jednoho textu. Je asi jasné, že pokud potřebujeme mezi texty nějaký oddělovač, bude jednodušší použít funkci JOIN. Spojit dny v týdnu čárkou pomocí JOIN() jsme si už ukázali, použili jsme vzorec
=JOIN("," ; C2:I2) S pomocí CONCATENATE() bychom museli pro stejný výsledek použít hodně krkolomný zápis =CONCATENATE(C2;",";D2;",";E2;",";F2;",";G2;",";H2;",";I2) Na rozdíl od JOIN() umí CONCATENATE() spojovat i blok dat, který nemusí být jen řádek nebo sloupec. Když si zkusíme spojit naše texty první druhý třetí čtvrtý vzorcem =CONCATENATE(C13:D14) dostaneme výsledek, který je v buňce B19. Vidíte, že funkce spojuje texty po jednotlivých řádcích. Pokud bychom chtěli do výsledku dostat jako oddělovač třeba čárku, dalo by se to vyřešit vzorcem (buňka B20) =CONCATENATE(C20:F21) a oddělovače bychom si zadali přímo do bloku dat.
SUBSTITUTE()
Funkci SUBSTITUTE() jsme už použili výše, teď si o ní řekneme trochu víc. První parametr je text, který budeme prohledávat. Druhý parametr je text, který budeme hledat a třetí parametr obsahuje text, kterým se vyhledaný text nahradí. Čtvrtý parametr je nepovinný. Dá se jím určit kolikátý výskyt se nahradí, pokud se parametr neuvede, nahradí se všechny nalezené výskyty. Parametry jako vždy mohou být přímo ve funkci, nebo jako odkaz na buňku nebo pojmenovanou oblast. První ukázka je v buňce B22 =SUBSTITUTE(C22;"Karel";"Jarda") z věty Karel poslal dopis Pavlovi. tak dostaneme větu Jarda poslal dopis Pavlovi. V buňce B23 pak provedeme druhou náhradu a získáme větu Jarda poslal e-mail Pavlovi. Obě náhrady můžeme samozřejmě zapsat do jednoho vzorce, ukázka je v buňce B24 =SUBSTITUTE(substitute(C22;"Karel";"Jirka");"dopis";"balík") Vzorec nám z věty Karel poslal dopis Pavlovi. vyrobí větu Jirka poslal balík Pavlovi. A ještě poslední ukázka v buňce B25, vzorec =SUBSTITUTE(C25;"Karel";"Jirka";1) nám ve větě Karel Novák a Karel Dvořák jeli vlakem. nahradí jen prvního Karla, bez posledního parametru by se nahradily oba výskyty.
TRIM()
Funkce má jen jeden parametr a funguje tak, že odstraní případné mezery na začátku a konci textu. Ukázka je v buňce B26.
CLEAN()
Podobná funkce jako TRIM(), jen z textu neodstraňuje mezery, ale netisknutelné ASCII znaky. Pokud v textu budou netisknutelné Unicode znaky, nebudou funkcí odstraněny.
REPT()
Velice jednoduchá funkce, první parametr udává text, druhý počet jeho opakování. Ukázky jsou v buňkách B28 a B29.
Teď si ve zkratce probereme funkce pro převod na velká nebo malá písmena.
UPPER()
Převádí text na velká písmena.
LOWER()
Převod na malá písmena.
PROPER()
První písmeno v každém slově převede na velké, zbytek slova převede na malá písmena. Ukázky všech tří funkcí jsou na řádcích 30-33.
EXACT()
Funkce má dva parametry a porovnává, zda se oba texty shodují, při shodě vrací TRUE, jinak FALSE.
Na řádcích 34 a 35 vidíte ukázku, kdy funkce vrací FALSE, přestože porovnávané texty vypadají vizuálně stejné. V prvním případě jsem v buňce D34 použil vzorec
="test"&CHAR(9)
který ke slovu test přidal znak s ASCII kódem 9, (tabelátor), takže funkce správně vyhodnotila, že se oba texty neshodují. No a v buňce D35 je za slovo test doplněna mezera, takže opět se oba texty liší.
CHAR()
Funkce má jeden parametr, kterým je číslo Unicode znaku. Číslo musí být v desítkovém formátu. V buňce B36 je vzorec
=char(C36) & char(D36) & char(E36)
který spojuje 3 znaky jejichž kódy jsou uvedené v buňkách C36 - E36.
Další ukázka je v buňce B37, kde se vzorcem
=char(hex2dec("A9"))
hexadecimální hodnota A9 nejprve převede na desetinné číslo a to se převede na znak © (copyright). Stejným způsobem byl v buňce B38 sestaven vzorec α + β
Dál si ukážeme několik funkcí, které nám z textu vyberou jeho určitou část.
LEFT()
Funkce má jeden povinný parametr, kterým je text nebo adresa buňky. Pokud není druhý parametr uveden, vrátí funkce první znak textu zleva. Příklad je v buňce B39. Pokud druhý parametr uvedeme, určuje počet vrácených znaků. Parametr opět může být přímo ve vzorci, nebo jako odkaz na buňku, jako je to v buňce B40 kde je vzorec
=left(C40;D40)
Vyzkoušejte si, že pokud bude druhý parametr text, nebo záporné číslo, vrátí funkce chybu #VALUE! s patřičným komentářem v poznámce.
RIGHT()
Obdoba předchozí funkce, jen vrací znaky od konce textu. Ukázky jsou na řádcích 41 a 42.
MID()
Funkce vrací část původního textu. Prvním parametrem je text, který má zpracovat, druhý udává začátek “výřezu” a třetí parametr jeho délku.
Takže třeba vzorec (buňka B43)
=MID(C43;3;4)
nám z textu nějaký text vrátí text jaký, protože jako první bere třetí znak a požadovaná délka je 4 znaky. Asi je jasné, že pokud jako druhý parametr použijeme 1 bude funkce dávat stejný výsledek jako LEFT(). A pokud si chcete vzorce potrénovat, můžete zkusit funkci MID() použít jako náhradu funkce RIGHT(). Výsledný vzorec je v buňce B44.
=MID(C44;LEN(C44)-3;4)
FIND()
Funkce slouží k vyhledávání v textu. První parametr je text, který chceme najít, druhý parametr je text, ve kterém budeme hledat. Třetí parametr je nepovinný a udává pozici od které se bude hledat, pokud není uveden, vyhledává se od prvního znaku. Funkce vrací pozici prvního výskytu, pokud text nenalezne skončí chybou #VALUE! s vysvětlením v poznámce.
Pozor, funkce rozlišuje malá a velká písmena, v buňce B46 je ukázka.
Pro vyhledávání bez rozlišení velikostí písmen použijte následující funkci SEARCH().
Případně je možné si oba texty převést na malá nebo velká písmena jako v buňce B47.
=find(LOWER("Te");LOWER(C47))
SEARCH()
Stejná funkčnost i parametry jako u funkce FIND(), ale funkce nerozlišuje malá a velká písmena. Dál si popíšeme 3 funkce nad kterými zajásají ti, kdo dobře ovládají regulární výrazy. My ostatní si zkusíme alespoň nějaké základní příklady :-)
REGEXREPLACE()
Funkce slouží podobně jako SUBSTITUTE() k vyhledání textu a jeho nahrazením jiným textem. První parametr je text, v němž budeme vyhledávat a nahrazovat, druhým parametrem je regulární výraz, podle kterého hledání proběhne a třetí parametr je text, kterým se vyhledaný text nahradí. Dva jednoduchoučké příklady jsou v buňkách B49 a B50
=REGEXREPLACE(C49; "P…. "; "Českých Budějovicích ")
Protože v regulárních výrazech tečka znamená libovolný znak, funkce vyhledá všechny kombinace kde bude velké P následováno čtyřmi libovolnými znaky a jako další znak bude mezera.
Takže slova Praze a Plzni tuto podmínku splňují a funkce je tak nahradila slovy Českých Budějovicích. Za Budějovicích je potřeba doplnit mezeru, jinak by se nám Budějovice spojily s následujícím slovem by.
REGEXEXTRACT()
Prvním parametrem je text a druhým regulární výraz. Funkce pak vrátí první část textu, která vyhovuje regulárnímu výrazu. Opět velmi jednoduchý příklad v buňce B51.
=REGEXEXTRACT(C51;"P.... ")
REGEXMATCH()
Stejné parametry jako předchozí funkce, ale funkce vrací logickou hodnotu TRUE pokud některá část textu vyhovuje regulárnímu výrazu, nebo FALSE pokud nevyhovuje. V buňkách B52 až B54 jsem použil stejný regulární výraz " ... " tedy mezeru 3 tečky a opět mezeru.
Jak jsme si řekli, tak tečka znamená libovolný znak, proto regulární výraz hledá, zda ve větě existuje libovolné slovo o třech znacích, před a za slovem musí být mezera. Vidíte, že první věta "V Plzni by chtěl žít každý," vyhovuje, slovo žít má 3 znaky a z obou stran jsou mezery. V další větě "V Plzni by chtěl bydlet každý," slovo o třech znacích není, takže výsledek je FALSE. No a konečně věta "V Plzni by chtěl každý žít," vrací taky FALSE, protože za žít je tečka, ale regulární výraz hledá skupinu 3 znaků za kterou bude následovat mezera.
Pokud Vás zajímá cokoliv dalšího, obraťte se na nás na ahoj@appsatori.eu