http://officir.web3.cz
pro časopis 
Přeci jen je začátek roku a nějaké to předsevzetí by se mělo splnit. Tak se na to podíváme. Chodit ráno se psem, přestat se cpát čokoládou, být vlídnější na souseda, schodit nějaké to kilo - a abych nezapomněl - zlepšit se v tom nešťastném Excelu... |
|
S laskavou podporou redakce můžete v následujících vydáních časopisu CHIP alespoň zčásti zasytit své svědomí. Na těchto místech naleznete články o Excelu pod lupou. Kapitoly jsou zamýšleny jako praktická příručka pro středně pokročilé uživatele Excelu od verze 97 výše. Věřím, že díky množství podkladů a vzorů, které vzápětí podrobíte vlastnímu testování, budete schopni vyřešit většinu úkolů, ať už je vaším cílem vyhodnotit produktivitu výrobního týmu nebo připravit databázi klientů firmy. Setkáte se s následujícími připravenými tématy (první díl již v tomto čísle):
|
|
| Jasně zelené buňky slouží pro vstup hodnot, na nichž závisí jiné buňky, oranžové pozadí představuje buňky výstupní (naformátované, se vzorci), tmavě zelená pole označují hlavičky tabulek a šedá oblast pak poznámky. |
| Pokud některá z popsaných technik bude v rozporu s vašimi znalostmi či zkušenostmi, prosím napište mi a podělte se o ně s ostatními. Nezapomeňte uvést verzi operačního systému a také Excelu (včetně uvedení případných servisních balíčků). Stejně tak potěší i zpráva o každém byť dílčím úspěchu při práci s Excelem. Jednotlivé díly budou synchronně vycházet na níže uvedené webové stránce, kde najdete i ohlasy, doplňky a případné opravy. Obsah kapitol není fixní, a lze jej přizpůsobit. Vyplyne-li to z vašich dopisů a neutuchajícího zájmu, dočkáte se pokračování na téma maker a VBA nebo společně nahlédneme pod kapotu Wordu a PowerPointu. Přeji nerušený tok užitečných informací. |
Formát buňky, textové funkce
| 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. |
| 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á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. | |
 |
|
| 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. | |
 |
|
 |
|
| 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. |
|
 |
|
| 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 |
|
 |
|
| Řá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. |
|
 |
|
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. |
|
|
|
| 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:
|
|
| Ú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). |
pro časopis 
Sešit Excelu s uvedenými příklady (komprese ZIP)