In het vorige artikel (Heatmap en voorwaardelijke opmaak) zijn diverse (standaard-)opties van voorwaardelijke opmaak langs gekomen. Ik realiseerde me later dat ik iets te weinig over pictogrammen heb verteld.
Dus deze keer nog maar eens: voorwaardelijke opmaak.
Pictogrammen/classificaties
In het Voorbeeldbestand in het tabblad Classif staat een overzicht van de productie per maand.
Voor demonstratie-doeleinden worden de (meeste) maanden gevuld met willekeurige bedragen tussen 0 en een op te geven bovengrens (druk op F9 voor andere waarden).
Daaronder staan overzichten met dezelfde waarden, maar die we verschillende soorten voorwaardelijke opmaak hebben gegeven.
De eerste reeks (rij 8) heeft als volgt een opmaak gekregen:
- eerst zijn alle cellen geselecteerd, die een voorwaardelijke opmaak moeten krijgen; hier dus C8:N8
- daarna is in het blok Stijlen de optie Voorwaardelijke opmaak gekozen
- en dan bij Pictogramseries/Classificaties de optie met de 5 staafdiagrammen
Maar hoe komt de opmaak nu precies tot stand:
- selecteer één van de cellen met opmaak en kies dan opnieuw Voorwaardelijke opmaak
- dan de opties Regels beheren en Regel bewerken
Dus het eerste pictogram wordt getoond als de waarde in de cel groter of gelijk is aan 80% et cetera.
Maar waar is dit nu een percentage van? Dat wordt nergens duidelijk.
Let op wanneer je opmaak gebruikt op basis van procenten (zoals hierboven) dan bepaalt Excel op de achtergrond het verschil tussen de laagste en de hoogste waarde en zal daar de procent-berekening op los laten.
In het voorbeeld van tabblad Classif heeft de eerste maand de waarde 0 gekregen en de laatste maand de bovengrens uit cel D2. Op deze manier is de werking van de voorwaardelijke opmaak goed te bestuderen (druk op F9).
NB in het tabblad Classif2 van het Voorbeeldbestand kun je zien wat er met de opmaak gebeurt als de ondergrens en bovengrens niet zijn “vastgepind”.
In de regels 11 en 14 staan 2 andere voorbeelden van voorwaardelijke opmaak. Waar de opmaak hiervoor 5 verschillende vormen kan aannemen (geen, 1, 2, 3 of 4 balkjes gekleurd) hebben deze voorbeelden 4 respectievelijk 3 opties.
Een ster is helemaal, half of niet ingekleurd.
NB Alle voorbeelden hiervoor zijn gebaseerd op een serie gegevens (in dit geval iedere keer een rij) waarbij de voorwaardelijke opmaak aangeeft hoe de waarde van een cel relatief ten opzichte van de serie scoort.
Maar soms wil je op basis van een waarde in één cel een opmaak weergeven; bijvoorbeeld op basis van een score tussen 0 en 5 een aantal sterren laten zien.
Aantal sterren obv een score
De meest simpele vorm staat hiernaast (zie het tabblad 5Ster in het Voorbeeldbestand).
In kolom C wordt een getal tussen 0 en 5 gecreëerd met 1 decimaal (door eerst een heel getal tussen 0 en 50 te genereren en dat te delen door 10).
Met behulp van de functie Herhaling wordt in kolom D een overeenkomend aantal sterretjes geplaatst. In cel D3 staat de formule =HERHALING(“*”;C3)
Maar dat moet natuurlijk mooier kunnen.
Hiernaast worden maximaal 5 sterren ingekleurd op basis van een score. Wanneer het decimaal gedeelte groter of gelijk is aan 0,5 dan wordt er ook een halve ster gekleurd.
Hoe is dit overzicht opgebouwd? In iedere regel wordt de eerste ster ingekleurd als de score groter of gelijk is aan 1, de tweede ster als de score groter of gelijk is aan 2 enzovoort.
Maar de voorwaarde voor de 2e ster kunnen we ook anders formuleren: als de score minus 1 groter of gelijk is aan 1 dan moet die ingekleurd worden. Iets vergelijkbaars geldt voor de volgende kolommen.
Op deze tweede manier hebben we er voor gezorgd dat in iedere kolom de voorwaarde hetzelfde is.
Dus op basis van de score vullen we de kolommen daarachter met 2 verschillende formules: in de eerste kolom wordt de score overgenomen, de waardes in de andere kolommen zijn gelijk aan de vorige kolom minus 1 (ziet het tabblad 5Ster).
De cellen waar een ster moet komen krijgen dan allemaal de bovenstaande opmaak. Dus bij Type staat nu niet Procent maar wordt er aan een hard Getal gerefereerd.
Als de waarde in een cel groter of gelijk is aan 1 dan wordt die ster helemaal ingekleurd, is de waarde groter of gelijk aan 0,5 dan half en anders blijft de ster leeg.
Aantal sterren obv een score met schaling
Bovenstaand voorbeeld werkt prima als scores tussen 0 en 5 liggen. In de praktijk zullen scores vaak een andere range bestrijken.
In het tabblad Geschaald van het Voorbeeldbestand staan in kolom C bedragen tussen 0 en 100.
Voordat we daar op bovenstaande manier een aantal sterren (maximaal 5) aan kunnen koppelen moeten die bedragen eerst geschaald worden: =5*$C5/$C$2
Als dat gebeurd is kunnen er weer sterren uitgedeeld worden. Om de resultaten makkelijk te kunnen beoordelen is in het voorbeeld een hulpkolom (#Ster) toegevoegd waarin het aantal toe te kennen sterren wordt berekend:
=GEHEEL(D5)+ALS(D5-GEHEEL(D5)>=0,5;0,5;0)
Het aantal toe te kennen hele sterren wordt bepaald met behulp van de functie GEHEEL (de decimalen worden afgehakt); daarna wordt er gekeken of er nog een halve ster bij moet door te kijken of het decimale gedeelte groter of gelijk is aan 0,5.
NB1 voor de liefhebbers, bovenstaande formule kan compacter:
=GEHEEL(D5)+0,5*(D5-GEHEEL(D5)>=0,5)
Hierbij maken we gebruik van de interne werking van Excel: iets wat WAAR is, is gelijk aan 1 en iets wat ONWAAR is, is gelijk aan 0.
NB2 uiteraard zijn de hulpkolommen Geschaald en #Ster niet nodig; zie het tabblad Gesch2 in het Voorbeeldbestand.
NB3 in de vorige voorbeelden is het aantal sterren gebaseerd op een te behalen bovengrens (staat op 100 ingesteld maar is te wijzigen). Soms kan het nodig zijn om het aantal toe te kennen sterren relatief ten opzichte van de maximale score te bepalen; zie het tabblad Gesch3 in het Voorbeeldbestand.
Zoals u kunt zien kunt u ook desgewenst, via Regels beheren, pictogrammen weglaten:
NB4 in het tabblad Gesch4 is het overzicht iets flexibeler ingericht. Voegt u bijvoorbeeld op de plaats van kolom F een nieuwe kolom toe dan zal het systeem direct de juiste berekening uitvoeren (wel in de nieuwe kolom de formules uit de kolom daarachter kopiëren!).
