Použití podmíněného formátování v kontingenční tabulce může být trochu složité.
Vzhledem k tomu, že kontingenční tabulky jsou tak dynamické a data v backendu se mohou často měnit, musíte znát správný způsob použití podmíněného formátování v kontingenční tabulce v Excelu.
Špatný způsob použití podmíněného formátování v kontingenční tabulce
Podívejme se nejprve na pravidelný způsob použití podmíněného formátování v kontingenční tabulce.
Předpokládejme, že máte kontingenční tabulku, jak je uvedeno níže:
Ve výše uvedené datové sadě je datum v řádcích a ve sloupcích máme data o prodejích.
Zde je pravidelný způsob použití podmíněného formátování na libovolnou datovou sadu:
- Vyberte data (v tomto případě aplikujeme podmíněné formátování na B5: D14).
- Přejděte na stránku Domů -> Podmíněné formátování -> Pravidla pro horní/dolní část -> Nad průměrem.
- Zadejte formát (používám „Zelenou výplň s Dardovým zeleným textem“).
- Klikněte na OK.
Tím se použije podmíněné formátování, jak je uvedeno níže:
Všechny datové body, které jsou nad průměrem celé datové sady, byly zvýrazněny.
Problém této metody spočívá v tom, že použila podmíněný formát na pevný rozsah buněk (B5: D14). Pokud přidáte data do backendu a aktualizujete tuto kontingenční tabulku, podmíněné formátování se na ni nepoužije.
Například se vrátím k datové sadě a přidám data pro jedno další datum (11. ledna 2015). To je to, co dostanu, když aktualizuji kontingenční tabulku.
Jak vidíte na obrázku výše, data za 11. leden 2015 nejsou zvýrazněna (i když by měla být, protože hodnoty pro Store 1 a Store 3 jsou nadprůměrné).
Důvod, jak jsem uvedl výše, je ten, že podmíněné formátování bylo použito na pevný rozsah (B5: D14) a nerozšíří se na nová data v kontingenční tabulce.
Správný způsob použití podmíněného formátování v kontingenční tabulce
Zde jsou dvě metody, jak zajistit, aby podmíněné formátování fungovalo, i když jsou v backendu nová data.
Metoda 1 - Použití ikony formátování kontingenční tabulky
Tato metoda používá ikonu Možnosti formátování kontingenční tabulky, která se zobrazí, jakmile použijete podmíněné formátování v kontingenční tabulce.
Zde jsou kroky, jak toho dosáhnout:
- Vyberte data, na která chcete použít podmíněné formátování.
- Přejděte na domovskou stránku -> podmíněné formátování -> pravidla pro horní/dolní část -> nad průměrem.
- Zadejte formát (používám „Zelenou výplň s Dardovým zeleným textem“).
- Klikněte na Ok.
- Když provedete výše uvedené kroky, použije podmíněné formátování na datovou sadu. V pravé dolní části datové sady se zobrazí ikona Možnosti formátování:
- Klikněte na ikonu. V rozevíracím seznamu se zobrazí tři možnosti:
- Vybrané buňky (které by byly ve výchozím nastavení vybrány).
- Všechny buňky zobrazující hodnoty „Součet výnosů“.
- Všechny buňky zobrazující hodnoty „Součet výnosů“ pro „Datum“ a „Obchod“.
- Vyberte třetí možnost - Všechny buňky zobrazující hodnoty „Součet výnosů“ pro „Datum“ a „Uložit“.
Když nyní přidáte jakákoli data do back -endu a aktualizujete kontingenční tabulku, dodatečná data budou automaticky pokryta podmíněným formátováním.
Pochopení tří možností:
- Vybrané buňky: Toto je výchozí možnost, kde se podmíněné formátování použije pouze na vybrané buňky.
- Všechny buňky zobrazující hodnoty „Součet výnosů“: V této možnosti zohledňuje všechny buňky, které zobrazují hodnoty součtu výnosů (nebo jakákoli data, která máte v sekci hodnot kontingenční tabulky).
- Problém této možnosti spočívá v tom, že pokryje také hodnoty celkového součtu a použije na něj podmíněné formátování.
- Všechny buňky zobrazující hodnoty „Součet výnosů“ pro „Datum“ a „Obchod“: V tomto případě je to nejlepší volba. Aplikuje podmíněné formátování na všechny hodnoty (kromě celkových součtů) pro kombinaci data a obchodu. I když na zadní konec přidáte další data, tato možnost se o to postará.
Poznámka:
- Ikona Možnosti formátování je viditelná hned po použití podmíněného formátování na datovou sadu. Pokud zmizí, pokud uděláte něco jiného (upravit buňku nebo změnit písmo/zarovnání atd.).
- Podmíněné formátování zmizí, pokud změníte pole řádků/sloupců. Pokud například odeberete pole Datum a použijete jej znovu, dojde ke ztrátě podmíněného formátování.
Metoda 2 - Použití správce pravidel podmíněného formátování
Kromě použití ikony Možnosti formátování můžete také použít dialogové okno Správce pravidel podmíněného formátování k použití podmíněného formátování v kontingenční tabulce.
Tato metoda je užitečná, pokud jste již použili podmíněné formátování a chcete změnit pravidla.
Postupujte takto:
- Vyberte data, na která chcete použít podmíněné formátování.
- Přejděte na stránku Domů -> Podmíněné formátování -> Pravidla pro horní/dolní část -> Nad průměrem.
- Zadejte formát (používám „Zelenou výplň s Dardovým zeleným textem“).
- Klikněte na Ok. Tím se na vybrané buňky použije podmíněné formátování.
- Přejděte na Domů -> Podmíněné formátování -> Spravovat pravidla.
- Ve Správci pravidel podmíněného formátování vyberte pravidlo, které chcete upravit, a klikněte na tlačítko Upravit pravidlo.
- V dialogovém okně Upravit pravidlo uvidíte stejné tři možnosti:
- Vybrané buňky.
- Všechny buňky zobrazující hodnoty „Součet výnosů“.
- Všechny buňky zobrazující hodnoty „Součet výnosů“ pro „Datum“ a „Obchod“.
- Vyberte třetí možnost a klikněte na OK.
Toto použije podmíněné formátování na všechny buňky pro pole „Datum“ a „Uložit“. I když změníte data backendu (přidáte další data úložiště nebo data), bude podmíněné formátování funkční.
Poznámka: Podmíněné formátování zmizí, pokud změníte pole řádků/sloupců. Pokud například odeberete pole Datum a použijete jej znovu, dojde ke ztrátě podmíněného formátování.
