Tagarchief: voorwaardelijke opmaak

Data verrijken

23 april 2020ExcelDraaitabel, Gegevensmodel, Index, Power Query, Vergelijken, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: DataVerrijken Download

Iedere dag krijgen of vinden we wel gegevens, waarvan je denkt: interessant, maar als ik die met een andere set combineer (verrijken van data) kan ik er nog veel meer informatie uit halen.

In dit artikel geef ik uitleg over verschillende methoden zoals bestaande tabellen uitbreiden met extra kolommen, gebruik maken van het Excel-gegevensmodel en het gebruik van Power Query.

Brongegevens

We gaan als voorbeeld een Excel-tabel gebruiken met daarin het aantal verkochte producten, uitgesplitst naar datum, klant- en productcode.
In het tabblad Verkoop van het Voorbeeldbestand vindt u 1000 records in de Excel-tabel tblVerkoop.

Welke klanten komen hierin voor? Welke producten zijn verkocht? Over welke periode gaat dit bestand?
Meer vragen dan antwoorden.

Verkoopoverzicht

Om snel inzicht te krijgen in bovenstaande vragen maken we op basis van het bestand een draaitabel:

Plaats de cursor ergens in de tabel tblVerkoop.
Kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK.
Sleep de Datum naar de Rijen, Aantal naar het Waarden-gebied en Prod naar Kolommen.

We zien dus direct (tabblad OvzVerkoop) dat het 6 producten betreft en dat het over de periode 2018-2020 gaat.

Klik je op de + voor een jaar, dan zie je welke kwartalen en maanden er in het overzicht voorkomen.

NB1 vanaf versie 2016 zal Excel datums, die in de kolommen of rijen worden geplaatst, direct groeperen naar Jaren, Kwartalen en Maanden.
Gebeurt dit niet automatisch, klik dan met de rechtermuisknop op een datum en kies Groeperen; selecteer daar dan de gewenste opties.

NB2 wil je geen groepering naar kwartaal? Klik rechts op een van de tijdaanduidingen, kies Groeperen en deselecteer de optie Kwartalen.

Maar hoe zit het met de klanten?

Verwijder Prod uit de Kolommen.
Verplaats Jaren van Rijen naar Kolommen.
Sleep Klanten naar Rijen.

Zie het tabblad OvzVerkoop.

NB Excel plaatst de klanten bij het aanmaken van de draaitabel netjes in alfabetische volgorde, maar door de methode van codering komt K10 direct na K1. Gelukkig is dit snel verholpen: selecteer de cel met K10, ‘pak’ met de muis de rand en sleep de code naar beneden.

‘Toevallig’ weet ik dat de klanten over 2 regio’s zijn verdeeld: K2, K5, K7 en K8 horen bij de regio Zuid, de anderen bij Noord.
Die moeten we nu dus nog groeperen (zie Groeperen in een draaitabel): selecteer de draaitabel-rij met K2 (het muis-symbool is dan een pijltje naar rechts), houd Ctrl ingedrukt en selecteer op dezelfde manier K5, K7 en K8. Klik rechts en kies Groeperen. Nog even de namen van de groepen aanpassen (gewoon overschrijven) en de groep Noord naar boven verslepen: klaar!

Op deze manier hebben we handmatig de eerste verrijkingen aangebracht.

Verrijking door toevoegen kolommen

Maar we weten nog meer over onze klanten en de producten:

Hiermee kunnen we onze dataset uitbreiden. Dezelfde gegevens van het tabblad Verkoop zijn allereerst gekopieerd naar VerkBerek van het Voorbeeldbestand.

Aan de Excel-tabel (met de naam tblVerkBerek) zijn nieuwe kolommen toegevoegd:

Regio, in cel F3 staat de formule:
=INDEX(tblKlant[Regio];VERGELIJKEN([@Klant];tblKlant[Klant];0))
NB1 hier is een alternatief voor verticaal zoeken gebruikt. De avz-truc komt goed van pas! Zie zoeken-index-en-vergelijken.
NB2 alle berekeningen worden binnen een Excel-tabel uitgevoerd, waardoor we gestructureerde verwijzingen kunnen gebruiken. Zie voor de voordelen van tabellen: kunst-en-excel.
NB3 na het invoeren van de formule in F3 wordt deze automatisch naar beneden doorgevoerd, zodat alle cellen in die kolom een consistente formule hebben.
In cel G3 halen we op een vergelijkbare manier de naam van het product op:
=INDEX(tblProd[ProdNaam];VERGELIJKEN([@Prod];tblProd[Product];0))
De totale kosten, die met een verkoop zijn gemoeid worden in cel H3 bepaald:
=[@Aantal]* INDEX(tblProd[KostPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
In cel I3 bepalen we de omzet:
=[@Aantal]* INDEX(tblProd[VerkPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
Daarmee kunnen we in J3 de bruto-winst berekenen:
=[@Omzet]-[@Kosten]
Maar (sommige) klanten krijgen korting; in cel K3:
=INDEX(tblKlant[Korting];VERGELIJKEN([@Klant];tblKlant[Klant];0))
Waarmee we ook een netto-winst kunnen bepalen in cel L3:
=[@Omzet]*(1-[@Korting])-[@Kosten]

Op basis van deze nieuwe dataset kunnen we diverse analyses uitvoeren, uiteraard met behulp van een draaitabel. Bijvoorbeeld (zie tabblad OvzVerkBerek van het Voorbeeldbestand):

Verrijken m.b.v. gegevensmodel

Een nadeel van de vorige methode is, dat het bestand meteen een stuk groter wordt wanneer er kolommen aan een tabel worden toegevoegd. Dat valt nog wel mee als het over 1.000 records gaat, maar als het er een miljoen zijn en als het meer kolommen betreft….

De koppelingen, die we hiervoor met Index/Vergelijken hebben gemaakt, kunnen sinds versie 2013 ook intern in Excel met behulp van een gegevensmodel worden vastgelegd zonder dat dit extra ruimte in beslag neemt. Wel moeten de basisgegevens in Excel-tabellen vastliggen. In het Voorbeeldbestand bevatten de tabbladen Verkoop, Klant en Product onze basisgegevens.

Nu gaan we het gegevensmodel vullen door de relaties tussen deze tabellen vast te leggen:

Kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Relaties (de button met 3 tabellen en lijntjes daar tussen).

U legt een relatie vast door op de knop Nieuw te klikken:

De eerste tabel moet de gegevens bevatten, die uitgebreid gaan worden; in dit geval dus tblVerkoop.
We gaan via de klant-code gegevens opzoeken, dus geven als kolom Klant op.
Daaronder moeten we aangeven in welke tabel we gaan zoeken, in het huidige voorbeeld tblKlant.
Ook in deze tabel bevat de kolom Klant de overeenkomende gegevens.
Klik op OK.
NB1 in de laatste stap staat het woord primair; hiermee wordt bedoeld, dat de kolom unieke gegevens moet bevatten. Anders kan er geen relatie aangemaakt worden.
NB2 heb je meer dan 1 kolom nodig om een relatie te leggen (bijvoorbeeld Naam en Afd) dan zul je een kunstgreep moeten uithalen: in beide tabellen moet je de 2 kolommen aan elkaar koppelen in een nieuwe kolom, bijvoorbeeld Naam&Afd.
Maak op dezelfde manier ook een relatie tussen tblVerkoop en tblProd; de kolomnamen die daarbij gebruikt worden zijn respectievelijk Prod en Product.
De namen hoeven dus niet hetzelfde te zijn.
Sluit het scherm Relaties beheren.

Nu het gegevensmodel is gevuld kunnen we op basis daarvan overzichten maken met behulp van draaitabellen (in het tabblad OvzVerkModel1 van het Voorbeeldbestand staat een voorbeeld):

Plaats de cursor in een lege cel. Hier zal het overzicht komen, dus meestal doe je dit op een nieuw tabblad.

Start via Toevoegen/Draaitabel een nieuwe draaitabel
Hiernaast is te zien, dat Excel nu automatisch weet, dat we ons gegevensmodel als bron willen gebruiken. Klik OK.

Het overzicht van draaitabel-velden ziet er iets anders uit dan bij een ‘normale’ draaitabel.
Alle tabellen uit het gegevensmodel zijn beschikbaar met alle bijbehorende kolommen.
Hiernaast zijn alle 4 gebieden gevuld met gegevens uit 3 verschillende tabellen.
NB1 bevat de werkmap nog meer Excel-tabellen (net als het Voorbeeldbestand), dan zijn die ook in dit overzicht zichtbaar.
NB2 heb je velden naar de gebieden versleept en klik je bovenaan op Actief, dan zie je alleen de tabellen die gebruikt zijn in dit overzicht.

Helaas: op deze manier kun je alleen velden uit de basis-tabel (in het voorbeeld tblVerkoop) in het waarden-gebied plaatsen. Sleep je bijvoorbeeld Korting in het waarden-gebied dan krijg je de volgende melding:

Dit scherm krijg je ook als je niet alle tabellen via relaties met elkaar hebt verbonden, maar wel kolommen uit die tabellen gebruikt.

Wat nu wel kan: in de draaitabel kunnen unieke waarden geteld worden. Laten we even via een voorbeeld kijken (tabblad OvzVerkModel2 van het Voorbeeldbestand):

Maak een draaitabel aan zoals hiervoor beschreven.
Plaats Regio en Klant in de Rijen.
Sleep Aantal naar het Waarden-gebied.
Sleep daarna 2x de Datum naar het Waarden-gebied.
Klik op het vinkje achter Datum2 en kies Waardeveldinstellingen.
Helemaal onderaan krijg je nu de mogelijkheid om te kiezen voor Uniek aantal.

Telling van Datum geeft het aantal datums weer per klant, dus eigenlijk het aantal keer dat een klant voorkomt (als de datum is gevuld); het totaal is dan ook precies 1.000.

Unieke telling van Datum2 laat zien hoe vaak een unieke datum voorkomt bij een klant.

Verrijken m.b.v. Power Query

En dan nu een oplossing die als een soort combinatie van de vorige 2 gezien kan worden: Power Query. Binnen dit Excel-onderdeel leggen we de verbanden tussen de tabellen vast én we maken daar allerlei berekeningen die we nodig hebben:

Selecteer een cel in de tabel tblKlant.
Kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.

In principe zijn alle kolommen in orde. Aangezien Korting een percentage is, kunnen we de lay-out nog aanpassen:
* klik op de 1.2 naast Korting
* wijzig de instelling naar Percentage.

Kies dan het vinkje naast Sluiten en laden.
Klik op Sluiten en laden naar.
Zorg dat de optie Alleen verbinding maken is geselecteerd en klik op de button Laden.
Voer de vorige stappen ook uit voor de tabel tblProduct. Zorg dat KostPrijs en VerkPrijs de instelling Decimaal getal krijgen.

Als laatste doet u hetzelfde met de tabel tblVerkoop, maar nog NIET sluiten. Wijzig de opmaak van de kolom Datum in alleen Datum.
Voordat we Power Query afsluiten moeten we nog relaties leggen tussen de 3 tabellen.
Kies het vinkje achter Query’s samenvoegen en kies de optie Samenvoegen als nieuw.

Maak de verbindingen zoals hierboven en klik op OK. Wijzig de naam van de nieuwe query in PQtblVerkoop.
Om de gegevens uit tblKlant als extra kolommen toe te voegen klikt u op het symbool rechts van tblKlant. Stel de opties in zoals hierboven en klik OK.
Doe hetzelfde met tblProduct.
Nu gaan we nog wat extra kolommen toevoegen: klik in de kolom Datum, klik op de menutab Kolom toevoegen en kies binnen de optie Datum voor het Jaar. Doe hetzelfde om de maand, de naam van de maand en het kwartaal toe te voegen.
Kies daarna de optie Aangepaste kolom binnen Kolom toevoegen. De naam wordt Omzet en de formule =[Aantal]*[VerkPrijs].
Voeg op dezelfde manier nog 3 kolommen toe:
Kosten: =[Aantal]*[KostPrijs]
Brutowinst: =[Omzet]-[Kosten]
Nettowinst: =[Aantal]*([VerkPrijs]*(1-[Korting])-[Kostprijs])
Zorg dat de 4 toegevoegde kolommen de instelling Decimaal getal hebben.

Kies nu Sluiten en laden en zorg dat er alleen een verbinding tot stand wordt gebracht.
LET OP zorg wel dat deze query PQtblVerkoop in de laatste stap wordt toegevoegd aan het gegevensmodel.

Nu staat alles klaar om overzichten te maken. Op het tabblad OvzVerkPQ van het Voorbeeldbestand vindt u een voorbeeld:

De gegevens van de bruto- en nettowinst zijn niet als getallen zichtbaar. Met behulp van Voorwaardelijke opmaak hebben die gegevensbalken gekregen:

Selecteer een cel in een betreffende rij.
Kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak.
Kies dan Gegevensbalken en een kleur(overgang).
Naast de cel staat nu het tekentje van een draaitabel, kies daar de onderste optie.
Via Regels beheren/Regel bewerken de optie Alleen balk weergeven aanvinken.
Pas de rijhoogte naar wens aan.

De Aantallen hebben op een vergelijkbare manier een Voorwaardelijke opmaak gekregen, namelijk Kleurenschalen.

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd? Door te klikken op de button

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd?
Door te klikken op de button Alles vernieuwen in het blok Verbindingen van de menutab Gegevens, worden alle koppelingen, het gegevensmodel en alle draaitabellen ververst.

Corona-data

27 maart 2020ExcelDraaigrafiek, Draaitabel, SOMMEN.ALS, Tekst, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand van 29-3: Corona.xlsx Download

Vanaf 31 maart publiceert het RIVM andere gegevens dan daarvoor. Helaas is het dus niet meer mogelijk om via het voorbeeldbestand de (ver)spreiding van Corona op een consistente manier te volgen.
Op de site https://nlcovid-19-esrinl-content.hub.arcgis.com/ zijn wel nog diverse overzichten en kaarten te vinden.

Corona: de laatste weken beheerst deze crisis niet alleen het nieuws maar ook ons leven. Het einde is nog niet in zicht.
Het zou mooi zijn als we met Excel een bijdrage zouden kunnen leveren aan de oplossing er van.
Helaas, maar wat we wel kunnen, is proberen inzicht te geven in de omvang en voortgang van de besmettingen en overlevenden.

Het RIVM publiceert dagelijkse nieuwe gegevens en ook een kaartje dat de verspreiding van de besmettingen laat zien.

Voor G-Info is dit een goede aanleiding om eens te kijken hoe we van gegevens, die we over Corona kunnen vinden, informatie kunnen maken. Niet voor niets is onze hoofddoelstelling: van Gegevens naar Informatie.

In dit artikel gaan we eerst op zoek naar gegevens rond Corona. Daarna kijken we welke informatie we daaraan kunnen ontlenen. We zullen daarom diverse (vaak met behulp van draaitabellen gemaakte) overzichten bekijken. Als laatste zult u zien dat Voorwaardelijke opmaak heel handig is om snel binnen een grote hoeveelheid gegevens de uitschieters te signaleren.

Brongegevens

In dit artikel focussen we ons op de situatie in Nederland. Het is dan ook logisch dat we terecht komen bij het RIVM. Dit instituut publiceert iedere dag een update van de situatie op hun website rivm.nl.

Op deze site hebben we vanaf de uitbraak in Nederland kunnen terugvinden hoeveel mensen er besmet zijn geraakt en hoeveel daarvan er ondertussen zijn overleden (zie het tabblad DataNed van het Voorbeeldbestand).

Deze gegevens zijn dagelijks handmatig ingevoerd in een Excel-tabel met de naam tblNed. De kolom Cum bevat een formule, die een lopend cumulatief bepaalt (in cel D9 staat bijvoorbeeld =D8+C9); op een vergelijkbare manier worden ook de 5e en 7e kolom gevuld. In de laatste kolom (Actief) berekenen we het aantal personen, dat op dit moment nog besmet is: =[@Cum]-[@CumHerst]-[@CumOvl].

NB1 het RIVM geeft aan, dat de door hen gehanteerde cijfers geen exacte waarheid weergeven:
“Het werkelijke aantal besmettingen met het nieuwe coronavirus ligt hoger dan het aantal dat hier genoemd wordt. Dit komt omdat niet iedereen met mogelijke besmetting getest wordt, maar vooral patiënten die zo ziek zijn dat ze in het ziekenhuis opgenomen worden en zorgverleners.
Het aantal gemelde patiënten en overleden patiënten kan per dag verschillen om verschillende redenen. Zo zien we dat overleden patiënten niet altijd op dezelfde dag gemeld worden.”
Voor inzicht in de verspreiding lijken ze mij echter significant genoeg.

NB2 gegevens over personen, die hersteld zijn, zijn niet bekend bij het RIVM of worden niet geregistreerd. Op de website van de Johns Hopkins University over Corona worden wel aantal vermeld. Of deze op dit moment de werkelijkheid benaderen betwijfel ik.

NB3 dit artikel is in de loop van enkele dagen geschreven. Aangezien de werkmap in die dagen continu is bijgewerkt kunnen data in de afbeeldingen afwijken van die in het Voorbeeldbestand.

NB4 boven de tabel staat een dynamische kopregel; de inhoud past zich aan aan de datums in de tabel:
=”Corona in Nederland van “&TEKST(MIN(tblNed[Datum]);”d mmm jjjj”)&” t/m “&TEKST(MAX(tblNed[Datum]);”d mmm jjjj”)
Teksten worden aan elkaar gekoppeld met behulp van het &-teken; de minimum- en maximum-datum wordt met behulp van de functie Tekst van het gewenste formaat voorzien.

Als tweede bron is dagelijks een bestand gedownload van de RIVM-site (via het pijltje naast de kaart met gemelde Corona-gevallen).

In het Voorbeeldbestand is dit geautomatiseerd via de Power Query-tabel op het tabblad DagInput. Het bestand bevat het totaal aantal bekende Corona-gevallen per gemeente. Om de resultaten van de gemeentes met elkaar te kunnen vergelijken is dit aantal genormaliseerd (de kolom Aantal per 100.000 inwoners), namelijk door te delen door het aantal inwoners van die gemeente (en met 100.000 te vermenigvuldigen).

Deze gegevens (zonder de kopregel) zijn iedere dag gekopieerd naar de Excel-tabel tblGem op het tabblad DataGem van het Voorbeeldbestand.

LET OP vanaf 27 maart bevat het bestand van het RIVM 4 namen van gemeenten, die anders gespeld worden dan daarvoor:
Bergen (L.) moet zijn: Bergen (L)
Bergen (NH.) moet zijn: Bergen (NH)
Hengelo moet zijn: Hengelo (O)
s-Gravenhage moet zijn: ‘s-Gravenhage (in de Excel-cel moet dus een dubbele ‘ komen)
De Power Query routine is hier op aangepast, anders met de hand wijzigen in de tabel tblGem.

Een ander bronbestand (zie het tabblad ProvGemeente) is een overzicht van alle gemeentes in Nederland met daarachter het inwoneraantal, een indeling naar klein, middelgroot en groot en de bijbehorende provincie.

NB De gegevens zijn ontleend aan de website van het Ministerie van Sociale Zaken en Werkgelegenheid (stand van 1 jan 2019).

Overzicht stand van zaken Nederland

Het overzicht in het tabblad DataNed van het Voorbeeldbestand laat het verloop in de tijd zien van het aantal besmettingen, overledenen en herstelden. Maar hoe de getallen zich tot elkaar verhouden is moeilijk te onderscheiden. Een grafiek is geschikter om dit te laten zien:

De eerste grafiek (staafdiagram) laat het aantal nieuwe geregistreerde besmettingen, herstelden en overledenen per dag zien (tabblad GrafNed1).
De lijngrafiek (tabblad GrafNed2) toont de cumulatieven daarvan in de tijd.
In de derde grafiek (tabblad GrafNed3) wordt door het gebruik van vlakken de opdeling van het aantal cumulatieve besmettingen zichtbaar gemaakt.

NB1 bij het schrijven van dit artikel was het aantal herstelden nog gering (volgens de gehanteerde bronnen); resultaten daarvan zijn in de grafieken dan ook nauwelijks/niet zichtbaar.

NB2 in de laatste grafiek moet het oranje vlak (de actieve besmettingen) in de loop van de tijd steeds meer de veel besproken ‘uitgevlakte’ curve laten zien. Het groene vlak (herstelden) zal steeds groter moeten worden. Een fictief voorbeeld staat hiernaast.

Overzicht per gemeente, basis

Zoals bij de bronnen aangegeven bevat het tabblad DataGem van het Voorbeeldbestand de totaal-aantallen geregistreerde besmettingen per gemeente per dag.

Om hierna diverse analyses op de gegevens uit te kunnen voeren zijn de brongegevens in deze tabel verrijkt:

in kolom F wordt de dagelijkse groei van het aantal voor een gemeente berekend:
=[@Aantal]-SOMMEN.ALS([Aantal];[Datum];[@Datum]-1;[Plaats];[@Plaats])
Trek van het Aantal in een bepaalde rij het Aantal af van de vorige dag bij die gemeente.
NB SOMMEN.ALS telt alle Aantallen op waarvan de Datum gelijk is aan de Datum in die regel minus 1 en waar de Plaats gelijk is aan de Plaats in die regel. In principe is er altijd maar 1 geval, die aan deze voorwaarden voldoet.
de indeling van de gemeente naar grootte wordt in kolom G bepaald door dat gegeven op te zoeken in tblGemProv in het tabblad ProvGemeente:
=INDEX(tblGemProv[Grootte];VERGELIJKEN([@Plaats];tblGemProv[Gemeentenaam];0))
op een vergelijkbare manier worden in de kolommen H en I respectievelijk de provincie en het aantal inwoners van een gemeente opgehaald.
de genormaliseerde aantallen per gemeente (uitgedrukt in Aantal besmettingen per 100.000 inwoners) wordt door het RIVM aangeleverd; kolom D.
Om provincies goed met elkaar te kunnen vergelijken moeten ook de aantallen per provincie worden genormaliseerd. In kolom J wordt de benodigde berekening uitgevoerd:
=[@Aantal]/ INDEX($N$5:$N$16;VERGELIJKEN([@Prov];$M$5:$M$16;0)) *100000
NB de kolommen M en N bevatten een draaitabel die het aantal inwoners per provincie bepaald.

Overzicht per provincie (1)

Waar in Nederland zitten de meeste Corona-gevallen? Met behulp van een draaitabel kunnen we snel een overzicht per provincie maken (gerangschikt van noord naar zuid; zie tabblad OvzProv1 van het Voorbeeldbestand):

Uit de bijbehorende draaigrafiek kunnen makkelijker conclusies getrokken worden (tabblad GrafProv1):

in absolute zin is het aantal besmettingen vanaf het begin (van de registratie in dit Excel-bestand, 19 maart) het grootst in Noord-Brabant, gevolgd door Noord- en Zuid-Holland en Gelderland en Limburg.
Deze laatste provincie kende een relatief grote groei op 24 maart.
De hoop dat de groei in Brabant zou gaan afvlakken zien we nog niet terug (dan zouden de lijnen daar steeds dichter bij elkaar moeten gaan liggen; op 27 maart zien we zelfs weer een groei) .
Ook neemt de groei in Noord- en Zuid-Holland toe.
In Limburg blijft de groei de laatste dagen gelijk.
De noordelijke provincies en Flevoland en Zeeland lijken nog weinig ‘geraakt’.

LET OP wanneer er gegevens voor een nieuwe datum zijn toegevoegd dan moet de draaitabel vernieuwd worden. Aangezien alle draaitabellen in deze werkmap dezelfde bron gebruiken (tblGem) worden al deze draaitabellen dan tegelijkertijd ververst.

Overzicht per provincie (2)

Wanneer we eenzelfde draaitabel sorteren van ‘hoog naar laag’ op de gegevens van de laatste dag (tabblad OvzProv2) dan ziet de bijbehorende draaigrafiek er als volgt uit (tabblad GrafProv2):

Noord- en Zuid-Holland gaan ongeveer gelijk op
Gelderland ‘haalt Limburg langzaamaan in’

Overzicht per provincie (3)

Maar als je provincies echt met elkaar wilt vergelijken moet je ook de verschillen in grootte daarbij betrekken. Op het tabblad OvzProv3 is daarom een draaitabel gemaakt van de genormaliseerde aantallen:

relatief zijn er dus in Brabant en Limburg de meeste besmettingen (toch gerelateerd aan Carnaval?)
we horen weinig over Utrecht, maar die komt in dit overzicht op de 3e plaats
Noord-Holland heeft relatief duidelijk meer besmettingen dan Zuid-Holland
de provincie Zeeland kende in het begin relatief evenveel besmettingen als Zuid-Holland; deze laatste vertoont echter in de loop van de tijd een grotere groei.
maar het virus moet ook in de ‘kleinere’ provincies niet onderschat worden

Overzicht per gemeente (1)

Voor diegene die nog wat ‘dieper willen kijken’ kunnen we op basis van de RIVM-cijfers op gemeenteniveau inzoomen. Het mooiste is natuurlijk om dit op een kaart zichtbaar te maken. De stand van 24 maart hebben we met behulp van Datawrapper.de ‘vertaald’:

*Nieuwsgierig naar details? Klik op de afbeelding.*

‘Uiteraard’ komen daarmee de grote plaatsen boven drijven. Dat is ook de reden, dat het RIVM vanaf het begin een genormaliseerde kaart heeft getoond. Dat hebben we voor 24 maart ook gedaan:

Op deze manier is beter te onderscheiden waar het virus zich vooral heeft verspreid. Naast de bekende plaatsen in Oost-Brabant zien we ook mogelijke haarden in Alphen-Chaam en Peel en Maas.

Aangezien bovenstaande methode nogal bewerkelijk is, zullen we voor een verdere detaillering gebruik maken van draaitabellen.
Op het tabblad OvzGem1 van het Voorbeeldbestand ziet u per provincie de verdeling naar gemeente. Om goed zicht te krijgen op de groei per dag kunt u (handmatig) een sortering aanbrengen; in het voorbeeld is dit op de laatste kolom uitgevoerd.

Vooral in de plaatsen aan de oost-kant van Brabant is het aantal besmettingen weer fors toegenomen. Op 27 maart kent ook Boekel weer 9 nieuwe gevallen, terwijl de dagen daarvoor de groei nul was.

NB1 in het voorbeeld hierboven zijn enkele dagen niet zichtbaar; via Beeld/Blokkeren is in cel E7 een Titelblokkering geplaatst.

NB2 wilt u een andere provincie zien? Maak een keuze in cel C2.

Overzicht per gemeente (2)

Grote steden kennen in absolute zin al snel veel besmettingen. Het effect daarvan kunnen we bekijken op het tabblad OvzGem2 van het Voorbeeldbestand.

Overzicht per gemeente (3)

Maar pieken zijn duidelijker te zien wanneer we de genormaliseerde aantallen in een draaitabel zetten (zie tabblad OvzGem3 in het Voorbeeldbestand):

In deze draaitabel is 2 keer het veld Aantal per 100.000 inwoners geplaatst.

Door rechts te klikken op de 2e kolom kunnen de Waardeveldinstellingen aangepast worden. Zoals u hiernaast kunt zien, zal Excel het Verschil met de Vorige Datum berekenen.

NB De kolomnamen zijn handmatig aangepast.

Voor allebei de waardevelden is een Voorwaardelijke opmaak ingesteld zodat uitschieters snel zichtbaar worden. Kijk in de menutab Start in het blok Stijlen bij Voorwaardelijke opmaak/Regels beheren.

LET OP wanneer er gegevens van een nieuwe datum bij zijn gekomen dan moet ook voor de nieuwe kolommen de Voorwaardelijke opmaak ingeregeld worden in de kolom Van toepassing op.

Klimaat-streepjescode

16 januari 2020ExcelDraaitabel, Trendlijn, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: GemTemp.xlsx Download

Het KNMI publiceerde vorige week een klimaat-streepjescode. Zonder veel extra informatie is direct te zien wat de hoofdconclusie is!

Behalve dat de achterliggende oorzaak niet blij-makend is, is dit wel een mooie aanleiding om te kijken of we in Excel een dergelijk overzicht kunnen maken.

Brongegevens

Het KNMI stelt van een 10-tal weerstations de historische gemiddelde temperaturen ter beschikking; gemiddeld per jaar en per maand.
In het tabblad Data van het Voorbeeldbestand staat een link naar één van de bestanden.

Met behulp van Power Query (zie dit artikel) zijn deze bestanden binnengehaald en in één totaal-tabel, tblBronData, geplaatst (zie het tabblad Brondata van het Voorbeeldbestand).

Jaar-overzicht

Het tabblad OvzJr van het Voorbeeldbestand bevat een draaitabel gemaakt op basis van deze brongegevens. In het filterblok hebben we alleen voor jaarcijfers gekozen.
Nu is snel te zien dat de meeste weerstations pas cijfers vanaf 2006 hebben. Ook zien we dat Nederland nog een behoorlijke variatie in de gemiddelde temperatuur kent (gemiddeld over alle jaren een verschil van 1,5 graad tussen Eelde en Vlissingen).
Enkele stations hebben geen cijfers voor de jaren 1944 en 1945.

NB1 de Waardeveldinstellingen van de gemiddelde temperatuur is gewijzigd van de standaardinstelling Som naar Gemiddeld, zodat in de eindkolom een gemiddelde over alle stations ontstaat.

NB2 de tekst Gemiddelde van GemTemp linksboven in de tabel is gewijzigd; wel staat er een spatie achter GemTemp, anders geeft Excel een foutmelding.

Maand-overzicht

In het tabblad OvzMnd van het Voorbeeldbestand is een andere draaitabel op basis van de brongegevens gemaakt. Per weerstation zijn daar de maandgemiddelden per jaar terug te vinden.

NB bij Maand is het item Year uitgevinkt.

Maandgrafiek

Meestal geeft een grafiek een beter inzicht in het verloop in de tijd dan een tabel met getallen.
In het tabblad GrafMnd van het Voorbeeldbestand ziet u het resultaat:

Door de grote hoeveelheid gegevens valt hier weinig te concluderen. Wel hebben we een (lineaire) trendlijn toegevoegd (rechts-klikken op de grafiek) en dan zien we dat er een stijgende lijn is. Voor iedere stap op de x-as (dus iedere maand) gaat de temperatuur gemiddeld 0,0013 graad omhoog; dat is per jaar dus ruim 0,015 graden en per 100 jaar 1,5 graad, ruwweg de spreiding van het gemiddelde over Nederland.

NB de is een maat voor de betrouwbaarheid van de trendlijn; hoe dichter bij 1 hoe beter. Zie het artikel Trend-analyse.

Jaargrafiek

Dan maar de maandgegevens weglaten en ons focussen op het jaargemiddelde.
Dat zegt nog steeds niet veel (zie het tabblad GrafJr van het Voorbeeldbestand).

Daarom de y-as wat aanpassen zodat de variaties per jaar beter zichtbaar worden:

Duidelijk is te zien dat het jaargemiddelde een stijgende lijn vertoont, ondanks de diverse uitschieters naar beneden.
De stijging van de trendlijn is bijna 0,017 graad per jaar; de betrouwbaarheid is natuurlijk vele malen groter omdat we alleen naar het jaar-gemiddelde kijken.

NB dit artikel gaat niet over trendanalyse; misschien is een lineaire trend hier niet de meest relevante.

Klimaat-streepjescode

Deze manier om klimaatdata weer te geven is bedacht door klimaatwetenschapper Ed Hawkins (onder de naam ‘warming stripes’); dit om de langjarige trend van temperaturen onder de aandacht te brengen.

Maar hoe maken we dit in Excel? Het lijkt op een grafiek maar is het niet; we gaan Voorwaardelijke opmaak gebruiken (zie het tabblad WarmStr in het Voorbeeldbestand):

we maken een draaitabel net als in het tabblad GrafJr, maar we zorgen er voor dat de jaren naast elkaar komen (dus plaatsen Jaar in de Kolommen).
NB door een kopie te maken van de draaitabel lopen de filteringen/selecties, die we later toepassen, synchroon. De draaitabellen laten allemaal dezelfde gegevens zien maar op een andere manier.
in rij 4 staat om de 10 cellen een verwijzing naar rij 9 (de jaren)
zorg dat in het Filterblok alle stations zijn gekozen en selecteer dan alle gevulde cellen in rij 10.
kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
kies bij Kleurenschalen de zesde optie (rood-wit-blauw)
maak de kolommen smaller en rij 10 hoger
zorg dat rijen en kolommen, die het beeld ‘vertroebelen’, niet zichtbaar zijn. Selecteer de betreffende rijen (of kolommen) en kies in de menutab Gegevens in het blok Overzicht de optie Groeperen.

NB1 gebruik Groeperen en NIET Verbergen. Een groot nadeel van de 2e methode is namelijk dat anderen (en jijzelf!) niet in de gaten hebben dat er iets niet zichtbaar is.

NB2 wil je de kleuren aanpassen? Ga naar Voorwaardelijke opmaak/Regels beheren.

Maak een kopie van de draaitabel, plaats die er onder en sorteer die op oplopende waarde van de gemiddelde temperatuur en u hebt een mooie temperatuurlegenda. Deze verandert automatisch mee bij verandering van weerstation-keuze.

Analyse

Maar we kunnen de trend binnen de basisgegevens nog verder (en preciezer) analyseren (zie het tabblad Analyse van het Voorbeeldbestand).
De richting van de trendlijn kunnen we ook bepalen zonder de trendlijn te tekenen:
=RICHTING(C6:C124;B6:B124)

De waarde voor b in de formule voor de rechte lijn bepalen we op een vergelijkbare manier met de functie Snijpunt.

NB Snijpunt bepaalt de waarde voor b in het jaar 0 (nul). In cel G4 staat een gecorrigeerde formule, zodat b de waarde geeft voor het eerste jaar uit de reeks (in het voorbeeld 1901): =SNIJPUNT(C6:C124;(B6:B124)-B6+1)

De wordt bepaald met de functie R.KWADRAAT.

Om de richting van de trend (en de bijbehorende ) op bepaalde tijdsintervallen te kunnen bepalen heb ik nog een apart blokje berekeningen toegevoegd:

Via een begin- en eindjaar wordt bepaald welke rijen uit de kolommen B en C we in de berekening mee willen nemen (in het voorbeeld hier de rijen 10 t/m 20). Bij het berekenen van de richting nemen we het betreffende blok mee door middel van de functie Verschuiving:

Bekijken we de gemiddelde resultaten voor heel Nederland tot en met 1980 , dan zien we dat de gemiddelde jaarlijkse temperatuurstijging op 0,0056 uitkomt met een lage betrouwbaarheid.

Terwijl de gemiddelde stijging in de laatste 40 jaar bijna een factor 10 groter is. Daarbij is de betrouwbaarheid van die trend ook een factor 10 hoger.

Power Query

4 december 2019ExcelImporteren, Ophalen en transformeren, Power Query, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: PowerQuery.zip Download

Waarschijnlijk is deze toevoeging aan Excel de grootste sprong voorwaarts sinds de introductie van Draaitabellen.
De eerste versies zijn ongeveer 10 jaar geleden door Microsoft vrijgegeven als een gratis add-in; sinds Excel-versie 2016 is het standaard beschikbaar, maar dan onder de naam Ophalen en Transformeren.

Deze ‘nieuwe’ naam geeft ook goed aan, waar Power Query vooral voor bedoeld is: ondersteuning bij het binnenhalen van externe bestanden, inclusief het gestandaardiseerd aanpassen en verrijken van de gegevens. Met gestandaardiseerd bedoel ik, dat alle handelingen die verricht moeten worden om externe gegevens in Excel in te lezen, vastliggen in een geautomatiseerde procedure. En deze procedure wordt dan iedere keer (dag, maand, kwartaal) op precies dezelfde manier doorlopen zodat fouten, die bij handmatige acties nog wel eens willen voorkomen, worden vermeden.
Niet alleen leuk voor u, maar ook voor uw controller en interne en externe accountant!

In dit artikel komen de eerste grondbeginselen en de basis-werking van Power Query aan bod.

Extern bestand inlezen

In het Voorbeeldbestand (een zip-bestand; pak dit uit en plaats de 3 bestanden ergens op uw PC) zitten ook twee tekstbestanden (Serie1.txt en Serie2.txt). We gaan het eerste bestand met Power Query/Ophalen en transformeren (hierna altijd met PQ aangeduid) in Excel inlezen:

kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
kies in het vervolg-menu de optie Uit bestand en daarna Uit tekstbestand
zoek het bestand Serie1.txt uit het Voorbeeldbestand op en kies Importeren

4. Excel interpreteert zo goed mogelijk de gegevens; hij ‘ziet’ welk scheidingsteken er is gebruikt etc.
De gegevens zien er goed uit, inclusief een kopregel, dus we kunnen deze gaan laden in Excel: kies het ‘vinkje’ naast Laden en kies de optie Laden naar …
5. zorg dat de button bij Tabel is geselecteerd en kies Laden

Het resultaat van de query komt automatisch in een Excel-tabel (in dit geval met de naam Serie1). Rechts in het scherm ziet u dat de query 15 rijen heeft binnengehaald (zie het tabblad Tekst in de werkmap PowerQuery.xlsx in het Voorbeeldbestand).

NB het overzicht is alleen maar zichtbaar als in de menutab Gegevens in het blok Ophalen en transformeren de optie Query’s weergeven is aangeklikt.

Maar…
het zou mooier zijn, als de namen in Soort met een hoofdletter zouden beginnen en het resultaat op datum zou zijn gesorteerd. Dit kan natuurlijk makkelijk in Excel, maar we gaan er voor zorgen dat dit direct iedere keer bij het inlezen al gebeurt.

Extern bestand inlezen en transformeren

Op dezelfde manier als hiervoor gaan we Serie1.txt opnieuw inlezen maar vanaf stap 4 wordt het anders:

4. kies de optie Gegevens transformeren

5. in de Power Query-editor is te zien welke stappen in de procedure Excel al heeft uitgevoerd: een Bron gekozen, iets met de kopregel en een Type gewijzigd (voor details: klik op een van de stappen en kijk in de formulebalk van de editor).
NB Power Query gebruikt een eigen programmeertaal, M. In dit artikel gaan we daar verder niet op in; wel belangrijk om te weten is, dat deze programmeertaal hoofdlettergevoelig is.
We gaan een stap toevoegen, namelijk het sorteren op datum: klik op het vinkje naast Datum en kies de optie Oplopend sorteren.
Er komt dan een procedure-stap bij: Rijen gesorteerd.

6. nu moet Soort nog met een hoofdletter beginnen. Dit doen we door een nieuwe kolom toe te voegen.

‘Normaal’ zou je kiezen voor een Aangepaste kolom toevoegen, maar de programmeurs van Microsoft hebben een prachtige optie Kolom toevoegen vanuit voorbeelden bedacht: klik op de tabel-button links van de kolomnamen.
In de eerste regel staat (na de sortering) bij Soort de tekst een; in het invulveld onder de nieuwe kolomkop Kolom1 tikken we Een en drukken op Enter.
PQ snapt ons: alle andere soorten zijn nu ook omgezet! Zie ook bovenaan waar een nieuwe transformatie-regel is vermeld: Text.Proper([Soort]). Dus klik op OK.
7. wijzig de kolomkop in Srt door dubbelklikken
8. plaats de nieuwe kolom links van Bedrag (met de muis slepen)
9. verwijder de kolom Soort (rechts klikken op de kolomkop)
10. klik op het vinkje bij Sluiten en laden en kies de optie Sluiten en laden naar. Zorg dat de Tabel-button aan staat en dat de tabel op het bestaande werkblad naast het vorige resultaat komt te staan; klik dan op de button Laden.

NB1 wanneer het bron-bestand Serie1.txt opnieuw wordt aangeleverd (met nieuwe gegevens), dan is het voldoende om deze resultaat-tabel te vernieuwen (ergens in de tabel rechts klikken).

NB2 wanneer je in het Voorbeeldbestand Vernieuwen kiest, zul je een foutmelding krijgen: de directory-structuur op mijn PC ziet er natuurlijk anders uit dan bij u. Hoe je dit oplost, leg ik hieronder uit.

Bestaand bestand transformeren

Maar ik zie nu, dat de bedragen verkeerd worden aangeleverd: het decimale teken is weggelaten.

We gaan daarom het vorige resultaat aanpassen:

klik ergens in de resultaat-tabel (die heeft van Excel de naam Serie1_2 gekregen)
kies dan in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel
kies, net als hiervoor, de optie Kolom toevoegen vanuit voorbeelden
in de eerste regel staat het bedrag 146; dit wordt dan 1,46 in de nieuwe kolom (en druk op Enter)
Excel begrijpt ons nog niet; in de tweede regel tikken we 1,79 (en Enter)
de transformatieregel wordt [Bedrag]/100. Precies wat we wilden! Klik op OK.
kolomkop wijzigen in Bedr, de kolom Bedrag verwijderen en dan Sluiten en laden (zorg dat het resultaat naast de andere 2 komt te staan).

Bestaande query aanpassen

Hey, wat is er met de Datum gebeurd? Die heeft ineens ook een tijdaanduiding. Zo slim is Excel nu ook weer niet 😉

dubbelklik in het blok Werkmapquery’s op de laatste query (met de naam Serie1_2)
klik op de kolomkop Datum
kies dan in het blok Transformeren de optie Gegevenstype en wijzig dit in Datum
ga terug via Sluiten en laden

Voor analyse-doeleinden is het handig om ook een kolom Jaar en Maand te hebben. In het vierde blok op het tabblad Tekst van het Voorbeeldbestand zijn deze kolommen toegevoegd (en ook de Dag).

Zelfde query, ander bestand of andere plaats

In het tabblad Tekst2 van het Voorbeeldbestand staat het resultaat van dezelfde query als hiervoor in de vierde tabel; deze haalt de gegevens uit Serie1.txt.

Wanneer u deze query vernieuwt (via rechtsklikken op het resultaat) zult u een foutmelding krijgen.

Dat is snel opgelost:

dubbelklik op de betreffende query (deze heeft de vermelding De download is niet voltooid.)
klik op Bron in de Toegepaste stappen
wijzig in de formulebalk de naam achter File.Contents zodanig dat verwezen wordt naar de map op uw PC, waarin het Voorbeeldbestand is uitgepakt.
ga terug via Sluiten en laden

LET OP rond de map- en de bestandsnaam moeten de aanhalingstekens blijven staan.

Dezelfde handelingen moet u uitvoeren wanneer u niet de gegevens van Serie1 wilt ophalen maar die van Serie2. Wijzig in de Bron de naam van het bestand en sluit de query-editor.

NB De omvang van de resultaat-tabel verandert automatisch met de grootte van het bronbestand.

Bestanden combineren

Hiervoor hebben we gezien hoe je een tekst-bestand geautomatiseerd kunt inlezen. Ook hoe je achter elkaar verschillende bestanden kunt binnenhalen.

Maar PQ kent een veel handiger manier om gelijksoortige bestanden in te lezen en wel naar één resultaat-tabel. Laten we dit eens gaan uitproberen met de 2 tekstbestanden uit het Voorbeeldbestand.

kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
kies in het vervolg-menu de optie Uit bestand en daarna Uit map
kies via Bladeren de map met de 2 tekstbestanden en klik op OK
klik in het vervolgscherm (met een overzicht van alle bestanden in de betreffende map) de button Gegevens transformeren.
We willen alleen de tekstbestanden inlezen: kies het vinkje naast Extension en zorg dat alleen de extensie txt wordt gefilterd.
kies de button Bestanden combineren naast Content
in het vervolgscherm kiezen we OK (alle instellingen staan goed)
maak twee nieuwe kolommen aan (via de optie Voorbeeld; zie hiervoor): Bron wordt de Source.Name zonder extensie en Bedr wordt Bedrag/100
verwijder de kolommen Source.Name en Bedrag en verplaats de nieuwe kolom Bron naar voren. Maak een sortering: eerst op Bron en als tweede op de Datum. Kies daarna Sluiten en laden.

Door PQ worden enkele hulp-query’s aangemaakt. Deze worden intern door het systeem gebruikt voor het combineren van de bestanden.
Zie het tabblad TekstCombi in het Voorbeeldbestand voor het resultaat.

Wanneer er nu een derde serie gegevens wordt aangeleverd dan plaatst u dat tekstbestand in dezelfde directory en kiest u Vernieuwen van deze resultaat-tabel.

LET OP het combineren van bestanden kan natuurlijk alleen maar als ALLE bron-bestanden dezelfde structuur hebben. Het is dus zaak om intern in de organisatie goede afspraken te maken over aanlevering van bestanden.

Van internet naar Excel

Soms is het handig om gegevens van een internet-pagina over te halen naar Excel (zie ook het artikel Excel en het world-wide-web).

We zullen hier laten zien hoe je de dagkoersen van de AEX kunt binnenhalen.

LET OP1 Excel kan alleen gegevens, die in een tabelvorm op internet worden gepresenteerd, inlezen.

LET OP2 In Excel zal het AEX-overzicht altijd iets achter lopen in de tijd; door de makers van de website is dit als bescherming opgenomen.

kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
kies in het vervolg-menu de optie Uit andere bronnen en daarna Van het web
tik de gewenste URL in of kopieer die uit de adresregel van uw browser als u weet welke pagina u wilt binnenhalen; in dit geval www.beurs.nl/koersen/AEX en druk op OK.
in de Navigator, die dan opent, blijkt dat we Table 0 moeten hebben. Kies Gegevens transformeren.
nu kunt u nog kolommen verwijderen, verplaatsen en andere gewenste aanpassingen doorvoeren
kies Sluiten en Laden naar en plaats het overzicht op een nieuw werkblad (zie het tabblad AEX in het Voorbeeldbestand).

Het is wel handig als deze tabel zich automatisch vernieuwt:
klik in de menutab Gegevens in het blok Verbindingen op het vinkje bij Alles vernieuwen.
Kies de optie Eigenschappen van verbinding.
Zorg dat de optie Vernieuwen om de 1 minuten is aangevinkt.
Klik op OK.

De tabel kan wel wat opmaak gebruiken:

voeg tijdens de transformatie in PQ een kolom StDal toe; probeer het uit via enkele voorbeelden. De formulebalk moet de volgende formule bevatten:
= Table.AddColumn(#”Type gewijzigd”, “StDal”, each if [#”+/-“] > 0 then 1 else if [#”+/-“] = 0 then 0 else -1, Int64.Type)
geef de nieuwe kolom in Excel een mooie Voorwaardelijke opmaak
zorg via opmaak dat alle getallen de juiste opmaak en het gewenste aantal decimalen krijgen.

Het resultaat staat in het tabblad AEX2 van het Voorbeeldbestand:

Voorwaardelijke opmaak met sterren

6 februari 2019ExcelGeheel, Herhaling, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: Sterren.xlsx Download

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!).