Rozšířené podmíněné formátování

Možná jste se již setkali s funkcí podmíněné formátování (dále PF), které umožňuje stanovit formát buňky na základě daných podmínek (obsahu buňky). Tak můžeme například Excelu říct, že pokud bude buňka A1 obsahovat číslo menší než 10, ohranič buňku červenou čarou. Ve všech ostatních případech zůstane buňka bez ohraničení.

Zatímco MS Excel 2007 a novější již umožňují zadat více kritérií, verze 2003 povolí zadat maximálně 3 podmínky. Mnohdy však potřebujeme podmínek zadat více. Jak se s tímto vypořádat?

Stáhnout vzorový sešit (xls, 30 kB), 25. 3. 13

Postup

Pro formátování dle více kritérií využijeme funkce VBA Select ... Case. Zatímco příkaz .Interior.ColorIndex nastaví barvu pozadí buňky, .Font.ColorIndex se postará o správnou barvu písma.

Makro je funkční, avšak prozatím neefektivní - pokusím se jej předělat v nejbližší době.

• Předpokládejme, že už máme v sešitu oblast buněk, kterou budeme chtít formátovat.
• Nyní si v jiné oblasti oblasti buněk vytvoříme "vzorkovník", podle kterého bude naše oblast formátována.
• Budeme-li chtít například použít 6 formátů buněk, ve vzorkovníku naformátujeme 6 buněk (každou s jedním formátem). Obsah buněk ve vzorkovníku by měl odpovídat podmínkám - viz obrázek vpravo.
• Do sešitu vložíme nový modul do kterého umístíme následující makro.

Option Explicit

Sub formát()

Application.ScreenUpdating = False

Dim bunka As Range

Dim oblast As String

 oblast = "z_přehled" '<<< oblast, která bude formátovaná

'vymaže předchozí formátování

With Range(oblast)

 .Interior.ColorIndex = xlNone

 .Font.ColorIndex = 1

End With

'naformátuje pozadí buňky a barvu textu v oblasti podle buněk A2:A7

For Each bunka In Range(oblast)

 With bunka

 Select Case .Value

 Case 1

 .Interior.ColorIndex = Range("A2").Interior.ColorIndex

 Case 2 

 .Interior.ColorIndex = Range("A3").Interior.ColorIndex

 Case 3 

 .Interior.ColorIndex = Range("A4").Interior.ColorIndex

 Case 4 To 8 

 .Interior.ColorIndex = Range("A5").Interior.ColorIndex

 Case 9 To 20 

 .Font.ColorIndex = Range("A6").Font.ColorIndex

 .Interior.ColorIndex = Range("A6").Interior.ColorIndex

 Case "-" 

 .Interior.ColorIndex = Range("A7").Interior.ColorIndex

 End Select

 End With

Next bunka

Application.ScreenUpdating = False

End Sub

• V makru nastavíme správnou oblast, která se bude formátovat a buňky, které tvoří vzorkovník (v tomto případě A1:A7).
• Makro spustíme.

Zásady pro vzhled tabulek

Tu a tam je potřeba vytvořit tabulku, která nebude sloužit pouze pro vlastní účely, ale uvidí ji i ostatní. Taková tabulka se pak stává vaši vizitkou a proto by podle toho měla vypadat.

Níže uvádím pár rad a doporučení, jak by tabulka měla vypadat.

Formátování

Asi to první, co každého na tabulce upoutá. Design by měl zůstat střízlivý – tabulka je především o datech, které obsahuje – nejedná se o výtvarnou soutěž.

Barvy

Hlídejte si počet barev. Na rozdíl od jiných prvků (grafů, prezentací) si tabulka mnohdy vystačí s jednou či dvěma barvami. V praxi se mi osvědčilo pracovat primárně s několika odstíny šedé, které v kombinaci s podmíněným formátování (různé barvy pro různé hodnoty) dělají doslova zázraky (viz obrázek dole).

