S příkazem "jít" můžete rychle najít a vybrat všechny buňky, které obsahují data určitého typu, jako jsou vzorce. Kromě toho můžete najít pouze ty buňky, které odpovídají jisté podmínky(např. poslední buňka v listu obsahující data nebo formátování, použijte příkaz Jdi).

Postupujte podle níže uvedených kroků.

tým	Zvýraznit
Poznámky
Konstanty
Vzorce	Poznámka: Pomocí zaškrtávacích políček níže vzorce je určen typ vzorce.
prázdný	Prázdné buňky.
aktuální oblast	aktuální rozsah, například celý seznam.
aktuální pole	celé pole, pokud je aktivní buňka obsažena v poli.
Objekty	Grafické objekty, včetně grafů a tlačítek, na listu a v textových polích.
Rozdíly mezi strunami	Všechny buňky, které se liší od aktivní buňky ve vybraném řádku. Ve vybrané oblasti je vždy jedna aktivní buňka - je to rozsah, řádek nebo sloupec. Stisknutím klávesy ENTER nebo TAB můžete změnit umístění aktivní buňky, která je ve výchozím nastavení první buňkou v řádku. Pokud je vybráno více než jeden řádek, porovnání se provede pro každý jednotlivý řádek výběru a buňka použitá v porovnání pro každý další řádek je ve stejném sloupci jako aktivní buňka.
Rozdíly mezi sloupci	Všechny buňky, které se liší od aktivní buňky ve vybraném sloupci. Výběr má vždy aktivní buňku, ať už se jedná o rozsah, řádek nebo sloupec. Stisknutím klávesy ENTER nebo TAB můžete změnit umístění aktivní buňky, která je ve výchozím nastavení první buňkou ve sloupci. Pokud vyberete více než jeden sloupec, porovnání se provede pro každý jednotlivý sloupec výběru. Buňka použitá v porovnání pro každý další sloupec je ve stejném řádku jako aktivní buňka.
Ovlivňování	Buňky, na které odkazuje vzorec v aktivní buňce. V kapitole závislý položky, proveďte jednu z následujících akcí: Klikněte pouze rovné čáry najít pouze buňky, na které vzorec odkazuje. Klikněte všechny úrovně najít všechny buňky přímo nebo nepřímo odkazované buňkami ve výběru.
Závislý	Buňky se vzorci, které odkazují na aktivní buňka. Proveďte jednu z následujících akcí: Klikněte pouze rovné čáry najít pouze buňky se vzorci, které přímo odkazují na aktivní buňku. Klikněte všechny úrovně najít všechny buňky, které přímo nebo nepřímo odkazují na aktivní buňku.
poslední buňka	Poslední buňka na listu, která obsahuje data nebo formátování.
Pouze viditelné buňky	Viditelné pouze buňky v rozsahu, který se protíná se skrytými řádky a sloupci.
Podmíněné formáty	Pouze buňky, které mají aplikované podmíněné formátování. V kapitole Ověření dat proveďte jednu z následujících akcí: Klepněte na tlačítko Všechno najít všechny buňky, které mají aplikované podmíněné formáty. Chcete-li vyhledat buňky se stejným podmíněným formátováním zvýrazněným v aktuální buňce, klikněte jeden z nich.
Ověření dat	Použijí se pouze buňky, které obsahují pravidla ověření dat. Proveďte jednu z následujících akcí:

Zjistěte, jak na to Excelové listy rychle změnit barvu celého řádku v závislosti na hodnotě jedné buňky. Podívejte se na triky a příklady vzorců pro číselné a textové hodnoty.

V jednom z předchozích článků jsme se zabývali tím, jak změnit barvu buňky v závislosti na její hodnotě. Tentokrát si povíme, jak v Excelu 2010 a 2013 zvýraznit celý řádek v závislosti na hodnotě jedné buňky a také prozradíme pár triků a ukážeme si příklady vzorců pro práci s číselnými a textovými hodnotami.

Jak změnit barvu řádku na základě číselné hodnoty jedné z buněk

Předpokládejme, že máme následující tabulku firemních objednávek:

Chceme obarvit řádky různými barvami v závislosti na objednaném množství (hodnota ve sloupci množství) pro zvýraznění nejdůležitějších zakázek. Nástroj Excel nám pomůže vyrovnat se s tímto úkolem - " Podmíněné formátování».

Jak vidíte, změna barvy celého řádku v Excelu na základě číselné hodnoty jedné z buněk není vůbec složitá. Dále se podíváme na pár dalších příkladů vzorců a pár triků pro řešení složitějších problémů.

Jak vytvořit více pravidel podmíněného formátování s danou prioritou

V tabulce z předchozího příkladu by pravděpodobně bylo vhodnější použít různé barvy výplně pro zvýraznění řádků obsahujících sloupec množství různé významy. Vytvořte například další pravidlo podmíněného formátování pro řetězce obsahující hodnotu 10 nebo více a zvýrazněte je růžovou barvou. K tomu potřebujeme vzorec:

Aby obě pravidla, která jsme vytvořili, fungovala současně, musíte je seřadit podle priority.

Jak změnit barvu řádku na základě textové hodnoty jedné z buněk

Pro zjednodušení kontroly plnění objednávek můžeme v naší tabulce zvýraznit různými barvami řádky objednávek s různým stavem doručení, informace o nich jsou obsaženy ve sloupci dodávka:

• Pokud je datum dodání objednávky v budoucnosti (hodnota Splatnost za X dní), pak by výplň takových buněk měla být oranžová;
• Pokud byla objednávka doručena (hodnota Doručeno), pak by výplň takových buněk měla být zelená;
• Pokud je datum dodání objednávky v minulosti (hodnota Po splatnosti), pak by výplň takových buněk měla být červená.

A samozřejmě by se měla změnit barva výplně buňky, pokud se změní stav objednávky.

Se vzorcem pro hodnoty Doručeno a Po splatnosti vše je jasné, bude to podobné vzorci z našeho prvního příkladu:

=$E2="Doručeno"
=$E2="Po termínu"

Úkol zní obtížněji pro objednávky, které musí být doručeny X dny (hodnota Splatnost za X dní). Vidíme, že dodací lhůta u různých objednávek je 1, 3, 5 nebo více dnů, což znamená, že výše uvedený vzorec zde neplatí, protože cílí na přesnou hodnotu.

V tomto případě je vhodné funkci použít VYHLEDÁVÁNÍ(HLEDAT) a pro nalezení částečné shody napište následující vzorec:

SEARCH("Splatnost za";$E2)>0
=SEARCH("Splatnost za",$E2)>0

V tomto vzorci E2- toto je adresa buňky, na základě jejíž hodnoty použijeme pravidlo podmíněného formátování; znak dolaru $ potřebné k aplikaci vzorce na celý řádek; podmínka" >0 ” znamená, že pravidlo formátování bude použito, pokud bude nalezen zadaný text (v našem případě „Splatné do“).

Vodítko: Pokud vzorec používá podmínku „ >0 “, pak bude řádek barevně zvýrazněn pokaždé, když bude zadaný text nalezen v klíčové buňce, bez ohledu na to, kde se v buňce nachází. V ukázkové tabulce na obrázku níže sloupec dodávka(sloupec F) může obsahovat text „Urgentní, splatný do 6 hodin“ (což znamená Urgentní, doručit do 6 hodin) a tento řádek bude také barevný.

Aby bylo možné barevně zvýraznit ty řádky, ve kterých obsah klíčové buňky začíná zadaným textem nebo symboly, musí být vzorec napsán v následujícím tvaru:

SEARCH("Splatnost za";$E2)=1
=SEARCH("Splatnost za",$E2)=1

Při použití takového vzorce musíte být velmi opatrní a zkontrolovat, zda buňky klíčového sloupce dat začínají mezerou. V opačném případě si můžete po dlouhou dobu lámat hlavu a snažit se pochopit, proč vzorec nefunguje.

Podle stejných kroků jako v , jsme vytvořili tři pravidla formátování a naše tabulka začala vypadat takto:

Jak změnit barvu buňky na základě hodnoty jiné buňky

