Če ste kdaj uporabili pogojno oblikovanje s svojo formulo, veste, da je najtežje zagotoviti, da formula dejansko deluje.
Težava je v tem, da območje formule v pravilu pogojnega oblikovanja ni zelo prijazno. Ne dobite označenih sklicev na celice, ne dobite samodejnega dokončanja funkcije ... heck .... niti nasvetov na zaslonu ne dobite.
Posledično je težko 'videti', ali bo formula delovala, dokler pravila ne shranite. Če se to ne zgodi, morate uporabiti poskus in napako:
- Uredite pravilo
- Uredite formulo s svojim 'najboljšim ugibanjem'
- Shranite pravilo, da vidite, kaj se zgodi
- Po potrebi ponovite
To ni zelo zabavno in lahko je res frustrirajuće, ko naletite na težavno težavo.
Na srečo obstaja enostavna rešitev: lažne formule.
Boljši način - preizkusite z lažnimi formulami
Pri bolj zapletenih formulah pogojnega oblikovanja je ključno, da pravilo najprej preizkusite z „lažnimi“ formulami, preden ustvarite pravilo. To se sprva morda zdi nemogoče - kako lahko preizkusite formulo za pogojno oblikovanje brez uporabe pogojne oblike?
Trik je v razumevanju, da si lahko pogojno oblikovanje predstavljate kot 'prekrivanje' nevidnih formul, ki sedijo na vrhu celic. Ko formula v prekrivnem elementu za določeno celico vrne TRUE, se uporabi oblikovanje.
Torej, če želite preizkusiti pravilo pogojnega oblikovanja, morate na delovnem listu zgraditi niz „lažnih“ formul, ki simulirajo prekrivanje.
kako izbrisati podatkovno tabelo v
Preskusne formule rad postavim ob strani podatkov, poravnanih z vrsticami. To olajša nastavitev in ujemanje referenc.
Nato preprosto napišite prvo formulo tako, da se v podatkih sklicujete na zgornjo levo celico. Ko bo ustvarjeno pravilo pogojnega oblikovanja, bo to aktivna celica.
Video: Preizkusite pogojno oblikovanje z lažnimi formulamiPrimer 1 - Enostavna formula
Na primer, recimo, da imate v tabeli številke in želite poudariti vrednosti nad 100.
Opomba: Excel vsebuje 'prednastavitev' za pogojno oblikovanje, ki bo poudarjala vrednosti 'večje od', zato za to ni treba uporabiti formule. Kot primer uporabljamo samo osnovno formulo.
Na desni imamo dovolj prostora, zato bomo tja dodali naše lažne formule. V celico H4 dodajte prvo formulo. V tem primeru želimo uporabiti:
=B4>100
Zakaj B4? Ker B4 ustreza aktivni celici, ki jo bomo imeli, ko bomo opredelili dejansko pravilo pogojnega oblikovanja.
Zdaj kopirajte formulo navzdol in navzdol. Kopirati morate le toliko vrstic, kolikor jih želite preizkusiti. V tem primeru lahko z majhnim naborom podatkov enostavno preizkusimo vse vrstice.
Upoštevajte, da v vsaki celici dobimo vrednost TRUE ali FALSE. Če preverimo nekaj referenc, lahko vidite, da vsaka formula ocenjuje celico v podatkih glede na B4. Vse sklice na B4 so se spremenile, saj je bil B4 vnesen kot relativni naslov.
Preverjanje referenc - vsaka formula se nanaša na celico glede na B4
Zdaj si preprosto predstavljajte, da so ti rezultati preneseni neposredno na vrh podatkov. Kjer koli vidite vrednost TRUE, bo uporabljeno pogojno oblikovanje:
Upoštevajte, da vrednosti TRUE pravilno označujejo vrednosti> 100 v podatkih (ročno označeno)
kako zaščititi stolpce v
Lažna formula izgleda dobro, zato jo preizkusimo v pravilu pogojnega oblikovanja.
Najprej kopirajte formulo v zgornjo levo celico lažnih formul - to je H4 v tem primeru.
Nato izberite podatke in določite novo pravilo pogojnega oblikovanja.
Izbrani podatki - upoštevajte, da je aktivna celica B4
Formulo prilepite v polje in nastavite obliko.
Pripravljeno shraniti novo pravilo
Uspeh! Vse celice z vrednostmi nad 100 označene:
Končno pogojno oblikovanje, uporabljeno s formulo, pri čemer so lažne formule odstranjene.
Primer 2 - bolj zapletena formula
To je bil preprost primer, zato poskusimo z istim pristopom z bolj zapleteno formulo.
Ustvarimo pravilo, ki označuje vrstice v tabeli na podlagi vrednosti v enem stolpcu. V tem primeru bomo označili naloge s prednostjo 'A'.
Označiti je treba vse vrstice s prednostjo 'A'
To je klasična težava pri pogojnem oblikovanju. Formula bo zahtevala mešano sklicevanje, vendar je mešane reference težko razumeti, če na delovnem listu ne vidite sklicev. Vendar pa lahko z uporabo lažnih formul preprosto preizkusimo in izpopolnimo pravilo.
Kot prej je prvi korak ugotoviti, kam postaviti preskusne formule. Na desni strani imamo dovolj prostora, zato bomo začeli v celici G5.
Ker želimo poudariti naloge s prednostjo 'A', bomo za začetek uporabili to formulo:
=B5='A'
Ko kopiram formule navzdol in navzdol, imamo naslednje:
Ne bo delovalo - označene bodo samo vrednosti v stolpcu B (oranžno senčenje ročno uporabljeno)
Upoštevajte, da smo dobili rezultat TRUE, kjer je prioriteta 'A', vendar le za vrednosti v stolpcu B. To je dober začetek, vendar bo označil samo celice v prvem stolpcu.
Formulo moramo prilagoditi tako, da vrne TRUE za celotno vrstico. Če želite to narediti, moramo v formuli uporabiti mešano referenco, da zaklenemo stolpec. Revidirana formula je:
=$B5='A'
Ko kopiram to novo formulo v našem testnem območju, dobimo tisto, kar potrebujemo:
Ko je stolpec zaklenjen, dobimo celo vrstico TRUE, ko je prioriteta 'A' (oranžno senčenje ročno uporabljeno)
Oglejte si, kako bodo delovale lažne formule? Predstavljajte si jih kot prekrivanje podatkov samih.
Ustvarimo pravilo pogojnega oblikovanja. Najprej izberite podatke:
Podatki so izbrani in pripravljeni za ustvarjanje novega pravila (upoštevajte, aktivna celica je B5)
kako nastaviti strani v
Na koncu ustvarimo pravilo s formulo v zgornjem levem kotu:
Formula prilepljena iz G5
Kot lahko vidite, novo pravilo deluje prvič.
Pogojno oblikovanje deluje po pričakovanjih (lažne formule so odstranjene)
Zaključek
Naslednjič, ko boste morali uporabiti pogojno oblikovanje z bolj zapleteno formulo, nastavite lažne formule poleg podatkov in natančno prilagodite formulo, dokler ne dobite TRUE vrednosti, kjer jih potrebujete. Če delate neposredno na delovnem listu, imate poln dostop do vseh Excelovih orodij za formule, formulo pa lahko preprosto odpravite in prilagodite, dokler ne deluje brezhibno.
Več formul za pogojno oblikovanje si oglejte tukaj Avtor Dave Bruns Priloge Primer pravila pravila CF 1.xlsx Preizkusite pravilo CF.2.xlsx