Tagarchief: Tabel

Grenzen aan de groei – 2

Excel, , , , , , ,
Voorbeeldbestand: ClubRome_2.xlsbDownloaden

LET OP: na het downloaden de extensie wijzigen in xlsb

In het vorige artikel (Grenzen aan de groei – 1) hebben we beloofd dat we een poging zouden wagen om het model uit het rapport van de Club van Rome in Excel te implementeren, althans een vereenvoudigde versie daarvan.
In dat artikel is te lezen hoe we dat zouden willen doen en aan de hand van wat vingeroefeningen hebben we laten zien dat het (in theorie) mogelijk zou moeten zijn.

Die laatste conclusie staat nog steeds, maar helaas hebben we wel moeten constateren dat de hoeveelheid verbanden tussen de diverse variabelen en de daarbij behorende parameters zo groot is dat een totale implementatie heel erg veel tijd gaat kosten. Dus deze keer vormt het Voorbeeldbestand geen afgerond project.
In dit artikel zullen we laten zien hoe ver we gekomen zijn en welke Excel-opties daarbij zijn gebruikt.

Voor eenieder de uitdaging om de ‘handdoek in de ring’ weer op te rapen en het model verder uit te werken!

Nogmaals: het model

(zie het tabblad SystemDynamics van het Voorbeeldbestand)

Dit is een schematische weergave van het (vereenvoudigde) ‘Club van Rome’-model; voor nadere uitleg zie het vorige artikel.

Belangrijk om te weten is het volgende:

  1. de pijlen geven aan welke relaties er tussen de variabelen zijn onderkend
  2. de pijlen laten zien in ‘welke richting’ de beïnvloeding loopt
  3. we onderkennen in het model 3 soorten variabelen: de Inputs, de Kritische Factoren en de Tussen-variabelen.
    De Input-variabelen worden niet beïnvloed door de omgeving, maar kunnen wel in de loop van de jaren variëren.
    De KF’s zijn die 5 variabelen die in alle grafieken van het rapport terugkomen.
    Alle overige vallen onder de categorie Tussen-variabelen.
  4. het rapport van de Club van Rome is in 1972 gepubliceerd; waar in dit artikel naar het verleden wordt verwezen bedoelen we dan ook de periode van 1900 tot en met 1970.
  5. voor diverse variabelen is in de literatuur (zie het tabblad Docu van het Voorbeeldbestand) te achterhalen wat de waardes in het verleden zijn geweest. Dit is de eerste basis van de implementatie van het model.
  6. bij de verdere implementatie heeft iedere variabele een eigen tabblad gekregen (behalve de Inputs; zie hierna). Alle aannames voor de berekening van een variabele staan in het betreffende tabblad vermeld. Vaak is daar ook een grafiek opgenomen van die variabele om snel het resultaat van de aannames te controleren.
Tabblad T15

Implementatie

Tabblad Beschr

In dit tabblad van het Voorbeeldbestand staat een overzicht van alle gebruikte variabelen met daarbij (zover al uitgezocht) de meest relevante berekening:

De betekenis van de Code en de Naam moge duidelijk zijn; de kolom Schema bevat de tekst zoals die op het tabblad SystemDynamics wordt gebruikt. In de kolom Omschrijving staat in het kort een nadere toelichting op de variabele en de laatste kolom bevat de (belangrijkste) formule voor de berekening van die variabele.

NB de codes onder de 10 voor de Tussenvariabelen hebben een extra 0 gekregen; dit om bij standaard-sorteringen altijd direct de juiste volgorde te hebben (anders zou bijvoorbeeld T10 vóór T2 komen).

Tabblad Inputs

Dit tabblad bevat de gegevens van de 4 Input-variabelen. Deze vormen samen de Excel-tabel tblInputs.

Op dit moment zijn alleen de I3 en I4 gevuld en per kolom hebben alle jaren dezelfde waarde. De juiste interpretatie van deze variabelen vergt nog onderzoek.

De KF-tabbladen

Zoals gezegd hebben alle KF’s (en ook de Tussen-variabelen) een eigen tabblad. Deze bladen hebben allemaal dezelfde structuur: de eerste kolom bevat de jaren en de tweede kolom de daarbij behorende waarde. Samen vormen deze 2 kolommen een Excel-tabel met een overeenkomende naam (in bovenstaand voorbeeld tblKF1).

NB de waarde-kolom heeft een voorwaardelijke opmaak: wanneer een cel een formule bevat dan wordt de inhoud in het groen, vet en cursief weergegeven.

In de volgende kolommen staat altijd de code en naam van de variabele, eventueel gebruikte bronnen en de diverse aannames.
De rest van het tabblad wordt gebruikt om zo nodig extra berekeningen uit te voeren, de aannames toe te lichten etcetera.

NB bij KF5 wordt een zogenaamde Eigen functie gebruikt (zie voor een toelichting het vorige artikel, Vingeroefening 2).

De T-tabbladen

Zoals aangegeven hebben deze tabbladen dezelfde structuur als de KF’s. In het voorbeeld hierboven is te zien dat voor T12 een formule wordt gebruikt, waarbij de gevoeligheid (GF) voor de invloed van een variabele kan worden ingesteld. Daarnaast kan aangegeven worden of de beïnvloeding een vertraging V kent en of er over een periode P gemiddeld moet worden (zie het artikel Grenzen aan de groei – 1).

