Voorwaardelijke opmaak met formules (2024)

Voorwaardelijke opmaak is een fantastische manier om gegevens snel in een spreadsheet te visualiseren.Met voorwaardelijke opmaak kunt u dingen doen zoals markeerdatums in de komende 30 dagen, problemen met het invoeren van gegevens, rijen markeren die topklanten bevatten, duplicaten tonen en meer.

Excel wordt geleverd met een groot aantal "presets" die het gemakkelijk maken om nieuwe regels zonder formules te maken.U kunt echter ook regels maken met uw eigen aangepaste formules.Door uw eigen formule te gebruiken, neemt u de voorwaarde over die een regel activeert en kan precies de logica toepassen die u nodig hebt.Formules geven u maximale kracht en flexibiliteit.

Met behulp van de vooraf ingestelde "Equalo", is het gemakkelijk om cellen te markeren die gelijk zijn aan "Apple".

Maar wat als u cellen wilt markeren die gelijk zijn aan "appel" of "kiwi" of "limoen"?Natuurlijk kunt u een regel voor elke waarde maken, maar dat is veel problemen.Of functie:

Hier is het resultaat van de regel die wordt toegepast op het bereik B4: F8 in deze spreadsheet:

Hier is de exacte formule die wordt gebruikt:

= Of (BC = "Apple", bk = "kiwi", bk = "lime")

Snelle start

U kunt een op formules gebaseerde voorwaardelijke opmaakregel maken in vier eenvoudige stappen:

1. Selecteer de cellen die u wilt formatteren.

2. Maak een voorwaardelijke opmaakregel en selecteer de formuleoptie

3. Voer een formule in die waar of onwaar retourneert.

4. Stel opmaakopties in en sla de regel op.

DeIsodd -functieRetourneert alleen waar voor oneven getallen, waardoor de regel wordt geactiveerd:

Video:Hoe u voorwaardelijke opmaak kunt toepassen met een formule

Formula -logica

Formules die voorwaardelijke opmaak toepassen, moeten true of onwaar of numerieke equivalenten retourneren.Hier zijn enkele voorbeelden:

= Clogs (a1) = isNumber (a1) = a1> 100 = en (a1> 100, b1 <50) = of (f1 = "mn", f1 = "wi")

De bovenstaande formules retourneren allemaal waar of onwaar, zodat ze perfect werken als een trigger voor voorwaardelijke opmaak.

Wanneer voorwaardelijke opmaak wordt toegepast op een bereik van cellen, voert u celreferenties in met betrekking tot de eerste rij en kolom in de selectie (d.w.z. de linkerbovencel). De truc om te begrijpen hoe voorwaardelijke opmaakformules werken is om dezelfde formule te visualiseren zijntoegepast opElke cel in de selectie, met celreferenties zoals gewoonlijk bijgewerkt.Stel je voor dat je de formula hebt ingevoerd in de linkerbovencel van de selectie en vervolgens de formule over de hele selectie heeft gekopieerd.Als je hiermee worstelt, zie het gedeelte overDummy -formulesonderstaand.

Formula -voorbeelden

Hieronder staan voorbeelden van aangepaste formules die u kunt gebruiken om voorwaardelijke opmaak toe te passen.Sommige van deze voorbeelden kunnen worden gemaakt met behulp van Excel's ingebouwde presets voor het markeren van cellen, maar aangepaste formules kunnen veel verder gaan dan voorinstellingen, zoals u hieronder kunt zien.

Markeer bestellingen uit Texas

Gebruik een formule die de referentie vergrendelt naar kolom F: om rijen uit Texas (afgekort TX) te markeren.

= $ F5 = "tx"

Zie dit artikel voor meer informatie:Markeer rijen met voorwaardelijke opmaak.

Video:Hoe rijen te markeren met voorwaardelijke opmaak

Markeer datums in de komende 30 dagen

Om datums te benadrukken die in de komende 30 dagen plaatsvinden, hebben we een formule nodig die (1) ervoor zorgt dat datums in de toekomst zijn en (2) ervoor zorgen dat datums vanaf vandaag 30 dagen of minder zijn.Een manier om dit te doen, is door deEn functiesamen met deNu functionerensoortgelijk:

= En (b4> nu (), b4 <= (nu ()+30))

Met een huidige datum van 18 augustus 2016 belicht de voorwaardelijke opmaak als volgt:

DeNu functionerenRetourneert de huidige datum en tijd.Zie dit artikel voor meer informatie over hoe deze formule werkt:Markeer datums in de komende n dagen.

Markeer kolomverschillen

Gezien twee kolommen die vergelijkbare informatie bevatten, kunt u voorwaardelijke opmaak gebruiken om onderverscharde verschillen te spotten.De formule die wordt gebruikt om de onderstaande opmaak te activeren, is:

= $ Bc <> $ cc

Zie ook:Een versie van deze formule die de exacte functie gebruikt om een case-gevoelige vergelijking te doen.

Markeer ontbrekende waarden

Om waarden in de ene lijst te markeren die bij de andere ontbreken, kunt u een formule gebruiken op basis van deCOUNTIF -functie:

= Countif (lijst, b5) = 0

Deze formule controleert eenvoudig elke waarde inMaak een lijst van eentegen waarden in het genoemde bereik "lijst" (d5: d10).Wanneer de telling nul is, retourneert de formule waar en activeert de regel, die waarden in de hoogte brengtMaak een lijst van eendie ontbreken inLijst B.

