2 spôsoby, ako zmeniť pozadie v Exceli

V tomto článku sa dozviete dva jednoduché spôsoby, ako zmeniť pozadie buniek na základe ich obsahu v najnovších verziách Excelu. Tiež pochopíte, ktoré vzorce použiť na zmenu odtieňa buniek alebo buniek, kde sú vzorce napísané nesprávne, alebo kde nie sú žiadne informácie.

Každý vie, že úprava pozadia jednoduchej bunky je jednoduchý postup. Kliknite na „farba pozadia“. Ale čo ak potrebujete korekciu farieb na základe určitého obsahu buniek? Ako to môžem urobiť automaticky? Nasleduje séria užitočných informácií, ktoré vám umožnia nájsť správny spôsob, ako splniť všetky tieto úlohy.

Dynamická zmena farby pozadia bunky

úloha: Máte tabuľku alebo množinu hodnôt a potrebujete upraviť farbu pozadia buniek podľa toho, aké číslo je tam zadané. Musíte tiež zabezpečiť, aby odtieň reagoval na meniace sa hodnoty.

Riešenie: Pre túto úlohu je pre farebné bunky s číslami väčšími ako X, menšími ako Y alebo medzi X a Y poskytnutá funkcia Excelu „podmienené formátovanie“.

Povedzme, že máte súbor produktov v rôznych štátoch s ich cenami a potrebujete vedieť, ktorý z nich stojí viac ako 3,7 USD. Preto sme sa rozhodli tie produkty, ktoré sú nad touto hodnotou, zvýrazniť červenou farbou. A bunky, ktoré majú podobnú alebo väčšiu hodnotu, bolo rozhodnuté zafarbiť do zeleného odtieňa.

Poznámka: Snímka obrazovky bola urobená vo verzii programu z roku 2010. To však nič neovplyvní, pretože postupnosť akcií je rovnaká bez ohľadu na to, ktorú verziu – najnovšiu alebo nie – daná osoba používa.

Tu je to, čo musíte urobiť (krok za krokom):

1. Vyberte bunky, v ktorých sa má upraviť odtieň. Napríklad rozsah $ B $ 2: $ H $ 10 (názvy stĺpcov a prvý stĺpec, v ktorom sú uvedené názvy štátov, sú zo vzorky vylúčené).

2. Kliknite na "Home" v skupine "štýl". Bude tam položka "Podmienené formátovanie". Tam si tiež treba vybrať "Nové pravidlo". V anglickej verzii Excelu je postupnosť krokov nasledovná: „Domov“, „Skupina štýlov“, „Podmienené formátovanie > Nové pravidlo».

3. V okne, ktoré sa otvorí, začiarknite políčko „Formátovať iba bunky, ktoré obsahujú“ („Formátovať iba bunky, ktoré obsahujú“ v anglickej verzii).

4. V spodnej časti tohto okna pod nápisom „Formátovať len bunky, ktoré spĺňajú nasledujúcu podmienku“ (Formátovať iba bunky s) môžete priradiť pravidlá, podľa ktorých sa formátovanie vykoná. Zvolili sme formát pre zadanú hodnotu v bunkách, ktorá musí byť väčšia ako 3.7, ako môžete vidieť na snímke obrazovky: 

5. Ďalej kliknite na tlačidlo „Format“. Zobrazí sa okno s oblasťou výberu farby pozadia vľavo. Predtým však musíte kartu otvoriť "Vyplniť" ("Vyplniť"). V tomto prípade je to červená. Potom kliknite na tlačidlo „OK“.

6. Potom sa vrátite do okna "Nové pravidlo formátovania", ale už v spodnej časti tohto okna si môžete pozrieť, ako bude táto bunka vyzerať. Ak je všetko v poriadku, musíte kliknúť na tlačidlo „OK“.

V dôsledku toho dostanete niečo takéto:

