Pozornost v sedmém pokračování praktického používání tabulkového procesoru Excel upřeme v návaznosti na předchozí část na práci s textem. Přestože primárně pro psaní a editaci textu využíváme textový editor (např. Word), v praxi se v řadě případů hodí využít i možnosti a funkčnost, kterou nám nabízí uspořádání textu do tabulek v prostředí Excelu.
Využití Excelu v praxi 7. část
Milan
Řezníček
PROČ PŘEVÁDĚT HODNOTU NA TEXT A KDY SE TO HODÍ
V posledním příkladu minulého seriálu (Účetnictví v Praxi č. 9/14 na str. 34) jsme si ukázali spojení textu s číslem za pomoci funkce HODNOTA. NA. TEXT. Víme již také, jak správně pracovat s vlastním formátem buňky, resp. jak zapisovat kód vlastního formátu, který v rámci zápisu parametrů této funkce ihned využijeme.
Syntaxe:
I-----------------I--------------------------------I-------------------------I IFunkceISyntaxeIPoznámkaI I-----------------I--------------------------------I-------------------------I IHODNOTA.NA.TEXTI =HODNOTA.NA.TEXT(číslo;formát) I Převede hodnotu na text I I I I v určitém formátu. I I-----------------I--------------------------------I-------------------------I
Název funkce vyjadřuje i podstatu jejího použití. Má-li uživatel potřebu vytvořit z hodnoty text v určitém formátu, druhý parametr funkce mu nabízí řadu možností, jak toho dosáhnout. Ortodoxní účetní by mohl konstatovat, že text je text a číslo je číslo, tak proč si s tím hrát. Odpovědí budiž následující praktické příklady:
Účtová třída 0
Zobrazení počátečních nul (např. u syntetických účtů účtové třídy 0) může činit v Excelu trochu potíže. Minule jsme si ukázali, jak tento problém řešit pomocí vlastního formátu buňky. Lze to však provést i převodem na text a to vzorcem, který jsme uvedli v buňce B1 na obrázku:
Náklady projektu za období od-do
Široké pole uplatnění nacházíme v převodu údajů z buněk obsahujících datum, se kterým Excel standardně pracuje jako se sériovým číslem, na text. Často je třeba sloučit právě datum s určitým textem. Na obrázku je zobrazen spojený text informující o období nákladů od data to data (buňka A2). Jaký by byl výsledek bez použití převodu hodnoty, kterou Excel chápe jako datum, na text, je znázorněno v buňce B3, kde pro srovnání byly textové údaje z buněk (A1 a C1) a číselný údaj datum (B1 a D1) sloučeny pouze operátorem & bez převodu hodnot na text.
Podmíněný součet za období převedené z hodnoty na text
Transformace hodnoty, kterou představuje v Excelu datum na text, je i praktická v úlohách, kdy sčítáme za určité období, které lze snadno uvnitř vzorce na text převést. Namísto složitějšího vymezení intervalu hodnot <DatumPočátek;DatumKonec>. Na následujícím obrázku je příklad takového součtu s vnitřním převodem data na text (sloupec A), a to v požadovaném tvaru (formátu) „rok.měsíc“ neboli přesně kódem „rrrr.mm“. Jakým způsobem si Excel ve vnitřní paměti transformuje údaje, je vidět na následujícím obrázku. V tomto případě vyhovují zadanému kritériu buňky A3 a A5, tudíž podmíněný součet se provede z hodnot v buňkách E3 a E5. Prakticky lze toto využít např. při uvádění hodnot do řádku 22 daňového přiznání k DPH1), kam se uvádí dodání zboží do třetích zemí, které ZDPH2) považuje při splnění podmínek uvedených v § 66 za vývoz zboží. Praktickým problémem je, že u tohoto plnění se v praxi liší datum uskutečnění účetního případu (účtování o tržbě momentem realizace dodávky)3) od data uskutečnění plnění v DPH, za které se považuje datum potvrzení výstupu zboží z EU. Evidujeme-li tato plnění v Excelu - lze nabídnout toto jednoduché řešení:
Poznámka:
Stejně tak vhodné by bylo použít převod data na období v textovém formátu v případě podmíněného součtu např. za rok 2014 nebo v případě součtu za na sebe nenavazující období, které bychom museli pracně parametrizovat pomocí několika intervalů apod. Vzorec by pak mohl mít např. tuto podobu: SOUČIN. SKALÁRNÍ((HODNOTA. NA. TEXT(A3:A6; “rrrr“)=“2014“)*(E3:E6)).
Doplňme, že kromě této funkce lze pro převod hodnoty na text využít také funkci ZOKROUHLIT. NA. TEXT. Tato funkce nejdříve číslo před převodem na text zaokrouhlí dle pravidel pro zaokrouhlení a následně převede na text, čímž nám odpadne eventuální vnoření zaokrouhlovacích funkcí a vzorec je tak přehlednější.
VYUŽITÍ DALŠÍCH TEXTOVÝCH FUNKCÍ VE VZÁJEMNÝCH KOMBINACÍCH
Při tvorbě tabulek se objevují různé potřeby na výsledek, který se má zobrazit. Z tohoto důvodu je vhodné si osvojit různé vhodné kombinace funkcí, které by na první pohled mohly zůstat bez povšimnutí. Mezi takové nepostradatelné pomocníky patří funkce DÉLKA a OPAKOVAT. Proč zjišťovat délku textového řetězce v buňce či proč opakovat určitý znak si ukážeme na praktickém příkladu, syntaxe obou zmíněných funkcí není nijak složitá:
I-----------------I--------------------------------I-------------------------I IFunkceISyntaxeIPoznámkaI I-----------------I--------------------------------I-------------------------I IDÉLKAI =DÉLKA(text) I Vrátí počet znaků I I I I textového řetězce. I I-----------------I--------------------------------I-------------------------I IOPAKOVATI =OPAKOVAT (text;počet) I Několikrát zopakuje I I I I zadaný text. I I-----------------I--------------------------------I-------------------------I
PŘÍKLAD
Neznám snad profesního účetního, který by měl v oblibě inventury. Zákon o účetnictví4) ukládá mimo jiné povinnost provést fyzické inventury majetku jednou za rok. Obecná ustanovení zákona nedávají praktické návody na konkrétní provádění inventur, a tak je třeba v praxi přizpůsobit tuto činnost na specifické podmínky dané účetní jednotky. My si ukážeme, jak například doplnit fyzické inventurní soupisy, které zaznamenají přímo pracovníci provádějící inventury a nemusí vůbec obsahovat ocenění, o přehledný soupis majetku s oceněním5) jako přílohu. Vstupní údaje obsažené ve sloupcích A až C můžeme mít exportovány např. z informačního systému. Úloha spočívá ve sloučení údajů ze sloupců A až C do jediného sloupce (D) tak, aby byl soupis přehledný a hezky „zařezával“ pod sebou. Dále požadujeme, aby byl sloupec do zadané jednotné šířky mezi názvem materiálu a jeho cenou vyplněn znakem tečka „.“.
Řešení
Nejprve do buňky D1 zadáme maximální rozsah sloupce (v našem případě je to 40 znaků), který pro účely tvorby inventurního soupisu s oceněním považujeme za postačující. Následně ve vzorci zapsaném v buňce D3 spojíme skladové číslo materiálu (buňka A3 „0040“) a znak pomlčky se dvěma mezerami („-“), poté název materiálu (buňka B3 „Tyč kruhová d50“) a cenu zboží (číselná hodnota z buňky C3 převedená na text, jak již umíme z přechozího příkladu). Mezi takto spojené řetězce vložíme funkci OPAKOVAT, kde v prvním parametru dosadíme požadovaný znak pro opakování - v našem případě tečku „.“. Do druhého parametru funkce OPAKOVAT, který má obsahovat délku opakování tečky, musíme zadat rozdíl mezi celkovým počtem znaků (v našem případě 40) a počtem znaků, které „obsadí“ číslo materiálu, pomlčka se dvěma mezerami, název materiálu a také hodnota převedená na text a naformátovaná jako údaj v Kč pomocí funkce HODNOTA. NA. TEXT, což tedy můžeme vzorcem vyjádřit takto:
=OPAKOVAT(“.“;DÉLKA($D$1)-SUMA(DÉLKA(A3);DÉLKA(B3);DÉLKA(“ - “))DÉLKA(HODNOTA. NA. TEXT(C3;“# #0,00 Kč“)))
V našem konkrétním případě Excel doplní mezi název materiálu a částku v Kč sedm teček, protože rozdíl mezi celkovou délkou textového řetězce (40 znaků) a délkou obsazených znaků z jednotlivých slučovaných řetězců (33) činí sedm. Celé kouzlo spočívá v tom, že počet teček bude proměnný v závislosti na délce ostatních spojovaných textových řetězců.
Poznámka:
V uvedeném příkladu bylo použito tzv.
neproporcionální
písmo (Courier New). Neproporcionální znamená to, že každý znak zabírá stejně velké místo (šířka znaku je určena nejširším znakem). Právě tato vlastnost nám umožní, aby jednotlivé údaje v buňkách pod sebou tzv. „zařezávaly“ a že výsledný text nebude buňku přesahovat nebo naopak nezaplňovat zcela, a to v závislosti na individuální šířce použitých znaků, jak by tomu bylo u proporcionálního
písma (další font neproporcionálního písma v Excelu je např. Consolas). Výchozí font písma v E2007, E2010 i E2013 je font s názvem Calibri, což je proporcionální písmo, a v uvedeném příkladu by jeho použití nebylo vhodné.Praxe nám přináší i situace, kdy musíme řešit opačný problém - namísto spojování textů - jejich rozdělení. Excel obsahuje nepostradatelné pomocníky v podobě textových funkcí ZLEVA, ČÁST, ZPRAVA. Připojme si ještě funkci NAJÍT, která se opět hodí v kombinaci s dalšími funkcemi jako DOSADIT, kterou si můžeme nahradit znak jiným znakem či se zbavit nežádoucích duplicitních znaků. Funkce PROČISTIT nám zase pomáhá si zbavit přebytečných mezer neboli prázdných textových řetězců.
I-----------I-------------------------------------I--------------------------I IFunkceISyntaxeIPoznámkaI I-----------I-------------------------------------I--------------------------I IZLEVAI =ZLEVA(text;délka) I Vrátí zadaný počet od I I I I začátku textového I I I I řetězce. I I-----------I-------------------------------------I--------------------------I IZPRAVAI =ZPRAVA(text;délka) I Vrátí zadaný počet znaků I I I I od konce textového I I I I řetězce. I I-----------I-------------------------------------I--------------------------I IČÁSTI =ČÁST(text;start;počet_znaků) I Vrátí znaky textového I I I I řetězce, je-li zadána I I I I počáteční pozice a počet I I I I znaků textového řetězce. I I-----------I-------------------------------------I--------------------------I INAJÍTI =NAJÍT(co;kde;[start]) I Vrátí počáteční pozici I I I I jednoho textového I I I I řetězce v druhém I I I I textovém řetězci I I I I (rozlišuje malá a velká I I I I písmena). I I-----------I-------------------------------------I--------------------------I IDOSADITI DOSADIT(text;starý,nový,[instance]) I Nahradí existující text I I I I novým textem v textovém I I I I řetězci. I I-----------I-------------------------------------I--------------------------I IPROČISTITI =PROČISTIT(text) I Odstraní všechny mezery I I I I z textového řetězce I I I I kromě jednotlivých mezer I I I I mezi slovy. I I-----------I-------------------------------------I--------------------------I
PŘÍKLAD
Představme si situaci, kdy obdržíme v Excelu seznam bankovních účtů (např. zaměstnanců nebo dodavatelů) a pro naše účely potřebujeme tyto údaje rozdělit do dvou sloupců. Zatímco kód banky uváděný za lomítkem má vždy čtyři znaky, čísla účtů mohou mít různou délku, a proto oddělovaný počet znaků z levé strany bude proměnná, kterou vždy potřebujeme zjistit.
Řešení
Oddělení čísla účtů s různou délkou provedeme kombinací funkcí ZLEVA, jejímž výsledkem je text oddělený zleva v počtu znaků, který zadáme do druhého argumentu funkce. Vnoříme tedy funkci NAJÍT do druhého argumentu funkce ZLEVA, přičemž funkce NAJÍT vrátí pořadové číslo znaku, který jí určíme (v našem případě lomítka „/“). V buňce A2 je lomítko na sedmém místě, a proto musíme ještě odečíst číslo 1, abychom dostali počet znaků, který je před lomítkem (bez lomítka) - tedy v tomto případě Excel vyhodnotí jako šest a vrátí tedy požadovaných „123456“. Vzorec zafunguje stejným způsobem i v buňce B3, neboť lomítko nalezne na osmé pozici, odečte si jedna a vrátí prvních sedm znaků zleva atd. V sloupci je zapsáno jednoduché oddělení kódu banky s pevným počtem znaků - použijeme funkci ZPRAVA.
PŘÍKLAD
V tomto příkladu si ukážeme, jakým způsobem bychom mohli využít textové funkce pro rozdělení textového řetězce v zrcadlově obráceném případě k příkladu s inventurním soupisem. Úloha tedy spočívá v řešení situace, kdy v jedné buňce máme sloučený text s číslem zboží, jeho názvem a cenou, a to vše ještě spojeno opakovaným znakem - tečkami, který potřebujeme rozdělit do tří sloupců, které budou obsahovat číslo, název a cenu materiálu.
Řešení
Jakým způsobem rozdělit text požadovaným způsobem si popíšeme v několika krocích:
1. Oddělení čísla materiálu
Protože číselná řada je oddělena mezerou (v Excelu prázdným textovým řetězcem tedy „ “), použijeme stejný způsob jako u příkladu s bankovními účty, pouze v argumentu funkce NAJÍT zapíšeme místo lomítka prázdný textový řetězec viz obrázek:
2. Oddělení názvu materiálu
Zde již potřebujeme oddělit část textu, která je uvnitř řetězce. K tomu nejlépe využijeme funkci ČÁST, pouze si musíme správně parametrizovat znaky, které název materiálu oddělují, abychom správně stanovili pozici, od které se má text vyjmout, a počet znaků, který se má z řetězce od počáteční pozice dále vyjmout. Název materiálu následuje hned za mezerou, která je za pomlčkou a končí před první tečkou. Musíme tedy vyjmout text začínající na pozici pomlčky +1 (mezera) a končící před pozicí první tečky, tj. najít pozici tečky -1; délka textu však musí být kladné číslo, a proto je třeba tuto podmínku zapsat do argumentu funkce v opačném pořadí. Odečítat tedy od sebe pozici první tečky -1 (tj. pozici posledního znaku názvu materiálu) a pozici pomlčky +1 (tj. pozici prvního znaku názvu materiálu), viz následující obrázek:
3. Hodnoty materiálu v Kč
Nejtvrdším oříškem je bezpochyby vyjmutí ceny materiálu. Zde se totiž musíme vypořádat hned s několika problematickými místy:
-
částka v Kč je totiž za několikanásobně opakovaným znakem,
-
navíc obsahuje i mezeru oddělující tisíce a ještě za ní je navazující text Kč,
-
dále částka v Kč může mít různou délku.
Jednoduché použití funkcí ZLEVA, ZPRAVA nebo ČÁST zde naráží na proměnnou velikost počtu znaků samotné částky a také na jednoznačné identifikátory v podobě znaků, kterými bychom mohli částku v textu jednoznačně oddělit a následně vyjmout. Příklad možného řešení je tak v kombinaci několika textových funkcí, které vnoříme do sebe, viz násl. obrázek:
V desateru pro podmíněný součet v Excelu jsme jako jedno z pravidel uvedli, že složitější vzorce je dobré skládat po částech. To je obecná pravda platící nejen pro podmíněný součet, a proto si pojďme uvedený vzorec také po částech rozebrat a vysvětlit:
a)
Jako první krok potřebujeme určit pozici, od které bychom identifikovali první znak částky v Kč v rámci textového řetězce. Protože se jedná o tečku, která se v textovém řetězci několikrát opakuje (s různým počtem opakování), musíme nejprve zjistit počet opakování znaku tečka v řetězci a poté poslední výskyt tohoto znaku nahradíme jiným znakem, který se v textovém řetězci nikde nevyskytuje. Zjištění počtu znaků „tečka“ provedeme odečtem celkové délky textového řetězce od délky textového řetězce očištěného od znaku „tečka“ neboli tímto zápisem:
=DÉLKA(A1)-DÉLKA(PROČISTIT(DOSADIT(A1;“.“;““))))=40-33=7 6)
Funkce DOSADIT nejprve za všech sedm teček dosadila prázdný textový řetězec (tedy vložila sedm mezer), avšak funkce PROČISTIT nás těchto přebytečných mezer ihned zbavila (odstranila je).
b)
Nyní, když víme, že v řetězci obsaženém v buňce A1 má tečka 7 znaků, přistoupíme k tomu, že poslední tečku nahradíme např. lomítkem a to za pomoci funkce DOSADIT, namísto nevhodné konstanty 7 dosadíme do posledního parametru funkce celý výraz z bodu a), jenž nám zajistí správný počet teček pro každý materiál při rozkopírování vzorce.
=DOSADIT(DOSADIT(A1;“.“;“/“;DÉLKA(A1)- DÉLKA(PROČISTIT(DOSADIT(A1; “.“;““))));“.“;““)= =„0040 - Tyč kruhová d50/1 851,00 Kč“
Tato část vzorce nám tedy dosadí následující výsledek, ze kterého již lze snadno oddělovat, máme již jednoznačný počáteční znak („lomítko“), od kterého stanovíme počátek vyjmutí částky v Kč: „0040 - Tyč kruhová d50/1 851,00 Kč“.
Nyní bychom již mohli využít některou z funkcí pro oddělení části textu, stále však nemáme vyřešenu otázku počtu znaků, protože částka v Kč může být různě veliká.
c)
V předposledním kroku zvolíme např. funkci ZPRAVA, do jejíhož prvního argumentu vložíme celý výraz uvedený v bodu b) - tedy výraz, který nám vrací textový řetězec v požadovaném tvaru „0040 - Tyč kruhová d50/1 851,00 Kč“, a do druhého argumentu, kterým stanovíme proměnnou velikost částky v textovém řetězci, tento výraz:
=DÉLKA(DOSADIT(DOSADIT(A1;“.“;“/“;DÉLKA(A1)- DÉLKA(PROČISTIT(DOSADIT(A1;“.“;““))));“.“;““))- NAJÍT(“/“;DOSADIT(DOSADIT(A1;“.“;“/“;DÉLKA(A1)- DÉLKA(PROČISTIT(DOSADIT(A1;“.“;““))));“.“;““);1))=34-23=11
Jde o rozdíl celkové délky upraveného textového řetězce bez mezer a s lomítkem a délky upraveného textového řetězce až po znak „lomítko“ včetně, který nám spolehlivě zjistí počet znaků, který v textovém řetězci zabírá částka včetně označení „Kč“, což je přesně ta délka, kterou potřebujeme z pravé části celého textového řetězce oddělit. Celý výraz opět pracuje bez konstant, takže ho lze rozkopírovat pro vydělení částky z dalších řetězců.
d)
Jako poslední krok můžeme celé řešení zapouzdřit do funkce HODNOTA, která umí textový řetězec „1 851,00 Kč“ převést na číselnou hodnotu 1851, což je velmi praktické, pokud budeme provádět s částkami výpočty.
Celý vzorec, byť působí na první pohled příliš složitě a nepřehledně, protože některé části jsou v něm použity opakovaně, má však tu výhodu, že reflektuje na délky jednotlivých částí řetězců, a tudíž ho lze dále již jen rozkopírovat bez nutnosti úprav či vytváření pomocných sloupců.
ZÁVĚR
Čtyři praktické informací pro práci s textem v buňce:
-
Údaj, který Excel primárně vyhodnotí jako hodnotu (číslo, datum), lze jako text zapsat tak, že před něj vložíme apostrof - znak „‘“ (doporučuji kombinací kláves Alt+39). Apostrof se v buňce nezobrazí, vložený údaj však Excel uloží jako textový řetězec.
-
Délka textu, který je možné do buňky vložit, je 32 767 znaků (verze E2003 jich v buňce zobrazí 1 024, v řádku vzorců celý počet), je tedy možné zapisovat i relativně obsáhlé texty.
-
Pro odřádkování při zadávání textu uvnitř jedné buňky je třeba použít kombinaci kláves ALT+Enter, samotný Enter by nás posunul o buňku dále.
-
I v rámci jedné buňky lze text omezeně formátovat (např. některé slovo zvýraznit jinou barvou, tučným písmem, kurzívou či podtrhnout), ne však, pokud je text v buňce výsledkem sloučení textových řetězců (tedy je zapsán jako vzorec).
1) Formulář „Přiznání k dani z přidané hodnoty“ 25 5401, vzor č. 19.
2) Zákon č. 235/2004 Sb., o dani z přidané hodnoty, ve znění pozdějších předpisů (dále jen „ZDPH“).
3) Český účetní standard č. 001 bod 2.4.3.
4) Zákon č. 563/1991 Sb., ve znění pozdějších předpisů (dále jen „ZU“), pravidla pro inventarizaci v § 29 až 30 byla změněna novelou č. 239/2012 Sb. s účinností od 1.9.2012.
5) Pro zjednodušení abstrahujeme od dalších údajů, které bychom v soupisu rádi viděli (počet MJ, cena MJ apod.).
6) Správnost výsledku můžeme ověřit nejen prostým součtem teček v buňce A1, ale také z předchozího příkladu na spojování řetězců, výsledkem jehož řešení je naše zadání (řešíme zrcadlově obrácený problém).