Tagarchief: voorwaardelijke opmaak

Klimaat-streepjescode



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):

  1. 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.
  2. in rij 4 staat om de 10 cellen een verwijzing naar rij 9 (de jaren)
  3. zorg dat in het Filterblok alle stations zijn gekozen en selecteer dan alle gevulde cellen in rij 10.
  4. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  5. kies bij Kleurenschalen de zesde optie (rood-wit-blauw)
  6. maak de kolommen smaller en rij 10 hoger
  7. 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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Power Query



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:

  1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
  2. kies in het vervolg-menu de optie Uit bestand en daarna Uit tekstbestand
  3. 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:

  1. klik ergens in de resultaat-tabel (die heeft van Excel de naam Serie1_2 gekregen)
  2. kies dan in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel
  3. kies, net als hiervoor, de optie Kolom toevoegen vanuit voorbeelden
  4. in de eerste regel staat het bedrag 146; dit wordt dan 1,46 in de nieuwe kolom (en druk op Enter)
  5. Excel begrijpt ons nog niet; in de tweede regel tikken we 1,79 (en Enter)
  6. de transformatieregel wordt [Bedrag]/100. Precies wat we wilden! Klik op OK.
  7. 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 😉

  1. dubbelklik in het blok Werkmapquery’s op de laatste query (met de naam Serie1_2)
  2. klik op de kolomkop Datum
  3. kies dan in het blok Transformeren de optie Gegevenstype en wijzig dit in Datum
  4. 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:

  1. dubbelklik op de betreffende query (deze heeft de vermelding De download is niet voltooid.)
  2. klik op Bron in de Toegepaste stappen
  3. wijzig in de formulebalk de naam achter File.Contents zodanig dat verwezen wordt naar de map op uw PC, waarin het Voorbeeldbestand is uitgepakt.
  4. 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.

  1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
  2. kies in het vervolg-menu de optie Uit bestand en daarna Uit map
  3. kies via Bladeren de map met de 2 tekstbestanden en klik op OK
  4. klik in het vervolgscherm (met een overzicht van alle bestanden in de betreffende map) de button Gegevens transformeren.
  5. We willen alleen de tekstbestanden inlezen: kies het vinkje naast Extension en zorg dat alleen de extensie txt wordt gefilterd.
  6. kies de button Bestanden combineren naast Content
  7. in het vervolgscherm kiezen we OK (alle instellingen staan goed)
  8. maak twee nieuwe kolommen aan (via de optie Voorbeeld; zie hiervoor): Bron wordt de Source.Name zonder extensie en Bedr wordt Bedrag/100
  9. 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.

  1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
  2. kies in het vervolg-menu de optie Uit andere bronnen en daarna Van het web
  3. 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.
  4. in de Navigator, die dan opent, blijkt dat we Table 0 moeten hebben. Kies Gegevens transformeren.
  5. nu kunt u nog kolommen verwijderen, verplaatsen en andere gewenste aanpassingen doorvoeren
  6. 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:

  1. 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)
  2. geef de nieuwe kolom in Excel een mooie Voorwaardelijke opmaak
  3. 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:


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Voorwaardelijke opmaak met sterren



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


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Heatmap en voorwaardelijke opmaak



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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Olympische spelen



De Giro voorbij, de Tour moet nog komen, Nederland niet op het WK: dan maar nog even dromen over de afgelopen winterspelen.

Een poosje geleden kwam ik een database tegen met daarin alle medaillewinnaars van de (moderne) Olympische spelen.

Een mooie aanleiding om eens te kijken of we wat leuke overzichten kunnen maken (met behulp van draaitabellen natuurlijk).

Basis-materiaal

De gegevens over alle Olympische Spelen zijn verzameld door Shane Devenshire. Die heeft in zijn sheet ook enkele voorbeelden opgenomen.

“The latest version of my free Excel Olympic Database is complete and may be downloaded by using the following link:
http://bit.ly/2BWvAte
Please share the link not the file because I want to know how often this file is downloaded so I can decide if I should continue updating it in the future. The 2016 version had very few downloads so I am thinking of discontinuing the project in the future.”

Ik heb een nieuw, aangepast Voorbeeldbestand gemaakt, maar vergeet niet óók op bovenstaande link te klikken.

Shane gebruikt op zijn welkom-pagina  een onderdeel van Excel, dat ik nog niet vaak ben tegengekomen: SmartArt-afbeeldingen.

Volgens Microsoft: “Een SmartArt-graphic is een visuele weergave van uw gegevens en ideeën. U maakt er een door een indeling te kiezen die past bij uw bericht. In sommige indelingen (zoals organigrammen en Venn-diagrammen) worden bepaalde soorten gegevens weergegeven, terwijl andere indelingen alleen een uitbreiding zijn van de weergave van een lijst met opsommingstekens.
Als u SmartArt-graphics combineert met andere functies, zoals thema’s, kunt u hiermee professionele illustraties maken met slechts een paar muisklikken.”

SmartArt is te vinden via de menutab Invoegen in het blok Illustraties.

Wanneer u in het tabblad Welkom van het Voorbeeldbestand op één van de ringen klikt, opent vanzelf het hulp-menu van SmartArt.

De gegevens zijn allemaal verzameld in het tabblad OlympData van het Voorbeeldbestand.

De betekenis van de meeste gegevens wijst zichzelf.
Alleen de kolom MedAantal (het aantal medailles) behoeft toelichting. Bij een individuele wedstrijd is dit aantal uiteraard gelijk aan 1. Bij een team-prestatie krijgt iedere deelnemer een “evenredig deel van de medaille”.
Dit betekent dat als je het aantal voorkomens in deze kolom telt, dat je dan het aantal uitgedeelde medailles krijgt. Neem je de som dan wordt een team-prestatie slechts als 1 medaille geteld (wat de bedoeling is als je bijvoorbeeld een landenklassement wilt maken).

De kolom Medaille heeft een Voorwaardelijke opmaak gekregen.

In het tabblad Locatie staat een overzicht van alle landen en plaatsen waar Olympische Spelen zijn gehouden.

Overzichten

In het Voorbeeldbestand zijn diverse overzichten opgenomen; de tabbladen Ovz:

  1. het eerste is een landenoverzicht. In cel C2 kan het gewenste jaar worden geselecteerd.
    In het Waarden-gebied van de draaitabel is de Som van het veld MedAantal weergegeven.
    De landen zijn gesorteerd naar aflopend totaal aantal medailles; klik rechts op een land, kies de optie Sorteren en dan Meer sorteeropties.
    NB1 de Medaille-velden heb ik dezelfde voorwaardelijke opmaak gegeven als de kolom in de bron-gegevens. Het woord Medaille is overschreven door MedSrt.
    NB2 gediskwalificeerde winnaars hebben wel een MedSrt, maar geen waarde voor MedAantal. In de draaitabel staat dus ook geen waarde in die kolom.
    Wilt u weten welke deelnemers gediskwalificeerd zijn: dubbel-klik dan op het eindtotaal van de betreffende kolom.
    In hetzelfde tabblad Ovz1 staat ook een landenoverzicht, maar daar zijn de diskwalificaties weggelaten door een filtering op MedSrt (klik op het vinkje achter MedSrt; hebt u een filtering aangebracht dan krijgt dit de vorm van een trechter).
  2. in Ovz2 is het landenoverzicht verder uitgesplitst naar Sport en Event.
  3. Ovz3 geeft de verdeling van het aantal medailles naar geslacht; uitgesplitst per sport.
    Duidelijk is te zien dat bobsleeën nog een mannensport is.
  4. ook zo benieuwd bij welke sport de meeste diskwalificaties plaats vinden en bij welk land? Kijk dan in Ovz4.
    LET OP als nu in het waarden-gebied het aantal van het veld MedAantal wordt bepaald, dan zal Excel niets laten zien omdat dat veld in deze situatie niet is gevuld. Kies dus voor het bepalen van het aantal voorkomens een ander (wel gevuld) veld.
  5. geïnteresseerd in de resultaten van Nederland op de winterspelen in de loop van de tijd? Kijk in Ovz5.
    NB wilt u weten welke atleten de medailles hebben binnengesleept: dubbel-klik op het betreffende aantal.
  6. in Ovz6 ziet u een verdeling naar leeftijd van de winnaars.
    De leeftijden zijn in categorieën ingedeeld.
    Hoe gaat dat in zijn werk?
    * voeg de leeftijd als Rijlabel toe aan de draaitabel
    * klik rechts op een willekeurige leeftijd en kies de optie Groeperen
    * u krijgt dan de mogelijkheid om aan te geven hoe groot het interval moet zijn
    * helaas: aangezien in de kolom Lft ook de tekst NB voorkomt, kan Excel deze groepering niet uitvoeren dus komt er wat ‘handwerk’ aan te pas: selecteer de cellen met de leeftijden 10 t/m 15 en klik rechts op één van die waardes, kies dan de optie Groeperen, wijzig de naam Groep1 in een meer relevante omschrijving (hier 10-15).
    NB1 het Waarden-gebied van de draaitabel heeft een voorwaardelijke opmaak gekregen, waarbij gebruik is gemaakt van de optie Gegevensbalken.
    NB2 er zijn meerdere jaren geselecteerd (2000 t/m 2018); Excel laat dan alleen de tekst “(Meerdere items)” zien. Het verdient aanbeveling om zelf in de cel daarnaast aan te geven welke selectie op dat moment zichtbaar is (in dit geval: resultaten vanaf 2000).

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: