Tagarchief: VERT.ZOEKEN

Eerbetoon aan Rosling cs

Excel, VBA (Visual Basic for Applications), , , , , ,

Rosling

RoslingOngetwijfeld 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).
roslingBij 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

Rosling2Toen 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’.

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

  1. Rosling4selecteer de cellen, die een naam moeten krijgen (inclusief de cellen daarboven)
  2. kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie
  3. 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.

Rosling5In 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:

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

  1. Rosling7maak een nieuw tabblad aan (bijvoorbeeld via Rosling6 onderaan op het scherm, op het einde van de andere tabbladen)
  2. kies in de menutab Invoegen in het blok Grafieken de optie Overige grafieken en kies de eerste optie bij Bel
  3. in het (lege) grafiekgebied rechts klikken en de optie Gegevens selecteren … kiezen en dan Toevoegen
  4. Rosling8in 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.
  5. Klik twee keer op OK en de grafiek is klaar!
  6. nog wat verfraaiingen: de rasterlijnen en legenda weglaten, titels bij de assen etc.
  7. 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.
  8. 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.
  9. 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
Rosling9Om gemakkelijk het verloop in de tijd te kunnen volgen is onder aan de grafiek een schuifbalk toegevoegd:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen en kies de schuifbalk (rechts naast Aa onder Formulierbestruringselementen)
  2. ’teken’ met de muis ergens op het grafiektabblad de plaats waar de schuifbalk moet komen
  3. Rosling10klik 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.
  4. 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.

Excel en het World-Wide-Web

Excel, , , , , , ,

Nee, dit is geen aankondiging van een nieuwe thriller, die ik geschreven zou hebben (dat laat ik aan een buurman over: zie glengoutstap.nl); in dit artikel wil ik laten zien welke mogelijkheden er zijn om Excel en het internet (www, world-wide-web) met elkaar te verbinden.

Het gaat dan wel om een eenzijdige verbintenis: hoe kunnen we met Excel op een makkelijke (?) manier informatie aan het internet onttrekken.

Methode 1

De meest gebruikte methode is:

  1. zoek op internet de pagina met gegevens, waar je iets mee wilt gaan doen in Excel
  2. selecteer met de muis de betreffende data
  3. kies Kopiëren
  4. ga naar Excel en kies Plakken

Voor eenmalige acties is hier niets mis mee; dit is dan zeker efficiënt en effectief. Maar wordt het een dagelijkse handeling om bijvoorbeeld koersgegevens op te halen, dan kan dat ook anders.

Methode 2

behr.nlVoor onderzoek naar koersverloop van indexen en bedrijven kom ik regelmatig op de site www.behr.nl; niet voor privé-gebruik ;-).
Daar kun je onder andere historische dagkoersen ophalen.
Even zoeken, maar via de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon zijn de dagkoersen van Aegon te vinden.

Behr.nlBehr levert als resultaat een pagina met datums en de daarbij behorende koersen. Geen verdere poespas daar omheen; dus zeker ook geschikt voor een snelle kopieer- en plak-actie.

Maar heb je dit overzicht vaker nodig dan gaat we dat slimmer aanpakken:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. Webkies binnen de menutab Gegevens in het blok Ext. geg. ophalen (ofwel Externe gegevens ophalen) de optie Van web
  3. WebEr opent zich een nieuw scherm; op de adresregel moet de hierboven genoemde URL ingevoerd worden.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. Door daar op te klikken kan aangegeven worden of dat betreffende blok wel of niet moet worden opgehaald. In dit geval klikken we alleen op het 2e pijltje; dat wordt dan een groen vinkje.
  6. kies dan onderaan het webquery-scherm de optie Importeren
  7. nog even aangeven waar de gegevens moeten komen en klaar: de data staan nu in Excel om verder verwerkt te worden.
  8. sla het bestand op met de naam Aegon.xlsx.

Moet de lijst morgen (of volgende week, volgende maand) geupdate worden:

  1.  open het bestand Aegon.xlsx
  2. klik met de muis rechts op één van de gevulde cellen en kies de onderste optie Vernieuwen

In het Voorbeeldbestand staat op het tabblad Aegon deze web-query. Daar kan die dus ook bijgewerkt worden.

LET OP de koppeling tussen Excel en internet is gebaseerd op het gebruik van tabellen op web-pagina´s.
Door de implementatie van nieuwere technieken bij de ontwikkeling van web-pagina’s komt het steeds vaker voor, dat Excel geen gegevens of veel teveel gegevens gaat ophalen. Aandachtspuntje voor Bill Gates!

Gegevens ‘knippen’

Helaas zijn de data, die op deze manier bij Behr.nl worden opgehaald, niet direct bruikbaar:  op iedere rij staat de datum en de koers in dezelfde cel (gescheiden door een :-teken), de datum-notatie ‘ziet er niet uit’ en de decimalen in de koers worden vooraf gegaan door een punt ipv een komma.
In het tabblad Heineken in het Voorbeeldbestand zijn daarom 2 kolommen toegevoegd, die ons het omzetten uit handen nemen: in de eerste kolom wordt mbv de functie DATUM een echte datum gecreëerd:

=DATUM(“20″&LINKS(B2;2);DEEL(B2;3;2);DEEL(B2;5;2))

PS1 de 2 meest linkse posities van cel B2 geven het jaar weer, het gedeelte vanaf positie 3 en 2 lang (dus positie 3 en 4) vormt de maand en de posities 5 en 6 vormen de dag.
PS2 aangezien Behr geen eeuw-aanduiding gebruikt ‘plakken’ we zelf er 20 voor, anders gaat Excel met datums uit de 20e eeuw werken.

De koers bepalen we op de volgende manier:

=WAARDE(SUBSTITUEREN(RECHTS(B2;LENGTE(B2)-7);”.”;”,”))

We nemen van B2 het rechtse gedeelte (we laten 7 tekens weg: de datum en het :-teken) en substitueren de punt door een komma. Omdat Excel dan ‘denkt’, dat dit een tekst oplevert, moeten we de waarde daarvan nemen.

Gegevens opmaken

In het Voorbeeldbestand worden ook de koersen van KPN opgehaald (op het tabblad KPN, dus).
Deze pagina is verder opgemaakt met kaders etc. Zorg er voor, dat bij het Vernieuwen deze opmaak niet wordt overschreven: klik rechts op één van de cellen uit de web-query (dus niet de hulp-kolommen) en kies de optie Eigenschappen van gegevensbereik …
Zorg dat Celopmaak behouden staat aangevinkt.
Experimenteer met de diverse opties in dit keuzescherm.

AEX

Het verloop van de AEX-index (en de aandelen, die daar onderdeel van uitmaken) zijn te vinden op www.beurs.nl/koersen/aex/p1.

Helaas is dit ook zo’n site met veel ‘ballast’ rond de cijfers: zie het tabblad AEX in het Voorbeeldbestand. Pas op regel 139 komen de data, waar we naar op zoek zijn.

Nog enkele opmerkingen bij dit tabblad:

  1. na het invoeren van het adres in de web-query komt er een Script-foutmelding: op Ja klikken
  2. Webtijdens het bewerken van de Webquery is via de button Opties… de opmaak ingesteld op HTML-indeling. Een groot gedeelte van de internet-opmaak en -mogelijkheden blijft dan gehandhaafd.
  3. WebDaarnaast is via Eigenschappen van gegevensbereik … ingesteld dat iedere minuut de pagina wordt geactualiseerd.

NB1 hoewel de site beurs.nl de koersen echt real-time laat zien, is dat op deze manier niet het geval. Een vertraging van 20-30 minuten is normaal.

NB2 ’s avonds zullen de koersen niet meer veranderen; de AEX sluit om 17:30 uur (en gaat om 9 uur weer van start).

Opmaak AEX

WebIn het tabblad AEX2 is een mooier overzicht van de AEX-koersen te vinden.  Met behulp van de functie VERT.ZOEKEN worden de gegevens uit het tabblad AEX opgehaald (en nu maar hopen dat de lay-out van de site en dus van het tabblad AEX niet verandert!).
Door gebruik te maken van voorwaardelijke opmaak is snel te zien welke koersen veel of weinig zijn gestegen of gedaald ten opzichte van de slotkoers van de vorige dag (de grens, waarbij een regel groen of rood wordt, is in cel C2 aan te passen).

NB bij het Vert.Zoeken wordt gebruik gemaakt van de functie KOLOM. Deze retourneert het kolomnummer van de cel, die als parameter wordt meegegeven. Geef je geen parameter mee dan geeft de functie de kolom terug van de cel waar de functie in staat.
Op deze manier is het mogelijk om de formule in cel C5 door te kopiëren naar het hele gebied zonder dat er nog aanpassingen nodig zijn. Let ook op de notatie van de Zoekwaarde ($B5; de verwijzing is dus half absoluut en half relatief).

Wandelingen op Veldkruus.nl

In het Voorbeeldbestand wordt op het tabblad Veldkruus het totaal-overzicht van de wandel- en fiets-tochten van de website Veldkruus.nl opgehaald.
Duidelijk is te zien, dat ‘harde returns’ in teksten in Excel als nieuwe regels tevoorschijn komen; voor een snelle verwerking van de gegevens (bijvoorbeeld sorteren op lengte) wordt het er dan niet makkelijker op.

NB even reclame maken voor een andere hobby van mij: met enkele ex-collega’s proberen we de veldkruisen, kapelletjes etcetera in Zuid-Limburg in kaart te brengen. Om het nog aantrekkelijker te maken zetten we ook ‘kruis-tochten’ langs deze objecten uit: zie Veldkruus.nl.

Dynamische web-query’s

Een groot nadeel van de hiervoor geschilderde koppeling tussen Excel en het Web is, dat de query een statisch karakter heeft: heb je een mooi overzicht van Heineken gemaakt en je wilt dezelfde gegevens van Starbucks zien, dan moet je alles opnieuw uitvoeren.

Gelukkig kunnen we de query met wat kunstgrepen dynamischer maken. Aan de hand van de slotkoersen van Behr.nl gaan we dit uitwerken. Het begin is hetzelfde als hierboven al aangegeven:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. kies binnen de menutab Gegevens in het blok Ext. geg. ophalen de optie Van web
  3. in het ‘Nieuwe web-query’-scherm voeren we op de adresregel de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon in.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. In dit geval alleen op het 2e pijltje klikken.

In plaats van importeren, slaan we deze query op. Rechts boven (naast Opties…) zit daarvoor een button Web9. Sla de query op met de naam Slotkoersen.iqy , bijvoorbeeld op het Bureaublad. Kies daarna Annuleren.

Web10Nu gaan we dat bestand, Slotkoersen.iqy, openen met een simpele tekstverwerker (in het voorbeeld met Kladblok; in de Verkenner rechtsklikken op het bestand).
Iedere web-query moet met de eerste 2 regels beginnen; de 3e regel herkennen we natuurlijk direct: dat is onze URL.
Dan een lege regel; gevolgd door de mogelijke instellingen voor web-query’s.

We passen de derde regel aan: op de plaats waar nu hard staat wat gezocht moet gaan worden (in dit geval ‘.a/aegon’) komt een parameter Fonds:

http://www.behr.nl/Beurs/Slotkoersen/[“Fonds”, “Geef codering van fonds (incl. letterindeling zoals ‘.k/kpn’)”]

LET OP de plaats van de [‘s, aanhalingstekens en de komma zijn wezenlijk

Sluit Kladblok en sla de query onder dezelfde naam op en dubbelklik op de bestandsnaam.
Excel zal opstarten en vraagt naar een code voor het fonds (zoals we hiervoor in de iqy bij de parameter hebben ingevoerd) en laat daarna de betreffende gegevens zien.
Klik rechts op een cel en kies Vernieuwen: dezelfde vraag komt weer en nu heb je de mogelijkheid om een ander fonds op te halen.
Zie ook het tabblad Behr1 in het Voorbeeldbestand.

Web11Het kan nog mooier:

  1. dubbelklik op Slotkoersen.iqy
  2. geef een code op: bijvoorbeeld .h/heineken
  3. tik dan in cel B1 in: .a/aegon
  4. rechtsklik op een cel van de web-query en kies Vernieuwen
  5. in plaats van een code tikken we nu in =b1 en zetten de 2 vinkjes aan
  6. tik in cel B1 een andere code: .s/Starbucks en de gegevens worden automatisch opgehaald!

In het tabblad Behr2 van het Voorbeeldbestand is dit nog iets verder uitgewerkt met behulp van Gegevens-validatie.

Temperaturen en thermometer

Excel, , , , , , ,

temp1Het 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 temp2hebben 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

temp3Voordat 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”:

  1. selecteer alle cellen met temperatuurgegevens (in het tabblad KNMI de cellen B17:B13120)
  2. temp4kies in de menutab Gegevens de optie Tekst naar kolommen
  3. er opent zich een nieuw scherm: kies daar de optie Gescheiden en klik op de knop Volgende
  4. zorg dat in het volgende scherm bij Scheidingstekens ook Komma staat aangevinkt en klik op de knop Voltooien
  5. 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:

  1. klik ergens in een lege cel de waarde 10 in
  2. kopieer deze cel (bijvoorbeeld via Ctrl-C)
  3. selecteer alle temperatuurcellen
  4. klik met de rechtermuisknop ergens in de selectie
  5. kies de optie Plakken speciaal …
  6. kies bij Bewerking de optie Delen
  7. klik op de knop OK
  8. 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).

