Tagarchief: Grafiek

Hoog-Laag-Slot-grafiek

16 december 2016ExcelCSE, Gegevensvalidatie, Grafiek, Index, Indirect, VergelijkenGijs Verbruggen

Binnenkort (nou ja, over een paar maanden) hebben we weer verkiezingen. In de aanloop daar naartoe zien we in de media steeds vaker de resultaten van diverse peilingen.

Op de site peilingwijzer.tomlouwerse.nl is het meest recente resultaat van een wetenschappelijk onderbouwde combinatie van 5 peilers (nee Maurice, geen pijlers!) te vinden.

Een grafiek op die site laat van 13 politieke partijen het verloop van de peilingen in de tijd zien; naast het gemiddelde van de 5 peilers zien we in de grafiek ook een 95%-onzekerheidsmarge (het licht gekleurde gebied).

Hieronder laat ik zien, hoe je in Excel een dergelijke grafiek kunt maken; we gebruiken daar een hoog-laag-slot-grafiek voor.
Daarbij moet wel aangetekend worden, dat de grafiek op de site van Tom Louwerse veel meer interactieve elementen bevat!

Basisgegevens

Zoals voor iedere grafiek hebben we basisgegevens nodig; deze zijn in dit geval op te halen vanaf de download-pagina van de Peilingwijzer.

In het Voorbeeldbestand heb ik op het tabblad Peilingwijzer deze gegevens geplaatst (op dit moment van 12 september 2012 tot en met 27 november 2016); zoals in eerdere voorbeelden ‘gieten’ we deze in de vorm van een tabel (Invoegen/Tabel, kopregels aanvinken) en geven die tabel de naam tblpeilingWijzer.

NB1 komen er nieuwe peiling-data beschikbaar, voeg deze dan onderaan de tabel als nieuwe regels toe.

NB2 komen er nieuwe partijen in het overzicht bij, voeg deze dan rechts aan de tabel als nieuwe kolommen toe.

Parameters

Hierna gaan we een grafiek maken van de peiling-resultaten. Hierbij kan echter maar één partij tegelijkertijd worden weergegeven. Om de invoer daarvan straks te vergemakkelijken leggen we de keuzemogelijkheden in een aparte tabel vast (zie tabblad Param in het Voorbeeldbestand).

NB wordt er in de peilingen een extra politieke partij meegenomen, dan moet deze onderaan toegevoegd worden; aangezien het ook hier een tabel betreft, zal Excel de nieuwe regel automatisch in tblPartijen meenemen.

Het is ook wel handig om te weten wat de eerste en laatste datum is, die in het peilingoverzicht voorkomt.
In cel E2 van het tabblad Param uit het Voorbeeldbestand bepalen we de eerste/kleinste datum door het minimum van die kolom op te zoeken.
Normaal gesproken zou dat kunnen met de formule =MIN(tblPeilingWijzer[Datum]), maar helaas levert dat de waarde 0 op. Dat komt omdat in de datum-kolom geen echte datum staat maar een tekst, dus het wordt wat ingewikkelder:
{=MIN(DATUM(
LINKS(tblPeilingWijzer[Datum];4);
DEEL(tblPeilingWijzer[Datum];6;2);
RECHTS(tblPeilingWijzer[Datum];2)))}

Met behulp van de functie LINKS zoeken we het jaar op, DEEL haalt 2 tekens op vanaf positie 6 (de maand dus) en RECHTS levert de dag. Deze 3 resultaten geven we door aan de functie DATUM, die er een datum van maakt. De functie MIN zoekt dan de kleinste datum op.

LET OP de 3 tekst-functies kijken eigenlijk alleen naar de datum uit de corresponderende rij (de 2e dus). Door na het intikken van de formule niet op Enter te drukken maar op Ctrl-Shift-Enter wordt het een zogenaamde array- of matrix-formule en worden de 3 tekst-functies op alle datums ‘losgelaten’. Vaak wordt dit ook CSE-invoer genoemd; Excel plaatst automatisch accolades om de formule (niet handmatig intypen!).

De formule in E3 mag dan geen verrassingen meer bevatten.

Voor het gemak hebben we de cellen E2 en E3 een naam gegeven MinDatum, resp. MaxDatum.

Grafiek met onzekerheidsmarge

Voordat we een dergelijke grafiek kunnen maken moeten we eerst een methode hebben om de gegevens van één partij uit de basis te destilleren.

Cel C3 (met de naam invPartij) in het tabblad Ovz van het Voorbeeldbestand gebruiken we als keuzevak:

kies in de menutab Gegevens de optie Gegevensvalidatie
in het pop-up-scherm kiezen we bij Toestaan: de optie Lijst
bij Bron: zouden we alle keuzemogelijkheden kunnen intikken gescheiden door een ; (punt-komma), dus bijvoorbeeld VVD;PvdA;GL etc.
Maar dat is niet zo handig. Die lijst kunnen we ook uit het tabblad Param halen: kolom Partijen in de tabel tblPartijen.
Helaas kan gegevensvalidatie niet goed omgaan met de nieuwere tabellen (geïntroduceerd in versie 2007), dus de formule =tblPartijen([Partijen]) werkt niet, maar wel als we de functie INDIRECT gebruiken (zie ook het artikel Tabellen (deel 2); denk aan de aanhalingstekens!!).
Eventueel nog een Invoerbericht en/of Foutmelding toevoegen (zie Voorbeeldbestand) en we zijn klaar: klik op OK.

De voorbereidingen zijn klaar, nu nog de gegevens ophalen van de gekozen partij:

in rij 5 van het tabblad Ovz van het Voorbeeldbestand staat een kopregel
vanaf B6 naar beneden staan alle datums, waarvoor er peilingen zijn
in cel C6 moet de eerste peiling van de gekozen partij komen:
=INDEX(
INDIRECT(“tblPeilingWijzer[“&invPartij&”]”);
VERGELIJKEN($B6;tblPeilingWijzer[Datum];0))
Weet u niet (meer) hoe de functie INDEX werkt? Kijk in het artikel Zoeken: Index en Vergelijken; daar vindt u ook een truc hoe u de de functie makkelijk implementeert.
Weer gebruiken we INDIRECT om de kolom, waarin gezocht moet worden, afhankelijk te maken van de cel C3 (met de naam invPartij).
de formules in D6 en E6 zijn vergelijkbaar, alleen worden daar de partij-gegevens uit de low- en high-kolom opgehaald.
de drie formules uit C6:E6 worden naar beneden gekopieerd, zodat bij iedere datum de gegevens tevoorschijn komen.
via de menutab Invoegen en de optie Tabel maken we van dit overzicht een tabel (met de naam tblGrafBasis). Wanneer er nieuwe peilingen beschikbaar zijn is het dan voldoende om onderaan de datum toe te voegen. Alle formules worden dan automatisch door Excel ingevuld.

En nu de grafiek:

plaats de cursor ergens in tblGrafBasis
in de menutab Invoegen kiest u in het blok Grafieken de optie Overige grafieken.
In het uitklapmenu nemen we de eerste grafieksoort in het blokje Hoog/Laag/Slot.
Dit soort grafieken wordt vaak in de financiële wereld gebruikt om een overzicht van de dagkoersen weer te geven: per dag ziet u dan de hoogste, laagste en slotkoers.
NB Excel beoordeelt automatisch in welke kolom de hoge, lage of slot-waarde (of in ons geval het gemiddelde) staat.
Nog even wat opmaak regelen:
* kies in de nieuwe menutab Hulpmiddelen voor grafieken het tabblad Indeling
* kies in het blokje Huidige selectie de Reeks Gemiddeld
* daarna de optie Selectie opmaken
* en kies bij Lijnkleur een gewenste kleur
* kies in het blokje Huidige selectie de Hoog/laag-lijnen
* daarna de optie Selectie opmaken
* en kies bij Lijnkleur dezelfde kleur, maar met een Transparantie van 75%
* kies in het blokje Huidige selectie de Reeks Hoog
* daarna de optie Selectie opmaken
* en kies bij Markeringsopties Geen
* Grafiektitel laten verwijzen naar cel E3
* aan de linker-as een Titel toevoegen, de notatie wijzigen in een percentage zonder decimalen en zorgen dat er het bereik altijd van 0% tot 30% loopt.

Wijzig cel C3 en bekijk het resultaat!

Grafiek zonder nullen

9 november 2016ExcelGrafiek, NBGijs Verbruggen

Ik kreeg deze week van Hasan de volgende vraag in mijn mailbox:

Ik ben bezig met een lijngrafiek waarbij de waarde nul zichtbaar is in mijn grafiek. Dat wil ik niet. Hoe kan ik dat oplossen?

Meestal leiden er meerdere wegen naar Rome, maar hiervoor kan ik toch maar één oplossing bedenken. Kent iemand nog een alternatief?

Aan de hand van het (vereenvoudigde) voorbeeld van Hasan zal ik laten zien hoe de oplossing werkt.

Prognose versus realisatie

Hasan heeft een overzicht per week (en maand en kwartaal) van budgetcijfers, prognoses en realisaties. Ik zal verder werken met alleen prognoses en realisaties (zie het tabblad Basis in het Voorbeeldbestand).

Zoals te zien is, kennen we de realisatie tot en met week 7; maar zit deze nou boven of onder de prognose?

Misschien dat een grafische weergave meer inzicht geeft?

Tsja, dat helpt nog niet echt veel. Nee, we ontkomen er niet aan om naast bovenstaande cijfers ook met cumulatieven vanaf week 1 te gaan werken.

Prognose versus realisatie, incl. cumulatieven

Twee extra kolommen nemen de cumulatieve cijfers voor hun rekening (zie tabblad Cum1 in het Voorbeeldbestand):

in cel E3 komt dezelfde waarde als in C3
zoals te zien is, staat in E4 de formule
=E3+C4
ofwel: neem de vorige cumulatieve prognose (E3) en tel daar de prognose van de betreffende week bij op (C4)
deze formule wordt naar beneden gekopieerd
hetzelfde gebeurt in kolom F voor de realisatie

Deze 2 kolommen aan de grafiek toevoegen; duidelijk (?) is te zien dat de totale realisatie (de paarse lijn) achter blijft bij de cumulatieve prognose (blauwe lijn).

Maar we kennen de realisatie t/m week 7 al; waarom dan nog met de prognose van die eerste weken rekenen om tot een cumulatieve prognose te komen? Zo is ook de cumulatieve realisatie na week 7 niet interessant.

Prognose versus realisatie, incl. cumulatieven (deel 2)

Daarom een nieuwe opzet gemaakt voor de cumulatieve kolommen (zie tabblad Cum2 in het Voorbeeldbestand):

