Farebné bunky podľa stavu. Ako nastaviť farbu výplne bunky. Excel zvýraznenie buniek podľa podmienok. Jednoduché pojmy

AT excel Existujú užitočné funkcie, o ktorých mnohí nevedia. Jednou z nich je funkcia podmieneného formátovania, ktorá automaticky aplikuje určitý formát, napríklad červené sfarbenie buniek, ktoré spĺňajú určitú podmienku nastavenú používateľom.

Tu vám povieme ako nastaviť podmienky v exceli aby sa potom farba buniek automaticky zhodovala.

Podmienky pre tieňovanie buniek Excelu vo Windows

Farbenie buniek za podmienok je veľmi jednoduché. Vyberte bunky, stĺpce alebo riadky, ktorých farbu chcete zmeniť. Potom na karte Domov prejdite do menu Štýly a vyberte si Podmienené formátovanie > Podmienky pre výber bunky. Vyberte si stav, ktorý vám vyhovuje. Zadajte referenčnú hodnotu, otvorte rozbaľovaciu ponuku a vyberte Vlastný formát. Kliknite na kartu vyplniť a vyberte farbu bunky. Nakoniec kliknite OK.

Podmienky pre tieňovanie buniek Excelu na Macu

Na počítačoch Mac funguje výber buniek podľa podmienok - presne to isté. Jediným rozdielom je širšia škála možností nastavenia podmienok.

Obrázok: © Mika Baumeister – Unsplash.com

Človek je tak usporiadaný, že farby vníma lepšie ako holé čísla – nie nadarmo sú tie najdôležitejšie úseky diagramov či riadkov tabuľky, ktoré stoja za pozornosť, zvyčajne zvýraznené kontrastnou farbou. Ručné označovanie buniek tabuľky „dobré“ a „zlé“ v Exceli však nie je práve najzábavnejšia činnosť. Prial by som si, aby to MS Excel dokázal sám a dokonca automaticky zmenil práve tieto farby, ak sa situácia zmení ...

Ale on to už dokonale zvláda – ty a ja mu môžeme len trochu pomôcť!

Poďme vyriešiť takýto aplikovaný problém: v našej tabuľke "ovocie" je hmotnosť jednej alebo druhej položky uvedená v kilogramoch. Aby sme uľahčili orientáciu v tom, čo nám chýba a čoho je naopak nadbytok, zafarbíme všetky hodnoty menšie ako 20 červenou farbou a všetko nad 50 zelenou farbou. V tomto prípade všetko, čo zostane v tomto rozsahu, nebude vôbec označené farbou. A aby sme úlohu skomplikovali, poďme ešte ďalej a urobme priradenie farieb dynamické – keď sa zmení hodnota v zodpovedajúcej bunke, zmení sa aj jej farba.

Najprv vyberte rozsah údajov, teda obsah druhého stĺpca tabuľky MS Excel, a potom prejdite na „ Domov", kde v skupine "Štýly" aktivujeme nástroj " Podmienené formátovanie“ a v rozbaľovacom zozname vyberte možnosť „ Vytvoriť pravidlo«.

V zobrazenom okne Vytvoriť pravidlo formátovania vyberte typ pravidla: „Formátovať iba bunky, ktoré obsahujú“ a v konštruktore nižšie nastavte parametre: „Hodnota bunky“, „Menej ako“ a ručne zadajte náš „okraj“: číslo 20.

Kliknite na tlačidlo „Formátovať“ nižšie, prejdite na kartu „Vyplniť“ a vyberte červenú farbu. Stlačíme "OK".

Poznámka: Bunky môžete samozrejme označiť nie farbou pozadia, ale napríklad veľkosťou alebo farbou písma - na to musíte použiť príslušné karty v tom istom okne.

Pozrite sa na tabuľku - očividne nám zostalo veľmi málo jabĺk a mandarínok, je čas urobiť nový nákup!

Skvelé, údaje sú už farebne zvýraznené!

Analogicky teraz vytvoríme ďalšie pravidlo - tentokrát s parametrami "Cell Value", "Greater", 20. Nastavte farbu výplne na zelenú. Pripravený.

Zdalo sa mi, že to nestačí – čierny text na červenom a zelenom pozadí nie je čitateľný, tak som sa rozhodol naše pravidlá trochu ozdobiť a zmeniť farbu textu na bielu. Ak to chcete urobiť, otvorte nástroj Podmienené formátovanie, ale vyberte nie "Vytvoriť pravidlo", ale " Správa pravidiel“, nižšie.

V zobrazenom okne postupne vyberte obe pravidlá a kliknite na tlačidlo " Zmeniť", po ktorom v už známom okne " Formátovať» prejdite na kartu « Písmo“ a zmeňte farbu textu na bielu. Tu je to, s čím som skončil:

Teraz som zmenil nielen pozadie buniek tabuľky, ale aj farbu písma

Skúsime zmeniť „zlé“ hodnoty na „dobré“? Raz a hotovo - farba sa automaticky zmenila, akonáhle zodpovedajúce bunky mali hodnoty, na ktoré sa vzťahuje jedno z pravidiel.

Meníme hodnoty v našej excelovej tabuľke ... všetko funguje!

Na splnenie tejto úlohy využijeme možnosti podmieneného formátovania.
Zoberme si tabuľku obsahujúcu zoznam objednávok, ich termíny, aktuálny stav a náklady. Skúsme, aby sa jeho bunky zafarbili samy v závislosti od ich obsahu.

Pokyny pre Excel 2010


ZAPNITE TITULKY!

Ako to urobiť v Exceli 2007


ZAPNITE TITULKY!
Vyberte bunky s cenami objednávky a kliknutím na šípku vedľa tlačidla „Podmienené formátovanie“ vyberte „Vytvoriť pravidlo“.

Vyberme si štvrtú položku, ktorá nám umožňuje porovnať aktuálne hodnoty s priemerom. Zaujímajú nás hodnoty nad priemerom. Kliknutím na tlačidlo "Formátovať" nastavte farbu buniek.


Potvrdíme náš výber a bunky s nadpriemernou cenou sa zmenili na modré, čím nás upozornili na drahé objednávky.


Vyberte bunky so stavmi objednávky a vytvorte nové pravidlo. Tentokrát využívame druhú možnosť, ktorá umožňuje kontrolovať obsah bunky. Vyberte "Text", "obsahuje" a zadajte slovo "Dokončené". Nastavíme zelenú farbu, potvrdíme a dokončená práca sa zmení na zelenú.


No, urobme ešte jedno pravidlo, ktoré maľuje zákazky po termíne na červeno. Vyberáme termíny realizácie objednávok. Pri vytváraní pravidla opäť vyberieme druhú položku, tentokrát však nastavíme „Hodnota bunky“, „menej ako“ a do ďalšieho poľa zadáme funkciu, ktorá vráti dnešný dátum.


„OK“ a dostali sme veselo vyzdobený stôl, ktorý vám umožňuje vizuálne sledovať priebeh objednávok.


Všimli ste si, že stavy sa nastavujú výberom z rozbaľovacieho zoznamu hodnôt? Ako vytvoriť takéto zoznamy, povedali sme v pokynoch.

Ako to urobiť v Exceli 2003


ZAPNITE TITULKY!
"Podmienené formátovanie" v ponuke "Formát". To bude vyžadovať trochu viac ručnej práce. Takto budú vyzerať nastavenia pre našu prvú úlohu - vyplniť bunky nadpriemernými hodnotami.


Budete musieť manuálne zadať funkciu "=AVERAGE()", umiestniť kurzor medzi zátvorky, kliknúť na tlačidlo vedľa a pomocou myši vybrať požadovaný rozsah.
Ale princíp činnosti je rovnaký.
Dobite Excel a čoskoro sa uvidíme!

Nie všetky firmy nakupujú špeciálne programy podnikať. Mnoho ľudí používa MS Excel, pretože tento je vhodný pre veľké informačné základne. Prax ukázala, že to len zriedka presahuje plnenie tabuliek. Tabuľka sa rozrastá, pribúdajú informácie a je potrebné rýchlo vybrať len tú, ktorú potrebujete. V takejto situácii vyvstáva otázka, ako za určitých podmienok zvýrazniť bunku v Exceli farbou, aplikovať farebné prechody na riadky v závislosti od typu alebo názvu dodávateľa, urobiť prácu s informáciami rýchlou a pohodlnou? Prečítajte si viac nižšie.

Kde je podmienené formátovanie

Ako zmeniť farbu bunky v Exceli v závislosti od hodnoty je veľmi jednoduché a rýchle. Na zvýraznenie buniek farbou je k dispozícii špeciálna funkcia „Podmienené formátovanie“, ktorá sa nachádza na karte „Domov“:

Podmienené formátovanie zahŕňa štandardnú sadu poskytovaných pravidiel a nástrojov. Ale čo je najdôležitejšie, vývojár dal používateľovi príležitosť vymyslieť a prispôsobiť potrebný algoritmus. Pozrime sa na spôsoby formátovania podrobne.


Pravidlá výberu buniek


Pomocou tejto sady nástrojov sa vyberajú tieto možnosti:

  • nájdite v tabuľke číselné hodnoty, ktoré sú väčšie ako nastavená hodnota;
  • nájsť hodnoty, ktoré sú menšie ako nastavená hodnota;
  • nájsť čísla v danom intervale;
  • určiť hodnoty rovné podmienenému číslu;
  • označte vo vybraných textových poliach len tie, ktoré sú potrebné;
  • označte stĺpce a čísla pre požadovaný dátum;
  • nájsť duplicitné textové alebo číselné hodnoty;
  • prísť s pravidlami, ktoré používateľ potrebuje.

Pozrite sa, ako sa vyhľadáva vybraný text: v prvom poli je zadaná podmienka a v druhom sa uvádza, ako sa má zvýrazniť výsledok. Upozorňujeme, že farbu pozadia a textu si môžete vybrať z ponuky v zozname. Ak chcete použiť iné odtiene, môžete to urobiť tak, že prejdete na "Vlastný formát". Všetky "Pravidlá pre výber buniek" sú implementované podobným spôsobom.


„Iné pravidlá“ sú implementované veľmi kreatívne: v šiestich scenároch vymyslite tie, ktoré sú pre prácu najvhodnejšie, napríklad gradient:


Nastavte farebné kombinácie pre minimálne, priemerné a maximálne hodnoty - na výstupe získate gradientné zafarbenie hodnôt. Pri analýze informácií je pohodlné používať gradient.

Pravidlá pre výber prvej a poslednej hodnoty.

Uvažujme o druhej skupine funkcií "Pravidlá pre výber prvej a poslednej hodnoty". V ňom budete môcť:

  • zvýraznite prvý alebo posledný N-tý počet buniek farbou;
  • použiť formátovanie na dané percento buniek;
  • vyberte bunky obsahujúce hodnotu nad alebo pod priemerom v poli;
  • v záložke "Iné pravidlá" nastavte potrebnú funkcionalitu.

Histogramy

Ak vám vyplnenie bunky farbou nevyhovuje, použite nástroj Histogram. Navrhované sfarbenie je ľahšie vnímateľné okom vo veľkom množstve informácií, funkčné pravidlá sú prispôsobené požiadavkám užívateľa.


Farebné škály

Tento nástroj rýchlo generuje gradientovú výplň indikátorov podľa výberu z väčších na menšie alebo naopak. Pri práci s ním sa nastavia potrebné percentá alebo textové hodnoty. K dispozícii sú vopred pripravené vzorky gradientov, ale vlastný prístup je opäť implementovaný v časti „Iné pravidlá“.


Sady ikon

Ak ste milovníkom emotikonov a emotikonov, vnímate obrázky lepšie ako farby, vývojári poskytujú sady ikon v príslušnom nástroji. Obrázkov je málo, ale na plnohodnotnú prácu stačí. Obrázky sú štylizované ako semafory, výkričníky, značky začiarknutia, krížiky na odstránenie značiek – jednoduchý a intuitívny prístup.


Vytvárať, mazať a spravovať pravidlá

Funkcia „Vytvoriť pravidlo“ úplne duplikuje „Iné pravidlá“ uvedené vyššie a vytvorí výber spočiatku na žiadosť používateľa.

Pomocou záložky "Vymazať pravidlo" sa vytvorené skripty vymažú z celého hárku, zo zvoleného rozsahu hodnôt, z tabuľky.

Zaujímavý je nástroj „Správa pravidiel“ - akási história vytvárania a zmien vykonaného formátovania. Zmeňte kolekcie, deaktivujte pravidlá, vráťte sa späť, zmeňte poradie aplikácie. Je to veľmi výhodné pre prácu s veľkým množstvom informácií.


Výber buniek podľa dátumov

Ak chcete zistiť, ako vytvoriť farbu bunky v programe Excel na základe hodnoty nastaveného dátumu, zvážte príklad s dátumami nákupu od dodávateľov v januári 2019. Ak chcete použiť tento výber, bunky s zavedený formát"Dátum". Ak to chcete urobiť, pred zadaním informácií vyberte požadovaný stĺpec a kliknite kliknite pravým tlačidlom myši myšou a v menu "Formát buniek" nájdite záložku "Číslo". Nastavte formát čísla na "Dátum" a vyberte jeho typ podľa želania.

Na výber požadovaných dátumov používame nasledujúcu postupnosť akcií:

  • vyberte stĺpce s dátumami (v našom prípade za január);
  • nájdite nástroj podmieneného formátovania;
  • v časti „Pravidlá výberu buniek“ vyberte položku „Dátum“;
  • na pravej strane formátovania otvorte rozbaľovacie pole s pravidlami;
  • vyberte príslušné pravidlo (vyberú sa napríklad dátumy za predchádzajúci mesiac);
  • v ľavom poli nastavte hotový výber farby "Žltá výplň a tmavožltý text"
  • Výber je farebný, kliknite na "OK".

Formátovaním buniek obsahujúcich dátum môžete vybrať desať hodnôt: včera/dnes/zajtra, posledný/aktuálny/budúci týždeň, posledný/aktuálny/nasledujúci mesiac, posledných 7 dní.


Zvýraznite stĺpec podľa podmienky

Ak chcete analyzovať aktivity spoločnosti pomocou tabuľky, vezmime si príklad, ako zmeniť farbu bunky v programe Excel v závislosti od stavu špecifikovaného zamestnancom. Ako príklad si zoberme tabuľku objednávok za január 2019 pre desať protistrán.

Musíme označiť modrou farbou tých dodávateľov, od ktorých sme kúpili tovar za sumu vyššiu ako 100 000 rubľov. Na vykonanie takéhoto výberu používame nasledujúci algoritmus akcií:

  • vyberte stĺpec s januárovými nákupmi;
  • Kliknite na nástroj Podmienené formátovanie
  • prejdite na "Pravidlá výberu buniek";
  • položka "Viac ...";
  • na pravej strane formátovania nastavte sumu na 100 000 rubľov;
  • v ľavom poli prejdite na kartu "Vlastný formát" a vyberte modrú farbu;
  • požadovaný výber je zafarbený modrou farbou, kliknite na „OK“.

Nástroj podmienené formátovanie sa používa na riešenie každodenných obchodných problémov. S jeho pomocou analyzujú informácie, vyberajú potrebné komponenty, kontrolujú podmienky interakcie medzi dodávateľom a klientom. Používateľ sám prichádza s kombináciami, ktoré potrebuje.

Dôležitú úlohu zohráva farebné prevedenie, pretože v bielej tabuľke s veľkým množstvom údajov sa ťažko orientuje. Ak prídete na postupnosť farieb a znakov, informatívnosť informácií bude vnímaná takmer intuitívne. Snímky obrazovky z takýchto tabuliek budú jasne viditeľné v správach a prezentáciách.

Potrebujete zvýrazniť duplicitné hodnoty v stĺpci? Vyberte prvých 5 maximálny počet buniek? Je potrebné kvôli prehľadnosti urobiť tepelnú stupnicu (farba sa mení v závislosti od nárastu / poklesu hodnoty buniek)? V Exceli je možné zvýraznenie buniek podľa podmienok vykonať veľmi rýchlo. V Exceli je špeciálna funkcia "Podmienené formátovanie" zodpovedná za zvýraznenie buniek farbou. Vysoko odporucany! Prečítajte si viac nižšie:

Ak chcete začať, na páse úloh v hlavnej ponuke nájdite časť Štýly a kliknite na tlačidlo Podmienené formátovanie,

Po kliknutí sa otvorí ponuka s rôznymi možnosťami tejto úpravy. Ako vidíte, príležitostí je tu naozaj veľa.

Teraz viac o najužitočnejších:

Excel zvýraznenie buniek podľa podmienok. Jednoduché pojmy

Ak to chcete urobiť, prejdite na položku Pravidlá výberu buniek. Ak napríklad potrebujete vybrať všetky bunky väčšie ako 100, kliknite na tlačidlo Viac. V okne:

v predvolenom nastavení sú podmienky navrhnuté tak, aby boli zvýraznené červenou farbou, ale kliknutím do pravého poľa a výberom požadovanej možnosti môžete nastaviť požadované formátovanie buniek.

Zvýraznenie duplicitných hodnôt, vrát. cez viacero stĺpcov

Pre zvýraznenie všetkých duplicitných hodnôt zvoľte príslušné menu (viď obrázok na začiatku článku). Ďalej sa znova zobrazí okno formátovania. Prispôsobte si, ako sa vám páči.

Čo robiť, ak potrebujete nájsť opakovania v dvoch alebo viacerých stĺpcoch, napríklad keď je celé meno v rôznych stĺpcoch? Vytvorte ďalší stĺpec a skombinujte hodnoty so vzorcom = , t.j. y v samostatnej bunke budete mať napísané IvanovIvanIvanych, potom v tomto stĺpci môžete vybrať duplicitné hodnoty. Je dôležité pochopiť, že ak sa slovosled líši, Excel bude takéto riadky považovať za neopakujúce sa.

Farebné zvýraznenie prvých/posledných hodnôt. Opäť podmienené formátovanie

Ak to chcete urobiť, prejdite na Pravidlá pre výber prvej a poslednej bunky a vyberte požadovanú položku. Okrem toho, že môžete zvýrazniť prvé / posledné hodnoty​​(vrátane percent), môžete využiť možnosť zvýraznenia údajov nad a pod priemerom (ja to používam ešte častejšie). Veľmi praktické na prezeranie výsledkov, ktoré sa vymykajú norme alebo priemeru!

Konštrukcia tepelného diagramu a histogramu

Skvelou funkciou pre vizualizáciu dát je teplotný/teplotný graf. Podstatou je, že v závislosti od hodnoty hodnoty v stĺpci alebo riadku je bunka zvýraznená určitým odtieňom farby. Tabuľky sú oveľa lepšie vnímané okom a rozhodovanie je jednoduchšie. Koniec koncov, jedným z najlepších analyzátorov je naše oko, a teda mozog, a nie stroj!

Histogram v bunke (obrázok nižšie) je tiež extrémne užitočná funkcia zisťovať zmeny hodnôt a porovnávať ich.

Zvýraznite bunky obsahujúce konkrétny text

Veľmi často potrebujete nájsť bunky, ktoré obsahujú určitú množinu znakov, môžete samozrejme použiť funkciu =, ale jednoduchšie a rýchlejšie je použiť ju na podmienené formátovanie, prejdite na Pravidlá výberu bunky - Text obsahuje (pozri obrázok 2 ).

Veľmi užitočné pri práci s textom. Príklad, keď máte v stĺpci celé mená zamestnancov, ale musíte vybrať všetkých kolegov Ivanovcov. Vyberte bunky, prejdite na položku ponuky a vyberte obsahujúci text Ivanov, po ktorom filtrujeme tabuľku podľa farby:

Excel zvýraznenie. Filtrujte podľa farby

Okrem vyššie uvedených možností môžete vybrané bunky filtrovať podľa farby pomocou bežného filtra. Na moje prekvapenie o tom vie len veľmi málo ľudí – zrejme ozveny verzie z roku 2003 – táto funkcia tam nebola.

Kontrola podmienok formátovania

Ak chcete skontrolovať, ktoré podmienené formátovanie ste už nastavili, prejdite na Hlavné - Podmienené formátovanie - Spravovať pravidlá, tu môžete upraviť už nastavené podmienky pre zvýraznenie buniek farbou, ako aj vybrať prioritu zadaného formátovania (kto je vyšší , to je dôležitejšie, môžete to zmeniť pomocou tlačidiel - šípok)

Neplatný rozsah podmieneného formátovania

Dôležité! Podmienené formátovanie pri nesprávnom použití je často príčinou spomalenia Excelu. To sa stane, ak kopírujete bunky s farebným zvýraznením mnohokrát. Potom môžete mať viacero podmienok s farbou. Sám som videl viac ako 3 tisíc podmienok - súbor bol nehanebne spomalený. Môže sa tiež spomaliť, keď je nastavený rozsah ako na obrázku vyššie, je lepšie zadať A: A - pre celý rozsah.

Prečítajte si viac o brzdách Excel a ich príčinách. Tento článok pomohol viac ako stovke ľudí ;)

Som rád, že som sa dozvedel o farbení buniek podľa podmienok v Exceli.

Zdieľajte náš článok na svojich sociálnych sieťach: