Tagarchief: Draaitabel.Ophalen

Totalen in draai-grafiek



Totalen in een grafiek weergeven, dat is simpel: voeg de betreffende reeks toe aan de grafiek-gegevens. Misschien dat je deze reeks nog aan de secundaire as moet koppelen, maar daarmee ben je klaar.

Maar bij een draai-grafiek (een grafiek gebaseerd op een draaitabel) zul je merken dat je wel wat flexibiliteit inlevert; dan is een lijntje met totalen toevoegen niet mogelijk.
Met VBA kun je een heel eind komen, maar zonder programmeren niet. In dit artikel wat alternatieve mogelijkheden.

Basis-gegevens

We gaan uit van een tabel (tblData2 op het tabblad Data van het Voorbeeldbestand) met daarin Datums en het Soort artikel met een bijbehorend Aantal.

Wil je zien hoe dit overzicht tot stand is gekomen: kijk op het tabblad Basis van het Voorbeeldbestand. Daar worden telkens random nieuwe data gecreëerd.

Draaitabel

Om deze gegevens snel te kunnen analyseren maken we een draaitabel:

  1. selecteer een cel in de brongegevens
  2. klik in de menutab Invoegen in het blok Tabellen op de optie Draaitabel en dan op de button OK
  3. sleep de Datum naar het Rijen-gebied, Soort naar de Kolommen en Aantal naar het Waarden-gebied
  4. standaard zal Excel de datums direct groeperen (in Jaren, Kwartalen en Maanden)
    LET OP één van de namen in de Rijen is nog steeds Datum, maar deze bevat nu de maanden.
    Wil je een andere groepering? Zie het artikel Groeperen in een draaitabel.
  5. sleep Jaren naar het Filters-gebied en verwijder Kwartalen uit de Rijen.
  6. de Soort is alfabetisch gesorteerd: versleep de kolom met de waarde Drie naar rechts (door de rand met de muis ‘vast te pakken’).
    Het resultaat staat in het tabblad Ovz1 van het Voorbeeldbestand.

Draaigrafiek

Een grafiek maakt de onderlinge verhoudingen tussen de cijfers vaak een stuk duidelijker:

  1. selecteer een cel van de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen/Analyseren in het blok Extra de optie Draaigrafiek
  3. kies de optie Gegroepeerde kolom en klik op OK

Verander je nu iets in de draaitabel (filter je bijvoorbeeld een bepaald jaar uit) dan past de grafiek zich automatisch aan.

Draaigrafiek aanpassen 1

De standaard-grafiek kent wel wat nadelen.
Allereerst willen we van die ‘lelijke’ veldknoppen af: klik rechts op één van de knoppen en kies de optie Alle veldknoppen verbergen in grafiek.

Op het tabblad Ovz2 van het Voorbeeldbestand staat het resultaat:

LET OP Excel geeft alle kolommen automatisch een kleur; normaal wijzig ik deze handmatig in vaste kleuren zodat ook bij filtering iedere Soort zijn eigen kleur houdt. Helaas: bij draaigrafieken worden deze instellingen door Excel niet vastgehouden.

Draaigrafiek aanpassen 2

Ook al is een grafiek bedoeld om intuïtief inzicht in de onderliggende cijfers te krijgen, dan nog werkt het goed (of is het zelfs noodzakelijk) om in een grafiek de waarde(s) van de belangrijkste gegeven(s) te laten zien.

In het tabblad Ovz3 van het Voorbeeldbestand heeft de grafiek een veelzeggende titel meegekregen:

  1. in een lege cel creëren we daartoe eerst de volgende formule:
    =ALS(C2=”(Alle)”; “Totaal alle jaren: “;
    “Totaal voor ” & C2 &”: “) & TEKST(DRAAITABEL.OPHALEN(“Aantal”;$B$4);”#.##0″)
    Als in cel C2 alle jaren zijn gekozen dan nemen we een overeenkomende tekst, anders wordt de tekst Totaal voor met daarachter de inhoud van cel C2 (gekoppeld door het &-teken). Achter de zo gemaakte tekst plaatsen we met behulp van de functie DRAAITABEL.OPHALEN (zie het betreffende artikel) het totaal van Aantal. Met de functie Tekst geven we een opmaak mee (een punt voor de duizendtallen en geen decimalen).
  1. zorg dat de grafiek een Grafiektitel heeft; bijvoorbeeld op de volgende manier: selecteer de grafiek en klik op de + rechts daarvan en vink de betreffende optie aan.
  1. klik op de Grafiektitel en daarna in de formulebalk. Tik in = en klik dan op de cel uit de eerste stap (in Ovz3 is dat cel I2).
    In de formulebalk komt dan automatisch de formule:
    =’Ovz3′!$I$2. Druk op Enter.

Nog een paar slicers toevoegen (zie Slicers in Excel) en we hebben (het begin van) een interactief dashboard.

Bij het filteren in de draaitabel (hier mer behulp van slicers) kan het gebeuren dat het scherm ‘verspringt’; door een paar aanpassingen aan de draaitabel blijft de opmaak stabiel:

  • om te zorgen dat in jaren waar (nog) niet alle maanden gevuld zijn (in het voorbeeld 2020), toch alle maanden zichtbaar zijn (en dus ook in de grafiek): klik rechts op een van de maanden in de draaitabel, kies Veldinstellingen, vink op het tabblad Indeling&afdrukken de optie Items zonder gegevens weergeven aan
  • klik rechts op een van de cellen in het Waarden-gebied, kies Opties voor draaitabel en vink de optie Kolombreedte automatisch aanpassen uit

Draaigrafiek aanpassen 3

Maar we zijn niet gauw tevreden: we hebben nu het totaal aantal in de titel staan, maar wat zijn de totalen per maand? In de draaitabel staan de betreffende getallen netjes op het einde van iedere rij, maar we zien die niet terug in de grafiek.

Nog erger: er is ook geen optie om dat klaar te krijgen!
Uiteraard kunnen we met VBA aan de slag, maar daar is wel wat programmeer-arbeid voor nodig. Eens even kijken of het ook zonder kan: ja natuurlijk, we zorgen dat één reeks labels heeft en laten de inhoud van die labels wijzen naar de totalen per maand.

  1. zorg dat ergens in het tabblad een reeks cellen gevuld is met de rij-totalen. In het tabblad Ovz4 staan in de cellen C24:C35 verwijzingen naar de draaitabel met behulp van de functie DRAAITABEL.OPHALEN.
  2. klik op één van de kolommen in de grafiek
  1. klik op de + rechts van de grafiek en zet de Gegevenslabels aan, inclusief de optie Basis, binnenkant zodat alle labels op dezelfde hoogte komen

  1. klik rechts op een van de labels en kies de optie Gegevenslabels opmaken
  2. bij Labelopties kun je de waarden opgeven die weergegeven moeten worden (Waarde uit cellen). Maak hier een verwijzing naar de cellen C24:C35; vink de optie bij Waarde uit en dan Waarde uit cellen aan.

Helaas, deze methode heeft 2 (?) tekortkomingen (zie het tabblad Ovz4 van het Voorbeeldbestand):

  • de labels worden gecentreerd op de overeenkomende kolom
  • als de betreffende soort uitgefilterd wordt zijn ook de labels weg!

Draaigrafiek aanpassen 4

In het tabblad Ovz5 van het Voorbeeldbestand staat een grafiek die de totalen per maand toont, inclusief de procentuele verdeling over het jaar.

De volgende aanpassingen zijn doorgevoerd:

  1. allereerst is er ruimte gemaakt onder aan de grafiek: klik rechts op de linkeras, kies As opmaken en zorg dat de minimumgrens niet meer automatisch wordt bepaald maar (in dit geval) -200 is
  2. de notatie van de as is zodanig aangepast, dat de negatieve getallen niet worden weergegeven: #.##0;
  3. vanaf cel C24 worden de maandtotalen opgehaald:
    =DRAAITABEL.OPHALEN(“Aantal”;$B$4;”Datum”;B24)
  4. vanaf cel D24 bepalen we de inhoud van de teksten die we gaan toevoegen:
    =ALS(C24=0;””;
    TEKST(C24;”#.##0″)&
    TEKEN(13)&
    TEKST(C24/DRAAITABEL.OPHALEN(“Aantal”;$B$4);”0%”))
    Als C24 nul is dan hoeft er niets weergegeven te worden, anders de inhoud van cel C24 samen met het resultaat van het maandresultaat (C24) gedeeld door het totale resultaat (weergegeven als percentage zonder decimalen); tussen de twee elementen staat een code 13 (‘naar de volgende regel’).
    NB in cel D24 en verder is het resultaat van code 13 niet te zien, maar dadelijk in de grafiek wel.
  5. klik ergens in de grafiek en kies dan in de menutab Invoegen in het blok Illustraties de optie Vormen en daarna bij Basisvormen de optie Tekstvak en ‘teken’ met de muis waar je het tekstvak wilt hebben
    LET OP als je niet eerst ergens in de grafiek klikt wordt het tekstvak niet aan de grafiek gekoppeld maar aan het tabblad; bij het verplaatsen van de grafiek gaat het tekstvak dan niet mee!
  6. klik in de formulebalk, tik het =-teken en klik met de muis op de cel met de tekst die weergegeven moet worden
  7. pas de grootte van de tekst en/of het tekstvak aan en verplaats het tekstvak naar de juiste plaats (pak met de muis de ‘rand vast’)
  8. herhaal de stappen 5 t/m 7 voor alle maanden
    NB je kunt ook het eerste tekstvak kopiëren (Ctrl-C) en dan via Ctrl-V zoveel tekstvakken maken als nodig zijn. Die moeten dan nog verplaatst worden en de inhoud aangepast.

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


Treemap en Waterval



Zolang als Excel al bestaat, wordt het niet alleen als rekentool gebruikt maar ook voor rapportage-doeleinden.
Waar in de beginperiode meestal door middel van cijfers werd gerapporteerd, zijn daar later ook grafieken bij gekomen.

Microsoft heeft ons in de afgelopen jaren met diverse soorten grafieken verblijd.
In iedere nieuwe versie van Excel verschijnen er weer nieuwe; in dit artikel aandacht voor de Treemap en de waterval-grafiek.

NB in oudere versies van Excel en in menige MAC-versie werken de voorbeelden niet.

Basisgegevens

Om een grafiek te kunnen maken hebben we natuurlijk basisgegevens nodig.
In het Voorbeeldbestand staat in het tabblad DataTree een overzicht van Bedragen uitgesplitst naar Maand en Soort.

NB door middel van de functie Aselecttussen worden de data door Excel willekeurig gekozen; bij iedere wijziging in de werkmap zullen dus nieuwe gegevens gegenereerd worden.
Op deze manier kun je snel de impact op de grafieken zien.

De gegevens zijn opgeslagen in een Excel-tabel met de naam tblDataTree. Uitbreidingen aan deze tabel (of verwijderingen) zullen daardoor automatisch doorwerken in de overzichten en grafieken die daar op gebaseerd zijn.

Draaitabel en -grafiek

De gegevens uit het tabblad DataTree zijn in een draaitabel samengevat op het tabblad OvzTree. In dit geval is de Soort in het Filter-veld geplaatst, de Maand in de Rijen en het Bedrag in het Waarden-gebied.
Deze draaitabel kan direct vertaald worden naar een grafiek:

  1. klik ergens in de draaitabel
  2. op dat moment komt er een nieuwe menu-tab bij, Hulpmiddelen voor Draaitabellen
  3. klik daarbinnen op de menutab Analyseren en dan in het blok Extra op Draaigrafiek
  4. na enkele cosmetische aanpassingen ontstaat bovenstaande grafiek

NB de grafiektitel is dynamisch: wanneer in het Filter een andere Soort wordt gekozen past de titel zich automatisch aan.
In cel C19 wordt de basis daarvoor gelegd. U wijzigt een grafiektitel als volgt: klik in de bestaande titel, daarna in de formulebalk, voer het =-teken in, klik dan op cel C19 en druk op Enter. In de formulebalk verschijnt =OvzTree!$C$19.

Niet altijd is in deze grafiek duidelijk welke maanden in welke mate bijdragen tot het totaal (klik rechts in de Draaitabel en kies Vernieuwen). Aangezien de basisgegevens telkens opnieuw worden gegenereerd zal de Draaitabel (en dus ook de Draaigrafiek) dienovereenkomstig worden bijgewerkt.
Door de maanden anders te rangschikken kunnen de onderlinge verhoudingen duidelijker worden gemaakt:

  1. klik op het keuzevinkje achter Rijlabels
  2. kies Meer sorteeropties
  3. klik op het keuzerondje vóór Aflopend
  4. kies dan daaronder als sorteervolgorde niet voor Maand maar voor Som van Bedrag
  5. klik op OK

Bekijk het effect als je de draaitabel vernieuwd.

Treemap

Maar niet iedereen vind het lezen/interpreteren van bovenstaande grafiek makkelijk.
Waarschijnlijk zijn deze mensen meer gebaat bij een zogenaamde Treemap, bedoeld om hiërarchie in resultaten te verduidelijken.

Helaas is de Treemap niet beschikbaar als de bron een draaitabel is. Daarom is in het tabblad OvzTree van het Voorbeeldbestand een hulptabel gecreëerd, die de gegevens van de draaitabel repliceert.

In cel C27 wordt, afhankelijk van de corresponderende waarde in kolom B, het Bedrag opgehaald in de draaitabel rond cel B4.

Het maken van een Treemap is dan een peuleschil:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C27
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Hiërarchiegrafiek
  3. kies daar de optie Treemap
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB Blijkbaar komt dit grafiektype van een andere software-maker; niet alle eigenschappen van een grafiek zijn beschikbaar.
De mooie ronde hoeken bijvoorbeeld zijn niet meer terug te vinden.
Ook een dynamische grafiektitel is niet op dezelfde manier, als hiervoor aangegeven, in te voeren. In dit geval (zie het tabblad OvzTree van het Voorbeeldbestand) heb ik er voor gekozen om een Tekstblok in te voegen en daar dan de verwijzing naar cel C19 te plaatsen.

Om het instellen van keuzemogelijkheden te vereenvoudigen zijn ook 2 Slicers toegevoegd: eentje voor het instellen van de Soort en een andere om bepaalde Maanden te kunnen selecteren.

Waterval-grafiek

Is niet zozeer de onderlinge verhouding van belang, maar wil je weten hoe ieder onderdeel bijdraagt aan het geheel, dan is een ander type grafiek meer voor de hand liggend, de Waterval-grafiek.

NB Ook deze grafiek in niet beschikbaar als een draaitabel de brongegevens bevat.

In het tabblad Waterval van het Voorbeeldbestand is daarom een nieuwe bron-tabel (met de naam tblDataWater) ingevoerd. Per Maand wordt hier een willekeurig Bedrag gegenereerd tussen -100 en +200.

Ook het maken van een Watervalgrafiek is dan ‘kinderspel’:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C3
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Waterval-, trechter-, ….
  3. kies daar de optie Waterval
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB ook voor dit type grafiek geldt het voorbehoud, dat niet alle (standaard-)instellingen beschikbaar zijn.


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


Trechtergrafieken



Trechtergrafieken (in het Engels Funnel charts) worden vooral binnen sales-afdelingen gebruikt.
Dit om het verloop van potentiële klanten naar daadwerkelijke verkopen inzichtelijk te maken.

Maar ze zijn nog op meer plaatsen toepasbaar; in principe overal waar een volgende laag een deelverzameling van de vorige is.

Bijvoorbeeld dus ook bij website-bezoeken: de bovenste laag wordt gevormd door alle bezoekers, de tweede door alle bezoekers die meerdere pagina’s bekijken, de volgende geeft het aantal bezoekers weer, die de winkelwagen vullen en de onderste laag zijn die mensen, die daadwerkelijk wat bestellen.
Maar ook: een uitsplitsing van alle medewerkers (bovenste laag), die goede computerkennis bezitten (tweede laag), waarvan kennis van Excel belangrijk is (derde laag) en daarbinnen ervaring met draaitabellen hebben (vierde laag).

Vanaf Excel 2016 zijn trechter-grafieken ingebouwd; binnen de oudere versies moet je zelf creatief aan de slag.
Maar ook binnen de nieuwe Excel-versies kan het handig zijn om alternatieve benaderingen te kennen en te gebruiken.

Basis-gegevens

We nemen als voorbeeld voor dit artikel een sales-afdeling, die over drie jaar gegevens heeft verzameld (zie het tabblad Data in het Voorbeeldbestand).
Naast de aantallen per sales-stadium zijn ook de daarmee gemoeide omzet-bedragen vastgelegd.

Voor ieder jaar is het aantal leads (potentiële klanten) in kaart gebracht (in 2016 waren dat er 1000); door middel van een kwalificatieslag blijft daar een gedeelte van over (800 in 2016). Bij de beoordeling of die klanten een oplossing geboden kan worden, vallen er weer klanten af (300 in het voorbeeld). Aan geïnteresseerden wordt dan een voorstel gestuurd (400 in 2016), waarna bij een gedeelte een afronding kan plaats vinden (er blijven er 250 over; in 2016 dus een eindconversie van 25%).

Vanuit deze gegevens kan gemakkelijk informatie gedestilleerd worden over de totale conversie, maar ook over de conversie per deelstap.
Op grond van deze analyse zijn er mogelijk verbeterplannen te maken, maar de informatie kan ook gebruikt worden om ramingen voor de jaaromzet te maken.

Draaitabel

Op het tabblad Ovz van het Voorbeeldbestand is een overzicht gemaakt van deze gegevens door middel van een draaitabel, waarbij de resultaten van de verschillende jaren bij elkaar worden genomen.
Door bovenaan een bepaald jaar te kiezen kan de informatie wel ingeperkt worden.

Analyse

Op basis van de draaitabel kunnen we een analyse van de sales-activiteiten uitvoeren (zie het tabblad Analyse van het Voorbeeldbestand):

De aantallen leads (cel C8) wordt uit de draaitabel opgehaald via de formule
==DRAAITABEL.OPHALEN(“Som van Aantal”;Ovz!$B$4;”Stadium”;$B8)

De overige aantallen gaan op een vergelijkbare manier; de formule kan naar beneden gekopieerd worden.

Voor de bedragen geldt een vergelijkbare formule:
=DRAAITABEL.OPHALEN(“Som van Bedrag”;Ovz!$B$4;”Stadium”;$B8)

De resultaten van deze formules gebruiken we nu om een Trechter-grafiek te maken; dit doen we door in de cel daarnaast een evenredig aantal blokjes te zetten.

Maar … 3700 blokjes is wel wat veel.
En als we in de draaitabel maar één jaar kiezen, dan ziet de grafiek er direct heel anders uit.

We zullen de resultaten dus moeten schalen: als we maar 100 blokjes in de eerste rij willen, dan moeten we de aantallen dus nog delen door 37. In het tabblad Analyse wordt daartoe een correctiefactor berekend: =C8/D2
Een vergelijkbare factor is nodig voor de bedragen.

NB wil je in de eerste rij meer of minder blokjes hebben, dan kan cel D2 gewijzigd worden.

Laten we de grafieken maken:

  1. in cel D8 komt de formule =HERHALING(“|”;C8/$D$3)
  2. om het mooier te maken (blokjes ipv streepjes) geven we die cel een aparte opmaak: kies het lettertype Script, met tekengrootte 8 en maak de ‘tekst’ vet
  3. kopieer deze formule naar beneden
  4. voor de bedragen komt een vergelijkbare formule in kolom H

Wordt er nu in de draaitabel een bepaald jaar geselecteerd, dan worden alle getallen in de analyse-sheet automatisch aangepast en uiteraard ook de grafieken.
Om dit te vergemakkelijken is een zogenaamde slicer toegevoegd.

NB in (veel) Excel-versies voor de MAC zijn slicers niet geïmplementeerd.

De grafieken geven een globaal inzicht in het verloop tussen de verschillende stadia. Maar we willen ook met de conversie-percentages kunnen rekenen. In de analyse-tabel zijn daar hulpkolommen voor opgenomen. In cel E9 staat de formule =C9/C8; het conversiepercentage door de kwalificatieslag. Voor de overige aantallen en ook voor de bedragen gelden vergelijkbare formules.
Door deze stap-percentages te vermenigvuldigen weet je ook hoe groot de kans is, dat de acties voor een klant, die in een bepaald stadium zit, met een goed resultaat zullen worden afgerond (zie kolommen F en J in het tabblad Analyse van het Voorbeeldbestand).

Ramingen

Op dit moment kent het onderhavige bedrijf een verdeling van de klanten naar stadia zoals weergeven in de kolommen Realisatie (zie tabblad Raming in het Voorbeeldbestand).
Door nu de analyse-resultaten te gebruiken kun je een raming maken van de omzet in de toekomst: de aantallen en bedragen worden vermenigvuldigd met de totale conversie-percentages van het betreffend stadium.
Uiteraard zijn de resultaten voor die ramingen afhankelijk van welke historie wordt meegenomen in de analyse (één specifiek jaar of alle jaren).

NB voor het gemak is de slicer naar dit tabblad gekopieerd.

Pasen

In veel software zijn zogenaamde paas-eieren verstopt. In Excel 2010 zit bijvoorbeeld ergens ‘ver weg’ een race-spel; in andere versies is het mogelijk om foto’s van de ontwikkelaars tevoorschijn te toveren. De ontwikkelaars hebben ooit een flight-simulator ingebouwd! Maar in de meer moderne versies van Excel heeft Microsoft al deze grapjes verwijderd. Heb je nog een ‘oude’ Excel-versie: google maar eens op excel easter egg.

Over Google gesproken; daar kunnen ze er ook wat van. Tik de volgende opdrachten in in de Google-zoekbalk:

  • askew
  • do a barrel roll
  • anagram
  • recursion
  • zerg rush

NB bij de laatste zoekopdracht: schrik niet; het is een spelletje. Probeer de rondjes weg te klikken.


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


VBA-routine automatisch starten



Af en toe is het handig, dat Excel automatisch de invoer in een bepaalde cel valideert of dat er automatisch een melding komt wanneer een bepaalde cel wordt gewijzigd of …

Wat ik bedoel is: soms zou je willen, dat wanneer een bepaalde cel wordt gewijzigd, er dan automatisch een actie wordt getriggerd.
Validaties kunnen vaak eenvoudig met de optie Gegevensvalidatie worden uitgevoerd; andere routines kunnen met behulp van VBA (eventueel door een macro op te nemen) worden geprogrammeerd.
Maar hoe zorg je er voor dat zo’n routine automatisch opstart?

In dit artikel zal ik aan de hand van enkele eenvoudige voorbeelden laten zien hoe dit in zijn werk gaat.

Invoer valideren

In het Voorbeeldbestand op het tabblad Vb1 staat een klein tabelletje, waarmee het kwadraat van een ingevoerd getal wordt berekend.

Wanneer in cel C2 (met de naam Invoer) een getal wordt ingetikt, dan wordt in cel C3 het kwadraat bepaald. Maar ook start er een VBA-routine die een controle uitvoert en wordt er een pop-up-scherm getoond, waarvan de inhoud afhankelijk is van het controle-resultaat.

NB de cellen C2, C3 en C4 hebben een naam gekregen: selecteer het bereik B2:C4, kies  in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie; zorg dat alleen Linkerkolom staat aangevinkt.

  1. start de VBA-editor via het tabblad Ontwikkelaars of Alt-F11
  2. dubbelklik in de Projectverkenner op Blad1 (Vb1)
  3. daar staat de volgende routine:

    Door de naam van de subroutine (Worksheet_Change) zal Excel deze routine automatisch opstarten als er ergens in dit werkblad (Vb1 dus) iets wordt gewijzigd. Welke cel(len) er is/zijn gewijzigd wordt dan via de variabele Target doorgegeven.
    Allereerst tellen we het aantal cellen in Target; als dat niet gelijk is aan 1, dan zijn we klaar.
    Dan testen we of het adres (de celverwijzing) van Target gelijk is aan die van Invoer; zo niet dan zijn we ook klaar.
    Via Int([Invoer]) bepalen we het Integer-gedeelte (het gedeelte vóór de decimale komma) van de cel Invoer en kijken of dat gelijk is aan de Invoer  zelf; zo niet dan zorgen we via Msgbox dat er een pop-up verschijnt met die melding.
    Is de invoer wel een geheel getal, dan komt er een Msgbox, die laat zien wat er is ingevoerd en wat het resultaat in de cel Uitvoer is. Wanneer op OK wordt geklikt in de pop-up, dan wordt de cel met de naam VBA_result gevuld met het kwadraat van Invoer.

NB1 Denk aan de vierkante haken om de naam van een cel(-bereik). Anders ziet VBA dat als een interne variabele.

NB2 Via Format zorgen we er voor, dat bij grote getallen ieder blokje van 3 cijfers gescheiden wordt. Aangezien de VBA-editor Engelstalig is, krijgen we als scheiding een komma; met het commando Replace vervangen we die door een punt.

NB3 verschillende stukken teksten koppelen we aan elkaar met het &-teken (zorg dat er spaties omheen staan); vbCrLf zorgt er voor, dat de rest van de tekst op een volgende regel komt. De naam van de code (Carriage Return, Linefeed; wagen terug en 1 regel naar beneden) stamt nog uit het typemachine-tijdperk!.

NB4 met de spatie en het _-teken (underscore) kun je in de VBA-editor lange regels in ‘stukken knippen’; Excel weet dan dat ze bij elkaar horen.

Draaitabel vernieuwen

In het tabblad Vb2 van het Voorbeeldbestand staan een (simpele) Excel-tabel (met de naam tblData2), een draaitabel, die daarop gebaseerd is, en een controle-blokje. Wanneer in dat laatste gebied de inhoud van cel F2 gelijk wordt aan Ja (via gegevensvalidatie zijn alleen Nee en Ja toegestaan), dan wordt de draaitabel automatisch vernieuwd.

De VBA-routine, die deze klus voor zijn rekening neemt, vindt u in de VBA-projectverkenner in het object Blad2 (Vb2). Vergeet niet te dubbelklikken!
Waarschijnlijk behoeft de routine geen verdere uitleg; hij lijkt veel op die in het tabblad Vb1.

NB cel F3 wordt met behulp van de functie DRAAITABEL.OPHALEN gevuld; zie ook het artikel over Draaitabel.Ophalen.

Draaitabel vernieuwen 2

De 2 VBA-routines uit Vb1 en Vb2 zijn redelijk rechttoe-rechtaan geprogrammeerd; om ze makkelijker te kunnen begrijpen zijn niet alle officiële regels gehanteerd en zijn er weinig   fout-controles ingebouwd.

Het voorbeeld in tabblad Vb3 is al iets netter: het vernieuwen van de draaitabel is in een aparte subroutine opgenomen (met de naam DraaiVernieuwen).

  1. start de VBA-editor (Alt-F11)
  2. kies in het VBA/menu Invoegen/Module
  3. en plaats in de nieuwe Module de volgende routine:

LET OP in Vb2 werd de draaitabel (in het Engels Pivottable) met de naam Draaitabel1 vernieuwd. Deze routine vernieuwt de draaitabel met het volgnummer 1 in het actieve werkblad.

De nieuwe subroutine kan ook op andere plaatsen en op andere manieren worden aangeroepen. In Vb3 is een knop gemaakt, die de routine aanroept.
Klik rechts op de knop en kies Macro toewijzen.

NB alleen macro’s/subroutines die in een module zijn opgenomen kunnen aan een knop worden toegewezen; vandaar dat de routine DraaiVernieuwen niet in het object Vb3 staat.

Draaitabel vernieuwen bij wijzigen bron

Maar het zou natuurlijk nog mooier zijn als de draaitabel automatisch zou worden vernieuwd bij het wijzigen van de bron-gegevens (waaronder ook wordt verstaan het toevoegen van nieuwe gegevens).

In het Voorbeeldbestand is in het tabblad Vb4 een nieuwe Excel-tabel opgenomen (met de naam tblData4) en een daarop gebaseerde draaitabel.

Verander je de Soort of het Aantal in een bestaande regel dan start automatisch de VBA-routine in het object Blad4 (Vb4). Beter gezegd: de routine start bij iedere wijziging van een cel in dit werkblad, maar er wordt slechts daadwerkelijk iets uitgevoerd als een cel in tblData4 wordt gewijzigd.

Omdat in regel 3 de Target maar 1 cel kan omvatten kun je die niet zomaar met tblData4 vergelijken. We willen weten of die ene Target-cel in tblData4 voorkomt.
De VBA-functie Intersect bepaalt wat de overlap tussen de twee bereiken is. Is die overlap leeg dan is Intersect gelijk aan Nothing, dus als die Not Nothing is dan is er wel overlap.

NB1 de truc met Not Nothing kom je op veel plaatsen op Internet tegen en is algemeen toepasbaar voor allerlei soorten ranges. In dit geval is de Target maar 1 cel; we kunnen regel 3 dan vervangen door een meer simpele constructie:
If Intersect(Target, [tblData4]) = Target Then

NB2 de Msgbox levert nu wel een vreemd resultaat; toch niet goed over de routine DraaiVernieuwen nagedacht, tja!

 


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

 

 

 

Draaitabel.Ophalen

Draaitabel ophalenMooie vraag: je hebt een heleboel gegevens, maar hoe maak je daar nu informatie van.

Zoals in het plaatje is weergegeven kun je op diverse manieren met de data gaan “stoeien”.  Eén van de meest gehanteerde methodes is het gebruik van draaitabellen. En terecht: menige beroepsgroep zou ontzettend blij zijn met zo’n krachtig, breed inzetbaar hulpmiddel!

Maar draaitabellen kennen (natuurlijk) ook nadelen. Ik kan er nu twee bedenken:

  1. de mogelijkheden en inzetbaarheid zijn ontzettend uitgebreid; waar begin je als je er iets over wilt vertellen!
  2. draaitabellen hebben altijd een vast stramien: als je een verdeling naar product in de rijen hebt staan kun je de volgorde van de producten nog wel makkelijk aanpassen, maar als het eerste resultaat in cel C10 komt dan komt de volgende in cel C11 etc.

Nadeel 1 kunnen we niet met één artikel door G-Info oplossen, maar aan het tweede kunnen we wel wat doen.

Draaitabel maken

Om gegevens uit een draaitabel te kunnen gebruiken moet je natuurlijk wel een draaitabel hebben.
DraaitabelLaten we er even eentje maken (zie Voorbeeldbestand):

  1. in het tabblad Basis1 staat een fictief omzetoverzicht. Per dag ligt de omzet vast, gesplitst naar soort en product.
    De gegevens zijn willekeurig door Excel gegenereerd met de formules van het tabblad Formules en daarna ‘hard’ gemaakt door de cellen te kopiëren en op dezelfde plaats te plakken als Waarden.
  2. selecteer een willekeurige cel in dit overzicht en kies dan de optie Draaitabel in de menutab Invoegen
  3. Excel zal automatisch het totale gebied met gegevens selecteren (in dit geval B2 t/m E1001).
    In het voorbeeld heb ik voor de locatie van de draaitabel een bestaand werkblad gekozen (namelijk Basis1) en de draaitabel op cel H3 geplaatst …
  4. Draaitabel… en de Lijst met draaitabelvelden ingevuld zoals hiernaast. Resultaat: een mooi overzicht van de verdeling van de omzet naar Soort en Product (zie tabblad Basis1):
    Draai3
    NB het gemiddelde bedrag hebben we gekregen door het veld Bedrag opnieuw naar het waarden-blok te slepen en de Waardeinstellingen te veranderen van Som naar Gemiddelde.

De volgende opmerkingen bij dit overzicht:

  1. de kolomnamen zijn groot waardoor alle kolommen automatisch extra breed worden (nog een nadeel van draaitabellen!).
    Oplossing: klik op zo’n naam en wijzig die in bijvoorbeeld Aantal of Gemid.
    Zie de tweede draaitabel op het tabblad Basis1.
  2. de sortering bij Soort is niet logisch.
    Oplossing1: versleep de diverse items naar de juiste plaats door met de cursor “de rand vast te pakken”
    Oplossing2: maak een eigen sortering aan en pas die toe (zie artikel doorvoeren-en-sorteren)
  3. wanneer de brongegevens uitgebreid worden met een nieuwe regel dan zal de draaitabel niet automatisch veranderen (ook niet na het Vernieuwen daarvan).
    Oplossing1: ga binnen de menutab Hulpmiddelen voor draaitabellen naar Opties en kies dan binnen Gegevens voor Andere Gegevensbron en zorg dat alle gegevens zijn geselecteerd.
    Oplossing2: zorg dat de brongegevens als een Excel-tabel zijn opgeslagen; zie hierna (Excel-tabel en draaitabel)
  4.  qua structuur is het niet ‘netjes’ om basisgegevens en afgeleide overzichten in hetzelfde tabblad weer te geven. In dit geval heb ik het wel even gedaan om snel het resultaat van de draaitabel te kunnen vergelijken met de bron.

Excel-tabel en draaitabel

Twee soorten tabellen, die niet door elkaar gehaald moeten worden!
In het tabblad Basis2 van het Voorbeeldbestand staan dezelfde gegevens als in blad Basis1. Maar ik heb daar een Excel-tabel van gemaakt:

  1. selecteer een willekeurige cel in dit overzicht en kies dan de optie Tabel in de menutab Invoegen
  2. Draaitabelin het nieuwe tussenvenster wordt automatisch het hele gegevensgebied gekozen en Excel ‘ziet’ dat er een kopregel is. Klik OK.
  3. het resultaat is een tabel, waarbij Excel automatisch de regels een ´zebra´-achtergrond geeft en de koppen van filter-knoppen voorziet:Draaitabel
  4. via de menutab Hulpmiddelen voor tabellen is de naam van de tabel gewijzigd van de standaardwaarde Tabel1 naar tblOmzet.

Wanneer we nu een draaitabel hiervan maken (zie tabblad Draai) en we vullen de brongegevens op het tabblad Basis2 aan met een nieuwe regel dan zal de draaitabel automatisch de nieuwe regel meenemen.
Wel nog Vernieuwen kiezen, bijvoorbeeld door rechts te klikken op de draaitabel!

Gegevens van een draaitabel gebruiken

Genoeg over de voorbereidingen; dit artikel zou gaan over het ophalen van gegevens uit een draaitabel.

DraaitabelStel dat we om een of andere reden niet alle gegevens willen zien maar bijvoorbeeld alleen het bedrag, dat bij de combinatie Soort=Een en Product=P2 hoort, dan kunnen we in Excel natuurlijk gewoon verwijzen naar cel G6 (zie het tabblad Draai in het Voorbeeldbestand).
DraaitabelMaar wat gebeurt er als de draaitabel wordt gewijzigd omdat er nieuwe soorten of producten bijkomen of als de structuur van de draaitabel wordt gewijzigd (wissel Soort en Product maar eens om van Rijlabels naar Kolomlabels en vice versa)?
Dan staan de gegevens van Een/P2 waarschijnlijk niet meer in dezelfde cel en klopt de verwijzing niet meer. Dit is een veel voorkomende fout in Excel-sheets!

Gelukkig heeft Microsoft daar iets op bedacht (vanaf versie Excel 2007?). Even een voorbeeld:

  1. klik in het tabblad Draai in cel C30
  2. tik het =-teken in en klik dan met de muis op cel M10 en druk op Enter
  3. als in cel C30 nu de formule =M10 staat, moet je nog iets aan de instellingen van de draaitabel veranderen:
    * klik ergens in de draaitabel
    * Draaitabelin de menutab Hulpmiddelen voor draaitabellen, die nu tevoorschijn komt, kiezen we de tab Opties. In het vak Draaitabel staat nog een keer Opties: zet daar het vinkje bij DraaitabelOphalen genereren aan. Ga dan opnieuw naar punt 1.
    Wil je deze optie niet gebruiken dan weet je nu dus ook waar je hem uit kunt zetten.
  4. in cel C30 staat nu de formule =DRAAITABEL.OPHALEN(“Som”;$B$3)
    Ofwel: haal het bedrag op dat hoort bij het veld Som (in ons geval is dat eigenlijk het veld Bedrag, maar we hebben Som van Bedrag gewijzigd in Som) uit de draaitabel, die begint in cel B3.
  5. de vorige formule haalt het totaalbedrag op. Voer de vorige procedure eens uit in cel C31 maar klik dan op cel D10 ipv M10. Excel zal dan automatich de volgende formule genereren: =DRAAITABEL.OPHALEN(“Som”;$B$3;”Product”;”P1″)
    Aan het ophalen wordt dus een voorwaarde meegegeven: haal niet alle bedragen op, maar alleen die bedragen waarbij het Product gelijk is aan P1 (let op de aanhalingstekens overal).
    NB1 Dit soort formules kun je ook met de hand intikken, maar het is lastig om dan geen fouten te maken.
    NB2 verwissel Soort en Product en kijk wat de resultaten in C30 en C31 zijn; als het goed is blijven die hetzelfde. We zijn dus niet meer afhankelijk van de structuur van de draaitabel!

Dat in cel D35 de volgende formule staat, zal dan geen verrassing meer zijn:
=DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;”Drie”;”Product”;”P3″)

Maar we gaan nog een stapje verder en maken de formule dynamisch: in cel E35 staat de formule =DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;E33;”Product”;E34)

Bij de voorwaarden staat geen letterlijke tekst meer maar een verwijzing naar de cellen E33 en E34.  Bij het wijzigen van de inhoud van die cellen zal de formule dus ook andere waarden ophalen.

LET OP als je in cel E33 de tekst Vijf intikt levert de formule een foutmelding op! Draaitabel.Ophalen kan alleen maar gegevens ophalen die in de draaitabel voorkomen.

Voorbeeld 2 voor Draaitabel.Ophalen

In tabblad DraaiDatum van het Voorbeeldbestand staat nog een voorbeeld van de functie Draaitabel.Ophalen.

DraaitabelDe draaitabel geeft per dag het aantal records en de omzet weer (de automatisch gegenereerde namen Aantal van Datum en Som van Bedrag zijn overschreven door Aantal respectievelijk Subtotaal).

Willen we alleen het resultaat van een specifieke dag zien dan gebruiken we weer de functie Draaitabel.Ophalen (zie tabblad DraaiDatum):Draaitabel

Maar omdat de datum 2-1-2015 niet voorkomt krijgen we een foutmelding; in cel G5 staat dan ook een iets uitgebreidere formule:
=ALS.FOUT(DRAAITABEL.OPHALEN(“SubTotaal”;$A$3;”Datum”;G3);0)

Dus als de functie Draaitabel.Ophalen een fout genereert dan wordt het resultaat gelijk aan 0 (nul).

Voorbeeld 3 voor Draaitabel.Ophalen

Een van de vele mogelijkheden van een draaitabel is dat je rubrieken kunt groeperen; helemaal interessant is dit voor datums.

In het tabblad DraaiMnd van het Voorbeeldbestand is een indeling naar Jaar, kwartaal en maand te zien. Hoe is dit gemaakt?
Draai1tabelOp basis van de gegevens in tabblad Basis3 is een draaitabel gemaakt met in de Rijlabels de Datum, aan het vak Waarden is nog een keer de Datum toegevoegd en ook het Bedrag.
Maar nu komt het: wanneer je nu in de draaitabel op één van de datums met de muis rechts klikt, krijg je de optie Groeperen: Excel ‘ziet’ dat het datums zijn en heeft al de optie Maanden geselecteerd; klik ook nog op Kwartalen en Jaren en OK.

Standaard zal Excel geen subtotalen voor de nieuwe groepen bepalen: klik rechts op het jaar 2015 en zet de optie Subtotaal Jaren aan; zo ook voor de kwartalen.

Omdat het tabblad Basis3 nog formules bevat, waarbij alle velden willekeurig worden gevuld,  zal het Vernieuwen van de draaitabel ook iedere keer andere resultaten opleveren.

Draai1tabelWanneer je in het tabblad Formules de Einddatum verandert in bijvoorbeeld 29-2-2016, zal het resultaat daarvan meteen zichtbaar zijn in de draaitabel (na Vernieuwen).

Bekijk nu alle formules die in het tabblad DraaiMnd in het blok G3:I13 staan.
Hopelijk spreken die voor zichzelf! Zo niet: neem contact op met G-Info.


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