Pro nastavení barvy datové čáry je určena vlastnost BarColor, kterou spojíte s vlastností ColorIndex. Příklad: Range("B2:B8").FormatConditions.Add xlDatabar Range("B2:B8").FormatConditions(1).MinPoint.Modify xlConditionLowestValue Range("B2:B8").FormatConditions(1).MaxPoint.Modify xlConditionValueHighest-Value Range("B2:B8").FormatConditions(1).BarColor.ColorIndex = 5
V sešitu Pokrocile_techniky.xlsm vytvořte makro, které v tabulce na pátém listu vytvoří tato podmíněná formátování: ❙ ❙ ❙ ❙
ve sloupci B duplicitní hodnoty žlutým pozadím; ve sloupci C tučným písmem údaje, u kterých je splatnost ve sloupci F z května nebo z června; ve sloupci D hodnoty mezi 300 a 500 modrým písmem, hodnoty nad 500 červeným písmem; ve sloupci E údaje zvýraznit modrými datovými čarami na základě hodnot.
V sešitu Pokrocile_techniky.xlsm je prvé makro vytvořeno pod názvem Podminene_formatovani.
7.7 Rozšířené řazení Jednoduché řazení metodou Sort funguje i v Excelu 2003. V Excelu 2007 a 2010 byly možnosti řazení tabulky podstatně rozšířeny: je možné třídit do více úrovní, třídit podle barvy písma nebo barvy pozadí. Informace o rozšířeném řazení uchovává objekt Sort, který spojíte s některým listem pomocí odkazu ActiveSheet nebo Worksheets. Objekt Sort obsahuje kolekci SortFields, jejímiž členy jsou jednotlivé úrovně řazení. Seřazení tabulky pomocí objektu Sort provedete ve dvou krocích: v prvním kroku přidáte ke kolekci SortFields požadované úrovně řazení, ve druhém kroku se spustí vlastní seřazení tabulky. Objekt Sort je výhodné využít k vytvoření objektové proměnné, deklarované jako typ Sort. Po zápisu této proměnné a tečky se zobrazí kontextová nabídka.
Protože se informace o řazení na každém listu ukládají se sešitem, před vytvářením nového řazení je vhodné stará řazení odstranit metodou Clear: Dim trideni As Sort Set trideni = ActiveSheet.Sort ActiveSheet.Sort.SortFields.Clear
Obrázek 7.7: Kontextová nabídka u objektu Sort
98 Pokročilé programovací techniky
Ukázka elektronické knihy, UID: KOS181278
Přidání nové úrovně řazení Přidání další úrovně řazení (neboli vytvoření dalšího členu kolekce SortFields) provedete metodou Add. Pro pohodlnější práci je opět výhodné použít tuto metodu k tvorbě objektové proměnné, deklarované jako SortField. Metoda Add má tyto parametry: ❚ Key: rozsah buněk (sloupec nebo řádek), kde se má řazení provést. Oblast pro třídění se musí zadat bez buňky s hlavičkou. ❚ SortOn: určuje, podle čeho se má řadit. Hodnotami jsou konstanty VBA xlSortOnValues (výchozí) řazení podle hodnot, xlSortOnCellColor – řazení podle barvy pozadí, xlSortOnFontColor – řazení podle barvy písma. ❚ Order: pořadí řazení. Hodnotami jsou konstanty VBA xlAscending (výchozí) – vzestupné řazení, xlDescending – sestupné řazení. U řazení podle barev pozadí nebo písma je vybraná barva u vzestupného třídění nahoře, u sestupného dole. Jestliže do řazení přidáte více barev, u vzestupného řazení je první uvedená barva umístěna jako první, u sestupného řazení jako poslední. ❚ CustomOrder: třídění podle uživatelského seznamu. Tento seznam zadáte jako textový řetězec („leden, únor, …“). ❚ DataOption: parametr se uplatní u čísel, formátovaných jako text. Konstanta xlSortNormal (výchozí) znamená, že se číselné hodnoty řadí textovým způsobem, tj. „1024“ je menší než „11“. Jestliže jsou ve sloupci pomíchána čísla s číselným nebo obecným a textovým formátem, umístí se napřed čísla seřazená podle velikosti, potom buňky s textovým formátem, seřazené jako text. Druhá možnost xlSortTextAsNumbers převede všechny údaje na číselné hodnoty a řazení se provede podle číselných hodnot. Do parametru CustomOrder je nutné zapsat text v uvozovkách. Není možné dosadit text seznamu do textové proměnné a tu použít jako hodnotu parametru CustomOrder – tento zápis skončí chybou kódu. Pro nastavení barvy u třídění je určena vlastnost SortOnValue. Použijte ji u vytvořené objektové proměnné a spojte ji s vlastností ColorIndex. Příklad řazení podle hodnot: Worksheets(3).Sort.SortFields.Add Range("C3:C8"), , xlAscending
Příklad řazení podle barvy písma: Dim trideni As SortField Set trideni = ActiveSheet.Sort.SortFields.Add(Range("B3:B8"), _ xlSortOnFontColor, xlAscending) trideni.SortOnValue.ColorIndex = 3
Příklad řazení podle barvy pozadí: Dim trideni As SortField Set trideni = ActiveSheet.Sort.SortFields.Add(Range("B3:B8"), _ xlSortOnCellColor, xlAscending) trideni.SortOnValue.ColorIndex = 6
Programování v Excelu 2007 a 2010 99
Ukázka elektronické knihy, UID: KOS181278
Příklad řazení podle uživatelského seznamu: Set trideni = ActiveSheet.Sort.SortFields.Add(Range("B3:B5") _ , , , "pondělí,úterý,středa,čtvrtek,pátek,sobota,neděle")
V parametru CustomOrder můžete zapsat libovolný textový řetězec. Je proto možné vytvořit řazení i podle textů, které nejsou zařazeny mezi vlastní seznamy.
Seřazení tabulky Vlastní výkonný příkaz pro seřazení je metoda Apply objektu Sort. Před spuštěním této metody je zapotřebí nastavit některé vlastnosti, které určují způsob řazení: ❚ SetRange: oblast tabulky, která se má seřadit. Použijete odkaz Range, do kterého zahrnete i řádek s hlavičkami sloupců. ❚ Header: tabulka se záhlavím nebo bez záhlaví. Hodnotami jsou konstanty VBA xlNo (výchozí) – bez záhlaví, xlYes – se záhlavím, xlGuess – typ tabulky se určí automaticky. ❚ Orientation: směr řazení. Hodnotami jsou konstanty VBA xlSortColumns – ve sloupci (shora dolů), xlSortRows – v řádku. ❚ MatchCase: hodnota True znamená, že se při řazení rozlišují malá a velká písmena, hodnota False (výchozí) je nerozlišuje. Příklad: Dim trideni As Sort trideni.SetRange Range("B2:E60") trideni.Header = xlYes trideni.Orientation = xlSortColumns trideni.Apply
Protože je výchozí hodnotou parametru Orientation konstanta xlNo, tabulka se automaticky předpokládá bez hlavičky (což je přesně opačně, než při uživatelské práci). U parametru Orientation se v nápovědě jako výchozí hodnota uvádí xlSortRows, tedy třídění v řádcích. Nicméně pokud nastavení této vlastnosti vynecháte, tabulka se setřídí ve sloupcích shora dolů. Z toho plyne jediné doporučení: používejte tyto vlastnosti vždy a vyhnete se nepříjemným překvapením. V sešitu Pokrocile_techniky.xlsm vytvořte makro, které tabulku na šestém listu seřadí takto: v prvé úrovni podle barvy pozadí ve sloupci B (žlutá nahoře), ve druhé úrovni podle barvy písma ve sloupci C (červená nahoře), ve třetí úrovni vzestupně podle hodnot ve sloupci D. V sešitu Pokrocile_techniky.xlsm je toto makro vytvořeno pod názvem Razeni.
100 Pokročilé programovací techniky
Ukázka elektronické knihy, UID: KOS181278