Categorie archief: Excel

Data verrijken

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

Voorbeeldbestand: DataVerrijken Download

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

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

Brongegevens

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

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

Verkoopoverzicht

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

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

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

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

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

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

Maar hoe zit het met de klanten?

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

Zie het tabblad OvzVerkoop.

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

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

Op deze manier hebben we handmatig de eerste verrijkingen aangebracht.

Verrijking door toevoegen kolommen

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

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

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

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

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

Verrijken m.b.v. gegevensmodel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Verrijken m.b.v. Power Query

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

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

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

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

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

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

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

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

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

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

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

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

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

Corona-data

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

Voorbeeldbestand van 29-3: Corona.xlsx Download

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

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

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

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

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

Brongegevens

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

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

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

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

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

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

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

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

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

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

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

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

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

Overzicht stand van zaken Nederland

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

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

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

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

Overzicht per gemeente, basis

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

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

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

Overzicht per provincie (1)

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

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

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

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

Overzicht per provincie (2)

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

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

Overzicht per provincie (3)

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

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

Overzicht per gemeente (1)

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

*Nieuwsgierig naar details? Klik op de afbeelding.*

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

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

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

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

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

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

Overzicht per gemeente (2)

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

Overzicht per gemeente (3)

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

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

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

NB De kolomnamen zijn handmatig aangepast.

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

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

Overzicht artikelen van G-Info

26 februari 2020ExcelDraaitabel, HyperlinkGijs Verbruggen

Voorbeeldbestand: ArtOvz.xlsx Download

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:

selecteer één van de cellen van de brongegevens
kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
alle instellingen staan goed, dus klik op OK
sleep post_date naar het blok Rijen
idem met ID en post_title
plaats hulp in het Waarden-gebied
er zijn nu 2 menubladen bijgekomen: Hulpmiddelen voor draaitabellen
kies daar in de menutab Ontwerpen in het blok Indeling de optie Rapportindeling; kies dan Tabelweergave
klik rechts op de subtotalen en verwijder het vinkje bij Subtotaal
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:

kopieer de inhoud van de cel in de zesde kolom en plak dit in de adresbalk van uw internet-browser of
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.

Reken-nauwkeurigheid van Excel

11 februari 2020ExcelAfronden, Formuletekst, StandaarddeviatieGijs Verbruggen

Voorbeeldbestand: Afronden.xlsx Download

Het symbool hiernaast betekent ongeveer gelijk aan.

Als we met Excel aan het rekenen zijn, nemen we daar natuurlijk geen genoegen mee! Toch moeten we ons altijd bewust zijn van de beperkingen van dit hulpmiddel wat rekenen betreft.

In dit artikel zullen we aan de hand van enkele voorbeelden laten zien dat Excel zeker ‘rekenfouten’ maakt. Soms worden dit bugs in Excel genoemd, maar dat is niet juist: het is onder andere de consequentie van het binaire karakter van onze computers.
Uiteraard kijken we ook hoe we in ons dagelijks werk rekening kunnen houden met dit ‘vreemde’ gedrag van Excel (en ieder ander rekenprogramma).

‘Rekenfouten’

Nog steeds als ik een (simpel) rekenapparaat in handen krijg moet ik gauw even testen hoe nauwkeurig die werkt: 1 delen door 3, het resultaat vermenigvuldigen met 3 en van dat resultaat 1 aftrekken. Het resultaat zou dan 0 moeten zijn.

In het tabblad Simpel van het Voorbeeldbestand staat een dergelijke berekening. In cel B6 is te zien, dat Excel in ieder geval vindt dat het eindresultaat exact gelijk is aan nul.

NB1 in kolom C is de functie Formuletekst gebruikt; deze zet een eventuele formule in de cel, die je tussen de haakjes meegeeft (in het voorbeeld cel B3), om in een tekst. Deze functie bestaat vanaf Excel 2013.

NB2 als de betreffende cel geen formule bevat dan is het resultaat van deze functie #N/B (Niet Beschikbaar).
Dit kunt u op de volgende manier ondervangen: =ALS.FOUT(FORMULETEKST(B3);”Geen formule”)

Maar eigenlijk is het niet zo logisch, dat het resultaat nul is! We weten allemaal dat, als we de breuk 1/3 omzetten naar een decimaal getal, we nooit klaar zijn; het aantal drietjes achter de komma is oneindig.

In het tweede voorbeeld op het tabblad Simpel staat dezelfde berekening, maar nu zijn alle resultaten weergegeven met 20 decimalen.

Duidelijk is te zien, dat Excel bij het berekenen van 1/3 er na 15 decimalen mee ophoudt. In de dagelijkse praktijk kunnen we daar meestal ook wel mee leven.
Maar: als we dat resultaat weer met 3 vermenigvuldigen komt Excel toch op 1 uit en niet op 0,99999… Wie houdt nu wie voor de gek? Blijkbaar legt Excel intern het getal 1/3 anders vast dan wij zien. Dat is natuurlijk ook wel logisch: wij zijn gewend om te rekenen in het decimale stelsel, terwijl computers in het binaire stelsel maar tot 2 kunnen tellen, nog erger eigenlijk maar tot 1. Zij kunnen alleen maar werken met nullen en enen; voor gehele getallen kom je dan een heel eind (als je maar genoeg bits op een rijtje zet), maar bij decimale getallen komen computers al snel ‘in de problemen’.

Met dit in het achterhoofd is het eigenlijk verwonderlijk dat we niet iedere dag Excel op rekenfouten betrappen.

NB na wat googlen: “Excel werkt met een aangepaste 1985-versie van de IEEE 754 specificatie. De implementatie van Excel omvat conversies tussen binaire en decimale weergaven, wat leidt tot een nauwkeurigheid die gemiddeld beter is dan men zou verwachten van een eenvoudige 15-cijferige nauwkeurigheid.“

Nog een voorbeeld: we laten Excel 1/9 uitrekenen. Dit zou een getal met oneindig veel decimalen gelijk aan 1 moeten opleveren; ook hier laat Excel maar 15 eentjes zien.
Daar tellen we 1 bij op en van dat resultaat trekken we weer 1 af. Als we die uitkomst aftrekken van de eerste berekening komt er precies nul uit.

Maar doen we hetzelfde met 1/9000 dan komt er geen nul meer uit! Het resultaat is ‘veel’ groter dan waar we mee begonnen.

NB1 6,17E-17 is de zogenaamde wetenschappelijke notatie van een getal; dit betekent dat we 6,17 moeten vermenigvuldigen met 10 tot de macht -17 (10^-17) ofwel delen door 10^17 ofwel de 6 komt op de plaats van de 17e decimaal.

NB2 de notatie E-17 moet niet verward worden met de in de wiskunde gebruikte exponentiële functie e:

Nog meer voorbeelden

Helaas is de reeks voorbeelden van ‘onverklaarbare rekenfouten’ in Excel schier onuitputtelijk. In het tabblad Vb van het Voorbeeldbestand vindt u er nog een paar:

In Vb3 is het getal 7745,11319561 ingevoerd; Excel verandert dat op eigen houtje.

En wat te denken van het verschil tussen 7,11 en 7,1 of 7,11 en 7,01?

Hiernaast staat een simpele berekening: het tweede en derde getal worden afgetrokken van het eerste. Iedereen ziet direct dat daar 0 uit moet komen. Gelukkig: Excel is het daarmee eens.

Maar wat nu? In kolom Q doen we dezelfde berekening, maar tellen daar dan nog 0 (nul, zero, niente, nada) bij op. Het resultaat is schrikken.

Het mag duidelijk zijn, dat we altijd alert moeten blijven bij resultaten van berekeningen in Excel. Ik kan me niet voorstellen, dat een bedrijf failliet gaat wanneer het laatste van 15 significante cijfers niet helemaal klopt. Maar er kunnen wel problemen ontstaan bij geautomatiseerde spreadsheets: verticaal zoeken levert verkeerde resultaten, controletellingen komen niet op nul uit, formules met Als leveren een foutieve uitkomst.

Afronden

Veel van de hierboven geschilderde problemen kunnen we oplossen door tussen- en/of eindresultaten af te ronden.

In het tabblad Afronden van het Voorbeeldbestand ronden we in kolom C iedere tussenstap van het vierde voorbeeld af op 5 decimalen.

NB 5 decimalen is ruim voldoende als het bijvoorbeeld een financieel overzicht betreft. De diverse eindresultaten dienen dan op 2 decimalen aan te sluiten.

Sneller, handiger en efficiënter is het om alleen de einduitkomst af te ronden:

In het tabblad Afronden van het Voorbeeldbestand vindt u diverse overzichten waarmee u kunt zien wat de effecten van de verschillende afrondingsmethoden zijn. Ook ziet u daar de consequenties van negatieve waarden voor het aantal decimalen.

Wat als?

De aanleiding voor dit artikel was een vraag van iemand, die zich afvroeg waarom zijn metingen-systeem in Excel zich ‘ineens zo raar gedroeg’.
Per monster worden 5 metingen gedaan; om te bepalen hoe groot de spreiding van de metingen is wordt een standaarddeviatie berekend.

Omdat het regelmatig voorkomt dat de metingen allemaal dezelfde waarde opleveren moet dit zichtbaar gemaakt worden door weergave van de tekst SD=0. Blijkbaar is dit jaren goed gegaan maar ineens kwamen er vreemde resultaten.
In het tabblad SD van het Voorbeeldbestand zijn in kolom B vijf meetresultaten opgevoerd; allemaal de waarde -1,6108. In cel C9 is met de functie STDEV de standaarddeviatie hiervan bepaald.
Waar we nul zouden verwachten, blijkt Excel een kleine waarde te genereren, waardoor in cel G9 niet de verwachte tekst (SD=0) tevoorschijn komt.

NB1 de functie STDEV is een ‘oude’ functie, die uit comptabiliteitsoverwegingen nog in Excel gebruikt kan worden.

NB2 de functie STDEV berekent de deviatie voor een steekproef. De werking is dus gelijk aan de ‘nieuwe’ functie STDEV.S.
Voor het bepalen van de deviatie voor de hele populatie moet u de functie STDEV.P (nieuw) of STDEVP (oud) gebruiken.

Op het tabblad SD van het Voorbeeldbestand hebben de 4 deviatie-berekeningen een voorwaardelijke opmaak gekregen: als het resultaat nul is, dan wordt het vak groen gekleurd, anders rood.

LET OP in kolom D staan dezelfde deviatiewaarden als in kolom C. De celeigenschappen hiervan zijn zodanig gewijzigd, dat er geen decimalen zichtbaar zijn. Nu lijkt het resultaat wel nul te zijn, maar de voorwaardelijke opmaak laat zien, dat dit niet zo is.
We hebben alleen de opmaak gewijzigd, niet de inhoud van de cel.

in cel G11: =ALS(AFRONDEN(C9;10)=0;”SD=0″;C9)

Wat te doen, als we toch de Als-functie willen gebruiken of voorwaardelijke opmaak aan cellen willen toevoegen?
Eén oplossing hebben we hiervoor al gezien: gebruik de functie Afronden.

Een andere methode is om binnen de Als-functie de mogelijke onnauwkeurigheid in te bouwen. In cel G10 staat de formule
=ALS(C9<0,00000000000001;”SD=0″;C9)

NB1 omdat een standaarddeviatie altijd positief is werkt bovenstaande formule prima. Als je niet zeker weet of de afwijking positief of negatief zal zijn dan is het beter om de volgende formule te gebruiken:
=ALS(ABS(C9)<0,00000000000001;”SD=0″;C9)

NB2 uiteraard hangt het van de situatie af, welke grens er in de vergelijking moet worden ingevuld. In dit geval zou de volgende formule ook voldoen:
=ALS(ABS(C9)<1e-10;”SD=0″;C9)

Allemaal gelijk of niet?

In het voorbeeld op het tabblad SD van het Voorbeeldbestand wordt de functie STDEV ook gebruikt om te beoordelen of alle metingen hetzelfde zijn.

Dat kan natuurlijk ook op een andere manier.
Hiernaast is in cel C18 de minimum-waarde van de metingen bepaald en in cel C19 het maximum.
In cel C20 wordt dan gekeken of deze twee gelijk zijn; zo ja dan zijn alle metingen gelijk.

Uiteraard is de tussenstap niet per se nodig. In cel D20 staat de formule:
=MIN(B3:B7)=MAX(B3:B7)

NB voor het bepalen van het minimum en maximum hoeft Excel geen berekening uit te voeren. We hoeven dus niet ‘bang te zijn’ dat Excel een extra onnauwkeurigheid toevoegt.

Klimaat-streepjescode

16 januari 2020ExcelDraaitabel, Trendlijn, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: GemTemp.xlsx Download

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

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

Brongegevens

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

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

Jaar-overzicht

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

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

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

Maand-overzicht

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

NB bij Maand is het item Year uitgevinkt.

Maandgrafiek

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

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

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

Jaargrafiek

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

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

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

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

Klimaat-streepjescode

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

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

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

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

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

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

Analyse

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

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

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

De wordt bepaald met de functie R.KWADRAAT.

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

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

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

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