Otestujte podmienené formátovanie pomocou fiktívnych vzorcov

Ak ste niekedy použili podmienené formátovanie pomocou vlastného vzorca, viete, že najťažšie je ubezpečiť sa, že vzorec skutočne funguje.

Problém je v tom, že oblasť vzorca v pravidle podmieneného formátovania nie je príliš priateľská. Nezískate zvýraznené odkazy na bunky, nezískate automatické dopĺňanie funkcií ... sakra .... nedostanete ani tipy na obrazovku.

V dôsledku toho je ťažké „zistiť“, či vzorec bude fungovať, kým neuložíte pravidlo. Ak nie, musíte použiť pokus a omyl:

1. Upraviť pravidlo
2. Upravte vzorec pomocou „najlepšieho odhadu“
3. Uložte si pravidlo, aby ste videli, čo sa stane
4. Opakujte podľa potreby

Nie je to veľmi zábavné a môže to byť aj frustrujúce, keď narazíte na záludný problém.

Našťastie existuje jednoduchá oprava: atrapy vzorcov.

Lepší spôsob - test s atrapami vzorcov

Pri komplikovanejších vzorcoch podmieneného formátovania je kľúčové najskôr otestovať pravidlo pomocou „fiktívnych“ vzorcov a až potom pravidlo vytvoriť. Na začiatku sa to môže zdať nemožné - ako môžete otestovať vzorec podmieneného formátovania bez použitia podmieneného formátu?

Ide o to, pochopiť, že podmienené formátovanie si môžete predstaviť ako „prekrytie“ neviditeľných vzorcov, ktoré sú umiestnené nad bunkami. Keď vzorec v prekrytí vráti pre danú bunku hodnotu TRUE, použije sa formátovanie.

Na otestovanie pravidla podmieneného formátovania teda stačí vytvoriť na pracovnom hárku sadu „fiktívnych“ vzorcov, ktoré simulujú prekrytie.

prevádzať čísla na text v programe Excel

Rád testovacie vzorce umiestnim na stranu údajov, zoradenú s riadkami. To uľahčuje nastavenie a porovnávanie referencií.

Potom jednoducho napíšte prvý vzorec odkazom na ľavú hornú bunku v dátach. Toto bude aktívna bunka pri vytváraní pravidla podmieneného formátovania.

Príklad 1 - Jednoduchý vzorec

Povedzme napríklad, že máte čísla v tabuľke a chcete zvýrazniť hodnoty nad 100.

Poznámka: Excel obsahuje „prednastavené“ podmienené formátovanie, ktoré zvýrazní hodnoty „väčšie ako“, takže na to nie je potrebné používať vzorec. Ako príklad používame základný vzorec.

Napravo máme dostatok priestoru, takže tam pridáme svoje fiktívne vzorce. Do bunky H4 pridajte prvý vzorec. V tomto prípade chceme použiť:

 
=B4>100

Prečo B4? Pretože B4 zodpovedá aktívnej bunke, ktorú budeme mať, keď definujeme skutočné pravidlo podmieneného formátovania.

Teraz skopírujte vzorec hore a dole. Stačí skopírovať toľko riadkov, koľko chcete otestovať. V tomto prípade s malou sadou údajov môžeme ľahko otestovať všetky riadky.

Všimnite si, že v každej bunke dostaneme hodnotu TRUE alebo FALSE. Ak skontrolujeme niekoľko odkazov, môžete vidieť, že každý vzorec hodnotí bunku v dátach vzhľadom na B4. Všetky odkazy na B4 sa zmenili, pretože B4 bol zadaný ako relatívna adresa.

Kontrola odkazov - každý vzorec odkazuje na bunku vzhľadom na B4

Teraz si jednoducho predstavte, že tieto výsledky sú transponované priamo nad údaje. Kdekoľvek uvidíte hodnotu TRUE, použije sa podmienené formátovanie:

Všimnite si, že PRAVÉ hodnoty v údajoch správne označujú hodnoty> 100 (ručne zvýraznené)

