Excel voorwaardelike formatteringsformules

Deur voorwaardelike formatering in Excel toe te voeg, kan jy verskillende formatering opsies toepas op 'n sel of reeks selle wat aan spesifieke voorwaardes voldoen wat jy gestel het.

Die formatering opsies word slegs toegepas wanneer die geselekteerde selle aan hierdie stelvoorwaardes voldoen.

Die formatering opsies wat toegepas kan word, sluit in lettertipe- en agtergrondkleurveranderinge, fontstyle, selgrense, en die toevoeging van getalformatering aan data.

Sedert Excel 2007 het Excel 'n aantal ingeboude opsies vir algemeen gebruikte toestande soos die vind van nommers wat groter of kleiner is as 'n sekere waarde of die vind van nommers wat bo of onder die gemiddelde waarde is .

Benewens hierdie vooraf ingestelde opsies, is dit ook moontlik om aangepaste voorwaardelike formateringreëls te skep met behulp van Excel-formules om te toets vir gebruikers-gespesifiseerde toestande.

Toepassing van verskeie reëls

Meer as een reël kan op dieselfde data toegepas word om vir verskillende toestande te toets. Byvoorbeeld, begrotingdata kan voorwaardes hê wat die formatteringsveranderings van toepassing is wanneer sekere vlakke - soos 50%, 75% en 100% - van die totale begroting bestee word.

In sulke omstandighede bepaal Excel eers of die verskillende reëls strydig is. As dit so is, volg die program 'n voorafbepaalde volgorde om te bepaal watter voorwaardelike formatteringsreël op die data toegepas word.

Voorbeeld: Die vind van data wat 25% en 50% oorskry, word verhoog met voorwaardelike formatering

In die volgende voorbeeld sal twee aangepaste voorwaardelike formatteringsreëls toegepas word op die reeks selle B2 tot B5.

• Die eerste reël kontroleer of die data in selle A2 tot A5 groter is as die ooreenstemmende waarde in B2 tot B5 met meer as 25%.
• Die tweede reël kontroleer of dieselfde data in A2: A5 die ooreenstemmende waarde in B2: B5 met meer as 50% oorskry.

Soos in die bostaande beeld gesien kan word, sal die agtergrondkleur van die sel of selle in die reeks B1: B4 verander as een van die bogenoemde toestande waar is.

• Vir data waar die verskil groter as 25% is, sal die agtergrondkleur van die sel na blou verander.
• As die verskil groter as 50% is, sal die agtergrondkleur van die sel na rooi verander.

Die reëls wat gebruik word om hierdie taak te bereik,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

sal ingevoer word deur die dialoogkassie Nuwe formateringreël te gebruik .

Voer die tutoriaal data in

1. Voer die data in selle A1 tot C5 in, soos in die prent hierbo gesien

Nota: Stap 3 van die tutoriaal voeg formules by selle C2: C4 wat die presiese persentasie verskil tussen die waardes in selle A2: A5 en B2: B5 toon om die akkuraatheid van die voorwaardelike formatteringsreëls na te gaan.

Die voorstelling van die kondisionele formatering reëls

Soos voorheen genoem, sal die voorwaardelike formatteringsreëls wat vir die twee toestande kyk, ingeskryf word deur die dialoog Nuwe Voorskrifreëlvoorwaardelik te gebruik .

Stel voorwaardelike formatering om meer as 25% toename te vind

1. Merk selle B2 tot B5 in die werkblad.
2. Klik op die Tuisblad van die lint.
3. Klik op die Voorwaardelike Formatering- ikoon in die lint om die aftreklys te open.
4. Kies Nuwe reël om die dialoogkassie Nuwe formateringreël oop te maak, soos gesien in die prent hierbo.
5. Klik in die boonste helfte van die dialoogkassie op die laaste opsie: Gebruik 'n formule om te bepaal watter selle om te formateer.
6. In die onderste helfte van die dialoogkassie, klik in die Formaat waardes waar hierdie formule waar is: reël.
7. Tik die formule : = (A2-B2) / A2> 25% in die spasie wat voorsien word
8. Klik op die Grootte knoppie om die dialoog Formaat selle oop te maak.
9. Kliek in die dialoogkassie op die blad Vul en kies 'n blou vulkleur.
10. Klik twee keer op OK om die dialoogvensters te sluit en terug te keer na die werkblad.
11. Op hierdie stadium moet die agtergrondkleur van selle B3 en B5 blou wees.

Stel voorwaardelike formatering in om meer as 50% te verhoog

1. Met selle B2 tot B5 nog steeds gekies, herhaal stap 1 tot 6 hierbo.
2. Tik die formule: = (A2-B2) / A2> 50% in die spasie wat voorsien word.
3. Klik op die Grootte knoppie om die dialoog Formaat selle oop te maak.
4. Klik op die blad Vul en kies 'n rooi vulkleur.
5. Klik twee keer op OK om die dialoogvensters te sluit en terug te keer na die werkblad .
6. Die agtergrondkleur van sel B3 moet steeds blou wees wat aandui dat die persentasie verskil tussen die getalle in selle A3 en B3 groter is as 25%, maar minder as of gelyk aan 50%.
7. Die agtergrondkleur van sel B5 moet verander na rooi wat aandui dat die persentasie verskil tussen die getalle in selle A5 en B5 groter is as 50%.

