Formules gebruiken voor voorwaardelijke opmaak in Excel

Door voorwaardelijke opmaak toe te voegen in Excel, kunt u verschillende opmaakopties toepassen op een cel of een cellenbereik dat voldoet aan specifieke voorwaarden die u instelt. De opmaakopties worden alleen toegepast wanneer de geselecteerde cellen aan deze ingestelde voorwaarden voldoen.

De opmaakopties die kunnen worden toegepast, omvatten wijzigingen in lettertypen en achtergrondkleuren, letterstijlen, celranden en het toevoegen van nummeropmaak aan gegevens.

Excel heeft een aantal ingebouwde opties voor algemeen gebruikte voorwaarden, zoals het vinden van getallen die groter zijn of minder dan een bepaalde waarde of het vinden van getallen die boven of onder de gemiddelde waarde liggen.

Naast deze vooraf ingestelde opties, is het ook mogelijk om aangepaste voorwaardelijke opmaakregels te maken met behulp van Excel-formules om te testen op door de gebruiker opgegeven voorwaarden.

01 van 04

Meerdere regels toepassen in Excel

Meerdere regels kunnen op dezelfde gegevens worden toegepast om voor verschillende omstandigheden te testen. Voor budgetgegevens kunnen bijvoorbeeld voorwaarden zijn ingesteld die opmaakwijzigingen toepassen wanneer bepaalde niveaus, zoals 50%, 75% en 100%, van het totale budget worden uitgegeven.

In dergelijke omstandigheden bepaalt Excel eerst of de verschillende regels met elkaar in conflict zijn en, als dat het geval is, volgt het programma een ingestelde prioriteitsvolgorde om te bepalen welke voorwaardelijke opmaakregel op de gegevens wordt toegepast.

Gegevens zoeken die hoger zijn dan 25% en 50%

In het volgende voorbeeld worden twee aangepaste voorwaardelijke opmaakregels toegepast op het bereik van cellen B2 naar B5.

• De eerste regel controleert of de gegevens in cellen A2 naar A5 is groter dan de overeenkomstige waarde in B2 naar B5 met meer dan 25%.
• De tweede regel controleert of dezelfde gegevens in A2: A5 overschrijdt de overeenkomstige waarde in B2: B5 met meer dan 50%.

Zoals te zien is in de bovenstaande afbeelding, als een van de bovenstaande voorwaarden waar is, de achtergrondkleur van de cel of cellen in het bereik B1: B4 zal veranderen.

• Voor gegevens waarvan het verschil groter is dan 25%, verandert de achtergrondkleur van de cel in groen.
• Als het verschil groter is dan 50%, verandert de achtergrondkleur van de cel in rood.

De regels die worden gebruikt om deze taak uit te voeren, worden ingevoerd met behulp van de voorwaardelijke opmaak Nieuwe opmaakregel dialoog venster. Begin met het invoeren van de voorbeeldgegevens in de cellen A1 tot en met C5, zoals te zien in de bovenstaande afbeelding

In de derde stap van de tutorial voegt formules toe aan cellen C2: C4 die het exacte procentuele verschil tussen de waarden in cellen weergeven A2: A5 en B2: B5 om de juistheid van de voorwaardelijke opmaakregels te controleren.

02 van 04

Voorwaardelijke opmaakregels instellen

Zoals vermeld, zullen de voorwaardelijke opmaakregels die controleren op de twee voorwaarden worden ingevoerd met behulp van de voorwaardelijke opmaak Nieuwe opmaakregel dialoog venster. Laten we beginnen met het instellen van onze aangepaste voorwaardelijke opmaak:

Voorwaardelijke opmaak om meer dan 25% toename te vinden

= (A2-B2) / A2> 25%

1. highlight cellen B2 naar B5 in het werkblad.
2. Klik op de Begin tabblad van de lint.
3. Klik op de Conditionele opmaak pictogram in de lint om de vervolgkeuzelijst te openen.
4. Kiezen Nieuwe regel om de te openen Nieuwe opmaakregel dialoog venster.
5. Van de Stijl: vak, selecteer de Klassiek keuze.
6. Klik in de tweede vervolgkeuzelijst op de laatste optieGebruik een formule om te bepalen welke cellen moeten worden opgemaakt.
7. Typ de formule hierboven verstrekt in de daarvoor bestemde ruimte.
8. Klik op de Formaat met: vervolgkeuzelijst en selecteer de Licht rood Vul met donkerrode tekst keuze.
9. Klik OK om de dialoogvensters te sluiten en terug te keren naar het werkblad.
10. Op dit punt is de achtergrondkleur van cellen B3 en B5 moet groen zijn.

Voorwaardelijke opmaak om meer dan 50 procent te vinden

= (A2-B2) / A2> 50%

highlight cellen B2 naar B5 in het werkblad.

1. Klik op de Begin tabblad van de lint.
2. Klik op de Conditionele opmaak pictogram in de lint om de vervolgkeuzelijst te openen.
3. Kiezen Nieuwe regel om de te openen Nieuwe opmaakregel dialoog venster.
4. Van de Stijl: vak, selecteer de Klassiek keuze.
5. Klik in de tweede vervolgkeuzelijst op de laatste optieGebruik een formule om te bepalen welke cellen moeten worden opgemaakt.
6. Typ de formule hierboven verstrekt in de daarvoor bestemde ruimte.
7. Klik op de Formaat met: vervolgkeuzelijst en selecteer de Lichtgroen vulling met donkergroene tekst keuze.
8. Klik OK om de dialoogvensters te sluiten en terug te keren naar het werkblad.

