Excel

Preizkusite pogojno oblikovanje z lažnimi formulami

Test Conditional Formatting With Dummy Formulas

Hiter začetek | Primeri | Odpravljanje težav | Usposabljanje

Č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:





  1. Uredite pravilo
  2. Uredite formulo s svojim 'najboljšim ugibanjem'
  3. Shranite pravilo, da vidite, kaj se zgodi
  4. 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 formulami

Primer 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.

Težava - označite vrednosti nad 100 s pravilom pogojnega oblikovanja

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.

Kopirajte formule navzdol in navzdol

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 sklicev na formule

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:

Lažne formule prikazujejo TRUE, kjer bo uporabljeno 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.

Kopirajte prvo formulo v lažnem nizu

Nato izberite podatke in določite novo pravilo pogojnega oblikovanja.

Izberite podatke in začnite novo pravilo pogojnega oblikovanja

Izbrani podatki - upoštevajte, da je aktivna celica B4

Formulo prilepite v polje in nastavite obliko.

Lažna formula prilepljena, pravilo pripravljeno za shranjevanje

Pripravljeno shraniti novo pravilo

Uspeh! Vse celice z vrednostmi nad 100 označene:

Končni pogojni format z odstranjenimi lažnimi formulami

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'.

Težava - označite naloge s prednostjo

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:

Lažne formule - prvi poskus

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:

Lažne formule - drugi poskus - deluje!

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:

Izbrani podatki - upoštevajte, da je aktivna celica B5

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, novo pravilo pripravljeno za shranjevanje

Formula prilepljena iz G5

Kot lahko vidite, novo pravilo deluje prvič.

Končna oblika - označene vrstice, lažne formule odstranjene

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 mapa Primer pravila pravila CF 1.xlsx mapa Preizkusite pravilo CF.2.xlsx


^