Tagarchief: Draaigrafiek

Corona-data



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:

  1. 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.
  2. 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))
  3. op een vergelijkbare manier worden in de kolommen H en I respectievelijk de provincie en het aantal inwoners van een gemeente opgehaald.
  4. 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:

Klik op de afbeelding.

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.


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


Treemap en Waterval



Zolang als Excel al bestaat, wordt het niet alleen als rekentool gebruikt maar ook voor rapportage-doeleinden.
Waar in de beginperiode meestal door middel van cijfers werd gerapporteerd, zijn daar later ook grafieken bij gekomen.

Microsoft heeft ons in de afgelopen jaren met diverse soorten grafieken verblijd.
In iedere nieuwe versie van Excel verschijnen er weer nieuwe; in dit artikel aandacht voor de Treemap en de waterval-grafiek.

NB in oudere versies van Excel en in menige MAC-versie werken de voorbeelden niet.

Basisgegevens

Om een grafiek te kunnen maken hebben we natuurlijk basisgegevens nodig.
In het Voorbeeldbestand staat in het tabblad DataTree een overzicht van Bedragen uitgesplitst naar Maand en Soort.

NB door middel van de functie Aselecttussen worden de data door Excel willekeurig gekozen; bij iedere wijziging in de werkmap zullen dus nieuwe gegevens gegenereerd worden.
Op deze manier kun je snel de impact op de grafieken zien.

De gegevens zijn opgeslagen in een Excel-tabel met de naam tblDataTree. Uitbreidingen aan deze tabel (of verwijderingen) zullen daardoor automatisch doorwerken in de overzichten en grafieken die daar op gebaseerd zijn.

Draaitabel en -grafiek

De gegevens uit het tabblad DataTree zijn in een draaitabel samengevat op het tabblad OvzTree. In dit geval is de Soort in het Filter-veld geplaatst, de Maand in de Rijen en het Bedrag in het Waarden-gebied.
Deze draaitabel kan direct vertaald worden naar een grafiek:

  1. klik ergens in de draaitabel
  2. op dat moment komt er een nieuwe menu-tab bij, Hulpmiddelen voor Draaitabellen
  3. klik daarbinnen op de menutab Analyseren en dan in het blok Extra op Draaigrafiek
  4. na enkele cosmetische aanpassingen ontstaat bovenstaande grafiek

NB de grafiektitel is dynamisch: wanneer in het Filter een andere Soort wordt gekozen past de titel zich automatisch aan.
In cel C19 wordt de basis daarvoor gelegd. U wijzigt een grafiektitel als volgt: klik in de bestaande titel, daarna in de formulebalk, voer het =-teken in, klik dan op cel C19 en druk op Enter. In de formulebalk verschijnt =OvzTree!$C$19.

Niet altijd is in deze grafiek duidelijk welke maanden in welke mate bijdragen tot het totaal (klik rechts in de Draaitabel en kies Vernieuwen). Aangezien de basisgegevens telkens opnieuw worden gegenereerd zal de Draaitabel (en dus ook de Draaigrafiek) dienovereenkomstig worden bijgewerkt.
Door de maanden anders te rangschikken kunnen de onderlinge verhoudingen duidelijker worden gemaakt:

  1. klik op het keuzevinkje achter Rijlabels
  2. kies Meer sorteeropties
  3. klik op het keuzerondje vóór Aflopend
  4. kies dan daaronder als sorteervolgorde niet voor Maand maar voor Som van Bedrag
  5. klik op OK

Bekijk het effect als je de draaitabel vernieuwd.

Treemap

Maar niet iedereen vind het lezen/interpreteren van bovenstaande grafiek makkelijk.
Waarschijnlijk zijn deze mensen meer gebaat bij een zogenaamde Treemap, bedoeld om hiërarchie in resultaten te verduidelijken.

Helaas is de Treemap niet beschikbaar als de bron een draaitabel is. Daarom is in het tabblad OvzTree van het Voorbeeldbestand een hulptabel gecreëerd, die de gegevens van de draaitabel repliceert.

In cel C27 wordt, afhankelijk van de corresponderende waarde in kolom B, het Bedrag opgehaald in de draaitabel rond cel B4.

Het maken van een Treemap is dan een peuleschil:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C27
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Hiërarchiegrafiek
  3. kies daar de optie Treemap
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB Blijkbaar komt dit grafiektype van een andere software-maker; niet alle eigenschappen van een grafiek zijn beschikbaar.
De mooie ronde hoeken bijvoorbeeld zijn niet meer terug te vinden.
Ook een dynamische grafiektitel is niet op dezelfde manier, als hiervoor aangegeven, in te voeren. In dit geval (zie het tabblad OvzTree van het Voorbeeldbestand) heb ik er voor gekozen om een Tekstblok in te voegen en daar dan de verwijzing naar cel C19 te plaatsen.

Om het instellen van keuzemogelijkheden te vereenvoudigen zijn ook 2 Slicers toegevoegd: eentje voor het instellen van de Soort en een andere om bepaalde Maanden te kunnen selecteren.

Waterval-grafiek

Is niet zozeer de onderlinge verhouding van belang, maar wil je weten hoe ieder onderdeel bijdraagt aan het geheel, dan is een ander type grafiek meer voor de hand liggend, de Waterval-grafiek.

NB Ook deze grafiek in niet beschikbaar als een draaitabel de brongegevens bevat.

In het tabblad Waterval van het Voorbeeldbestand is daarom een nieuwe bron-tabel (met de naam tblDataWater) ingevoerd. Per Maand wordt hier een willekeurig Bedrag gegenereerd tussen -100 en +200.

Ook het maken van een Watervalgrafiek is dan ‘kinderspel’:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C3
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Waterval-, trechter-, ….
  3. kies daar de optie Waterval
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB ook voor dit type grafiek geldt het voorbehoud, dat niet alle (standaard-)instellingen beschikbaar zijn.


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


Excel en kaarten



Om misverstanden te voorkomen: met het woord kaarten in de titel worden geografische kaarten bedoeld (in het Engels Maps).

Sommige mensen willen informatie aangeleverd krijgen in de vorm van tabellen met (exacte) getallen, maar de meeste mensen vinden grafieken makkelijker om gegevens te interpreteren en te analyseren.

Als het dan geografische gegevens betreft is het natuurlijk ook wel mooi om de informatie op een kaart weer te geven.
In dit artikel wat handreikingen hoe je dit kunt doen.

Basisgegevens

Voor dit artikel gebruik ik een uittreksel van gegevens van de website Veldkruus.nl (even reclame maken voor een andere hobby van mij: het letterlijk in kaart brengen van veldkruisen, kapelletjes etcetera in Zuid-Limburg). Zie het tabblad Data in het Voorbeeldbestand.

Van ieder object ziet u in het voorbeeld in welke plaats het staat, welke buurt en welke straat. Ook of het een kruis of beeld of kapel is. Wanneer er foto’s gemaakt zijn en of het object nog ‘actief’ is ofwel of het er nog wel is!
Daarnaast leggen we ook de coördinaten vast in de vorm van zijn Longitude en Latitude, lengte- respectievelijk breedtegraad.

NB1 simpel gezegd: de Longitude geeft aan hoever iets van de mediaan door Greenwich af ligt, terwijl de Latitude de ‘afstand’ tot de evenaar weergeeft.
NB2 in de praktijk is het toekennen van coördinaten afhankelijk van diverse zaken. Op Veldkruus gebruiken we de coördinaten-systematiek van Google-maps; klik je daar ergens op een kaart met de rechtermuisknop en je kiest Wat is hier? dan zie je de coördinaten van dat punt.

LET OP Google toont de coördinaten in de volgorde Lat, Long; dus eerst de verticale en dan de horizontale richting.

Overzicht

Om een idee te krijgen over de verdeling van de objecten over de regio maken we een draaitabel, gesplitst naar plaats en categorie (zie het tabblad Draai in het Voorbeeldbestand).
Op basis van deze draaitabel is het maken van een grafiek een ‘peuleschil’ (kies in de menutab Hulpmiddelen voor draaitabellen in het blok Opties de mogelijkheid Draaigrafiek).
Om het wat overzichtelijk te houden beperken we ons tot de top-20 (zie Top-5: methode 3). Om snel de verdeling per categorie te zien, staan boven de grafiek Slicers.

We hebben nu wel de verdeling per plaats, maar hoe is nu de verdeling over Zuid-Limburg?

Grafiek met kaart 1

In het tabblad Data20 van het Voorbeeldbestand ziet u de verdeling van de top-20 over de regio.

Hoe maakt u zo’n grafiek?

  1. allereerst moet u een mooi kaartje zoeken op internet dat u als achtergrond wilt gebruiken. Sla dat ergens op.
  2. dan krijgen de 20 plaatsen een x- en y-coördinaat (vul voorlopig getallen tussen 0 en 10 in; de juiste getallen komen hierna)
  3. maak een spreidingsgrafiek: kies in de menutab Invoegen in het blok Grafieken de eerste optie binnen Spreiding (dus geen lijnen)
  4. klik rechts in de nieuwe (lege) grafiek en kies Gegevens selecteren
  5. kies de optie Toevoegen en vul de vereiste verwijzingen in
  6. 2 keer OK klikken levert een spreidingsdiagram
  7. zorg dat de assen allebei lopen van 0 tot 10 (rechts klikken op een as, As opmaken kiezen en de grenzen vast zetten)
  8. klik ergens rechts in de grafiek en kies de optie Tekengebied opmaken, kies bij Opvulling de optie Invoegen uit: Bestand.  Kies dan het kaartje van punt 1.
    Sleep de randen van de grafiek naar binnen of naar buiten, zodat de kaart in de juiste verhoudingen wordt weergegeven.
  9. vul dan de coördinaten bij de plaatsen zo in, dat de de markeringen op de juiste plaats komen
  10. nog wat lay-out: grafiekpunten vergroten, kleuren aanpassen, rasterlijnen weg en als laatste ook de assen

LET OP zorg dat de achtergrond-kaart aan het Tekengebied wordt toegevoegd, NIET aan het Grafiekgebied. Doet u dat laatste, dan zullen de markeringen na bepaalde lay-out-aanpassingen niet meer op de juiste plaats worden weergegeven.

NB1 de exacte as-indeling doet er niet toe. Maar een verdeling van 0 tot 10 is handig.

NB2 tot versie 2013 van Excel kun je in een spreidingsgrafiek alleen de coördinaten als label meegeven. Wel is het mogelijk om met de hand eigen labels toe te voegen: klik rechts op een punt van de grafiek en kies Gegevenslabel toevoegen. Klik dan dubbel op één van de labels, plaats in de formulebalk het is-teken (=) en wijs met de muis de cel aan waar het label staat (bijvoorbeeld Data20!$E$3).

Grafiek met kaart 2

Met een bel-diagram kun je nog een derde dimensie aan de grafiek toevoegen; in dit  geval het totaal aantal objecten per plaats (zie het tabblad BelGraf in het Voorbeeldbestand).
De grootte van een bel wordt door Excel bepaald en is altijd relatief tov de andere bellen. Wel kun je de grootte van alle bellen schalen om ze beter op de kaart te laten uitkomen (klik rechts op een bel en kies Gegevensreeks opmaken).

Hoe je een bel-grafiek maakt is beschreven in het gelijknamige artikel.

Grafiek met kaart 3

Een variant op de vorige grafiek (zie tabblad LongLat in het Voorbeeldbestand). Nu werken we niet met fictieve coördinaten, maar met de werkelijke coördinaten per plaats.

De Long(itudes) zijn de x-waarden en de Lat(itudes) de y-waarden (voor de coördinaten van de plaatsen in Zuid-Limburg zie het tabblad Plaatsen).

