Deur voorwaardelike formatering in Excel by te voeg, kan jy verskillende formateringopsies toepas op 'n sel, of reeks selle, wat voldoen aan spesifieke voorwaardes wat jy stel. Deur sulke voorwaardes te stel, kan dit help om jou sigblad te organiseer en dit makliker te maak om te skandeer. Die formateringopsies wat jy kan gebruik, sluit in lettertipe- en agtergrondkleurveranderinge, lettertipestyle, selgrense en die byvoeging van getalformatering by data.
Excel het ingeboude opsies vir algemeen gebruikte toestande, soos om getalle te vind wat groter of minder as 'n spesifieke waarde is of om getalle te vind wat bo of onder die gemiddelde waarde is. Benewens hierdie voorafopgestelde opsies, kan u ook persoonlike voorwaardelike formateringreëls skep deur Excel-formules te gebruik.
Hierdie instruksies is van toepassing op Excel 2019, 2016, 2013, 2010 en Excel vir Microsoft 365.
Toepassing van veelvuldige voorwaardes in Excel
Jy kan meer as een reël op dieselfde data toepas om vir verskillende toestande te toets. Byvoorbeeld, begrotingdata kan voorwaardes gestel hê wat formateringsveranderinge toepas wanneer sekere vlakke van besteding bereik word, soos 50%, 75% en 100%, van die totale begroting.
In sulke omstandighede bepaal Excel eers of die verskillende reëls bots, en, indien wel, volg die program 'n vasgestelde volgorde van voorkeur om te bepaal watter voorwaardelike formateringreël op die data van toepassing moet wees.
Vind data wat 25% oorskry en 50%-toenames
In die volgende voorbeeld sal twee pasgemaakte voorwaardelike formateringreëls toegepas word op die reeks selle B2 tot B5.
- Die eerste reël kyk of die data in selle A2:A5 groter is as die ooreenstemmende waarde in B2:B5 deur meer as 25%.
- Die tweede reël kyk of dieselfde data in A2:A5 die ooreenstemmende waarde in B2:B5 met meer as oorskry 50%.
Soos gesien kan word in die prent hierbo, as enige van die bogenoemde voorwaardes waar is, sal die agtergrondkleur van die sel of selle in die reeks B1:B4 verander.
- Vir data waar die verskil meer as 25% is, sal die selagtergrondkleur na groen verander.
- As die verskil groter as 50% is, sal die selagtergrondkleur na rooi verander.
Die reëls wat gebruik word om hierdie taak uit te voer, sal ingevoer word deur die Nuwe formateringreël dialoogkassie. Begin deur die voorbeelddata in selle A1 tot C5 in te voer, soos gesien in die prent hierbo.
In die laaste gedeelte van die tutoriaal sal ons formules by selle voeg C2:C4 wat die presiese persentasie verskil tussen die waardes in selle toon A2:A5 en B2:B5; dit sal ons toelaat om die akkuraatheid van die voorwaardelike formateringreëls na te gaan.
Stel voorwaardelike formateringreëls
Eers sal ons voorwaardelike formatering toepas om 'n beduidende verhoging van 25 persent of meer te vind.
Die funksie sal soos volg lyk:
=(A2-B2)/A2>25%
- Hoogtepunt selle B2 na B5 in die werkblad.
- Klik op die Tuisoortjie van die lint.
- Klik op die Conditional Formatting-ikoon in die ribbon om die aftreklys oop te maak.
-
Kies Nuwe reël om die New Formatting Rule dialoogkassie oop te maak.
- Onder Kies 'n reëltipe, klik die laaste opsie: Gebruik 'n formule om te bepaal watter selle om te formateer.
- Tik die formule hierbo in die spasie hieronder in Formateer waardes waar hierdie formule waar is:
- Klik die Format-knoppie om die dialoogkassie oop te maak. Klik die Vul-oortjie en kies 'n kleur.
- Klik OK om die dialoogkassies toe te maak en terug te keer na die werkblad.
- Die agtergrondkleur van selle B3 en B5 moet verander na die kleur wat jy gekies het.
Nou, ons sal voorwaardelike formatering toepas om 'n 50 persent of meer verhoging te vind. Die formule sal so lyk:
- Herhaal die eerste vyf stappe hierbo.
-
Tik die formule wat hierbo verskaf word in die spasie hieronder Formateer waardes waar hierdie formule waar is:
- Klik die Format-knoppie om die dialoogkassie oop te maak. Klik die Vul-oortjie en kies 'n ander kleur as wat jy in die vorige stel stappe gedoen het.
- Klik OK om die dialoogkassies toe te maak en terug te keer na die werkblad.
Die agtergrondkleur van sel B3 moet dieselfde bly, wat aandui dat die persentasie verskil tussen die getalle in selle A3 enB3 is groter as 25 persent maar minder as of gelyk aan 50 persent. Die agtergrondkleur van sel B5 moet verander na die nuwe kleur wat jy gekies het, wat aandui dat die persentasie verskil tussen die getalle in selle A5 en B5 is groter as 50 persent.
Kontroleer voorwaardelike formateringreëls
Om te verifieer dat die voorwaardelike formateringreëls wat ingevoer is korrek is, kan ons formules in selle C2:C5 invoer wat die presiese persentasieverskil tussen die getalle in die reeksesal bereken A2:A5 en B2:B5.
Die formule in sel C2 lyk soos volg:
=(A2-B2)/A2
- Klik op sel C2 om dit die aktiewe sel te maak.
- Tik die formule hierbo en druk die Enter sleutel op die sleutelbord.
- Die antwoord 10% moet in sel C2 verskyn, wat aandui dat die nommer in sel A2 10% groter is as die getal in sel B2.
- Dit mag nodig wees om die formatering op sel C2 te verander om die antwoord as 'n persentasie te vertoon.
- Gebruik die vul-handvatsel om die formule van sel C2 na selle C3 na te kopieer C5.
- Die antwoorde vir selle C3 tot C5 moet 30%, 25% en 60% wees.
Die antwoorde in hierdie selle wys dat die voorwaardelike formateringreëls akkuraat is aangesien die verskil tussen selle A3 en B3 groter as 25 is persent, en die verskil tussen selle A5 en B5 is groter as 50 persent.
Sel B4 het nie van kleur verander nie omdat die verskil tussen selle A4 en B4 gelyk is 25 persent, en ons voorwaardelike formateringreël het gespesifiseer dat 'n persentasie groter as 25 persent vereis word vir die agtergrondkleur om te verander.
Voorrangorde vir voorwaardelike formatering
Wanneer jy veelvuldige reëls op dieselfde reeks data toepas, bepaal Excel eers of die reëls bots. Botsende reëls is dié waar die formateringopsies nie albei op dieselfde data toegepas kan word nie.
In ons voorbeeld bots die reëls aangesien albei dieselfde formatering-opsie gebruik - die agtergrondselkleur verander.
In die situasie waar die tweede reël waar is (die verskil in waarde is meer as 50 persent tussen twee selle), dan is die eerste reël (die verskil in waarde is groter as 25 persent) ook waar.
Aangesien 'n sel nie albei twee verskillende kleuragtergronde gelyktydig kan hê nie, moet Excel weet watter voorwaardelike formateringreël dit moet toepas.
Excel se rangorde bepaal dat die reël wat hoër in die lys in die Voorwaardelike Formatering Reëls Bestuurder dialoogkassie is, eerste toegepas word.
Soos getoon in die prent hierbo, is die tweede reël wat in hierdie tutoriaal gebruik word hoër in die lys en het dus voorrang bo die eerste reël. Gevolglik is die agtergrondkleur van sel B5 groen.
By verstek gaan nuwe reëls na die bokant van die lys; om die volgorde te verander, gebruik die Op en Af pyltjieknoppies in die dialoogkassie.
Toepassing van nie-botsende reëls
As twee of meer voorwaardelike formateringreëls nie bots nie, word albei toegepas wanneer die voorwaarde wat elke reël toets waar word.
As die eerste voorwaardelike formateringreël in ons voorbeeld die reeks selle B2:B5 met 'n oranje rand in plaas van 'n oranje agtergrondkleur geformateer het, sou die twee voorwaardelike formateringreëls nie konflik aangesien beide formate toegepas kan word sonder om met die ander in te meng.
Voorwaardelike formatering vs. gewone formatering
In die geval van konflik tussen voorwaardelike formateringreëls en handmatig toegepaste formateringopsies, geniet voorwaardelike formateringreël altyd voorrang en sal dit toegepas word in plaas van enige formateringopsies wat met die hand bygevoeg is.