temp5Maak 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:

  1. 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.
  2. 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
    * temp6kies 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.

temp1Het 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.
temp7Daar gaat ie (zie tabblad Thermometer in het Voorbeeldbestand):

  1. 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
  2. temp8in 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.
  3. 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.
  4. 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.
  5. temp2in 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.
  6. 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.
  7. 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??

Loterij

Excel, , , , , , , , ,

loterij2Vorige week kreeg ik de vraag, hoe je met Excel het makkelijkst de winnaars van een wedstrijd zou kunnen selecteren.
De bedoeling was om uit een (grote) hoeveelheid goede inzenders willekeurig drie personen er uit te lichten.

Een poosje geleden heb ik al iets geschreven over steekproeven; dezelfde systematiek is ook voor dit probleem toepasbaar.
Maar deze keer een iets andere benadering.

Loterij1

loterijIn het Voorbeeldbestand heb ik in het tabblad Loterij een overzicht opgenomen van alle verkochte loten, inclusief de corresponderende naam (in dit geval de lotnummers 1 t/m 100 en een fictieve naam, die daarvan is afgeleid; bijvoorbeeld bij het eerste lot hoort Naam 1).
NB in plaats van lotnummers kan dit overzicht ook alle goede inzenders van een wedstrijd voorstellen

loterij2Op het tabblad Uitslag worden 3 winnaars geselecteerd:

  1. Allereerst tellen we het aantal verkochte loten (of dus het aantal goede inzenders). In cel C2 staat daartoe de formule:
    =AANTAL(Loterij!B:B)
    Ofwel tel het aantal getallen in kolom B van het tabblad Loterij.
    LET OP zijn in kolom B geen nummers opgenomen maar teksten (bijvoorbeeld A1, A2, B1 etc) dan moet u de formule AANTALARG gebruiken en van het resultaat 1 aftrekken, omdat dan ook het woord LotNr in cel B2 wordt meegeteld
  2. uit het aantal verkochte loten worden dan 3 willekeurige getallen getrokken (cellen C5, C6 en C7):
    =ASELECTTUSSEN(1;$C$2)
    Dus neem een willekeurig getal tussen 1 en de waarde in C2 (in het voorbeeld dus 100); de grenzen doen ook mee.
  3. in cel D5 zoeken we dan de corresponderende naam op:
    =VERT.ZOEKEN(C5;Loterij!B:C;2)
    Deze formule zoekt de waarde uit cel C5 op in kolom B van Loterij en geeft als resultaat de corresponderende cel uit kolom C.
    LET OP bovenstaande formule werkt alleen goed, als de lotnummers in volgorde in kolom B staan en er geen “gaten” zijn. Beter is om de formule
    =VERT.ZOEKEN(C5;Loterij!B:C;2;ONWAAR) te gebruiken; de laatste parameter zorgt er voor, dat Excel naar een exacte match gaat zoeken.
  4. de formule in D5 kan naar beneden gekopieerd worden
  5. bij iedere wijziging in de werkmap worden de Aselect-formules opnieuw berekend; dus iedere keer zullen er andere winnaars tevoorschijn komen. Dit gebeurt ook door op de functietoets F9 te drukken: herberekenen.
    Belangrijk is om van tevoren duidelijk met de “notaris” af te spreken hoe vaak er herberekend zal worden, voordat de definitieve uitslag wordt bepaald.

LET OP met bovenstaande methode is het mogelijk dat prijswinnaar 2 en/of 3 gelijk is aan prijswinnaar 1. Dat is natuurlijk niet de bedoeling. Druk dan nog een keer op F9.

NB worden er aan de lijst in het tabblad Loterij nummers en namen toegevoegd of worden er verwijderd, dan zullen de resultaten in Uitslag direct daaraan worden aangepast; we kijken immers naar alle rijen in de kolommen B en C.

Loterij2

Het Voorbeeldbestand bevat ook een tabblad Loterij2; deze is vergelijkbaar met de eerste, maar is in de vorm van een Excel-tabel opgevoerd. Een groot voordeel hiervan is dat we niet alle cellen uit de kolommen B en C hoeven mee te nemen in de formules: wanneer de tabel wordt uitgebreid of verkleind dan zullen alle corresponderende formules zich automatisch daaraan aanpassen.

loterij3De formule in C2 ziet er anders uit:
=AANTAL(LoterijOvz2[LotNr])
Tel het aantal nummers in de kolom met als kopje LotNr uit de tabel LoterijOvz2 (de tabel uit het tabblad Loterij2).

De rest van kolom C is hetzelfde. Maar in kolom D gebruiken we geen VERT.ZOEKEN maar de functie INDEX. In cel D5 komt dan de formule =INDEX(LoterijOvz2[Naam];C5).
Haal in de kolom met als kopje Naam uit de tabel LoterijOvz2 de waarde op in die regel, die overeenkomt  met de waarde in C5.

NB aanpassingen aan de tabel in Loterij2 worden automatisch meegenomen in de resultaten.

LET OP ook hier kan het nodig zijn om een keer extra op F9 te drukken om geen dubbele prijswinnaars te krijgen.

Loterij3

loterij4In het tabblad Loterij3 van het Voorbeeldbestand is in de Excel-tabel een extra kolom opgenomen, waarin iedere regel van een willekeurig getal tussen 0 en 1 wordt voorzien dmv de formule =ASELECT().
De kans, dat hier dubbele getallen in voorkomen, is heel erg klein.

NB deze functie kent geen parameters, maar, zoals achter iedere functie, dienen er wel 2 haakjes te staan (openen en sluiten).

loterij5De bepaling van de winnaars gaat nu iets anders: in cel C3 wordt de eerste winnaar bepaald door het grootste getal (MAX) in de kolom Aselect van de tabel LoterijOvz3 op te zoeken. In D3 wordt met VERT.ZOEKEN het corresponderende lotnummer gevonden en in E3 met INDEX de naam (zoals uit de formules van kolom F blijkt kunnen we dat laatste ook met VERT.ZOEKEN in de hele tabel LoterijOvz3).

Maar hoe vinden we nu de 2e- en 3e- prijswinnaars? Dan kunnen we niet meer MAX gebruiken.
In cel C4 staat dan ook een andere formule: =GROOTSTE(LoterijOvz3[Aselect];2)
ofwel zoek de tweede in grootte in  de kolom Aselect van de tabel LoterijOvz3.
U begrijpt: in plaats van MAX in cel C3 hadden we ook de functie GROOTSTE met een parameter 1 kunnen gebruiken!

NB uiteraard hadden we in dit geval de winnaars ook kunnen selecteren met de functie KLEINSTE.

Dynamische grafieken

Excel, , , ,

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. DynGraf1

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:

  1.  cel D1 krijgt dan de waarde 6
  2. 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)
  3. idem voor cel E4: =ALS(B4<=$D$1;C4;””)
  4. D4 en E4 kopiëren we naar beneden
  5. 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
  6. 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: DynGraf2 en druk op Enter
  7. 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. DynGraf3Een voorbeeld:

  1. kies in het Voorbeeldbestand het tabblad DynGraf1
  2.  in cel H4 zetten we de formule =VERSCHUIVING(C4;2;0)
  3. het resultaat is 120; de functie geeft de inhoud van de cel 2 rijen onder en 0 kolommen rechts van C4 (dus C6)
  4. zo geeft de formule =VERSCHUIVING(C4;1;2) de waarde 110 (namelijk de inhoud van cel E5)
  5.  =VERSCHUIVING(E3;3;-2) werkt ook!! Resultaat is 120; de inhoud van cel C6.
  6. =VERSCHUIVING(D3;D1;0) en =VERSCHUIVING(D3;D1;1) leveren de maand en het bedrag afhankelijk van de waarde in cel D1
  7. 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: DynGraf4; dus wel degelijk de inhoud van drie cellen E4, E5 en E6
    * druk daarna niet op Enter maar op Esc
  8. 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

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

  1. kies opnieuw in het Voorbeeldbestand het tabblad DynGraf1
  2. scrol naar beneden zodat rij 70 in beeld is
  3. hier staat nog een grafiek die afhankelijk is van de waarde in cel D1; maar deze heeft geen “lege” maanden
  4. 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 NieuwDynGraf6, 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
  5. 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)
  6. 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 !
  7. 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

DynGraf7In 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.

DynGraf8In 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. DynGraf9Daarna 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.