Ako zvýrazniť bunku s maximálnou hodnotou. Zistite, ako vybrať bunky v programe Excel. Podmienky pre tieňovanie buniek Excelu vo Windows
Ak hodnota v bunke spĺňa užívateľom definovanú podmienku, potom pomocou pomoci môžete túto bunku zvýrazniť (napríklad zmeniť jej pozadie). V tomto článku poďme ďalej - vyberieme celý riadok tabuľky obsahujúci túto bunku.
Nechajte v rozsahu A6:C16 je tam tabuľka so zoznamom prác, termínmi a stavom ich dokončenia (pozri vzorový súbor).
Task1 - textové hodnoty
Je potrebné zvýrazniť čiaru obsahujúcu prácu určitého stavu farbou. Napríklad, ak sa práca nezačala, riadok sa zvýrazní červenou farbou, ak práca ešte nebola dokončená, potom sivou farbou a ak je dokončená, potom zelenou farbou. Riadky vyberieme pomocou pravidiel.
Riešenie1
Vytvorme si malú tabuľku so stavmi úloh v rozsahu E6:E9 .
Vyberte rozsah buniek A7:C17 , ktorý obsahuje zoznam diel, a nainštalujte cez menu Domov/ Farba výplne pozadie výplne je červené (predpokladáme, že všetky diela sú pôvodne v stave Nezačal).
Uistite sa, že je vybratý rozsah buniek A7:C17 (A7 musí byť ). Zavolajte príkaz ponuky Podmienené formátovanie / Vytvorenie pravidla / Použitie vzorca na určenie buniek na formátovanie.
- v poli" Formátujte hodnoty, pre ktoré platí nasledujúci vzorec» musíte zadať =$C7=$E$8 (v bunke E8 hodnota sa nájde V práci). Venujte pozornosť použitiu ;
- stlač tlačidlo Formátovať;
- vyberte kartu vyplniť;
- vyberte sivú farbu ;
- Stlačte tlačidlo OK.
POZOR: Ešte raz dávam pozor na vzorec =$C7=$E$8 . Zvyčajne vstupujú používatelia =$C$7=$E$8 , t.j. zadajte ďalší symbol dolára.
Na výber diel v stave musíte vykonať podobné akcie Dokončené. Vzorec v tomto prípade bude vyzerať takto =$C7=$E$9 a farbu výplne nastaví na zelenú.
V dôsledku toho bude mať naša tabuľka nasledujúcu formu.
Ak chcete rýchlo rozšíriť pravidlá podmieneného formátovania na nový riadok v tabuľke, vyberte bunky Nový riadok (A17:C17 ) a stlačte . Pravidlá sa skopírujú do reťazca 17 tabuľky.
Úloha 2 - Dátumy
Predpokladajme, že vediete denník návštev zamestnancov vedeckých konferencií(cm. príklad súboru dátumu listu).
Žiaľ, stĺpec Dátum návštevy nie je zoradený a je potrebné zvýrazniť dátum prvej a poslednej návštevy každého zamestnanca. Napríklad zamestnanec Kozlov išiel na konferenciu prvýkrát 24.7.2009 a naposledy 18.7.2015.
Najprv vytvorme vzorec pre podmienené formátovanie v stĺpcoch B a E. Ak vzorec vráti hodnotu TRUE, potom sa príslušný riadok zvýrazní, ak FALSE, tak nie.
V stĺpci D vytvorené = MAX(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 A, ktorý definuje maximálny dátum pre konkrétneho zamestnanca.
Poznámka: Ak potrebujete určiť maximálny dátum bez ohľadu na zamestnanca, vzorec bude oveľa jednoduchší =$B7=MAX($B$7:$B$16) a vzorec poľa nie je potrebný.
Teraz označme všetky bunky tabuľky bez hlavičky a vytvoríme pravidlo. Skopírujme vzorec do pravidla (netreba ho zadávať ako vzorec poľa!).
Teraz predpokladajme, že stĺpec dátumu bol zoradený a je potrebné vybrať riadky, do ktorých spadajú dátumy návštev špecifický rozsah.
Ak to chcete urobiť, použite vzorec =AND($B23>$E$22;$B23<$E$23)
Pre bunky E22 a E23 s použitými dátumami uzávierky (zvýraznené žltou farbou). 22 $ E$ a 23 $ E$. Pretože odkaz na ne by sa nemal meniť v pravidlách UV pre všetky bunky tabuľky.
Pre bunku B22 používa sa zmiešané adresovanie $B23, t.j. odkaz na stĺpec B by sa nemal meniť (na tento účel je pred B znakom $), ale odkaz na riadok by sa mal meniť v závislosti od riadku tabuľky (inak budú všetky hodnoty dátumu porovnané s dátumom od B23 ).
Takže pravidlo UV napríklad pre bunku je A27 bude vyzerať =AND($B27>$E$22,$B27<$E$23) , т.е. A27 budú zvýraznené, pretože v tomto riadku dátum od B27 spadá do určeného rozsahu (pri bunkách zo stĺpca A sa výber aj tak uskutoční v závislosti od obsahu stĺpca B z toho istého riadku – to je „kúzlo“ zmiešaného adresovania $B23).
A pre celu B31 pravidlo UV by vyzeralo takto =AND($B31>$E$22;$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из B31 nespadá do určeného rozsahu.
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!
Výberom nástrojov na karte: „DOMOV“ v časti „Štýly“ z rozbaľovacej ponuky „Podmienené formátovanie“ máme k dispozícii celú skupinu „Pravidiel pre výber prvej a poslednej hodnoty“. Často je však potrebné porovnávať a zvýrazniť bunky v Exceli, no žiadne z hotových riešení nespĺňa naše podmienky. Napríklad v konštrukcii podmienky chceme použiť viac kritérií alebo vykonať zložitejšie výpočty. Vždy môžete vybrať poslednú možnosť „Iné pravidlá“, ktorá je zároveň možnosťou „Vytvoriť pravidlo“. Podmienené formátovanie vám umožňuje použiť vzorec na vytvorenie zložitých kritérií na porovnávanie a výber hodnôt. Vytváraním vlastných pravidiel pre podmienené formátovanie pomocou rôznych vzorcov sa v ničom neobmedzujeme.
Ako porovnať stĺpce v Exceli a zvýrazniť ich bunky farbou?
Pre názornosť sa pozrime na konkrétny príklad vytvárania podmieneného formátovania pomocou vzorcov. Vezmime si napríklad jednoduchú tabuľku prehľadu ziskovosti obchodu za minulý a aktuálny rok. Naším pravidlom je prinútiť Excel, aby zvýraznil, ak súčet obchodov za aktuálny rok má záporný zisk (stratu) a je vyšší ako minuloročný:
Ak chcete vytvoriť nové vlastné pravidlo, postupujte takto:
Poznámka! V tomto vzorci používame iba relatívne odkazy na bunky – to je dôležité. Koniec koncov, potrebujeme vzorec na analýzu všetkých buniek zvoleného rozsahu.
Ako zvýrazniť bunku v Exceli podľa podmienky?
Teraz oranžovou farbou zvýrazníme tie množstvá obchodov, ktoré sú tento rok menšie ako vlani a so záporným ziskom. Vytvorme druhé pravidlo pre rovnaký rozsah D2:D12:
Vidíme, že sme nedosiahli celkom očakávaný výsledok, keďže vytvorené nové pravidlo má vždy najvyššiu prioritu v porovnaní so starými pravidlami podmieneného formátovania v Exceli. Pre nové pravidlo musíte znížiť prioritu. Ak chcete túto funkciu vizuálne analyzovať a podľa toho ju nakonfigurovať, musíte vybrať nástroj: DOMOV»-«Štýly»-«Podmienené formátovanie»-«Správa pravidiel».
V zobrazenom okne „Správca pravidiel podmieneného formátovania“ vyberte nové oranžové pravidlo a kliknite na tlačidlo „Nadol“ (CTRL + šípka nadol), ako je znázornené na obrázku:
Ako vidíte, postupnosť pravidiel je veľmi dôležitá, ak ich existuje veľa pre rovnaký rozsah buniek:
Na prvý pohľad sa môže zdať, že rovnakú bunku môže formátovať niekoľko pravidiel súčasne. V zásade je to pravda, ale za určitej podmienky, že všetky pravidlá budú používať rôzne typy formátovania. Napríklad pravidlo 1 – zmení písmo, 2 – zmení výplň, 3 – pridá orámovanie, 4 – vzor atď. Ak však po vykonaní akéhokoľvek pravidla, keď je splnená jeho podmienka, bolo pre túto bunku skontrolované ďalšie pravidlo, potom v okne dispečera začiarknite políčko v stĺpci „Zastaviť, ak je pravda“:
A na záver pridajme tretie pravidlo na zvýraznenie buniek súčtov obchodov, kde je kladný zisk a viac ako minulý rok. Zadajte nový vzorec:
!}
Týmto bunkám bude priradená zelená farba a všade kliknite na OK.
Poznámka. Vo vzorci môžete použiť ľubovoľný odkaz na aktuálny hárok. V Exceli 2010 sa môžete prepojiť aj s inými pracovnými hárkami. A v Exceli 2007 je možné pristupovať k ďalším hárkom iba prostredníctvom názvov rozsahov. Vo všetkých verziách Excelu odporúčame odkazovať na iné hárky podľa názvu, pretože sa tak vyhnete mnohým chybám pri vytváraní vlastných pravidiel pre podmienené formátovanie.
Excel je nielen výkonný nástroj na vykonávanie výpočtov, ale aj nástroj, pomocou ktorého môžete výsledky výpočtov prezentovať atraktívnym spôsobom. To je dôvod, prečo v tomto článku chcem hovoriť o spôsoboch, ako dať bunkám farbu. Farbu bunky v Exceli je možné nastaviť niekoľkými spôsobmi. Nedá sa povedať, ktorá z metód je najlepšia, pretože každá je svojím spôsobom jedinečná a je určená na použitie za určitých podmienok a okolností.
Manuálne nastavenie farby buniek
Väčšina jednoduchým spôsobom nastavenie farby bunky v exceli je nástroj Farba výplne, ktorý sa nachádza na hlavnom paneli nástrojov.
Po výbere určitého rozsahu buniek musíte kliknúť na nástroj Farba výplne alebo na šípku vedľa neho. Ak kliknete na šípku vedľa nej, otvoria sa farby motívu. Z navrhovaných farieb si môžete vybrať požadovanú farbu alebo kliknúť na tlačidlo "Viac farieb". V tomto prípade sa otvorí ďalšie okno s možnosťou výberu farby.
Okrem opísanej metódy existuje aj druhá manuálna metóda. Najprv musíte vybrať rozsah buniek a potom zavolať obsahové menu a kliknite na položku ponuky "Formátovať bunky".
Otvorí sa dialógové okno Formát bunky. Potom budete musieť kliknúť na kartu "Vyplniť".
V tomto dialógovom okne musíte vybrať farbu a je to.
Je potrebné poznamenať, že všetky manuálne metódy sa používajú len na to, aby boli tabuľky čitateľnejšie. Týmto spôsobom môžete ozdobiť hlavičky tabuľky a zvýrazniť riadky tabuľky. Manuálne metódy však nie sú vhodné na zafarbenie buniek v závislosti od zapísanej hodnoty.
Nastavte farbu pozadia bunky na základe hodnoty
Niekedy sa stáva, že v závislosti od toho, aká hodnota je napísaná v bunke, je potrebné nastaviť farbu určitého pozadia. Pozrime sa, ako to urobiť na príklade. Máme obchodných zástupcov, ktorí majú pridelenú normu plánu pre určité skupiny produktov. Ak norma nie je splnená, bunka s predajom by sa mala „rozsvietiť“ na červeno. Ako primárnu podmienku povedzme, že týždenný predaj by nemal byť nižší ako 185 tisíc rubľov.
Ak chcete dokončiť úlohu, musíte vybrať rozsah buniek a kliknúť na panel nástrojov "Podmienené formátovanie".
V ponuke, ktorá sa otvorí, nás zaujíma položka "Pravidlá pre výber buniek". Ak kliknete na zadanú položku, môžete vidieť, že vývojári programu Excel implementovali veľa možností na výber buniek. Pozadie môžete vybrať podľa nasledujúcich kritérií: väčšie ako, menšie ako, medzi, rovné, text obsahuje, dátum, duplicitné hodnoty. Na vyriešenie nášho problému musíme použiť „Menej“.
Po zadaní kritéria 185 tisíc sa v tabuľke objavia výbery buniek.
Navrhovaná metóda je široko používaná pri ekonomickej analýze rôznych údajov. Je jednoduchý a cenovo dostupný. Umožňuje nastaviť určité podmienky výberu ešte pred explicitnou definíciou údajov.
Nastavenie farby buniek pomocou VBA
Spôsob výberu buniek v programe Excel, o ktorom sa bude diskutovať nižšie, je najuniverzálnejší. Pretože s jeho pomocou môžete vytvoriť najkomplexnejšie pravidlá pre výber buniek. Ale bohužiaľ ho môžu používať iba pokročilí používatelia. Dôvod je jednoduchý – potrebujete programovať, čiže máte zložitejšie zručnosti.
Nastavenie farby bunky pomocou VBA je pomerne jednoduché, kód výberového programu bude vyzerať takto:
Rozsah("O6").Vyberte
Selection.Interior.Color = QBColor(10)
Po vykonaní tohto kódu sa bunka O6 zmení na zelenú.
Použitie VBA na zvýraznenie farby bunky vám umožňuje vykonávať širokú škálu úloh, ako je napríklad zvýraznenie buniek špecifickou farbou:
- v závislosti od typu zadávanej hodnoty;
- v závislosti od času úpravy dokumentu;
- v závislosti od používateľa počítača, na ktorom sa dokument upravuje atď.
Ak chcete správne umiestniť akcenty v dokumente vyrobenom v programe MS Excel, môžete zväčšiť veľkosť buniek, vybrať iné písmo alebo veľkosť písmen alebo môžete premaľovať štvorce, ktorým určite musíte venovať pozornosť.
Teraz zistíme, ako zvýrazniť bunky farbou v programe Excel alebo zmeniť farbu tých, ktoré sú už premaľované. Ako docieliť, aby bunka menila farbu podľa danej podmienky v závislosti od zadanej hodnoty a ako pracovať s vytvorenými pravidlami.
Jednoduchá bloková výplň
Vyfarbenie jedného alebo viacerých blokov v Exceli nie je ťažké. Najprv ich vyberte a na karte Domov kliknutím na šípku vedľa nádoby s farbou rozbaľte zoznam. Vyberte si odtiaľ vhodnú farbu a ak sa vám nič nehodí, kliknite na „Iné farby“.
Mimochodom, týmto spôsobom môžete vyplniť celý riadok, stačí najskôr kliknúť na jeho číslo a vybrať ho. O výbere buniek v Exceli si môžete prečítať samostatný článok.
Ak pracujete s tabuľkou, v ktorej je už niečo premaľované, môžete zmeniť farbu blokov alebo ju dokonca úplne odstrániť. Kliknite naň a z farieb, prípadne vyberte novú, prípadne kliknite na možnosť „Žiadne“.
V závislosti od zadaných údajov
Teraz sa pozrime na to, ako zmeniť farbu bunky na základe danej podmienky. Na to slúži podmienené formátovanie, o ktorom je na stránke samostatný článok.
Text
Vezmime si ako príklad nasledujúcu tabuľku. Urobme to tak, aby červená zodpovedala jablku, žltá hruške a oranžová oranžovej.
Vyberáme údaje, s ktorými budeme pracovať, v mojom prípade sú to názvy ovocia. Potom stlačíme "Podmienené formátovanie", ktorý budeme používať aj v budúcnosti. V zozname kliknite na "Vytvoriť pravidlo".
Toto okno sa otvorí. Vyberte typ v hornej časti "Formátovať iba bunky, ktoré obsahujú", ďalej to tiež oslávime. Nižšie uvádzame podmienky: máme text, ktorý obsahuje určité slová. V poslednom poli buď kliknite na tlačidlo a zadajte bunku, alebo zadajte text.
Rozdiel je v tom, že vložením odkazu na bunku (=$B$4) sa podmienka zmení v závislosti od toho, čo je v nej napísané. Napríklad namiesto jablka v B4 uvediem ríbezľu, pravidlo sa podľa toho zmení a bloky s rovnakým textom sa prefarbia. A ak do poľa zadáte jablko, bude sa hľadať toto konkrétne slovo a nebude závisieť od ničoho.
Tu vyberte farbu výplne a kliknite na tlačidlo OK. Ak chcete zobraziť všetky možnosti, kliknite na tlačidlo „Iné“.
Pravidlo bolo vytvorené a uložte ho stlačením tlačidla OK.
V dôsledku toho boli všetky bloky, ktoré obsahovali zadaný text, natreté červenou farbou.
Pravidlá sú vytvorené aj pre iné ovocie, len je zvolená iná možnosť plnenia.
Numerický
Teraz sa poďme zaoberať číselnými údajmi. Umiestnime čísla v stĺpci D na určité pozadie podľa podmienky, ktorú si nastavíme.
Vyberte stĺpec, vytvorte pravidlo, zadajte jeho typ. Ďalej píšeme - „Hodnota“ „väčšia ako“ „15“. Posledné číslo môžete zadať buď ručne, alebo zadať adresu bunky, z ktorej sa budú údaje brať. Rozhodneme sa pre výplň, kliknite na "OK".
Bloky, do ktorých sú zadané čísla väčšie ako vybrané, sú premaľované.
Špecifikujme viac pravidiel pre vybrané bunky - vyberte "Správa pravidiel".
Tu si vyberte všetko, ako som popísal vyššie, stačí zmeniť farbu a nastaviť podmienku "menej alebo rovnaké".
Keď je všetko pripravené, kliknite na „Použiť“ a „OK“.
Všetko funguje, hodnoty rovné a nižšie ako 15 sú vymaľované bledomodrou farbou.
Tu by bol tiež jasne viditeľný rozdiel medzi dovážaným a predávaným tovarom. Vyberte dva stĺpce a kliknite "Pravidlá výberu buniek" – "Duplicitné hodnoty". Vyberte si správnu farbu. Ak sa potom vyfarbia dve bunky vedľa seba, znamená to, že prinesené ovocie je už predané.
Na príklade posledného stĺpca si ukážeme, ako prinútiť bunku zmeniť farbu v závislosti od zadanej hodnoty. Vyberte ich a prejdite na "Správa pravidiel".
Pre aktuálny fragment vytvoríme nový, ak je to potrebné, v tomto rozbaľovacom zozname si môžete vybrať buď pre celý hárok, alebo pre iné hárky.
V okne, ktoré sa otvorí, vyberte požadované položky. Tmavozelenou vyplním všetky hodnoty, ktoré sú väčšie ako 90. Keďže som v poslednom poli zadal adresu (=$F$15 ), pri zmene čísla 90 v bunke napríklad na 110 sa pravidlo tiež zmeniť. Uložte zmeny kliknutím na tlačidlo OK.
Vytvorím ďalšie pravidlo, ale v ňom zvýrazním svetlozelenou všetko, čo je menšie alebo rovné 90. Nezabudnite si všetko uložiť kliknutím na tlačidlá vpravo dole.
Tu je to, čo sa stalo. Z posledného stĺpca môžete vizuálne rýchlo určiť zisk z predaja toho ktorého produktu bol väčší.
Ak sa napríklad zmení cena, upravia sa aj pravidlá. Ak je hodnota väčšia alebo menšia ako špecifikovaná hodnota, farba bunky sa automaticky zmení.
Myslím, že ste si všimli, že pravidlo sa vytvára nielen pre text a čísla, ale aj pre dátumy a v závislosti od vyplnenia a prítomnosti chýb v bunkách. Zadajte podmienku, vyberte farbu a uložte.
Ak chcete vidieť, čo ste pridali, vyberte rozsah a v okne "Správa pravidiel" bude úplný zoznam. Pomocou tlačidiel v hornej časti ich môžete pridať, zmeniť alebo odstrániť.
