Tento tutoriál ukáže, ako otestovať vzorce pred ich použitím na podmienené formátovanie v programe Excel.
Testovanie vlastných vzorcov - Excel
Pri použití vlastného vzorca na podmienené formátovanie v programe Excel je dôležité uistiť sa, že vzorec skutočne vráti v hárku správnu hodnotu PRAVDA alebo NEPRAVDA, aby podmienené formátovanie fungovalo správne.
Pred vytvorením pravidla podmieneného formátovania môžeme v našom excelovom zošite vytvoriť vzorec na „voľnom mieste“ a ubezpečiť sa, že vzorec funguje správne.
Testovanie vzorca na zvýraznenie bunky pracovného hárka
Vo vyššie uvedenom príklade chceme napísať vzorec, aby sme zistili, či je každá bunka v pracovnom hárku väčšia ako 5 a ak je väčšia ako 5, chceme zafarbiť pozadie bunky.
Môžeme začať s prvou bunkou v rozsahu - v tomto prípade B3, a potom môžeme testovať každý riadok a stĺpec rozsahu - až po E11.
Počnúc jednoduchým vzorcom vidíme, že B3 má hodnotu 5, a preto NIE je väčšia ako 5.
Potom môžeme pomocou držadla pretiahnuť tento vzorec nadol na riadok 11, pričom si všimneme, že riadková časť adresy bunky (tj. 3) sa zmení na ďalšie číslo, keď potiahneme vzorec nadol cez riadky, takže z B3 sa stane B4, potom B5 a tak ďalej.
Potom môžeme presúvať zvýraznený rozsah buniek cez 4 stĺpce a otestovať, či bunky v stĺpcoch C až E majú hodnotu väčšiu ako 5. Keď pretiahneme vzorec, časť stĺpca adresy bunky sa podľa toho zmení - od stĺpec C až D až E až F.
Ako teraz vidíme, dostaneme správnu PRAVDU alebo NEPRAVDU hodnotu v závislosti od hodnoty v zodpovedajúcej bunke.
Teraz vieme, že náš vzorec je správny, a môžeme ho použiť na vytvorenie pravidla podmieneného formátovania. Rovnako ako pre náš testovací vzorec používame prvú bunku v rozsahu B3.
= B3> 5
Keď máme vo vzorci typy, môžeme nastaviť formát farby pozadia a kliknúť na tlačidlo OK.
Ako vidíte, vzorec = B3> 5 platí pre rozsah B3: E11. Bunka vzorca musí vždy zodpovedať prvej bunke v rozsahu, ktorý sa má formátovať.
Kliknite Použiť použiť formátovanie na pracovný hárok.
Testovanie vzorca na zvýraznenie riadka pracovného hárka
Použitie vzorca na zmenu farby pozadia riadka namiesto stĺpca je mierne odlišné a o niečo komplikovanejšie.
Zvážte nasledujúci pracovný list.
V tomto pracovnom hárku chceme zvýrazniť celý riadok, ak je projekt po lehote splatnosti - ak má teda stĺpec E bunku, ktorá vracia hodnotu „Po termíne" namiesto "Načas“, Potom chceme zvýrazniť celý riadok, v ktorom sa táto bunka nachádza.
Vzorec na to je jednoduchý:
= IF (E4 = „Po lehote splatnosti“, PRAVDA, NEPRAVDA)
Ak to však použijeme na naše podmienené formátovanie, bude to vrátené.
Naformátovaný bude iba prvý stĺpec v rozsahu.
Vyskúšajme tento vzorec v našom hárku programu Excel.
V prípade, že je projekt po lehote splatnosti v stĺpci E, dostaneme požadovaný výsledok PRAVDA. Ak by sme však tento vzorec skopírovali pre 5 stĺpcov v rozsahu (napríklad: stĺpček B do stĺpca E), vrátený výsledok by bol NEPRAVDA .
Vzorec by sa zmenil - takže stĺpec E by sa zmenil na stĺpec F - a samozrejme, v stĺpci F nič nie je, takže vzorec by zakaždým vrátil NEPRAVDU.
Musíme sa uistiť, že vzorec sa pozerá IBA na stĺpec E - ale to je na pohľad na správny riadok - takže keď skopírujeme vzorec naprieč, stĺpec E zostane rovnaký. Aby sme to mohli urobiť, musíme použiť zmiešanú referenciu, ktorá zaistí stĺpec na mieste.
= $ E4 = „Po splatnosti“
Keď to teraz skopírujeme do 4 stĺpcov, stĺpec vo vzorci zostane rovnaký, ale riadok sa zmení.
Keď náš vzorec teraz funguje v pracovnom hárku, môžeme vytvoriť vlastný vzorec v podmienenom formátovaní.
Keď klikneme OKa Použiť, naše riadky, ktorých stĺpec E hovorí po termíne, budú zvýraznené.
