Tagarchief: Grafiek

Grafische analyse in Excel

Excel, , , ,
Voorbeeldbestand: AEX.xlsxDownload

Onlangs kwam ik op de website www.exceluser.com enkele nuttige artikelen tegen: 3 Simple Tricks to Improve Analytical Charting in Excel, 3 More Simple Tricks to Improve Excel Charts for Business, Introducing the Power of Year-Over-Year Performance Charts in Excel en What Inflation? How Excel Charts Can Help You Avoid ‘Anecdotal Economics’.

In dit artikel zal ik een paar ideeën van Charles W. Kyd de revue laten passeren en aan de hand van voorbeelden de principes daarachter uitleggen.

Wat laat je zien?

Hiernaast ziet u een simpel omzet-overzicht van een bedrijfje (zie het tabblad Vb1 in het Voorbeeldbestand).
In de maandrapportage kun je zo’n overzicht opnemen, maar je kunt er ook voor kiezen om een grafiek te tonen.

Directeur tevreden, iedereen tevreden?
Nee dus, de marketingmanager vindt dat zo niet goed zichtbaar is, dat de marketing-activiteiten hun vruchten afwerpen en wil een andere grafiek.

Dan blijkt dat de productie-manager weer andere wensen heeft (een collega heeft in de maand maart zijn werkzaamheden overgenomen) en vindt dat het beter is om de groei/krimp van de omzet in de loop van de tijd te laten zien.

Waar cijfer-overzichten veelal de interpretatie van de data aan de lezer overlaten, mag het duidelijk zijn dat grafische presentaties het gevaar van misleiding met zich mee brengen.

Onvergelijkbare gegevens vergelijken

Stel we hebben een maandoverzicht waarin 2 soorten bedragen voorkomen, die qua grootte ver bij elkaar uit de buurt liggen (zie het tabblad Vb2 in het Voorbeeldbestand).

Wanneer we deze in een standaard lijn-grafiek weergeven, dan zijn er weinig details per maand uit de grafiek af te leiden.

Eén van de meest gehanteerde oplossingen is om de twee reeksen aan verschillende assen toe te wijzen.
NB het kleurgebruik moet de lezer helpen om te zien welke reeks bij welke as hoort.

Als vooral het verloop van de tijd van belang is en niet de exacte hoogte van de bedragen, dan is een andere oplossing om de reeksen te indexeren.
Dan is er maar één verticale as nodig; dit voorkomt misverstanden.
Het betekent wel dat er aan de basisgegevens kolommen moeten worden toegevoegd met een berekening zoals =[@Bedr1]/$C$6

Verloop AEX

Eén van de artikelen op ExcelUser.com is geschreven naar aanleiding van het dalen van de koersen in Amerika (What Inflation?). Deze daling liep synchroon met de correctie van de bitcoin-koersen. Maar was/is hier sprake van een correctie of begint de inflatie weer toe te nemen? Of nog erger: zitten we weer aan het begin van een recessie?

En hoe zit dit in Nederland? Geeft de AEX ook aanleiding tot dit soort bespiegelingen?
De bedoeling van dit artikel is niet om hier nu een sluitend antwoord op te geven; wel zal ik aan de hand van enkele voorbeelden laten zien hoe grafieken het proces om te komen tot beantwoording kunnen ondersteunen.

In het tabblad AEX van het Voorbeeldbestand staat een overzicht van de AEX-resultaten vanaf januari 2000 (alleen koersen per werkdag zijn beschikbaar). We zullen alleen de kolom Datum en Close (koerswaarde op het moment van sluiten van de beurs) gebruiken.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAEX. Een van de grote voordelen van deze vorm is, dat wanneer er nieuwe cijfers beschikbaar zijn en deze onderaan toegevoegd worden, alle formules en grafieken automatisch aangepast worden. Een update van het analyse-systeem is dus een ‘fluitje van een cent’.

Om makkelijk te kunnen inzoomen op gedeeltes van het verloop van de AEX is deze grafiek dynamisch gemaakt. Dus wat er getoond wordt is afhankelijk van de inhoud van bepaalde cellen, in dit geval F3 en F4 (zie ook het artikel dynamische-grafieken).