pridať do rozbaľovacieho zoznamu programu Excel

Falošný vzorec vyzerá dobre, takže si ho vyskúšame v pravidle podmieneného formátovania.

Najprv skopírujte vzorec v ľavej hornej bunke figurínových vzorcov - to je v tomto prípade H4.

Ďalej vyberte údaje a definujte nové pravidlo podmieneného formátovania.

Vybraté údaje - aktívna bunka je B4

Vložte vzorec do poľa a nastavte formát.

Nové pravidlo je pripravené uložiť

Úspech! Všetky bunky so zvýraznenými hodnotami nad 100:

Konečné podmienené formátovanie použité so vzorcom, s odstránenými atrapami vzorcov.

Príklad 2 - zložitejší vzorec

To bol jednoduchý príklad, takže skúsme rovnaký prístup s komplikovanejším vzorcom.

Vytvorme pravidlo, ktoré zvýrazní riadky v tabuľke na základe hodnoty v jednom stĺpci. V tomto prípade zvýrazníme úlohy s prioritou „A“.

Je potrebné zvýrazniť všetky riadky s prioritou „A“

Toto je klasický problém pri podmienenom formátovaní. Vzorec bude vyžadovať zmiešanú referenciu, ale zmiešané referencie môžu byť ťažko zrozumiteľné, keď v pracovnom hárku nemôžete vidieť odkazy. Použitím fiktívnych vzorcov však môžeme ľahko otestovať a zdokonaliť pravidlo.

Ako predtým, prvým krokom je zistiť, kam umiestniť testovacie vzorce. Vpravo máme dostatok miesta, takže začneme v bunke G5.

Pretože chceme zvýrazniť úlohy s prioritou „A“, použijeme na začiatok tento vzorec:

 
=B5='A'

Potom, čo skopírujem vzorce hore a dole, máme toto:

Nefunguje to - zvýraznia sa iba hodnoty v stĺpci B (oranžové tieňovanie sa použije ručne)

Všimnite si, že dostávame výsledok TRUE, kde je priorita 'A', ale iba pre hodnoty v stĺpci B. Je to dobrý začiatok, ale zvýrazní iba bunky v prvom stĺpci.

Vzorec musíme upraviť tak, aby vracal hodnotu TRUE pre celý riadok. Aby sme to urobili, na zamknutie stĺpca musíme použiť zmiešanú referenciu vo vzorci. Revidovaný vzorec je:

 
=$B5='A'

Keď skopírujem tento nový vzorec do nášho testovacieho rozsahu, dostaneme to, čo potrebujeme:

So zamknutým stĺpcom získame celý riadok TRUE, keď je priorita „A“ (oranžové tieňovanie bolo použité ručne)

Vidíte, ako budú fungovať figuríny? Predstavte si ich ako prekrytie samotných údajov.

Teraz vytvoríme pravidlo podmieneného formátovania. Najprv vyberte údaje:

Údaje sú vybraté a pripravené na vytvorenie nového pravidla (aktívna bunka je B5)

ako pridať trendovú čiaru v programe Excel 2016

Nakoniec vytvoríme pravidlo pomocou vzorca v ľavom hornom rohu:

Vzorec prilepený z G5

Ako vidíte, nové pravidlo funguje perfektne na prvýkrát.

Podmienené formátovanie funguje podľa očakávania (fiktívne vzorce odstránené)

Záver

Nabudúce budete musieť použiť podmienené formátovanie s komplikovanejším vzorcom, nastaviť vedľa údajov fiktívne vzorce a doladiť vzorec, kým nedostanete PRAVÉ hodnoty tam, kde ich potrebujete. Vďaka práci priamo na pracovnom hárku máte plný prístup ku všetkým nástrojom vzorca v programe Excel a môžete ľahko odstraňovať problémy a upravovať vzorec, kým nebude fungovať perfektne.

Testujte pravidlo CF príklad1.xlsx

Testujte pravidlo CF príklad2.xlsx