Kontroleer voorwaardelike formatteringsreëls

Bereken% Verskil

Om seker te maak dat die voorwaardelike formatering reëls ingevoer is, kan ons formules in selle C2: C5 invoer. Die berekening van die presiese persentasie verskil tussen die getalle in die reekse A2: A5 en B2: B5.

1. Klik op sel C2 om dit die aktiewe sel te maak.
2. Tik die formule = (A2-B2) / A2 in en druk die Enter-sleutel op die sleutelbord.
3. Die antwoord 10% moet in sel C2 verskyn, wat aandui dat die getal in sel A2 10% groter is as die getal in sel B2.
4. Dit kan nodig wees om die formatering op sel C2 te verander om die antwoord as 'n persentasie te vertoon.
5. Gebruik die vulhendel om die formule van sel C2 na selle C3 tot C5 te kopieer.
6. Die antwoorde vir selle C3 tot C5 moet wees: 30%, 25% en 60%.
7. Die antwoorde in hierdie selle toon dat die voorwaardelike formatering reëls geskep korrek is aangesien die verskil tussen selle A3 en B3 groter is as 25% en die verskil tussen selle A5 en B5 is groter as 50%.
8. Sel B4 het nie kleur verander nie omdat die verskil tussen selle A4 en B4 25% is en ons voorwaardelike formatteringsreël het bepaal dat 'n persentasie van meer as 25% nodig was vir die agtergrondkleur om na blou te verander.

Bestelling van voorrang vir voorwaardelike formatteringsreëls

Toepassing van Konflikterende Voorwaardelike Formatteringsreëls

Wanneer verskeie reëls op dieselfde reeks data toegepas word, bepaal Excel eers of die reëls strydig is.

Konflikreëls is dié waar die gekose formatteringsopsies vir elke reël nie beide op dieselfde data toegepas kan word nie.

In die voorbeeld wat in hierdie handleiding gebruik word, is die reëls in konflik omdat beide reëls dieselfde opsies gebruik, naamlik die verandering van die agtergrond sel kleur.

In die situasie waar die tweede reël waar is (die verskil in waarde is groter as 50% tussen twee selle), dan is die eerste reël (die verskil in waarde groter as 25%) ook waar.

Excel se volgorde van voorrang

Aangesien 'n sel nie gelyktydig 'n rooi en blou agtergrond kan hê nie, moet Excel weet watter voorwaardelike formatteringsreël dit moet toepas.

Watter reël word toegepas word bepaal deur die voorrangorde van Excel, wat bepaal dat die reël wat hoër is in die lys in die dialoogkassie Voorwaardelike formateringreëlsbestuurder voorrang het.

Soos hierbo getoon, is die tweede reël wat in hierdie handleiding gebruik word (= (A2-B2) / A2> 50%) hoër in die lys en het dus voorrang bo die eerste reël.

As gevolg hiervan word die agtergrondkleur van sel B5 verander na rooi.

Standaard word nuwe reëls by die bokant van die lys gevoeg en het dus 'n hoër prioriteit.

Om die volgorde van voorkeur te verander, gebruik die pyltjie omhoog en af ​​in die dialoogkassie soos geïdentifiseer in die prent hierbo.

Toepassing van Nie-Botsende Reëls

As twee of meer voorwaardelike formatteringsreëls nie in konflik is nie, word beide toegepas wanneer die toestand wat elke reël toets, waar word.

As die eerste voorwaardelike formatteringsreël in ons voorbeeld (= (A2-B2) / A2> 25%) die reeks selle B2: B5 met 'n blou grens in plaas van 'n blou agtergrondkleur geformateer het, sou die twee voorwaardelike formatering reëls nie bots nie albei formate kan toegepas word sonder om met die ander te meng.

As gevolg daarvan sal sel B5 beide 'n blou grens en 'n rooi agtergrondkleur hê, aangesien die verskil tussen die getalle in selle A5 en B5 groter is as beide 25 en 50 persent.

Voorwaardelike Formatering vs Gereelde Formatering

In die geval van konflikte tussen voorwaardelike formatering reëls en handmatig toegepaste formateringsopsies, het voorwaardelike formatering reël altyd voorrang en sal toegepas word in plaas van enige handmatig bygevoeg formatering opsies.

As 'n geel agtergrondkleur aanvanklik op selle B2 tot B5 in die voorbeeld toegepas is, het die selle B2 en B4, wanneer die voorwaardelike formatteringsreëls bygevoeg is, geel.

Aangesien die voorwaardelike formatteringsreëls van toepassing is op selle B3 en B5, sal hul agtergrondkleure onderskeidelik van geel na blou en rooi verander.