Texty by měly zůstat dobře čitelné a neunavovat oči (čili použití červené, svítivě zelené, žluté apod. nepřichází v úvahu). Chceme-li něco zvýraznit, můžeme použít například tučné písmo, nebo jinou velikost písma, ale s barvami neplýváme.

Ohraničení

Tabulka, kde je každá buňka ohraničená černě je přežitek. Data samotná se pak ve změti černých čar ztrácí a oči mají problém najít konkrétní číslo nebo text. Proto ohraničení používáme jen v případě nutnosti.

Mnohdy se osvědčí použít na ohraničení buněk světle šedou barvu. Výhodu také přináší zamyšlení, zda pro ohraničení potřebujeme jak vodorovné, tak svislé čáry – většinou dojdu k závěru, že svislé čáry (pro oddělení sloupců) bohatě stačí.

Formátování čísel

Nebojte se změnit již zažité návyky. Například jestliže Excel standardně formátuje datum jako d.m.yyyy (např. 17.6.2009), nebojte se změnit formát na d. m. yy (ve výsledku 17. 06. 09). Stejně tak se vyplatí pohrát si s formátem měny, procent a brát v potaz počet desetinných čísel.

Výška řádků a šířka sloupců

Opět žádné psané pravidlo, ale čistě moje doporučení. Snažím se nastavit sloupce tak, aby jejich šířka v pixelech byla stejná nebo tvořila násobky. Např. sloupec A = 50 px, sloupec B = 50 px, sloupec C = 200 px, sloupec D = 100 px. To samé platí pro výšku řádků.

Fonty


Používejte klasické fonty (druhy písma). Můžete sice vytvořit hezký nápis, ale pokud ostatní nebudou mít ve svém PC tento font, zobrazí se jim ve fontu alternativním – výsledek pak může být nemile překvapující.

Ostatní

Odstraňte ze sešitu přebytečné listy. Máte-li svůj projekt na jednom listu, je zbytečné publikovat i další dva, které zůstanou prázdné.

Pokud můžete, vyhněte se použití maker – nesprávně napsané makro nemusí fungovat ve všech verzích Excelu nebo jiných tabulkových procesorech. Na většinu úkonů vystačí funkce listu.

Inspirujte se staršími nápady. Víte-li, že jednu tabulku budete používat několik měsíců (let) a průběžne ji upravovat, doporučuji si při každé větší grafické změně udělat screenshot. Tak budete vědět, jak tabulka vypadala například před rokem a jaký pokrok jste na ni udělali. Třeba (jako já) zjistíte, že původní vzhled byl dobrý a vrátite se k němu (nebo použijete některé jeho prvky).

P. S.: Obdobná pravidla platí i pro tvorbu grafů – o tom však už napsal dostatek Jon Peltier nebo Chandoo. Tipy, jak ze špatného grafu udělat graf dobrý, uvádím také na tomto blogu, články se štítkem grafy.

Výsečový graf s dílčí výsečí

Původní graf

Říká se, že jestli je jeden výsečový graf špatný, dva jsou přímo katastrofa. Problematikou vizualizace dat jsem se nedávno zabýval v jedné ze svých seminárních prací a ve skriptech, ze kterých jsem také čerpal, jsem narazil na praktickou ukázku.

Co je na grafu špatně:

• typ grafu sice může být vhodný pro prezentaci podílu jednotlivých částí na celku, pro větší počet hodnot je však těžko interpretovatelný,
• druhý „koláč“ ještě více ztěžuje porovnání jednotlivých hodnot od oka (bez čtení hodnot),
• uspořádání řad je nelogické, hodnoty nejsou seřazeny ani chronologicky, ani od nějvětší po nejmenší,
• struktura hodnot, kde zdánlivě nedůležitá položka „ostatní“ tvoří drtivou většinu z prvního výsečového grafu,

Nový graf