De achtergrondkleur van cel B3 moet nog steeds groen zijn, wat aangeeft dat het procentuele verschil tussen de nummers in cellen A3 en B3 is groter dan 25 procent maar minder dan of gelijk aan 50 procent. De achtergrondkleur van cel B5 moet rood worden om aan te geven dat het procentuele verschil tussen de nummers in cellen A5 en B5 is meer dan 50 procent.

03 of 04

Voorwaardelijke opmaakregels controleren

Om te controleren of de voorwaardelijke opmaakregels correct zijn ingevoerd, kunnen we formules in cellen invoeren C2: C5 die het exacte procentuele verschil tussen de getallen in de bereiken zal berekenen A2: A5 en B2: B5.

= (A2-B2) / A2

1. Klik op cel C2 om het de actieve cel te maken.
2. Typ de bovenstaande formule en druk op de invoeren toets op het toetsenbord.
3. Het antwoord 10% zou moeten verschijnen in cel C2, wat aangeeft dat het nummer in cel A2 is 10% groter dan het nummer in cel B2.
4. Het kan nodig zijn om de opmaak aan te passen cel C2 om het antwoord als een percentage weer te geven.
5. Gebruik de handgreep vullen om de formule te kopiëren van cel C2 naar cellen C3 naar C5.
6. De antwoorden voor cellen C3 naar C5 moet 30%, 25% en 60% zijn.

De antwoorden in deze cellen laten zien dat de voorwaardelijke opmaakregels correct zijn gemaakt sinds het verschil tussen cellen A3 en B3 is groter dan 25 procent en het verschil tussen cellen A5 en B5 is meer dan 50 procent.

Cel B4 veranderde niet van kleur omdat het verschil tussen cellen A4 en B4 is gelijk aan 25 procent en onze voorwaardelijke opmaakregel gespecificeerd dat een percentage van meer dan 25 procent nodig was om de achtergrondkleur groen te maken.

04 van 04

Volgorde voor voorwaardelijke opmaak

Wanneer meerdere regels worden toegepast op hetzelfde gegevensbereik, bepaalt Excel eerst of de regels in conflict zijn. Conflicterende regels zijn die waarbij de gekozen opmaakopties voor elke regel niet beide op dezelfde gegevens kunnen worden toegepast.

In het voorbeeld dat in deze zelfstudie wordt gebruikt, zijn de regels in strijd, omdat beide regels dezelfde opmaakoptie gebruiken: die van het wijzigen van de achtergrondcelkleur.

In de situatie waarin de tweede regel waar is (het verschil in waarde is groter dan 50 procent tussen twee cellen), is de eerste regel (het verschil in waarde is groter dan 25 procent) ook waar.

Excel's prioriteitsvolgorde

Omdat een cel niet tegelijkertijd een rode en een groene achtergrond kan hebben, moet Excel weten aan welke voorwaardelijke opmaakregel deze moet voldoen.

Welke regel wordt toegepast, wordt bepaald door de prioriteitsvolgorde van Excel, waarin staat dat de regel die hoger is in de lijst in het dialoogvenster Regels van voorwaardelijke opmaakregels beheren voorrang heeft.

Zoals te zien is in de bovenstaande afbeelding, is de tweede regel die in deze zelfstudie wordt gebruikt hoger in de lijst en heeft daarom voorrang op de eerste regel. Als gevolg hiervan wordt de achtergrondkleur van cel B5 gewijzigd in rood.

Standaard worden nieuwe regels aan de bovenkant van de lijst toegevoegd en hebben daarom een ​​hogere prioriteit. Gebruik de om de volgorde van voorrang te wijzigen Omhoog en omlaag pijlknoppen in het dialoogvenster zoals aangegeven in de bovenstaande afbeelding.

Niet-conflicterende regels toepassen

Als twee of meer voorwaardelijke opmaakregels niet conflicteren, worden beide toegepast wanneer de voorwaarde waarin elke regel test wordt, waar wordt.

Als de eerste voorwaardelijke opmaakregel in ons voorbeeld het celbereik heeft opgemaakt B2: B5 met een groene rand in plaats van een groene achtergrondkleur, zouden de twee voorwaardelijke opmaakregels niet conflicteren, omdat beide indelingen kunnen worden toegepast zonder de andere te hinderen.

Als gevolg, cel B5 zou zowel een groene rand als een rode achtergrondkleur hebben, aangezien het verschil tussen de getallen in cellen A5 en B5 is groter dan zowel 25 als 50 procent.

Voorwaardelijke opmaak versus reguliere opmaak

In het geval van conflicten tussen voorwaardelijke opmaakregels en handmatig toegepaste opmaakopties, heeft voorwaardelijke opmaakregel altijd voorrang en wordt deze toegepast in plaats van handmatig toegevoegde formatteringsopties.

Als er aanvankelijk een gele achtergrondkleur is toegepast cellen B2 naar B5 in het voorbeeld, als de voorwaardelijke opmaakregels zijn toegevoegd, alleen cellen B2 en B4 zou geel blijven. Omdat de ingevoerde voorwaardelijke opmaakregels van toepassing zijn op cellen B3 en B5, hun achtergrondkleuren zouden respectievelijk van geel in groen en rood veranderen.