Tagarchief: Draaitabel

Data verrijken



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:

  1. Plaats de cursor ergens in de tabel tblVerkoop.
  2. Kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK.
  3. 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?

  1. Verwijder Prod uit de Kolommen.
  2. Verplaats Jaren van Rijen naar Kolommen.
  3. 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:

Tabblad Klant
Tabblad Product

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:

  1. 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.
  2. In cel G3 halen we op een vergelijkbare manier de naam van het product op:
    =INDEX(tblProd[ProdNaam];VERGELIJKEN([@Prod];tblProd[Product];0))
  3. De totale kosten, die met een verkoop zijn gemoeid worden in cel H3 bepaald:
    =[@Aantal]* INDEX(tblProd[KostPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  4. In cel I3 bepalen we de omzet:
    =[@Aantal]* INDEX(tblProd[VerkPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  5. Daarmee kunnen we in J3 de bruto-winst berekenen:
    =[@Omzet]-[@Kosten]
  6. Maar (sommige) klanten krijgen korting; in cel K3:
    =INDEX(tblKlant[Korting];VERGELIJKEN([@Klant];tblKlant[Klant];0))
  7. 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:

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

  1. 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.
  2. 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.
  3. 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):

  1. Plaats de cursor in een lege cel. Hier zal het overzicht komen, dus meestal doe je dit op een nieuw tabblad.
  1. Start via Toevoegen/Draaitabel een nieuwe draaitabel
  2. Hiernaast is te zien, dat Excel nu automatisch weet, dat we ons gegevensmodel als bron willen gebruiken. Klik OK.

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

  1. Maak een draaitabel aan zoals hiervoor beschreven.
  2. Plaats Regio en Klant in de Rijen.
  3. Sleep Aantal naar het Waarden-gebied.
  4. Sleep daarna 2x de Datum naar het Waarden-gebied.
  5. Klik op het vinkje achter Datum2 en kies Waardeveldinstellingen.
  6. 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:

  1. Selecteer een cel in de tabel tblKlant.
  2. Kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
  1. 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.
  1. Kies dan het vinkje naast Sluiten en laden.
  2. Klik op Sluiten en laden naar.
  3. Zorg dat de optie Alleen verbinding maken is geselecteerd en klik op de button Laden.
  4. Voer de vorige stappen ook uit voor de tabel tblProduct. Zorg dat KostPrijs en VerkPrijs de instelling Decimaal getal krijgen.
  1. Als laatste doet u hetzelfde met de tabel tblVerkoop, maar nog NIET sluiten. Wijzig de opmaak van de kolom Datum in alleen Datum.
  2. 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.

  1. Maak de verbindingen zoals hierboven en klik op OK. Wijzig de naam van de nieuwe query in PQtblVerkoop.
  2. 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.
  3. Doe hetzelfde met tblProduct.
  4. 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.
  5. Kies daarna de optie Aangepaste kolom binnen Kolom toevoegen. De naam wordt Omzet en de formule =[Aantal]*[VerkPrijs].
  6. Voeg op dezelfde manier nog 3 kolommen toe:
    Kosten: =[Aantal]*[KostPrijs]
    Brutowinst: =[Omzet]-[Kosten]
    Nettowinst: =[Aantal]*([VerkPrijs]*(1-[Korting])-[Kostprijs])
  7. Zorg dat de 4 toegevoegde kolommen de instelling Decimaal getal hebben.
  1. 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:

  1. Selecteer een cel in een betreffende rij.
  2. Kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak.
  3. Kies dan Gegevensbalken en een kleur(overgang).
  4. Naast de cel staat nu het tekentje van een draaitabel, kies daar de onderste optie.
  5. Via Regels beheren/Regel bewerken de optie Alleen balk weergeven aanvinken.
  6. 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.


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


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:


Overzicht artikelen van G-Info



Op deze website zijn ondertussen 100 artikelen verschenen!

Over diverse onderwerpen, met verschillende diepgang, meer of minder uitgebreid. Het scala is zo divers dat ik soms zelf niet meer weet wat ik waarover heb geschreven. Dus tijd om overzichten te maken; die wil ik middels dit artikel met u delen.

NB1 hiernaast ziet u de top-5 van meest gelezen artikelen. Zeker de eerste en de vijfde zijn voor iedere Excel-gebruiker een must om te bestuderen.

NB2 hebt u nog ideeën voor een artikel? Schroom niet om die door te geven. Ook met vragen kunt u altijd terecht bij contact@ginfo.nl.

Brongegevens

De site van G-Info is gebouwd met behulp van WordPress. Alle artikelen komen (uiteraard) in een daarbij behorende database terecht. Voor mijn bronbestand moest ik daar wel wat in rondstruinen en zaken combineren.
Gelukkig hebben we (tegenwoordig) Power Query.

In het tabblad TotOvz van het Voorbeeldbestand vindt u het overzicht van alle 100 artikelen. Naast een ID van WordPress ziet u ook de publicatiedatum (en tijd) en de titel van het artikel. De tag geeft de belangrijkste items van een artikel weer. Daarbij maakt WordPress onderscheid tussen categorieën en echte ‘tags’.
Dit zijn de elementen die bij ieder artikel onder de titel staan:

Ik heb zelf nog 2 kolommen toegevoegd: link en hulp.
De eerste bevat de formule
=HYPERLINK(“http://www.ginfo.nl?p=”&[@ID])
Op deze manier ontstaat een ‘klikbare’ link naar het betreffende artikel.

NB de gegevens staan in een Excel-tabel (met de naam tblTotOvz); met de constructie [@ID] wordt verwezen naar de ID-kolom in dezelfde rij waar de formule staat.

De hulp-kolom hebben we straks nodig om te turven hoeveel artikelen in een bepaalde periode zijn gepubliceerd. In cel H6 staat de formule
=ALS(B6<>B5;1;0)
Dus als het ID in deze regel NIET gelijk is aan het vorige ID dan komt in de cel een 1, anders een 0.

NB om in een Excel-tabel zo’n formule te plaatsen moet je wat ‘trucen’, anders genereert Excel zogenaamde gestructureerde verwijzingen (met de rechte haken en eventueel een @).

In plaats van te klikken op de cellen B5 en B6, moet je eerst verwijzingen buiten de tabel maken en deze verwijzingen dan later corrigeren (versleep de gekleurde rand naar de juiste positie).

Overzicht per jaar en maand

Het voordeel van de database-structuur van onze brongegevens is, dat we snel allerlei overzichten kunnen maken.

Het eerste overzicht is een uitsplitsing van de artikelen naar publicatiejaar en -maand:

  1. selecteer één van de cellen van de brongegevens
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. alle instellingen staan goed, dus klik op OK
  4. sleep post_date naar het blok Rijen
  5. idem met ID en post_title
  6. plaats hulp in het Waarden-gebied
  7. er zijn nu 2 menubladen bijgekomen: Hulpmiddelen voor draaitabellen
  8. kies daar in de menutab Ontwerpen in het blok Indeling de optie Rapportindeling; kies dan Tabelweergave
  9. klik rechts op de subtotalen en verwijder het vinkje bij Subtotaal
  10. we willen geen overzicht op dag-niveau; klik rechts op een van de datums en kies Groeperen. Zorg dat Maanden en Jaren zijn geselecteerd en klik op OK.

In het tabblad OvzDtm van het Voorbeeldbestand is een slicer toegevoegd zodat snel een overzicht van één of meerdere jaren kan worden gemaakt. Deze optie zit in het tabblad Analyseren bij de Hulpmiddelen voor draaitabellen.

Overzicht van categorieën per artikel

In het tabblad OvzArtCat van het Voorbeeldbestand ziet u een overzicht van alle artikelen met daarachter de categorie of categorieën waar het op betrekking heeft. Zoals u ziet moet het filter taxonomy op category ingesteld worden.

Ook hier is een slicer handig om snel een keuze te maken, waarna het overzicht zich automatisch aanpast.

Overzicht van ‘tags’ per artikel

Een vergelijkbaar overzicht (zie het tabblad OvzArtTags van het Voorbeeldbestand) kunnen we ook maken door de ‘tags’ per artikel te laten zien.
In plaats van op category filteren we het taxonomy-veld op tag.

Overzicht van artikelen per ‘tag’

Maar met een kleine aanpassing wordt het overzicht nog interessanter (zie het tabblad OvzTagsArt van het Voorbeeldbestand); door tag en post_title in het Rijen-blok te wisselen zie je direct per onderwerp in welk artikel het aan bod is gekomen.

Denk je nu: dat ene artikel wil ik wel eens lezen, dan kun je dat natuurlijk op www.ginfo.nl opzoeken, maar makkelijker is om deze vanuit Excel direct op te starten.
Wil je bijvoorbeeld het artikel Aantallen turven lezen, dubbelklik dan op de 1 achter dat artikel. Excel zal de betreffende regel uit de bron-gegevens ophalen en deze in een apart tabblad weergeven:

NB vaak moeten enkele kolombreedtes aangepast worden om de inhoud te kunnen lezen.

Maar wat blijkt nu: Excel heeft wel de betreffende bron-gegevens opgehaald, maar helaas is de opmaak niet optimaal. In de zesde kolom staat geen echte hyperlink.

Twee oplossingen:

  1. kopieer de inhoud van de cel in de zesde kolom en plak dit in de adresbalk van uw internet-browser of
  2. klik in de cel met de hyperlink en klik dan in de formulebalk. Druk direct op Enter et voilà, Excel begrijpt ons weer:

    De cel bevat nu wel een ‘klikbare’ link.

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


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:


Unpivot



Ofwel hoe kom ik van een ‘gewoon’ Excel-overzicht naar een database-structuur.

Uit mijn artikelen mag blijken, dat ik een draaitabel-fanaat ben. De mogelijkheden en flexibiliteit van deze Excel-optie zijn grandioos. Een voorwaarde voor het gebruik daarvan is wel dat de brongegevens op een bepaalde manier klaar staan. Dit heb ik ooit de database-structuur genoemd.
Ben je verantwoordelijk voor een rapportageproces en vraag je iemand een overzicht te maken van bijvoorbeeld per dag de aantallen per regio, dan krijg je in 90% van de gevallen een overzicht zoals hierboven links wordt getoond (in de rijen de dagen en in de kolommen de regio’s), een ‘gewoon’ Excel-overzicht. Een groot voordeel hiervan dat het voor de mens makkelijk leesbaar en begrijpelijk is. Een groot nadeel dat je niet meer flexibel bent. Hoe zorg je er voor, dat totalen toch nog kloppen als je even één regio niet mee wilt laten tellen of wanneer je soms wel en soms niet de subtotalen van de maanden wilt tonen?

In dit artikel daarom nogmaals aandacht voor draaitabellen, maar zeker ook voor een methode om een ‘gewoon’ overzicht geautomatiseerd om te zetten naar een database-structuur.

Brongegevens

In het tabblad Data van het Voorbeeldbestand staat een overzicht met aantallen per werkdag van het eerste kwartaal van 2020, uitgesplitst naar regio.

In kolom B staan de werkdagen met een zodanige opmaak, dat ook de omschrijving van de dag wordt weergegeven (zie het artikel ginfo.nl/data-datums).

NB1 kolom G bevat per dag een totaal over alle regio’s. Excel toont in sommige cellen een klein waarschuwingsdriehoekje. Wanneer je met de muis daarboven ‘gaat hangen’ krijg je te zien wat de waarschuwing is.

In dit geval ‘ziet’ Excel dat naast de getallen onder de regio’s er ook nog een getal links daarvan staat; intern Excel is een datum namelijk een getal.

NB2 de werkdagen zijn als volgt gecreëerd: in cel B3 is de datum 6-1-20 ingevoerd. Daarna is de vulgreep (het kleine vierkantje rechtsonder) naar beneden doorgevoerd. Niet met de linker-muis-toets, maar met behulp van de rechter-muis-toets. Laat u die toets los, dan kunt u diverse opties kiezen; in dit geval Werkdagen doorvoeren.

Unpivot

Voordat we op basis van deze brongegevens een draaitabel (in het Engels Pivottable) kunnen gaan maken moeten we het voorbeeldbestand eerst gaan omzetten naar een database-structuur. Daar gebruiken we Power Query voor (zie ook het artikel ginfo.nl/power-query).

Het voorbeeld-overzicht lijkt op het resultaat van een draaitabel; dit overzicht moeten we dus ont-draaitabellen. Laten we toch maar de Engelse uitdrukking Unpivot gebruiken.
In het tabblad Ovz1 van het Voorbeeldbestand was een kopie van de kolommen B t/m G uit het tabblad Data opgenomen.
Hoe gaan we deze nu Unpivot-ten?

  1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
    In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
  3. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
  4. eerst een paar kleine aanpassingen: verwijder de Totaal-kolom (die maken we straks wel weer met behulp van een draaitabel) en klik links van Datum op het kalender-symbool en wijzig het type van Datum/tijd in Datum
  5. zorg dat de Datum-kolom is geselecteerd en klik dan in de menutab Transformeren in het blok Alle kolommen op het kleine driehoekje naast de optie Draaitabel opheffen
    Kies de optie Draaitabel voor andere kolommen opheffen.
  1. Dat is het al bijna! Verander de kop van
    de 2e kolom in Regio en die van de 3e in Aantal (via dubbelklikken).
  2. Kies dan in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
    In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Draaitabel

Op basis van de nieuwe brongegevens gaan we nu een draaitabel maken:

  1. klik ergens in de nieuwe tabel
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. Bepaal waar de nieuwe draaitabel moet komen en klik op OK
  4. Plaats de Datum in het blok Rijen, Regio in Kolommen en Aantal in het Waarden-blok.
    Afhankelijk van de Excel-versie zal Excel de datums ook al groeperen in maanden. Gebeurt dit bij u niet automatisch dan kunt u later de datums nog (automatisch laten) groeperen.

En we hebben een overzicht, dat uitblinkt in gemak en flexibiliteit (zie het tabblad Ovz1 van het Voorbeeldbestand): gebruik de plus- en min-tekens vóór de maanden om deze uit- of in te klappen, klik op een driehoekje bij de kopjes om een bepaalde item wel of niet mee te nemen, bepaalde sortering door te voeren etc.

NB Ontvangt u gegevens over andere maanden, voeg die dan onderaan de bron-tabel toe. Vergeet niet daarna de met Power Query gegeneerde tabel te vernieuwen én ook de draaitabel te vernieuwen.

Brongegevens2

Maar dezelfde brongegevens kunnen natuurlijk ook worden aangeleverd zoals hiernaast weergegeven.

Het jaar en de maand zijn alleen gevuld wanneer er een wijziging plaats vindt.

Voordat we op bovenstaande manier kunnen gaan ‘Unpivot-ten’ moeten de ontbrekende gegevens in die kolommen worden aangevuld.
Dat kan handmatig door de waardes te kopiëren, maar dat kan wat makkelijker:

  1. selecteer in de Maand-kolom alle cellen met en zonder waardes (op het tabblad Data2 van het Voorbeeldbestand de cellen C3:C64)
  2. klik Ctrl-G (of gebruik de F5-toets, dit is de optie Ga naar), kies de button Speciaal, in het vervolgscherm de optie Lege cellen en klik dan op OK
  3. de eerste lege cel is nu geselecteerd. Type daarin het =-teken en tik op de cursor-omhoog-toets (we maken dus een verwijzing naar de bovenstaande cel, in dit geval =C3).
    LET OP druk dan NIET op Enter maar op Ctrl-Enter. Op die manier worden alle geselecteerde (dus lege) cellen met deze formule gevuld.
  4. Selecteer alle gevulde cellen in de Maand-kolom, tik Ctrl-C en klik dan met de rechter-muisknop ergens in dit gebied en kies de tweede Plak-optie (Waarden). De formules worden door harde waarden overschreven.
    NB deze laatste stap is eigenlijk niet nodig voor het verdere Unpivot-proces.
  5. Doe hetzelfde voor de Jaar-kolom.

Dit overzicht kan dan op dezelfde manier als hiervoor worden getransformeerd naar een database-structuur (zie tabblad Data2 in het Voorbeeldbestand). Met dit verschil: zorg dat in stap 5 eerst de kolommen Jaar, Maand en Dag zijn geselecteerd.

Unpivot2

Maar het bijwerken van de brongegevens kunnen we natuurlijk ook met behulp van Power Query doen (zie het tabblad Ovz2 in het Voorbeeldbestand):
NB ik kreeg een tip van Martien, dat onderstaande methode veel te omslachtig is (zie reactie hieronder); als algemene werkwijze bij het gebruik van gegevens uit andere records is het wel belangrijk om deze manier te kennen.

  1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
  3. In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
  4. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
  5. in deze records moeten op diverse plaatsen dus waarden uit het record daarvoor worden opgehaald; dat kan alleen als de records een index hebben:
    kies in de menutab Kolom toevoegen de optie Indexkolom
  6. nu gaan we een nieuwe jaarkolom toevoegen. Kies in de menutab Kolom toevoegen de optie Voorwaardelijke kolom.

    Vul het vervolgscherm in zoals hierboven.
  7. Het nieuwe resultaat ziet er nu als volgt uit:

    In de formulebalk moeten we “test” vervangen door een formule die verwijst naar het Jaar in het vorige record:
    #”Index toegevoegd”{[Index]-1}[Jaar]
    Ofwel: zoek in het resultaat van de vorige stap (met de naam Index toegevoegd) in het record met het volgnummer Index-1 de waarde in de kolom Jaar.
  8. deze formule moet wel in alle records worden doorgevoerd:
    selecteer de eerste cel in de nieuwe kolom en kies in de menutab Transformeren in het blok Alle kolommen de optie Doorvoeren omlaag
  9. voor de maand wordt op een vergelijkbare manier een nieuwe kolom toegevoegd. De querystap ziet er dan als volgt uit:
    = Table.AddColumn(#”Omlaag doorgevoerd”, “Aangepast.1”, each if [Maand] = null then #”Omlaag doorgevoerd”{[Index]-1}[Maand] else [Maand])
    Vergeet niet de formule in deze kolom door te voeren.
    NB het opzoeken in andere records is voor Excel een intensief proces; het verversen van de gegevens kan even duren.
  10. de oorspronkelijke Jaar– en Maand-kolom en ook de Index kunnen nu verwijderd worden. Verplaats de 2 aangepaste kolommen naar voren en geef die dan de namen Jaar en Maand.
  11. we zijn toch lekker bezig. Laten we zorgen dat we ook een echte datum in het bestand krijgen:
    Kies via het driehoekje linksboven in de tabel (dus naast de nieuwe kolom Jaar) de optie Kolom toevoegen vanuit voorbeelden.
    Vul onder Kolom1 in het vervolgscherm in: 6 jan 2020 en druk op Enter. Uit de formule, die Excel genereert, blijkt dat hij/zij niet weet wat we willen. In de tweede regel plaatsen we 7 jan 2020. Nu begrijpen we elkaar, dus klik op OK.
    De nieuwe kolom heeft de naam Samengevoegd gekregen; verander die in Datum (via dubbelklikken).
    Het type van deze kolom is Tekst (zie de aanduiding ABC naast de naam); dit veranderen we in Datum (klik op ABC).
    Verplaats deze kolom nu zodat die op de vierde plaats komt.
  12. nu nog even de unpivot-truc: selecteer de eerste 4 kolommen en kies de optie Draaitabel voor andere kolommen opheffen.
    Nog even de namen van de twee laatste kolommen wijzigen in Regio en Aantal.
  13. Als laatste: kies in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
    In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Voor het resultaat, een draaitabel en de Power Query’s zie het tabblad Ovz2 van het Voorbeeldbestand.

NB1 een query is altijd te bekijken door in het scherm Werkmapquery’s op de betreffende query te dubbelklikken.

NB2 het kan gebeuren dat een query niet direct in Excel geladen wordt (het ‘wieltje’ bij de querynaam blijft draaien) stop het vernieuwen en probeer het opnieuw:


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