Tagarchief: Draaitabel

Grafiek automatisch schalen en labels toevoegen



Herkent u de frustratie? Heb je net een grafiek ‘netjes’ gemaakt door de assen zodanig te schalen dat de gegevens mooi verdeeld zijn over de grafiek, klopt er niets meer van als er een andere of uitgebreidere reeks gegevens worden weergegeven.
Of je hebt ‘met de hand’  alle labels bij de punten aangepast (ja, ik gebruik nog een oude Excel-versie), dan gaan die bij een andere reeks de mist in.

Dus deze keer een anti-frustratie-artikel: 2 VBA-routines, die bovenstaande problemen oplossen.

Test-gegevens

Om een voorbeeld te kunnen maken had ik wat test-gegevens nodig. Meestal maak ik die door wat random-getallen of teksten te genereren.
Maar ik herinnerde me ineens dat ik ooit eens ergens gelezen had dat je ook test-gegevens op een site kon laten maken.
Na wat zoeken kwam ik de website mockaroo.com tegen.

Na een paar pogingen had ik een mooi lijstje met voorbeeld-gegevens (zie het tabblad Basis in het Voorbeeldbestand).

  1. bij Soort heb ik gekozen voor financiële markten (Mockaroo kiest dan willekeurig uit 2 mogelijkheden)
  2. de Datum ligt tussen 1 jan 2018 en 31 dec 2018
  3. bij Omschr heb ik voor willekeurige voornamen gekozen
  4. het Bedrag ligt tussen 1000 en 2000

Om de rest wat overzichtelijk te houden heb ik 20 regels daaruit geselecteerd (zie het tabblad Basis2) en een beetje aangepast. Deze test-gegevens staan klaar in een Excel-tabel met de naam Tabel1.

Grafiek

Om het verloop van de bedragen per Soort handig te kunnen weergeven, heb ik een draaitabel gemaakt op basis van de gegevens in Tabel1 (zie het tabblad Result in het Voorbeeldbestand).

Op basis van deze draaitabel kun je makkelijk een Draaigrafiek maken, maar dan komt Omschr automatisch op de x-as en ik wil deze gebruiken als Labels in de grafiek.

Dus maar een ‘gewone’ grafiek gemaakt met op de x-as de datums en op de y-as de bedragen.

Hiernaast staat de grafiek waarbij er geen Soort is geselecteerd (dus 20 punten). De x-as begint automatisch bij 19 januari omdat dat de kleinste datum is; iets vergelijkbaars geldt voor het einde van de as.
Excel heeft ook de y-as automatisch geschaald.

Assen handmatig aanpassen

Om de assen handmatig te schalen klik je met de rechtermuisknop op een as en pas je het minimum en het maximum aan (het vinkje daarvoor zal dan verdwijnen ten teken dat er geen automatische schaling hoeft plaats te vinden).

Maar iedere keer als er in de draaitabel een andere Soort wordt gekozen moeten er aanpassingen worden doorgevoerd.

Assen automatisch aanpassen

Om deze schaal-aanpassingen met VBA geautomatiseerd te kunnen doorvoeren moeten we ergens vastleggen wat het gewenste minimum en maximum is.

In het Voorbeeldbestand op het tabblad Result staat in cel G2 de formule =MIN(B:B)
Oftewel bepaal de kleinste waarde/datum in kolom B. In G3 wordt met de functie MAX de grootste waarde vastgelegd. Maar het is mooier als de x-as op de eerste van een maand begint: in cel H2 staat daartoe de formule =DATUM(JAAR(G2);MAAND(G2);1). Deze cel heeft de naam x_min gekregen.

Het maximum van de x-as moet de eerste dag van de volgende maand zijn: cel H3 (met de naam x_max) heeft daarom de formule =DATUM(JAAR(G3);MAAND(G3)+1;1)

NB ook als G3 een datum in december bevat, werkt deze formule goed; Excel zal automatisch het jaar ophogen en de maand gelijk maken aan januari.

In kolom J worden de minimum- en maximum-bedragen uit kolom D bepaald. Cel K2 bevat de formule =GEHEEL(J2/100)*100, zodat het minimum voor de y-as het eerste honderdtal is, kleiner dan de waarde in J2.
In cel K3 bepalen we de bovengrens voor de y-as: =(GEHEEL(J3/100)+1)*100

Nu hebben we nog een VBA-routine nodig, die de handelingen voor het aanpassen van de assen van ons overneemt.

In het tabblad Result is een knop met de naam Assen schalen toegevoegd waarmee deze macro/subroutine kan worden uitgevoerd.

Labels toevoegen

In de oudere versies van Excel is het niet mogelijk om automatisch extra informatie (zoals de Omschr uit het voorbeeld) als Labels aan een punt toe te voegen. Handmatig kan dit wel:

  1. klik rechts op de lijn in de grafiek
  2. kies de optie Gegevenslabels toevoegen
  3. klik op één van de labels; als dan alle labels zijn geselecteerd nogmaals op het label klikken
  4. de tekst van dit label kan nu aangepast worden

Maar als er nu in de draaitabel een andere Soort wordt gekozen dan kloppen de handmatig ingevoerde labels niet meer!

Daar komt VBA weer goed van pas:

Het spannende is hier wat er gebeurt met de variabele xVals.

Wanneer je in de grafiek op de lijn klikt verschijnt er in de formulebalk een formule die begint met =REEKS(
Daarna komt de cel, die de naam van de reeks bevat, dan de x-waarden en daarna de y-waarden.

Aangezien VBA Engelstalig is bevat xVals na het uitlezen van de formule van de Collection: =SERIES(Result!$B$4,Result!$B$6:$B$25,Result!$D$6:$D$25,1)

NB1 binnen Excel (met Nederlandse instellingen) worden de parameters gescheiden door een ; (punt-komma), maar binnen VBA is dit een , (komma).

NB2 de 1 op het einde geeft aan, dat deze lijn de eerste reeks is die in de grafiek geplot moet worden. We hebben maar één lijn, dus deze parameter heeft hier geen invloed.

We hebben in het vervolg van de routine alleen de reeks van de x-waarden nodig, de B6:B25.
Daarom nemen we eerst alles wat rechts van de eerste komma staat:
xVals = Mid(xVals, InStr(xVals, “,”) + 1)
en dan alles links van de volgende, dan weer eerste, komma:
xVals = Left(xVals, InStr(xVals, “,”) – 1)

xVals bevat nu  het bereik Result!$B$6:$B$25

LET OP als de naam van het tabblad zelf ook een komma bevat dan wordt de formule wel wat ingewikkelder (zie VBA).

De For-Next-loop gaat dan alle elementen van dit bereik langs en plaatst de waarde rechts van de datum in het betreffende label.

Uiteraard is deze routine ook via een knop uit te voeren op het tabblad Result van het Voorbeeldbestand.

Titel van grafiek

De titel van de grafiek is dynamisch gemaakt:

  1. in cel N2 staat de formule:  =”Bedragen van “&C2
    Oftewel koppel de inhoud van cel C2 aan de tekst Bedragen van.
  2. zorg dat de grafiek een of andere titel heeft
  3. klik ergens in de titel
  4. tik dan in de formulebalk =N2
  5. en druk op Enter

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

 

 

Olympische spelen



De Giro voorbij, de Tour moet nog komen, Nederland niet op het WK: dan maar nog even dromen over de afgelopen winterspelen.

Een poosje geleden kwam ik een database tegen met daarin alle medaillewinnaars van de (moderne) Olympische spelen.

Een mooie aanleiding om eens te kijken of we wat leuke overzichten kunnen maken (met behulp van draaitabellen natuurlijk).

Basis-materiaal

De gegevens over alle Olympische Spelen zijn verzameld door Shane Devenshire. Die heeft in zijn sheet ook enkele voorbeelden opgenomen.

“The latest version of my free Excel Olympic Database is complete and may be downloaded by using the following link:
http://bit.ly/2BWvAte
Please share the link not the file because I want to know how often this file is downloaded so I can decide if I should continue updating it in the future. The 2016 version had very few downloads so I am thinking of discontinuing the project in the future.”

Ik heb een nieuw, aangepast Voorbeeldbestand gemaakt, maar vergeet niet óók op bovenstaande link te klikken.

Shane gebruikt op zijn welkom-pagina  een onderdeel van Excel, dat ik nog niet vaak ben tegengekomen: SmartArt-afbeeldingen.

Volgens Microsoft: “Een SmartArt-graphic is een visuele weergave van uw gegevens en ideeën. U maakt er een door een indeling te kiezen die past bij uw bericht. In sommige indelingen (zoals organigrammen en Venn-diagrammen) worden bepaalde soorten gegevens weergegeven, terwijl andere indelingen alleen een uitbreiding zijn van de weergave van een lijst met opsommingstekens.
Als u SmartArt-graphics combineert met andere functies, zoals thema’s, kunt u hiermee professionele illustraties maken met slechts een paar muisklikken.”

SmartArt is te vinden via de menutab Invoegen in het blok Illustraties.

Wanneer u in het tabblad Welkom van het Voorbeeldbestand op één van de ringen klikt, opent vanzelf het hulp-menu van SmartArt.

De gegevens zijn allemaal verzameld in het tabblad OlympData van het Voorbeeldbestand.

De betekenis van de meeste gegevens wijst zichzelf.
Alleen de kolom MedAantal (het aantal medailles) behoeft toelichting. Bij een individuele wedstrijd is dit aantal uiteraard gelijk aan 1. Bij een team-prestatie krijgt iedere deelnemer een “evenredig deel van de medaille”.
Dit betekent dat als je het aantal voorkomens in deze kolom telt, dat je dan het aantal uitgedeelde medailles krijgt. Neem je de som dan wordt een team-prestatie slechts als 1 medaille geteld (wat de bedoeling is als je bijvoorbeeld een landenklassement wilt maken).

De kolom Medaille heeft een Voorwaardelijke opmaak gekregen.

In het tabblad Locatie staat een overzicht van alle landen en plaatsen waar Olympische Spelen zijn gehouden.

Overzichten

In het Voorbeeldbestand zijn diverse overzichten opgenomen; de tabbladen Ovz:

  1. het eerste is een landenoverzicht. In cel C2 kan het gewenste jaar worden geselecteerd.
    In het Waarden-gebied van de draaitabel is de Som van het veld MedAantal weergegeven.
    De landen zijn gesorteerd naar aflopend totaal aantal medailles; klik rechts op een land, kies de optie Sorteren en dan Meer sorteeropties.
    NB1 de Medaille-velden heb ik dezelfde voorwaardelijke opmaak gegeven als de kolom in de bron-gegevens. Het woord Medaille is overschreven door MedSrt.
    NB2 gediskwalificeerde winnaars hebben wel een MedSrt, maar geen waarde voor MedAantal. In de draaitabel staat dus ook geen waarde in die kolom.
    Wilt u weten welke deelnemers gediskwalificeerd zijn: dubbel-klik dan op het eindtotaal van de betreffende kolom.
    In hetzelfde tabblad Ovz1 staat ook een landenoverzicht, maar daar zijn de diskwalificaties weggelaten door een filtering op MedSrt (klik op het vinkje achter MedSrt; hebt u een filtering aangebracht dan krijgt dit de vorm van een trechter).
  2. in Ovz2 is het landenoverzicht verder uitgesplitst naar Sport en Event.
  3. Ovz3 geeft de verdeling van het aantal medailles naar geslacht; uitgesplitst per sport.
    Duidelijk is te zien dat bobsleeën nog een mannensport is.
  4. ook zo benieuwd bij welke sport de meeste diskwalificaties plaats vinden en bij welk land? Kijk dan in Ovz4.
    LET OP als nu in het waarden-gebied het aantal van het veld MedAantal wordt bepaald, dan zal Excel niets laten zien omdat dat veld in deze situatie niet is gevuld. Kies dus voor het bepalen van het aantal voorkomens een ander (wel gevuld) veld.
  5. geïnteresseerd in de resultaten van Nederland op de winterspelen in de loop van de tijd? Kijk in Ovz5.
    NB wilt u weten welke atleten de medailles hebben binnengesleept: dubbel-klik op het betreffende aantal.
  6. in Ovz6 ziet u een verdeling naar leeftijd van de winnaars.
    De leeftijden zijn in categorieën ingedeeld.
    Hoe gaat dat in zijn werk?
    * voeg de leeftijd als Rijlabel toe aan de draaitabel
    * klik rechts op een willekeurige leeftijd en kies de optie Groeperen
    * u krijgt dan de mogelijkheid om aan te geven hoe groot het interval moet zijn
    * helaas: aangezien in de kolom Lft ook de tekst NB voorkomt, kan Excel deze groepering niet uitvoeren dus komt er wat ‘handwerk’ aan te pas: selecteer de cellen met de leeftijden 10 t/m 15 en klik rechts op één van die waardes, kies dan de optie Groeperen, wijzig de naam Groep1 in een meer relevante omschrijving (hier 10-15).
    NB1 het Waarden-gebied van de draaitabel heeft een voorwaardelijke opmaak gekregen, waarbij gebruik is gemaakt van de optie Gegevensbalken.
    NB2 er zijn meerdere jaren geselecteerd (2000 t/m 2018); Excel laat dan alleen de tekst “(Meerdere items)” zien. Het verdient aanbeveling om zelf in de cel daarnaast aan te geven welke selectie op dat moment zichtbaar is (in dit geval: resultaten vanaf 2000).

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

 

 

Unieke waarden



Er bestaan nogal wat situaties, waarbij je wilt weten of items allemaal verschillend zijn of niet.
En, als ze niet allemaal verschillend zijn, welke unieke exemplaren komen er dan voor?

In Excel kennen we een dergelijk probleem ook: welke unieke waarden komen er in een rij of kolom voor?

Deze keer zullen we enkele methoden de revue laten passeren; van simpele tot complexe, van opmaak tot selectie.

Voorwaardelijke opmaak

In het Voorbeeldbestand heb ik op het tabblad Data1 een tabel opgenomen met in de kolommen oa de afdelingen en het soort bedrag.

Alle cellen in die kolommen hebben een voorwaardelijke opmaak gekregen:

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. klik op de optie Nieuwe regel
  4. in het vervolgscherm kiezen we als Type: Een formule gebruiken
  5. en de formule wordt:
    =AANTAL.ALS(C$3:C3;C3)=1
    ofwel: als in het bereik C$3:C3 de waarde uit cel C3 1 keer voor komt mag de cel een opmaak krijgen.
    Dat is natuurlijk altijd waar!
    Maar wat gebeurt er als we deze voorwaardelijke opmaak ook ‘loslaten’ op een cel lager? Excel interpreteert de formule dan als =AANTAL.ALS(C$3:C4;C4)=1 en kijkt of de waarde uit C4 slechts 1 keer voor komt in het bereik C$3:C4.
    Ook wanneer deze formule in kolom D wordt toegepast is de controle precies wat we zoeken.
    NB om de formule ook in andere kolommen te kunnen gebruiken is alleen de 3 absoluut gemaakt als begin van het bereik (vandaar het $-teken vóór de 3) en is niet het meer voor de hand liggende $C$3 gebruikt.
  6. voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
  7. klik op OK
  8. zorg dat de opmaak op alle relevante cellen van kolom C en D wordt toegepast:
    * kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
    klik op de optie Regels beheren
    * vul bij Van Toepassing op het gewenste bereik in (in dit geval $C$3:$D$32)

NB1 het overzicht is een Excel-tabel. Daarom zal Excel, wanneer er een regel aan wordt toegevoegd, ook de Voorwaardelijke opmaak direct meenemen; het bereik wordt automatisch aangepast.

NB2 welke unieke waarden er in een tabel-kolom zitten ziet u ook direct door op het ‘vinkje’ achter een kolomkop te klikken.

NB3 het vorige ‘recept’ kan met 1 stap ingekort worden: selecteer in de eerste stap niet één cel, maar het totale bereik waar de opmaak voor moet gelden; stap 8 kan dan vervallen.

Via draaitabel

De vorige oplossing was een optische methode om unieke waarden te vinden; bij een grote tabel niet echt handig.

Het maken van een draaitabel is echter een simpele en doeltreffende manier om snel de unieke waarden in een kolom te vinden:

  1. selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
  4. klik OK
  5. sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
  6. hetzelfde kunt ook doen voor de Soort, maar u kunt beter de vorige draaitabel uitbreiden: sleep Soort naar Kolomlabels.
    Sleep ook nog Bedrag naar het Waarde-gebied en u hebt meteen een overzicht van de verdeling van de bedragen naar afdeling en soort!

    Zie ook het tabblad Data1 in het Voorbeeldbestand.

Even een uitstapje: wilt u weten wat het totaal van Srt1 is dan kunt u ook de volgende formule gebruiken: =SOM.ALS(tblData1[Soort];”Srt1″;tblData1[Bedrag]) maar beter is het volgende:

  1. plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
  2. in de cel daarnaast gaan we een formule opbouwen:
    tik in =som.als(
  3. wijs met de cursor de bovenkant van de kolom Afd aan (de cursor wordt dan een zwarte pijl naar beneden) en klik.
    De formule wordt dan aangevuld met tblData1[Soort] ofwel alle cellen in de kolom Soort van de Excel-tabel tblData1.
  4. tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
  5. als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
  6. nog een haakje sluiten en Enter

Door in G14 een andere soort in te typen, krijgt u daarvan het totaal.

Maar wat als er aan 2 (of meer voorwaarden moet worden voldaan?

U moet dan de functie SOMMEN.ALS gebruiken.
Deze werkt net iets anders: eerst geeft u het bereik op, waarvan u de som wilt bepalen, dan het eerste criterium-bereik met daarachter het criterium zelf, daarna een nieuw criterium-bereik met criterium etc.

Gegevensvalidatie

Maar in G14 kunnen nu willekeurige teksten worden ingevoerd. Dat is natuurlijk niet de bedoeling; we willen alleen bestaande soorten kunnen opgeven.

Dit gaat eenvoudig met gegevens-validatie:

  1. plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. kies bij Toestaan: de optie Lijst
  4. en bij Bron: kiest u de kolom Soort uit de Excel-tabel op de manier zoals hierboven aangegeven.
    NB Excel vertaalt de kolom direct naar daadwerkelijke cellen, maar als de tabel groter (of kleiner wordt) verandert dit bereik mee.
  5. klik op OK.

Oeps, het resultaat is niet helemaal wat we hoopten. In de keuzelijst staan alle voorkomens uit de kolom Soort, we willen natuurlijk alleen unieke waarden hebben!

Unieke waarden via filtering

Om een lijstje te maken van unieke waarden (maar u wilt geen draaitabel gebruiken) dan kent Excel nog een andere aanpak:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
  3. voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
  4. bij Kopiëren naar vult u de cel in, waar de unieke waarden moeten komen (in het voorbeeld heeft deze cel de naam AfdKop gekregen)
  5. vergeet niet het vinkje te plaatsen bij Unieke records!

In het tabblad Data1 van het Voorbeeldbestand ziet u het resultaat van deze actie.

Ook voor de kolom Soort zijn de unieke waarden opgehaald.

NB1 Wat direct opvalt is dat er geen (automatische) sortering plaats vindt. De volgorde is gelijk aan de volgorde in de bron.

NB2 als er regels aan de bron-tabel worden toegevoegd dan moeten de ophaal-acties opnieuw worden uitgevoerd.
Excel ‘onthoudt’ de gegevens van de laatste actie. Dit is ook te zien in het overzicht Namen beheren in de menutab Formules.

LET OP1 de locatie, waar de unieke waarden moeten komen, moet op hetzelfde tabblad staan als waar de brongegevens zich bevinden.

LET OP2 vergeet niet om bij het Uitgebreid filter ook de kop van de kolom mee te nemen, anders krijgt u de eerste waarde dubbel. Dit is nergens in de Microsoft-documentatie terug te vinden!

Gegevensvalidatie

Op basis van deze unieke waarden kunnen nu makkelijk Gegevensvalidaties worden gemaakt.
Maar om het geheel dynamisch te houden (als er nieuwe unieke waarden bijkomen moet dit automatisch worden meegenomen bij de gegevensvalidatie) wordt als bron-lijst niet  een hard bereik genomen (bijvoorbeeld R3:R5).

Nee, we creëren een dynamisch bereik door een naam toe te voegen:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. vul een naam in (bijvoorbeeld AfdUniek)
  3. en plaats de formule
    =VERSCHUIVING(AfdKop;1;0;AANTALARG(Data1!$R:$R)-1)
    in het vak Verwijst naar.

Bij gegevensvalidatie (zie cel G20 in het tabblad Data1 van het Voorbeeldbestand) gebruikt u als Bron de naam AfdUniek (vergeet niet het =-teken daar voor te zetten!).

In de cellen G21 en H21 is op een vergelijkbare manier gegevensvalidatie toegepast, maar daarbij zijn als bron voor de verschuiving 2 simpele draaitabellen gebruikt.

LET OP worden er regels aan de oorspronkelijke tabel toegevoegd, vergeet dan niet de draaitabellen te vernieuwen anders bevatten de gegevensvalidaties niet de meest recente gegevens.


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: