Tagarchief: Tabel

Deze week las ik een artikel over dynamische draaitabellen gemaakt met PowerPivot (een invoegtoepassing waarmee u krachtige gegevensanalyses in Excel kunt uitvoeren).
De draaitabel liet een overzicht zien, gesplitst naar landen. Werd er echter (met behulp van een slicer) één land geselecteerd, dan veranderde de draaitabel automatisch in een overzicht, gesplitst naar steden.
In PowerPivot kunnen dit soort zaken geïmplementeerd worden met behulp van een eigen programmeertaal.

De uitdaging was om te kijken of dit ook ‘gewoon’ in Excel geregeld kan worden. Ik dacht de verkiezingsuitslagen van vorige week mooi als voorbeeld te kunnen gebruiken (overzicht per provincie verandert automatisch in een overzicht per gemeente), maar helaas . Omdat de uitslagen van 2017 nog niet (op detail-niveau) bekend zijn heb ik de data van 2012 gebruikt. Met een paar kleine aanpassingen kunnen de resultaten van 2017 ingekopieerd worden en kunnen er dan ook vergelijkingen tussen de jaren gemaakt worden. Dat doen we later nog een keer; beloofd is beloofd!

Basis

De gegevens heb ik kunnen downloaden van www.verkiezingsuitslagen.nl in de vorm van een CSV-bestand (Comma Seperated Values). Zie het tabblad Uitslagen 2e Kamer 2012 in het Voorbeeldbestand.

Zoals te zien is, krijg je een tekstbestand in Excel, dat nog wat aanpassingen verdient:

allereerst moeten de teksten over verschillende kolommen verdeeld worden:
* selecteer alle cellen in kolom A, die omgezet moeten worden (A1 t/mA443)
* kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Tekst naar kolommen
* aangezien de kolommen geen standaardbreedte hebben maar door een komma van elkaar zijn gescheiden, kiezen we in het vervolgscherm de optie Gescheiden en klikken op Volgende
* zorg dat in het volgende scherm de optie Komma staat aangevinkt en klik op Voltooien
even wat regels over de totalen per partij verwijderen (de eerste 22), de gegevens in de vorm van een tabel gieten (kies in de menutab Invoegen de optie Tabel) en de onderste (dreigende) regels verwijderen.
de kopregel aanpassen, zodat de breedte van de kolommen wat beperkt blijft (via Ctrl-1/Uitlijning)
even sorteren op CBS-code
dan blijken veel plaatsen al per provincie bij elkaar te staan: een kolom Provincie toevoegen, aan ieder blok een Provincienaam toevoegen en dan ‘met de hand’ nog wat correcties doorvoeren (wat de systematiek van het CBS voor de codes is, is me niet helemaal duidelijk!)
zo krijgen we een overzicht van de stemmen per gemeente (zie tabblad Uitslagen in het Voorbeeldbestand)

Verkiezingsuitslagen

In dit artikel gaan we alleen wat doen met de partij-uitslagen, dus allerlei kolommen over geldige stemmen enzovoorts kunnen weg.

Om de overzichten zo flexibel mogelijk te kunnen maken moet de bron nog in een zogenaamde database-structuur worden gegoten: niet alle partijen naast elkaar, maar per plaats een aparte regel voor iedere partij met daarbij het aantal stemmen.
Op deze manier ontstaat een basis-bestand met 8.778 regels; voor een mens niet zo handig maar voor Excel geen punt!
Zie het tabblad Basis in het Voorbeeldbestand.

Overzicht per provincie

Allereerst gaan we een overzicht per provincie maken:

selecteer een cel in het basis-bestand
kies Invoegen/Draaitabel en klik OK (Excel kiest automatisch als bron de totale tabel tblBasis)
op het nieuwe werkblad gaan we de draaitabel vormgeven:
* sleep het veld Partij naar Kolomlabels
* sleep het veld Provincie naar Rijlabels
* sleep Aantal naar het Waarde-gebied
* sleep nogmaals Aantal naar het Waarde-gebied
* zorg dat Waarden in de Rijlabels staat
* klik rechts op een van de cellen in een regel met Som van Aantal2, kies Waardeveldinstellingen en dan bij Waarden weergeven als de optie % van rijtotaal
* wijzig één van de namen Som van Aantal2 in Perc
en nog een sortering aanbrengen:
* klik met de muis rechts op één van de partijen
* kies Sorteren en dan de optie Meer sorteeropties
* in het vervolgvenster kiest u Aflopend en in het bijbehorende keuzeveld de optie Som van Aantal (of Perc; dat maakt in dit geval niets uit). Op deze manier zullen de partijen altijd gesorteerd worden van meeste stemmen naar minste; ook als de draaitabel van inhoud verandert.

Zo ontstaat het overzicht zoals weergegeven in het tabblad Ovz1 van het Voorbeeldbestand.

Overzicht per gemeente

Als we nu willen weten hoe bijvoorbeeld de uitslag van de provincie Groningen tot stand is gekomen, moeten we de draaitabel aanpassen (of een nieuwe maken):

maak een kopie van het tabblad Ovz1 (‘met de muis vastpakken’ en, met de Ctrl-toets ingedrukt, verslepen)
versleep het veld Provincie van Rijlabels naar het Rapportfilter
sleep het veld Plaats naar Rijlabels (boven Waarden!)
selecteer bij Provincie de keuze Groningen (in het tabblad Ovz2 is dat cel C2)

Dynamische draaitabel

Maar zou het niet mooi zijn, als de draaitabel eerst een overzicht van de provincies vertoont, maar dat deze automatisch de plaatsen laat zien als we slechts 1 provincie kiezen?

Om dit voor elkaar te krijgen, moeten we de volgende stappen doorlopen:

maak eerst een nieuwe draaitabel aan vergelijkbaar met hiervoor (zie tabblad Ovz3 in het Voorbeeldbestand)
cel E3 (waar een bepaalde provincie kan worden gekozen) heeft de naam Prov gekregen
aan het basisbestand is een kolom PrPl toegevoegd met de volgende formule:
=ALS(LINKS(Prov;4)=”(All”;[@Provincie];[@Plaats])
Dus als in E3 de optie alle provincies is gekozen, dan komt er in de toegevoegde kolom de Provincie te staan, anders de Plaats.
NB1 @Provincie betekent in een tabel: haal de inhoud op uit de kolom Provincie in dezelfde regel
NB2 we controleren of de eerste 4 karakters van de cel Prov gelijk is aan (All, omdat Excel soms (Alles), soms (Alle) en in de Engelstalige versie (All) laat zien
sleep nu het veld PrPl naar de Rijlabels van de draaitabel in Ovz3 (eerst even de draaitabel Vernieuwen, omdat anders de nieuwe kolom nog niet in de bron is opgenomen)
voeg nog een slicer toe voor de Provincie (zie het artikel Slicers in Excel)

Wanneer we nu ‘spelen’ met de opties in de cel Prov (of met de slicer) zien we, dat het systeem nog niet vlekkeloos werkt: bij het veranderen van 1 naar alle provincies of andersom moet de draaitabel eerst vernieuwd worden, voordat de gewenste resultaten zichtbaar zijn. Dit komt omdat dan wel de kolom PrPl in de bron wordt aangepast, maar de draaitabel nog de oorspronkelijke inhoud in het geheugen heeft.
Met VBA kunnen we dit automatiseren.

NB Vernieuwen van een draaitabel kan door ergens rechts te klikken in een draaitabel en dan Vernieuwen te kiezen of (als een cel van een draaitabel is geselecteerd) op Alt-F5 te drukken.

VBA

Met de volgende code is het actualiseren van de draaitabel te automatiseren:

Public ProvOud
Public Vernieuwen As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If [Prov] <> ProvOud And Not Vernieuwen Then
Vernieuwen = True
ActiveSheet.PivotTables(“Draaitabel3”).PivotCache.Refresh
End If
ProvOud = [Prov]
Vernieuwen = False

End Sub

Doe nu het volgende:

open (zonodig) het Voorbeeldbestand
klik met de rechter muisknop op het tabblad Ovz3
kies de optie Programmacode weergeven
zorg dat (links) in het projectenoverzicht het blad Ovz3 is geselecteerd
plak dan in het code-scherm de bovenstaande code
sluit de VBA-editor
Sla het bestand op als Excel-werkmap met macro’s; dus met de extensie xlsm
wissel nu tussen provincies afzonderlijk en alle provincies

Trend-analyse

9 februari 2017ExcelCSE, Draaigrafiek, Draaitabel, Kiezen, Lijnsch, R.Kwadraat, Richting, Slicer, Snijpunt, Tabel, Trendlijn, WeekdagGijs Verbruggen

Ik was deze week nog eens naar Google-analytics aan het kijken hoe het met de bezoekersaantallen van G-Info gaat.

Er zit nog steeds een stijgende lijn in; altijd leuk!

Wel wat vreemde uitschieters:

eind april/begin mei 2015 heeft Google problemen gehad en is er niets geturfd
juli en aug van ieder jaar vertoont een dip; waarschijnlijk hebben mensen dan wat anders te doen
ook bij de dips van eind december kan ik me wat voorstellen
bij nadere bestudering blijken alle punten aan de onderkant op zaterdag en zondag te vallen
en de pieken naar boven vallen samen met de momenten dat ik een Nieuwsbrief uitstuur met de aankondiging dat er een nieuw artikel op de site is verschenen.

Maar laten we eens kijken hoe we de trend kunnen analyseren.

Trendlijn-1

Op basis van de gegevens van Google heb ik bovenstaande grafiek gemaakt (je kunt de data uit Analytics eenvoudig exporteren naar Excel):

in het Voorbeeldbestand staan op het tabblad Gegevensset de aantallen bezoekers per dag (beter gezegd de aantallen sessies).
Daar heb ik ‘natuurlijk’ direct een Excel-tabel van gemaakt (zie het artikel over de 10 voordelen van een tabel en Tabellen deel 2). De naam daarvan is Tabel1.
selecteer de gegevens uit de 2 kolommen door de bovenkant van de kolommen aan te wijzen (de cursor is dan een zwart-pijltje naar beneden; de linker-muis-toets ingedrukt houden).
kies in de menubalk de optie Invoegen/Grafieken/Lijn
het resultaat vindt u terug in het tabblad Graf

In de voorbeeldgrafiek heb ik Excel ook een trendlijn laten toevoegen:

klik ergens met de rechtermuisknop op de grafiek en kies de optie Trendlijn toevoegen
laten we het simpel houden en voor een rechte lijn (de optie Lineair) kiezen
vink ook de 2 onderste opties aan: Vergelijking en R-kwadraat

Excel heeft een mooie (stijgende) lijn getekend; de wiskundige formule die daar bij hoort is
y = 0,0956x – 3998,4

Het getal voor de x is de richtingscoëfficiënt (RC): hoeveel verandert de lijn als x (in dit geval de dag) met 1 toeneemt (dus ruwweg iedere 10 dagen komt er 1 bezoeker meer).
Het andere getal (-3998,4; het snijpunt met de y-as) geeft het aantal bezoekers weer als x gelijk is aan 0 (nul).

NB1 Omdat op de x-as datums staan en voor Excel een datum niets anders is dan het aantal dagen na 1-1-1900, zou het aantal bezoekers op die dag-nul dus flink negatief zijn geweest. De lijn naar het verleden doortrekken is dus onzinnig.

NB2 voor de liefhebbers: Excel gebruikt voor het bepalen van de lijn de zogenaamde kleinste-kwadratenmethode.

NB3 R² (R-kwadraat) is de zogenaamde determinatiecoëfficiënt. Deze geeft aan welk gedeelte van de variatie in de ene variabele door de andere wordt verklaard.
Ofwel: hoe ´betrouwbaar´ is de trendlijn. Is de R² kleiner dan 0,5 dan is het verband zwak tot matig, ligt die tussen 0,5 en 0,75 dan is het verband sterk en anders zeer sterk.

Hoeveel bezoekers mag G-Info op basis van deze trendlijn over een jaar verwachten? Als iedere 10 dagen er 1 extra bezoek bij komt dan zouden er over 365 dagen ongeveer 36 meer moeten zijn dan nu; dus ipv gemiddeld 95 zouden er dat ongeveer 130 zijn.
Dit kunnen we ook makkelijk grafisch laten zien:

klik met de rechtermuisknop op de trendlijn in de grafiek
kies Trendlijn opmaken
en bij Voorspelling/Vooruit vullen we 365 in

Richting, Snijpunt, R.kwadraat en Lijnsch

Om in Excel met de trend te kunnen rekenen moeten we de richtingscoëfficiënt en het snijpunt met de y-as exact weten; we hebben niet genoeg aan een formule in de grafiek.

Uiteraard zijn daar functies voor (zie het tabblad Gegevensset in het Voorbeeldbestand):

in cel H2 willen we de RC plaatsen.
* tik in =Richting(
* klik op de bovenrand van Sessies in kolom C (de y-waarden)
* tik in ;
* klik op de bovenrand van Dagindex in kolom B (de x-waarden)
* druk op Enter (Excel zal automatisch de sluithaak van de formule toevoegen)
in H3 is op dezelfde manier de functie SNIJPUNT ingevoerd
en in H4 de functie R.KWADRAAT (denk aan de punt na de R)

Voor de liefhebbers: Excel kent nog een andere functie; deze levert meer statistische resultaten op, LIJNSCH.

In het kort (zie het tabblad Gegevensset in het Voorbeeldbestand):

selecteer 6 lege cellen, 2 kolommen en 3 regels (in het voorbeeld G12:H14)
in de eerste cel komt de volgende formule:
=LIJNSCH(Tabel1[Sessies];Tabel1[Dagindex];WAAR;WAAR)
De y- en x-waarden zijn de bekende kolommen uit de tabel met Google-data.
druk nu niet op Enter, maar Ctrl-Shift-Enter (de zogenaamde CSE-invoer)

De betekenis van de 6 cellen heb ik in de kolommen er naast aangegeven; de cellen G12:H14 hebben corresponderende namen gekregen, zodat formules die er naar verwijzen beter leesbaar zijn.

NB De 2 ‘boven’ de R krijg je als volgt: selecteer in de formulebalk de 2, klik Ctrl-1 (de sneltoets voor Celeigenschappen) en kies de optie Superscript. De m, b en y achter SD hebben de eigenschap Subscript gekregen.

LET OP Lijnsch is een zogenaamde array- of matrix-functie. Het resultaat bestaat niet uit 1 waarde maar uit meerdere, vandaar dat die met CSE wordt ingevoerd. Alle cellen bevatten dezelfde functie, omgeven door accolades.

NB Lijnsch levert meer dan 6 resultaten terug; probeer zelf maar uit door in de eerste stap meer kolommen en regels te selecteren.

De resultaten van bovenstaande functies kunnen we nu gebruiken om voorspellingen voor de toekomst te genereren (zie het tabblad Gegevensset in het Voorbeeldbestand).

Trendlijn-2

Omdat de bezoekersaantallen in de weekenden heel anders zijn dan op werkdagen, heb ik voor een nadere analyse op het tabblad Gegevensset in het Voorbeeldbestand nog een kolom toegevoegd:
=KIEZEN(WEEKDAG([@Dagindex];2);”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)

Ofwel: bepaal de Weekdag van de corresponderende datum in kolom B (Dagindex; we willen dat de week op maandag begint, vandaar de parameter 2). Aangezien deze functie volgnummers oplevert (een maandag is 1, dinsdag 2 etc), heb ik de nummers nog vertaald in teksten mbv de functie Kiezen.

Op basis van deze tabel heb ik een draaitabel gemaakt (zie het tabblad Draai in het Voorbeeldbestand).
Door het veld Weekdag in het Rapportfilter te plaatsen kunnen we snel een overzicht per afzonderlijke dag maken.
Om ook analyses per jaar, kwartaal of maand te kunnen maken heb ik de datums in het veld Dagindex gegroepeerd (zie Groeperen in een draaitabel).

Nog een paar stappen en we kunnen met de resultaten gaan ‘spelen’:

selecteer een cel in de draaitabel
in de menutab Hulpmiddelen voor draaitabellen kiezen we het tabblad Opties
klik dan op de optie Draaigrafiek in het blok Extra
kies een Lijngrafiek en klik OK
voeg een Trendlijn toe, inclusief Vergelijking en R-kwadraat

Na wat lay-outen en het toevoegen van 2 slicers (zie Slicers in Excel) krijgen we een resultaat zoals weergegeven in het tabblad DrGraf in het Voorbeeldbestand.

LET OP de b in de vergelijking van de trendlijn gebruikt voor x=0 niet meer de datum 1-1-1900, maar de eerste datum in de betreffende draaitabel.

Wanneer we in de Slicer Weekdag alleen de maandag kiezen (klikken op de button ma) dan zien we dat de R² al veel beter wordt (0,6381).

LET OP de m in de vergelijking van de trendlijn geeft nu niet de verandering per dag aan, maar de verandering naar de volgende maandag, dus na 1 week.
Wil je de trendlijn 1 jaar vooruit laten ‘kijken’, tik bij Voorspelling/Vooruit dan ook geen 365, maar 52 in (dus over 1 jaar 160 bezoekers op maandag?).

Wil je het resultaat over alle werkdagen zien:

kies in de Slicer Weekdag de button ma
houd Shift ingedrukt en klik op vr
laat Shift los
de draaitabel, draaigrafiek en trendlijn passen zich automatisch aan

Analyse van de jaren laat zien (gebruik de betreffende Slicer), dat de trendlijn voor 2015 ´betrouwbaarder´ is dan die van 2016. De resultaten van vorig jaar worden zwaar beïnvloed door de dips in vakantie-periodes.

Top2000 en draaitabellen

5 januari 2017ExcelDraaigrafiek, Draaitabel, Filter, Slicer, TabelGijs Verbruggen

Voorbeeld bijgewerkt t/m 2020: Top2000_2020 Download

Eind 2016 stond voor menigeen, op muziekgebied, weer in het teken van de jaarlijkse Top-2000; één van de (Nederlandse) tradities naast Kerstmis, Oud en Nieuw, oliebollen etc.

Bovenaan de ranglijst is het normaal gesproken niet echt spannend; de top-3 ligt wel ongeveer vast. De vraag was of de vele in 2016 gestorven pop-helden daar verandering in zou brengen. Niet dus.

Tijd om wat overzichten te maken; uiteraard gebruiken we daar draaitabellen voor!

Basis-materiaal

De Top-2000 wordt uitgezonden door Radio-2; op de website daarvan is dan ook altijd de rangschikking van het laatste jaar te vinden. Door nog wat verder te googelen heb ik ook de standen vanaf het begin van de Top-2000 kunnen vinden; de eerste uitzending was eind december 1999.

Wat heb ik vastgelegd? Per nummer het jaar van uitzenden, de notering/positie in dat jaar, de naam van de artiest/groep, de titel van het nummer en het jaar dat het nummer gepubliceerd/uitgebracht is (zie het tabblad Basis in het Voorbeeldbestand).

Alle gegevens heb ik in de vorm van een Excel-tabel vastgelegd met de naam tblTop2000 (heb je een aaneengesloten gebied met gevulde cellen, plaats de cursor ergens in dat gebied en kies in de menutab Invoegen de optie Tabel; kies dan in de nieuwe menutab Hulpmiddelen voor tabellen de optie Ontwerpen en wijzig de naam).
Eind 2017 zal de traditie wel worden voortgezet: kopieer de nieuwe gegevens en voeg deze onderaan de tabel toe. Nog even sorteren en alles staat klaar:

plaats de cursor ergens in de tabel
kies in de menutab Gegevens in de groep Sorteren en filteren de optie Sorteren
in het tussenscherm staan de juiste instellingen al klaar:
Klik dus op OK.

NB hebt u nieuwe data toegevoegd, vergeet dan niet de draaitabellen te Vernieuwen, anders worden de nieuwe gegevens niet meegenomen in de overzichten. Eén draaitabel vernieuwen is voldoende; aangezien alle overzichten aan dezelfde basis zijn gekoppeld worden dan alle draaitabellen bijgewerkt.

Filteren

Om de resultaten van een artiest te bekijken kunnen we de filter-eigenschappen van Excel gebruiken.
Aangezien het basis-materiaal als een tabel is opgeslagen krijgen we filter-opties er ‘gratis’ bij (zie de achter iedere kolom-kop).
Wanneer we bijvoorbeeld alle noteringen van de Eagles willen weten:

klik op het achter Artiest
zoals u ziet kunt u daar ook een sortering aanbrengen, maar we zijn nu meer geïnteresseerd in de filtermogelijkheden.
Excel heeft ontdekt dat de kolom tekst bevat, dus we krijgen tekstfilters aangeboden.
Onder het invulvak staan standaard alle mogelijkheden waaruit u kunt kiezen; u zou dus naar de Eagles kunnen scrollen (of eerst een E intikken en dan verder scrollen), maar dat is nogal tijdrovend. In plaats daarvan tikken we in het invulvak eagles (het zoeken is niet hoofdletter-gevoelig) en Excel geeft ons alleen die opties, die ergens het woord eagles bevatten.
Ik bedoelde niet de Eagles of Death Metal, dus die vinken we uit en klikken op OK
en we hebben het gewenste overzicht:

Eventueel nog sorteren op Titel om het verloop in de jaren per nummer makkelijk te kunnen volgen.

Overzicht per artiest

We gaan eens analyseren hoe de diverse artiesten het in de loop van de tijd hebben gedaan:

klik ergens in de tabel tblTop2000 in het tabblad Basis van het Voorbeeldbestand
kies in de menutab Invoegen de optie Draaitabel en klik op OK
sleep de velden UitzendJr, Artiest en Titel naar de gewenste plaats in de tabel zoals hiernaast weergegeven.
In de rijen zal Excel nu alle (unieke!) artiesten tonen en in de kolommen komen de (12) verschillende uitzendjaren.
In het waarden-gebied hebben we de Titel geplaatst; Excel weet dat dit een tekst is, dus zal direct het aantal voorkomens van een artiest in een jaar turven.
NB Hadden we een numeriek-veld in het waarden-gebied geplaatst dan bepaalt Excel standaard de som van die waarden.
kies in de nieuwe menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen en dan bij Rapportindeling de optie Tabelweergave
nog even een sortering aanpassen: kies het ‘vinkje’ achter UitzendJr en sorteer van hoog naar laag

Het resultaat is terug te vinden in het tabblad ovzArtiest van het Voorbeeldbestand:

Wil je weten welke 3 nummers van 10CC er in 2016 in de lijst stonden en op welke plaats: dubbelklik op de 3 in cel B5 en er verschijnt een nieuw tabblad met de drie betreffende records.
Dubbelklikken op de 101 zal alle noteringen van 10CC in de loop der jaren laten zien.

Welke artiest/groep heeft de meeste noteringen?

klik ergens in de 2016-kolom, bijvoorbeeld cel B5
kies in de menutab Gegevens in het blok Sorteren en filteren de optie .
wil je de stand in een ander jaar weten? Herhaal de procedure met de cursor in een andere kolom.
Hetzelfde als u de stand over alle jaren wilt weten: plaats de cursor in de laatste kolom en sorteer.

Overzicht per artiest en titel

Met nog een paar aanpassingen aan de draaitabel ontstaat een totaaloverzicht van de hele Top-2000 over alle jaren:

Voeg nogmaals de Titel toe aan de Lijst met draaitabelvelden; deze keer in het Rijlabels-gebied.
Let op zorg dat de titel ONDER de artiest komt! Probeer maar uit wat het verschil is.
in het waarden-gebied halen we de Titel weg (versleep buiten het keuzegebied) en plaatsen daar de Notering.
Aangezien dit een numeriek veld is stelt Excel voor om dan de Som daarvan te pakken. Aangezien iedere artiest/titel-combinatie in ieder uitzendjaar maar één keer voorkomt, zal Excel dus ‘gewoon’ de notering laten zien.

Een voorbeeld kunt u vinden in het tabblad ovzArtTitel van het Voorbeeldbestand.

NB uit het voorbeeld hier rechts mag blijken dat in het basis-materiaal de titels niet consistent gevuld zijn. Ook bij de artiesten was dit het geval; deze heb ik handmatig zo goed mogelijk ‘opgeschoond’.

Noteringen in de loop van de tijd

In het tabblad ovzJrVerloop van het Voorbeeldbestand ziet u een andere mogelijkheid om het verloop in de tijd te volgen:
De Artiest hebben we in het Rapportfilter geplaatst.

De titel heeft een ‘vreemde’ sortering gekregen:

plaats de cursor in één van de titels
klik in die cel met de rechter-muisknop en kies binnen de optie Sorteren het item Meer sorteeropties
in het vervolgscherm kiezen we dan een oplopende sortering; maar niet op Titel maar op Som van Notering

NB1 omdat Excel naar het totaal over alle jaren heen kijkt, is de sortering niet vlekkeloos.

NB2 Ook door de inconsistente vulling van de database levert dit niet altijd het verwachte/gewenste resultaat op; kijk bijvoorbeeld wat er gebeurt als je ABBA kiest.

Grafiek

Wanneer we van de draaitabel een grafiek maken levert dit nog meer inzicht op in het verloop van de noteringen in de tijd (zie tabblad ovzJrVerloopGraf):

klik ergens in de draaitabel
kies in de menutab Hulpmiddelen voor draaitabellen de tab Opties
in het blokje Extra vindt u de Draaigrafiek
na de keuze van het gewenste type komt de grafiek op hetzelfde tabblad waar ook de draaitabel staat; kies en zet de grafiek op een Nieuw blad
aangezien de grafiek aan de draaitabel gekoppeld is kun je hier direct een andere artiest-keuze maken. Deze keuze wordt dan direct ook in de gekoppelde draaitabel toegepast, inclusief sorteringen.

Publicatiejaar

Ik was ook geïnteresseerd naar het ‘nostalgische’ karakter van de Top2000; ofwel uit welke jaren komen de meeste nummers?

Uit de grafiek over het uitzendjaar 1999 blijkt dat toen het zwaartepunt lag bij muziek uit de eind-60- en eind-70-jaren.
Wanneer we dit vergelijken met de grafiek over 2016 dan zien we dat er een veel gelijkmatigere verdeling over de tijd is! De Top-2000 is niet alleen meer voor ‘ouwe knarren’?

Hoe is deze grafiek gemaakt?

de basis is weer een draaitabel.
In het Rapportfilter is het UitzendJr geplaatst, waarmee we de hoofdselectie maken.
Het PublJr plaatsen we in de rijen en we turven het aantal door de Titel in het waarden-gebied te laten ‘vallen’.
daarna is van deze draaitabel een draaigrafiek gemaakt; klaar!

In het tabblad ovzPublJr van het Voorbeeldbestand is het resultaat terug te vinden.

Slicers

Om de keuze van het uitzendjaar makkelijker te kunnen maken is een slicer toegevoegd aan de draaitabel:

klik ergens in de draaitabel
kies in de menutab Hulpmiddelen voor draaitabellen de tab Opties
kies in het blokje Sorteren en filteren de optie Slicer invoegen
in het vervolgscherm kiezen we in dit geval de optie UitzendJr
regel nog wat opmaak via de Opties in Hulpmiddelen voor Slicers

Bel-grafiek of -diagram

14 september 2016Excel, VBA (Visual Basic for Applications)Beldiagram, Belgrafiek, Kleurcodes, RGB, Tabel, VERT.ZOEKENGijs Verbruggen

In het artikel Eerbetoon aan Rosling is gebruik gemaakt van zogenaamde bel-diagrammen, ook vaak bel-grafiek genoemd.
Met behulp van dit soort grafieken is het mogelijk om 3 dimensies weer te geven: via de x- en y-as en door de grootte van de bellen.

Het artikel was aanleiding voor een vraag, die er op neer kwam of er ook nog een vierde dimensie mogelijk was door de kleur van de bellen te variëren.
Met de hand is dit mogelijk maar er is geen standaard-optie, die bij wijzigingen in de sheet de kleuren automatisch zal aanpassen (wat wel gebeurt met de grootte van de bellen).

Kleuren in Excel

Voordat we verder gaan met een mogelijke oplossing voor het probleem van Sander moeten we eerst iets meer weten over de codering van de kleuren in Excel.

Alle kleuren worden opgebouwd door een combinatie van Rood, Groen en Blauw (de zogenaamde RGB-codering). Ieder van die 3 basis-kleuren kan in 256 stappen worden toegevoegd (lopend van 0 tot 255).
Zwart is dan opgebouwd uit 0 delen van iedere kleur, terwijl wit ontstaat door het mengen van alle drie de kleuren met een intensiteit van 255. Door alleen Rood en Groen te mengen (met een intensiteit van 255) ontstaat geel.

Door de blauw-code met 65536 te vermenigvuldigen, de groen-code met 256 en deze 2 dan op te tellen met de rood-code krijgen we een decimale kleurcode.
In het schema hierboven en op het tabblad Param van het Voorbeeldbestand staan enkele voorbeelden.

De decimale codes zullen we hierna gaan gebruiken.

Probleem

Als je een bellengrafiek maakt krijgen de bellen standaard allemaal dezelfde kleur. Deze kunnen gewijzigd worden door één voor één de bellen te selecteren (klik op een bel, dan wordt de hele serie geselecteerd; klik nogmaals op de bel dan wordt de selectie beperkt tot de betreffende bel) en dan via rechts-klikken de opmaak aan te passen.

Maar de vraag is: kan dit geautomatiseerd, waarbij de kleur afhankelijk is van de inhoud van bepaalde Excel-cellen?

Hieronder volgt een oplossing waarbij gebruik wordt gemaakt van VBA; een zelf-geschreven routine controleert van iedere bel wat de corresponderende kleurcode moet zijn en past die aan. Wel moet de routine na iedere wijziging handmatig aangeroepen worden; daar hebben we dan weer een button voor gecreëerd.

Oplossing

Zoals voor ieder bel-diagram hebben we naast waarden voor de x- en y-as ook waarden nodig die de grootte van de bellen zullen bepalen.

NB1 een bel-grafiek is een speciale vorm van een spreiding- of xy-grafiek en kan dus op de assen alleen maar met getallen werken, geen teksten.

NB2 we kunnen niet exact de grootte van de bellen bepalen; Excel bepaalt zelf de grootte relatief ten opzichte van elkaar.

In het tabblad Data van het Voorbeeldbestand is dit tabelletje opgenomen en is een bel-grafiek gemaakt.

Maar nu komt de vraag van Sander: kunnen de bellen een eigen kleur krijgen afhankelijk van het aantal keren, dat een combinatie van x en y voorkomt. Hij zou graag zien, dat een bel rood is als het aantal 1 tot 5 is, geel bij 5 tot 10 en groen bij 10 of meer.

Daarom eerst even een hulptabel opgezet (zie tabblad Param in het Voorbeeldbestand):

een kolom voor de ondergrens voor een bepaalde kleur
een kolom met de decimale code voor de gewenste kleur (zie hierboven)
de 8 cellen zijn in een Excel-tabel geplaatst (via Invoegen/Tabel) en deze tabel heeft de naam tbKleur gekregen.

Aan de gegevens heb ik een vierde kolom toegevoegd met een fictief aantal en nog een kolom die uit Param afleidt welke kleurcode de bel moet krijgen. De gegevens zijn in een Excel-tabel opgenomen met de naam tbData.

De formule in de kolom Kleur ziet er als volgt uit:
=VERT.ZOEKEN([@Aantal];tbKleur;2;WAAR)

Ofwel: neem de waarde uit de corresponderende kolom Aantal, zoek in de Excel-tabel tbKleur deze waarde op en geeft de waarde uit de 2e kolom terug (de kleurcode). We zoeken geen exacte match, maar een ‘benadering’ (de grootste waarde die nog voldoet); vandaar WAAR als laatste parameter in de functie VERT.ZOEKEN.

Alles staat nu klaar om de bellen de juiste kleurcodes te geven.

VBA-routine

Zoals hiervoor aangegeven, kent Excel geen automatische koppeling tussen de kleur van de bellen en de waarde in bepaalde cellen. Dus zullen we het zelf moeten doen:

in de eerste regel geven we ons programma (subroutine) een naam: BelKleur
in de 2e en 3e regel zorgen we er voor dat de variabelen pts en x netjes worden gedefinieerd (mbv Dim): in de eerste variabele gaan we punten bewaren, in de tweede gehele getallen.
in de 4e regel vullen we de variabele pts met de punten (Points) uit de eerste serie (SeriesCollection(1)) van de grafiek (Chart) met de naam grBel (ChartObjects(“grBel”)) in het actieve Excel-tabblad (ActiveSheet)
dan komt er een For-Next-loop waarbij x loopt van 1 tot het aantal bellen in de grafiek (pts.Count). Alles tussen For en Next wordt dus net zo vaak herhaald als er bellen zijn.
regel 6 en 7 horen bij elkaar (door de Underscore en een spatie op het einde van regel 6).
De opmaak (Format) van punt x (pts(x)) wordt ingesteld; de bel wordt gevuld (Fill) met een kleurcode (ForeColor.RGB) gelijk aan de inhoud van de cel x+1 (de kop telt ook mee, vandaar +1) uit de kolom Kleur van de tabel tbData uit de actieve sheet (ActiveSheet)

Bellen kleuren

Iedere keer als er iets aan de gegevens wordt gewijzigd (nieuwe regels toegevoegd, aantallen aangepast) zal bovenstaande routine moeten worden uitgevoerd.

Dat kan op verschillende manieren:

klik op de button Bellen kleuren op het tabblad Data van het Voorbeeldbestand
kies op de menu-tab Ontwikkelaars de optie Macro’s, selecteer de routine BelKleur en klik op Uitvoeren
Druk op Alt-F8, selecteer de routine BelKleur en klik op Uitvoeren

Zonnepanelen

4 juli 2016ExcelCumulatief, Draaigrafiek, Draaitabel, Namen, SOM.ALS, Tabel, Teken, voorwaardelijke opmaakGijs Verbruggen

In het kader van duurzaamheid hebben wij ook zonnepanelen op het dak liggen. Vijf jaar geleden hebben we 2 (kleine) sets laten plaatsen; na dit lustrum zullen we eens kijken hoe we de resultaten inzichtelijk kunnen maken.

Een mooie definitie in dit kader:

“Duurzame ontwikkeling is de ontwikkeling die aansluit op de behoeften van het heden zonder het vermogen van de toekomstige generaties om in hun eigen behoeften te voorzien in gevaar te brengen”

Dataverzameling

Een beetje jaloers kijk ik wel eens naar andere mensen, die recentelijk panelen hebben aangeschaft: de huidige stroom-omvormers communiceren via wifi met centrale servers waar alle gegevens over stroomproductie worden verzameld. Via internet kunnen deze gegevens on-the-fly worden opgevraagd; alle details zijn in te zien, maar ook allerlei grafiekjes staan de ‘stroom-leveranciers’ tot hun beschikking.

In mijn geval moet ik wekelijks een laptop aansluiten op de omvormers en dan kan ik de resultaten van die periode uitlezen.
Uiteraard sla ik die in Excel op, zodat ik ook overzichten kan maken; zie het Voorbeeldbestand, tabblad Data.

Per dag leg ik vast hoeveel kWh een set (met drie panelen) heeft opgeleverd. Voor het gemak laat ik Excel ook de dag van de week weergeven. Naast de datum staat daartoe een kolom met dezelfde inhoud maar met een andere datumopmaak namelijk “dddd” (selecteer een cel in die kolom en kies celeigenschappen m.b.v. Ctrl-1).
De kolom met de kWh’s heeft een voorwaardelijke opmaak: als het resultaat van een dag lager is dan het gemiddelde dan wordt die rood weergegeven.

Wikipedia:

Het kilowattuur (symbool kWh) is een eenheid van arbeid of, populair uitgedrukt, een hoeveelheid elektrische energie. Als men een machine met een vermogen van 1 kW één uur laat werken heeft men een energie van 1 kWh "verbruikt".
De kosten voor opwekking van 1 kWh aan elektriciteit zijn in Nederland ongeveer 4 eurocent voor een elektriciteitscentrale die wordt gestookt op aardgas of steenkool, en 5-8 eurocent, afhankelijk van de standplaats van de molen, voor windenergie (2003). 
De verbruikersprijs ligt echter aanzienlijk hoger vanwege de kosten van bijvoorbeeld service, stroomtransport en energiebelasting. In 2013 kost een kWh ca. 23 eurocent.
Een gemiddeld huishouden verbruikte in Nederland in 2010 ongeveer 3.300 kWh.

Om makkelijk analyses te kunnen uitvoeren zijn aan de lijst nog enkele kolommen toegevoegd met het jaar, de maand en de week.
NB de gegevens worden opgeslagen in een zogenaamde Excel-tabel met de naam Zon (zie het artikel over de voordelen van tabellen). Formules en lay-out worden bij het toevoegen van nieuwe resultaten automatisch doorgevoerd.

Gemiddeldes en totalen

Wat hebben de 3 panelen in die 5 jaar opgeleverd? Het Voorbeeldbestand bevat een tabblad Ovz met enkele statistieken.

In cel C2 wordt het totaal aantal opgeleverde kWh’s bepaald, de SOM van alle waardes in de kolom kWh van de Excel-tabel Zon. Die cel heeft de naam TotKWH gekregen.

NB wanneer de tabel Zon wordt uitgebreid met volgende dagen dan zal de formule direct geactualiseerd worden met de nieuwe gegevens.

In cel C3 (met de naam AantDg) staat de volgende formule om het aantal geregistreerde dagen te tellen:
=MAX(Zon[Datum])-MIN(Zon[Datum])+1

Dus de grootste datum in de tabel minus de kleinste; gecorrigeerd met 1 omdat de eerste dag ook meetelt.

NB aangezien ik zeker weet dat alle dagen gevuld zijn had ik ook kunnen gebruiken:
=AANTAL(Zon[Datum])

Het gemiddelde per dag hebben we nu ook: in cel C4 (met de naam GemDag; deze naam wordt in de voorwaardelijke opmaak van het tabblad Data gebruikt) staat de formule:
=TotKWH/AantDg

Door in C5 het gemiddeld aantal dagen per jaar in te vullen weten we het jaargemiddelde.

Natuurlijk willen we ook de totalen per jaar zien. In cel F3 staat daartoe de formule:
=SOM.ALS(Zon[Jaar];E3;Zon[kWh])

Dus de som van alle kWh’s waarvoor geldt dat in de kolom Jaar de waarde uit cel E3 staat.

Kopieer de formule naar beneden en klaar is …!

Het lijkt er op, dat we in 2016 het resultaat van het topjaar (2015) niet gaan bereiken.

Om wat beter inzicht te krijgen hoe de resultaten over de tijd zijn verdeeld, maken we wat grafieken.

Grafieken

Het resultaat van de afgelopen 5 jaar in beeld: in het blauw de dagresultaten en de rode lijn geeft het totaal in de loop van de tijd weer.

Hoe is deze grafiek gemaakt:

op basis van de gegevens in het tabblad Data is een draaitabel gemaakt (via de menu-optie Invoegen en dan Tabellen/Draaitabel); zie het tabblad OvzDag in het Voorbeeldbestand:
als rij-label is daar de Datum genomen en de kWh’s zijn 2 keer in het waardegebied gezet.
de 2e kWh-kolom heeft een aparte weergave:
Via rechtsklikken op één van de waardes in die kolom is de optie Waarden weegeven als gekozen. Na het klikken van de optie Voorlopig totaal in … kan één van de velden in Rijlabels worden gekozen (in dit voorbeeld hebben we er maar 1, namelijk Datum).
Op deze manier creëren we een lopend cumulatief: het resultaat is de som van alle kWh’s tot en met de betreffende datum.
Kies bij Opties in het tabblad Hulpmiddelen voor Draaitabellen het onderdeel Draaigrafiek; zorg dat de dagresultaten Kolom-grafiek als type krijgen en het lopend cumulatief een lijngrafiek en plaats deze laatste op de secundaire as.
Nog even een sprekende titel:
* in cel F2 staat de formule:
=”Zonnepanelen set 1: “&TEKEN(13)&TEKST(GemDag;”0,0″)&” kWh gem per dag en “&TEKST(TotKWH;”#.##0″)&” totaal na “&TEKST(AantDg;”#.##0″)&” dagen”
Hier worden diverse teksten aan elkaar ‘geplakt’ met het &-teken; naast letterlijke teksten ook verwijzingen naar bepaalde cellen (inclusief opmaak mbv de functie TEKST). De functie TEKEN zorgt met de code 13 voor een overgang naar een nieuwe regel (niet direct zichtbaar in de cel F2).
* in het tabblad Hulpmiddelen voor Draaigrafieken zorgen we via de Indeling en de optie Grafiektitel dat er een titel tevoorschijn komt; terwijl deze actief is tikken we in de formulebalk in =OvzDag!$F$2 (of we klikken met de muis op de betreffende cel)

Maar een grafiek per dag is wel heel gedetailleerd; in het Voorbeeldbestand is een tabblad OvzWk opgenomen waarin opnieuw een draaitabel is gemaakt op basis van de gegevens uit het tabblad Data.
In de rijen staan nu 2 velden: het Jaar en de Week. De 2e kolom van de kWh’s krijgt een voorlopig totaal op basis van de Week, zodat een lopend cumulatief per jaar ontstaat.
Op eenzelfde manier is een grafiek per maand gemaakt (zie tabblad OvzMnd):

Om de resultaten per maand in de loop van de tijd beter te kunnen vergelijken hebben we ook nog tabblad OvzMnd2:

Duidelijk is te zien, dat de afgelopen maand (juni 2016) ver achter is gebleven bij het resultaat in dezelfde maand de jaren daarvoor; 60 in plaats van 90 kWh!

NB juni 2011 is zo laag omdat die maand slechts 2 productiedagen telde.

G-Info

van gegevens naar informatie