Vložení buněk na základě hodnoty v aplikaci Microsoft Excel

Při práci s tabulkami mají hodnoty, které jsou v ní zobrazeny, zásadní význam. Důležitou součástí je však také její konstrukce. Někteří uživatelé to považují za druhotný faktor a nevěnují mu příliš pozornost. Ale marně, protože krásně navržený stůl je důležitou podmínkou pro jeho lepší vnímání a pochopení ze strany uživatelů. Zvláště důležitou roli v tomto je vizualizace dat. Například pomocí vizualizačních nástrojů můžete barvit buňky tabulky v závislosti na jejich obsahu. Zjistíme, jak to lze v aplikaci Excel udělat.

Postup změny barvy buněk v závislosti na obsahu

Samozřejmě je vždy dobré mít dobře navržený stůl, ve kterém jsou buňky, v závislosti na obsahu, malovány v různých barvách. To platí obzvláště pro velké tabulky obsahující velkou sadu dat. V tomto případě buňky s barevným kódem výrazně usnadní orientaci uživatelů v tomto obrovském množství informací, protože lze říci, že jsou již strukturované.

Prvky listu lze vyzkoušet ručně, ale opět, pokud je tabulka velká, bude trvat značné množství času. Navíc v takové řadě dat může lidský faktor hrát roli a chyby budou učiněny. Nehovoříme-li o tom, že tabulka může být dynamická a data v ní se pravidelně mění a masově. V takovém případě se ruční změna barvy vůbec nerealizuje.

Ale existuje cesta. U buněk, které obsahují dynamické (měnící se) hodnoty, se používá podmíněné formátování a pro statistické údaje můžete použít nástroj Najít a nahradit .

Metoda 1: podmíněné formátování

Pomocí podmíněného formátování můžete určit určité limity hodnot, při kterých budou buňky vymalovány v konkrétní barvě. Barvení se provede automaticky. V případě, že se hodnota buňky změní v důsledku změny za hranice hranice, bude tento listový prvek automaticky překreslený.

Podívejme se, jak to funguje na konkrétním příkladu. Máme tabulku příjmů podniků, v níž jsou údaje přerušovány měsíčně. Potřebujeme zvýraznit v různých barvách ty prvky, jejichž výše příjmů je menší než 400 000 rublů, od 400 000 do 500 000 rublů a přesahuje 500 000 rublů.

1. Vybíráme sloupec, ve kterém jsou umístěny informace o výnosech podniku. Potom přejděte na kartu Domov . Klepněte na tlačítko "Podmíněné formátování" , které je umístěno na pásku v poli "Styly" . V otevřeném seznamu vyberte položku "Spravovat pravidla ..." .
2. Otevře se okno pro správu podmíněných formátovacích pravidel. Pole "Zobrazit pravidla formátování pro" by mělo být nastaveno na "Aktuální fragment" . Ve výchozím nastavení by mělo být tam, ale jen pro případ, zkontrolujte a v případě nesouladu změňte nastavení podle výše uvedených doporučení. Poté klikněte na tlačítko "Vytvořit pravidlo ..." .
3. Otevře se okno pro vytvoření pravidla pro formátování. V seznamu typů pravidel vyberte možnost "Formát pouze buňky, které obsahují" . V bloku popisu pravidel v prvním poli by měl být přepínač v poloze "Hodnoty" . Ve druhém poli nastavte přepínač na pozici "méně" . Ve třetím poli určíme hodnotu, prvky listu s hodnotou menší než je barva určitou barvou. V našem případě bude tato hodnota 400 000 . Poté klikněte na tlačítko "Formát ..." .
4. Otevře se okno formátu buňky. Přejdeme na kartu "Vyplnit" . Vybíráme barvu výplně, kterou chceme mít buňky, které obsahují méně než 400 000 . Poté klikněte na tlačítko "OK" ve spodní části okna.
5. Vrátíme se do okna pro vytvoření pravidla pro formátování a tam také klikneme na tlačítko "OK" .
6. Po této akci budeme opět přesměrováni do Správce podmíněných formátovacích pravidel . Jak vidíte, bylo již přidáno jedno pravidlo, ale musíme přidat ještě dvě. Klikněte znovu na tlačítko "Vytvořit pravidlo ..." .
7. A opět se dostaneme do okna vytváření pravidel. Přejdeme do části "Formátovat pouze buňky, které obsahují" . V prvním poli této části ponecháme parametr "Hodnota buňky" av druhém nastavíme přepínač do polohy "Mezi" . Ve třetím poli je třeba zadat počáteční hodnotu rozsahu, ve kterém budou prvky listu naformátovány. V našem případě je toto číslo 400 000 . Ve čtvrté hodnotě udáváme konečnou hodnotu tohoto rozsahu. Bude to 500 000 . Potom klikněte na tlačítko "Formát ..." .
8. V okně formátování se přesuneme znovu na kartu "Vyplnit" , tentokrát však již zvolíme jinou barvu a pak klikneme na tlačítko "OK" .
9. Po návratu do okna vytváření pravidel klikneme také na tlačítko "OK" .
10. Jak vidíte, v Správci pravidel jsme již vytvořili dvě pravidla. Zbývá tedy vytvořit třetí. Klepněte na tlačítko "Vytvořit pravidlo" .
11. V okně vytváření pravidel se opět přesuňte do části "Formátovat pouze buňky, které obsahují" . V prvním poli ponecháme volbu "Hodnota buňky" . Ve druhém poli nastavte přepínač na policii "Více" . Ve třetím poli řídíme číslo 500000 . Potom, stejně jako v předchozích případech, klikněte na tlačítko "Formát ..." .
12. V okně "Formát buněk" se opět přesuneme na kartu "Vyplnit" . Tentokrát zvolte barvu, která se liší od předchozích dvou případů. Klikněte na tlačítko "OK" .
13. V okně vytváření pravidel zopakujte kliknutí na tlačítko "OK" .
14. Otevře se Správce pravidel . Jak vidíte, jsou vytvořena všechna tři pravidla, takže klikněte na tlačítko "OK" .
15. Nyní jsou prvky tabulky v podmínkách podmíněné formátování vyfarbené podle zadaných podmínek a hranic.
16. Pokud změníme obsah v jedné buňce, a tak budeme odcházet mimo některá z uvedených pravidel, pak tento prvek listu automaticky změní barvu.

Kromě toho můžete použít podmíněné formátování poněkud jinak pro barevné prvky listu s barvou.

1. Chcete-li to provést, po přechodu do okna Formátování z správce pravidel zůstaneme v sekci "Formátovat všechny buňky na základě jejich hodnot" . V poli "Barva" můžete vybrat barvu, podle které budou prvky listu stínovány. Poté klikněte na tlačítko "OK" .
2. V Správci pravidel také klikněte na tlačítko "OK" .
3. Jak je vidět, potom jsou buňky ve sloupci zbarveny různými odstíny stejné barvy. Hodnota, která obsahuje listový prvek, je větší, odstín je světlejší, čím menší - tím tmavší.

Lekce: Podmíněné formátování v aplikaci Excel

Metoda 2: Použijte nástroj Najít a vybrat

Pokud tabulka obsahuje statické údaje, které se neplánují měnit v průběhu času, můžete pomocí nástroje změnit barvu buněk podle obsahu nazvaného "Najít a zvýraznit" . Zadaný nástroj vám umožní zjistit zadané hodnoty a změnit barvu v těchto buňkách na požadovaného uživatele. Mějte však na paměti, že když změníte obsah prvků listu, barva se automaticky nezmění, ale zůstane stejná. Chcete-li změnit barvu na aktuální, budete muset zopakovat postup znovu. Tato metoda proto není optimální pro tabulky s dynamickým obsahem.

Podívejme se, jak to funguje na konkrétním příkladu, pro který vezmeme stejnou tabulku příjmů podniku.

1. Vyberte sloupec s údaji, které mají být formátovány barvou. Poté přejděte na kartu "Domov" a klikněte na tlačítko "Najít a vše" , které se nachází na pásu karet v panelu nástrojů "Upravit" . V otevřeném seznamu klikněte na "Najít" .
2. Okno "Najít a nahradit" se otevře na kartě "Najít" . Nejdříve najdeme hodnoty až 400 000 rublů. Vzhledem k tomu, že nemáme jednu buňku o hodnotě nižší než 300 000 rublů, musíme v podstatě vybrat všechny prvky, které obsahují čísla v rozsahu od 300 000 do 400 000 . Tento rozsah můžete bohužel přímo zadat, protože v případě podmíněného formátování nelze tuto metodu použít.

   Ale je tu možnost udělat trochu jinak, což nám dá stejný výsledek. Ve vyhledávacím pruhu můžete nastavit následující vzor "3" . Otazník znamená libovolný znak. Program tedy vyhledá všechna šestimístná čísla začínající číslem "3" . To znamená, že výsledky vyhledávání budou obsahovat hodnoty v rozsahu 300 000 - 400 000 , což je to, co potřebujeme. Pokud by tabulka měla čísla menší než 300 000 nebo méně než 200 000 , pak by pro každý rozsah sto tisíc mělo být vyhledávání provedeno odděleně.

   Do pole "Najít" zadejte výraz "3 ?????" a klikněte na tlačítko "Najít vše ".

3. Potom se v dolní části okna zobrazí výsledky vyhledávání. Klepněte na levé tlačítko myši na kterékoli z nich. Potom zadejte Ctrl + A. Poté jsou zvýrazněny všechny výsledky vyhledávacího výstupu a současně jsou zvýrazněny prvky ve sloupci, ke kterému jsou tyto výsledky odkazovány.
4. Po zvýraznění prvků ve sloupci nezapomeňte zavřít okno "Najít a nahradit" . Zatímco na kartě "Domov", ve které jsme se přestěhovali dříve, přejděte na pásek do pole "Font" . Klikněte na trojúhelník napravo od tlačítka "Plnit barvu" . Otevře se výběr různých barev výplně. Vyberte barvu, kterou chceme použít na prvky listu, obsahující hodnoty menší než 400 000 rublů.
5. Jak je vidět, všechny buňky ve sloupci, ve kterých jsou hodnoty menší než 400 000 rublů, jsou zvýrazněny vybranou barvou.
6. Nyní je třeba namalovat prvky, jejichž hodnoty se pohybují od 400 000 do 500 000 rublů. Tento rozsah zahrnuje čísla, která odpovídají vzoru "4" . Do vyhledávacího pole je přemístíme a klikneme na tlačítko "Najít vše" , které předtím vybralo sloupec, který potřebujeme.
7. Stejně tak, s předchozím časem ve výsledku vyhledávání, vybereme veškerý výsledek získaný stisknutím klávesové zkratky CTRL + A. Potom přejděte na ikonu výběru barvy výplně. Klikněte na ni a klikněte na ikonu požadovaného odstínu, který bude barvit prvky listu, kde se hodnoty pohybují od 400 000 do 500 000 .
8. Jak vidíte, po této akci jsou všechny prvky tabulky s údaji v intervalu od 400000 do 500000 zvýrazněny vybranou barvou.
9. Nyní musíme přidělit poslední interval hodnot - více než 500 000 . Zde máme také štěstí, protože všechna čísla přes 500 000 jsou v rozmezí od 500 000 do 600 000 . Proto ve vyhledávacím poli zadejte výraz "5 ?????" a klikněte na tlačítko "Najít vše" . Pokud by existovaly hodnoty přesahující 600 000 , pak bychom museli navíc hledat výraz "6 ?????" atd.
10. Znovu vyberte výsledky hledání pomocí kombinace Ctrl + A. Poté pomocí tlačítka na pásu karet vybereme novou barvu pro vyplnění intervalu přesahující 500 000 stejnou analogií jako předtím.
11. Jak vidíte, po této akci budou všechny prvky sloupce stínovány podle číselné hodnoty, která je v nich umístěna. Nyní můžete zavřít vyhledávací okno kliknutím na standardní tlačítko zavření v pravém horním rohu okna, protože náš úkol může být považován za vyřešen.
12. Pokud však změníme číslo na jinou, která přesahuje hranice, které jsou nastaveny pro určitou barvu, pak se barva nezmění, jak tomu bylo u předchozí metody. To znamená, že tato možnost bude fungovat spolehlivě pouze v těch tabulkách, ve kterých se data nemění.

Lekce: Jak provést vyhledávání v aplikaci Excel

Jak můžete vidět, existují dva způsoby, jak barvit buňky v závislosti na číselných hodnotách, které obsahují: pomocí podmíněného formátování a pomocí nástroje Najít a nahradit . První metoda je progresivnější, protože umožňuje jasněji definovat podmínky, na které budou listové prvky zvýrazněny. Navíc s podmíněným formátováním se barva prvku automaticky změní, pokud se obsah změní, což druhý způsob nedokáže. Je však také možné použít plnění buněk v závislosti na hodnotě pomocí nástroje Najít a nahradit , ale pouze v statických tabulkách.

• ← Převést všechny písmena na velká písmena v aplikaci Microsoft Excel
• Funkce INDIRECT v aplikaci Microsoft Excel →