Voorwaardelijke opmaak

Aan het einde van deze les is bekend:
  • Hoe cellen opgemaakt kunnen worden aan de hand van de celinhoud;
  • Op welke twee manieren voorwaardelijke opmaak gebruikt kan worden;
  • Hoe de maximaal drie voorwaarden optimaal benut kunnen worden;

Celopmaak
Via Opmaak - Celeigenschappen kan op het tabblad ‘Getal’ een bepaalde categorie gekozen worden. We kiezen bij punt 1 de categorie 'Getal':
Het zal bekend zijn dat het in rood weergeven van negatieve getallen mogelijk is. Wanneer bij punt 2 de vierde optie gekozen is en er tevens voor gekozen is om een punt als scheidingsteken te gebruiken voor duizendtallen, kan vervolgens bij Categorie gekozen worden voor 'Aangepast'.
Bij Type wordt het volgende getoond:
#.##0,00_ ;[Rood]-#.##0,00
Hiermee wordt de opmaak van een getal in deze cel bepaald. De #.##0 betekent dat er een punt wordt gebruikt tussen duizendtallen. De 0,00 laat zien dat er twee decimalen worden getoond, ongeacht of deze nu worden ingevoerd of niet.
[Rood]- betekent dat negatieve getallen in het rood worden weergegeven met een minteken voor het getal.

Op bovenstaande manier wordt de opmaak van een getal al afhankelijk gesteld van een voorwaarde, namelijk groter gelijk aan nul, of kleiner dan nul. Maar de opmaak van de hele cel kan afhankelijk gemaakt worden van de inhoud van die cel, of zelfs van de inhoud van een andere cel.


2 Mogelijkheden van voorwaardelijke opmaak
Selecteer eerst de cel, of het cellenbereik waarop u de voorwaardelike opmaak wilt toepassen. Klik dan via Opmaak op Voorwaardelijk opmaak en nevenstaand scherm verschijnt:
Linksboven staat het nummer van de voorwaarde. Standaard wordt met de eerste begonnen. Wanneer je meerdere voorwaardes wilt doorvoeren, kun je op de knop 'Toevoegen' klikken, tot een maximum van drie. In combinatie met hetgeen aan het begin van deze les verteld is, zijn er dus vier opmaken mogelijk. De standaard opmaak, en de drie afhankelijk van een bepaalde voorwaarde.
De celopmaak kan afhankelijk gesteld worden van de celwaarde, en er kan een formule ingevoerd worden.


Opmaak afhankelijk van celwaarde
Als de opmaak afhankelijk gesteld wordt van de celwaarde, dan kan uit de volgende wiskundige regels of vergelijkingsoperatoren gekozen worden:
Er zijn hier vele mogelijkheden. Stel dat van de twaalf maanden van het jaar (B2:B13) de omzet in een tabel staat, en de maanden met een omzet hoger dan gemiddeld moeten opvallen. Selecteer dan dit bereik, en voer de volgende voorwaarde in:
De verwijzing naar het bereik is absoluut omdat de maanden van het jaar blijven staan.
Wanneer de voorwaarde gesteld is, kan de opmaak bepaald worden door op de knop Opmaak te klikken. Het dialoogvenster voor Celeigenschappen verschijnt dan, met daarop drie tabbladen:
Per tabblad zijn diverse eigenschappen in te stellen. Het lettertype en de puntsgrootte is via voorwaardelijke opmaak niet te wijzigen. Van de randstijlen zijn slechts de eerste zeven te kiezen die bij Opmaak - Celeigenschappen mogelijk zijn, terwijl bij Patronen de keuzes gelijk zijn. Ook de uitlijning is niet met voorwaardelijke opmaak te wijzigen.
Als de bovengemiddelde maanden groen moeten worden, maar de maand met de maximale omzet van het jaar weer moet opvallen tussen die maanden door de cel blauw te maken en de letters rood en vet, kan een tweede voorwaarde worden toegevoegd door op de knop Toevoegen te klikken en dan in te voeren:
Celwaarde is gelijk aan =MAX($B$2:$B$13)
Hier ontstaat wel een probleem. Zodra de celwaarde aan voorwaarde één voldoet, worden voorwaarde twee en drie genegeerd. Het maximum blijft dus gewoon groen. De oplossing ziet er als volgt uit:

Wanneer er meerdere voorwaardes gesteld moeten worden aan de opmaak van een cel, dient van tevoren goed nagedacht te worden over de volgorde van de voorwaarden. Zodra de inhoud van een cel aan voorwaarde één voldoet, kijkt Excel niet meer naar voorwaarde twee en drie. Wordt niet aan voorwaarde één voldaan, maar wel aan voorwaarde twee, dan wordt voorwaarde drie genegeerd.

Voorwaarde kopiëren
Als de voorwaardelijke opmaak alleen op cel B2 is gezet, kan deze gekopieerd worden naar de andere maanden op twee manieren:
  1. Klik op de kwast in de werkbalk Opmaak, en selecteer vervolgens cel B3 t/m B13.
  2. Druk op Ctrl+V, selecteer A3:A13 en ga via Bewerken – Plakken speciaal, en klik op Opmaak. Klik dan op OK.