De truc zit hem er nu in om de assen zodanig te schalen, dat de Plaatsen op de juiste plek op het achterliggende plaatje komen:

  1. bekijk zo goed mogelijk waar het 0-punt van de assen op het achterliggende plaatje ligt
  2. zoek in Google-maps de coördinaten van dat punt op (rechtsklikken en de optie Wat is hier? kiezen)
  3. klik rechts op de x-as en kies As opmaken. Maak het Minimum Vast en vul hier de gevonden Long-waarde van punt 2 in (voor Nederland en omstreken is dat het kleinste getal, dus het tweede)
  4. doe hetzelfde met de y-as maar vul daar de Lat-waarde in
  5. zoek op dezelfde manier de Long-waarde op van het eindpunt van de x-as en gebruik deze om het maximum van de x-as vast te pinnen
  6. zoek op dezelfde manier de Lat-waarde op van het eindpunt van de y-as en gebruik deze om het maximum van de y-as vast te pinnen

LET OP als de kwaliteit van de achtergrond niet goed is (niet de juiste kaart-verhoudingen), dan zullen de Plaatsen niet helemaal goed op de kaart komen. Een beetje ‘spelen’ met minima en maxima (no pun intended zoals de Engelstaligen zeggen) kan dan wel helpen.

Alternatieve toepassingen

De boven geschetste methode hoeft natuurlijk niet beperkt te blijven tot landkaarten. Hierboven een voorbeeld van een andere toepassing (zie het tabblad Afdelingen in het Voorbeeldbestand).


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

 

 

Trend-analyse

Ik was deze week nog eens naar Google-analytics aan het kijken hoe het met de bezoekersaantallen van G-Info gaat.

Er zit nog steeds een stijgende lijn in; altijd leuk!

Wel wat vreemde uitschieters:

  • eind april/begin mei 2015 heeft Google problemen gehad en is er niets geturfd
  • juli en aug van ieder jaar vertoont een dip; waarschijnlijk hebben mensen dan wat anders te doen
  • ook bij de dips van eind december kan ik me wat voorstellen
  • bij nadere bestudering blijken alle punten aan de onderkant op zaterdag en zondag te vallen
  • en de pieken naar boven vallen samen met de momenten dat ik een Nieuwsbrief uitstuur met de aankondiging dat er een nieuw artikel op de site is verschenen.

Maar laten we eens kijken hoe we de trend kunnen analyseren.

Trendlijn-1

Op basis van de gegevens van Google heb ik bovenstaande grafiek gemaakt (je kunt de data uit Analytics eenvoudig exporteren naar Excel):

  1. in het Voorbeeldbestand staan op het tabblad Gegevensset de aantallen bezoekers per dag (beter gezegd de aantallen sessies).
    Daar heb ik ‘natuurlijk’ direct een Excel-tabel van gemaakt (zie het artikel over de 10 voordelen van een tabel en Tabellen deel 2). De naam daarvan is Tabel1.
  2. selecteer de gegevens uit de 2 kolommen door de bovenkant van de kolommen aan te wijzen (de cursor is dan een zwart-pijltje naar beneden; de linker-muis-toets ingedrukt houden).
  3. kies in de menubalk de optie Invoegen/Grafieken/Lijn
  4. het resultaat vindt u terug in het tabblad Graf

In de voorbeeldgrafiek heb ik Excel ook een trendlijn laten toevoegen:

  1. klik ergens met de rechtermuisknop op de grafiek en kies de optie Trendlijn toevoegen
  2. laten we het simpel houden en voor een rechte lijn (de optie Lineair) kiezen
  3. vink ook de 2 onderste opties aan: Vergelijking en R-kwadraat

Excel heeft een mooie (stijgende) lijn getekend; de wiskundige formule die daar bij hoort is
y = 0,0956x – 3998,4

Het getal voor de x is de richtingscoëfficiënt (RC): hoeveel verandert de lijn als x (in dit geval de dag) met 1 toeneemt (dus ruwweg iedere 10 dagen komt er 1 bezoeker meer).
Het andere getal (-3998,4; het snijpunt met de y-as) geeft het aantal bezoekers weer als x gelijk is aan 0 (nul).

NB1 Omdat op de x-as datums staan en voor Excel een datum niets anders is dan het aantal dagen na 1-1-1900, zou het aantal bezoekers op die dag-nul dus flink negatief zijn geweest. De lijn naar het verleden doortrekken is dus onzinnig.

NB2 voor de liefhebbers: Excel gebruikt voor het bepalen van de lijn de zogenaamde kleinste-kwadratenmethode.

NB3 R² (R-kwadraat) is de zogenaamde determinatiecoëfficiënt. Deze geeft aan welk gedeelte van de variatie in de ene variabele door de andere wordt verklaard.
Ofwel: hoe ´betrouwbaar´ is de trendlijn. Is de R² kleiner dan 0,5 dan is het verband zwak tot matig, ligt die tussen 0,5 en 0,75 dan is het verband sterk en anders zeer sterk.

Hoeveel bezoekers mag G-Info op basis van deze trendlijn over een jaar verwachten? Als iedere 10 dagen er 1 extra bezoek bij komt dan zouden er over 365 dagen ongeveer 36 meer moeten zijn dan nu; dus ipv gemiddeld 95 zouden er dat ongeveer 130 zijn.
Dit kunnen we ook makkelijk grafisch laten zien:

  1. klik met de rechtermuisknop op de trendlijn in de grafiek
  2. kies Trendlijn opmaken
  3. en bij Voorspelling/Vooruit vullen we 365 in

Richting, Snijpunt, R.kwadraat en Lijnsch

Om in Excel met de trend te kunnen rekenen moeten we de richtingscoëfficiënt en het snijpunt met de y-as exact weten; we hebben niet genoeg aan een formule in de grafiek.

Uiteraard zijn daar functies voor (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. in cel H2 willen we de RC plaatsen.
    * tik in =Richting(
    * klik op de bovenrand van Sessies in kolom C (de y-waarden)
    * tik in ;
    * klik op de bovenrand van Dagindex in kolom B (de x-waarden)
    * druk op Enter (Excel zal automatisch de sluithaak van de formule toevoegen)
  2. in H3 is op dezelfde manier de functie SNIJPUNT ingevoerd
  3. en in H4 de functie R.KWADRAAT (denk aan de punt na de R)

Voor de liefhebbers: Excel kent nog een andere functie; deze levert meer statistische resultaten op, LIJNSCH.

In het kort (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. selecteer 6 lege cellen, 2 kolommen en 3 regels  (in het voorbeeld G12:H14)
  2. in de eerste cel komt de volgende formule:
    =LIJNSCH(Tabel1[Sessies];Tabel1[Dagindex];WAAR;WAAR)
    De y- en x-waarden zijn de bekende kolommen uit de tabel met Google-data.
  3. druk nu niet op Enter, maar Ctrl-Shift-Enter (de zogenaamde CSE-invoer)

De betekenis van de 6 cellen heb ik in de kolommen er naast aangegeven; de cellen G12:H14 hebben corresponderende namen gekregen, zodat formules die er naar verwijzen beter leesbaar zijn.

NB De 2 ‘boven’ de R krijg je als volgt: selecteer in de formulebalk de 2, klik Ctrl-1 (de sneltoets voor Celeigenschappen) en kies de optie Superscript. De m, b en y achter SD hebben de eigenschap Subscript gekregen.

LET OP Lijnsch is een zogenaamde array- of matrix-functie. Het resultaat bestaat niet uit 1 waarde maar uit meerdere, vandaar dat die met CSE wordt ingevoerd. Alle cellen bevatten dezelfde functie, omgeven door accolades.

NB Lijnsch levert meer dan 6 resultaten terug; probeer zelf maar uit door in de eerste stap meer kolommen en regels te selecteren.

De resultaten van bovenstaande functies kunnen we nu gebruiken om voorspellingen voor de toekomst te genereren (zie het tabblad Gegevensset in het Voorbeeldbestand).

Trendlijn-2

Omdat de bezoekersaantallen in de weekenden heel anders zijn dan op werkdagen, heb ik voor een nadere analyse op het tabblad Gegevensset in het Voorbeeldbestand nog een kolom toegevoegd:
=KIEZEN(WEEKDAG([@Dagindex];2);”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)

Ofwel: bepaal de Weekdag van de corresponderende datum in kolom B (Dagindex; we willen dat de week op maandag begint, vandaar de parameter 2). Aangezien deze functie volgnummers oplevert (een maandag is 1, dinsdag 2 etc), heb ik de nummers nog vertaald in teksten mbv de functie Kiezen.

Op basis van deze tabel heb ik een draaitabel gemaakt (zie het tabblad Draai in het Voorbeeldbestand).
Door het veld Weekdag in het Rapportfilter te plaatsen kunnen we snel een overzicht per afzonderlijke dag maken.
Om ook analyses per jaar, kwartaal of maand te kunnen maken heb ik de datums in het veld Dagindex gegroepeerd (zie Groeperen in een draaitabel).

Nog een paar stappen en we kunnen met de resultaten gaan ‘spelen’:

  1. selecteer een cel in de draaitabel
  2. in de menutab Hulpmiddelen voor draaitabellen kiezen we het tabblad Opties
  3. klik dan op de optie Draaigrafiek in het blok Extra
  4. kies een Lijngrafiek en klik OK
  5. voeg een Trendlijn toe, inclusief Vergelijking en R-kwadraat

Na wat lay-outen en het toevoegen van 2 slicers (zie Slicers in Excel) krijgen we een resultaat zoals weergegeven in het tabblad DrGraf in het Voorbeeldbestand.

LET OP de b in de vergelijking van de trendlijn gebruikt voor x=0 niet meer de datum 1-1-1900, maar de eerste datum in de betreffende draaitabel.

Wanneer we in de Slicer Weekdag alleen de maandag kiezen (klikken op de button ma) dan zien we dat de R² al veel beter wordt (0,6381).

LET OP de m in de vergelijking van de trendlijn geeft nu niet de verandering per dag aan, maar de verandering naar de volgende maandag, dus na 1 week.
Wil je de trendlijn 1 jaar vooruit laten ‘kijken’, tik bij Voorspelling/Vooruit dan ook geen 365, maar 52 in (dus over 1 jaar 160 bezoekers op maandag?).

Wil je het resultaat over alle werkdagen zien:

  1. kies in de Slicer Weekdag de button ma
  2. houd Shift ingedrukt en klik op vr
  3. laat Shift los
  4. de draaitabel, draaigrafiek en trendlijn passen zich automatisch aan

Analyse van de jaren laat zien (gebruik de betreffende Slicer), dat de trendlijn voor 2015 ´betrouwbaarder´ is dan die van 2016. De resultaten van vorig jaar worden zwaar beïnvloed door de dips in vakantie-periodes.


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

Groeperen in een draaitabel

Hoewel steeds meer Excel-gebruikers weten, dat draaitabellen ontzettend handig zijn bij de analyse van gegevens, het maken van overzichten etc. en deze mogelijkheden ook in hun dagelijkse werk gebruiken, is het groeperen binnen draaitabellen nog minder bekend: deze keer dus de focus op Groeperen in draaitabellen.

Oplettende lezers hebben ongetwijfeld gezien, dat ik dit in mijn vorige artikel (Top-2000 en draaitabellen) heb gebruikt zonder verdere uitleg.

Basis

Om een draaitabel te kunnen maken hebben we wat basisgegevens nodig. Ik heb in het Voorbeeldbestand op het tabblad Basis de fictieve productie van een simpele winkel gezet: een datum, het artikel en het verkochte aantal (soms dubbel).
Uiteraard (zie Kunst en Excel; 10 voordelen van Tabellen) zijn die gegevens in de vorm van een Tabel opgeslagen (met als naam tblVerkopen).
Ze waren eerst niet gesorteerd, maar om gemakkelijk de resultaten van de draaitabellen te beoordelen, heb ik dat nu wel even gedaan (klik op het vinkje achter Datum).

Overzicht per dag

Laten we snel even een overzicht maken van deze gegevens:

  1. Plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Datum naar de Rijlabels, Art naar de Kolomlabels en Aantal naar het waardengebied
  5. eigenlijk is de draaitabel klaar, maar ik vind de standaard-layout niet handig, dus heb ik in de menutab Hulpmiddelen voor draaitabellen de tab Ontwerpen gekozen en bij Rapportindeling de optie Tabelweergave.

Het resultaat staat op het tabblad Draai van het Voorbeeldbestand: een overzicht van de totale verkoopaantallen per dag en gesplitst naar artikel.

Overzicht per maand

Vaak willen we in rapportages niet alle details zien, maar bijvoorbeeld in plaats van dag-resultaten de verkopen per maand.

In dit geval zou je de basisgegevens daartoe kunnen uitbreiden met een zogenaamde hulp-kolom waarin met behulp van de functie MAAND de maand wordt vastgelegd. Als we ook nog het jaar willen weten, moeten we nog een extra hulp-kolom toevoegen (met daarin de functie JAAR).

Maar die hulpkolommen zijn niet nodig:

  1. klik met de rechtermuisknop op één van de datums in de hiervoor gemaakte draaitabel
  2. het zogenaamde context-gevoelige submenu komt dan tevoorschijn. Kies daar de optie Groeperen
  3. Excel heeft de kolom, waarin geklikt is, geanalyseerd en heeft gezien dat dit allemaal datums zijn.
    We willen een maand-overzicht dus het voorstel van Excel kunnen we overnemen; klik op OK

LET OP als in de basisgegevens ergens in de kolom Datum een cel niet is gevuld of gevuld met een niet-bestaande datum of een getal of een tekst, dan zal het Groeperen-scherm er anders uitzien. De datum-opties zijn dan niet beschikbaar.
Nog erger: ook al corrigeert u de basisgegevens, Excel zal deze kolom nooit meer als een datum ‘zien’. Er zit dan niets anders op dan het werkblad met de draaitabel te verwijderen en het overzicht opnieuw te maken!

NB In het blok Lijst met draaitabelvelden aan de rechterkant van het scherm staat bij de te kiezen velden nog steeds Datum, maar Excel zal in de draaitabel alleen nog maar de maanden laten zien.

Overzicht per maand en jaar

Het overzicht per maand is in dit geval prima omdat Basis alleen maar datums uit 2016 bevat. Voegen we echter onderaan (of tussenin) de volgende regel toe:
1-1-2017, Servetten, 10
dan komen die in het Overzicht per maand uiteraard ook in de januari-regel tevoorschijn (wel even de draaitabel Vernieuwen door ergens rechts te klikken!).

We moeten zorgen dat de draaitabel ook per jaar groepeert:

  1. klik met de rechtermuisknop op één van de maanden in de datum-kolom van de draaitabel
  2. kies opnieuw Groeperen
  3. en klik nu ook de Jaren aan en OK

NB1 in de Lijst met draaitabelvelden is een er een nieuw veld bijgekomen, Jaren. Dit veld kunt u op dezelfde manier in de draaitabel plaatsen als andere velden. Bijvoorbeeld in het Rapportfilter zodat u overzichten per jaar kunt maken.

NB2 wanneer u een draaitabel maakt, dan legt Excel de brongegevens apart op een eigen manier in het geheugen vast. Zo ook de consequenties van groeperingen. Maakt u nu met dezelfde bron (in dit geval tblVerkopen) een andere draaitabel, dan gebruikt Excel, om geheugenruimte te sparen, dezelfde interne bron. Dus ook eventuele groeperingen zijn in de tweede draaitabel direct zichtbaar.

NB3 hebt u bij een analyse/overzicht in dezelfde draaitabel (of een andere; zie NB2) de datums zelf nodig, klik dan bij het Groeperen ook de Dagen aan.
Hebt u de dagen in het huidige overzicht niet (meer) nodig, verwijder dan het vinkje voor de Datum in Lijst met draaitabelvelden.

Overzicht per kwartaal

Ongetwijfeld hebt u ondertussen ook gezien dat u op deze manier heel gemakkelijk kwartaaloverzichten kunt maken.
In het overzicht hiernaast is de groepering per kwartaal aangezet (zoals te zien is heb ik ook de 2017-regel toegevoegd en de draaitabel vernieuwd).
Nog een paar kleine aanpassingen:

  1. met de rechtermuisknop klikken op een jaar en dan Subtotaal Jaren aanvinken
  2. met de rechtermuisknop klikken op een kwartaal en dan Subtotaal Kwartalen aanvinken
  3. de eerste drie kwartalen ‘dichtklappen’ door op het min-teken vóór het betreffende kwartaal te klikken
  4. grafiek nodig? Klik ergens in de draaitabel, kies het tabblad Opties in de menutab Hulpmiddelen voor draaitabellen en binnen het blok Extra de optie Draaigrafiek. We willen een Kolomgrafiek, dus het klikken op OK volstaat.

 

Artikelen groeperen

Voordat we verder gaan is het het makkelijkst om de draaitabel op het tabblad Draai van het Voorbeeldbestand eerst aan te passen:

  1. klik het vinkje voor Datum weg in de Lijst met draaitabelvelden
  2. verplaats Art van Kolomlabels naar Rijlabels

Zoals we gezien hebben weet Excel bij datums hoe hij kan groeperen; bij teksten is dat natuurlijk een stuk lastiger.
Daarom gaan we als volgt te werk:

  1. selecteer met de muis, die cellen die u wilt groeperen. In dit geval liggen die niet tegen elkaar, dus moeten we de Ctrl-toets gebruiken.
    Klik op Broodrooster
  2. hou Ctrl ingedrukt en klik op Strijkijzer
  3. klik met de rechtermuisknop op één van die 2 artikelen en kies Groeperen
  4. doe hetzelfde met Servetten en Tafelkleed
  5. verander de nieuwe Groep1 in Hard door de tekst ‘er overheen’ te tikken
  6. Groep2 wordt op die manier Soft
  7. de nieuwe kolomkop Art2 wordt ArtGroep
  8. zet nog de subtotalen voor ArtGroep aan

Nu weer een kwartaaloverzicht maken:

  1. klik het vinkje voor Art weg
  2. verplaats ArtGroep van Rijlabels naar Kolomlabels
  3. plaats Kwartalen en Jaren in de juiste volgorde in Rijlabels

Getallen groeperen

Om dit toe te lichten maken we een nieuwe draaitabel:

  1. plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Aantal naar de Rijlabels, Art naar de Kolomlabels en Datum naar het waardengebied

We zien op die manier het aantal keren, dat een bepaald aantal verkopen voorkomt, ook nog gesplitst naar artikel.

LET OP Aantal van Datum is wat misleidend: we turven niet het aantal dagen, maar het aantal keer dat het veld Datum gevuld is. We hadden ook Aantal van Art of zelfs Aantal van Aantal kunnen kiezen.

NB de basis-gegevens zijn random gevuld. Je zou dus overal exact 50 verwachten. Als ik 100.000 records had aangemaakt zou de spreiding ook minder groot zijn geweest!

Maar nu groeperen:

  1. klik met de rechtermuisknop op één van de cijfers 2, 3, 4, 5 of 6 en kies Groeperen
  2. we krijgen nu een heel ander groepeer-scherm; we kunnen een eigen indeling maken.
    Verander de 2 in 1 en de 1 achter Op: in 3.
    Ofetwel begin bij 1 en groepeer in blokken van 3.
  3. klik op OK.

Als we ook nog de artikelen groeperen naar Hard en Soft dan krijgen we het volgende overzicht:


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