LET OP in dat artikel hebben we, bij het gebruik van een periode, een rekenkundig gemiddelde gebruikt. Dat is, theoretisch gezien, niet juist. Hoewel dat in dit model, bij niet te grote ontwikkelingen in de tijd, niet echt relevant is, hebben we toch de betere methode gehanteerd waarbij het gemiddelde wordt bepaald met behulp van de Pde-machts wortel (ofwel tot de macht 1/P).

Resultaten

In principe zijn de resultaten van het model bekend als alle tabbladen zijn gevuld met waardes en formules. Door ’te spelen’ met de aannames, en vooral met de eventuele GF-, V– en P-waardes kan het model gefinetuned worden.

We zijn gestart met de onderkant van het model, het gedeelte rond de Industriële productie. De bovenkant is op de uitkomsten daarvan gebaseerd. Het implementeren van deze onderkant heeft heel wat hoofdbrekens gekost (wat is de betekenis van de variabelen, hoe zijn deze variabelen van elkaar afhankelijk, hoe kunnen we de afhankelijkheid modelleren, welke GF-, V– en P-waardes geven een zo getrouw mogelijk beeld van de werkelijkheid etcetera).

Door tijdgebrek moet G-Info het verder vullen van het model dan ook aan anderen overlaten.

Wel zullen we hieronder nog laten zien op welke manier de resultaten in Excel gemakkelijk kunnen worden weergegeven.

Tabblad Variabelen

Op dit tabblad in het Voorbeeldbestand staan de 25 variabelen van het model nogmaals in een overzicht. Maar ditmaal met aanvullende gegevens, waarmee we de lay-out van de output kunnen sturen:

  1. in de eerste kolom staat aangegeven welk soort variabele het betreft: Input, KF of Tussen
  2. de tweede kolom geeft aan of de betreffende variabele in de standaard-grafieken van het rapport van de Club van Rome is opgenomen
  3. in dat rapport wordt een variabele altijd op dezelfde (onzichtbare) schaal weergegeven. Dat kunnen we nabootsen door aan te geven welke waarde van de variabele overeenkomt met de onderkant van het grafiekgebied (de kolom GrafMin) en welke waarde met de bovenkant (GrafMax)
  4. in de laatste kolom staat een formule waarmee het label bij de betreffende lijn in de grafiek wordt bepaald. Standaard is het een combinatie van de 3e en 4e kolom, gescheiden door een ‘harde return’:

Dit overzicht is een Excel-tabel met de naam tblVar.

Query’s en verbindingen

Alle resultaten moeten nu nog geschaald worden. Dit kan uiteraard op de diverse tabbladen zelf door extra kolommen toe te voegen, maar we hebben er voor gekozen om dit met behulp van Power Query te implementeren.

NB1 Ziet u de Excel-verbindingen aan de rechterkant van het scherm niet, kies dan in de menutab Gegevens de optie Query’s en verbindingen.

Voor alle Excel-tabellen is een verbinding gemaakt. Wil je bekijken hoe die er uit ziet? Klik rechts op een verbinding en kies de optie Bewerken (of dubbel-klik op een query-naam).

NB2 de Input-query’s zijn iets ingewikkelder omdat daar één specifieke kolom uit de input-tabel moet worden opgehaald.

Bij het bewaren van de query’s is de optie Alleen verbinding maken gekozen en is de optie Toevoegen aan gegevensmodel aangevinkt.
Die laatste optie zorgt er voor dat de query’s in het gegevensmodel van deze Excel-sheet worden opgeslagen.

Het gegevensmodel bevat nog één extra query, q_GrafData. Deze combineert alle andere verbindingen tot één database. Deze database vormt de basis voor een draaitabel en een daarbij behorende grafiek.
Ook deze query is opgeslagen met de eigenschappen Alleen verbinding en Toevoegen aan gegevensmodel.

Bekijk de query door dubbelklikken op de naam.

Tabblad GrafData

Op het tabblad GrafData van het Voorbeeldbestand staat een draaitabel, gebaseerd op de query q_GrafData. Hoe genereer je zo’n draaitabel?

  1. selecteer een lege cel, waar de draaitabel moet komen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het pop-up scherm ziet u dat Excel het gegevensmodel zal gaan gebruiken:
  4. klik op het driehoekje vóór de gewenste query uit het gegevensmodel en sleep de benodigde velden naar de juiste plaats:

In het voorbeeld hebben we ook 3 slicers toegevoegd waarmee het maken van keuzes vergemakkelijkt wordt. Hierboven hebben we met een klik op Ja in de eerste slicer alleen die variabelen geselecteerd, die ook in het rapport van de Club van Rome in de standaard-grafieken voorkomen.

LET OP als er iets aan de parameters van het model wordt gewijzigd dan worden de gegevens op het betreffende tabblad direct gewijzigd. Ook resultaten van formules op andere tabbladen kunnen daardoor wijzigen. De query’s en de draaitabel wijzigen niet automatisch mee! Alles zal vernieuwd moeten worden.
Op het tabblad GrafData wordt met één klik op de betreffende button een VBA-routine gestart die deze totale verversing van het Excel-systeem voor zijn rekening neemt. Dit kan wel enkele minuten duren!

Tabblad Grafiek

Het tabblad Grafiek van het Voorbeeldbestand bevat een draaitabelgrafiek. Dit is de grafische weergave van de gegevens van de draaitabel van het tabblad GrafData. Dus: ook keuzes gemaakt met de slicers worden in deze grafiek meegenomen.

Duidelijk is te zien dat de implementatie van het model nog lang niet klaar is. De industriële productie en de hulpbronnen geven bijvoorbeeld wel al een verwacht verloop, terwijl de blijvende toename van de bevolking of voedsel per hoofd niet reëel is.
Het model in het Voorbeeldbestand is dan ook nog maar voor een klein gedeelte geïmplementeerd. Zoals gezegd: tijdgebrek noopt ons om de rest aan andere Excel-liefhebbers over te laten.

Unpivot

Excel, ,
Voorbeeldbestand: Unpivot.xlsxDownload

Ofwel hoe kom ik van een ‘gewoon’ Excel-overzicht naar een database-structuur.

Uit mijn artikelen mag blijken, dat ik een draaitabel-fanaat ben. De mogelijkheden en flexibiliteit van deze Excel-optie zijn grandioos. Een voorwaarde voor het gebruik daarvan is wel dat de brongegevens op een bepaalde manier klaar staan. Dit heb ik ooit de database-structuur genoemd.
Ben je verantwoordelijk voor een rapportageproces en vraag je iemand een overzicht te maken van bijvoorbeeld per dag de aantallen per regio, dan krijg je in 90% van de gevallen een overzicht zoals hierboven links wordt getoond (in de rijen de dagen en in de kolommen de regio’s), een ‘gewoon’ Excel-overzicht. Een groot voordeel hiervan dat het voor de mens makkelijk leesbaar en begrijpelijk is. Een groot nadeel dat je niet meer flexibel bent. Hoe zorg je er voor, dat totalen toch nog kloppen als je even één regio niet mee wilt laten tellen of wanneer je soms wel en soms niet de subtotalen van de maanden wilt tonen?

In dit artikel daarom nogmaals aandacht voor draaitabellen, maar zeker ook voor een methode om een ‘gewoon’ overzicht geautomatiseerd om te zetten naar een database-structuur.

Brongegevens

In het tabblad Data van het Voorbeeldbestand staat een overzicht met aantallen per werkdag van het eerste kwartaal van 2020, uitgesplitst naar regio.

In kolom B staan de werkdagen met een zodanige opmaak, dat ook de omschrijving van de dag wordt weergegeven (zie het artikel ginfo.nl/data-datums).

NB1 kolom G bevat per dag een totaal over alle regio’s. Excel toont in sommige cellen een klein waarschuwingsdriehoekje. Wanneer je met de muis daarboven ‘gaat hangen’ krijg je te zien wat de waarschuwing is.

In dit geval ‘ziet’ Excel dat naast de getallen onder de regio’s er ook nog een getal links daarvan staat; intern Excel is een datum namelijk een getal.

NB2 de werkdagen zijn als volgt gecreëerd: in cel B3 is de datum 6-1-20 ingevoerd. Daarna is de vulgreep (het kleine vierkantje rechtsonder) naar beneden doorgevoerd. Niet met de linker-muis-toets, maar met behulp van de rechter-muis-toets. Laat u die toets los, dan kunt u diverse opties kiezen; in dit geval Werkdagen doorvoeren.

Unpivot

Voordat we op basis van deze brongegevens een draaitabel (in het Engels Pivottable) kunnen gaan maken moeten we het voorbeeldbestand eerst gaan omzetten naar een database-structuur. Daar gebruiken we Power Query voor (zie ook het artikel ginfo.nl/power-query).

Het voorbeeld-overzicht lijkt op het resultaat van een draaitabel; dit overzicht moeten we dus ont-draaitabellen. Laten we toch maar de Engelse uitdrukking Unpivot gebruiken.
In het tabblad Ovz1 van het Voorbeeldbestand was een kopie van de kolommen B t/m G uit het tabblad Data opgenomen.
Hoe gaan we deze nu Unpivot-ten?

  1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
    In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
  3. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
  4. eerst een paar kleine aanpassingen: verwijder de Totaal-kolom (die maken we straks wel weer met behulp van een draaitabel) en klik links van Datum op het kalender-symbool en wijzig het type van Datum/tijd in Datum
  5. zorg dat de Datum-kolom is geselecteerd en klik dan in de menutab Transformeren in het blok Alle kolommen op het kleine driehoekje naast de optie Draaitabel opheffen
    Kies de optie Draaitabel voor andere kolommen opheffen.
  1. Dat is het al bijna! Verander de kop van
    de 2e kolom in Regio en die van de 3e in Aantal (via dubbelklikken).
  2. Kies dan in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
    In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Draaitabel

Op basis van de nieuwe brongegevens gaan we nu een draaitabel maken:

  1. klik ergens in de nieuwe tabel
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. Bepaal waar de nieuwe draaitabel moet komen en klik op OK
  4. Plaats de Datum in het blok Rijen, Regio in Kolommen en Aantal in het Waarden-blok.
    Afhankelijk van de Excel-versie zal Excel de datums ook al groeperen in maanden. Gebeurt dit bij u niet automatisch dan kunt u later de datums nog (automatisch laten) groeperen.

En we hebben een overzicht, dat uitblinkt in gemak en flexibiliteit (zie het tabblad Ovz1 van het Voorbeeldbestand): gebruik de plus- en min-tekens vóór de maanden om deze uit- of in te klappen, klik op een driehoekje bij de kopjes om een bepaalde item wel of niet mee te nemen, bepaalde sortering door te voeren etc.

NB Ontvangt u gegevens over andere maanden, voeg die dan onderaan de bron-tabel toe. Vergeet niet daarna de met Power Query gegeneerde tabel te vernieuwen én ook de draaitabel te vernieuwen.

Brongegevens2

Maar dezelfde brongegevens kunnen natuurlijk ook worden aangeleverd zoals hiernaast weergegeven.

Het jaar en de maand zijn alleen gevuld wanneer er een wijziging plaats vindt.

Voordat we op bovenstaande manier kunnen gaan ‘Unpivot-ten’ moeten de ontbrekende gegevens in die kolommen worden aangevuld.
Dat kan handmatig door de waardes te kopiëren, maar dat kan wat makkelijker:

  1. selecteer in de Maand-kolom alle cellen met en zonder waardes (op het tabblad Data2 van het Voorbeeldbestand de cellen C3:C64)
  2. klik Ctrl-G (of gebruik de F5-toets, dit is de optie Ga naar), kies de button Speciaal, in het vervolgscherm de optie Lege cellen en klik dan op OK
  3. de eerste lege cel is nu geselecteerd. Type daarin het =-teken en tik op de cursor-omhoog-toets (we maken dus een verwijzing naar de bovenstaande cel, in dit geval =C3).
    LET OP druk dan NIET op Enter maar op Ctrl-Enter. Op die manier worden alle geselecteerde (dus lege) cellen met deze formule gevuld.
  4. Selecteer alle gevulde cellen in de Maand-kolom, tik Ctrl-C en klik dan met de rechter-muisknop ergens in dit gebied en kies de tweede Plak-optie (Waarden). De formules worden door harde waarden overschreven.
    NB deze laatste stap is eigenlijk niet nodig voor het verdere Unpivot-proces.
  5. Doe hetzelfde voor de Jaar-kolom.

Dit overzicht kan dan op dezelfde manier als hiervoor worden getransformeerd naar een database-structuur (zie tabblad Data2 in het Voorbeeldbestand). Met dit verschil: zorg dat in stap 5 eerst de kolommen Jaar, Maand en Dag zijn geselecteerd.

Unpivot2

Maar het bijwerken van de brongegevens kunnen we natuurlijk ook met behulp van Power Query doen (zie het tabblad Ovz2 in het Voorbeeldbestand):
NB ik kreeg een tip van Martien, dat onderstaande methode veel te omslachtig is (zie reactie hieronder); als algemene werkwijze bij het gebruik van gegevens uit andere records is het wel belangrijk om deze manier te kennen.

  1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
  3. In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
  4. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
  5. in deze records moeten op diverse plaatsen dus waarden uit het record daarvoor worden opgehaald; dat kan alleen als de records een index hebben:
    kies in de menutab Kolom toevoegen de optie Indexkolom
  6. nu gaan we een nieuwe jaarkolom toevoegen. Kies in de menutab Kolom toevoegen de optie Voorwaardelijke kolom.

    Vul het vervolgscherm in zoals hierboven.
  7. Het nieuwe resultaat ziet er nu als volgt uit:

    In de formulebalk moeten we “test” vervangen door een formule die verwijst naar het Jaar in het vorige record:
    #”Index toegevoegd”{[Index]-1}[Jaar]
    Ofwel: zoek in het resultaat van de vorige stap (met de naam Index toegevoegd) in het record met het volgnummer Index-1 de waarde in de kolom Jaar.
  8. deze formule moet wel in alle records worden doorgevoerd:
    selecteer de eerste cel in de nieuwe kolom en kies in de menutab Transformeren in het blok Alle kolommen de optie Doorvoeren omlaag
  9. voor de maand wordt op een vergelijkbare manier een nieuwe kolom toegevoegd. De querystap ziet er dan als volgt uit:
    = Table.AddColumn(#”Omlaag doorgevoerd”, “Aangepast.1”, each if [Maand] = null then #”Omlaag doorgevoerd”{[Index]-1}[Maand] else [Maand])
    Vergeet niet de formule in deze kolom door te voeren.
    NB het opzoeken in andere records is voor Excel een intensief proces; het verversen van de gegevens kan even duren.
  10. de oorspronkelijke Jaar– en Maand-kolom en ook de Index kunnen nu verwijderd worden. Verplaats de 2 aangepaste kolommen naar voren en geef die dan de namen Jaar en Maand.
  11. we zijn toch lekker bezig. Laten we zorgen dat we ook een echte datum in het bestand krijgen:
    Kies via het driehoekje linksboven in de tabel (dus naast de nieuwe kolom Jaar) de optie Kolom toevoegen vanuit voorbeelden.
    Vul onder Kolom1 in het vervolgscherm in: 6 jan 2020 en druk op Enter. Uit de formule, die Excel genereert, blijkt dat hij/zij niet weet wat we willen. In de tweede regel plaatsen we 7 jan 2020. Nu begrijpen we elkaar, dus klik op OK.
    De nieuwe kolom heeft de naam Samengevoegd gekregen; verander die in Datum (via dubbelklikken).
    Het type van deze kolom is Tekst (zie de aanduiding ABC naast de naam); dit veranderen we in Datum (klik op ABC).
    Verplaats deze kolom nu zodat die op de vierde plaats komt.
  12. nu nog even de unpivot-truc: selecteer de eerste 4 kolommen en kies de optie Draaitabel voor andere kolommen opheffen.
    Nog even de namen van de twee laatste kolommen wijzigen in Regio en Aantal.
  13. Als laatste: kies in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
    In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Voor het resultaat, een draaitabel en de Power Query’s zie het tabblad Ovz2 van het Voorbeeldbestand.

NB1 een query is altijd te bekijken door in het scherm Werkmapquery’s op de betreffende query te dubbelklikken.

NB2 het kan gebeuren dat een query niet direct in Excel geladen wordt (het ‘wieltje’ bij de querynaam blijft draaien) stop het vernieuwen en probeer het opnieuw:

Grafiek automatisch schalen en labels toevoegen

Excel, VBA (Visual Basic for Applications), ,
Voorbeeldbestand: GrafOpm.xlsmDownload

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

 

Absoluut en relatief in Excel-tabellen

Excel, , ,
Voorbeeldbestand: TabelRef.xlsxDownload

Absolute en relatieve verwijzingen in Excel: dat blijft altijd even opletten!
Maar sinds we met zijn allen steeds meer gebruik maken van Excel-tabellen komt daar ineens een struikelblok(je) bij.

In dit artikel kijken we eerst in algemene zin naar de implementatie van tabel-verwijzingen (Gestructureerde verwijzingen of Structured references in Microsoft-taal); daarna komt een methode langs hoe we ook tabel-verwijzingen absoluut kunnen maken.

Voorbeeld-bestand

In het Voorbeeldbestand staat in het tabblad CelRef een overzicht van bedragen per kwartaal en regio.

Er loopt blijkbaar een afspraak dat, als het bedrag groter is dan 100, er dan een korting van 10% wordt gegeven.
De daarbij gehanteerde formule is:
=ALS(D3>100;10%;0)

Ofwel (geredeneerd vanuit cel E3) als de waarde in cel D3 groter is dan 100, dan komt in E3 de waarde 0,1 anders 0.

NB 10% is voor Excel slechts een opmaak van een numerieke waarde; intern wordt gerekend met 0,1.

De formule is F3 (het bedrag inclusief korting) kan dan zijn:
=D3*(1-E3)

Allebei de formules kunnen naar beneden gekopieerd worden; dat gaat het makkelijkst door dubbel te klikken op het vierkantje rechtsonder aan de rand van een cel (de vulgreep) als die cel is geselecteerd.

Aangezien alle cel-verwijzingen relatief zijn (er staan nergens $-tekens bij de cellen D3 en E3) zullen de formules zich automatisch aanpassen: D3 wordt D4 etc.

LET OP om kolom F niet te breed te maken is de tekst in cel F2 gescheiden door een harde return (Alt-Enter). Een kop nooit over 2 cellen verdelen; altijd een harde return gebruiken.

Willen we weten hoe de verdeling over de kwartalen en/of de regio’s is, dan ligt het maken van een draaitabel voor de hand.

LET OP een bug in Excel kan er bij het aanpassen van de opmaak van de data voor zorgen dat de kop die met een harde return is gescheiden, inhoudelijk wijzigt (alles vanaf de harde return wordt verwijderd); hier is dan een handmatige aanpassing nodig.

Tabel-verwijzingen

Wanneer we dezelfde gegevens in een Excel-tabel opnemen (zie het tabblad TablRef van het Voorbeeldbestand) dan gaat Excel bij het maken van de kortingsformules automatisch met gestructureerde verwijzingen werken in plaats van celverwijzingen:

  1. selecteer cel E3
  2. tik in =als(
  3. klik met de muis in cel D3; Excel plaatst automatisch de tekst [@Bedrag] in de formule
  4. maak de formule af en druk op Enter
  5. Excel zal automatisch de formule naar de overige cellen van dezelfde kolom kopiëren.

Gelukkig hoeven we de gestructureerde verwijzingen dus niet zelf te typen; de notatie daarvan is niet altijd even eenvoudig.
De vierkante haken om de diverse onderdelen van een formule maken voor Excel het verschil tussen gestructureerde en cel-verwijzingen.
Het ‘apestaartje’ (@) voor een kolomnaam betekent dat de waarde uit dezelfde rij uit de betreffende kolom wordt opgehaald.

LET OP in cel F3 staat de formule =[@Bedrag]*(1-[@[Korting%]]); aangezien het %-teken in Excel diverse betekenissen kan hebben zijn nog eens extra vierkante haken om de kolomnaam geplaatst.
Andere speciale tekens die eenzelfde behandeling nodig hebben:
tab, nieuwe regel, Enter-teken, komma (,), dubbele punt (:), punt (.), vierkante haak openen ([), vierkante haak sluiten (]), hekje (#), enkel aanhalingsteken (‘), dubbel aanhalingsteken (“), accolade openen ({), accolade sluiten (}), dollarteken ($), accent circonflexe (^), en-teken (&), sterretje (*), plusteken (+), gelijkteken (=), minteken (-), groter dan (>), kleiner dan (<) en het deelteken (/).

Totalen

Aan een tabel kunnen snel totalen toegevoegd worden:

  1. klik op een cel van de tabel
  2. kies in de menutab Hulpmiddelen voor tabellen de optie Ontwerpen
  3. en vink de optie Totaalrij aan
  4. in de nieuwe regel onderaan de tabel kan dan per kolom het soort totalen gekozen worden (Som, Aantal, Gemiddelde etc.)

Een draaitabel die gebaseerd is op een tabel met totalen, zal deze totalen buiten beschouwing laten (gelukkig!); zie tabblad TablRefTot van het Voorbeeldbestand.

Externe verwijzing naar een tabel

Wanneer u een bepaald overzicht wilt genereren en geen gebruik wilt/kunt maken van draaitabellen dan kunt u via externe verwijzingen gebruik maken van de tabel-gegevens:

  1. selecteer cel J21 in het tabblad TablRefTot
  2. tik in =som.als(
  3. wijs met de muis de bovenkant van de kolom Regio van de tabel daarnaast aan (de cursor wordt dan een pijltje naar beneden) en klik; Excel plaats automatisch de tekst tblKwReg2[Regio] in de formule
  4. tik in ;J$20; en voeg op dezelfde manier de kolom Bedrag toe

NB omdat het verwijzingen buiten de tabel zijn plaatst Excel automatisch de naam van de betreffende tabel voor de rechte haken.

Door de formule nu naar rechts te kopiëren krijgen we ook het totaal-bedrag van regio Zuid te zien. Tenminste: als we op de juiste manier kopiëren!
Selecteer cel J21, klik op Ctrl-C, selecteer cel K21 en klik Ctrl-V en alles gaat goed.
Maar wanneer de vulgreep rechtsonder cel J21 wordt gebruikt, gaat het mis! Dan wordt de verwijzing naar Regio ineens Bedrag en Bedrag wordt Korting%!
DUS: met kopiëren/plakken worden de externe verwijzingen als absoluut beschouwd, maar gebruiken we de vulgreep dan ziet Excel de verwijzingen als relatief!

Ditzelfde effect zien we ook als we verwijzingen naar de tabellen-koppen maken (niet de bovenkant van een kolom aanklikken, maar de cel zelf) en die kopiëren (zie cellen I34 tot en met K35 van het tabblad TablRefTot van het Voorbeeldbestand).

NB wanneer gegevens aan de tabel worden toegevoegd (selecteer cel F32 en druk op Tab; de totalen schuiven automatisch naar beneden) zullen alle overzichten automatisch de nieuwe gegevens meenemen (wel nog via rechtsklikken de draaitabel Vernieuwen).
Dit geldt ook voor formules die naar de totaalrijen verwijzen.

Absoluut en relatief in gestructureerde verwijzingen

In het tabblad TablRefTot2 van het Voorbeeldbestand is een resultaat-overzicht per regio en kwartaal opgenomen. Dit overzicht is zelf ook weer een tabel (met de naam tblResult). Om dit te realiseren wordt de functie SOMMEN.ALS gebruikt.

LET OP de syntax is echt anders dan van SOM.ALS.

De formule in cel J26 berekent de som van die bedragen uit de kolom Bedrag van de tabel tblKwReg2b, waarbij de Regio-kolom van die tabel de koptekst van de kolom Noord van tblResult bevat en waarbij de waarde in de Kwartaal-kolom van de tabel tblKwRegTot2b dezelfde is als die in de Kwartaal-kolom in dezelfde tabel en rij waar deze formule staat.

De formule in J26 kopiëren naar J27 levert geen problemen op welke methode ook gehanteerd wordt (kopiëren/plakken of de vulgreep).

Maar: in het voorbeeld is J26 naar K26 gekopieerd met behulp van de vulgreep; de verwijzingen worden dan als relatief beschouwd. Dat klopt voor de regio-kop in de resultaat-tabel (die moet Zuid worden) maar niet voor de verwijzingen naar de bron-tabel tblKwRegTot2.
Cel J27 is via kopiëren/plakken naar K27 gekopieerd. De verwijzingen blijven absoluut, maar dat klopt dan niet voor de regio-kop!

Oplossing: verwijzingen die absoluut moeten blijven moeten dubbel in de formule worden opgenomen, gescheiden door een dubbele punt (:).
De formule die hier nodig is wordt dan (zie cel J31):
=SOMMEN.ALS(tblKwReg2b[[Bedrag]:[Bedrag]];
tblKwReg2b[[Regio]:[Regio]]; tblResult2[[#Kopteksten];[Noord]];
tblKwReg2b[[Kwartaal]:[Kwartaal]]; tblResult2[@[Kwartaal]:[Kwartaal]])

Wanneer deze formule via de vulgreep naar rechts en naar beneden wordt gekopieerd, krijgen we in alle cellen de juiste resultaten!

NB1 alle kolomnamen hebben vierkante haken, maar de dubbelen krijgen daar omheen nog een extra set rechte haken. Let ook op de notatie bij de @.

NB2 ook de laatste parameter (Kwartaal) moet in dit geval een tabel-aanduiding krijgen, ook al is de verwijzing binnen de tabel zelf.

 

Subtotaal

Excel, , , ,
Voorbeeldbestand: Subtotaal.xlsxDownload

Subtotalen: in de dagelijkse praktijk zijn we meestal niet alleen geïnteresseerd in totalen, maar willen we ook aantallen, bedragen etc. zien per jaar of per maand, per afdeling of per soort of ….
Subtotalen dus.

Eigenlijk vind ik, dat er maar één goede methode is om subtotalen te bepalen en dat is met behulp van draaitabellen. Bij de meeste andere methodes worden namelijk de basis-gegevens aangepast en dat druist in tegen goed Excel-gebruik.

Wanneer een (sub)totaal moet wijzigen, als er regels in de bron-data worden verborgen (maar wie wil dat nou?), alleen dan zul je de functie SUBTOTAAL moeten gebruiken.

Maar omdat het altijd goed is om meerdere alternatieve methodes te kennen, volgt hieronder een uitleg van verschillende manieren om subtotalen te genereren.

Brongegevens

In het Voorbeeldbestand op het tabblad Data staat een serie bedragen (200 regels), waarbij ieder Bedrag drie kenmerken heeft: het Jaar, de Maand en een Regio.

Het totaal van de bedragen kunnen we snel vinden door de gehele kolom te selecteren (klik op de betreffende kolom-letter, in dit geval E) en kijk rechtsonder in de statusbalk:
Afhankelijk van de Excel-versie zie je tegelijkertijd ook het gemiddelde, aantal enz. of je kunt deze oproepen door op het vinkje te klikken.

Maar wanneer je alleen het totaal van 2015 wilt weten of van de regio noord dan wordt het wat ingewikkelder: eerst sorteren op de betreffende kolom, dan alle bedragen van het jaar of regio selecteren en dan onderaan het subtotaal aflezen. Maar ondertussen hebben we iets met onze brongegevens gedaan (namelijk gesorteerd) en dat willen we niet; er kan altijd iets mis gaan bij zo’n activiteit.

En willen we het totaal van een ander jaar of andere regio dan moeten we opnieuw beginnen. Dat moet dus anders kunnen.

Draaitabel

Wat te doen?

  1. selecteer één van de cellen van de brongegevens
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. kies in het vervolgscherm OK
  4. sleep het veld Jaar naar het Rijlabels-gebied
  5. sleep het veld Bedrag naar het Waardegebied

En we hebben een overzicht van het totaalbedrag en subtotalen per jaar.

Maar als we nu toch bezig zijn, dan kunnen we dit overzicht nog wel wat aanpassen:

  1. sleep het Jaar naar het Kolomslabels-gebied
  2. sleep het veld Maand naar het Rijlabels-gebied
  3. sleep het veld Bedrag voor de tweede keer naar het Waardegebied
  4. klik op de 2e Som van Bedrag en wijzig bij Waardeveldinstellingen de Som in Gemiddeld
  5. sleep Waarden naar het Rijlabels-gebied

Dus door het simpel verslepen van velden kunnen we Excel snel diverse totalen en subtotalen laten bepalen.

Filter

De eerste alternatieve methode, die me invalt, is het gebruik van filters:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Filter
  3. maak via de ‘vinkjes’ in de koppen de gewenste selectie
  4. selecteer alle overblijvende bedragen en onder in de statusbalk komt de som (en/of gemiddelde enzovoort) tevoorschijn

Zie het tabblad Filter in het Voorbeeldbestand; zoals daar te zien is trekken de functies SOM, GEMIDDELDE etc. zich niets aan van een filtering.
Dus dit is geen structurele oplossing.

SOM.ALS

Met behulp van ALS-formules kunnen wel subtotalen bepaald worden. In cel E3 in het tabblad Als van het Voorbeeldbestand staat de volgende formule: =SOM.ALS(B7:B206;E2;E7:E206)

Dit betekent, dat als er in het bereik B7:B206 de waarde uit cel E2 staat (in dit geval 2015) dan mag de overeenkomende waarde uit kolom E meegeteld worden.

Hetzelfde idee gaat op voor Aantal, Gemiddelde etc.

LET OP ook deze formules trekken zich niets aan van een mogelijke filtering van de brongegevens.

Wil je een iets complexer subtotaal (bijvoorbeeld van 1 jaar slechts het totaal van 1 maand) dan komt de volgende formule in beeld: =SOMMEN.ALS(E7:E206;B7:B206;E2;C7:C206;F2)

NB misschien wat verwarrend, maar de volgorde van de parameters is net wat anders; zie ook het artikel Tellen-met-voorwaarden.

Excel-tabel

De vorige ALS-formules werken nog makkelijker met een Excel-tabel (zie het tabblad AlsTabel in het Voorbeeldbestand):

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Invoegen in het blok Tabellen de optie Tabel
  3. zorg dat het vinkje aanstaat bij kopregel en klik op OK

De formule om het subtotaal voor 2015/feb te bepalen wordt dan:

=SOMMEN.ALS(tblData[Bedrag];tblData[Jaar];JrSel;tblData[Maand];MndSel)

Hierbij hebben de invoercellen voor het jaar en de maand de namen JrSel, respectievelijk MndSel gekregen.

Ook hier geldt weer, dat filteren in de tabel geen invloed heeft op de formules (al zou het voorbeeld hierboven anders doen vermoeden; wijzig de filtering maar eens!).

Maar de Excel-tabel kent wel een totaliseer-optie, die rekening houdt met filtering:

  1. selecteer een cel in de Excel-tabel
  2. kies in de nieuwe menutab Hulpmiddelen voor tabellen/Ontwerpen  in het blok Opties voor tabelstijlen de optie Totaalrij.

In de cel in de onderste regel in de Bedrag-kolom komt nu automatisch de formule =SUBTOTAAL(109;[Bedrag]); het totaal van de gefilterde bedragen.

In de cel daarvoor is ‘handmatig’ de formule =SUBTOTAAL(101;[Bedrag]) geplaatst; deze zorgt voor het gemiddelde van de zichtbare bedragen (zie hierna voor de betekenis van de codes 109 en 101).

NB probeer het effect uit van de dubbele vinkjes in de onderste regel van de tabel.

SUBTOTAAL

De subtotaal-functionaliteit hoeft niet beperkt te blijven tot Excel-tabellen.
Nee, hebt u een database met gegevens dan kan Excel ook op de volgende manier een subtotalen-overzicht genereren:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Overzicht de optie Subtotaal
  3. in het vervolgscherm kan worden aangegeven voor welk veld er subtotalen moeten komen (Bij iedere wijziging in), u kunt aangeven of u de som of gemiddelde wilt zien (of nog wat anders) en van welk veld u de som wilt zien.
  4. de drie onderste vinkjes spreken voor zich
  5. en klik op OK

Het resultaat is een brij van details en totalen (zie het tabblad Subtotaal in het Voorbeeldbestand).

LET OP Bij het gebruik van deze functionaliteit is het van groot belang, dat de bron-gegevens gesorteerd staan! En wel op de kolom, waarvan de subtotalen bepaald moeten worden.

In het tabblad SubTot2 zijn de gegevens eerste gesorteerd op Jaar en pas toen zijn op bovenstaande manier subtotalen bepaald.

Excel groepeert automatisch alle bij elkaar behorende regels; gebruik de + en – knoppen om meer of minder details te zien. Ook kun je gebruik maken van de cijfers linksboven (in dit geval 1, 2 en 3; er zijn drie niveau’s: totaal, subtotaal per jaar en detail).

NB als de sortering op meer dan 1 kolom is doorgevoerd dan kunnen ook op meerdere niveaus subtotalen worden bepaald. Als bijvoorbeeld binnen het jaar ook op de maand is gesorteerd, dan kunnen eerst subtotalen voor het maandniveau bepaald worden en daarna op jaar-niveau (vergeet niet het vinkje uit te zetten bij Huidige subtotalen vervangen!).

In cel E71 heeft Excel automatisch de formule =SUBTOTAAL(9;E3:E70) geplaatst. De formules voor de andere jaren zijn vergelijkbaar. Voor het totaal in E206 staat echter de formule =SUBTOTAAL(9;E3:E204).
Excel laat tussenliggende subtotalen dus automatisch buiten de berekening!

Ook kunnen meerdere soorten subtotalen onder elkaar geplaatst worden; zie het tabblad SubTot3 in het Voorbeeldbestand.

Ook nu is het zaak om niet te vergeten het vinkje weg te halen bij Huidige subtotalen vervangen.

SUBTOTAAL 2

Maar de functie Subtotaal kunt u ook zelf overal in een sheet plaatsen.
De functie kent in principe 2 parameters: de eerste (het functiegetal) geeft aan welke bewerking moet worden uitgevoerd (som, gemiddelde, aantal) en de tweede welk bereik bij de berekening moet worden meegenomen.
Excel kent 11 soorten berekeningen (zie het tabblad SubTot4a in het Voorbeeldbestand), waarvan het bepalen van het gemiddelde (functiegetal=1), aantal (functiegetal=2) en som (functiegetal=9) de meest gebruikte zijn.

Het functiegetal kan ook 100 groter gekozen worden; het verschil zit hem er in, dat in het tweede geval verborgen regels niet meetellen!

Bekijk op het tabblad SubTot4a het effect van het wijzigen van het functiegetal in regel 40.

Filteren op een of meerdere kolommen heeft op allebei de soorten functiegetallen hetzelfde effect: uitgefilterde waarden worden niet meegeteld.
Het groeperen van regels heeft voor de subtotalen hetzelfde resultaat als het verbergen van regels.
Bekijk het effect van de 3 bewerkingen (filteren, groeperen en verbergen) in het voorbeeld op het tabblad SubTot4b.

LET OP Blijkbaar is dit zo ingewikkeld dat Excel bij het aan- en uitzetten van bewerkingen af en toe de verkeerde resultaten oplevert!!

NB het groeperen van regels heeft verreweg de voorkeur boven het verbergen van regels. In het tweede geval komt het nogal eens voor dat je niet in de gaten hebt dat er regels ‘weg zijn’.