Ve skutečnosti jde o speciální případ. Místo celé tabulky vyberte sloupec nebo oblast, ve které chcete změnit barvu buněk, a použijte výše popsané vzorce.

Můžeme například nastavit tři naše pravidla tak, aby byly barevně zvýrazněny pouze buňky obsahující číslo objednávky (sloupec číslo objednávky) na základě hodnoty jiné buňky v tomto řádku (pomocí hodnot ze sloupce dodávka).

Jak nastavit více podmínek pro změnu barvy řádku

Pokud potřebujete zvýraznit řádky stejnou barvou, když se objeví jedna z několika různých hodnot, můžete místo vytváření několika pravidel formátování použít funkce A(a) NEBO(OR) a spojit tak několik podmínek do jednoho pravidla.

Například růžově můžeme označit objednávky očekávané do 1 a 3 dnů a žlutě ty splatné do 5 a 7 dnů. Vzorce budou vypadat takto:

NEBO($F2="Splatnost za 1 den";$F2="Splatnost za 3 dny")
=OR($F2="Splatnost za 1 den",$F2="Splatnost za 3 dny")

NEBO($F2="Splatnost za 5 dní";$F2="Splatnost za 7 dní")
=OR($F2="Splatnost za 5 dní",$F2="Splatnost za 7 dní")

Aby bylo možné zvýraznit objednávky s množstvím alespoň 5, ale maximálně 10 (hodnota ve sloupci množství), zapíšeme vzorec s funkcí A(A):

AND($D2>=5;$D2<=10)
=AND($D2>=5,$D2<=10)

Samozřejmě, že ve vzorcích můžete použít ne nutně dvě, ale tolik podmínek, kolik je potřeba. Například:

NEBO($F2="Splatnost za 1 den";$F2="Splatnost za 3 dny";$F2="Splatnost za 5 dní")
=OR($F2="Splatnost za 1 den",$F2="Splatnost za 3 dny",$F2="Splatnost za 5 dní")

Vodítko: Nyní, když jste se naučili vybarvovat buňky různými barvami v závislosti na hodnotách, které obsahují, možná budete chtít zjistit, kolik buněk je zvýrazněno určitou barvou, a vypočítat součet hodnot v těchto buňkách. . Chci vás potěšit, tuto akci lze provést i automaticky a řešení tohoto problému si ukážeme v článku na otázku Jak vypočítat množství, množství a nastavit filtr pro buňky určité barvy v Excelu.

Ukázali jsme jen několik možných způsobů, jak vytvořit tabulku, aby vypadala jako pruh zebry, jehož barva závisí na hodnotách v buňkách a může se měnit spolu se změnou těchto hodnot. Pokud hledáte pro svá data něco jiného, ​​dejte nám vědět a společně něco určitě vymyslíme.

Potřebujete zvýraznit duplicitní hodnoty ve sloupci? Potřebujete vybrat prvních 5 maximálních buněk? Je nutné pro názornost udělat tepelnou stupnici (barva se mění v závislosti na nárůstu / poklesu hodnoty buněk)? V Excelu lze zvýraznění buněk podle podmínek provést velmi rychle a snadno. Speciální funkce "Podmíněné formátování" je zodpovědná za zvýraznění buněk barvou. Vřele doporučuji! Přečtěte si více na:

Hlavní vlastnosti jsem popsal na začátku článku, ale ve skutečnosti je jich hodně. Více o nejužitečnějších

Chcete-li začít, na hlavním panelu v hlavní nabídce najděte část Styly a klikněte na tlačítko Podmíněné formátování.

Po kliknutí se otevře nabídka s různými možnostmi pro tuto úpravu. Jak vidíte, příležitostí je zde opravdu hodně.

Nyní více o nejužitečnějších:

Excel zvýraznění buněk podle podmínek. Jednoduché termíny

Chcete-li to provést, přejděte na položku Pravidla výběru buněk. Pokud například potřebujete vybrat všechny buňky větší než 100, klikněte na tlačítko Více. V okně:

ve výchozím nastavení jsou podmínky navrženy tak, aby byly zvýrazněny červeně, ale požadované formátování buněk můžete nastavit kliknutím do pravého pole a výběrem požadované možnosti.

Zvýraznění duplicitních hodnot, vč. přes více sloupců

Chcete-li zvýraznit všechny duplicitní hodnoty, vyberte příslušnou položku nabídky Duplicitní hodnoty.

Co dělat, když potřebujete najít opakování ve dvou nebo více sloupcích, například když je celé jméno v různých sloupcích? Vytvořte další sloupec a zkombinujte hodnoty se vzorcem = , tj. v samostatné buňce budete mít napsáno IvanovIvanIvanych. Takovým sloupcem již snadno zvýrazníte duplicitní hodnoty. Je důležité pochopit, že pokud se slovosled liší, Excel bude takové řádky považovat za neopakující se (například Ivan Ivanych Ivanov).

Barevné zvýraznění první/poslední hodnoty. Opět podmíněné formátování

Chcete-li to provést, přejděte na Pravidla pro výběr první a poslední buňky a vyberte požadovanou položku. Kromě toho, že můžete zvýraznit první / poslední hodnoty (včetně procent), můžete využít možnost zvýraznění dat nad a pod průměrem (používám ji ještě častěji). Velmi užitečné pro prohlížení výsledků, které vybočují z normy nebo průměru!

Konstrukce tepelného diagramu a histogramu

Skvělou funkcí pro vizualizaci dat je teplotní/teplotní graf. Podstatou je, že v závislosti na hodnotě hodnoty ve sloupci nebo řádku je buňka zvýrazněna určitým odstínem barvy, čím více, tím například červenější. Tabulky jsou mnohem lépe vnímány okem a rozhodování je jednodušší. Jedním z nejlepších analyzátorů je často naše oko, respektive mozek, a ne stroj!

Histogram buněk (na obrázku níže v modré barvě) je také velmi užitečná funkce pro detekci změn hodnot a jejich porovnávání.

Zvýrazněte buňky obsahující konkrétní text

Velmi často potřebujete najít buňky, které obsahují určitou sadu znaků, můžete samozřejmě použít funkci =, ale jednodušší a rychlejší je použít podmíněné formátování, projít - Pravidla výběru buněk - Text obsahuje

Velmi užitečné při práci s textem. Příklad, kdy máte ve sloupci celá jména zaměstnanců, ale potřebujete vybrat všechny kolegy Ivanovů. Vyberte buňky, přejděte na požadovanou položku a vyberte obsahující text Ivanov, poté filtrujeme tabulku podle barvy

Excel zvýraznění. Filtrujte podle barvy

Kromě výše uvedených možností můžete vybrané buňky filtrovat podle barvy pomocí běžného filtru. K mému překvapení o tom ví jen velmi málo lidí - zřejmě ozvěny verze z roku 2003 - tato funkce tam nebyla.

Kontrola podmínek formátování

Chcete-li zkontrolovat, jaké podmíněné formátování jste nastavili, přejděte na Domů - Podmíněné formátování - Spravovat pravidla. Zde můžete upravovat již nastavené podmínky, rozsah použití a také zvolit prioritu zadaného formátování (kdo je vyšší, je důležitější, to můžete změnit tlačítky se šipkami).

Neplatný rozsah podmíněného formátování

Důležité! Podmíněné formátování je při nesprávném použití často příčinou silného . Dochází ke zdvojnásobení formátování, např. při mnohonásobném kopírování buněk se zvýrazněním. Pak budete mít mnoho podmínek s barvou. Sám jsem viděl více než 3 tisíce podmínek - soubor se ošklivě zpomalil. Soubor se také může zpomalit, když je rozsah nastaven jako na obrázku výše, je lepší zadat A: A - pro celý rozsah.

Přečtěte si více o brzdách Excel a jejich příčinách. Tento článek pomohl více než stovce lidí ;)

Doufám, že to bylo užitečné, omlouvám se!

Sdílejte náš článek na svých sociálních sítích:

Ke splnění tohoto úkolu využijeme možnosti podmíněného formátování.
Vezměme si tabulku obsahující seznam objednávek, jejich termíny, aktuální stav a cenu. Zkusme, aby se jeho buňky vybarvily samy v závislosti na jejich obsahu.

Pokyny pro Excel 2010

ZAPNOUT TITULKY!

Jak to udělat v Excelu 2007

ZAPNOUT TITULKY!
Vyberte buňky s cenami objednávky a kliknutím na šipku vedle tlačítka „Podmíněné formátování“ vyberte „Vytvořit pravidlo“.

Vyberme čtvrtou položku, která nám umožňuje porovnat aktuální hodnoty s průměrem. Zajímají nás nadprůměrné hodnoty. Kliknutím na tlačítko "Formát" nastavte barvu buněk.


Potvrzujeme naši volbu a buňky s nadprůměrnou cenou zmodraly a upozornily nás na drahé zakázky.


Vyberte buňky se stavy objednávky a vytvořte nové pravidlo. Tentokrát využíváme druhou možnost, která umožňuje kontrolu obsahu buňky. Vyberte „Text“, „obsahuje“ a zadejte slovo „Dokončeno“. Nastavíme zelenou barvu, potvrdíme a dokončená práce se změní na zelenou.


No, pojďme udělat ještě jedno pravidlo, které maluje zakázky po splatnosti červeně. Vybíráme termíny realizace zakázek. Při vytváření pravidla opět vybereme druhou položku, ale tentokrát nastavíme "Hodnota buňky", "menší než" a do dalšího pole zadáme funkci, která vrátí dnešní datum.


„OK“ a dostali jsme vesele vyzdobený stůl, který umožňuje vizuálně sledovat průběh objednávek.


Všimli jste si, že stavy se nastavují výběrem z rozevíracího seznamu hodnot? Jak vytvořit takové seznamy, řekli jsme v pokynech.

Jak to udělat v Excelu 2003

ZAPNOUT TITULKY!
"Podmíněné formátování" v nabídce "Formát". To bude vyžadovat trochu více ruční práce. Takto bude vypadat nastavení pro náš první úkol - vyplnit buňky nadprůměrnými hodnotami.


Budete muset ručně zadat funkci "=PRŮMĚR()", umístit kurzor do hranatých závorek, kliknout na tlačítko vedle a pomocí myši vybrat požadovaný rozsah.
Ale princip akce je stejný.
Podmaňte si Excel a brzy se uvidíme!

Chcete-li správně umístit akcenty v dokumentu vytvořeném v MS Excel, můžete zvětšit velikost buněk, vybrat jiné písmo nebo velikost písmen nebo můžete přemalovat čtverce, na které je určitě třeba věnovat pozornost.

Nyní zjistíme, jak zvýraznit buňky barvou v Excelu nebo změnit barvu těch, které jsou již přebarveny. Jak docílit toho, aby buňka měnila barvu podle dané podmínky v závislosti na hodnotě do ní zadané a jak pracovat s vytvořenými pravidly.

Jednoduchá výplň bloku

Vybarvení jednoho nebo více bloků v Excelu není obtížné. Nejprve je vyberte a na kartě Domů kliknutím na šipku vedle nádoby s barvou rozbalte seznam. Odtud si vyberte vhodnou barvu, a pokud vám nic nevyhovuje, klikněte na „Další barvy“.

Mimochodem, tímto způsobem můžete vyplnit celý řádek, stačí nejprve kliknout na jeho číslo a vybrat jej. O výběru buněk v Excelu si můžete přečíst samostatný článek.

Pokud pracujete s tabulkou, ve které je již něco přemalováno, můžete změnit barvu bloků nebo ji dokonce úplně odstranit. Klikněte na něj a z barev, nebo vyberte novou, nebo klikněte na možnost „Žádné“.

V závislosti na zadaných údajích

Nyní se podíváme na to, jak změnit barvu buňky na základě dané podmínky. K tomu slouží podmíněné formátování, o kterém je na webu samostatný článek.

Text

Vezměme si jako příklad následující tabulku. Udělejme to tak, aby červená odpovídala jablku, žlutá hruškě a oranžová oranžové.

Vybíráme data, se kterými budeme pracovat, v mém případě jsou to názvy plodů. Poté přitlačíme "Podmíněné formátování", kterou budeme používat i v budoucnu. V seznamu klikněte na "Vytvořit pravidlo".

Toto okno se otevře. Vyberte typ nahoře "Formátovat pouze buňky, které obsahují", dále to také oslavíme. Níže uvádíme podmínky: máme text, který obsahuje určitá slova. V posledním poli buď klikněte na tlačítko a určete buňku, nebo zadejte text.

Rozdíl je v tom, že vložením odkazu na buňku (=$B$4) se podmínka změní v závislosti na tom, co je v ní napsáno. Například místo jablka v B4 označím rybíz, pravidlo se podle toho změní a bloky se stejným textem se přemalují. A pokud do pole zadáte jablko, bude se hledat toto konkrétní slovo a nebude na ničem záviset.

Zde vyberte barvu výplně a klikněte na OK. Chcete-li zobrazit všechny možnosti, klikněte na tlačítko "Jiné".

Pravidlo bylo vytvořeno a uložte jej stisknutím tlačítka OK.

V důsledku toho byly všechny bloky, které obsahovaly zadaný text, natřeny červenou barvou.

Pravidla jsou vytvořena i pro jiné ovoce, pouze je vybrána jiná možnost plnění.

Numerický

Nyní se pojďme zabývat číselnými údaji. Položme čísla ve sloupci D na určité pozadí podle podmínky, kterou si nastavíme.

Vyberte sloupec, vytvořte pravidlo, určete jeho typ. Dále napíšeme - „Hodnota“ „větší než“ „15“. Můžete buď zadat poslední číslo ručně, nebo zadat adresu buňky, ze které se budou data brát. Rozhodneme se pro výplň, klikněte na "OK".

Bloky, do kterých jsou zadána čísla větší než vybraná, jsou přemalovány.

Upřesníme více pravidel pro vybrané buňky - vyberte "Správa pravidel".

Zde si vyberte vše, jak jsem popsal výše, stačí změnit barvu a nastavit podmínku "méně nebo stejně".

Když je vše připraveno, klikněte na „Použít“ a „OK“.

Vše funguje, hodnoty rovné a nižší než 15 jsou vybarveny bledě modrou barvou.

Zde by byl také jasně vidět rozdíl mezi dováženým a prodávaným zbožím. Vyberte dva sloupce a klikněte "Pravidla pro výběr buněk" – "Duplicitní hodnoty". Vyberte si správnou barvu. Pokud se poté vybarví dvě buňky vedle sebe, znamená to, že dovezené ovoce bylo všechny prodáno.

Na příkladu posledního sloupce si ukážeme, jak docílit změny barvy buňky v závislosti na zadané hodnotě. Vyberte je a přejděte na "Správa pravidel".

Vytvoříme nový pro aktuální fragment, v případě potřeby v tomto rozevíracím seznamu můžete vybrat buď pro celý list, nebo pro další listy.

V okně, které se otevře, vyberte požadované položky. Tmavě zelenou vyplním všechny hodnoty, které jsou větší než 90. Jelikož jsem adresu zadal do posledního pole (=$F$15 ), když se číslo 90 v buňce změní např. na 110, pravidlo se také změnit. Uložte změny kliknutím na tlačítko OK.

Vytvořím další pravidlo, ale v něm světle zeleně zvýrazním vše, co je menší nebo rovno 90. Nezapomeňte si vše uložit kliknutím na tlačítka vpravo dole.

Tady je to, co se stalo. Z posledního sloupce můžete vizuálně rychle určit zisk z prodeje toho kterého produktu byl větší.

Pokud se například změní cena, dojde také k revizi pravidel. Pokud bude hodnota větší nebo menší než zadaná hodnota, barva buňky se automaticky změní.

Myslím, že jste si všimli, že pravidlo se vytváří nejen pro text a čísla, ale také pro data a v závislosti na vyplnění a přítomnosti chyb v buňkách. Zadejte podmínku, vyberte barvu a uložte.

Chcete-li zobrazit, co jste přidali, vyberte rozsah a v okně "Správa pravidel" bude kompletní seznam. Pomocí tlačítek v horní části je můžete přidat, změnit nebo odstranit.