De voorwaarde kan ook gekopieerd worden naar een cel of cellenbereik buiten de tabel waar het om gaat. Met de dollartekens wordt het bereik absoluut gemaakt, ofwel vastgezet. Wanneer er geen dollartekens gebruikt worden, is het bereik variabel en verandert mee als de voorwaardelijke opmaak gekopieerd wordt naar een andere cel of cellenbereik.

Opmaak afhankelijk van formule
De tweede optie bestaat uit het gebruiken van een formule. Dit dient een formule te zijn die als uitkomst WAAR of ONWAAR heeft. De celwaarde of een berekening op de celwaarde dient vergeleken te worden met een constante of waarde.
Stel dat in de cel G21 een datum wordt ingevoerd en dat die datum geen zaterdag mag zijn. Wanneer de datum een zaterdag betreft, moet de cel rood worden. Kies bij Voorwaardelijke opmaak voor 'Formule is' en voor het volgende in: =WEEKDAG(G21)=7. De functie WEEKDAG herrekent de datum om naar een serieel getal. De 7 staat voor de zaterdag.
Een tweede voorbeeld. De invoer in een tabel betreft namen die uniek dienen te zijn. Om te laten zien of een invoer binnen het bereik A2:A13 meer dan één keer voor komt, wordt de functie AANTAL.ALS gebruikt:
=AANTAL.ALS($A$2:$A$13;A2)>1

Drie voorwaarden
Een bedrijf heeft vier afzetmarkten, laten we zeggen Noord, Oost, Zuid en West. Er worden drie soorten producten verkocht. Desktops, notebooks en printers. Iedere maand wordt de winst gerapporteerd. Het management heeft behoefte om maanden waarin verlies wordt gedraaid in het rood te zien en vet met een minteken voor het getal, en maanden met winst in het blauw met een plusteken voor het getal. Wanneer daarentegen de winst of het verlies tussen + 1.000 euro en -/- 1.000 euro zit, dan wordt de cel in zwarte letters getoond. Is de winst 50.000 euro of meer, dan dient de cel eruit te springen door een lichtgele achtergrond en vette blauwe getallen. Bij een verlies van 50.000 euro of meer dient de cel een rode rand te krijgen en een lichtgele achtergrondkleur.
Dit alles is mogelijk door celopmaak te combineren met alle drie opties voor voorwaardelijke opmaak, en wel als volgt.
Selecteer het bereik C2:C14, en ga via Opmaak naar Celeigenschappen:

Kies Getal, zet de decimalen op nul en vink het scheidingsteken voor duizendtallen aan. Kies vervolgens bij 'Negatieve getallen' voor de optie met rood en een minteken voor het getal. Wanneer je nu linksonder op 'Aangepast' klikt, kun je zien hoe Excel deze optie weergeeft:
#.##0_ ;[Rood]-#.##0
Zet tussen rechte haken voor het eerste # +blauw neer en klik op OK.
De selectie is nog steeds het bereik C2:C14. Klik op Opmaak - Voorwaardelijke opmaak en van het venster hiernaast verschijnt het bovenste gedeelte.

Bij Voorwaarde 1 dien je op het tabblad Rand eerste de kleur te kiezen en de lijnstijl alvorens aan te geven welke lijnen gekleurd moeten worden. Dit kan eenvoudig door op ‘Omtrek’ te klikken. Op het tabblad Patronen kies je een lichtgele kleur bij ‘Opvulkleur’. Op het tabblad Lettertype kies je bij 'Tekenstijl' voor ‘Vet’.
Bij Voorwaarde 2 Dien je op het tabblad Lettertype te kiezen voor het zwarte vierkantje linksboven. Kies hier niet 'Automatisch', ook al wordt deze zwart weergegeven. Dat is namelijk de standaardkleur voor alle cellen, maar we hebben juist voor blauw en rood gekozen aan het begin van deze paragraaf:

Bij Voorwaarde 3 dien je bij lettertype te kiezen voor ‘vet’ en een blauwe kleur.
Wanneer je het dialoogvenster ‘Voorwaardelijke opmaak’ hebt gesloten, zie je in de tabel vijf verschillende opmaken!

Uitlijnen van de plus- en mintekens
Mocht het management nog niet tevreden zijn omdat de plus- en mintekens meer moeten opvallen, dan kun je die helemaal links in de cel zetten. Selecteer de cellen, en ga via Opmaak naar Celeigenschappen. Op het tabblad ‘Getal’ klik je op ‘Aangepast’. Hierdoor verschijnt rechts de huidige opmaak:
[Blauw]+#.##0_ ;[Rood]-#.##0
Zet tussen de + en het hekje een spatie, een sterretje en weer een spatie. Doe dit ook voor het minteken:
[Blauw]+ * #.##0_ ;[Rood]- * #.##0
Op deze manier zijn de tekens links uitgelijnd en staan ze netjes onder elkaar. Het resultaat komt er ongeveer zo uit te zien:



EINDE LES Voorwaardelijke Opmaak.