Zvýraznění klíčových dat pomocí podmíněného formátování aplikace Excel

Pokud vstoupíte do jakékoli kanceláře, pravděpodobně narazíte na uživatele aplikace Microsoft Excel. Je to užitečný nástroj pro obchodní analýzu, pokud se správně používá. Problémem mnoha tabulek je, že všechny buňky vypadají stejně. Klíčové informace musíte lovit. V tomto návodu vám ukážu, jak zvýraznit buňky v aplikaci Excel pomocí několika metod. (Příklad tabulky ke stažení v části Zdroje.)

Běžně se setkávám s tímto scénářem. Dostanete od někoho tabulku se stovkami datových řádků, které vypadají stejně. Vše je naformátováno stejným nudným způsobem. Jsou však data stejná? Existují hodnoty buněk, které se liší od ostatních? Vymyká se něco průměru? To jsou typické otázky, které mají uživatelé, když otevřou tabulky.

Formátování buněk aplikace Excel na základě podmínek

Místo toho, aby čtenář prohledával každou buňku, můžete nechat software udělat kus práce pomocí podmíněného formátování. To umožňuje aplikaci Excel použít definovaný formát na buňky, které splňují určitá kritéria. Jedná se o pravidla, která můžete definovat.

Cílem je nechat vyniknout důležité informace, abyste je mohli snadněji najít.

Excel již některé z nich dělá za vás. Například při formátování čísel existují možnosti, jak zobrazit záporná čísla červeně. To je příklad předdefinovaného formátu.

Program dokonce umožňuje používat možnosti formátování jednotlivých buněk nebo řádků. Nejjednodušší metodou je nechat aplikaci Excel použít podmíněné formátování, pokud splňuje určité podmínky nebo místa. Používá se přitom metoda „Hodnota buňky je“.

Některé příklady zvýraznění hodnoty buňky zahrnují:

• Použít červenou výplň, pokud je hodnota menší než 50
• Použít kurzívu, tučný styl písma, pokud je hodnota mezi 70 a 90
• Použít zelenou barvu písma, pokud text obsahuje „Montana.“
• Zvýraznit hodnoty, které se rovnají 15
• Použít žlutou výplň u duplicitních hodnot
• Přidat ikonu šipky nahoru k hodnotám v buňce nad 10 %

Excel také umožňuje použít vzorce pro podmíněné formátování. Jednou z výhod vzorců Excelu je, že se na hodnoty můžete odkazovat i jinde v tabulce. V níže uvedeném příkladu požaduji, aby se v případě, že je hodnota v B2 větší než hodnota v C2, použila zelená barva pozadí.

Microsoft tuto funkci v průběhu let výrazně vylepšil a nyní předdefinoval oblíbené příklady, takže se nemusíte tolik spoléhat na vzorce. Níže uvádíme několik oblíbených příkladů.

Jak použít podmíněné formátování aplikace Excel

Přidání nového pravidla formátování je snadné. Nejtěžší je najít, co chcete v tabulce zdůraznit. Pomáhá přemýšlet o potřebách publika a o tom, jaké akce by mohlo provést.

Příklad 1: Zvýraznění buněk nad určitým číslem

Pokud chcete postupovat podle něj, je to příklad 1 z ukázkové tabulky. Budu zvýrazňovat vybrané buňky, pokud počet obyvatel státu přesáhne nebo se rovná 2 % obyvatel USA.

1. Otevřete vzorovou tabulku state-counts-cf.xlsx a klikněte na záložku Příklad č. 1.
2. Klikněte na buňku F2.
3. Vyberte celý sloupec stisknutím kláves Ctrl +Shift + ↓.
4. Na kartě Domů klikněte na tlačítko Podmíněné formátování.
5. V rozbalovací nabídce vyberte možnost Pravidla zvýrazňování buněk.
6. Z postranní nabídky vyberte možnost Větší než…

1. upravte hodnotu v poli Formátovat buňky, které jsou VĚTŠÍ než:.

