Tagarchief: AANTALLEN.ALS

Reviews

7 januari 2023Excel, VBA (Visual Basic for Applications)AANTALLEN.ALS, Geavanceerd filteren, Gemiddelde.als, Herhaling, Keuzerondje, Notatie, SymbolenGijs Verbruggen

Voorbeeldbestand: Reviews.xlsmDownloaden

Alle (on-line) bedrijven willen tegenwoordig van hun klanten graag een review ontvangen.
Dit is niet alleen interessant voor de marketingafdeling maar ook voor nieuwe kopers. Ikzelf kijk tenminste ook altijd gauw door de lijst reviews om te zien wat anderen van het product vinden.

Meestal kun je een product waarderen met 1-5 sterren.
De betrouwbaarheid van de gemiddelde score (zoals hierboven de 4,7) wordt natuurlijk steeds groter met het aantal reviews dat er is uitgebracht. Maar de kwaliteit van de score wordt ook bepaald door de spreiding van de uitgebrachte scores. Vandaar dat je meestal nog kunt doorklikken:

In dit artikel gaan we wat mogelijkheden binnen Excel langs waarmee we bij een review-overzicht snel en interactief op de details kunnen inzoomen (het idee en de cijfers zijn ontleend aan een artikel van Chandoo).

Basis-gegevens

In het Voorbeeldbestand in het tabblad Reviews ziet u een bestand met voor enkele top-deals van Bol.com een reviewer-code met de daarbij behorende FICTIEVE score.

Door middel van een draaitabel kunnen we snel een overzicht met gemiddeldes genereren:

Maar om makkelijk een overzicht van de onderliggende details te kunnen tonen gaan we een andere methode hanteren.

Samenvattend overzicht

In kolom C hebben we de namen van de aanbiedingen geplaatst, waar we de gemiddelde Rating van willen weten (zie het tabblad Ovz1 van het Voorbeeldbestand).

NB wil je snel alle items hebben, die in het basis-bestand voorkomen, dan liggen er 3 methodes voor de hand:

maak een draaitabel zoals hierboven en kopieer de namen naar de gewenste plaats
gebruik de optie Geavanceerd in het blok Sorteren en filteren van de menutab Gegevens:
en vul bij Kopiëren naar de gewenste plaats in
kopieer de kolom met de aanbiedingen en kies dan de optie Dubbele waarden verwijderen in het blok Hulpmiddelen voor gegevens van de menutab Gegevens.

In cel D3 staat de formule =GEMIDDELDE.ALS(tblReviews[Aanbieding];C3;tblReviews[Rating])
Ofwel bepaal het Gemiddelde van alle Ratings als de naam in C3 gelijk is aan een naam in de kolom Aanbieding van de Excel-tabel tblReviews.

In kolom E wordt het gemiddelde grafisch weergegeven. Daarbij worden 3 symbolen uit het font Wingdings2 gebruikt: (zie het tabblad Berekeningen).

De cellen met de symbolen hebben respectievelijk de namen crkVol, crkHalf en crkLeeg gekregen.

Cel E3 bevat de formule:
=HERHALING(crkVol;GEHEEL(D3+0,25))
&HERHALING(crkHalf;ALS(EN(REST(D3;1)>0,25;REST(D3;1)<0,75);1;0))
&HERHALING(crkLeeg;GEHEEL(5-(D3-0,25)))

Eerst wordt het aantal volle cirkels bepaald door het gehele deel van de waarde in D3 te nemen (door daar 0,25 bij op te tellen wordt een waarde van bijvoorbeeld 1,8 ook voorzien van 2 volle cirkels); de functie Herhaling genereert een tekst met het gewenste aantal volle cirkels.

Als de Rest van deling van D3 door 1 groter is dan 0,25 EN kleiner dan 0,75 dan zal de tweede Herhaling-functie een halfvolle cirkel opleveren, anders niets.

Als laatste wordt het aantal toe te voegen lege cirkels gegeneerd.

Door middel van het &-teken worden de drie teksten aan elkaar gekoppeld.

LET OP op deze manier wordt een tekst gemaakt die uit symbolen uit het font Wingdings2 bestaat. Om deze tekst in Excel zichtbaar te maken moeten de betreffende cellen in kolom E dan ook het lettertype Wingdings2 hebben.

Zoals hiervoor gezegd hebben we details over het aantal reviews en de spreiding in de scores nodig om de gemiddeldes op waarde te schatten.

Detail overzicht

In het tabblad Berekeningen van het Voorbeeldbestand vindt u een overzicht van de scores van een bepaalde aanbieding.
Afhankelijk van de waarde in cel C2 (met de naam aanbNr) wordt in cel D2 (met de naam aanbNm) de naam van de betreffende aanbieding opgehaald door middel van de functie Index (lstAanbiedingen is de lijst producten in kolom C van het tabblad Ovz1).

Cellen B1:B9 bevatten de getallen 1 t/m 5; alleen hebben die een Aangepaste notatie gekregen: “Score “0
Selecteer daartoe de cellen, druk op Ctrl-1 (Celeigenschappen) en kies het tabblad Getal:

In cel C5 staat de formule: =AANTALLEN.ALS(tblReviews[Aanbieding];aanbNm;tblReviews[Rating];B5)
Ofwel bepaal het aantal als de waarde in de kolom Aanbieding van de Excel-tabel tblReviews gelijk is aan aanbNm en de waarde in Rating gelijk is aan de waarde in B5 (het woord Score daar is opmaak!).
Cel C5 heeft ook weer een Aangepaste notatie (0) gekregen.

In cel C10 wordt de som bepaald van de cellen daarboven met als Aangepaste notatie 0 “reviews”. Dit levert het totaal aantal reviews voor de betreffende aanbieding. Dit totaal wordt in de cellen D5:D9 overgenomen. Dit hebben we nodig voor het maken van de bijbehorende grafiek.

Detail grafiek

Van het detail-overzicht kunnen we nu makkelijk een grafiek maken:

selecteer de cellen B4:D9 in het tabblad Berekeningen
kies in de menutab Invoegen in het blok Grafieken de optie Kolom- of staafdiagram
kies het eerste staafdiagram (Gegroepeerde staaf)

We hebben op deze manier een grafische weergave van de aantallen per score vergeleken met het totaal aantal reviews.

Deze grafiek is gekopieerd naar het tabblad Ovz1; zoals te zien is doet wat opmaak wonderen:

verwijder de horizontale as, de gridlijnen en de legenda
klik op Grafiektitel, plaats de cursor in de Formulebalk, tik in = en klik met de muis op cel C10 van het tabblad Berekeningen

klik ergens in de grafiek met de rechter muisknop en kies Gegevens selecteren. Verplaats Aantal naar beneden
klik rechts op één van de staven en kies de optie Gegevensreeks opmaken.
Zet de Overlapping van reeks op 100%.
klik rechts op één van de scores en kies As opmaken.
Zet het vinkje aan bij Categorieën in omgekeerde volgorde.
klik rechts op één van de grote staven en kies de optie Gegevenslabels toevoegen.
Klik rechts op één van de labels en kies de optie Gegevenslabels opmaken. Kies de optie Waarde uit cellen en selecteer de cellen C5:C9. Vink de optie Waarde uit.
pas de overige opmaak naar wens aan.

Interactief details tonen (1)

Nu moeten we nog een methode vinden om vanuit het review-overzicht de detail-grafiek te tonen. Op het tabblad Ovz1 van het Voorbeeldbestand hebben we gebruik gemaakt van Keuzerondjes:

kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen

en kies daar de optie Keuzerondje (Formulierbesturingselement).
NB zie je geen menutab Ontwikkelaars? Klik ergens in het menugedeelte met de rechter muisknop en kies de optie Het lint aanpassen. Zorg dan dat in het rechterblok de optie Ontwikkelaars is aangevinkt.
klik in de cel waar je het eerste rondje wilt hebben. Verschuif het rondje eventueel nog zodat hij precies op de gewenste plaats staat.
pas de standaard-tekst Keuzerondje 1 aan; in dit geval wissen we de tekst helemaal.
klik rechts op het rondje en kies de optie Besturingselement opmaken. Zorg dat bij Koppeling met cel een verwijzing naar de cel Berekeningen!$C$2 komt.
klik rechts op het rondje en kies Kopiëren
toets 8 keer Ctrl-V (plakken) in
plaats het onderste rondje op de juiste plaats
selecteer alle rondjes door er op te klikken met Ctrl ingedrukt
kies in de menutab Hulpmiddelen voor tekenen/Opmaak in het blok Rangschikken de optie Uitlijnen en kies daarna Links uitlijnen; daarna nogmaals Uitlijnen met de optie Verticaal verdelen.

Wanneer je nu een rondje aanklikt zal het overeenkomende nummer in cel C2 van het tabblad Berekeningen worden geplaatst. Het detail-overzicht en de bijbehorende grafiek(en) passen zich dan automatisch aan.

NB aan de cellen C3:E11 is een voorwaardelijke opmaak gekoppeld die er voor zorgt dat de achtergrond van de gekozen aanbieding wordt gekleurd:

