Voorwaardelijke opmaak

1. Leerdoel

De student moet de verschillende voorwaardelijke opmaakmogelijkheden van cellen en celbereiken kennen en kunnen toepassen.

2. Toelichting

MS Excel 2016 beschikt over heel wat mogelijkheden om cellen in een werkblad 'op te maken' overeenkomstig de waarde die in de cel werd ingevoerd.

In onderstaand voorbeeld worden de evaluatiescores van medewerkers uitgezet in een tabel. Naast de deelscores voor 'Kennis', 'Vaardigheden' en 'Attitudes' wordt eveneens een 'Totaalscore' berekend (de optelsom van de drie voorgaande scores), een gemiddelde score ('Gemiddeld') en een afgeronde gemiddelde score ('Afgerond').

Elke kolom met cijfergegevens werd met een verschillende methode van de functie 'Voorwaardelijke opmaak' opgemaakt:

1. Kolom 'Kennis' markeert elke cel met een pijl die, afhankelijk van de waarde in de cel, telkens een andere richting uitwijst en tevens anders is ingekleurd.
   
   
2. Kolom 'Vaardigheden' markeert elke cel met een icoon dat, afhankelijk van de celwaarde, groen, geel of rood van kleur is.
   
   
3. Kolom 'Attitudes' toont in elke cel een staafgrafiekje dat, naarmate de celwaarde toeneemt, als maar meer ingekleurde staafjes bevat.
   
   
4. Kolom 'Totaalscore' kleurt elke cel anders in: de cel met de laagste waarde kleurt rood en die met de hoogste waarde donkergroen, terwijl alle tussenliggende cellen zijn opgemaakt met een kleurschakering tussen de twee voornoemde 'uiterste' kleuren.
   
   
5. De cellen in kolom 'Gemiddeld' zijn rood ingekleurd wanneer de totaalscore onder 10 op 20 zakt, geel bij een totaalscore tussen 10 en 14, en kleuren lichtgroen bij een score boven 14 op 20.
   
   
6. In kolom 'Afgerond' wordt in elke cel een blauwkleurige gegevensbalk getoond die als maar groter wordt weergegeven naar mate de celwaarde toeneemt.

Dit voorbeeld maakt duidelijk dat MS Excel heel wat verschillende mogelijkheden inzake 'Voorwaardelijke opmaak' aanreikt, die toelaten markante waarden te benadrukken of samenhangen en trends te visualiseren.

3. Werkwijze

Open het bestand Oefeningen.xlsx en klik op werkblad 'Voorwaardelijke opmaak' indien je onderstaande toelichting meteen zelf in de praktijk wil zetten.

De functie 'Voorwaardelijke opmaak' is op twee manieren bereikbaar:

1. via het tabblad 'Start' in de groep 'Stijlen' met een klik op de knop 'Voorwaardelijke opmaak'.

MS Excel opent daardoor een rolmenu waarin de voorwaardelijke opmaakmogelijkheden worden opgelijst.

2. via de knop 'Snelle analyse', die verschijnt nadat je een celbereik met getallen hebt geselecteerd

Wanneer je de knop 'Snelle opmaak' aanklikt, verschijnt een uitklapmenu waarin onder het tabblad 'OPMAAK' de meeste (maar toch wat minder) voorwaardelijke opmaakmogelijkheden eveneens worden opgelijst

We overlopen deze opmaakmogelijkheden in de onderstaande paragrafen.

3.1. 'Markeringsregels voor cellen'

Deze opmaakmogelijkheid van de functie 'Voorwaardelijke opmaak' laat je toe om zelf in te stellen welke achtergrondkleur aan cellen wordt gegeven afhankelijk van de 'markeringsregels' (criteria of voorwaarde) die jijzelf daartoe opstelt.

Zo worden bijvoorbeeld in onderstaande figuur cellen in de kolom 'Kennis' met een waarde, kleiner dan 10, rood ingekleurd ; cellen tussen 10 en 14 worden oranje ingekleurd en cellen met een waarde groter dan 14 worden groen ingekleurd.

Dat gaat als volgt:

3.1.1 'Kleiner dan'

1. Selecteer al de cellen waarop de verschillende markeringsregels moeten worden toegepast
   

2. Klik in het tabblad 'Start' op de knop 'Voorwaardelijke opmaak' van de groep 'Stijlen'
   
   
3. Kies in het rolmenu voor de optie 'Markeringsregels voor cellen'
   
   
4. en vervolgens 'Kleiner dan...' omdat we als eerste markeringsregel willen invoeren dat waarden onder 10 op 20 rood moeten worden ingekleurd

5. Excel opent daardoor het dialoogvenster 'Kleiner dan' waarin we de regel (het criterium of de voorwaarde) kunnen invoeren:
   
   
   1. Tik de gewenste waarde - in dit geval het cijfer 10 - in het invoerveld (in onderstaande figuur vervang je de door Excel voorgestelde waarde '11,75' door de waarde '10)
      OF: klik op de knop 'Celadres' en vervolgens op de cel met de gewenste waarde

   

2. Kies de gewenste opmaak in de keuzelijst (in dit geval lijkt de eerste optie 'Lichtrode opvulling met donkerrode tekst' aangewezen)




OF: klik in de keuzelijst op de optie 'Aangepaste indeling' waardoor je in het dialoogvenster 'Celeigenschappen' (waarover we het reeds eerder hebben gehad) een gepaste tekstkleur, lettertype en achtergrondkleur (tab 'Opvulling') kan selecteren.



6. Klik op 'OK' wanneer je daarmee klaar bent en klik naast de geselecteerde cellen om het resultaat te bekijken

3.1.2 'Groter dan'

1. In een tweede markeringsregel willen we opgeven dat alle cellen met een waarde groter dan 14 groen moeten worden ingekleurd:
   
   
   1. eerst selecteer je de cellen waarop de regel moet worden toegepast
   2. klik op de knop 'Voorwaardelijke opmaak'
   3. dan kies je 'Markeringsregels voor cellen'
   4. en vervolgens 'Groter dan'
      
      
2. In het dialoogvenster 'Groter dan' tik je de gewenste waarde in en kies je de gewenste opmaak. Bemerk dat elke keuze die je maakt in de keuzelijst onmiddellijk via een livevoorbeeld wordt toegepast nog vóór je op de 'OK'-knop hebt geklikt.

3. Klik op 'OK' en bekijk het resultaat, nadat je naast de gemarkeerde cellen hebt geklikt:

Noot: het dialoogvenster 'Groter dan' verschijnt eveneens wanneer je in het uitklapmenu, dat verschijnt na een klik op de knop 'Snelle analyse' , op de knop 'Groter dan' klikt in het tabblad 'OPMAAK'.

3.1.3 'Tussen...'

Voor de laatste markeringsregel:

1. Kies je in het rolmenu van de knop 'Voorwaardelijke opmaak' voor de optie 'Tussen...'
   
   
2. voer je in de invoervelden de twee uiterste waarden in (10 en 14),
   
   
3. en kies je een opmaak (die weer onmiddellijk wordt toegepast op de betreffende cellen).
   
   
4. Wanneer het weergegeven resultaat je bevalt, klik je op 'OK'.

Het uiteindelijke resultaat oogt als volgt:

3.1.4 'Markeringsregels beheren'

Indien je in de loop van de bovenstaande procedure een fout hebt gemaakt of wijzigingen aan markeringsregels wil aanbrengen, dan kan je deze wijzigingen als volgt doorvoeren.

1. Klik op de knop 'Voorwaardelijke opmaak'
   
   
2. en kies de allerlaatste optie in het rolmenu 'Regels beheren'

3. Excel opent daardoor het dialoogvenster 'Regels voor voorwaardelijke opmaak beheren'.
   
   
   1. In de keuzelijst 'Opmaakregels weergeven voor' kan je bepalen of ofwel de regels met betrekking tot de huidige geselecteerde cellen, dan wel voor het hele werkblad worden getoond (zie nummer 1 in onderstaande figuur)
      
      
   2. Wanneer je een regel aanklikt, wordt deze geselecteerd
      
      
   3. Als je vervolgens op de knop 'Regel verwijderen' klikt, dan wordt deze gewist

4. Je kan de voorwaarde of de opmaak van een regel wijzigen door te dubbelklikken op de huidige waarde of de weergegeven opmaak. Daardoor wordt het dialoogvenster 'Opmaakregel bewerken' geopend waarin je:
   
   
   1. Een ander type regel kan kiezen (zie nummer 1 in onderstaande figuur) (zie verder op deze pagina voor een toelichting)
      
      
   2. De voorwaarde kan aanpassen: je beschikt daartoe over een keuzelijst met nog meer operatoren dan de keuzelijst 'Markeringsregels voor cellen'!
      
      
   3. Of de opmaak kan wijzigen door de knop 'Opmaak' aan te klikken
      
      
   4. Klik op 'OK' wanneer je daarmee klaar bent



5. Je kan in het dialoogvenster 'Regels voor voorwaardelijke opmaak beheren' tevens het celbereik (waarop de regel wordt toegepast) aanpassen door een ander celbereik in te tikken of door gebruikt te maken van de knop 'Celadres' .
   
   
3. En of dat niet allemaal nog niet genoeg is, kan je in het dialoogvenster 'Regels voor voorwaardelijke opmaak beheren' ook nog geheel nieuwe regels toevoegen, door op de knop 'Nieuwe regel...' te klikken.

3.2. 'Regels voor bovenste/onderste'

Een tweede optie in het vervolgmenu van de knop 'Voorwaardelijke opmaak' laat je toe om in een celbereik de 'bovenste waarden' (hoogste waarden) dan wel de 'onderste waarden' (laagste waarden) op een zelf te bepalen wijze op te maken (andere tekenstijl, andere achtergrondkleur, andere omranding, ...).

In onderstaande figuur (rechts) worden bijvoorbeeld de 10 hoogste waarden, met behulp van de menuoptie 'Bovenste 10%', op een rode achtergrond weergegeven. De 15 laagste waarden worden via de optie 'Onderste 10 items...' (na wijziging van de standaard '10' naar '15') op een gele achtergrond, met groene en vetjes weergegeven waarden.

Door op één van de opties in het submenu te klikken wordt een dialoogvenster geopend waarin telkens het aantal op te maken waarden, alsook de aard van de opmaak, kan worden ingesteld.

• Omdat je in het dialoogvenster telkens zélf het aantal op te maken waarden kan instellen, hoef je bijgevolg niet steeds de standaardwaarde '10' te aanvaarden
  
  
• De keuzelijst, waarin je de opmaak kan kiezen, is dezelfde als deze welke hoger werd besproken.

• Wanneer je voor de opties 'Boven gemiddelde' of 'Onder gemiddelde' kiest, dan kan je als vanzelfsprekend enkel de opmaak kiezen voor de cellen waarvan de waarde boven dan wel onder het gemiddelde (van alle waarden) ligt.

Noot: wanneer je een celbereik met getallen selecteert en op de knop 'Snelle analyse' klikt, dan kan je de 'Bovenste 10%' (de 10% cellen in het celbereik met de hoogste waarden) roodkleurig tegen een rode achtergrond weergeven door op de gelijknamige knop te klikken. Je kan echter geen andere opmaak instellen.

3.3. 'Gegevensbalken'

Een derde keuzeoptie in het vervolgmenu van de knop 'Voorwaardelijke opmaak' laat je toe kleurige gegevensbalken als achtergrondfiguur in cellen weer te geven.

Een voorbeeld om dit duidelijk te maken zie je in onderstaande figuur in de kolom 'Totaalscore':

1. Selecteer de cellen waarop de gegevensbalk moeten worden toegepast
   
   
2. Kik in het tabblad 'Start' op de knop 'Voorwaardelijke opmaak' van de groep 'Stijlen'
   
   
3. Klik in het vervolgmenu op de optie 'Gegevensbalken'
   
   
4. Wijs in de galerij van gegevensbalken de gewenste balken aan, waardoor het kleureffect door middel van een livevoorbeeld meteen op de geselecteerde cellen wordt toegepast

5. Klik op de gewenste gegevensbalk in de galerij om deze te selecteren en toe te passen op de geselecteerde cellen

Indien je opteert voor een andere kleur dan deze die in de galerij worden weergegeven, klik dan op de knop 'Meer regels...' onderaan in de galerij.

Je kan dan in het daardoor geopende dialoogvenster 'Nieuwe opmaakregel' elke gewenste kleur voor de gegevensbalken kiezen.

Noot: Je kan voor het toepassen van de voorwaardelijke opmaak 'Gegevensbalken' ook gebruik maken van de knop 'Snelle analyse' :

1. Selecteer het celbereik
2. Klik op de knop 'Snelle analyse'
3. Klik op het tabblad 'OPMAAK'
4. Klik op de knop 'Gegevensbalken'
5. De geselecteerde cellen worden met blauwe gegevensbalken gevuld

3.4. 'Kleurenschalen'

Een volgende optie in het vervolgmenu 'Voorwaardelijke opmaak' is bedoeld voor het toepassen van kleurenschalen op een geselecteerd celbereik. Daarbij wordt afhankelijk van de waarden in de geselecteerde cellen een bepaalde kleur(schakering) op elke cel afzonderlijk toegepast.

Je kan daarbij kiezen uit een kleurenschaal met 2 kleuren of een kleurenschaal met 3 kleuren:

Links wordt een kleurenschaal met drie kleuren getoond: rood (voor de waarden minder dan 10), schakeringen van geel voor de waarden tussen 10 en 13, blauw voor de waarden boven 13

Rechts wordt een kleurenschaal met twee kleuren getoond: schakeringen van groen voor waarden tussen 7 en 12, schakeringen van geel voor de hogere waarden.

1. Selecteer de cellen waarop de kleurenschaal moet worden toegepast
   
   
2. Kies voor de optie 'Kleurenschalen' in het vervolgmenu 'Voorwaardelijke opmaak'
   
   
3. In de daardoor geopende galerij kan je (in de bovenste rij) vier kleurenschalen met drie kleuren kiezen of (in de onderste rij) vier kleurenschalen met twee kleuren.

Indien je een andere kleurenschaal verkiest, klik dan op de optie 'Meer regels...' onder de galerij

Je kan in het dialoogvenster 'Nieuwe opmaakregel' kiezen voor een gewenste opmaakstijl (2- of 3-kleurenschaal), een gepast type en de gepaste kleuren (en kleurschakeringen).

Noot: Ook voor het toepassen van een kleurenschaal, kan je gebruik maken van de (zeer beperkte) opmaakmogelijkheden onder de knop 'Snelle analyse' . Je ermee een driekleurensschaal toepassen op een celbereik.

3.5. 'Pictogramseries'

Een laatste optie in het vervolgmenu van de knop 'Voorwaardelijke opmaak' is de optie 'Pictogramseries'

Deze toont bij het aanklikken een galerij met een hele reeks pictogramseries die worden opgebouwd met drie, vier of vijf verschillende pictogrammen.

Een voorbeeld waarbij de waarden onder 10 met een rode neerwaarts gerichte pijl worden gemarkeerd, waarden tussen 10 en 12 met een zijwaarts wijzende gele pijl en waarden hoger dan 12 met een groene opwaarts gerichte pijl:

Indien de voorgestelde pictogramseries in de galerij niet voldoen, klik dan op de knop 'Meer regels...' onder de galerij. Je kan dan in het dialoogvenster je eigen pictogramserie opbouwen:

Noot: Met behulp van de knop 'Snelle analyse' kunnen op een numeriek celbereik de pijlvormige pictogrammen uit de bovenstaande figuur worden toegepast.

4. Oefeningen

1. Open het bestand Oefeningen.xlsx op je USB-stick of harde schijf ; of download dit bestand wanneer je dat nog niet gedaan hebt.
   
   
2. Klik op werkblad 'Voorwaardelijke opmaak' en oefen bovenstaande voorwaardelijke opmaken in.
   
   
3. Een voorbeeld van voorwaardelijke opmaak kan je vinden op werkblad 'Voorwaardelijke opmaak' van het bestand Oefeningen_Oplossing.xlsx.
   
   

5. Bronnen

Helpprogramma bij Microsoft Excel 2016: zoek op 'voorwaardelijke opmaak'.