Ďalej musíme pridať ešte jednu podmienku, to znamená zmeniť pozadie buniek s hodnotami menšími ako 3.45 na zelené. Ak chcete vykonať túto úlohu, musíte kliknúť na "Nové pravidlo formátovania" a zopakujte vyššie uvedené kroky, iba podmienku je potrebné nastaviť ako „menej ako alebo ekvivalent“ (v anglickej verzii „menej než alebo rovné“ a potom napíšte hodnotu. Na konci musíte kliknúť na tlačidlo „OK“.

Teraz je tabuľka naformátovaná týmto spôsobom.

Zobrazuje najvyššie a najnižšie ceny paliva v rôznych štátoch a môžete okamžite určiť, kde je situácia najoptimistickejšia (samozrejme v Texase).

Odporúčania: V prípade potreby môžete použiť podobný spôsob formátovania, pričom neupravíte pozadie, ale písmo. Ak to chcete urobiť, v okne formátovania, ktoré sa objavilo v piatej fáze, musíte vybrať kartu "Písmo" a postupujte podľa pokynov v okne. Všetko je intuitívne jasné a zorientuje sa v tom aj začiatočník.

Výsledkom je takáto tabuľka:

Ako zachovať rovnakú farbu bunky, aj keď sa hodnota zmení?

úloha: Pozadie musíte vyfarbiť tak, aby sa nikdy nezmenilo, aj keď sa pozadie v budúcnosti zmení.

Riešenie: nájdite všetky bunky s konkrétnym číslom pomocou funkcie Excel “Nájsť všetko” „Nájsť všetko“ alebo doplnok "Vybrať špeciálne bunky" („Vybrať špeciálne bunky“) a potom pomocou funkcie upravte formát bunky "Formátovať bunky" („Formátovať bunky“).

Toto je jedna z tých zriedkavých situácií, ktoré nie sú zahrnuté v príručke programu Excel a dokonca aj na internete sa riešenie tohto problému dá nájsť pomerne zriedka. Čo nie je prekvapujúce, pretože táto úloha nie je štandardná. Ak chcete pozadie natrvalo upraviť, aby sa nikdy nezmenilo, kým ho používateľ programu ručne neupraví, musíte postupovať podľa vyššie uvedených pokynov.

Vyberte všetky bunky, ktoré obsahujú určitú podmienku

Existuje niekoľko možných metód v závislosti od druhu konkrétnej hodnoty, ktorá sa má nájsť.

Ak potrebujete označiť bunky s konkrétnou hodnotou so špeciálnym pozadím, musíte prejsť na kartu "Home" A vyberte „Nájsť a vybrať“ – „Nájsť“.

Zadajte požadované hodnoty a kliknite na “Nájsť všetko”.

Pomoc: Môžete kliknúť na tlačidlo "Možnosti" na pravej strane môžete využiť niektoré ďalšie nastavenia: kde hľadať, ako zobraziť, či sa majú rešpektovať veľké a malé písmená atď. Môžete tiež napísať ďalšie znaky, ako napríklad hviezdičku (*), aby ste našli všetky riadky obsahujúce tieto hodnoty. Ak použijete otáznik, môžete nájsť ľubovoľný jeden znak.

Ak by sme v našom predchádzajúcom príklade chceli nájsť všetky ceny pohonných hmôt medzi 3,7 a 3,799 XNUMX USD, mohli by sme náš vyhľadávací dopyt spresniť.

Teraz vyberte ktorúkoľvek z hodnôt, ktoré program našiel v spodnej časti dialógového okna, a kliknite na jednu z nich. Potom stlačením kombinácie klávesov „Ctrl-A“ vyberte všetky výsledky. Ďalej kliknite na tlačidlo „Zavrieť“. 

Tu je postup, ako vybrať všetky bunky s konkrétnymi hodnotami pomocou funkcie Nájsť všetko. V našom príklade potrebujeme nájsť všetky ceny pohonných hmôt nad 3,7 USD a Excel vám to bohužiaľ neumožňuje pomocou funkcie Nájsť a nahradiť.

Tu sa objavuje „sud medu“, pretože existuje ďalší nástroj, ktorý vám pomôže s takými zložitými úlohami. Volá sa to Select Special Cells. Tento doplnok (ktorý je potrebné nainštalovať do Excelu samostatne) pomôže:

• nájsť všetky hodnoty v určitom rozsahu, napríklad medzi -1 a 45,
• získať maximálnu alebo minimálnu hodnotu v stĺpci,
• nájsť reťazec alebo rozsah,
• nájsť bunky podľa farby pozadia a oveľa viac.

Po nainštalovaní doplnku stačí kliknúť na tlačidlo “Vybrať podľa hodnoty” („Vybrať podľa hodnoty“) a potom spresnite vyhľadávací dopyt v okne doplnku. V našom príklade hľadáme čísla väčšie ako 3,7. Stlačte tlačidlo "Vybrať" („Vybrať“) a za sekundu dostanete takýto výsledok:

Ak máte o doplnok záujem, skúšobnú verziu si môžete stiahnuť na odkaz.

Zmena pozadia vybratých buniek prostredníctvom okna „Formát buniek“.

Teraz, keď boli všetky bunky s určitou hodnotou vybraté pomocou jednej z vyššie opísaných metód, zostáva pre ne určiť farbu pozadia.

Ak to chcete urobiť, musíte otvoriť okno "Formát bunky"stlačením klávesu Ctrl + 1 (môžete tiež kliknúť pravým tlačidlom myši na vybrané bunky a ľavým tlačidlom myši na položku „formátovanie buniek“) a upraviť formátovanie, ktoré potrebujete.

Vyberieme si oranžový odtieň, ale môžete si vybrať akýkoľvek iný.

Ak potrebujete upraviť farbu pozadia bez zmeny ostatných parametrov vzhľadu, stačí kliknúť na "farebná výplň" a vyberte si farbu, ktorá vám dokonale vyhovuje.

Výsledkom je takáto tabuľka:

Na rozdiel od predchádzajúcej techniky sa tu farba bunky nezmení ani po úprave hodnoty. Dá sa to využiť napríklad na sledovanie dynamiky tovaru v určitej cenovej skupine. Ich hodnota sa zmenila, ale farba zostala rovnaká.

Úprava farby pozadia pre špeciálne bunky (prázdne alebo s chybami pri písaní vzorca)

Podobne ako v predchádzajúcom príklade má používateľ možnosť upraviť farbu pozadia špeciálnych buniek dvoma spôsobmi. Existujú statické a dynamické možnosti.

Použitie vzorca na úpravu pozadia

Tu sa farba bunky upraví automaticky na základe jej hodnoty. Táto metóda používateľom veľmi pomáha a je žiadaná v 99% situácií.

Ako príklad môžete použiť predchádzajúcu tabuľku, ale teraz budú niektoré bunky prázdne. Musíme určiť, ktoré neobsahujú žiadne hodnoty a upraviť farbu pozadia.

1. Na karte "Home" musíte kliknúť na "Podmienené formátovanie" ->  "Nové pravidlo" (rovnako ako v kroku 2 prvej časti „Dynamická zmena farby pozadia“.

2. Ďalej musíte vybrať položku „Použite vzorec na určenie...“.

3. Zadajte vzorec =JePrázdne() (ISBLANK vo verzii), ak chcete upraviť pozadie prázdnej bunky, príp =IsError() (vo verzii ISERROR), ak potrebujete nájsť bunku, kde je chybne napísaný vzorec. Keďže v tomto prípade potrebujeme upraviť prázdne bunky, zadáme vzorec =JePrázdne()a potom umiestnite kurzor medzi zátvorky a kliknite na tlačidlo vedľa poľa na zadanie vzorca. Po týchto manipuláciách by sa mal rozsah buniek vybrať manuálne. Okrem toho si rozsah môžete určiť sami, napr. =JePrázdne(B2:H12).

4. Kliknite na tlačidlo „Formátovať“ a vyberte príslušnú farbu pozadia a urobte všetko tak, ako je popísané v odseku 5 časti „Zmena farby pozadia dynamickej bunky“, a potom kliknite na „OK“. Tam môžete tiež vidieť, akú farbu bude mať bunka. Okno bude vyzerať asi takto.

5. Ak sa vám páči pozadie bunky, musíte kliknúť na tlačidlo „OK“ a zmeny sa okamžite vykonajú v tabuľke.

Statická zmena farby pozadia špeciálnych buniek

V tejto situácii po priradení zostane farba pozadia taká, bez ohľadu na to, ako sa bunka zmení.

Ak potrebujete natrvalo zmeniť špeciálne bunky (prázdne alebo obsahujúce chyby), postupujte podľa týchto pokynov:

1. Vyberte dokument alebo niekoľko buniek a stlačením klávesu F5 otvorte okno Prejsť na a potom stlačte tlačidlo "Zlatý klinec".
2. V dialógovom okne, ktoré sa otvorí, vyberte tlačidlo „Prázdne“ alebo „Vyprázdniť bunky“ (v závislosti od verzie programu – alebo angličtiny), čím vyberiete prázdne bunky.
3. Ak potrebujete zvýrazniť bunky, ktoré obsahujú vzorce s chybami, mali by ste vybrať položku "Vzorce" a ponechajte jedno začiarkavacie políčko vedľa slova „Chyby“. Ako vyplýva zo snímky obrazovky vyššie, bunky je možné vybrať podľa ľubovoľných parametrov a v prípade potreby je k dispozícii každé z opísaných nastavení.
4. Nakoniec je potrebné zmeniť farbu pozadia vybratých buniek alebo ich prispôsobiť iným spôsobom. Ak to chcete urobiť, použite metódu opísanú vyššie.

Nezabudnite, že zmeny formátovania vykonané týmto spôsobom zostanú zachované, aj keď vyplníte medzery alebo zmeníte špeciálny typ bunky. Samozrejme, je nepravdepodobné, že niekto bude chcieť túto metódu použiť, ale v praxi sa môže stať čokoľvek.

Ako vyťažiť maximum z Excelu?

Ako nároční používatelia programu Microsoft Excel by ste mali vedieť, že obsahuje veľa funkcií. Niektoré z nich poznáme a milujeme, iné zostávajú pre bežného používateľa záhadné a veľké množstvo blogerov sa ich snaží vniesť aspoň trochu do svetla. Existujú však bežné úlohy, ktoré bude musieť vykonať každý z nás, a Excel nezavádza niektoré funkcie alebo nástroje na automatizáciu niektorých zložitých akcií.

A riešením tohto problému sú doplnky (addony). Niektoré z nich sú distribuované bezplatne, iné za peniaze. Existuje mnoho podobných nástrojov, ktoré môžu vykonávať rôzne funkcie. Nájdite napríklad duplikáty v dvoch súboroch bez tajomných vzorcov alebo makier.

Ak skombinujete tieto nástroje so základnou funkcionalitou Excelu, môžete dosiahnuť veľmi skvelé výsledky. Môžete napríklad zistiť, ktoré ceny pohonných hmôt sa zmenili, a potom nájsť duplikáty v súbore za posledný rok.

Vidíme, že podmienené formátovanie je šikovný nástroj, ktorý vám umožňuje automatizovať prácu na tabuľkách bez akýchkoľvek špecifických zručností. Teraz viete, ako vyplniť bunky niekoľkými spôsobmi na základe ich obsahu. Teraz zostáva len uviesť to do praxe. Veľa štastia!