Categorie archief: Excel

Van globaal naar detail en vv

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

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Verder zoeken

Zoeken in Excel blijft een belangrijke en veel gebruikte optie.
Ik heb daar dan ook al enkele keren een artikel met tips over geschreven.

Recent kreeg ik een opmerking op het artikel Alternatief voor Verticaal.zoeken:

Als er dubbele waarden of woorden in een reeks staan waar je de functie vergelijken op los laat dan is het resultaat de positie van de eerste waarde die hij vindt. Betekent dat dan dat je de functie vergelijken moet toepassen op een gegevensreeks met unieke waarden of tekens?

Mijn antwoord hield in, dat hij daar gelijk in had. En dat je dus altijd moet opletten met zoeken in Excel. En dat dit inspiratie was voor een artikel over het zoeken in niet-unieke gegevens.

Dus deze keer wat inspiratie voor uw speurwerk in Excel.

Basisgegevens

Uiteraard hebben we een voorbeeld nodig, waar we wat mee kunnen oefenen.
In het tabblad Data van het Voorbeeldbestand staat een overzicht van personen, die een betaling aan mij hebben gedaan. We hebben afgesproken, dat iedere werkdag iemand een bedrag stort (was het maar waar!).

Het overzicht begint op 1 september (cel C3. De dag van de week is via cel-opmaak zichtbaar gemaakt; druk Ctrl-1).
In de cel daaronder staat de formule:
=WERKDAG(C3;1)

Omdat de gegevens in een Excel-tabel staan (met de naam tblBetalingen) wordt deze formule automatisch in de rest van de kolom doorgevoerd; voeg maar eens een nieuwe persoon onderaan toe.

U ziet dat sommige personen 1 betaling hebben gedaan, anderen 2 en enkelen hebben er 3 gedaan.
Maar hoeveel heeft iedereen nu gestort en wanneer was de laatste betaling per persoon? Tijd voor nadere analyse.

Aantal-analyse

Allereerst moeten we een overzicht hebben wie er allemaal mee betalen:

  1. klik ergens in de tabel
  2. kies dan in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  3. in het vervolgscherm kunt u diverse zaken instellen:
    * we gaan niet filteren, maar kopiëren dus de 2e optie aanvinken
    * het Lijstbereik invullen; waar moeten de gegevens vandaan komen? Wijs met de muis de bovenrand van de eerste kolom aan tot de cursor verandert in een zwarte pijl naar beneden en klik dan
    * in het veld Kopiëren naar moet een verwijzing komen naar de eerste cel waar gegevens moeten worden weggeschreven
    * en als laatste aangeven dat we alleen unieke waarden willen overhouden
    * klik dan op OK

Er blijken tot nu toe 11 betalers te zijn; zie tabblad Ovz1 van het Voorbeeldbestand.

Het aantal betalingen per persoon is nu snel gevonden. In cel C3 staat de formule:
=AANTAL.ALS(tblBetalingen[Naam];B3)

Nog even naar beneden kopiëren, et voilà.

Twee personen hebben dus al 3 keer betaald, vier al 2 keer en 5 mensen nog maar 1 keer.

Bedrag-analyse

We gaan nu via Zoeken analyseren hoeveel iedereen heeft betaald (ja, ik weet het: mbv. een draaitabel is dat in een mum van tijd gebeurd!).

Zoals hierboven al opgemerkt kan Excel alleen maar naar unieke sleutels zoeken (dit in tegenstelling tot bijvoorbeeld Access, dat bij het zoeken naar Piet 2 verschillende records zou opleveren).

We moeten dus alle regels in Excel uniek maken; dat doen we door aan de namen een volgnummer te koppelen.
Aan de basistabel zijn daarom 2 hulpkolommen toegevoegd (zie tabblad Data in het Voorbeeldbestand):

  1. in kolom E (Hulp1) staat de formule
    =AANTAL.ALS(VERSCHUIVING(tblBetalingen[[#Kopteksten];[Naam]];1;0;RIJ()-RIJ(tblBetalingen[[#Kopteksten];[Naam]]));[@Naam])
    Turf het aantal keren, dat de Naam uit de betreffende rij voorkomt in het bereik dat door Verschuiving wordt bepaald.
    De Verschuiving is spannender:
    * het bereik begint in de cel waar de kop van de naam-kolom staat (klik bij het invullen van de formule op die cel en de verwijzing wordt automatisch gegenereerd)
    * maar start 1 regel lager
    * en 0 kolommen naar rechts of links
    * en de lengte van het bereik is gelijk aan de Rij waarin de formule staat minus de rij waar de kolomkop staat
    * in de eerste regel is het bereik dus maar 1 cel groot, in de tweede regel 2 cellen etc.
  2. in kolom F (Hulp2) staat:
    =[@Naam]&[@Hulp1]
    Ofwel: koppel de Naam uit de betreffende rij aan het volgnummer uit Hulp1

De gegevens in Hulp2 zijn nu altijd uniek, ook na uitbreiding van de tabel. Dus op die kolom kunnen we nu onze zoekacties uitvoeren. Aangezien Verticaal.zoeken niet ‘naar links’ kan zoeken gebruiken we de formule met de Index-Vergelijken-combinatie (zie het betreffende artikel daarover).

In het Voorbeeldbestand in het tabblad Ovz1 staat in cel D16 de formule
=ALS.FOUT(INDEX(tblBetalingen[Bedrag];VERGELIJKEN($B16&D$15;tblBetalingen[Hulp2];0));””)

Vergelijken zoekt in de Hulp2-kolom op welke plaats de combinatie van cel B16 en cel D15 (naam met volgnummer dus) staat. De nul geeft aan, dat we een exacte vergelijking willen uitvoeren.
Als we die plaats weten dan wordt met behulp van Index het bijbehorende Bedrag opgehaald.
Omdat de naam of de combinatie met het volgnummer niet hoeven voor te komen, zorgen we er voor dat, als de Index-formule een fout oplevert, er in de cel niets komt te staan (de dubbele aanhalingstekens).

LET OP in principe kan de formule uit D16 naar rechts en naar beneden gekopieerd worden. Doe dit niet door de vulgreep rechts onder van cel D16 te verslepen, maar gebruik de toets-combinaties Ctrl-C en Ctrl-V.

In kolom C nog even een Som-formule plaatsen en we weten hoeveel iedereen in totaal heeft betaald.

NB we zijn er hier van uit gegaan, dat er maximaal 5  betalingen per persoon plaats vinden. Klopt dit niet (meer) dan moeten er nog kolommen worden toegevoegd. Vergeet niet de Som-formule in kolom C aan te passen!

Datum-analyse

Op welke dagen zijn de betalingen verricht?

Op exact dezelfde manier als hiervoor halen we nu de Datum op in plaats van het Bedrag.
Deze keer plaatsen we in kolom C de Max-formule om de laatste betaaldatum per persoon te achterhalen.

Alternatieven zonder hulp-kolommen

Voor de liefhebbers staan in het tabblad Ovz2 van het Voorbeeldbestand nog 2 alternatieven, waarbij formules worden gebruikt die geen hulpkolommen nodig hebben.

In cel D3 wordt aan de hand van de naam in B3 en het volgnummer in D2 op de volgende manier het bijbehorende bedrag opgehaald:
={ALS.FOUT(
INDEX(tblBetalingen[Bedrag];
KLEINSTE(
(tblBetalingen[Naam]=$B3)*(RIJ(tblBetalingen[Naam]));
D$2 + AANTAL.ALS(tblBetalingen[Naam];”<>”&$B3)) –
RIJ(tblBetalingen[[#Kopteksten];[Naam]])
)
;””)}

Gebruik in de menutab Formules in het blok Formules controleren de optie Formules evalueren om te onderzoeken hoe de formule werkt.

NB Deze formule is ingevoerd door in plaats van op Enter op Ctrl-Shif-Enter te drukken, de zogenaamde CSE-methode.

Wil je de CSE-methode vermijden dan wordt de formule:
=ALS.FOUT(INDEX(tblBetalingen[Bedrag];INDEX(KLEINSTE((tblBetalingen[Naam]=$B9)*(RIJ(tblBetalingen[Naam]));D$2+AANTAL.ALS(tblBetalingen[Naam];”<>”&$B9))-RIJ(tblBetalingen[[#Kopteksten];[Naam]]);0));””)

Zie cel D9 in het tabblad Ovz2 van het Voorbeeldbestand.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

VBA-routines versnellen

Wanneer u net begint met het ontwikkelen van VBA-routines om werkzaamheden te automatiseren, zult u versteld staan van de tijdwinst die dit kan opleveren.

Maar ….  als dan de hoeveelheid gegevens, waarmee u werkt, gaat groeien dan kan het zo maar zijn dan zo’n routine tientallen secondes bezig is en dat u denkt: kan dat niet sneller!

Het antwoord is (hoogst waarschijnlijk): JA, dat kan zeker sneller.

In dit artikel enkele tips voor efficiëntere VBA-routines.

Gebruik van Select

Wanneer u bepaalde handelingen in Excel wilt automatiseren, is de macro-recorder van VBA een uiterst handig hulpmiddel. U neemt de toetsaanslagen op en volgende keer kunt u met één druk op een knop diezelfde toetsaanslagen herhalen. Vaak wordt zo’n recorder-opname dan gebruikt als basis voor een uitgebreidere routine.

In dit voorbeeld geldt dat ook (zie Blad1 in het Voorbeeldbestand):

  • we hebben een tabel (met de naam Tabel1) met 2 kolommen: in de eerste staan maanden en in de tweede worden willekeurige bedragen met 2 decimalen gegenereerd, liggend tussen 1 en 2:
    =ASELECTTUSSEN(100;200)/100
  • het eerste bedrag (cel C3) heeft de naam Bedrag1 gekregen, terwijl het bereik van alle bedragen de naam Bedr heeft.
  •  in kolom F worden het aantal regels, het totaalbedrag en het gemiddelde bepaald. Het tweede gemiddelde is gelijk aan het  totaalbedrag gedeeld door het aantal; een simpel voorbeeld van een ingebouwde controle.
  • als voorbeeld heb ik een VBA-routine gemaakt (gebaseerd op een macro-opname), die ook dezelfde berekeningen uitvoert.
    Deze routine selecteert cel Bedrag1  en “loopt” zolang er geen lege cel is naar beneden; ondertussen wordt het aantal geturfd en een lopend totaal bepaald:
  • op het einde van de VBA-routine zorgen we dat de cursor weer in cel A1 komt en worden de resultaten via een pop-up zichtbaar gemaakt.
    Ook de verstreken tijd is in de pop-up opgenomen.
  • klik op de button Select in het Voorbeeldbestand om de VBA-routine uit te voeren.

NB1 door de rechte haken rond een naam weet VBA, dat het een naam in de Excel-sheet is, dus niet de naam van een VBA-variabele.

NB2 zo ziet een info-scherm op de MAC er uit; de PC-versie oogt anders.

NB3 mijn MAC heeft dus ruim 11 seconden nodig voor de 1.000 verplaatsingen en berekeningen! Op een PC duurt het (uiteraard afhankelijk van de soort machine) nog geen seconde.

NB4 wil je de gebruikte routine zien: klik Alt-F11 of ga via de menutab Ontwikkelaars naar Visual Basic.

Gebruik van Select-2

Het grootste gedeelte van de benodigde tijd van de vorige VBA-routine gaat zitten in het fysiek verplaatsen van de cursor (via Offset).

Met behulp van het VBA-commando Application.ScreenUpdating = False kunnen we er voor zorgen, dat het verversen van het scherm niet meer plaats vindt. Het resultaat is, dat de MAC binnen een halve seconde klaar is!

Klik op de button Select2 om de alternatieve VBA-routine uit te voeren.

NB vergeet niet op het einde van zo’n routine de scherm-verversing weer aan te zetten: Application.ScreenUpdating = True

Gebruik van Range

Veel Excel-ontwikkelaars vinden het gebruik van de Select-methode uit den boze. Bij een prima alternatief wordt het belangrijkste gedeelte van de routine:

De berekeningen worden uitgevoerd voor iedere cel in het bereik Bedr. De computer gaat nu in sneltrein-verhaal langs de inhoud van de cellen zonder dat er een verplaatsing van de cursor nodig is.

De MAC is nu binnen 1/100 van een seconde klaar! Test de routine door op de button Range te klikken.

Gebruik van een array

In het vorige voorbeeld hebben we gebruik gemaakt van een Excel-bereik (in dit geval met de naam Bedr).
Bij heel grote bestanden kan er tijdwinst geboekt worden door dit bereik eerst in een aparte array (rij, reeks, matrix) te plaatsen:

  • het bereik met de naam Bedr (de truc met de rechte haken werkt nu niet meer) wordt cel voor cel in een array aBedr gestopt
  • voor ieder element van de array wordt het lopende totaal bepaald (Ubound=UpperBound=bovengrens)

Sneller dan het geluid!? De routine heeft minder dan 1/10.000 seconde nodig.

Gebruik van Tabel

Een alternatief voor de vorige oplossing is:

  • de kolom Bedrag uit Tabel1 wordt in de array aBedr gestopt
  • de bovengrens van de array plaatsen we in de variabele N (in het vorige voorbeeld moet de bovengrens iedere keer opnieuw worden bepaald; niet efficiënt)

Gebruik van Tabel-2

In een variant van de vorige oplossing schrijven we de resultaten ook nog weg in de Excel-sheet (in cellen van kolom G met de namen Aantal, Totaal en Gem1_):

NB1 cel G5 heeft NIET de naam Gem1. Er bestaat al een cel met die naam, namelijk de cel in kolom GEM, rij 1. Dit wordt vaak opgelost door een underscore er voor of er achter te zetten.

NB2 doordat er cellen in de sheet worden aangepast gaat Excel alle formules doorrekenen; dus alle bedragen in Bedr veranderen NADAT de VBA-berekening is uitgevoerd.

Effect van groter bestand op de berekeningen

Door Tabel1 uit te breiden naar bijvoorbeeld 100.000 regels is het verschil in benodigde tijd beter te beoordelen. Trek daartoe de vulgreep van de tabel rechtsonder naar beneden.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Excel en kaarten

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


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

DB-functies

Data-analyse: een belangrijk item voor menig bedrijf en daarom zijn diverse artikelen op deze site geschreven met dat onderwerp in het achterhoofd.
De gereedschapskist van een data-analist moet flink gevuld zijn om zijn of haar werk goed, effectief en efficiënt uit te kunnen voeren.

Naar aanleiding van het artikel SOMPRODUCT: meer dan SOM en PRODUCT kwam de opmerking langs om in dat kader ook aandacht te besteden aan de DB-functies, ofwel de database-functies van Excel.

Hieronder zal ik enkele mogelijke tools in een analyse-traject naast elkaar zetten: de draaitabel, SOMPRODUCT en de DB-functies. Op die manier wordt vanzelf duidelijk wanneer je welk gereedschap het best kunt inzetten.

Database

In dit artikel gebruiken we een omzet-overzicht, gesplitst naar Regio, Product en Datum (zie het tabblad VbData in het Voorbeeldbestand).
De database is opgeslagen als Excel-tabel met als naam tblOmzet; voor de voordelen zie de artikelen over tabellen deel 1 en deel 2.

Deze database bevat 1.000 records (regels) en een kopregel, waarin de namen van de velden (kolommen) staan.

Analyse dmv draaitabellen

Wat mij betreft is dit in verreweg de meeste gevallen het handigste hulpmiddel voor data-analyse.

Selecteer één van de cellen in de database, klik op Invoegen en dan Draaitabel en bevestig met OK.

Sleep Regio naar Rijlabels, Product naar Kolomlabels en de Omzet naar het Waardegebied.
De laatste stap nog 2 keer herhalen en Som veranderen in Aantal, respectievelijk Gemiddelde.
Nog wat opmaak regelen  en in een mum van tijd hebben we een goed inzicht in de verdeling van de omzet naar de diverse categorieën (zie het tabblad Draai in het Voorbeeldbestand).

Een andere draaitabel laat de verdeling over de maanden zien. Daartoe is de Datum in het vak Rijlabels gesleept. Even rechtsklikken op één van de datums in het overzicht dat ontstaat, Groeperen kiezen (zie Groeperen in een draaitabel), alleen de Jaren en de Maanden selecteren en OK.

Via opmaak van de draaitabel kan deze veelal in de gewenste vorm gebracht worden. Is dat niet het geval dan kan de informatie met de functie DRAAITABEL.OPHALEN nog anders weergegeven worden (zie het gelijknamige artikel).

Analyse met SOMPRODUCT

In het artikel SOMPRODUCT: meer dan SOM en PRODUCT heb ik al laten zien hoe de functie Somproduct gebruikt kan worden om gegevens uit een database op te halen.

Deze methode is ontzettend flexibel is: op iedere willekeurige plaats, in iedere willekeurige volgorde kan informatie verzameld worden uit de database.
Maar … de gebruikte formules zijn niet altijd op het eerste gezicht duidelijk, zie bijvoorbeeld cel C4 in het tabblad SomProd van het Voorbeeldbestand:

=SOMPRODUCT((tblOmzet[Regio]=$C$2)*(tblOmzet[Product]=$C$3)*tblOmzet[Omzet])

NB een groot nadeel van deze methode is, dat je wel al moet weten welke Regio’s en welke Producten in de database voorkomen. Een draaitabel laat automatisch alle voorkomende namen zien.

Analyse met DB-functies

Excel kent diverse database-functies; de naam daarvan beginnen allemaal met DB (in de Engelstalige versie alleen D).
De naam geeft vaak goed weer waar de functie voor bedoeld is: DBSOM, DBAANTAL, DBGEMIDDELDE, DBMAX, DBMIN etc.

Alle database-functies verwachten 3 parameters/argumenten: allereerst waar de database te vinden is (het betreffende cel-bereik), dan het veld (kolomnaam) waarvan gegevens moeten worden verzameld en als derde een bereik waarin criteria zijn aangegeven waaraan de gezochte records moeten voldoen.

Op het tabblad DB1 van het Voorbeeldbestand staat in cel F3 een formule, waarmee de totale omzet wordt bepaald uit Regio Noord voor het Product met de naam Vier:
=DBSOM(tblOmzet[#Alle];tblOmzet[[#Kopteksten];[Omzet]];Criteria)

De 1e parameter (tblOmzet[#Alle]) verwijst naar alle cellen in de omzet-tabel. Voegt u records toe dan zal deze verwijzing de toevoegingen automatisch meenemen.

LET OP tblOmzet is als parameter niet voldoende; de kopregel met veldnamen wordt dan niet meegenomen.

Achter de eerste punt-komma komt de 2e parameter (tblOmzet[[#Kopteksten];[Omzet]]); een rechtstreekse verwijzing dus naar het veld Omzet.

NB in plaats van een verwijzing kun je ook een letterlijke tekst intikken, in dit geval “Omzet” (denk aan de aanhalingstekens). Maar let op: wanneer de veldnaam in de kopregel van de database wordt aangepast, dan werkt deze formule niet meer!
Ook is het toestaan om als 2e parameter een volgnummer van de betreffende kolom mee te geven (in dit geval zou dit 4 zijn). Maar ook hier opletten: komt er een kolom in de database bij, dan werkt de formule niet meer naar behoren.

Achteraan staat de 3e parameter (Criteria). Dit is een zelf-gedefinieerde bereiknaam,  een verwijzing naar de cellen B3:C4.

In het tabblad DB1 staan ook 2 gegevenstabellen, die gebaseerd op DB-functies een soort draaitabel nabootsen; informatie over gegevenstabellen vindt u in het betreffende artikel.

In het tabblad DB2 van het Voorbeeldbestand heb ik een methode uitgewerkt waarmee het ook mogelijk is om de totalen van een bepaalde maand te selecteren. Zoals u ziet kun je in de criteria ook twee keer hetzelfde veld opgeven.

De voordelen van de DB-methode liggen er vooral in, dat de criteria voor de selectie apart  en duidelijk herkenbaar vastliggen.

De selectiecriteria hoeven niet beperkt te blijven tot 1 regel.
In het tabblad DB3 van het Voorbeeldbestand staat een voorbeeld waarbij de criteria uit 2 regels bestaan (waarbij het bereik de naam Crit2 heeft gekregen). In dit geval worden alle records van de Regio Noord meegenomen, waarbij de Productnaam Een OF Twee is.

Bij het opgeven van criteria moet u het volgende altijd goed in het achterhoofd houden:

  • aan de voorwaarden in dezelfde regel  moet tegelijkertijd voldaan worden (in het voorbeeld Regio=Noord EN Product=Een)
  • voorwaarden in verschillende regels selecteren díe records, die aan de ene regel OF aan de andere OF aan allebei voldoen

LET OP1 in de 2e regel van het voorbeeld mag u niet Noord weglaten, want dan zal Excel alle records selecteren, waarbij (Regio gelijk is aan Noord EN Product gelijk aan Een) OF (Product gelijk is aan Twee).

LET OP2 als de criteria meer dan 1 regel beslaan vergeet dan niet alle regels te vullen; eventueel met dezelfde voorwaarden.

NB een leeg veld binnen de criteria betekent dat er geen voorwaarde aan dat veld wordt opgelegd (in die regel).

In dit voorbeeld vooral ziet u de flexibiliteit van het gebruik van de DB-functies.
Probeer dit maar eens snel in een draaitabel klaar te krijgen! Zeker als je later bedenkt dat de grens van de Omzet beter bij 1250 kan liggen.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: