Categorie archief: Excel

Voorwaardelijke opmaak met sterren

Excel, ,
Voorbeeldbestand: Sterren.xlsxDownload

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:

  1. eerst zijn alle cellen geselecteerd, die een voorwaardelijke opmaak moeten krijgen; hier dus C8:N8
  2. daarna is in het blok Stijlen de optie Voorwaardelijke opmaak gekozen
  3. en dan bij Pictogramseries/Classificaties de optie met de 5 staafdiagrammen

Maar hoe komt de opmaak nu precies tot stand:

  1. selecteer één van de cellen met opmaak en kies dan opnieuw Voorwaardelijke opmaak
  2. 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!).

Heatmap en voorwaardelijke opmaak

Excel, ,
Voorbeeldbestand: VoorwOpmaak2.xlsxDownload

Ik was deze week op Strava een zogenaamde heatmap aan het bekijken; in dit geval een overzicht van de meest (en minst) gefietste trajecten van West-Europa.
Wat direct opvalt is dat er vooral in de buurt van de grote steden (en in Zuid-Limburg) veel gefietst wordt, maar zeker ook dat Nederland en België er flink uitspringen.
Hoe zou dat komen? Wordt er in Noord-Frankrijk en in Duitsland echt minder gefietst? Of leggen de fietsers daar hun tochten niet vast in Strava? Of …

Misschien is dat ook de reden waarom je heatmaps in de praktijk niet zo veel tegenkomt: ze roepen soms meer vragen op dan ze antwoorden geven.

Maar bij mij kwam wel direct de vraag op: hoe zou je een heatmap in Excel maken? Och ja, natuurlijk: met voorwaardelijke opmaak. Daarom in dit artikel maar eens de schijnwerper op diverse mogelijkheden van de, in Excel ingebouwde, opties.

Voorwaardelijke opmaak

Deze optie vinden we in Excel binnen de menutab Start in het blok Stijlen. Wanneer u deze optie kiest opent zich een vervolgscherm met diverse mogelijkheden. Een groot gedeelte daarvan zal hieronder de revue passeren.

Markeringsregels

Kiest u voor Markeringsregels dan ziet u diverse opties: de opmaak regelen van cellen die een waarde bevatten groter dan een bepaalde target, of juist kleiner of die tussen 2 targets in liggen etc.

In het tabblad Markering van het Voorbeeldbestand staat een opbrengst-overzicht per dag. In kolom C wordt de dagopbrengst bepaald met de formule =ASELECTTUSSEN(0;10)
oftewel een willekeurig geheel getal tussen 0 en 10 (allebei de uitersten doen mee).

Om alle cellen, die een waarde groter dan 7 bevatten, te accentueren doorlopen we de volgende stappen:

  1. selecteer alle cellen, die een opmaak moeten krijgen; in het voorbeeld de cellen C3:C33
  2. kies Voorwaardelijke opmaak/Markeringsregels/Groter dan
  3. vul in het vervolgscherm de targetwaarde in (hier 7) en kies als opmaak Groene opvulling met donkergroene tekst
  4. klik dan op OK

Om de cellen, waarvan de waarde kleiner is dan 3, te benadrukken doen we iets vergelijkbaars, alleen kiezen we in stap 2 de optie Kleiner dan.

NB door op F9 te drukken, wordt de Excel-map opnieuw berekend. Alle waarden in kolom C worden dan ververst en de opmaak zal zich daaraan aanpassen.

Voorwaardelijke opmaak aanpassen

Soms voldoet de op deze manier standaard ingestelde opmaak niet (helemaal). Volg dan de volgende stappen:

  1. selecteer één van de cellen, waarvan de opmaak gewijzigd moet worden, in dit geval bijvoorbeeld C3
  2. kies Voorwaardelijke opmaak en dan de optie Regels beheren…
  3. bovenstaand scherm komt dan tevoorschijn. Standaard worden alleen de regels van de huidige selectie getoond. Dit kunt u makkelijk wijzigen in het eerste keuzevak.
  4. klik op de regel die u wilt wijzigen (of bekijken) en kies Regel bewerken
  5. afhankelijk van het soort regel kunnen nu diverse parameters ingesteld worden, inclusief de gewenste opmaak

Bij iedere regel is ook te zien voor welke cellen de regel geldt; in dit geval allebei de keren voor C3:C33.

NB Dit bereik kan hier aangepast worden. Uitermate handig als u in de eerste stap bij het instellen van opmaak vergeten bent een reeks cellen te selecteren.

Ook als er nieuwe waarden aan kolom B en C worden toegevoegd zal het bereik voor de opmaak-regels moeten worden aangepast.

Opmaak in een Excel-tabel

In de kolommen E en F van het tabblad Markering in het Voorbeeldbestand staat eenzelfde soort overzicht, maar dan als Excel-tabel (zie onder andere het artikel Kunst en Excel, ofwel de 10 voordelen van het gebruik van tabellen).
Dit heeft de volgende voordelen:

  1. selecteert u één van de cellen van de tabel en u kiest dan Regels beheren, dan zal Excel direct alle opmaakregels van die tabel tonen
  2. voegt u nieuwe gegevens onder aan de tabel toe, dan zal Excel automatisch ook de voorwaardelijke opmaak aan die cellen toevoegen

In kolom E wordt via Voorwaardelijke opmaak/Markering/Datum de datum van vandaag met een gele achtergrondkleur weergegeven.

Kolom F kent dezelfde opmaak als in het vorige overzicht.

Opmaak ten opzichte van het gemiddelde

In het tabblad Markering in het Voorbeeldbestand bevatten de kolommen H en I ook een tabel (met de naam tblOpbr2).
In cel K3 (met de naam GemOpbr) wordt de gemiddelde opbrengst bepaald.

Kolom I heeft op de volgende manier een voorwaardelijke opmaak gekregen:

  1. selecteer de cellen I3:I33
  2. kies Voorwaardelijke opmaak/Markering/Groter dan
  3. als target geven we nu niet een harde grens op, maar selecteren met de muis cel K3 of drukken op de functietoets F3 en kiezen dan de naam GemOpbr (de laatste optie is het mooiste, omdat dan duidelijker te zien is wat deze opmaakregel doet)
  4. kies de gewenste opmaak
  5. klik OK
  6. herhaal het vorige maar nu met de optie Kleiner dan

Opmaak voor unieke of dubbele waarden

Voorwaardelijke opmaak kan ook eenvoudig gebruikt worden om unieke of dubbele waarden te markeren (zie het tabblad UniekDubbel in het Voorbeeldbestand):

  1. selecteer de cellen, die een opmaak moeten krijgen (in dit geval B3:B9)
  2. kies Voorwaardelijke opmaak/Markering/Dubbele waarden
  3. kies de optie Dubbele of Unieke
  4. geef de gewenste opmaak op en klik op OK

Opmaak voor bovenste/onderste

Soms wilt u zien welke cellen de grootste of kleinste waarden bevatten. Dat gaat als volgt (zie het tabblad BovenOnder in het Voorbeeldbestand):

  1. selecteer de cellen, die een opmaak moeten krijgen
  2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Bovenste 10 items
  3. dan geeft u aan of u echt 10 items wilt opmaken; u kunt bijvoorbeeld ook voor de top-3 kiezen
  4. kies de gewenste opmaak en klik op OK

NB1 u ziet ook de optie Bovenste 10%; de 10 is weer apart in te stellen. Excel zal dan zelf, aan de hand van het geselecteerde bereik, bepalen hoeveel cellen er ‘gekleurd’ moeten worden.

NB2 uiteraard kunt u ook de cellen met de laagste waarden accentueren (Onderste in plaats van Bovenste).

Opmaak ten opzichte van het gemiddelde 2

Hiervoor hebben we via Markering een methode laten zien om cellen boven of onder het gemiddelde op te maken. Excel kent standaard een alternatief (zie tabblad BovenOnder van het Voorbeeldbestand, kolommen E en F):

  1. selecteer de cellen, die een opmaak moeten krijgen
  2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Boven gemiddelde
  3. geef een opmaak op en klik op OK

NB nadeel van deze methode is, dat u nergens zelf het gemiddelde ziet.

Bovenste/onderste interactief

Soms hebben we inzicht nodig in de hoogste 5, een andere keer willen we de laagste 5 (of een ander aantal) zien (zie tabblad BovenOnder van het Voorbeeldbestand, kolommen H en I). Hier voorziet Excel niet standaard in.

  1. leg ergens vast hoeveel cellen er ‘gekleurd’ moeten worden; in het voorbeeld gebruiken we cel L2 daarvoor met de naam BovOnderAantal
  2. plaats in een cel de tekst Boven of Onder; in het voorbeeld cel L3 met de naam BovOnder. Via gegevensvalidatie kan uit die 2 woorden worden gekozen en is de invoer beperkt. 
  3. selecteer de cellen, die een opmaak moeten krijgen (beginnend bij I3)
  4. kies Voorwaardelijke opmaak, dan de optie Nieuwe regel en daarna de optie Een formule gebruiken
  5. plaats dan de volgende formule =EN(BovOnder=”Boven”; I3>=GROOTSTE($I$3:$I$33;BovOnderAantal))
  6. geef een gewenste opmaak en klik OK

De formule levert alleen als waarde WAAR (en dan zal de opmaak worden doorgevoerd) als aan allebei de voorwaarden wordt voldaan: cel L2 moet de waarde Boven bevatten EN de waarde in cel I3 moet groter of gelijk zijn aan de grootste waarde in het bereik van geselecteerde cellen (als BovOnderAantal gelijk is aan 1, aan de één na grootste als BovOnderAantal gelijk is aan 2 etcetera).

LET OP bij het maken van een formule voor voorwaardelijke opmaak met relatieve verwijzingen.
In dit geval bevat de formule I3, omdat dat het startpunt van onze selectie was.
Selecteer cel I4 en bekijk nu de regel die bij de voorwaardelijke opmaak hoort; Excel heeft de relatieve verwijzing in de formule automatisch aangepast.

NB de andere gebruikte formule (in het geval dat we de laagste waarden zoeken):
=EN(BovOnder=”Onder”;I3<=KLEINSTE($I$3:$I$33;BovOnderAantal))

Gegevensbalken

Sommige mensen willen getallen zien in overzichten, anderen zijn meer grafisch ingesteld.

Gegevensbalken als voorwaardelijke opmaak zijn in dit geval een uitkomst. Hierboven (zie het tabblad GegBalk in het Voorbeeldbestand) zijn per kolom de waarden ‘vertaald’ naar kleine grafiekjes:

  1. selecteer de cellen van de Uit-kolom. Wijs daartoe met de muis de bovenrand van de Uit-cel aan; de cursor wordt dan een pijl-naar-beneden. Klik dan met de muis.
  2. kies Voorwaardelijke opmaak en dan de optie Gegevensbalken. In dit geval kiezen we de derde kleurovergang (Rood)
  3. om de kleuren-balk en de getallen te scheiden: kies opnieuw Voorwaardelijke opmaak, maar dan de optie Regels beheren en Regel bewerken. Klik op de knop Negatieve waarden en as en zorg dat bij Asinstellingen de optie Middelpunt is geselecteerd.
  4. voor de In-kolom geldt een vergelijkbare procedure
  5. de Result– en ResCum-kolom zijn iets ingewikkelder: in eerste instantie kiezen we voor een Groene gegevensbalk. Daarna moet er nog een aanpassing doorgevoerd worden: kies bij Regel bewerken/Negatieve waarden en as een Rode opvulkleur (ook voor de rand van de balk). Zet meteen ook Middelpunt aan.

Gegevensbalken 2

Bijna hetzelfde overzicht als hiervoor (wel heb ik de kolom met cumulatieve resultaten weggelaten; zie het tabblad GegBalk in het Voorbeeldbestand, bereik B17:E29).
Het verschil zit hem er in dat de grootte van de gegevensbalken nu relatief ten opzichte van de totale tabel bepaald zijn en niet per kolom.

LET OP zoals uit de diverse voorbeelden mag blijken, moeten de overzichten wel altijd van een goede legenda worden voorzien anders kan de lezer snel de verkeerde conclusies trekken.

Gegevensbalken 3

Wanneer de exacte details van de in- en uitgaande stromen niet van belang zijn kunt u er ook voor kiezen om alleen de gegevensbalken te laten zien (zie het tabblad GegBalk in het Voorbeeldbestand, bereik H2:K14):

  1. selecteer één van de cellen waarvan de opmaak moet worden gewijzigd
  2. kies Voorwaardelijke opmaak/Regels beheren
  3. selecteer de betreffende regel en klik op Regel bewerken
  4. plaats een vinkje voor de optie Alleen balk weergeven

Gegevensbalken 4

Gegevensbalken zijn ook toepasbaar in draaitabellen (zie het tabblad GegBalkDraai in het Voorbeeldbestand).

LET OP wanneer er aan de brongegevens regels worden toegevoegd dan dient de draaitabel te worden vernieuwd. Komt er in de draaitabel dan ook een nieuwe regel bij, dan zal de opmaak niet automatisch worden doorgevoerd. Dit kunt u als volgt oplossen:

  1. selecteer in de draaitabel één van de cellen waarvan de opmaak moet worden gewijzigd
  2. kies Voorwaardelijke opmaak/Regels beheren
  3. selecteer de betreffende regel en klik op Regel bewerken
  4. kies één van de 2 opties, die beginnen met Alle cellen

Heatmap

Dan nu nog even over Heatmaps (de aanleiding voor dit artikel).

Hiernaast worden de grootste getallen groen gekleurd en de kleinste rood (zie het tabblad Kleuren in het Voorbeeldbestand).
De getallen niet in de buurt van de extremen krijgen een tussenschakering.

We gaan als volgt te werk:

  1. selecteer alle cellen, die bij de heatmap betrokken moeten worden
  2. kies Voorwaardelijke opmaak en dan de optie Kleurenschalen. In dit geval kiezen we de eerste kleurovergang (groen-geel-rood)

Maar we kunnen de standaardinstellingen van Excel nog aanpassen.
Wanneer we alles onder de 30 te weinig vinden en alles boven 90 prima, dan passen we de regel als volgt aan (zie het tabblad Kleuren in het Voorbeeldbestand, bereik I3:L14):

Kies Voorwaardelijke opmaak/Regels beheren, selecteer de betreffende regel en klik op Regel bewerken. Stel de opties in zoals hieronder weergegeven.

Nog meer opmaak

Voor diegene die de smaak te pakken hebben gekregen: het Voorbeeldbestand bevat nog diverse andere voorbeelden van opmaak. Met het bovenstaande in het achterhoofd moeten de daarbij gemaakte keuzes duidelijk zijn.

Interactieve grafiek

Excel,
Voorbeeldbestand: Invloed-1.xlsxDownload

Deze week zag ik in de Volkskrant een mooie grafiek; daarin kon je niet alleen zien, welke 20 personen in 2018 in Nederland het meeste invloed hebben gehad, maar ook hoe ze in voorgaande jaren scoorden.

Door het veel verschillende kleurgebruik wel een beetje een kerstboom; maar dat zal wel beïnvloed zijn door de tijd van het jaar.

NB voor de systematiek en andere achtergronden, zie volkskrant.nl/hoe-de-top-200-van-invloedrijkste-nederlanders-wordt-samengesteld/

Al met al een aanleiding om eens te kijken of de grafiek (makkelijk) na te bouwen is in Excel en of we deze wat interactiever kunnen maken.

Basis-gegevens

Op de site van de Volkskrant zijn de basisgegevens niet terug te vinden; dus dan moeten we ze maar zelf uit de grafiek afleiden (zie het tabblad Data in het Voorbeeldbestand).

Wel ontbreken er dan nogal wat namen (de grijze lijnen in de grafiek); die zijn gecodeerd met Onbekend.  Deze personen komen niet meer in 2018 in de top-20 voor. De 1e onbekende zien we alleen in 2014 en 2015, terwijl de 2e onbekende ook nog in 2016 en 2017 voorkomt.

Alle data zijn opgeslagen in een Excel-tabel met de naam tblData. Bij uitbreiding van de gegevens zal Excel hier automatisch rekening mee houden.

Overzicht

Om de grafiek te kunnen maken genereren we eerst op basis van de bovenstaande gegevens een draaitabel; in de rijen komt het Jaar, in de kolommen de Naam van de personen en in het waardegebied het VolgNr (als we de gegevens goed in de tabel hebben ingevuld, komt iedereen maar 1 keer per jaar voor. Dus het is geen probleem om Som van VolgNr te gebruiken).

Deze draaitabel vormt op zijn beurt weer de basis voor een lijngrafiek (met markeringen).

Er moet nog wel wat aan de grafiek aangepast worden:

  1. allereerst de Legenda verwijderen
  2. rechts klikken op de draaitabel-knoppen (Som van .. en J..) en Verbergen kiezen
  3. rechts klikken op de linkeras en As opmaken kiezen
  4. Minimum vastzetten op 0 en Maximum op 21
  5. Waarde in omgekeerde volgorde aanvinken en OK klikken
  6. de linkeras verwijderen
  7. horizontale rasterlijnen verwijderen
  8. rechtsklikken op een lijn en Gegevensreeks opmaken kiezen
  9. bij Markeringsopties Standaard kiezen, als Type het rondje selecteren en de Grootte op 16 zetten
  10. Markeringsopvulling: een effen kleur kiezen en aanpassen aan de voorbeeldgrafiek (rood voor stijgers, blauw voor dalers etcetera)
  11. de Lijnkleur ook aanpassen via Ononderbroken streep en OK klikken
  12. nog een keer rechtsklikken op de lijn: Gegevenslabels toevoegen
  13. opnieuw rechtsklikken en dan Gegevenslabels opmaken kiezen
  14. de Labelpositie moet worden: Centreren en OK klikken
  15. dan op de rechtse markering van de lijn klikken (alleen dit punt van de grafiek is dan geselecteerd) en daar rechtsklikken. Kies Gegevenslabel opmaken
  16. Bij Label bevat ook de Reeksnaam aanvinken en als positie Rechts kiezen
  17. alle punten vanaf 8 opnieuw uitvoeren voor de overige lijnen; kies telkens de juiste kleur

Het resultaat mag er zijn (zie het tabblad Ovz1 van het Voorbeeldbestand):

NB het aanpassen van de grafiek is een flink karwei. Huiswerk voor een volgend artikel: het automatisch aanpassen van een grafiek met behulp van VBA.

Interactief overzicht

In de vorige grafiek staan zoveel lijntjes dat het soms niet meevalt om “de bomen door het bos te zien”.

Het zou natuurlijk mooi zijn als we een persoon zouden kunnen selecteren en dat dan de daarbij behorende “scores” in de grafiek automatisch benadrukt worden.

In het tabblad Ovz2 van het Voorbeeldbestand is via Gegevensvalidatie in cel S2 de invoer van een persoonsnaam geautomatiseerd (alleen namen uit de kop van de draaitabel van Ovz1 zijn toegestaan).
In cel S3 staat de formule: 
=INDEX(‘Ovz1’!$C$5:$AL$9;
VERGELIJKEN(R3;’Ovz1′!$B$5:$B$9;0);
VERGELIJKEN($S$2;’Ovz1′!$C$4:$AL$4;0))
Ofwel: zoek met behulp van Index in het blok C5:AL9 van het tabblad Ovz1 naar de rij die overeenkomt met de waarde in R3 en de kolom die overeenkomt met de naam in S2.

Omdat niet iedereen in elk jaar voorkomt kan deze formule ook soms de waarde 0 opleveren; deze waardes willen we niet in de grafiek zien, vandaar dat in T3 de volgende formule staat: =ALS(S3=0;NB();S3)

NB de functie NB() levert als resultaat de waarde #N/B. Deze waardes worden in een grafiek genegeerd. Zie ook het artikel grafiek-zonder-nullen.

In het tabblad Ovz2 van het Voorbeeldbestand is een kopie van de grafiek uit Ovz1 geplakt.
Aan deze grafiek is een nieuwe reeks toegevoegd, de cellen T3:T7.
Deze lijn van deze reeks heeft een rode kleur gekregen. Nog wat andere aanpassingen: Vloeiende lijn aanvinken en een pijl als Eindtype bij Lijnstijl.

Om het geheel rustig te laten ogen hebben alle overige lijnen een grijze kleur gekregen.

Kies in cel S2 een andere naam en de grafiek zal zich automatisch aanpassen!

NB een optie met slicers is handiger, maar dit wordt niet door iedere Excel-versie ondersteund.

Grafiek automatisch schalen en labels toevoegen

Excel, VBA (Visual Basic for Applications), ,
Voorbeeldbestand: GrafOpm.xlsmDownload

Herkent u de frustratie? Heb je net een grafiek ‘netjes’ gemaakt door de assen zodanig te schalen dat de gegevens mooi verdeeld zijn over de grafiek, klopt er niets meer van als er een andere of uitgebreidere reeks gegevens worden weergegeven.
Of je hebt ‘met de hand’  alle labels bij de punten aangepast (ja, ik gebruik nog een oude Excel-versie), dan gaan die bij een andere reeks de mist in.

Dus deze keer een anti-frustratie-artikel: 2 VBA-routines, die bovenstaande problemen oplossen.

Test-gegevens

Om een voorbeeld te kunnen maken had ik wat test-gegevens nodig. Meestal maak ik die door wat random-getallen of teksten te genereren.
Maar ik herinnerde me ineens dat ik ooit eens ergens gelezen had dat je ook test-gegevens op een site kon laten maken.
Na wat zoeken kwam ik de website mockaroo.com tegen.

Na een paar pogingen had ik een mooi lijstje met voorbeeld-gegevens (zie het tabblad Basis in het Voorbeeldbestand).

  1. bij Soort heb ik gekozen voor financiële markten (Mockaroo kiest dan willekeurig uit 2 mogelijkheden)
  2. de Datum ligt tussen 1 jan 2018 en 31 dec 2018
  3. bij Omschr heb ik voor willekeurige voornamen gekozen
  4. het Bedrag ligt tussen 1000 en 2000

Om de rest wat overzichtelijk te houden heb ik 20 regels daaruit geselecteerd (zie het tabblad Basis2) en een beetje aangepast. Deze test-gegevens staan klaar in een Excel-tabel met de naam Tabel1.

Grafiek

Om het verloop van de bedragen per Soort handig te kunnen weergeven, heb ik een draaitabel gemaakt op basis van de gegevens in Tabel1 (zie het tabblad Result in het Voorbeeldbestand).

Op basis van deze draaitabel kun je makkelijk een Draaigrafiek maken, maar dan komt Omschr automatisch op de x-as en ik wil deze gebruiken als Labels in de grafiek.

Dus maar een ‘gewone’ grafiek gemaakt met op de x-as de datums en op de y-as de bedragen.

Hiernaast staat de grafiek waarbij er geen Soort is geselecteerd (dus 20 punten). De x-as begint automatisch bij 19 januari omdat dat de kleinste datum is; iets vergelijkbaars geldt voor het einde van de as.
Excel heeft ook de y-as automatisch geschaald.

Assen handmatig aanpassen

Om de assen handmatig te schalen klik je met de rechtermuisknop op een as en pas je het minimum en het maximum aan (het vinkje daarvoor zal dan verdwijnen ten teken dat er geen automatische schaling hoeft plaats te vinden).

Maar iedere keer als er in de draaitabel een andere Soort wordt gekozen moeten er aanpassingen worden doorgevoerd.

Assen automatisch aanpassen

Om deze schaal-aanpassingen met VBA geautomatiseerd te kunnen doorvoeren moeten we ergens vastleggen wat het gewenste minimum en maximum is.

In het Voorbeeldbestand op het tabblad Result staat in cel G2 de formule =MIN(B:B)
Oftewel bepaal de kleinste waarde/datum in kolom B. In G3 wordt met de functie MAX de grootste waarde vastgelegd. Maar het is mooier als de x-as op de eerste van een maand begint: in cel H2 staat daartoe de formule =DATUM(JAAR(G2);MAAND(G2);1). Deze cel heeft de naam x_min gekregen.

Het maximum van de x-as moet de eerste dag van de volgende maand zijn: cel H3 (met de naam x_max) heeft daarom de formule =DATUM(JAAR(G3);MAAND(G3)+1;1)

NB ook als G3 een datum in december bevat, werkt deze formule goed; Excel zal automatisch het jaar ophogen en de maand gelijk maken aan januari.

In kolom J worden de minimum- en maximum-bedragen uit kolom D bepaald. Cel K2 bevat de formule =GEHEEL(J2/100)*100, zodat het minimum voor de y-as het eerste honderdtal is, kleiner dan de waarde in J2.
In cel K3 bepalen we de bovengrens voor de y-as: =(GEHEEL(J3/100)+1)*100

Nu hebben we nog een VBA-routine nodig, die de handelingen voor het aanpassen van de assen van ons overneemt.

In het tabblad Result is een knop met de naam Assen schalen toegevoegd waarmee deze macro/subroutine kan worden uitgevoerd.

Labels toevoegen

In de oudere versies van Excel is het niet mogelijk om automatisch extra informatie (zoals de Omschr uit het voorbeeld) als Labels aan een punt toe te voegen. Handmatig kan dit wel:

  1. klik rechts op de lijn in de grafiek
  2. kies de optie Gegevenslabels toevoegen
  3. klik op één van de labels; als dan alle labels zijn geselecteerd nogmaals op het label klikken
  4. de tekst van dit label kan nu aangepast worden

Maar als er nu in de draaitabel een andere Soort wordt gekozen dan kloppen de handmatig ingevoerde labels niet meer!

Daar komt VBA weer goed van pas:

Het spannende is hier wat er gebeurt met de variabele xVals.

Wanneer je in de grafiek op de lijn klikt verschijnt er in de formulebalk een formule die begint met =REEKS(
Daarna komt de cel, die de naam van de reeks bevat, dan de x-waarden en daarna de y-waarden.

Aangezien VBA Engelstalig is bevat xVals na het uitlezen van de formule van de Collection: =SERIES(Result!$B$4,Result!$B$6:$B$25,Result!$D$6:$D$25,1)

NB1 binnen Excel (met Nederlandse instellingen) worden de parameters gescheiden door een ; (punt-komma), maar binnen VBA is dit een , (komma).

NB2 de 1 op het einde geeft aan, dat deze lijn de eerste reeks is die in de grafiek geplot moet worden. We hebben maar één lijn, dus deze parameter heeft hier geen invloed.

We hebben in het vervolg van de routine alleen de reeks van de x-waarden nodig, de B6:B25.
Daarom nemen we eerst alles wat rechts van de eerste komma staat:
xVals = Mid(xVals, InStr(xVals, “,”) + 1)
en dan alles links van de volgende, dan weer eerste, komma:
xVals = Left(xVals, InStr(xVals, “,”) – 1)

xVals bevat nu  het bereik Result!$B$6:$B$25

LET OP als de naam van het tabblad zelf ook een komma bevat dan wordt de formule wel wat ingewikkelder (zie VBA).

De For-Next-loop gaat dan alle elementen van dit bereik langs en plaatst de waarde rechts van de datum in het betreffende label.

Uiteraard is deze routine ook via een knop uit te voeren op het tabblad Result van het Voorbeeldbestand.

Titel van grafiek

De titel van de grafiek is dynamisch gemaakt:

  1. in cel N2 staat de formule:  =”Bedragen van “&C2
    Oftewel koppel de inhoud van cel C2 aan de tekst Bedragen van.
  2. zorg dat de grafiek een of andere titel heeft
  3. klik ergens in de titel
  4. tik dan in de formulebalk =N2
  5. en druk op Enter

 

Absoluut en relatief in Excel-tabellen

Excel, , ,
Voorbeeldbestand: TabelRef.xlsxDownload

Absolute en relatieve verwijzingen in Excel: dat blijft altijd even opletten!
Maar sinds we met zijn allen steeds meer gebruik maken van Excel-tabellen komt daar ineens een struikelblok(je) bij.

In dit artikel kijken we eerst in algemene zin naar de implementatie van tabel-verwijzingen (Gestructureerde verwijzingen of Structured references in Microsoft-taal); daarna komt een methode langs hoe we ook tabel-verwijzingen absoluut kunnen maken.

Voorbeeld-bestand

In het Voorbeeldbestand staat in het tabblad CelRef een overzicht van bedragen per kwartaal en regio.

Er loopt blijkbaar een afspraak dat, als het bedrag groter is dan 100, er dan een korting van 10% wordt gegeven.
De daarbij gehanteerde formule is:
=ALS(D3>100;10%;0)

Ofwel (geredeneerd vanuit cel E3) als de waarde in cel D3 groter is dan 100, dan komt in E3 de waarde 0,1 anders 0.

NB 10% is voor Excel slechts een opmaak van een numerieke waarde; intern wordt gerekend met 0,1.

De formule is F3 (het bedrag inclusief korting) kan dan zijn:
=D3*(1-E3)

Allebei de formules kunnen naar beneden gekopieerd worden; dat gaat het makkelijkst door dubbel te klikken op het vierkantje rechtsonder aan de rand van een cel (de vulgreep) als die cel is geselecteerd.

Aangezien alle cel-verwijzingen relatief zijn (er staan nergens $-tekens bij de cellen D3 en E3) zullen de formules zich automatisch aanpassen: D3 wordt D4 etc.

LET OP om kolom F niet te breed te maken is de tekst in cel F2 gescheiden door een harde return (Alt-Enter). Een kop nooit over 2 cellen verdelen; altijd een harde return gebruiken.

Willen we weten hoe de verdeling over de kwartalen en/of de regio’s is, dan ligt het maken van een draaitabel voor de hand.

LET OP een bug in Excel kan er bij het aanpassen van de opmaak van de data voor zorgen dat de kop die met een harde return is gescheiden, inhoudelijk wijzigt (alles vanaf de harde return wordt verwijderd); hier is dan een handmatige aanpassing nodig.

Tabel-verwijzingen

Wanneer we dezelfde gegevens in een Excel-tabel opnemen (zie het tabblad TablRef van het Voorbeeldbestand) dan gaat Excel bij het maken van de kortingsformules automatisch met gestructureerde verwijzingen werken in plaats van celverwijzingen:

  1. selecteer cel E3
  2. tik in =als(
  3. klik met de muis in cel D3; Excel plaatst automatisch de tekst [@Bedrag] in de formule
  4. maak de formule af en druk op Enter
  5. Excel zal automatisch de formule naar de overige cellen van dezelfde kolom kopiëren.

Gelukkig hoeven we de gestructureerde verwijzingen dus niet zelf te typen; de notatie daarvan is niet altijd even eenvoudig.
De vierkante haken om de diverse onderdelen van een formule maken voor Excel het verschil tussen gestructureerde en cel-verwijzingen.
Het ‘apestaartje’ (@) voor een kolomnaam betekent dat de waarde uit dezelfde rij uit de betreffende kolom wordt opgehaald.

LET OP in cel F3 staat de formule =[@Bedrag]*(1-[@[Korting%]]); aangezien het %-teken in Excel diverse betekenissen kan hebben zijn nog eens extra vierkante haken om de kolomnaam geplaatst.
Andere speciale tekens die eenzelfde behandeling nodig hebben:
tab, nieuwe regel, Enter-teken, komma (,), dubbele punt (:), punt (.), vierkante haak openen ([), vierkante haak sluiten (]), hekje (#), enkel aanhalingsteken (‘), dubbel aanhalingsteken (“), accolade openen ({), accolade sluiten (}), dollarteken ($), accent circonflexe (^), en-teken (&), sterretje (*), plusteken (+), gelijkteken (=), minteken (-), groter dan (>), kleiner dan (<) en het deelteken (/).

Totalen

Aan een tabel kunnen snel totalen toegevoegd worden:

  1. klik op een cel van de tabel
  2. kies in de menutab Hulpmiddelen voor tabellen de optie Ontwerpen
  3. en vink de optie Totaalrij aan
  4. in de nieuwe regel onderaan de tabel kan dan per kolom het soort totalen gekozen worden (Som, Aantal, Gemiddelde etc.)

Een draaitabel die gebaseerd is op een tabel met totalen, zal deze totalen buiten beschouwing laten (gelukkig!); zie tabblad TablRefTot van het Voorbeeldbestand.

Externe verwijzing naar een tabel

Wanneer u een bepaald overzicht wilt genereren en geen gebruik wilt/kunt maken van draaitabellen dan kunt u via externe verwijzingen gebruik maken van de tabel-gegevens:

  1. selecteer cel J21 in het tabblad TablRefTot
  2. tik in =som.als(
  3. wijs met de muis de bovenkant van de kolom Regio van de tabel daarnaast aan (de cursor wordt dan een pijltje naar beneden) en klik; Excel plaats automatisch de tekst tblKwReg2[Regio] in de formule
  4. tik in ;J$20; en voeg op dezelfde manier de kolom Bedrag toe

NB omdat het verwijzingen buiten de tabel zijn plaatst Excel automatisch de naam van de betreffende tabel voor de rechte haken.

Door de formule nu naar rechts te kopiëren krijgen we ook het totaal-bedrag van regio Zuid te zien. Tenminste: als we op de juiste manier kopiëren!
Selecteer cel J21, klik op Ctrl-C, selecteer cel K21 en klik Ctrl-V en alles gaat goed.
Maar wanneer de vulgreep rechtsonder cel J21 wordt gebruikt, gaat het mis! Dan wordt de verwijzing naar Regio ineens Bedrag en Bedrag wordt Korting%!
DUS: met kopiëren/plakken worden de externe verwijzingen als absoluut beschouwd, maar gebruiken we de vulgreep dan ziet Excel de verwijzingen als relatief!

Ditzelfde effect zien we ook als we verwijzingen naar de tabellen-koppen maken (niet de bovenkant van een kolom aanklikken, maar de cel zelf) en die kopiëren (zie cellen I34 tot en met K35 van het tabblad TablRefTot van het Voorbeeldbestand).

NB wanneer gegevens aan de tabel worden toegevoegd (selecteer cel F32 en druk op Tab; de totalen schuiven automatisch naar beneden) zullen alle overzichten automatisch de nieuwe gegevens meenemen (wel nog via rechtsklikken de draaitabel Vernieuwen).
Dit geldt ook voor formules die naar de totaalrijen verwijzen.

Absoluut en relatief in gestructureerde verwijzingen

In het tabblad TablRefTot2 van het Voorbeeldbestand is een resultaat-overzicht per regio en kwartaal opgenomen. Dit overzicht is zelf ook weer een tabel (met de naam tblResult). Om dit te realiseren wordt de functie SOMMEN.ALS gebruikt.

LET OP de syntax is echt anders dan van SOM.ALS.

De formule in cel J26 berekent de som van die bedragen uit de kolom Bedrag van de tabel tblKwReg2b, waarbij de Regio-kolom van die tabel de koptekst van de kolom Noord van tblResult bevat en waarbij de waarde in de Kwartaal-kolom van de tabel tblKwRegTot2b dezelfde is als die in de Kwartaal-kolom in dezelfde tabel en rij waar deze formule staat.

De formule in J26 kopiëren naar J27 levert geen problemen op welke methode ook gehanteerd wordt (kopiëren/plakken of de vulgreep).

Maar: in het voorbeeld is J26 naar K26 gekopieerd met behulp van de vulgreep; de verwijzingen worden dan als relatief beschouwd. Dat klopt voor de regio-kop in de resultaat-tabel (die moet Zuid worden) maar niet voor de verwijzingen naar de bron-tabel tblKwRegTot2.
Cel J27 is via kopiëren/plakken naar K27 gekopieerd. De verwijzingen blijven absoluut, maar dat klopt dan niet voor de regio-kop!

Oplossing: verwijzingen die absoluut moeten blijven moeten dubbel in de formule worden opgenomen, gescheiden door een dubbele punt (:).
De formule die hier nodig is wordt dan (zie cel J31):
=SOMMEN.ALS(tblKwReg2b[[Bedrag]:[Bedrag]];
tblKwReg2b[[Regio]:[Regio]]; tblResult2[[#Kopteksten];[Noord]];
tblKwReg2b[[Kwartaal]:[Kwartaal]]; tblResult2[@[Kwartaal]:[Kwartaal]])

Wanneer deze formule via de vulgreep naar rechts en naar beneden wordt gekopieerd, krijgen we in alle cellen de juiste resultaten!

NB1 alle kolomnamen hebben vierkante haken, maar de dubbelen krijgen daar omheen nog een extra set rechte haken. Let ook op de notatie bij de @.

NB2 ook de laatste parameter (Kwartaal) moet in dit geval een tabel-aanduiding krijgen, ook al is de verwijzing binnen de tabel zelf.