Funkcie (2.diel)

 

Dúfam, že každý z vás má už v malíčku základ z minulého článku a tak sa môže s čistým svedomím začať zaoberať textovými funkciami. Ako ste si už zvykli, sample file nájdete tu.

V priloženom súbore nájdete pomenovanie a príklad použitia jednotlivých funkcií, prípadne aj zložených funkcií tak, aby dávali reálny zmysel.

Veľmi často som sa v praxi stretol s potrebou rozdeliť alebo spojiť text v reportoch tak aby som mohol porovnávať jednotlivé tabuľky. Začnime spojením textu.

Vybraný text v bunkách môžeme spojiť funkciou Concatenate, stačí do zátvorky vkladať jednotlivé bunky s textovým obsahom.

Ak potrebujete mať text oddelený čiarkou alebo medzerou stačí v zátvorke medzi bunkami vložiť “,” alebo “ “ . V prípade ak potrebujete k bunkám s textom pridať taký, ktorý v žiadnej bunke nie je, použite “text”. 

Stáva sa a mnohí mi možno dajú za pravdu, že efektívnejší spôsob (pri malom množstve textových buniek) spájania buniek s textom alebo ich kombinácie s čiarkami, medzerami a iným textom mimo bunky je pomocou tohto operátora: &. Ako príklad uvediem vzorový algoritmus =A1&B1&” “&C1&”-“&”Ľubovoľný text”&AB1.

Ak potrebujete rozdeliť text do niekoľkých buniek, existujú na to funkcie Část alebo v našej anglickej verzii MID. V sample file som ju nepoužil a nakoľko vo väčšine prípadov sa rozdeľuje veľké množstvo dát (veľa riadkov) do viacerých stĺpcov, použijeme inú metódu, ale až v budúcom doplňujúcom článku. Každopádne syntax vyzerá takto: =MID(“Bunka”;poradie začiatočného písmena;počet písmen) teda ako príklad: =MID(B46;2;4).

Funkcie Left, Right spolu s Len sa používajú v praxi ak máte napríklad sheet s vyexportovanými dátami z SQL alebo SNOW apod., no po rozdelení do stĺpcov alebo finálny export dát obsahujú po stranách znaky, ktoré sú zbytočné a môžu znepríjemňovať prácu s reportom. Preto použijeme Left a Right na očistenie dát a funkciu Len (vráti počet znakov v bunke) na to, aby algoritmus pracoval obecne. Syntax = LEFT(RIGHT("Bunka";LEN("Bunka")-počet nevyhovujúcich znakov vpravo);LEN(RIGHT("Bunka";LEN("Bunka")-počet nevyhovujúcich znakov vpravo))-počet nevyhovujúcich znakov vľavo) alebo pre lepšie pochopenie príklad zo sample file-u: =LEFT(RIGHT(B12;LEN(B12)-2);LEN(RIGHT(B12;LEN(B12)-2))-2).

Zaujímavá funkcia je Text, s ktorou sa oplatí hrať hlavne pri dátumoch. Ona vlastne konvertuje hodnotu na text podľa nami určeného formátu. Ak máme napríklad dátum 21.11.2001 a chceme z neho použiť názov dňa, urobíme to takto: =TEXT(“Bunka, v ktorej je dátum”,”dddd”). Pri určovaní formátu je dôležité rozlišovať podmienky formátovania. Pre “dd” vyselektujete číslo dňa. Vo väčšom počte písmen “d” dostávate už konkrétne názvy dní: “ddd” – deň v skratke (Ut ako Utorok). Na obrázku som použil formát, v ktorom sa zobrazuje celý názov dňa.

Pre zmenu veľkosti písmen (Malé alebo veľké) použijeme funkciu Upper (všetky  veľké) a Lower (všetky malé) poprípade ak potrebujeme začiatočne písmená každého slova veľké, použijeme funkciu Proper. Stačí jednoducho definovať bunku, v ktorej text si želáte zmeniť (viď sample file).

Pri komplexnejších formulách, kde chcete vytvoriť takú, ktorá by pracovala všeobecne pre akýkoľvek dlhý text, je potrebné niekedy zistiť pozíciu cieľového znaku. Pre zistenie poradia daného znaku sa dá použiť funkcia Find.  Postačí zadať hľadaný znak, bunku v ktorej sa nachádza a začiatočný znak pre hľadanie. Príklad: =FIND(",";B39;1).

Ak by sme potrebovali hľadaný znak vymeniť za iný, použili by sme Replace ošetrenú funkciou Iferror a to takto: =IFERROR(REPLACE("Bunka";FIND(",";"Bunka");1;".");"Bunka"). Menej všeobecné avšak jednoduchšie riešenie by bolo s Substitute formulou. Stačí zadať bunku spolu so znakom, ktorý zamieňame a novým nahradzujúcim znakom: SUBSTITUTE(“Bunka”;",";"-").

Funkcie Rept a Exact sú pomerne jednoduché a nebudem ich podrobne rozoberať (viď sample file). Užitočnou sa javí možnosť získania ASCII kódu na znak v konkrétnej bunke. Použite Code pre získanie ASCII čísla a neskôr jeho použitím vo funkcii Char získajte špecifický symbol naspäť. Tým pádom by som povedzme, že ukončil etapu textových funkcií aj keď Excel ich ponúka oveľa viac. Práve preto je potreba aby ste čo najviac sa stretávali s riešením problémov cez Excel a postupne nachádzali ďalšie funkcie, ktoré vám môžu uľahčiť analytický život. Nasledujúci článok bude doplnkovým k textovým funkciám a rozoberiem v ňom metódu komplexného rozdelenia buniek do viacerých stĺpcov.