allereerst maken we het ons gemakkelijk en plaatsen we in cel D2 het laatste weeknummer waarvan we de realisatie kennen
in cel E5 komt de eerste ‘cumulatieve’ prognose; deze is afhankelijk van D2. Als er nog geen realisatie is (D2<B5) dan is de cumulatieve prognose gelijk aan de prognose van de eerste week (C5); is de eerste week afgesloten (D2=B5) dan is de cumulatieve prognose gelijk aan de (cumulatieve) realisatie (F5; is gelijk aan D5) en anders doet de cum. prognose er niet toe en maken we die gelijk aan 0.
de cumulatieve prognose van de tweede week komt in cel E6:
=ALS(B6=$D$2;F6;ALS(B6>$D$2;E5+C6;0))
Als het de laatst gerealiseerde week betreft (B6=$D$2) dan komt hier de waarde van de cumulatieve realisatie (F6). Als het weeknummer groter is dan de afgesloten week (B6>$D$2)dan is de cumulatieve prognose gelijk aan de vorige (E5) plus de prognose van deze week (C6) en anders doet de prognose er niet meer toe (dus 0).
de formule in E6 kan naar beneden gekopieerd worden (vandaar dat D2 ‘absoluut’ is gemaakt mbv de $-tekens).
nu nog de cumulatieve realisatie: in cel F5 komt de formule
=ALS($D$2>=B5;D5;0)
Dus als de gerealiseerde week (D2) groter of gelijk is aan de eerste week (B5) dan wordt de eerste cumulatieve realisatie gelijk aan de eerste realisatie (D5) en anders 0.
in F6 komt de tweede cumulatieve realisatie:
=ALS($D$2>=B6;F5+D6;0)
Als de gerealiseerde week (D2) groter of gelijk is aan de betreffende week (B6) dan wordt de cumulatieve realisatie gelijk aan de vorige cumulatiieve realisatie (F5) plus de realisatie van deze week (D6) en anders 0.
ook deze formule kan naar beneden worden gekopieerd.

De bijbehorende grafiek laat het resultaat zien, wanneer de realisatie van week 7 bekend is (de weekcijfers horen bij de linker (primaire) as, de cumulatieven bij de rechter (secundaire) as).

Hier wordt het probleem van Hasan zichtbaar: door de nullen in de diverse formules zien we een vreemde overgang van week 6 naar 7 (bij de cumulatieve prognose) en van week 7 naar 8 (bij de realisatie). Ook het wijzigen van de 0 in de formules in dubbele aanhalingstekens levert geen oplossing; Excel interpreteert dit in de grafiek als nul-waarden.

Prognose versus realisatie, grafiek zonder nullen

De enige oplossing (die ik kan bedenken) is om de nullen niet te vervangen door iets leegs (de dubbele aanhalingstekens) maar door de functie NB(). Deze geeft aan dat de waarde Niet Bekend is en deze worden in de grafiek niet weergegeven (zie tabblad Cum3 in het Voorbeeldbestand).

Dat ziet er beter uit:

De cumulatieve lijn loopt netjes door.
De grafiek heeft een dynamische titel; gekoppeld aan cel F2.
Kleuren zijn aan elkaar aangepast.
De secundaire as heeft een zodanige vaste verdeling gekregen (een maximum van 2500), dat de horizontale lijnen én links én rechts toegepast kunnen worden.

NB Is niet alleen de grafiek van belang maar wordt ook de tabel gebruikt in rapportages, zorg dan dat de #N/B’s niet zichtbaar zijn; dat kan eenvoudig met een voorwaardelijke opmaak.

Eerbetoon aan Rosling cs

20 november 2015Excel, VBA (Visual Basic for Applications)Beldiagram, Grafiek, Index, Namen, Schuifbalk, Vergelijken, VERT.ZOEKENGijs Verbruggen

Rosling

Ongetwijfeld hebt u op Internet, Youtube of op TV (DWDD of bijvoorbeeld bij Zondag met Lubach) wel eens een presentatie van Hans Rosling gezien.
Iedere keer is het weer interessant en enerverend (eigenlijk ook wel spannend) om te zien hoe hij (openbare, vrij beschikbare) gegevens weet om te zetten in informatie.
Wikipedia: Rosling stichtte de Gapminder Foundation samen met zijn zoon Ola Rosling en zijn schoondochter Anna Rosling Rönnlund. Gapminder ontwikkelde de Trendalyzer-software, die internationale statistieken omzet in bewegende, interactieve en onderhoudende grafieken.
Het doel is de promotie van een wereldvisie gebaseerd op feiten, door verhoogd gebruik en begrip van gratis toegankelijke openbare statistieken. Zijn lezingen aan de hand van Gapminder-visualisaties vielen in de prijzen doordat ze grappig en toch doodernstig zijn. De interactieve animaties zijn vrij beschikbaar op de website van de stichting (zie gapminder.org).
Bij veel van de presentaties gebruikt Rosling de Trendalyzer-software, waarmee het mogelijk is om diverse items in samenhang te tonen.
Zoals hiernaast bijvoorbeeld: op de (niet zichtbare) assen is het Inkomen per inwoner tegen de Levensverwachting uitgezet. Per land wordt dit door een bolletje weergegeven, waarbij de grootte van het bolletje wordt bepaald door het aantal inwoners van dat land. Doordat de grafiek ook nog eens een reis door de tijd kan maken, waardoor we historische ontwikkelingen kunnen zien, hebben we dus te maken met een informatie-overzicht met maar liefst 5 dimensies!

Bellendiagram

Toen ik bovenstaand voorbeeld zag, vroeg ik me af in hoeverre het mogelijk zou zijn om dit in Excel na te bouwen.
Zelf gebruik ik in rapportages zogenaamde bellendiagrammen (in het Engels bubble chart) wel eens, maar ze zijn niet echt gangbaar.
Daarom leek het me wel de moeite waard om dit idee uit te werken; ook als een soort eerbetoon aan het idealistisch te noemen werk van Rosling cs.

Basis-gegevens

Voordat we een grafiek kunnen gaan maken, moeten we natuurlijk de beschikking hebben over relevante basisgegevens.
Omdat Rosling alleen gebruik maakt van openbare bronnen is dit verzamelen niet zo moeilijk; op de site gapminder.org vinden we de nodige gegevens zelf of verwijzingen naar de bronnen.
In het Voorbeeldbestand zijn die opgenomen in de tabbladen Landen (overzicht van bijna alle landen ter wereld), Inkomen (het jaarinkomen per inwoner van de diverse landen, van 1800 tot 2015), Bevolking (het aantal inwoners per land, van 1800 tot 2015) en LevVerwachting (de levensverwachting bij geboorte per land, van 1800 tot 2015).

Inkomen
Bij sommige landen ontbraken (gedeeltes van) inkomen-gegevens. Om te zorgen dat het tekenen van de grafiek hierna niet spaak zal lopen, heb ik die gegevens aangevuld met de waarde 1 (één).

Bevolking
Het aantal inwoners per land gaat in de grafiek de belgrootte bepalen. Vandaar dat ook hier de ontbrekende gegevens aangevuld zijn met de waarde 1.

Parameters

Alle gegevens, die nodig zijn voor de besturing van ons Excel-systeem, zijn vastgelegd op het tabblad Parameters:

alle landen, die we in de grafiek willen opnemen,
het beginjaar en
eindjaar (in dit systeem 1800, respectievelijk 2015),
het jaar, waarvoor we de grafiek willen zien,
een indicator voor de snelheid van de verandering van de grafiek (overgang naar een volgend jaar) en
het opschrift van een button, die we maken om de grafiek ‘af te kunnen spelen’.

Om verwijzingen in formules overzichtelijker/leesbaarder te maken zijn aan alle parameters namen gegeven. Het snelste gaat dat op de volgende manier:

selecteer de cellen, die een naam moeten krijgen (inclusief de cellen daarboven)
kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie
zorg dat in het vervolgscherm (in dit geval) alleen het vinkje bij Bovenste rij aan staat en klik OK

NB Klik op één van de parameters (bijvoorbeeld 2015) en zie dat in het Naamvak linksboven niet meer de rij en kolom wordt weergegeven, maar de naam, die we aan de cel hebben gegeven.

Op dezelfde manier heeft de reeks landen ook een naam gekregen. Klik op het pijltje naast het Naamvak en kies Landen om dit te verifiëren.

Berekeningen

Voordat we de grafiek kunnen maken, zullen we alle benodigde gegevens bij elkaar moeten verzamelen in een vorm, die handig is om als bron voor de grafiek te fungeren.

In het tabblad Berek van het Voorbeeldbestand staat in cel B3 de formule =Landen (een verwijzing naar de landen op het tabblad Parameters). Deze formule is zo vaak naar beneden gekopieerd als noodzakelijk is om alle landen te zien.

LET OP een dergelijke verwijzing naar een zelf-gedefinieerde naam voor een bereik haalt gegevens op uit de corresponderende regel, dus in dit geval regel 3. Wil je dat niet: selecteer dan eerst alle cellen, die gevuld moeten worden; tik in =Landen en druk dan op Ctrl-Shift-Enter. Op deze manier wordt een zogenaamde matrixformule ingevoerd; Excel zet automatisch accolades rond de formule.

Per land laten we Excel de corresponderende gegevens opzoeken:

in kolom C de regio:
=VERT.ZOEKEN(B3;LandenBron;2;ONWAAR)
de inhoud van cel B3 wordt in het bereik LandenBron opgezocht. Als het land gevonden wordt, dan levert de functie het corresponderende resultaat uit de 2e kolom van het blok. Met ONWAAR geven we aan, dat we alleen tevreden zijn als de inhoud van B3 ook echt gevonden is (dus niet Benaderen).
het inkomen per land komt in kolom D:
=INDEX(Inkomen;VERGELIJKEN($B3;InkLand;0);VERGELIJKEN(KeuzeJaar;InkJaar;0))
Hoewel de inkomengegevens ook met VERT.ZOEKEN zouden kunnen worden gevonden, heb ik voor de functie Index gekozen (vind ik persoonlijk beter leesbaar en is meer universeel toepasbaar).
Met behulp van Index zoeken we in het blok Inkomen de gewenste regel en kolom op en krijgen direct het resultaat.
Maar op welke regel staat het betreffende land? Met
VERGELIJKEN($B3;InkLand;0)
is dat zo geregeld: InkLand is de naam van de reeks landen op het tabblad Inkomen. De functie Vergelijken geeft de positie van B3 in deze reeks.
Op een vergelijkbare manier wordt de juiste kolom opgezocht (het gewenste jaar staat in de parameter Keuzejaar).
LET OP de 3e parameter van de functie Vergelijken moet 0 (nul) zijn: we zoeken ook weer hier een exacte waarde, geen benadering.
de Levensverwachting en de Bevolkingsgrootte worden ook mbv de functie Index gevuld.

Grafiek

Eindelijk zijn we zover; we gaan de verzamelde informatie grafisch weergeven.

Stap voor stap (op het tabblad Graf van het Voorbeeldbestand staat het uiteindelijke resultaat):

maak een nieuw tabblad aan (bijvoorbeeld via onderaan op het scherm, op het einde van de andere tabbladen)
kies in de menutab Invoegen in het blok Grafieken de optie Overige grafieken en kies de eerste optie bij Bel
in het (lege) grafiekgebied rechts klikken en de optie Gegevens selecteren … kiezen en dan Toevoegen
in het nieuwe scherm de gegevens voor de x- en y-as en de belgrootte invullen. De reeksnaam laten we leeg; heeft bij een belgrafiek weinig nut.
Klik twee keer op OK en de grafiek is klaar!
nog wat verfraaiingen: de rasterlijnen en legenda weglaten, titels bij de assen etc.
Excel past standaard de assen automatisch aan op basis van de gegevens die gepresenteerd worden. Dat willen we niet, want als het KeuzeJaar wordt veranderd, gaat de grafiek ‘springen’.
Klik rechts op één van de cijfers van de y-as en zorg dat de Levensverwachting loopt van 10 tot 100 jaar.
Ook de x-as passen we aan: het Inkomen laten we lopen van 200 naar 100.000. Maar we zijn nog niet klaar: in het gebied met lage inkomens zitten heel veel bellen heel dicht bij elkaar, de hoge inkomens zijn uitschieters. Door deze as logaritmisch weer te geven, worden de lage inkomens duidelijker onderscheiden, terwijl de hogere inkomens ‘in elkaar schuiven’.
LET OP Een dergelijke logaritmische indeling is moeilijk leesbaar, dus alleen gebruiken als de exacte getallen niet wezenlijk zijn.
alle bellen krijgen dezelfde kleur. Handmatig is dit aan te passen, maar bij deze hoeveelheid niet echt praktisch. Met een VBA-routine zou dit (op basis van de regio) wel mogelijk zijn.
In dit geval heb ik de bel van Nederland opgezocht (aan de hand van de onderliggende cijfers) en die gekleurd en een label meegegeven.
Het jaar achter de grafiek?
Maak een tekstvak aan (via de menutab Invoegen) en tik direct in de formulebalk in =Keuzejaar. Nog wat lay-outen: lettertype en -grootte etcetera en schuif het tekstvak achter de grafiek (in het Voorbeeldbestand zijn de randen bewust zichtbaar gehouden).

Schuifbalk
Om gemakkelijk het verloop in de tijd te kunnen volgen is onder aan de grafiek een schuifbalk toegevoegd:

kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen en kies de schuifbalk (rechts naast Aa onder Formulierbestruringselementen)
’teken’ met de muis ergens op het grafiektabblad de plaats waar de schuifbalk moet komen
klik rechts op de schuifbalk en vul de diverse opties in:
Huidige waarde: laten we beginnen met 1800
Minimumwaarde: in dit geval 1800
Maximumwaarde: 2015 dus
De stappen daaronder worden 1 en 10 (1 jaar verder wanneer op het pijltje wordt geklikt, 10 jaar wanneer er in het lege gebied van de schuifbalk wordt geklikt)
Koppeling met cel: hier vullen we KeuzeJaar in; een verwijzing naar het tabblad Parameters dus.
klik OK

Mbv deze schuifbalk kunnen we nu makkelijk onze grafiek laten veranderen: de reis in de tijd kan beginnen!

Reis in de tijd
Het is natuurlijk nog mooier als we de veranderingen in de tijd automatisch kunnen laten zien.
Achter de Play-button op het tabblad Graf van het Voorbeeldbestand is een kleine VBA-routine opgenomen, die dit voor zijn rekening neemt.
Klik op Alt-F11 om de routine te bekijken. Hebt u vragen hierover? Schroom niet om contact op te nemen met G-Info.
Om de snelheid aan te passen (van 1 naar 5 sec als pauze) is nog een schuifbalk toegevoegd.

Temperaturen en thermometer

3 juli 2015ExcelAANTALLEN.ALS, Gegevensvalidatie, Grafiek, Grootste, Plakken speciaal, SOM.ALS, Tabel, VERT.ZOEKENGijs Verbruggen

Het is vandaag toch geen weer om buiten te zijn, dus maar achter de PC gekropen om een artikel voor G-Info te schrijven.

Het onderwerp lag voor de hand: waar hebben we het allemaal over de laatste dagen? Juist ja, de temperaturen!

Eens kijken of we (met behulp van Excel) nog wat informatie kunnen toevoegen. Uit KNMI-gegevens maken we een grafiek waardoor het onderwerp nog meer gaat leven.

Voor de fun: in het Voorbeeldbestand vind je ook een “live”-thermometer.

Brongegevens

Voordat we temperatuurgrafieken kunnen maken hebben we brongegevens nodig.
Even “googelen”: al snel bleek, dat de KNMI heel veel (historisch) materiaal klaar heeft staan, dat je gratis kunt downloaden (zie http://www.knmi.nl/klimatologie/uurgegevens/select_uur).

Ik heb voor dit artikel alleen de temperatuur per uur voor drie weerstations voor het lopende jaar binnengehaald (hoewel de gegevens over de duur van de neerslag ook wel eens interessant zouden kunnen zijn!)

Na het downloaden hebben we een tekstbestand op de PC staan; deze gegevens allereerst maar eens in Excel geplakt (zie het tabblad KNMI in het Voorbeeldbestand).
Na diverse regels toelichting komen de gegevens waar het ons om te doen is: het weerstationnummer, de datum, het uur en de temperatuur. Per dag zien we alle gegevens in 1 cel komen; gescheiden door een komma.
Dat wordt “knippen”:

selecteer alle cellen met temperatuurgegevens (in het tabblad KNMI de cellen B17:B13120)
kies in de menutab Gegevens de optie Tekst naar kolommen
er opent zich een nieuw scherm: kies daar de optie Gescheiden en klik op de knop Volgende
zorg dat in het volgende scherm bij Scheidingstekens ook Komma staat aangevinkt en klik op de knop Voltooien
alle gegevens zijn nu mooi over kolommen verdeeld

Wat opvalt: de datum staat in het militaire formaat (jjjjmmdd, dus 4 cijfers voor het jaar, 2 voor de maand en 2 voor de dag; in die volgorde!).
Daarnaast moeten we de temperatuur nog door 10 delen om netjes graden Celsius te krijgen.

TIP op de volgende manier gaat dat het snelst:

klik ergens in een lege cel de waarde 10 in
kopieer deze cel (bijvoorbeeld via Ctrl-C)
selecteer alle temperatuurcellen
klik met de rechtermuisknop ergens in de selectie
kies de optie Plakken speciaal …
kies bij Bewerking de optie Delen
klik op de knop OK
maak de cel uit punt 1 weer leeg

Temperatuurgrafiek1

Om op de standaardmanier een grafiek te maken moeten we de gegevens van de KNMI nog iets anders indelen: na het “knippen” zetten we de temperaturen van de drie weerstations achter elkaar in verschillende kolommen en zetten nog wat verklarende teksten boven de kolommen (zie tabblad Grafiek1).

Maak op de ‘gewone’ manier een grafiek.
Zorg dat op de x-as de Datum en het Uur zichtbaar worden door én de gegevens van kolom B én die van kolom C op te geven.

Door de grote hoeveelheid dagen en daarbinnen de temperatuur per uur wordt de grafiek moeilijk te lezen.

Temperatuurgrafiek2

Een alternatieve grafiek kunnen we creëren door middel van een draaitabel. Een groot voordeel hierbij is dat de brongegevens niet hoeven worden aangepast.

In het Voorbeeldbestand zijn in het tabblad KNMI_data de brongegevens in een Excel-tabel opgenomen. Nieuwe gegevens kunnen onderaan worden toegevoegd; zijn ze nog niet ‘geknipt’, doe dat dan hier. Excel neemt automatisch de deling van de KNMI-temperatuur door 10 voor zijn rekening en als ’toegift’ wordt de datum in een normaal formaat gezet.

In een draaitabel worden alle gegevens uit de tabel in een bruikbaar formaat gezet (zie tabblad Draai in het Voorbeeldbestand).
Hierbij zijn een paar handigheidjes verwerkt:

in de kolommen zijn de weerstations weergegeven; standaard komen hier de nummers tevoorschijn. Deze nummers heb ik overschreven met de betreffende namen. Voortaan houdt Excel deze wijziging vast.
in de rijen staan naast de uren ook de echte datums uit KNMI_data. Deze zijn echter zodanig gegroepeerd, dat er ook een onderscheid in jaren en maanden wordt gemaakt:
* klik met de rechtermuisknop op een van de datums
* kies de optie Groeperen
* in het vervolgscherm de grenzen ruim genoeg zetten, zodat toekomstige gegevens ook direct goed verwerkt worden
* klik bij de optie Op de Dagen, Maanden en Jaren aan
* klik op de knop OK
* sleep de Jaren en Maanden van de Rijlabels naar het Rapportfilter

Wanneer de draaitabel actief is (klik ergens in de draaitabel) komt er bovenaan een nieuwe set tabbladen tevoorschijn: Hulpmiddelen voor draaitabellen. Daar vinden we binnen de menutab Opties een knop Draaigrafiek.

Het resultaat staat op het tabblad Grafiek2.
Via de filters op Jaren, Maanden en Station kunnen meer of minder en andere gegevens zichtbaar gemaakt worden.

LET OP als er regels aan de tabel KNMI_data zijn toegevoegd, kies dan wel nog de optie Vernieuwen in de Draaitabel of de Draaigrafiek (hoe? Klik rechts ergens in de tabel of aan de rand van de grafiek).

Thermometer

Terwijl ik dit aan het schrijven ben, kijk ik af en toe op de thermometer om te kijken of mijn warmtegevoel overeen komt met de werkelijkheid. Nou, hierbinnen is het bijna 30 graden en zo voelt het ook! Dus nog maar wat water drinken.

Maar dit is wel aanleiding om te kijken of we in Excel ook een thermometer kunnen inbouwen.
Daar gaat ie (zie tabblad Thermometer in het Voorbeeldbestand):

om de keuze van weerstation te vergemakkelijken maken we een klein tabelletje met ‘rugnummers’. Wanneer we aan de tabel KNMI_data ook andere weerstations toevoegen dan moet dit lijstje uitgebreid worden
in de cellen F2 en F3 kan gekozen worden voor welke dag en welk station de temperatuur moet worden weergegeven.
Denk aan de datum-notatie!
Cel F3 is van een Gegevensvalidatie voorzien, zodat daar alleen nummers gekozen kunnen worden uit het lijstje van punt 1. In cel F4 komt dan automatisch de naam van het station via de formule
=VERT.ZOEKEN(F3;Stations;2;ONWAAR)
De waarde van cel F3 (in dit geval 380) wordt opgezocht in de tabel met de naam Stations (het lijstje uit punt 1). Als die gevonden wordt, dan wordt de waarde uit de 2e kolom teruggegeven (de naam dus). Door Onwaar geven we aan, dat we een exacte match willen; de lijst hoeft dus niet gesorteerd te zijn.
dan gaan we de temperatuur opzoeken, die bij die dag én dat station hoort en dat per uur:
=SOMMEN.ALS(KNMI_data[Temp];KNMI_data[Datum];$F$2;KNMI_data[Station];Thermometer!$F$3;KNMI_data[Uur];E10)
Niet schrikken! Verticaal zoeken met verschillende voorwaarden kan niet, dus daarom iets ingewikkelder: tel alle temperaturen in de kolom Temp van de tabel KNMI_data op (we zorgen, dat er maar 1 temperatuur wordt opgehaald, dus dat is een makkie voor Excel) als aan de volgende voorwaarden wordt voldaan:
* in de kolom Datum van de tabel KNMI_data staat de waarde uit cel F2
* én in de kolom Station staat de waarde uit cel F3
* én het Uur komt overeen met cel E10
Door de relatieve en absolute verwijzingen kunnen we de formule uit F10 naar beneden kopiëren.
omdat op deze manier de waarde 0 (nul) ontstaat als er geen gegevens zijn staat er nog een ALS-formule omheen, die een lege resultaat genereert als er geen temperatuur te vinden is.
in de cellen F5, F6 en F7 worden de minimum-, maximum- en laatst bekende waarde opgehaald (voor de historische data zal dit dus altijd 12 uur ’s nachts zijn).
NB de formule in cel F7, die als matrixformule is ingevoerd (zie de accolades), vergt teveel uitleg voor dit weer! Probeer zelf de logica te ontrafelen. Kom je er niet uit; stuur een berichtje via de site.
dan een grafiek maken met de 3 waardes uit de cellen F5, F6 en F7, zodat we naast de “actuele” temperatuur ook het minimum en maximum van die dag zien.
De actuele/laatste waarde van die dag als een staafdiagram, de andere twee als lijngrafiek, inclusief markering.
Omdat we onder 0 graden ook een markering willen zien, is er nog een reeks toegevoegd met de waarde -50.
Bijna klaar: het moet er nog als een echte thermometer uitzien. Dus een afbeelding van een thermometer gegoogeled en daar de grafiek overheen gelegd, zodanig dat de schalen overeen komen.
Dan in de grafiek alle overbodige zaken als assen en rasterlijnen verwijderen en het grafiek- en tekengebied transparant maken (de optie Geen opvulling).
De 2 objecten (Grafiek en Afbeelding) allebei selecteren en Groeperen kiezen, zodat ze samen verplaatst kunnen worden: daar is onze thermometer!

NB door de tabel in het tabblad KNMI_data aan te vullen met de meest recente gegevens zal de thermometer een echt actuele stand weergeven.
Helaas nog geen real-time! Iemand een idee hoe dat zou kunnen??

Dynamische grafieken

11 april 2015ExcelF9, Grafiek, Schuifbalk, Verschuiving, VERT.ZOEKENGijs Verbruggen

Niet iedereen zal meteen weten, wat hiermee bedoeld wordt.
De term dynamische grafiek wordt in de Excel-omgeving gebruikt, wanneer een grafiek zich direct aanpast aan wijzigingen in de bron-spreadsheet zonder dat de gebruikte bereiken voor de x- en y-waarden hoeven worden aangepast.
Het bekendste voorbeeld: er komen omzet-gegevens van een nieuwe maand beschikbaar; deze worden onderaan een reeks toegevoegd. Dan zou het fijn zijn als de bijbehorende grafiek dit automatisch zou overnemen.

Eenvoudige oplossing

Op allerlei manieren is er een bepaalde vorm van dynamiek te implementeren.

Laten we eerst een eenvoudige implementatie bekijken.
In het Voorbeeldbestand op het tabblad DynGraf1 zijn bedragen (kolom C) voor iedere maand (kolom A) opgenomen; in kolom B hebben de maanden een volgnummer gekregen.
Stel dat we alleen de cijfers tot en met juni willen weergeven:

cel D1 krijgt dan de waarde 6
in D4 staat de formule: =ALS(B4<=$D$1;A4;””)
Dus als het volgnummer kleiner of gelijk is aan de waarde in cel D1, dan wordt de waarde uit A4 (de maand) overgenomen en anders komt er niets (er staan alleen maar 2 aanhalingstekens)
idem voor cel E4: =ALS(B4<=$D$1;C4;””)
D4 en E4 kopiëren we naar beneden
dan maken we een grafiek, die voor de x-waarden (horizontale as) “kijkt” naar de cellen D4 t/m D15 en voor de y-waarden naar E4 t/m E15
de titel van de grafiek maken we dynamisch door een verwijzing te maken naar cel B17; dit gaat als volgt:
* geef de grafiek een willekeurige titel
* klik in de titel, dan in de formulebalk en voer daar het =-teken in, gevolgd door een verwijzing naar de gewenste cel: en druk op Enter
in cel B17 zorgen we dat de tekst aangepast wordt voor de maandkeuze:
=”Bedragen t/m “&VERT.ZOEKEN($D$1;B4:D15;3;ONWAAR)
Hier worden 2 teksten gekoppeld (zie het artikel Teksten samenvoegen): de “harde” tekst Bedragen t/m en de betreffende maand, die met de functie verticaal zoeken wordt opgehaald (zoek in de eerste kolom van het blok B4:D15 naar de waarde uit cel D1; geef de corresponderende waarde uit de 3e kolom van dat blok terug)

Een groot nadeel van de bovenstaande methode is, dat er voor de ontbrekende maanden een lege ruimte in de grafiek overblijft.
Dat kunnen we voorkomen door de grafiek dynamisch te maken met behulp van de functie Verschuiving.

Verschuiving

Voordat we deze functie voor grafieken gaan gebruiken eerst wat uitleg:
overal waar je in Excel een verwijzing naar een cel of een groep cellen (een cel-bereik of -range) gebruikt kun je ook VERSCHUIVING gebruiken.
De verwijzing zal dan niet naar de opgegeven cel zijn, maar naar 1 of meerdere cellen verschoven. Een voorbeeld:

kies in het Voorbeeldbestand het tabblad DynGraf1
in cel H4 zetten we de formule =VERSCHUIVING(C4;2;0)
het resultaat is 120; de functie geeft de inhoud van de cel 2 rijen onder en 0 kolommen rechts van C4 (dus C6)
zo geeft de formule =VERSCHUIVING(C4;1;2) de waarde 110 (namelijk de inhoud van cel E5)
=VERSCHUIVING(E3;3;-2) werkt ook!! Resultaat is 120; de inhoud van cel C6.
=VERSCHUIVING(D3;D1;0) en =VERSCHUIVING(D3;D1;1) leveren de maand en het bedrag afhankelijk van de waarde in cel D1
Maar deze functie kent nog meer parameters (optioneel; zijn dus niet verplicht). We kunnen ook nog aangeven hoeveel rijen en kolommen het bereik vanuit de nieuwe positie moet hebben:
=VERSCHUIVING(D3;1;1;3;1) geeft als resultaat de inhoud van de cellen E4:E6; namelijk, vanuit cel D3 startend, 1 rij naar beneden en 1 kolom naar rechts (dus E4) nemen we een bereik van 3 rijen hoog en 1 kolom breed.
Wanneer we deze formule intikken in Excel, levert dit het resultaat 100; Excel laat de waarde van de eerste cel (E4 dus) zien.
NB wil je weten wat Excel intern als resultaat van de functie vasthoudt:
* klik in de formulebalk achter de betreffende functie en druk op F9
* in plaats van de formule komt nu het resultaat: ; dus wel degelijk de inhoud van drie cellen E4, E5 en E6
* druk daarna niet op Enter maar op Esc
maar als het resultaat van de vorige functie de inhoud van 3 cellen is, dan kunnen we natuurlijk ook nog het volgende doen:
=SOM(VERSCHUIVING(D3;1;1;D1;1))
Het resultaat is de som van de bedragen van de maanden, waarbij cel D1 bepaalt hoeveel maanden er worden meegenomen.

Complexere (maar mooiere) oplossing

Nu we met Verschuiving overweg kunnen, kunnen we die voor onze dynamische grafiek gebruiken:

kies opnieuw in het Voorbeeldbestand het tabblad DynGraf1
scrol naar beneden zodat rij 70 in beeld is
hier staat nog een grafiek die afhankelijk is van de waarde in cel D1; maar deze heeft geen “lege” maanden
voordat je een dergelijke grafiek kunt maken, moeten binnen Excel enkele nieuwe namen gedefinieerd worden, die voor de x- en y-waarden worden gebruikt:
* ga naar de menu-tab Formules
* kies binnen het blok Gedefinieerde namen de optie Namen beheren
* kies de optie Nieuw, bij Naam tikken we in Maand en Verwijst naar wordt =verschuiving($A$49;0;0;$D$1;1)
* klik OK; in het overzicht van namen is onze nieuwe naam Maand opgenomen, waarbij Excel aan de cellen A49 en D1 automatisch de naam van het betreffende tabblad en een ! heeft toegevoegd.
LET OP maak verwijzingen naar cellen ALTIJD absoluut (met de $-tekens) anders zal de naam relatief zijn t.o.v. de cel, die op het moment van aanmaken geselecteerd is
op dezelfde manier maken we een nieuwe naam Bedrag: =verschuiving($B$49;0;0;$D$1;1) of
=verschuiving($A$49;0;1;$D$1;1)
bij het maken van de grafiek geven we als Aslabelbereik =DynGraf1!Maand op en als Reekswaarden de naam =DynGraf1!Bedrag.
LET OP begin met het =-teken en laat de hierboven gedefinieerde naam voorafgaan door de naam van het tabblad en een !
Verander de waarde in D1 en bekijk het resultaat

NB wat gebeurt er als je in D1 bijvoorbeeld 15 intikt?

NB2 zet in D1 de formule =AANTAL(B49:B60) en maak cel B60 leeg

Nog complexere (en nog mooiere) oplossingen

In het Voorbeeldbestand heb ik nog 2 alternatieve oplossingen opgenomen.

In het eerste voorbeeld kun je ook de beginmaand opnemen, zodat de grafiek niet altijd met januari hoeft te beginnen, maar je bijvoorbeeld alleen de resultaten van het lopende kwartaal kunt laten zien.

Open het tabblad DynGraf2 en bekijk de opbouw van de sheet en de grafiek.
In dit tabblad worden de namen Maand2, Bedrag2 en Totaal gebruikt. De laatste is toegewezen aan het tekstvak, dat over de grafiek ligt.

In het laatste voorbeeld (tabblad DynGraf3) kunnen de grenzen met behulp van zogenaamde Schuifbalken worden ingesteld.

Om deze in een sheet te plaatsen kies je in de menu-tab Ontwikkelaars in het blok Besturingselementen de optie Invoegen. Klik dan binnen het blok Formulierbesturingselementen de optie Schuifbalk; geef met de cursor in de sheet aan waar de balk moet komen. Daarna kan na rechtsklikken op de schuifbalk het besturingselement opgemaakt worden.

Zoals te zien is kunnen hier de minimale en maximale waarden worden vastgelegd. Van groot belang is de optie Koppeling met cel.