Tagarchief: Draaitabel

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:


Grafiek automatisch schalen en labels toevoegen



Herkent u de frustratie? Heb je net een grafiek ‘netjes’ gemaakt door de assen zodanig te schalen dat de gegevens mooi verdeeld zijn over de grafiek, klopt er niets meer van als er een andere of uitgebreidere reeks gegevens worden weergegeven.
Of je hebt ‘met de hand’  alle labels bij de punten aangepast (ja, ik gebruik nog een oude Excel-versie), dan gaan die bij een andere reeks de mist in.

Dus deze keer een anti-frustratie-artikel: 2 VBA-routines, die bovenstaande problemen oplossen.

Test-gegevens

Om een voorbeeld te kunnen maken had ik wat test-gegevens nodig. Meestal maak ik die door wat random-getallen of teksten te genereren.
Maar ik herinnerde me ineens dat ik ooit eens ergens gelezen had dat je ook test-gegevens op een site kon laten maken.
Na wat zoeken kwam ik de website mockaroo.com tegen.

Na een paar pogingen had ik een mooi lijstje met voorbeeld-gegevens (zie het tabblad Basis in het Voorbeeldbestand).

  1. bij Soort heb ik gekozen voor financiële markten (Mockaroo kiest dan willekeurig uit 2 mogelijkheden)
  2. de Datum ligt tussen 1 jan 2018 en 31 dec 2018
  3. bij Omschr heb ik voor willekeurige voornamen gekozen
  4. het Bedrag ligt tussen 1000 en 2000

Om de rest wat overzichtelijk te houden heb ik 20 regels daaruit geselecteerd (zie het tabblad Basis2) en een beetje aangepast. Deze test-gegevens staan klaar in een Excel-tabel met de naam Tabel1.

Grafiek

Om het verloop van de bedragen per Soort handig te kunnen weergeven, heb ik een draaitabel gemaakt op basis van de gegevens in Tabel1 (zie het tabblad Result in het Voorbeeldbestand).

Op basis van deze draaitabel kun je makkelijk een Draaigrafiek maken, maar dan komt Omschr automatisch op de x-as en ik wil deze gebruiken als Labels in de grafiek.

Dus maar een ‘gewone’ grafiek gemaakt met op de x-as de datums en op de y-as de bedragen.

Hiernaast staat de grafiek waarbij er geen Soort is geselecteerd (dus 20 punten). De x-as begint automatisch bij 19 januari omdat dat de kleinste datum is; iets vergelijkbaars geldt voor het einde van de as.
Excel heeft ook de y-as automatisch geschaald.

Assen handmatig aanpassen

Om de assen handmatig te schalen klik je met de rechtermuisknop op een as en pas je het minimum en het maximum aan (het vinkje daarvoor zal dan verdwijnen ten teken dat er geen automatische schaling hoeft plaats te vinden).

Maar iedere keer als er in de draaitabel een andere Soort wordt gekozen moeten er aanpassingen worden doorgevoerd.

Assen automatisch aanpassen

Om deze schaal-aanpassingen met VBA geautomatiseerd te kunnen doorvoeren moeten we ergens vastleggen wat het gewenste minimum en maximum is.

In het Voorbeeldbestand op het tabblad Result staat in cel G2 de formule =MIN(B:B)
Oftewel bepaal de kleinste waarde/datum in kolom B. In G3 wordt met de functie MAX de grootste waarde vastgelegd. Maar het is mooier als de x-as op de eerste van een maand begint: in cel H2 staat daartoe de formule =DATUM(JAAR(G2);MAAND(G2);1). Deze cel heeft de naam x_min gekregen.

Het maximum van de x-as moet de eerste dag van de volgende maand zijn: cel H3 (met de naam x_max) heeft daarom de formule =DATUM(JAAR(G3);MAAND(G3)+1;1)

NB ook als G3 een datum in december bevat, werkt deze formule goed; Excel zal automatisch het jaar ophogen en de maand gelijk maken aan januari.

In kolom J worden de minimum- en maximum-bedragen uit kolom D bepaald. Cel K2 bevat de formule =GEHEEL(J2/100)*100, zodat het minimum voor de y-as het eerste honderdtal is, kleiner dan de waarde in J2.
In cel K3 bepalen we de bovengrens voor de y-as: =(GEHEEL(J3/100)+1)*100

Nu hebben we nog een VBA-routine nodig, die de handelingen voor het aanpassen van de assen van ons overneemt.

In het tabblad Result is een knop met de naam Assen schalen toegevoegd waarmee deze macro/subroutine kan worden uitgevoerd.

Labels toevoegen

In de oudere versies van Excel is het niet mogelijk om automatisch extra informatie (zoals de Omschr uit het voorbeeld) als Labels aan een punt toe te voegen. Handmatig kan dit wel:

  1. klik rechts op de lijn in de grafiek
  2. kies de optie Gegevenslabels toevoegen
  3. klik op één van de labels; als dan alle labels zijn geselecteerd nogmaals op het label klikken
  4. de tekst van dit label kan nu aangepast worden

Maar als er nu in de draaitabel een andere Soort wordt gekozen dan kloppen de handmatig ingevoerde labels niet meer!

Daar komt VBA weer goed van pas:

Het spannende is hier wat er gebeurt met de variabele xVals.

Wanneer je in de grafiek op de lijn klikt verschijnt er in de formulebalk een formule die begint met =REEKS(
Daarna komt de cel, die de naam van de reeks bevat, dan de x-waarden en daarna de y-waarden.

Aangezien VBA Engelstalig is bevat xVals na het uitlezen van de formule van de Collection: =SERIES(Result!$B$4,Result!$B$6:$B$25,Result!$D$6:$D$25,1)

NB1 binnen Excel (met Nederlandse instellingen) worden de parameters gescheiden door een ; (punt-komma), maar binnen VBA is dit een , (komma).

NB2 de 1 op het einde geeft aan, dat deze lijn de eerste reeks is die in de grafiek geplot moet worden. We hebben maar één lijn, dus deze parameter heeft hier geen invloed.

We hebben in het vervolg van de routine alleen de reeks van de x-waarden nodig, de B6:B25.
Daarom nemen we eerst alles wat rechts van de eerste komma staat:
xVals = Mid(xVals, InStr(xVals, “,”) + 1)
en dan alles links van de volgende, dan weer eerste, komma:
xVals = Left(xVals, InStr(xVals, “,”) – 1)

xVals bevat nu  het bereik Result!$B$6:$B$25

LET OP als de naam van het tabblad zelf ook een komma bevat dan wordt de formule wel wat ingewikkelder (zie VBA).

De For-Next-loop gaat dan alle elementen van dit bereik langs en plaatst de waarde rechts van de datum in het betreffende label.

Uiteraard is deze routine ook via een knop uit te voeren op het tabblad Result van het Voorbeeldbestand.

Titel van grafiek

De titel van de grafiek is dynamisch gemaakt:

  1. in cel N2 staat de formule:  =”Bedragen van “&C2
    Oftewel koppel de inhoud van cel C2 aan de tekst Bedragen van.
  2. zorg dat de grafiek een of andere titel heeft
  3. klik ergens in de titel
  4. tik dan in de formulebalk =N2
  5. en druk op Enter

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

 

 

Olympische spelen



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

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

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

Basis-materiaal

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

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

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

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

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

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

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

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

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

De kolom Medaille heeft een Voorwaardelijke opmaak gekregen.

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

Overzichten

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

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

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

 

 

Unieke waarden



Er bestaan nogal wat situaties, waarbij je wilt weten of items allemaal verschillend zijn of niet.
En, als ze niet allemaal verschillend zijn, welke unieke exemplaren komen er dan voor?

In Excel kennen we een dergelijk probleem ook: welke unieke waarden komen er in een rij of kolom voor?

Deze keer zullen we enkele methoden de revue laten passeren; van simpele tot complexe, van opmaak tot selectie.

Voorwaardelijke opmaak

In het Voorbeeldbestand heb ik op het tabblad Data1 een tabel opgenomen met in de kolommen oa de afdelingen en het soort bedrag.

Alle cellen in die kolommen hebben een voorwaardelijke opmaak gekregen:

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. klik op de optie Nieuwe regel
  4. in het vervolgscherm kiezen we als Type: Een formule gebruiken
  5. en de formule wordt:
    =AANTAL.ALS(C$3:C3;C3)=1
    ofwel: als in het bereik C$3:C3 de waarde uit cel C3 1 keer voor komt mag de cel een opmaak krijgen.
    Dat is natuurlijk altijd waar!
    Maar wat gebeurt er als we deze voorwaardelijke opmaak ook ‘loslaten’ op een cel lager? Excel interpreteert de formule dan als =AANTAL.ALS(C$3:C4;C4)=1 en kijkt of de waarde uit C4 slechts 1 keer voor komt in het bereik C$3:C4.
    Ook wanneer deze formule in kolom D wordt toegepast is de controle precies wat we zoeken.
    NB om de formule ook in andere kolommen te kunnen gebruiken is alleen de 3 absoluut gemaakt als begin van het bereik (vandaar het $-teken vóór de 3) en is niet het meer voor de hand liggende $C$3 gebruikt.
  6. voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
  7. klik op OK
  8. zorg dat de opmaak op alle relevante cellen van kolom C en D wordt toegepast:
    * kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
    klik op de optie Regels beheren
    * vul bij Van Toepassing op het gewenste bereik in (in dit geval $C$3:$D$32)

NB1 het overzicht is een Excel-tabel. Daarom zal Excel, wanneer er een regel aan wordt toegevoegd, ook de Voorwaardelijke opmaak direct meenemen; het bereik wordt automatisch aangepast.

NB2 welke unieke waarden er in een tabel-kolom zitten ziet u ook direct door op het ‘vinkje’ achter een kolomkop te klikken.

NB3 het vorige ‘recept’ kan met 1 stap ingekort worden: selecteer in de eerste stap niet één cel, maar het totale bereik waar de opmaak voor moet gelden; stap 8 kan dan vervallen.

Via draaitabel

De vorige oplossing was een optische methode om unieke waarden te vinden; bij een grote tabel niet echt handig.

Het maken van een draaitabel is echter een simpele en doeltreffende manier om snel de unieke waarden in een kolom te vinden:

  1. selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
  4. klik OK
  5. sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
  6. hetzelfde kunt ook doen voor de Soort, maar u kunt beter de vorige draaitabel uitbreiden: sleep Soort naar Kolomlabels.
    Sleep ook nog Bedrag naar het Waarde-gebied en u hebt meteen een overzicht van de verdeling van de bedragen naar afdeling en soort!

    Zie ook het tabblad Data1 in het Voorbeeldbestand.

Even een uitstapje: wilt u weten wat het totaal van Srt1 is dan kunt u ook de volgende formule gebruiken: =SOM.ALS(tblData1[Soort];”Srt1″;tblData1[Bedrag]) maar beter is het volgende:

  1. plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
  2. in de cel daarnaast gaan we een formule opbouwen:
    tik in =som.als(
  3. wijs met de cursor de bovenkant van de kolom Afd aan (de cursor wordt dan een zwarte pijl naar beneden) en klik.
    De formule wordt dan aangevuld met tblData1[Soort] ofwel alle cellen in de kolom Soort van de Excel-tabel tblData1.
  4. tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
  5. als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
  6. nog een haakje sluiten en Enter

Door in G14 een andere soort in te typen, krijgt u daarvan het totaal.

Maar wat als er aan 2 (of meer voorwaarden moet worden voldaan?

U moet dan de functie SOMMEN.ALS gebruiken.
Deze werkt net iets anders: eerst geeft u het bereik op, waarvan u de som wilt bepalen, dan het eerste criterium-bereik met daarachter het criterium zelf, daarna een nieuw criterium-bereik met criterium etc.

Gegevensvalidatie

Maar in G14 kunnen nu willekeurige teksten worden ingevoerd. Dat is natuurlijk niet de bedoeling; we willen alleen bestaande soorten kunnen opgeven.

Dit gaat eenvoudig met gegevens-validatie:

  1. plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. kies bij Toestaan: de optie Lijst
  4. en bij Bron: kiest u de kolom Soort uit de Excel-tabel op de manier zoals hierboven aangegeven.
    NB Excel vertaalt de kolom direct naar daadwerkelijke cellen, maar als de tabel groter (of kleiner wordt) verandert dit bereik mee.
  5. klik op OK.

Oeps, het resultaat is niet helemaal wat we hoopten. In de keuzelijst staan alle voorkomens uit de kolom Soort, we willen natuurlijk alleen unieke waarden hebben!

Unieke waarden via filtering

Om een lijstje te maken van unieke waarden (maar u wilt geen draaitabel gebruiken) dan kent Excel nog een andere aanpak:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
  3. voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
  4. bij Kopiëren naar vult u de cel in, waar de unieke waarden moeten komen (in het voorbeeld heeft deze cel de naam AfdKop gekregen)
  5. vergeet niet het vinkje te plaatsen bij Unieke records!

In het tabblad Data1 van het Voorbeeldbestand ziet u het resultaat van deze actie.

Ook voor de kolom Soort zijn de unieke waarden opgehaald.

NB1 Wat direct opvalt is dat er geen (automatische) sortering plaats vindt. De volgorde is gelijk aan de volgorde in de bron.

NB2 als er regels aan de bron-tabel worden toegevoegd dan moeten de ophaal-acties opnieuw worden uitgevoerd.
Excel ‘onthoudt’ de gegevens van de laatste actie. Dit is ook te zien in het overzicht Namen beheren in de menutab Formules.

LET OP1 de locatie, waar de unieke waarden moeten komen, moet op hetzelfde tabblad staan als waar de brongegevens zich bevinden.

LET OP2 vergeet niet om bij het Uitgebreid filter ook de kop van de kolom mee te nemen, anders krijgt u de eerste waarde dubbel. Dit is nergens in de Microsoft-documentatie terug te vinden!

Gegevensvalidatie

Op basis van deze unieke waarden kunnen nu makkelijk Gegevensvalidaties worden gemaakt.
Maar om het geheel dynamisch te houden (als er nieuwe unieke waarden bijkomen moet dit automatisch worden meegenomen bij de gegevensvalidatie) wordt als bron-lijst niet  een hard bereik genomen (bijvoorbeeld R3:R5).

Nee, we creëren een dynamisch bereik door een naam toe te voegen:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. vul een naam in (bijvoorbeeld AfdUniek)
  3. en plaats de formule
    =VERSCHUIVING(AfdKop;1;0;AANTALARG(Data1!$R:$R)-1)
    in het vak Verwijst naar.

Bij gegevensvalidatie (zie cel G20 in het tabblad Data1 van het Voorbeeldbestand) gebruikt u als Bron de naam AfdUniek (vergeet niet het =-teken daar voor te zetten!).

In de cellen G21 en H21 is op een vergelijkbare manier gegevensvalidatie toegepast, maar daarbij zijn als bron voor de verschuiving 2 simpele draaitabellen gebruikt.

LET OP worden er regels aan de oorspronkelijke tabel toegevoegd, vergeet dan niet de draaitabellen te vernieuwen anders bevatten de gegevensvalidaties niet de meest recente gegevens.


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