Tagarchief: Draaigrafiek

Excel en kaarten

Excel, ,
Voorbeeldbestand: Kaart.xlsbDownload

Om misverstanden te voorkomen: met het woord kaarten in de titel worden geografische kaarten bedoeld (in het Engels Maps).

Sommige mensen willen informatie aangeleverd krijgen in de vorm van tabellen met (exacte) getallen, maar de meeste mensen vinden grafieken makkelijker om gegevens te interpreteren en te analyseren.

Als het dan geografische gegevens betreft is het natuurlijk ook wel mooi om de informatie op een kaart weer te geven.
In dit artikel wat handreikingen hoe je dit kunt doen.

Basisgegevens

Voor dit artikel gebruik ik een uittreksel van gegevens van de website Veldkruus.nl (even reclame maken voor een andere hobby van mij: het letterlijk in kaart brengen van veldkruisen, kapelletjes etcetera in Zuid-Limburg). Zie het tabblad Data in het Voorbeeldbestand.

Van ieder object ziet u in het voorbeeld in welke plaats het staat, welke buurt en welke straat. Ook of het een kruis of beeld of kapel is. Wanneer er foto’s gemaakt zijn en of het object nog ‘actief’ is ofwel of het er nog wel is!
Daarnaast leggen we ook de coördinaten vast in de vorm van zijn Longitude en Latitude, lengte- respectievelijk breedtegraad.

NB1 simpel gezegd: de Longitude geeft aan hoever iets van de mediaan door Greenwich af ligt, terwijl de Latitude de ‘afstand’ tot de evenaar weergeeft.
NB2 in de praktijk is het toekennen van coördinaten afhankelijk van diverse zaken. Op Veldkruus gebruiken we de coördinaten-systematiek van Google-maps; klik je daar ergens op een kaart met de rechtermuisknop en je kiest Wat is hier? dan zie je de coördinaten van dat punt.

LET OP Google toont de coördinaten in de volgorde Lat, Long; dus eerst de verticale en dan de horizontale richting.

Overzicht

Om een idee te krijgen over de verdeling van de objecten over de regio maken we een draaitabel, gesplitst naar plaats en categorie (zie het tabblad Draai in het Voorbeeldbestand).
Op basis van deze draaitabel is het maken van een grafiek een ‘peuleschil’ (kies in de menutab Hulpmiddelen voor draaitabellen in het blok Opties de mogelijkheid Draaigrafiek).
Om het wat overzichtelijk te houden beperken we ons tot de top-20 (zie Top-5: methode 3). Om snel de verdeling per categorie te zien, staan boven de grafiek Slicers.

We hebben nu wel de verdeling per plaats, maar hoe is nu de verdeling over Zuid-Limburg?

Grafiek met kaart 1

In het tabblad Data20 van het Voorbeeldbestand ziet u de verdeling van de top-20 over de regio.

Hoe maakt u zo’n grafiek?

  1. allereerst moet u een mooi kaartje zoeken op internet dat u als achtergrond wilt gebruiken. Sla dat ergens op.
  2. dan krijgen de 20 plaatsen een x- en y-coördinaat (vul voorlopig getallen tussen 0 en 10 in; de juiste getallen komen hierna)
  3. maak een spreidingsgrafiek: kies in de menutab Invoegen in het blok Grafieken de eerste optie binnen Spreiding (dus geen lijnen)
  4. klik rechts in de nieuwe (lege) grafiek en kies Gegevens selecteren
  5. kies de optie Toevoegen en vul de vereiste verwijzingen in
  6. 2 keer OK klikken levert een spreidingsdiagram
  7. zorg dat de assen allebei lopen van 0 tot 10 (rechts klikken op een as, As opmaken kiezen en de grenzen vast zetten)
  8. klik ergens rechts in de grafiek en kies de optie Tekengebied opmaken, kies bij Opvulling de optie Invoegen uit: Bestand.  Kies dan het kaartje van punt 1.
    Sleep de randen van de grafiek naar binnen of naar buiten, zodat de kaart in de juiste verhoudingen wordt weergegeven.
  9. vul dan de coördinaten bij de plaatsen zo in, dat de de markeringen op de juiste plaats komen
  10. nog wat lay-out: grafiekpunten vergroten, kleuren aanpassen, rasterlijnen weg en als laatste ook de assen

LET OP zorg dat de achtergrond-kaart aan het Tekengebied wordt toegevoegd, NIET aan het Grafiekgebied. Doet u dat laatste, dan zullen de markeringen na bepaalde lay-out-aanpassingen niet meer op de juiste plaats worden weergegeven.

NB1 de exacte as-indeling doet er niet toe. Maar een verdeling van 0 tot 10 is handig.

NB2 tot versie 2013 van Excel kun je in een spreidingsgrafiek alleen de coördinaten als label meegeven. Wel is het mogelijk om met de hand eigen labels toe te voegen: klik rechts op een punt van de grafiek en kies Gegevenslabel toevoegen. Klik dan dubbel op één van de labels, plaats in de formulebalk het is-teken (=) en wijs met de muis de cel aan waar het label staat (bijvoorbeeld Data20!$E$3).

Grafiek met kaart 2

Met een bel-diagram kun je nog een derde dimensie aan de grafiek toevoegen; in dit  geval het totaal aantal objecten per plaats (zie het tabblad BelGraf in het Voorbeeldbestand).
De grootte van een bel wordt door Excel bepaald en is altijd relatief tov de andere bellen. Wel kun je de grootte van alle bellen schalen om ze beter op de kaart te laten uitkomen (klik rechts op een bel en kies Gegevensreeks opmaken).

Hoe je een bel-grafiek maakt is beschreven in het gelijknamige artikel.

Grafiek met kaart 3

Een variant op de vorige grafiek (zie tabblad LongLat in het Voorbeeldbestand). Nu werken we niet met fictieve coördinaten, maar met de werkelijke coördinaten per plaats.

De Long(itudes) zijn de x-waarden en de Lat(itudes) de y-waarden (voor de coördinaten van de plaatsen in Zuid-Limburg zie het tabblad Plaatsen).

De truc zit hem er nu in om de assen zodanig te schalen, dat de Plaatsen op de juiste plek op het achterliggende plaatje komen:

  1. bekijk zo goed mogelijk waar het 0-punt van de assen op het achterliggende plaatje ligt
  2. zoek in Google-maps de coördinaten van dat punt op (rechtsklikken en de optie Wat is hier? kiezen)
  3. klik rechts op de x-as en kies As opmaken. Maak het Minimum Vast en vul hier de gevonden Long-waarde van punt 2 in (voor Nederland en omstreken is dat het kleinste getal, dus het tweede)
  4. doe hetzelfde met de y-as maar vul daar de Lat-waarde in
  5. zoek op dezelfde manier de Long-waarde op van het eindpunt van de x-as en gebruik deze om het maximum van de x-as vast te pinnen
  6. zoek op dezelfde manier de Lat-waarde op van het eindpunt van de y-as en gebruik deze om het maximum van de y-as vast te pinnen

LET OP als de kwaliteit van de achtergrond niet goed is (niet de juiste kaart-verhoudingen), dan zullen de Plaatsen niet helemaal goed op de kaart komen. Een beetje ‘spelen’ met minima en maxima (no pun intended zoals de Engelstaligen zeggen) kan dan wel helpen.

Alternatieve toepassingen

De boven geschetste methode hoeft natuurlijk niet beperkt te blijven tot landkaarten. Hierboven een voorbeeld van een andere toepassing (zie het tabblad Afdelingen in het Voorbeeldbestand).

 

Trend-analyse

Excel, , , , , , , , , , ,

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

  1. 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.
  2. 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).
  3. kies in de menubalk de optie Invoegen/Grafieken/Lijn
  4. het resultaat vindt u terug in het tabblad Graf

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

  1. klik ergens met de rechtermuisknop op de grafiek en kies de optie Trendlijn toevoegen
  2. laten we het simpel houden en voor een rechte lijn (de optie Lineair) kiezen
  3. 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:

  1. klik met de rechtermuisknop op de trendlijn in de grafiek
  2. kies Trendlijn opmaken
  3. 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):

  1. 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)
  2. in H3 is op dezelfde manier de functie SNIJPUNT ingevoerd
  3. 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):

  1. selecteer 6 lege cellen, 2 kolommen en 3 regels  (in het voorbeeld G12:H14)
  2. 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.
  3. 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’:

  1. selecteer een cel in de draaitabel
  2. in de menutab Hulpmiddelen voor draaitabellen kiezen we het tabblad Opties
  3. klik dan op de optie Draaigrafiek in het blok Extra
  4. kies een Lijngrafiek en klik OK
  5. 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:

  1. kies in de Slicer Weekdag de button ma
  2. houd Shift ingedrukt en klik op vr
  3. laat Shift los
  4. 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.

Groeperen in een draaitabel

Excel, ,

Hoewel steeds meer Excel-gebruikers weten, dat draaitabellen ontzettend handig zijn bij de analyse van gegevens, het maken van overzichten etc. en deze mogelijkheden ook in hun dagelijkse werk gebruiken, is het groeperen binnen draaitabellen nog minder bekend: deze keer dus de focus op Groeperen in draaitabellen.

Oplettende lezers hebben ongetwijfeld gezien, dat ik dit in mijn vorige artikel (Top-2000 en draaitabellen) heb gebruikt zonder verdere uitleg.

Basis

Om een draaitabel te kunnen maken hebben we wat basisgegevens nodig. Ik heb in het Voorbeeldbestand op het tabblad Basis de fictieve productie van een simpele winkel gezet: een datum, het artikel en het verkochte aantal (soms dubbel).
Uiteraard (zie Kunst en Excel; 10 voordelen van Tabellen) zijn die gegevens in de vorm van een Tabel opgeslagen (met als naam tblVerkopen).
Ze waren eerst niet gesorteerd, maar om gemakkelijk de resultaten van de draaitabellen te beoordelen, heb ik dat nu wel even gedaan (klik op het vinkje achter Datum).

Overzicht per dag

Laten we snel even een overzicht maken van deze gegevens:

  1. Plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Datum naar de Rijlabels, Art naar de Kolomlabels en Aantal naar het waardengebied
  5. eigenlijk is de draaitabel klaar, maar ik vind de standaard-layout niet handig, dus heb ik in de menutab Hulpmiddelen voor draaitabellen de tab Ontwerpen gekozen en bij Rapportindeling de optie Tabelweergave.

Het resultaat staat op het tabblad Draai van het Voorbeeldbestand: een overzicht van de totale verkoopaantallen per dag en gesplitst naar artikel.

Overzicht per maand

Vaak willen we in rapportages niet alle details zien, maar bijvoorbeeld in plaats van dag-resultaten de verkopen per maand.

In dit geval zou je de basisgegevens daartoe kunnen uitbreiden met een zogenaamde hulp-kolom waarin met behulp van de functie MAAND de maand wordt vastgelegd. Als we ook nog het jaar willen weten, moeten we nog een extra hulp-kolom toevoegen (met daarin de functie JAAR).

Maar die hulpkolommen zijn niet nodig:

  1. klik met de rechtermuisknop op één van de datums in de hiervoor gemaakte draaitabel
  2. het zogenaamde context-gevoelige submenu komt dan tevoorschijn. Kies daar de optie Groeperen
  3. Excel heeft de kolom, waarin geklikt is, geanalyseerd en heeft gezien dat dit allemaal datums zijn.
    We willen een maand-overzicht dus het voorstel van Excel kunnen we overnemen; klik op OK

LET OP als in de basisgegevens ergens in de kolom Datum een cel niet is gevuld of gevuld met een niet-bestaande datum of een getal of een tekst, dan zal het Groeperen-scherm er anders uitzien. De datum-opties zijn dan niet beschikbaar.
Nog erger: ook al corrigeert u de basisgegevens, Excel zal deze kolom nooit meer als een datum ‘zien’. Er zit dan niets anders op dan het werkblad met de draaitabel te verwijderen en het overzicht opnieuw te maken!

NB In het blok Lijst met draaitabelvelden aan de rechterkant van het scherm staat bij de te kiezen velden nog steeds Datum, maar Excel zal in de draaitabel alleen nog maar de maanden laten zien.

Overzicht per maand en jaar

Het overzicht per maand is in dit geval prima omdat Basis alleen maar datums uit 2016 bevat. Voegen we echter onderaan (of tussenin) de volgende regel toe:
1-1-2017, Servetten, 10
dan komen die in het Overzicht per maand uiteraard ook in de januari-regel tevoorschijn (wel even de draaitabel Vernieuwen door ergens rechts te klikken!).

We moeten zorgen dat de draaitabel ook per jaar groepeert:

  1. klik met de rechtermuisknop op één van de maanden in de datum-kolom van de draaitabel
  2. kies opnieuw Groeperen
  3. en klik nu ook de Jaren aan en OK

NB1 in de Lijst met draaitabelvelden is een er een nieuw veld bijgekomen, Jaren. Dit veld kunt u op dezelfde manier in de draaitabel plaatsen als andere velden. Bijvoorbeeld in het Rapportfilter zodat u overzichten per jaar kunt maken.

NB2 wanneer u een draaitabel maakt, dan legt Excel de brongegevens apart op een eigen manier in het geheugen vast. Zo ook de consequenties van groeperingen. Maakt u nu met dezelfde bron (in dit geval tblVerkopen) een andere draaitabel, dan gebruikt Excel, om geheugenruimte te sparen, dezelfde interne bron. Dus ook eventuele groeperingen zijn in de tweede draaitabel direct zichtbaar.

NB3 hebt u bij een analyse/overzicht in dezelfde draaitabel (of een andere; zie NB2) de datums zelf nodig, klik dan bij het Groeperen ook de Dagen aan.
Hebt u de dagen in het huidige overzicht niet (meer) nodig, verwijder dan het vinkje voor de Datum in Lijst met draaitabelvelden.

Overzicht per kwartaal

Ongetwijfeld hebt u ondertussen ook gezien dat u op deze manier heel gemakkelijk kwartaaloverzichten kunt maken.
In het overzicht hiernaast is de groepering per kwartaal aangezet (zoals te zien is heb ik ook de 2017-regel toegevoegd en de draaitabel vernieuwd).
Nog een paar kleine aanpassingen:

  1. met de rechtermuisknop klikken op een jaar en dan Subtotaal Jaren aanvinken
  2. met de rechtermuisknop klikken op een kwartaal en dan Subtotaal Kwartalen aanvinken
  3. de eerste drie kwartalen ‘dichtklappen’ door op het min-teken vóór het betreffende kwartaal te klikken
  4. grafiek nodig? Klik ergens in de draaitabel, kies het tabblad Opties in de menutab Hulpmiddelen voor draaitabellen en binnen het blok Extra de optie Draaigrafiek. We willen een Kolomgrafiek, dus het klikken op OK volstaat.

Artikelen groeperen

Voordat we verder gaan is het het makkelijkst om de draaitabel op het tabblad Draai van het Voorbeeldbestand eerst aan te passen:

  1. klik het vinkje voor Datum weg in de Lijst met draaitabelvelden
  2. verplaats Art van Kolomlabels naar Rijlabels

Zoals we gezien hebben weet Excel bij datums hoe hij kan groeperen; bij teksten is dat natuurlijk een stuk lastiger.
Daarom gaan we als volgt te werk:

  1. selecteer met de muis, die cellen die u wilt groeperen. In dit geval liggen die niet tegen elkaar, dus moeten we de Ctrl-toets gebruiken.
    Klik op Broodrooster
  2. hou Ctrl ingedrukt en klik op Strijkijzer
  3. klik met de rechtermuisknop op één van die 2 artikelen en kies Groeperen
  4. doe hetzelfde met Servetten en Tafelkleed
  5. verander de nieuwe Groep1 in Hard door de tekst ‘er overheen’ te tikken
  6. Groep2 wordt op die manier Soft
  7. de nieuwe kolomkop Art2 wordt ArtGroep
  8. zet nog de subtotalen voor ArtGroep aan

Nu weer een kwartaaloverzicht maken:

  1. klik het vinkje voor Art weg
  2. verplaats ArtGroep van Rijlabels naar Kolomlabels
  3. plaats Kwartalen en Jaren in de juiste volgorde in Rijlabels

Getallen groeperen

Om dit toe te lichten maken we een nieuwe draaitabel:

  1. plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Aantal naar de Rijlabels, Art naar de Kolomlabels en Datum naar het waardengebied

We zien op die manier het aantal keren, dat een bepaald aantal verkopen voorkomt, ook nog gesplitst naar artikel.

LET OP Aantal van Datum is wat misleidend: we turven niet het aantal dagen, maar het aantal keer dat het veld Datum gevuld is. We hadden ook Aantal van Art of zelfs Aantal van Aantal kunnen kiezen.

NB de basis-gegevens zijn random gevuld. Je zou dus overal exact 50 verwachten. Als ik 100.000 records had aangemaakt zou de spreiding ook minder groot zijn geweest!

Maar nu groeperen:

  1. klik met de rechtermuisknop op één van de cijfers 2, 3, 4, 5 of 6 en kies Groeperen
  2. we krijgen nu een heel ander groepeer-scherm; we kunnen een eigen indeling maken.
    Verander de 2 in 1 en de 1 achter Op: in 3.
    Ofetwel begin bij 1 en groepeer in blokken van 3.
  3. klik op OK.

Als we ook nog de artikelen groeperen naar Hard en Soft dan krijgen we het volgende overzicht:

Top2000 en draaitabellen

Excel, , , ,
Voorbeeld bijgewerkt t/m 2020: Top2000_2020Download

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:

  1. plaats de cursor ergens in de tabel
  2. kies in de menutab Gegevens in de groep Sorteren en filteren de optie Sorteren
  3. 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:

  1. klik op het  achter Artiest
  2. 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.
  3. Ik bedoelde niet de Eagles of Death Metal, dus die vinken we uit en klikken op OK
  4. 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:

  1. klik ergens in de tabel tblTop2000 in het tabblad Basis van het Voorbeeldbestand
  2. kies in de menutab Invoegen de optie Draaitabel en klik op OK
  3. 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.
  4. kies in de nieuwe menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen en dan bij Rapportindeling de optie Tabelweergave
  5. 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?

  1. klik ergens in de 2016-kolom, bijvoorbeeld cel B5
  2. kies in de menutab Gegevens in het blok Sorteren en filteren de optie .
  3. wil je de stand in een ander jaar weten? Herhaal de procedure met de cursor in een andere kolom.
  4. 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:

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

  1. plaats de cursor in één van de titels
  2. klik in die cel met de rechter-muisknop en kies binnen de optie Sorteren het item Meer sorteeropties
  3. 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):

  1. klik ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen de tab Opties
  3. in het blokje Extra vindt u de Draaigrafiek
  4. 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
  5. 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?

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

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

Zonnepanelen

Excel, , , , , , ,

ZonnepanelenIn 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

zon1Een 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.
zon2NB  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

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

zon4Natuurlijk 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

zon5

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:

  1. zon6op 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.
  2. de 2e kWh-kolom heeft een aparte weergave:
    zon7Via 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.
  3. 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.
  4. 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.
zon8Op eenzelfde manier is een grafiek per maand gemaakt (zie tabblad OvzMnd):

zon9

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

zon10

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.