Poznámka: Excel toto pole předvyplní na základě stávajících hodnot zvýrazněných buněk.

1. Zadejte požadovanou hodnotu. V našem případě je to 2,0 %. Excel začne zvýrazňovat buňky za vás.
2. Klepnutím na trojúhelník klávesnice_šipka_dolů provedete výběr barvy.

Poznámka: Můžete vytvořit jiné schéma výběrem Vlastního formátu…

1. Klepněte na OK.

Měli byste nyní vidět aplikované výsledky.

Příklad 2: Zvýraznění 10 nejlepších záznamů

Jedním z oblíbených požadavků je vyhledání nejlepších záznamů, ať už jde o stanovený počet nebo procento. Toto je příklad jednoho z přednastavených nebo vestavěných formátů aplikace Excel. Jinými slovy, nemusíte zadávat konkrétní hodnoty jako v příkladu 1. Tyto předvolby jsou vytvořeny na základě běžných položek, které lidé hledají, například Top 10 nebo podprůměrné výsledky. Výhodou je, že nemusíte provádět matematické výpočty.

V tomto příkladu chci zvýraznit 10 nejlepších států podle počtu míst ve Sněmovně reprezentantů USA ve sloupci D.

1. Otevřete stránku State-counts-cf.xlsx vzorovou tabulku a klikněte na záložku Příklad 2.
2. Klikněte na buňku D2.
3. Vyberte celý sloupec stisknutím kláves Ctrl +Shift + ↓.
4. Na kartě Domů klikněte na tlačítko Podmíněné formátování.
5. V rozbalovací nabídce Podmíněné formátování vyberte možnost Pravidla nahoře/dole.
6. V boční nabídce vyberte možnost Top 10 položek…
7. V dialogovém okně Top 10 položek v případě potřeby upravte počet nebo barevné schéma.
8. Klepněte na tlačítko OK.

Příklad 3: Zobrazení datových pruhů místo barevného stínování

Další užitečnou možností je použití „datových pruhů“. Jedná se o další typ vizualizace, který můžete použít. Hlavním rozdílem oproti formátu Datové pruhy je, že se zobrazují na všech buňkách, místo aby splňovaly určitou podmínku.

Ve výchozím nastavení zachová Excel pozadí textové hodnoty. Zjistil jsem, že to může být rušivé. V tomto příkladu jsem zdvojil sloupec % změny, ale řekl jsem Excelu, aby nevkládal hodnotu dat do sloupce I. Výsledkem je pouze datový pruh.

Formát datového pruhu nefunguje dobře se všemi typy dat. Toto je dobrý příklad vyzkoušení různých formátů, abyste zjistili, co pro vaše data funguje nejlépe. To platí zejména v případě, že máte záporné hodnoty nebo úzký rozsah.

1. Otevřete vzorovou tabulku state-counts-cf.xlsx a klikněte na záložku Příklad 3. V případě, že máte záporné hodnoty nebo úzký rozsah, můžete použít formát datových sloupců.
2. Klikněte na buňku I2.
3. Vyberte celý sloupec stisknutím kláves Ctrl +Shift + ↓.
4. Na kartě Domů klikněte na tlačítko Podmíněné formátování.
5. V rozbalovací nabídce Podmíněné formátování vyberte možnost Datové sloupce.
6. Z postranní nabídky vyberte možnost Další pravidla…

1. V části Upravit popis pravidla: zaškrtněte políčko Zobrazit pouze sloupec.

1. Klikněte na tlačítko OK.

Příklad 4: Zvýraznění řádku

Zvýraznění odlehlých buněk je skvělé, ale někdy, pokud máte velkou tabulku, nemusíte barevné buňky vidět, protože jsou mimo obrazovku. V těchto situacích pomůže zvýraznění celého řádku. Tímto způsobem bez ohledu na to, které sloupce si prohlížíte, víte, které řádky jsou důležité. Nejlépe to funguje, když máte podmíněnou 1.

Klíčovým rozdílem je, že zde nebudeme zvýrazňovat sloupec, ale celou tabulku. Tak získáme efekt řádků.

1. Otevřete ukázkovou tabulku state-counts-cf.xlsx a klikněte na kartu Příklad 4.
2. Klikněte na buňku I2.
3. Vyberte všechny řádky stisknutím kláves Ctrl +Shift + ↓ + →.
4. Na kartě Domů klikněte na tlačítko Podmíněné formátování.
5. Zvolte Nové pravidlo…
6. V modulu Upravit pravidlo formátování vyberte možnost Použít vzorec pro určení, které buňky formátovat.
7. V části Formátovat hodnoty, kde je tento vzorec pravdivý: zadejte svá kritéria.

1. Klikněte na tlačítko Formátovat… a použijte svou barvu zvýraznění.
2. Klepnutím na tlačítko OK přijmete svou barvu.

V uvedeném příkladu žádám o zvýraznění každého řádku, kde je hodnota ve sloupci F (Procenta celkem obyvatel USA) větší než 2 %. Barva zvýraznění je zelená. Používám také smíšený odkaz na buňku tím, že ve vzorci před F2 umístím znak $.

zvýrazněný_znak Tip: Pokud si nejste jisti, že máte správný vzorec, můžete jej vždy vyzkoušet v jiném sloupci. Například bych mohl do buňky H2 zadat tento vzorec =$F2>20%. Buňka H2 by se vyhodnotila jako TRUE nebo FALSE. Pak byste mohli vzorec zkopírovat dolů do sloupce, abyste se ujistili, že jste získali očekávané výsledky.

1. Klepněte na OK.

Zjištění vašich pravidel pro podmíněné formátování

Někdy dostaneme tabulku a vidíme zvýrazněné buňky nebo řádky. Ale… kde jsou podmíněná pravidla, nebo tyto buňky někdo naformátoval ručně? Excel naštěstí nabízí způsob, jak pravidla podmíněného formátování zobrazit. Můžete použít příkladovou tabulku a postupovat podle ní nebo použít vlastní.

1. Otevřete tabulku.
2. Na kartě Domů klepněte na tlačítko Podmíněné formátování.
3. V rozbalovací nabídce vyberte možnost Spravovat pravidla…

1. Ve Správci pravidel podmíněného formátování vyberte svůj list.
2. Klepněte na tlačítko OK.

Ve výše uvedeném příkladu vidíte pravidlo, které jsem použil pro zvýraznění řádku. Odtud můžete pravidlo přidat, upravit nebo odstranit.

Tipy & Triky

Tento článek se pouze dotkl některých způsobů, jak můžete podmíněné formátování použít. Nejlepším způsobem, jak se to naučit, je experimentovat s nějakými daty, ať už vlastními, nebo s ukázkovou tabulkou. Zde je pro vás několik dalších tipů:

• V jednom sloupci můžete mít více pravidel. Například můžete chtít, aby různé hodnoty rozsahu měly různé barvy.
• Hrajte si s různými formáty, abyste zjistili, který typ se pro vaše data hodí nejlépe.
• Pokud dostanete neočekávané výsledky, zkontrolujte, zda se spouští více podmínek a jejich pořadí. Možná bude třeba změnit pořadí.
• Na datových sloupcích můžete nastavit rozsahy. Například pokud máte sloupec % dokončených, můžete nastavit rozsah 0 až 1.
• Pomocí možnosti Vymazat pravidla můžete odstranit pravidla podmíněného formátování.
• Po zadání dat aplikujte svá pravidla. Někdy se při vkládání řádků vytvářejí duplicitní pravidla.

Podmíněné formátování aplikace Excel může při vhodném použití zvýšit užitečnost vašich tabulek. Lidé nebudou ztrácet čas hledáním důležitých změn nebo oné „jehly v kupce sena.“

Další zdroj

• Průvodce programem Excel pro začátečníky XLOOKUP
• Jak používat Excel VLOOKUP
• Jak používat funkci IF. v Excelu
• Jak vytvořit tabulku Excel Pivot