# Řekni mi, co v Excelu čteš

Formát buňky, textové funkce

autor: Ing. Petr Pecháček
http://officir.web3.cz

Sešit Excelu s uvedenými příklady (komprese ZIP)

## Na úvod

Definice zní: Formátování buňky určuje pouze způsob zobrazení dat. Není to tak úplně pravda, neboť jím můžeme ovlivnit výsledky vzorců. Tato definice odpovídá formátování jakoby vnějšímu, jenž dává formu obsahu samotnému nebo jeho okolí (počet desetinných míst, druh písma, pozadí buňky, ohraničení atd.). Formát je ovšem třeba chápat i jako vnitřní, definující obsah buněk pro výpočty. Pokud například formátujeme číslo 12,82365 s přesností na dvě desetinná místa, pak tím stanovujeme pouze vizuální projev, kdy vidíme číslo 12,82. Excel ale nadále počítá ve vzorcích s nezaokrouhleným číslem. Pokud ovšem buňce s tímto číslem přiřadíme formát text, pak doznají změn výsledky informativních funkcí (JE.TEXT a jiné), které na tuto buňku odkazují. A to již nejde o kosmetickou změnu. Vnitřní i vnější formát buňky často určujeme nevědomky způsobem, jakým zadáváme data. Typickými vodítky pro interpretaci hodnot jako text jsou písmena abecedy a znak mezery, pro formátování na datum a čas pak tečka, lomítko, dvojtečka a pomlčka. Pod menu Formát buňky se rovněž skrývá i volba zamčení buňky. Tu ovšem zde podrobněji rozebírat nebudeme, neboť se více vztahuje k tématu ochrany dat.

## Formátujeme

### Globální formátování

Začneme globálním formátováním. Pokud máme potřebu udělat cokoli v MS Office jak se říká "ve velkém" a "do budoucna", měli bychom si vždy vybavit pojem šablona. Excel samozřejmě umožňuje její vytvoření (více v nápovědě). Pro nás je v tuto chvíli podstatné, že taková šablona si umí uchovat tzv. "styly" a aplikovat je na buňkách sešitů založených na této šabloně. Výchozí podobu buněk nadefinujeme pod menu Formát / Styl..., kde nás především zajímá definice normálního stylu. Pokud nemáte problémy se zrakem, doporučuji pozměnit výchozí velikost písma Arial na osm. Výrazně tak zvýšíte počet viditelných řádků i sloupců na monitoru bez nutnosti rolování. Osobně mám ještě zaškrtnutou volbu Zalomit text na kartě Zarovnání a svislé zarovnání na střed.

### Individuální formátování

Individuálně formátujeme buňky nejčastěji pomocí ikonových tlačítek panelu nástrojů Formát či s využitím nabídky Formát / Buňky.... Dobře si pamatujte především tlačítko pro kopírování formátu (obrázek štětce namočeného ve žluté barvě). Rovněž jistě z jiných aplikací balíku MS Office znáte běžné zkratkové klávesy pro formátování písma (Ctrl+B přepíná tloušťku, Ctrl+I kurzívu). Formáty využíváme buď vestavěné nebo vlastní. Obrázek pod tímto textem vás již přímo nabádá k experimentům.

### Netradiční formáty

O obecných pravidlech zápisu formátu se dočtete v nápovědě (hledejte "Vytvoření vlastního číselného formátu"). Jen připomínám, že se v případě plného znění jedná o čtyři sekce oddělené středníky a to popořadě zleva: kladná čísla, záporná čísla, nula a textové hodnoty. Forma zápisu není až tak triviální, lépe jej osvětlí konkrétní příklady.

## Chyby formátu

Ukázka nad tímto textem poukazuje na jednu slabinu Excelu. Formátování je jedna z událostí, kterou Excel bohužel nepovažuje za impuls k přepočítání listu. Navíc zde nefunguje kombinace kláves Ctrl+Alt+F9, což je asi nejúčinnější způsob, jak za jiných okolností donutit Excel k přepočtu.
Formát buňky může být někdy velmi zavádějící. Jako nejlepší příklad poslouží obrázek výše, kdy funkce SUMA nevrací správnou hodnotu díky vizuálnímu zaokrouhlování. Zde nejde o chybu Excelu, ale vaši vlastní. Jak již bylo řečeno, funkce v tomto případě počítají s nezaokrouhlenými hodnotami, které můžete vidět v řádku vzorců. Chcete-li zaokrouhlovat doopravdy, použijte funkce listu ve vzorcích. Nezapomínejte ovšem, že chyba vzniklá zaokrouhlováním roste s počtem zaokrouhlovaných hodnot.

## Rady a tipy

- Uzpůsobte si Excel a používejte ikony pro rychlé formátování! Některé jsou zobrazeny již po prvním spuštění Excelu (Kopírovat formát, Přidat desetinné místo, Tučné, ...), ostatní najdete pod menu Nástroje / Vlastní, karta Příkazy, Kategorie: Formát. Ikonu s popisem z pravého okna přetáhněte myškou na některý z panelů nástrojů. Naopak její skrytí provedete tažením do prostoru tabulky.
- Účetnický formát (česky správně "účetní"?) oproti formátu měny úhledně odsadí číslo od pravého okraje.
- Odřádkování delšího textu buňky v libovolném místě dosáhnete kombinací Alt+Enter.
- V Excelu můžete používat vlastní barvy (viz Nástroje / Možnosti, karta Barva).
- Barevný text s výjimkou bílého je při nebarevném tisku převeden na černý. Nezapomínejte na to při použití barevného pozadí pod textem a zobrazte si náhled před tiskem pro kontrolu.
- I v jedné buňce se může vyskytovat vícero druhů formátování textu. Stačí v editačním modu vybrat část textu a změnit jeho vlastnosti.
- Používejte volby "bez výplně" namísto bílé barvy, která například coby výplň zakrývá hrany buňky (přeruší linky mřížky).
- Vnější ohraničení jedné buňky ovlivňuje i buňky sousedící. Ne vždy je ale regulérně zobrazeno na koncích stránky. Před tiskem provádějte kontrolu.
- Skutečně dlouhý text (vysvětlivky, nápověda apod.) nezapisujte do buňky. Použijte Textové pole z panelu nástrojů Kreslení.
- Potřebujete text otočený o 180°? Jediné mně známé řešení je následující: Původní text natočte pod úhlem -90° a text vůči němu "vzhůru nohama" nastavte na +90°.
- Textu zarovnaný doleva doporučují odsadit (Formát buňky, karta Zarovnání, Odsazení: 1)
- Výběr kopírovaný přes schránku z webové stránky doporučuji vkládat jako text (Úpravy / Vložit jinak... / Text) a následně teprve aplikovat zalomení a podobně. Ztratíte sice formát vkládané oblasti, ale tato technika je dle mého názoru rychlejší a čistší.

Podmíněné formátování bude uvedeno v samostatném dílu.

## Textové funkce

Výčet funkcí uvedených v příkladech je téměř kompletní a domnívám se, že ve většině úloh práce s textem si vystačíte. Snad jen funkce ČÁST by měla být členěna na ČÁSTZLEVA a ČÁSTZPRAVA, neboť sama o sobě hledá znaky vždy zleva. Jak uvádí například řádek 19, musíme si pak dopomoci jinými funkcemi, hlavně funkcí DÉLKA. Podívejme se ale ještě na některé příklady.
Řádek 3 znázorňuje textovou alternativu formátu buňky, funkce POLÍČKO pak představuje šikovnou funkci, která spadá více do informativních funkcí, velmi vhodná je ale právě ve spojitosti s dalším textem. V řádcích 5 a 6 je využito grafických aspektů některých znaků či celých fontů. Opakovaně jsou zde uvedeny funkce ZLEVA a ZPRAVA v dalším možném využití. Řádek 9 a 10 ukazuje techniku zápisu "vzorce nevzorce". Tyto způsoby vřele doporučuji oproti pouhému naformátování buňky na typ text, které navíc musí předcházet vložení textu ve tvaru vzorce.

## Úlohy k zamyšlení

- Dokážete již nyní pro špatně dostupné znaky (&, @, ö, ©) zhotovit tabulku zkratkových kláves pro jejich vkládání? Jakou funkci použijete? Tuto otázku lze nejlépe vyřešit s pomocí VBA, netrapte se jí dopodrobna. Utilitku si můžete stáhnout na webových stránkách Officíra, nebo se podívejte na nejběžnější znaky a jim přiřazené číselné kombinace pod tímto odstavcem.
- Při probírání formátů buňky jste mohli narazit na příklad doplnění buňky znaky rovná se a tedy její ochraně proti dopsání cifer. Za předpokladu, že buňka bude mít vždy stejnou šířku, uměli byste za pomocí některé z textových funkcí dosáhnout podobného efektu?
- Která funkce by vám umožnila "rozřezat" slovo na jednotlivá písmena?

## Generátor textových posloupností

Velmi často je potřeba vytvořit řadu textů lišících se pouze indexy. Pokud je tento index na počátku či konci textového řetězce, lze řadu vytvořit pouhým tažením za úchytný bod. Za základ řady někdy poslouží jedna buňka, jindy musí být alespoň dvě, aby Excel pochopil krok posloupnosti. (Nedaří-li se vám přesvědčit Excel o svém záměru, zkuste táhnout pravým tlačítkem myši a volit z příruční nabídky nebo před tažením stisknout klávesu Ctrl.) Větu, která tedy bude základem řady, vždy musíte přerušit v místě číslovky a napojit ji na další část například funkcí CONCATENATE. Využití jistě najdete sami (části kódu VBA využívající OFFSET, tagy HTML kódu pro miniatury obrázků apod.).

Jestliže je číslo mezi řetězci (viz obrázek výše), zkuste utilitku, která je součástí sešitu ke stažení na této stránce. Sloupec A a E slouží ke vstupu krajních textových částí, ve sloupci C pak zmíněným taženích vytvořte libovolnou číselnou řadu. Zaškrtávací políčka přidávají nebo naopak ubírají mezeru mezi řetězci. No a ve sloupci F vidíte výsledek, z něhož je možné překopírovat hodnoty ať už v rámci aplikace nebo mimo ni.

## Co všechno jde

Ošetření vstupů tabulky od různých lidí často není dosti účinné a podle toho vypadají i databáze klientů. V těchto tabulkách se špatně vyhledává, na dopisních obálkách se dopouštíme trapných omylů a v horším případě například účtujeme komu nemáme. Chyby narůstají s nepozorností, nedodržováním zápisů do vyhrazených kolonek, časovým stresem a nesprávně užívanou gramatikou (předem se omlouvám za své chyby na těchto stránkách :-). Jak ukazuje porovnání vstupů a výstupů uvedených v obrázku, v nápravě lze podniknout opravdu hodně. Ne vše lze ošetřit vestavěnými funkcemi listu (hranice, kde končí funkce listu a nastupuje VBA je ovšem mnohem dále, než si myslíte!). V daném případě k odstranění titulů a iniciál slouží vlastní funkce VBA, stejně tak pro zjišťování výskytu křestních jmen.
Poznámky pro jmenné a firemní seznamy:

- Ačkoliv je to "neosobní" dodržujte pořadí příjmení a jméno.
- Nepoznamenávejte si iniciály jmen. Beztak si nebudete po čase pamatovat, jestli J. je Jan nebo Jiří.
- Vynechte oslovení typu p., pí., pan, paní apod.
- Není to běžné, ale pokud už je to nutné, doporučuji pro tituly mít zvláštní kolonky (před jménem i za ním).
- Víte, že standardně (ne vždy) se po znacích tečka a čárka píše mezera a před nimi nikoli, že typ společnosti (a. s., s. r. o.) je přístavek a před ním se čárka (s mezerou) píše? Ale to jsme již mimo téma...

## Co nejde

Úlohy na téma práce s textem vyřešíme vždy jen s určitou úspěšností pouze se blížící ke 100%. Nemůžeme například předpokládat, že ženské příjmení končí na "ová" nebo programově ošetřit jméno Jan Pavel. A pokud tato řešení existují, náklady na jejich řešení jsou neúměrné zisku. Troufám si říci, že procento úspěšnosti algoritmů můžeme "vyhnat" na nějakých 95%. Čím volnější je definice podstaty řešeného problému, tím komplikovanější a méně úspěšnější je řešení (vcelku benevolentní syntaxe e-mailových adres je toho důkazem).

Sešit Excelu s uvedenými příklady (komprese ZIP)

pro časopis