Pro větší množství hodnot je mnohem vhodnější sloupcový nebo pruhový graf, který je nejjednodušší na interpretaci.

Co se zlepšilo:

• řady jsou v pruhovém grafu mnohem jednodušeji porovnávatelné mezi sebou,
• řady jsou uspořádány chronologicky,
• nadpis grafu je v horní části, na viditelnějším mís­tě,
• popisky hodnot jsou seřazeny pod sebou, což usnadní porovnání řad v „textové“ podobě,
• celková plocha, kterou graf zabírá, je menší, oči toho nemusí tolik zkoumat.

Práce s makry

Zdá se vám někdy, že možnosti Excelu nestačí? Potřebovali byste vytvořit vlastní funkci nebo zautomatizovat určitou posloupnost příkazů? Makra, napsána v jazyce VBA (Visual Basic for Applications), jsou tu od toho. Pojďme se podívat, jak je možné takové makro vytvořit.

Aby byly sešity s makry funkční, je třeba mít zapnutou střední úroveň zabezpečení. Toho docílíte následujícím postupem: menu Nástroje – položka Možnosti – karta Zabezpečení – tlačítko Zabezpečení maker – volba Střední.

Záznamník maker

Nejjednodušší je použít funkci Záznam makra. V Excelu 2003 jej najdeme pod nabídkou Nástroje – Makra – Záznam nového makra. Excel 2007 má tlačítko na kartě Vývojář. Pokud tato karta není zobrazená, klepněte na kulaté tlačítko sady Microsoft Office v levém horním rohu a potom na tlačítko Možnosti aplikace Excel. V kategorii Oblíbené zaškrtněte v části Nejpoužívanější možnosti při práci s aplikací Excel políčko Zobrazit na pásu kartu Vývojář. Potvrďte tlačítkem OK.

Excel bude po spuštění záznamínku požadovat zadání názvu makra. Po potvrzení tlačítkem OK se spustí „kamera“, která bude zaznamenávat veškerý pohyb na listu až do stisknutí tlačítka Stop.

Takto Excel naučíte, co by mělo vaše makro dělat. Po spuštění vlastního makra se tak jedním úkonem provede přesně ta sekvence úkolů, které jste provedli během záznamu makra.

Pro začátečníky se jedná o nejjednodušší metodu, jak přijít do styku s makry. Později však zjistíte, že vygenerovaný kód je značně nečistý a obsahuje spoustu přebytečných příkazů. Práce se záznamníkem maker je také omezená pouze „viditelnou“ prací na listu. Možnosti maker a VBA jsou však mnohem dále.

Vlastnoruční napsání makra

Toto řešení již vyžaduje jisté znalosti jazyka VBA, avšak výsledek je mnohem přehlednější a čistější. Krom toho můžete pomocí velkého množství funkcí, které jazyk nabízí, dostáhnout mnohem dále než s běžným záznamníkem.

Přes klávesovou zkratku Alt + F11 se dostaneme do editoru VBE (Visual Basic Editor). V levé části vydíme seznam aktuálně otevřených listů nebo doplňků.

Abychom mohli začít psát makro, je nutné nejprve do aktivního sešitu vložit tzv. modul, do kterého jsou všechna makra ukládaná.

• Klepneme pravým tlačítkem na aktivní sešit a z nabídky Insert vybereme příkaz Module.
• Do nově zobrazeného okna můžeme psát vlastní makro.

Import existujícího modulu

Existuje mnoho již připravených modulů s makry. Vzhledem k faktu, že jeden modul může obsahovat i více maker, je tak relativně snadnou záležitostí vytvořené moduly importovat do vlastních sešitů.

• V editoru klepneme pravým tlačítkem na aktivní sešit a z kontextového menu vyberem příkaz Insert file.
• Vyhledáme soubor s příslušným modulem (přípona *.bas).
• Klepnutím na tlačítko Otevřít se modul vloží a makra budou připravena k použití.