Interactief details tonen (2)

Een andere methode is in het tabblad Ovz2 van het Voorbeeldbestand geïmplementeerd.

Als je daar op één van de cellen C3:E11 klikt dan start een VBA-routine die er voor zorgt dat cel C2 (met de naam aanbNr) van het tabblad Berekeningen wordt aangepast.

klik met de rechter muisknop op de naam van één van de tabbladen en kies de optie Programmacode weergeven
klik in het Project-overzicht aan de linkerkant van het scherm dubbel op het blad Ovz2
plaats in het codescherm de volgende programma-regels:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim topRij As Integer
     If Not (Application.Intersect(Target, Range(“lstOvz2”).Cells) Is Nothing) Then
          topRij = Range(“lstOvz2”).Cells(1, 1).Row
          [aanbNr] = Target.Row() – topRij + 1
     End If
End Sub
Kortweg: als in dit werkblad de selectie wordt veranderd (door bijvoorbeeld het klikken met de muis of het gebruik van de cursortoetsen) dan zal de VBA-routine kijken of er een overlap is tussen de cel die is geselecteerd (de Target) en de cellen van lstOvz2 (dus C3:E11). Is dat zo dan wordt het rijnummer van de eerste cel van deze lijst in de variabele topRij geplaatst. Door deze topRij af te trekken van het rijnummer van de Target-cel weten we welke aanbieding aangeklikt is (nog wel even corrigeren met +1).

Kindernamen ofwel Sorteren in draaitabel

6 augustus 2020ExcelAANTALLEN.ALS, Draaitabel, lengte, Links, SOMMEN.ALS, Sorteren, Top-10Gijs Verbruggen

Voorbeeldbestand: Kindernamen.xlsx Download

Bij een analyse van de populariteit van diverse kindernamen gebruikte ik (uiteraard) draaitabellen. Bij het sorteren realiseerde ik me weer dat daar bij een draaitabel wel wat haken en ogen aan zitten, maar dat deze optie ook extra mogelijkheden biedt.
Een mooie aanleiding voor een nieuw artikel.

Brongegevens

Gelukkig hoeven we voor een overzicht van namen, die in de loop van de jaren aan kinderen zijn gegeven, niet zelf op onderzoek uit; al jaren publiceert het SVB deze.
Op de website svbkindernamen.nl kunnen we de namen van de laatste 5 jaar terugvinden.

NB om te vermijden dat namen naar personen kunnen worden herleid, publiceert het SVB namen, die minder dan 25 keer voorkomen, niet.

Met wat kopiëren en plakken kunnen we de overzichten van de site snel overhevelen naar Excel.
Het tabblad Data van het Voorbeeldbestand bevat alle namen van de laatste 5 jaar met het aantal keren dat ze toegekend zijn en de rangorde in het betreffende jaar; de gegevens zijn aangevuld met 2 extra kolommen: het jaar en of het een jongens- of meisjesnaam is.

De gegevens staan in een Excel-tabel tblData. Zoals te zien is, zijn nog enkele hulpkolommen en -cellen toegevoegd:

in cel C3 (met de naam MaxJr) wordt het hoogste jaar opgehaald.
Tik in cel C3 in: =max( en wijs dan met de muis de bovenrand van cel B6 aan (het muisteken wordt een pijltje naar beneden) en geef een muisklik. Excel vult zelf de formule aan: =max(tblData[Jaar]
Druk op Enter.
op een vergelijkbare manier bevat cel C4 (met de naam MinJr) het laagste jaar
in de nieuwe kolom Lengte wordt het aantal letters van de naam bepaald:
=LENGTE([@Voornaam])
De functie Lengte berekent hier de lengte van de naam in de kolom Voornaam in deze tabel (aangeduid door de rechte haken) en wel de cel in de overeenkomende rij (aangeduid door de @).
NB je hoeft dit soort notaties niet te onthouden; klik bij het invoeren van de formule op de gewenste cel en Excel vult alles automatisch in.
de beginletter van de naam vinden we met de volgende formule:
=LINKS([@Voornaam])
Om te zorgen dat Ömer ook onder de O komt is de gebruikte formule iets uitgebreid:
=ALS(LINKS([@Voornaam])=”Ö”; “O”;LINKS([@Voornaam]))
in de kolom daarnaast bepalen we of de naam vaker voorkomt dan het jaar daarvoor:
=ALS([@Jaar]=MinJr;”-“;
     ALS([@Aantal] >
          SOMMEN.ALS([Aantal];
               [Jaar];[@Jaar]-1;[JM];[@JM];[Voornaam];[@Voornaam]
          );
     “J”;”N”))
De eerste Als zorgt er voor, dat als het om een naam uit het eerste jaar gaat (MinJr), de aanduiding een streepje wordt (we weten niet of die in populariteit gestegen is of gedaald).
Anders: als het Aantal uit de betreffende regel groter is dan ‘iets anders’ dan wordt het resultaat gelijk aan J, anders N.
Maar wat is dat ‘iets anders’? Met de functie Sommen.als tellen we alle (geen @) gegevens op uit de kolom Aantal, die voldoen aan de voorwaardes daarna: het Jaar moet gelijk zijn aan het jaar uit de huidige regel minus 1, de code JM moet gelijk zijn aan die uit de huidige regel én de Voornaam moet overeenkomen.
NB als de bron-data consistent zijn dan levert deze exercitie maar 1 resultaat op: het aantal keren dat de naam een vorig jaar voorkwam.
om straks te kunnen zien of een naam in de loop van de jaren alleen maar populairder wordt ziet u nog een kolom SteedsStijgend met de formule:
=AANTALLEN.ALS(
     [JM];[@JM];
     [Voornaam];[@Voornaam];
     [StijgendJN];”J”
     )
     =MaxJr – MinJr
Op een vergelijkbare manier als hiervoor met Sommen.als bepalen we met behulp van Aantallen.als eerst het aantal keren dat bij een naam de codering StijgendJN gelijk is aan J.
Daarna vergelijken we of dit aantal gelijk is aan MaxJr minus MinJr; als dat zo is, dan wordt het resultaat WAAR, anders ONWAAR.
NB door het gebruik van de cellen MaxJr en MinJr hoeft er aan ons ‘analyse-systeem’ niets meer gewijzigd te worden wanneer er gegevens van andere jaren worden toegevoegd.

Jaar-overzicht

Allereerst maken we een overzicht van totalen per jaar (zie het tabblad JrOverz# van het Voorbeeldbestand).
De eerste conclusie zou kunnen zijn, dat er per jaar ruim 110.000 kinderen worden geboren en dat er blijkbaar per jaar meer jongens bij komen dan meisjes.

Deze getallen toch maar eens checken. Via Statline van het CBS zien we dat het aantal geboren kinderen in deze jaren ongeveer 170.000 is geweest en dat er ongeveer 5% meer jongens dan meisjes worden geboren:

NB wereldwijd worden er zelfs 7% meer jongens dan meisjes geboren. Volgens diverse bronnen zou uit onderzoek blijken, dat vrouwelijke embryo’s een net iets grotere kans hebben om te overlijden in de buik. Bij de bevruchting zou de verhouding wel degelijk 50/50 zijn.

Dat de totalen in het SVB-overzicht lager zijn is te wijten aan het uitsluiten van namen die minder dan 25 keer per jaar voorkomen. De verhouding jongens-meisjes volgens het SVB ligt nog verder scheef; blijkbaar worden voor meisjes vaker dan voor jongens originele namen bedacht!

Wanneer we dit overzicht beperken door de beginletter van de naam als filter te gebruiken, kunnen we diverse opvallende ontdekkingen doen.
Bijvoorbeeld: er zijn 3x zoveel meer jongens- dan meisjesnamen met een B in gebruik (tenminste namen die vaker dan 25 keer voor komen). Wanneer we daar de aantallen voor corrigeren, dan is de verhouding jongens t.o.v. meisjes binnen deze categorie iets groter dan over de hele populatie.

Bij deze draaitabel is de sortering nog rechttoe rechtaan: bij het aanmaken van de draaitabel heeft Excel de rijen en kolommen oplopend gesorteerd. Wil je de sortering anders hebben:

klik op een rij- of kolomnaam
klik op de menutab Gegevens
klik op of
of klik rechts op een rij- of kolomnaam en kies binnen de menu-optie Sorteren de gewenste volgorde:

Top-10

Laten we eens een Top-10 maken (zie ook het artikel Top-5):

klik ergens in het tabblad Data van het Voorbeeldbestand op een cel in de tabel tblData
kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK
plaats het Jaar in de Kolommen, Voornaam in de Rijen en Aantal en Populariteit in het Waarden-gebied

klik rechts op één van de voornamen in de draaitabel en kies de optie Filteren en daarna de optie Top-tien.
Zorg dat de volgende instelling is geselecteerd en sluit af via OK:
de namen staan nu nog in alfabetische volgorde; we willen ze natuurlijk van hoog naar laag van het aantal keren dat ze gebruikt zijn:
klik rechts op één van de voornamen in de draaitabel en kies de optie Sorteren en daarna Meer sorteeropties. Vul het vervolgscherm als volgt in en klik op OK:

Om een top-10 van jongens óf meisjes en/of andere deelselecties te maken zijn in het tabblad Top10 van het Voorbeeldbestand de velden JM, BegLetter en Lengte in het Filters-gebied geplaatst:

In het overzicht dat we op deze manier hebben gemaakt, staan de namen in de volgorde van het Eindtotaal. Wil je bijvoorbeeld de volgorde van 2019 zien, doe dan het volgende:

klik rechts op één van de voornamen en kies de opties Sorteren en dan Meer sorteeropties
kies op het vervolgscherm Meer opties
vul het volgende scherm als volgt in:

LET OP op deze manier is de sortering wel aan te passen aan het gewenste jaar. De namen van de top-10 veranderen daardoor niet. De filtering wordt door Excel altijd op basis van het Eindtotaal gemaakt.

Top-10 per jaar

Een echte top-10 per jaar kunnen we alleen maken door ook het Jaar in het Filters-gebied van de draaitabel te plaatsen.

Zie het tabblad JrTop10 van het Voorbeeldbestand.

Wat opvalt: korte, krachtige namen zijn erg in trek!

Tendens over de jaren

Bij een ‘analyse’ mag een verloop in de tijd niet ontbreken (zie het tabblad JrTendens in het Voorbeeldbestand):

maak een draaitabel op basis van de brongegevens
plaats het Jaar in de Kolommen, de Voornaam in de Rijen en 2x het Aantal in het Waarden-gebied
om een deelselectie te kunnen maken plaatsen we JM, BegLetter en Lengte in het Filters-gebied
klik rechts op een van de getallen in de eerste Aantal-kolom, pas de naam aan (StDal)
kies Waarden weergeven als en vul het vervolgscherm in:

Wanneer we nu het overzicht voor jongens maken, valt direct een vreemde sortering van de namen op.

Alle namen staan netjes alfabetisch op de eerste 2 na!
De reden daarvoor zag ik zo gauw niet, tot ik me ineens realiseerde dat Excel Jan en Sep waarschijnlijk als maandaanduiding ziet. Wat blijkt: Excel gaat bij een sortering in een draaitabel niet zonder meer uit van een standaard ‘alfabetische’ sortering maar gaat ook op zoek naar Aangepaste lijsten (zie Doorvoeren en sorteren). Als er gegevens zijn die aan een Aangepaste lijst voldoen, dan worden deze gegevens eerst gesorteerd, daarna komen de anderen in alfabetische volgorde.

Als je het probleem onderkent, is de oplossing vaak dichtbij.
Bij een sortering buiten een draaitabel kun je op de grote button Sorteren klikken en krijg je een vervolgscherm waarin je kunt aangeven op welke manier er gesorteerd moet worden:

Standaard staat de sortering op A-Z, maar die kun je wijzigen. Waaronder de optie om een Aangepaste lijst te gebruiken.
Bij een draaitabel werkt dit echter niet. Via de volgende weg kun je dit oplossen:

klik rechts op een van de namen en kies de optie Sorteren en dan Meer sorteeropties
klik op de button Meer opties
zet de optie Automatisch sorteren in het vervolgscherm uit en kies als Sorteervolgorde: Geen berekening

Bij Sorteervolgorde zit nu de optie verstopt of je wel of niet een Aangepaste lijst wilt gebruiken!

NB aan het veld StDal is een Voorwaardelijke opmaak toegevoegd, zodat de tendens in de loop van de jaren sneller duidelijk is.

Steeds populairdere namen

Wanneer we een overzicht willen hebben van alle namen die in de loop der jaren steeds populairder worden, maken we een draaitabel waaraan we als filter ook het veld SteedsStijgend toevoegen (zie het tabblad JrStijgend van het Voorbeeldbestand).

De naam Fos zal in 2015 ook wel zijn voorgekomen, maar het aantal zal lager dan 25 zijn geweest; door het SVB worden die in het kader van de privacy weggelaten.

Selectie

Ben je op zoek naar een handig hulpmiddel om een naam te vinden voor een kind (populair of juist niet, beginnend met een bepaalde letter, een korte of lange naam)?
Het tabblad Selectie van het Voorbeeldbestand bevat een draaitabel met slicers waarmee een keuze maken heel eenvoudig is (?):

NB de inhoud van de slicers past zich automatisch aan aan keuzes die in andere slicers zijn gemaakt. Door als beginletter G te kiezen bij de jongens zijn er alleen nog namen met 4 tot 8 letters over.

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??