In het tabblad AEX_Ovz van het Voorbeeldbestand staan daartoe 2 hulp-tabellen.
In cel C3 (met de naam MinDtm) wordt het minimum van de kolom Datum uit de tabel tblAEX bepaald; zo ook in cel C4 het maximum. Onder andere deze 2 waardes worden gebruikt om de mogelijke invoer in de cellen F3 (met de naam Start) en F4 (met de naam Eind) te begrenzen (met behulp van Gegevens-validatie).
In cel G3 (met de naam StartRij) wordt dan bepaald in welke rij van de AEX-tabel de datum uit F3 staat: =VERGELIJKEN(Start;tblAEX[Datum])

LET OP de derde parameter in de functie VERGELIJKEN is weggelaten; dat betekent dat de functie de grootste datum zoekt die kleiner of gelijk is aan Start. De kolom Datum in de tabel tblAEX moet dan wel oplopend gesorteerd zijn!

Zo wordt ook de rij van de laatste gewenste datum opgezocht in cel G4 (met de naam EindRij).

Namen definieren

In het voorgaande heb ik al regelmatig Namen gebruikt en dan vooral met het doel formules leesbaarder te maken. Ook hebben alle Excel-tabellen een naam gekregen, waardoor in formules direct duidelijk is naar welke tabel verwezen wordt. Het gebruik van tabellen heeft daarnaast als voordeel dat formules en grafieken verwijzen naar kolomnamen i.p.v. naar cel-bereiken (dus =tblVb1[Omzet] in plaats van =C3:C8)

Maar om dynamische grafieken te kunnen creëren hebben we ook iets ingewikkelder constructies als Naam nodig:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren
  2. klik op de button Nieuw…
  3. geef een naam op; als eerste voorbeeld rngDtm
  4. de formule onder Verwijst naar wordt dan
    =VERSCHUIVING(tblAEX[[#Kopteksten];[Datum]];StartRij;0;EindRij-StartRij+1)
  5. klik Sluiten

NB de functie VERSCHUIVING geeft als resultaat de reeks cellen, die ontstaat als we vanaf de kop van de kolom Datum in de tabel tblAEX het aantal StartRij naar beneden gaan en 0 kolommen naar rechts/links, waarbij de lengte van de reeks bepaald wordt door de formule EindRij-StartRij+1.

De formule voor rngClose is dan gelijk aan =VERSCHUIVING(tblAEX[[#Kopteksten];[Close]];StartRij;0;EindRij-StartRij+1)

LET OP Deze rng-namen kunnen nu via Gegevens selecteren aan een grafiek worden gekoppeld; denk er wel aan om niet =rngDtm in te vullen, maar =AEX!rngDtm, dus voor alle namen een naam van een tabblad, aangevuld met een !-teken.

Vul je in F3 de waarde 1-1-2007 in en in cel F4 de waarde 31-12-2013 dan ontstaat automatisch de volgende grafiek:

AEX in perspectief

Eén van de tips van ExcelUser.com is om een grafiek van een (historisch) perspectief te voorzien. In dit geval kan het interessant zijn om te zien wat het verband is tussen het koersverloop en recessies.

Daartoe zijn in het tabblad Recessie van het Voorbeeldbestand de recessies vanaf 2000 vastgelegd met een begin en een einddatum. Dit in de vorm van een Excel-tabel met als naam tblRecessie.

De tabel tblAEX in het tabblad AEX is uitgebreid met een kolom F waarin de formule
=ALS([@Datum]<=INDEX(tblRecessie[Eind]; VERGELIJKEN([@Datum];tblRecessie[Start]));
1;NB())
staat.

Korte toelichting: zoek met de functie VERGELIJKEN de Datum uit dezelfde regel (vandaar de @) op in de Start-kolom van tblRecessie ; als dit niets oplevert dan is het resultaat van de totale formule #N/B.
Dan wordt met behulp van de functie INDEX gekeken of deze datum kleiner of gelijk is aan de overeenkomende regel in de Eind-kolom van tblRecessie.
Als dat zo is, dan wordt het resultaat 1 en anders ook weer #N/B.
Het resultaat #N/B wordt in een grafiek niet weergegeven.

Deze recessie-gegevens nemen we in de grafiek op; niet rechtstreeks maar ook weer via een naam-formule: rngRecessie
=VERSCHUIVING(tblAEX[[#Kopteksten];[Recessie]];StartRij;0;EindRij-StartRij+1)

Deze range krijgt een eigen type grafiek, namelijk een Vlak en we zetten deze uit op de secundaire as. Zorg dat het maximum van deze as altijd 1 is en verwijder alle kenmerken van de as.

Op een vergelijkbare manier zijn ook de minimum- en maximum-koersen in de grafiek opgenomen.

Alarmbellen?

Als we inzoomen op het koersverloop van dit jaar blijft de vraag of er alarmbellen moeten gaan rinkelen; bijna 50 punten verschil tussen het minimum en het maximum, is dat een teken dat het fout gaat?

Die vraag is natuurlijk niet zo maar te beantwoorden, maar zoals Charles W. Kyd in één van zijn artikelen aangeeft: probeer een andere invalshoek.

Daarom zijn de AEX-gegevens uitgebreid met een kolom, die aangeeft hoeveel procent de koers op een bepaalde datum is gestegen of gedaald ten opzichte van het jaar daarvoor:

=[@Close]/INDEX([Close];VERGELIJKEN(ZELFDE.DAG([@Datum];-12);[Datum]))-1

Deel de slotkoers van een dag door de overeenkomende dag 12 maanden terug en trek daar 1 vanaf.

Ook nu is de derde parameter van de functie VERGELIJKEN weggelaten; dus soms wordt de slotkoers niet vergeleken met de slotkoers exact 12 maanden terug, maar de dichtstbijliggende datum er voor.

De grafiek hiervan is via de naam rngCloseMutJr weergegeven op het tabblad AEX_Ovz2 van het Voorbeeldbestand.

In de grafiek is te zien, dat fikse negatieve resultaten veelal samen vallen met recessies. Begin 2018 scoort nog steeds flink positief.
Om toch maar een voorspelling te doen: de conclusie lijkt gerechtvaardigd dat de economie positieve perspectieven laat zien.

 

Van globaal naar detail en vv

Excel, , ,
Voorbeeldbestand: GlobaalDetail.xlsxDownload

Mensen zijn verschillend en ook hun informatiewensen zijn verschillend.

Sommige mensen willen globale informatie zien, anderen zoveel mogelijk details.
Voor afdelingen die verantwoordelijk zijn voor rapportages blijft dit altijd een lastig fenomeen. Gelukkig bieden digitale dashboards tegenwoordig een uitkomst: gebruikers van zo’n dashboard kunnen vaak zelf de selectie van voor hen interessante onderwerpen en ook het gewenste detailniveau instellen.

In dit artikel een uitwerking hoe je een grafiek in een dashboard ‘dynamisch’ kunt maken (zie ook een eerder artikel over dynamische grafieken).

Voorbeelddata

Ik ben eigenaar van een energiecentrale (in de vorm van zonnepanelen) en uiteraard wil ik wel volgen wat de energieproductie in de loop van de tijd is.

Voor een rapportage is het dag-niveau wat gedetailleerd, daarom maken we een weekrapportage. Maar ook dan zien we door de hoeveelheid cijfers vaak door de bomen het bos niet meer.

In het tabblad Data van het Voorbeeldbestand vindt u onze weekproductie vanaf week 27 in 2011. De gegevens zijn opgeslagen in een Excel-tabel met de naam tblZon. In de derde kolom staat een formule die het jaar en de week combineert.
In cel C3: =[@Jaar]*100+[@Week]
Ofwel neem het getal uit de Jaar-kolom uit dezelfde regel (daar zorgt de @ voor), vermenigvuldig dat met 100 en tel dan de Week uit die regel er bij op.

Om hierna te weten hoeveel weken er gevuld zijn, wordt dit in een afzonderlijke cel bepaald: in cel I2 staat de formule =AANTAL(tblZon[JrWeek]). Deze turft het aantal numerieke velden in de kolom JrWeek van de tabel tblZon.

De cel I2 heeft de naam MaxAant gekregen; daar zullen we hierna regelmatig naar verwijzen.

NB we hadden in C3 ook de formule =[@Jaar]&[@Week] kunnen gebruiken, maar het resultaat is dan een tekst; dat is voor rapportage-doeleinden minder flexibel.

Grafiek-1

Een grafiek (zie het tabblad Data van het Voorbeeldbestand) laat het verloop in de tijd wel goed zien. De pieken en dalen hieronder komen overeen met de zomers en winters van de afgelopen jaren. In 6,5 jaar heeft de betreffende set van 3 panelen bijna 4.000 kWh opgeleverd.

Maar aangezien de mede-eigenaar van deze energie-centrale meer in details is geïnteresseerd zou het mooi zijn als we de resultaten per jaar afzonderlijk zouden kunnen bekijken.

NB1 de resultaten per week staan uitgezet volgens de linker- ofwel primaire as, de cumulatieve opbrengst  volgens de secundaire as.
Klik rechts op een gegevensreeks en kies dan de Reeks opmaken om te wisselen van as.

NB2 zorg bij het gebruik van 2 assen dat de schaalverdelingen logisch met elkaar overeen komen. In dit geval is de maximale weekproductie op 25 gezet, terwijl het maximum van van de rechter-as op 5.000 is vastgezet.
In dit geval is door de kleur van de cijfers op de assen duidelijk gemaakt welke gegevensreeks bij welke as hoort.

Grafiek-2

Om de details beter te kunnen beoordelen heb ik een nieuwe grafiek gemaakt, die slechts een selectie van het totaal laat zien.

Om snel de jaren te kunnen vergelijken kun je hierbij aangeven met welke week je wilt beginnen (zie tabblad Calc1 van het Voorbeeldbestand).

In cel H2 vul je het volgnummer van de gewenste eerste week in; alle overige cellen bevatten formules en mogen dus niet gewijzigd worden.

Op basis van de invoer in cel H2 (deze heeft de naam Start gekregen) wordt een Excel-tabel met de naam tblZonGed gevuld; er is ruimte gecreëerd voor 55 regels.
In de eerste kolom staat een volgnummer voor de te tonen week.
Daarnaast staat in de kolom JrWeek de formule:
=ALS(Start+[@Nr]-1>MaxAant;””;
VERSCHUIVING(tblZon[[#Kopteksten];[JrWeek]];Start+[@Nr]-1;0))

Ofwel: als in een regel de week boven het maximaal aantal uitkomt, dan wordt de cel leeg (de dubbele aanhalingstekens).
Anders wordt via de functie VERSCHUIVING het weeknummer opgehaald uit de tabel tblZon op het tabblad Data; gerekend vanaf de koptekst JrWeek in die tabel zoveel regels naar beneden als door Start (cel H2) plus het volgnummer uit de betreffende regel wordt aangegeven (nog even met 1 corrigeren).

NB zie voor uitleg over de functie Verschuiving onder andere het artikel Dynamische grafieken.

In de kolommen kWh en Cum staat een vergelijkbare formule; alleen wordt de cel niet leeg gemaakt als de week boven het maximum uitkomt, maar gelijk aan de functie NB(). Die laatste zorgt er voor, dat de grafiek in dat geval geen ‘vreemde’ daling vertoont.

In de kolommen H en J worden nog enkele zaken opgehaald en klaar gezet voor de ‘aankleding’ van de grafiek:

  • in cel J3: =VERSCHUIVING(tblZonGed[[#Kopteksten];[JrWeek]];Eind-Start+1;0)
    op basis van de Start- en Eind-week wordt de naam van de week opgehaald uit de tabel van het tabblad Calc1
  • in cel H4: =”(van week “&TEKST(J2;”0000-00″)&” t/m “&TEKST(J3;”0000-00″)&”)”
    diverse teksten worden aan elkaar gekoppeld door middel van het &-teken; de begin- en eind-week worden daarbij opgemaakt met een opmaakcode, zodat er een streepje tussen het jaar en de week komt
  • in cel H5: =”Opbrengst zonnepanelen  set 1″&TEKEN(13)&Tekst
    ook hier worden teksten gekoppeld, in dit geval gescheiden gescheiden door een code 13, die er voor zorgt dat het vervolg op een nieuwe regel komt

Maak een titel aan in de grafiek, klik daarin dubbel en tik dan in de Formulebalk in =Titel

Grafiek-3

In een dashboard is het niet fraai als gebruikers ergens iets moeten intypen. Daarom gaan we er voor zorgen, dat op een meer intuïtieve manier de grafiek kan worden aangepast.

Allereerst maken we weer een set aan met basis-gegevens (zie tabblad Calc2 in het Voorbeeldbestand). We kunnen daar de begin-week opgeven en het aantal te tonen weken.

In plaats van het aanmaken van een hulptabel zoals bij Grafiek-2 leggen we alle voor de grafiek benodigde gegevens vast in zelf-gedefinieerde namen:

  1. kies in de menu-tab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. de eerste naam wordt WkCalc2
  3. en de bijbehorende verwijzing:
    =VERSCHUIVING(tblZon[[#Kopteksten];[JrWeek]];Calc2!Start;0;Aantal)
    De laatste parameter (Aantal) zorgt er voor, dat het resultaat niet 1 cel is, maar een heel bereik.
  4. op dezelfde manier voegen we nog de naam DataCalc2 toe met de verwijzing
    =VERSCHUIVING(tblZon[[#Kopteksten];[ kWh]];Calc2!Start;0;Aantal)
  5. en dan nog de naam CumCalc2 met
    =VERSCHUIVING(tblZon[[#Kopteksten];[ Cum]];Calc2!Start;0;Aantal)
  6. bij het aanmaken van de grafiek worden nu als verwijzingen naar de benodigde reeksen deze nieuwe namen gebruikt.
    LET OP: plaats ook een verwijzing naar een tabblad of werkmap voor de naam (inclusief uitroepteken), bijvoorbeeld voor de x-waarden =Calc2!WkCalc2

Door nu onder de grafiek schuifbalken te plaatsen, die gekoppeld zijn aan de Start– en Aantal-cellen van het tabblad Calc2 ontstaat er een handige, interactieve grafiek (zie het tabblad DashBoard van het Voorbeeldbestand).

  1. kies in de menu-tab Ontwikkelaars in het blok Besturingselementen de optie Invoegen.
  2. klik dan binnen het blok Formulierbesturingselementen op de optie Schuifbalk
  3. geef met de cursor in de sheet aan waar de balk moet komen.
  4. daarna kan na rechts-klikken op de schuifbalk het besturingselement opgemaakt worden
  5. maak een koppeling met de cel die gewijzigd moet worden door de schuifbalk
  6. vul een minimum- en een maximumwaarde in
  7. vul de 2 wijzigingswaarden in. De eerste is de stap waarmee de waarde in de cel moet wijzigen als op de pijltjes aan de zijkant geklikt wordt; de tweede als in de balk links of rechts van het schuifje wordt geklikt

 

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

 

Gantt-grafiek

Excel, , , ,
Voorbeeldbestand: Gantt.xlsxDownload

Henry Laurence Gantt ontwikkelde de Gantt-grafiek rond 1910.
In zijn werk als werktuigkundig ingenieur, managementconsultant en bedrijfsadviseur werd de Gantt-grafiek gebruikt als een visueel hulpmiddel om de planning en voortgang van een project te laten zien. In die tijd werd dit gezien als een opzienbarende innovatie.

Voor (complexe) Gantt-grafieken worden veelal specialistische (project-)programma’s gebruikt, maar voor het gewone werk kunnen we met Excel ook al aardig vooruit.

Aan de hand van een simpele project-opzet zullen we 2 mogelijkheden voor een Gantt-grafiek bekijken.

Project-data

Hiernaast ziet u de gegevens van een fictief project (zie het tabblad ProjData in het Voorbeeldbestand).
Per stap en substap leggen we een volgnummer vast, samen met een omschrijving, een begindatum en het aantal geplande dagen dat de stap duurt (alleen op het laagste niveau).

Uiteraard beïnvloeden de stappen elkaar. In het voorbeeld zijn dat de volgende zaken:

  1. de startdatum van het project leggen we vast in cel D3
  2. in cel D4 komt de echte startdatum van de eerste hoofd-stap, de Brainstorm-sessies. We gebruiken de functie WEEKDAG om te controleren of D3 niet in een weekend valt. Als D3 een zondag is dan is het resultaat van WEEKDAG een 1, bij maandag een 2 etc.
  3. er zijn 2 brainstormsessies, die parallel plaats vinden. De startdatums daarvan (cellen D5 en D6) zijn gelijk aan D4.
  4. In E5 en E6 ligt vast hoeveel dagen deze sessies in beslag nemen.
  5. de einddatum van deze 2 substappen wordt bepaald met de functie WERKDAG. Zo staat in F5 de formule =WERKDAG(D5;E5-1)
    Deze functie bepaalt de einddatum door bij de startdatum (D5) een aantal dagen op te tellen (E5); de functie telt dagen van het weekend daarbij niet mee.
    Omdat op de startdatum ook al gewerkt wordt, verlagen we het aantal te werken dagen met 1.
  6. De afronding van de brainstorm vindt plaats op de eerste werkdag nadat de laatste sessie klaar is; in cel D7 staat daarom de formule =WERKDAG(MAX(F5:F6);1)
  7. De ontwerpfase begint als de brainstorm voorbij is; eerst het Functioneel Ontwerp, daarna het Technisch Ontwerp en vervolgens een Eindoverleg om alle details door te nemen.
    De opzet van de cellen D8:D11 mag dan ook duidelijk zijn.
  8. De Bouw start na de afronding van het Ontwerp (cellen D12 en D13).
  9. Deel2 van de bouw kan pas beginnen na afronding van Deel1 (cel D14: =WERKDAG(F13;1)).
  10. Wel kan de bouw van de Schil parallel aan de andere bouwactiviteiten plaats vinden (cel D15 is gelijk aan D12).
  11. De daadwerkelijke oplevering vindt plaats nadat de laatste bouwactiviteit is afgerond; in D16 staat de formule =WERKDAG(MAX(F13:F15);1)

NB de functie WERKDAG kent nog een derde parameter, Vakantiedagen. Leg ergens in uw werkmap een overzicht vast van die dagen, dat er voor het project niet gewerkt kan worden en voeg deze reeks als derde parameter toe aan alle WERKDAG-functies (zie een voorbeeld op het tabblad ProjData).

Gantt-grafiek 1

Op basis van de gegevens uit het tabblad ProjData van het Voorbeeldbestand gaan we nu een grafiek maken. Wel hebben we dan nog 2 hulpkolommen nodig:

  • in kolom G komt de startdatum van de betreffende stap minus 1 (waarom dat zullen we hieronder zien; datums zijn voor Excel gewoon getallen dus in G3 komt de formule =D3-1)
  • in kolom H komt het verschil in kalenderdagen tussen de start- en einddatum van iedere stap (in H3 staat dan de formule =F3-D3+1; met 1 gecorrigeerd omdat de startdatum ook meedoet)

Dan kunnen we de grafiek gaan opbouwen:

  1. selecteer de cellen G3:G16
  2. kies in de menutab Invoegen in het blok Grafieken de optie Staaf
  3. en dan bij 2D-staaf de 2e optie (Gestapelde staaf). Het eerste gedeelte van de grafiek is klaar.
  4. selecteer de cellen H3:H16 en Kopieer deze (Ctrl-C)
  5. klik ergens in de grafiek van punt 3 en Plak (Ctrl-V)
  6. de legenda kan weg: klik er op en druk op Delete
  7. de basis-as moet nog aangepast worden:
    * klik met de rechtermuisknop ergens in de grafiek
    * kies de optie Gegevens selecteren
    * klik op de button Bewerken onder Horizontale aslabels
    * selecteer de cellen B4:C16 en klik op OK
    * nog een keer OK om bij de grafiek terug te komen
  8. eigenlijk willen we alleen de tweede staaf zien; de eerste geeft het verloop tot aan de startdatum weer (vandaar de minus 1 in kolom G):
    * klik rechts op één van de staven, die ‘weg’ moeten
    * kies de optie Gegevensreeks opmaken
    * kies onder Opvulling de optie Geen opvulling en dan de button Sluiten
  9. Nog één aanpassing. De volgorde van de stappen is (voor ons) niet logisch:
    * klik rechts op de Categorie-as
    * kies de optie As opmaken
    * vink de optie Categorieën in omgekeerde volgorde aan en klik op de button Sluiten

In het tabblad Gantt1 ziet u het resultaat van bovenstaande exercitie (met nog enkele aanpassingen, die de grafiek overzichtelijker maken).

NB kijk wat er gebeurt, als je in cel D3 van het tabblad ProjData een andere startdatum voor het project invoert.

Gantt-grafiek 2

Deze Gantt-grafiek is op een heel andere manier tot stand gekomen (het is geen echte grafiek, maar een inkleuring van Excel-cellen; zie tabblad Gantt2 van het Voorbeeldbestand):

  1. het ‘blauwe’ blok zijn allemaal verwijzingen naar het tabblad ProjData; bijvoorbeeld in cel B6 staat de formule =ProjData!B4
  2. in cel H4 staat de formule =D5-5, zodat de ‘grafiek’ niet exact op de startdatum begint, maar 5 dagen eerder.
  3. in I4 staat de volgende dag =H4+1; deze formule is zover naar rechts gekopieerd, dat de einddatum van het project in de rij voorkomt.
  4. in H3 hebben we de Maand zichtbaar gemaakt met de formule =Tekst(H4,”mmm”) en deze formule is ook naar rechts gekopieerd
  5. in regel 2 bepalen we op een vergelijkbare manier het Jaar

Alle overige effecten (verticale lijnen, gekleurde cellen en het al dat niet zichtbaar maken van het jaar en de maand) zijn met behulp van Voorwaardelijke opmaak geïmplementeerd (LET OP deze optie is niet direct beschikbaar; om ongewilde wijzigingen te voorkomen is  het werkblad Gantt2 beveiligd. Verwijder de beveiliging via de menutab Controleren en dan de optie Beveiliging blad opheffen (geen wachtwoord vereist). Vergeet niet daarna de beveiliging weer aan te brengen!):

  1. als de tekst in regel 3 verandert (H$3<>G$3), dan komt er een rand aan de linkerkant van de cellen in de regels 2 t/m 18
  2. als de inhoud van een cel in de 2e en 3e regel gelijk is aan de cel links daarvan (H2=G2) dan krijgt de tekst in die cel een witte kleur en is dus niet meer zichtbaar
  3. de cellen in de regels 4 t/m 18 krijgen een rode linker- en rechterrand als de inhoud van de 4e regel gelijk is aan de datum van vandaag (H$4=VANDAAG()).
  4. ook krijgen de cellen in de regels 4 t/m 18 een groenige achtergrond als de datum in de 4e regel een weekend voorstelt (WEEKDAG(H$4;2)>5; de parameter 2 geeft aan dat de week op een maandag begint)
  5. de cellen in de regels 5 t/m 18 krijgen een bruinige achtergrond als de datum in de 4e regel groter of gelijk is aan de startdatum in kolom D EN de datum in de 4e regel kleiner of gelijk is aan de datum in de kolom E.

Welke methode heeft de voorkeur?

Mijn persoonlijke voorkeur gaat naar de 2e methode uit. Hierbij is ieder facet van de ‘grafiek’ in te stellen.

Dit is meteen ook het grootste nadeel van deze methode. Dus heb je snel een grafiek nodig, gebruik dan methode 1.

Laat in een reactie hieronder weten, welke methode u prefereert.

 

Sparklines

Excel, , ,

Volgens Microsoft:

Een sparkline is een kleine grafiek in een cel van een werkblad met een visuele weergave van de gegevens. Gebruik sparklines om trends in een reeks waarden aan te geven, zoals seizoensmatige stijgingen of dalingen of economische cycli, of om maximum- en minimumwaarden te markeren.

Anders gezegd: de sparkline-grafiek is niet bedoeld om details te onderscheiden, maar vooral om snel bepaalde trends of uitschieters te signaleren.

Hieronder gaan we eerst kijken hoe je een sparkline in je werkmap opneemt en daarna worden enkele extra mogelijkheden belicht en ook een aandachtspunt bij het gebruik van draaitabellen.

Hoe maak je een sparkline?

Laten we een simpel voorbeeld nemen: de inkomsten en uitgaven per maand en het resultaat daarvan. Zie het tabblad InUit in het Voorbeeldbestand.

LET OP de getallen zullen afwijken van de figuur hierboven. Met de functie ASELECTTUSSEN worden iedere keer nieuwe cijfers gegenereerd (druk op F9); dit om de consequenties voor de grafieken makkelijk te kunnen laten zien.

  1. selecteer het blokje getallen, dat in grafieken moet worden weergegeven (in het voorbeeld de cellen C4:N6; dus zonder de verklarende teksten! Een sparkline geeft alleen getallen weer.)
  2. kies in de menutab Invoegen in het blok Sparklines de optie Lijn
  3. in het nieuwe venster, dat automatisch opent, is het gegevensbereik al ingevuld. De plaats van de sparklines moet nog ingevuld worden. In dit geval moeten die achter de gegevens (dus in kolom O) komen; we vullen bij Locatiebereik O4:O6 in.
  4. klik op OK
  5. maak de kolom met de sparklines (in dit geval O) zo breed als gewenst om de resultaten het beste tot hun recht te laten komen.

LET OP het aantal cellen in locatiebereik moet gelijk zijn aan het aantal regels in het Gegevensbereik anders krijg je een foutmelding.

NB1 De sparklines mogen overal in het werkblad staan en ze mogen onder of naast elkaar komen. Maar zoals op de Microsoft-website staat:  “Plaats een sparkline dichtbij de gegevens voor het optimale effect.“

NB2 Je kunt bij punt 1 ook beginnen met de selectie van de locatiecellen (waar moeten de sparklines komen). In stap 3 moet dan het gegevensbereik worden opgegeven.

Sparklines opmaken

Hoewel in bovenstaand voorbeeld het verloop in de tijd al goed zichtbaar is, kunnen we bepaalde kenmerken nog accentueren:

  1. selecteer één van de sparklines; klik bijvoorbeeld met de muis in cel O4
  2. kies in de nieuwe menutab Hulpmiddelen voor sparklines de optie Ontwerpen
  3. vink in het blok Weergeven de opties Hoge punt en Negatieve punten aan
  4. kies in het blok Stijl eventueel een andere voor-geprogrammeerde opmaak

Deze opmaak wordt automatisch tegelijkertijd toegepast op alle drie de sparklines. Dit komt omdat Excel deze drie grafieken in één groep heeft geplaatst (klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat alle drie de sparklines bij elkaar horen).

Bij de inkomsten is de hoogste waarde (Hoge punt) goed, maar bij de uitgaven is dit natuurlijk niet het geval. De toppen moeten een verschillende kleur krijgen, daarom mogen de grafieken niet meer als één groep geselecteerd worden :

  1. selecteer de cellen met sparklines (O4:O6) en kies in de menutab Hulpmiddelen voor sparklines binnen de optie Ontwerpen de optie Groep opheffen
  2. selecteer alleen cel O4; kies binnen de optie Markeringskleur bij Hoge punt een groene kleur
  3. selecteer alleen cel O5; kies binnen de optie Markeringskleur bij Hoge punt een rode kleur
  4. selecteer alleen cel O6; kies binnen het blok Groeperen de optie As/As weergeven

NB Klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat deze sparkline alleen bij gegevens uit regel 4 hoort.

In het tabblad InUit2 van het Voorbeeldbestand is nog een andere optie gebruikt; bij de eerste 2 grafieken is als Type de optie Kolom gekozen:

NB de sparklines worden in Excel als achtergrond van een cel opgenomen. Dat betekent dat we nog steeds andere informatie in de cel kunnen opnemen, bijvoorbeeld toelichtende tekst.

Sparklines en draaitabellen

Wanneer je sparklines achter een draaitabel plaatst kan het voorkomen, dat wanneer deze draaitabel ‘groeit’ door nieuwe informatie die aan de bron wordt toegevoegd, de draaitabel over de sparklines valt.

Een oplossing is om van te voren te zorgen dat de draaitabel al alle mogelijke items bevat, zodat deze nooit meer groter kan worden (tabblad Ovz in het Voorbeeldbestand):

Aan de bron zijn hiertoe lege regels voor Nov en Dec toegevoegd; zie tabblad Data in het Voorbeeldbestand.

NB Het Resultaat staat niet in de bron, maar wordt in de draaitabel berekend:

  1. selecteer in de draaitabel één van de soorten(In of Uit); klik bijvoorbeeld met de muis in cel A5
  2. kies in de menutab Hulpmiddelen voor draaitabellen binnen Opties in het blok Berek. de optie Velden, Items en sets

    Kies dan de optie Nieuw item.
    NB we gaan binnen het veld Soort een nieuw item maken gebaseerd op andere items binnen Soort. De optie Nieuw veld wordt gebruikt om een nieuw veld (naast Soort, Maand en Bedrag) te maken.
  3. in het vervolgvenster geven we het nieuwe item een Naam, bijvoorbeeld Res (Resultaat is al toegevoegd) en als Formule tikken we in: = In-Uit en klikken op Toevoegen en OK