Video:Hoe u ontbrekende waarden kunt vinden met Countif

Markeer eigenschappen met 3+ slaapkamers onder $ 350k

Om eigenschappen in deze lijst te vinden die minimaal 3 slaapkamers hebben, maar minder dan $ 300.000 zijn, kunt u een formule gebruiken op basis van de en functie:

= En ($ C5 <350000, $ d5> = 3)

De dollartekens ($) vergrendelen de verwijzing naar kolommen C en D en deEn functiewordt gebruikt om ervoor te zorgen dat beide conditions waar zijn.In rijen waar de en functie waar retourneert, wordt de voorwaardelijke opmaak toegepast:

Markeer topwaarden (dynamisch voorbeeld)

Hoewel Excel voorinstellingen heeft voor "topwaarden", laat dit voorbeeld zien hoe hetzelfde te doen met een formule en hoe formules flexibeler kunnen zijn.Door een formule te gebruiken, kunnen we het werkblad interactief maken - wanneer de waarde in F2 wordt bijgewerkt, reageert de regel onmiddellijk en belicht nieuwe waarden.

De formule die voor deze regel wordt gebruikt, is:

= B4> = groot (gegevens, invoer)

Waar "data" het genoemde bereik B4: G11 is en "invoer" het genoemde bereik F2 is.Deze pagina heeftDetails en een volledige uitleg.

Gantt -hitlijsten

Geloof het of niet, u kunt zelfs formules gebruiken om eenvoudige Gantt -grafieken te maken met voorwaardelijke opmaak zoals deze:

Dit werkblad maakt gebruik van twee regels, één voor de bars en een voor de weekendschaduw:

= En (d $ 4> = $ b5, d $ 4 <= $ c5) // bars = weekdag (d $ 4,2)> 5 // weekends

Dit artikel legt de formule voor bars uit, EnDit artikel legt de formule uit voor weekenddaden.

Eenvoudig zoekvak

Een coole truc die je kunt doen met voorwaardelijke opmaak is het bouwen van een eenvoudig zoekvak.In dit voorbeeld benadrukt een regel cellen in kolom B die tekst bevatten getypt in cel F2:

De gebruikte formule is:

= IsNumber (zoeken ($ f $ 2, b2)))

Zie voor meer informatie en een volledige uitleg:

• Artikel:Hoe cellen te markeren die specifieke tekst bevatten
• Artikel:Hoe rijen te markeren die specifieke tekst bevatten
• Video:Hoe u een zoekvak kunt bouwen om gegevens te markeren

Probleemoplossen

Als u uw voorwaardelijke opmaakregels niet correct kunt laten schieten, is er waarschijnlijk een probleem met uw formule.Zorg er eerst voor dat u de formule hebt gestart met een gelijkteken (=).Als je deze stap vergeet, zal Excel zwijgend je hele formule omzetten in tekst, waardoor deze nutteloos wordt.Verwijder gewoon de dubbele aanhalingstekens die Excel aan beide kanten heeft toegevoegd en zorg ervoor dat de formule begint met gelijken (=).

Als uw formeel correct is ingevoerd, maar de regel niet activeert, heeft u misschien een beetje dieper.Normaal gesproken kunt u de F9 -toets gebruiken om resultaten te controleren in een formule -functie om de evaluatiefunctie te gebruiken om een formule te doorlopen.Helaas kunt u deze tools niet gebruiken met voorwaardelijke opmaakformules, maar u kunt een techniek gebruiken genaamd "dummy -formules".

Dummy -formules

Dummy -formules zijn een manier om uw voorwaardelijke opmaakformules rechtstreeks op het werkblad te testen, zodat u kunt zien wat ze eigenlijk doen.Dit kan een grote tijdsbesparing zijn als je moeite hebt om celreferenties correct te laten werken.

Kortom, u voert dezelfde formule in over een reeks cellen die overeenkomen met de vorm van uw gegevens.Hiermee kunt u de waarden zien die door elke formule worden geretourneerd, en het is een geweldige manier om te visualiseren en te begrijpen hoe op formules gebaseerde voorwaardelijke opmaak werkt.Voor een gedetailleerde uitleg,Zie dit artikel.

Video:Test voorwaardelijke opmaak met dummy -formules

Beperkingen

Er zijn enkele beperkingen die worden geleverd met op formules gebaseerde voorwaardelijke opmaak:

1. U kunt geen pictogrammen, kleurenschalen of gegevensbars toepassen met een aangepaste formule.U bent beperkte tostandard celopmaak, inclusief nummerindelingen, lettertype, vul Colorand Border -opties.
2. U kunt bepaalde formuleconstructies zoals unie, kruispunten of arrayconstanten niet gebruiken voor voorwaardelijke opmaakcriteria.
3. U kunt niet verwijzen naar andere werkboeken in een voorwaardelijke opmaakformule.

Je kunt soms rond #2 en #3 werken.Mogelijk kunt u de logica van de formule verplaatsen naar een cel in het werkblad en vervolgens naar die cel in de formule verwijzen.Als u probeert een arrayconstante te gebruiken, probeer dan in plaats daarvan een benoemd bereik te maken.

Meer CF -formulebronnen

• Voorbeelden van meer dan 30 voorwaardelijke opmaakformules
• Videotraining met oefenwerkbladen