Tagarchief: voorwaardelijke opmaak

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.

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

Aanmeldformulier

Symbolen en vergelijkingen

Office, , , , , ,
Voorbeeldbestand: Vergelijkingen.xlsxDownloaden

Binnen Excel, maar beter gezegd binnen alle Office-onderdelen, is het mogelijk om allerlei nuttige, mooie en/of grappige Symbolen in te voegen.

Maar niet alleen symbolen, ook (wat Microsoft noemt) Vergelijkingen. Daarmee wordt het opzetten van een documentatie-tabblad in Excel of een technisch artikel in Word a-piece-of-cake 😉.

In dit artikel zoomen we in op de diverse mogelijkheden om symbolen in te voegen en op het maken van Vergelijkingen. We maken daarbij gebruik van Excel, maar in de andere Office-onderdelen is de werkwijze vergelijkbaar.

Ook zullen we aandacht besteden aan de zogenaamde ASCII– en Unicodering.

Symbolen-1

Er zijn allerlei manieren om symbolen in te voeren. De eerste die we hier behandelen is het invoegen van Speciale tekens. Kies in de menutab Invoegen in het blok Symbolen de optie Symbool:

In de pop-up selecteert u het tabblad Speciale tekens. Kies bijvoorbeeld het copyright-teken en klik op Invoegen (of dubbelklik op het teken).

Op het tabblad Symbolen van het Voorbeeldbestand ziet u het resultaat.

LET OP wat er op het scherm wordt weergegeven is afhankelijk van het gekozen lettertype voor de cel. Wijzig het lettertype voor de range B4:B6 in Wingdings en het resultaat is

Symbolen-2

Wanneer bovengenoemde pop-up tevoorschijn komt, is altijd automatisch het tabblad Symbolen geactiveerd.
NB1 maak het pop-up-scherm groter om een beter overzicht van alle mogelijkheden te krijgen.

Selecteer het gewenste symbool en klik op Invoegen.

NB2 De meeste lettertypes bevatten veel meer symbolen dan je zou denken. Zorg ervoor dat onderaan bij van: de optie Unicode (hex) is gekozen; je kunt dan diverse deelverzamelingen kiezen:

In het tabblad Symbolen van het Voorbeeldbestand zijn op bovenstaande manier symbolen ingevoegd. De symbolen in de eerste kolom komen uit de deelverzameling Latijn-1; als achteraf het lettertype van deze kolom gewijzigd wordt, dan zullen de symbolen er vaak nog hetzelfde uitzien met slechts typografische verschillen.

Maar kies je bijvoorbeeld als lettertype Wingdings dan is de inhoud ineens anders (zie 2e kolom)! Dit is dan ook een lettertype vol met vreemde symbolen. Er is standaard ook nog een tweede en derde versie van dit font. Het lettertype Segoe MDL2 Assets zit vol met verrassingen (zie kolom 3). Ook met het lettertype Webdings kun je veel mooie symbolen tevoorschijn toveren.

LET OP niet iedere Windows-versie kent dit tweede font; soms kom je Segoe-symbol tegen.

ASCII versus Unicode

Voordat we verder gaan over symbolen moeten we aandacht besteden aan de coderingen van letters en andere symbolen.
In het begin van het computertijdperk is de ASCII-codering bedacht. Ieder teken werd door 1 byte voorgesteld (eigenlijk pas later in de Extended ASCII) met als consequentie dat er maar 256 mogelijkheden waren. Dat was toen geen probleem: alle hoofd- en kleine letters, cijfers en leestekens passen daar in. Zoals hierboven te zien is was er ook voorzien in de meest gangbare breuken, Griekse letters en letters met diakritische tekens.
Later zijn daar de Unicodes bijgekomen; in versie 2.0 is er plaats voor een miljoen tekens. Ruim voldoende voor diverse talen en andere nuttige symbolen.

Wat kunnen we zelf met die codes? Uiteraard zijn die niet (allemaal) te onthouden. Maar natuurlijk wel van symbolen die we vaak nodig hebben; dan is het lastig als je iedere keer in die hele serie symbolen moet gaan zoeken.

Laten we een voorbeeld nemen; het graden-teken.

Even zoeken maar dan hebben we hem; onderaan de pop-up is te zien dat dit symbool in ASCII de (decimale) code 176 heeft. Kiezen we voor de optie Unicode (hex) dan is de code 00B0:

Hebben we nu weer het graden-teken nodig: open het pop-up-scherm, kies een van de 2 coderingen en voer bij Tekencode de overeenkomende code in en klik op Invoegen.

Symbolen-3

Als een lettertype/font symbolen kent die binnen de ASCII-codes voorkomen (en dus een Tekencode hebben in de soort ASCII) dan kunnen deze symbolen nog veel sneller ingevoerd worden (zie het tabblad Symbolen):

  • tik in een cel de tekst Het is nu 19 (inclusief de spatie achter de 9)
  • hou de Alt-toets ingedrukt en tik op het numerieke toetsenbord de code 0176 in.
  • tik in C en druk op Enter.

NB1 deze Alt-invoer kan alleen met de numerieke toetsen aan de rechterkant van het toetsenbord; zorg wel dat de NumLock aanstaat.

NB2 voor een uitgebreidere toelichting over codes zie www.vreemdetekens.nl/

LET OP ASCII-codes vanaf 128 moeten bij dit type invoer voorafgegaan worden door een 0 (nul).

Symbolen-4

Wanneer je de Unicode van een symbool weet kun je deze ook nog op een andere manier gebruiken.
Stel dat we de n (superscript-n) willen gebruiken (zie het tabblad Symbolen van het Voorbeeldbestand).

Via bovenstaande methode kunnen we zien dat deze de Unicode 207F heeft (een hexadecimaal getal).
In cel J2 gebruiken we de Excel-formule Unicode om de code van het symbool in I2 op te halen. Het resultaat is nu 8319; dit is de decimale code.

In cel J3 zien we dat dit klopt; hier is de hexadecimale code omgezet naar een decimaal getal met behulp van de Excel-formule Hex.n.dec.

In cel K3 gebruiken we 3 verschillende methoden om xn te schrijven:
=”x”&UNITEKEN(J3)&”, x”&UNITEKEN(HEX.N.DEC(I3))&” of x”&I2

Op het tabblad Unicode van het Voorbeeldbestand ziet u een groot overzicht van allerlei symbolen met de daarbij behorende code.

Ook laten we daar zien hoe je symbolen in een Gegevensvalidatie gebruikt, inclusief het gebruik van de codes in een Voorwaardelijke opmaak.

Symbolen-5

Misschien nog wel de makkelijkste methode om symbolen in te voegen is kopiëren-plakken!
Kijk of je ergens het gewenste symbool kunt vinden (bijvoorbeeld op www.vreemdetekens.nl) , selecteer het met de muis, Ctrl-C, ga naar de gewenste plaats in Excel en Ctrl-V.

Op het tabblad Symbolen hebben we deze methode gebruikt om een aangepaste getal-opmaak te definiëren in de kolommen N en O.

Symbolen-6

Als laatste een methode die overal in Windows werkt (vanaf Windows-10): druk de Windows-toets () in, hou vast en druk dan op de punt-komma-toets.

Onderstaande pop-up opent waarna u de beschikking hebt over een arsenaal aan nuttige (?) symbolen.

Zie het tabblad Symbolen van het Voorbeeldbestand.

Vergelijkingen (standaard)

Excel kent ook een menu-optie Vergelijking; ook te vinden via de menutab Invoegen in het blok Symbolen.
Achter het vinkje zit een serie standaard-formules. Fijn, maar het is wel een paar weken geleden dat ik een Fourier-reeks nodig heb gehad 😁.

Laten we maar aannemen dat ze als voorbeeld bedoeld zijn. Klik op één van deze voorbeelden en het resultaat komt in Excel terecht:

Via de hendels kunt u het formule-blok verschuiven, de grootte wijzigen en via de menutab Hulpmiddelen voor tekenen/Opmaak, die nu beschikbaar is gekomen, kunnen diverse opmaak-opties ingesteld worden. Wilt u de lettergrootte aanpassen? Gebruik de mogelijkheden in de menutab Start.

NB het lettertype wordt automatisch ingesteld op Cambria Math; dit is niet te wijzigen.

Op het tabblad Verg1 van het Voorbeeldbestand staan enkele standaard-formules. Ook hebben we daar even gecontroleerd of de Taylor-uitbreiding voor het berekenen van ex wel klopt😉

De standaard-formules kunnen wel gebruikt worden als basis om andere formules te ‘schrijven’, want alle formules zijn nog aanpasbaar.

Vergelijkingen (handmatig)

Over schrijven gesproken: je kunt de gewenste formule/vergelijking handmatig invoeren: kies in de menutab Invoegen in het blok Symbolen het vinkje achter Vergelijking. Helemaal onderaan staat dan de optie Handgeschreven vergelijking.

Met wat proberen, Gummen, Selecteren en corrigeren en opnieuw Schrijven kom je al snel een heel eind.
Maar het lukte mij niet om de komma tussen de 1 en de 2 te krijgen; ook het extra streepje achter de a in de noemer kreeg ik niet weg.

Gelukkig: na het kiezen van Invoegen in de pop-up kan de door Excel gegenereerde formule nog gewijzigd worden:

Vergelijking (kwadratische formule)

In de standaard-vergelijkingen van Excel komen ook de stelling van Pythagoras en de kwadratische formule voor (zie het tabblad Verg2 van het Voorbeeldbestand). De laatste wordt in het wiskundeonderwijs meestal de abc-formule genoemd.

In de standaardformule (rood omcirkeld) staat voor het =-teken alleen x. Meestal gebruiken we x1,2 om aan te geven dat er in principe 2 oplossingen zijn van een 2e graadsvergelijking; de eerste krijgen we door de + voor het wortelteken, de tweede door de wortel af te trekken van -b.

De ‘groene’ formule is veel beter leesbaar dan

Dit is de zogenaamde Lineaire vorm (in tegenstelling tot de vorige die Professioneel wordt genoemd). Je kunt als volgt switchen tussen de twee verschijningsvormen

  1. selecteer een formule door er op te klikken
  2. er verschijnt een nieuwe menutab Hulpmiddelen voor vergelijkingen
  3. klik daar op het tabblad Ontwerpen
  4. in het blok Extra vooraan ziet u de 2 opties

Het voordeel van de Lineaire variant is, dat deze soms makkelijker te corrigeren is.

In het tabblad Verg2 gebruiken we de abc-formule om de oplossingen van 2e graadsvergelijkingen te vinden.

Wanneer we de cellen D17:D19 namen geven is de formule veel beter leesbaar:
=(-b+WORTEL(b^2-4ac_))/(2*a)

NB voor cel D19 kunnen we niet de naam c gebruiken (een gereserveerde code voor Excel), vandaar de underscore erachter.

In de formule in E18 wordt gebruik gemaakt van de inhoud van cel H17. Deze bevat een x met het symbool 2 (Alt-253).

NB de overgangen naar een nieuwe regel in de formule zijn voor de leesbaarheid; druk op Alt-Enter.

Vergelijking (hypotheek)

In het tabblad Hyp staat een voorbeeld van een hypotheekberekening.
Excel kent daarvoor de standaard-functie Bet, maar zoals je hierboven kunt zien kun je ook een ‘eigen’ formule gebruiken.

Door gedeeltes van de toelichtende vergelijking dezelfde kleuren te geven als de overeenkomende cellen wordt snel het verband duidelijk:

Grenzen aan de groei

Een ander voorbeeld van een formule die je binnen de Office-onderdelen makkelijk kunt maken, komt uit het ‘rapport van de Club van Rome’.

Als je de voorraad van (in dit geval) grondstoffen weet en ook de hoeveelheid die je per jaar nodig hebt, dan weet je ook hoeveel jaar je nog vooruit kunt met die voorraad (de S hierboven). Maar een van de fundamentele bijdrages van de ‘club’ aan de discussie was om ook rekening te houden met de groei van de behoefte. Dit levert het aantal jaren op dat je werkelijk vooruit kunt (e). Deze is vaak dramatisch lager dan de S!

In het tabblad Groei van het Voorbeeldbestand is het effect van de groei van de behoefte grafisch weergegeven.

Binnenkort eens kijken of we de andere variabelen uit het model ook kunnen vinden, inclusief de aannames die daar zijn gebruikt. Dan kunnen we proberen het model in Excel na te bouwen.

Technologie

Nog maar even verder op de nostalgische tour. Tijdens mijn studie op de THE kregen we een handboekje (voor ƒ 1,00). Vol met interessante formules en grafieken.

Laten we de formule voor het Reynolds-getal eens gaan opbouwen (zie het tabblad Technologie van het Voorbeeldbestand):

  1. kies in de menutab Invoegen in het blok Symbolen de optie Vergelijking (dus NIET op het vinkje erachter klikken). Een blok met Typ hier uw vergelijking komt tevoorschijn in het midden van het scherm.
  2. tik in Re=
  3. nu moet er een breuk komen: klik in de menutab Ontwerpen in het blok Structuren op het vinkje onder Breuk
  4. we kiezen de eerste breuk, waarbij de gestippelde vierkantjes aangeven dat daar nog iets ingevuld kan worden
  5. selecteer het vierkantje in de teller en klik op het knopje Meer, rechtsonder bij Symbolen. Kies daar de ρ (Griekse letter rho).
  6. de rest van de teller is dan eenvoudig; tik in .U.2m
  7. selecteer het vierkantje in de noemer en klik op het knopje Meer, rechtsonder bij Symbolen. We hebben de η (eta) nodig; die is niet te vinden bij Elementaire wiskunde. Kies rechtsboven bij Symbolen de optie Griekse letters en zoek de juiste letter.
  8. klik ergens buiten het formuleblok.

LET OP als je een formule gaat opbouwen zorg dan dat je elke keer goed vooruitkijkt. Zoals hiervoor bij het maken van een breuk moet je eerst de breuk-optie kiezen waarna je dan pas de teller en noemer kunt invullen.
Wil je x2 tikken, kies dan eerst optie één binnen Script en vul dan de twee vierkantjes met de x en de 2 (of selecteer de 7e optie natuurlijk!).

NB door te switchen naar Lineair (zie hierboven) is het mogelijk om bijvoorbeeld een vergeten breuk te corrigeren door achter de bestaande formule een / te plaatsen. Ga daarna weer naar Professioneel.

Het vergt wat onderzoek om alle mogelijkheden voor het maken van formules te doorgronden. Zelfstudie is zeer noodzakelijk. Zoals eerder gezegd werkt het in de praktijk prima om eerst ‘met de hand’ zo goed mogelijk de formule te schrijven en dan met de Excel-hulpmiddelen zo nodig nog aanpassingen door te voeren.

Afstudeerscriptie

Naar aanleiding van de voorgaande voorbeelden herinnerde ik me dat mijn afstudeerscriptie (van 1978) vol stond met formules, die gedeeltelijk met een typemachine en gedeeltelijk met de hand moesten worden gemaakt (zie hiernaast).

Wanneer er ergens iets fout was (of er moest nog wat tekst tussen) dan moest zo’n pagina opnieuw. Soms kon je gelukkig volstaan met letterlijk knippen en plakken.

NB Wanneer je een keer de slaap niet kunt vatten, kan ik deze lectuur van harte aanbevelen: pure.tue.nl/ws/portalfiles/portal/86904037/297491.pdf

In het tabblad Menging van het Voorbeeldbestand heb ik het belangrijkste resultaat van mijn onderzoek nog eens nagebouwd. Binnen een paar minuten was dat gebeurd, terwijl dat toen op een typemachine heel wat meer tijd kostte.

En een correctie doorvoeren is een fluitje van een cent.

Op het tabblad Menging is ook te zien dat het schrijven van een differentiaal-vergelijking niets meer voorstelt; bovenaan staat een stukje uit mijn afstudeerscriptie, daar onder een Excel-formule.

Jammer dat ik nu nooit meer iets van doen heb met differentiaalvergelijkingen😉

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

Aanmeldformulier

Kringverwijzingen en iteraties

Excel, ,
Voorbeeldbestand: GameLife.xlsxDownload

Iedereen maakt het wel eens mee dat Excel ineens een melding geeft dat er een Kringverwijzing is gesignaleerd.

Wat betekent dat? Hoe weet je of er Kringverwijzingen zijn? Hoe los je het op? En kunnen we kringverwijzingen zinvol toepassen in de praktijk? Om de laatste vraag alvast te beantwoorden: jazeker, en dat noemen we dan Iteraties.

Kringverwijzing

Soms gebeurt het ineens: ben je ijverig bezig een mooie spreadsheet in elkaar te zetten, krijg je ineens de volgende melding:

Deze melding kun je ook krijgen als je een bestaande spreadsheet opent.

De uitleg in de melding maakt wel duidelijk wat er aan de hand is: een formule in een cel verwijst naar deze cel zelf (direct of indirect). Excel weet dan niet wat ie moet doen: 1 keer berekenen of, als dat resultaat is berekend, nog een keer? En daarna nog een keer? In de standaard-instelling (waarbij Excel alles automatisch herberekent) stopt de berekening en is het resultaat van de formule gelijk aan 0.

Wanneer Excel een kringverwijzing tegenkomt dan wordt dit zichtbaar in de status-balk onder aan het Excel-venster.

LET OP1 ook al staat er in de statusbalk maar één cel, er kunnen meerdere kringverwijzingen in het spreadsheet voorkomen.

LET OP2 als er kringverwijzingen zijn dan moet je normaal gesproken deze direct oplossen; ALLE resultaten van berekeningen zijn daardoor onbetrouwbaar.

Een paar voorbeelden (zie het tabblad Kring van het Voorbeeldbestand):

In cel B2 staat de formule =B2+1.

Er staat nu de waarde 0, dus het resultaat zou dan 1 moeten zijn. Maar als er 1 staat in de cel, dan wordt het resultaat van de formule 2 enzovoort.
Meestal betekent dit dat er naar een verkeerde cel wordt verwezen of dat de formule in de verkeerde cel wordt geplaatst.
De oplossing is simpel: pas de verwijzing in de formule aan of verplaats de formule.

Een vaker voorkomende fout bij het maken van een spreadsheet: in de verwijzing in een Som-formule wordt ook verwezen naar de cel, waarin de formule staat (in het voorbeeld hiernaast D6).
Oplossing: zorg dat de verwijzing in de formule naar de juiste cellen verwijst.

We kennen ook een zogenaamde verborgen kringverwijzing; deze zijn soms moeilijker op te sporen omdat deze niet altijd optreden.
In het voorbeeld hiernaast staat in cel F6 een formule die afhankelijk van de waarde in F2 twee verschillende berekeningen uitvoert.
Als F2 de waarde 1 bevat kan Excel de berekening gewoon uitvoeren en zal dan ook niets melden; bevat die cel echter een andere waarde dan wordt de tweede berekening uitgevoerd en dan ontstaat er wel een kringverwijzing.
Oplossing: zorg dat de verwijzing in de formule naar de juiste cellen verwijst.

Een voorbeeld van een indirecte kringverwijzing: in cel J2 staat een verwijzing naar de cellen H2:H4. Niets geks.
Behalve als we kijken naar de inhoud van cel H4: =SOM(H2:J3), dus hierbij wordt ook impliciet verwezen naar de cel J2.

Dit soort fouten zijn vaak moeilijker te achterhalen. Maar gelukkig helpt Excel ons door een lijntje tussen de 2 cellen te tekenen. Dubbelklik hierop en Excel toont afwisselend de inhoud van de 2 betrokken cellen.

Toepassing Kringverwijzing

Zoals gezegd: meestal duidt het optreden van kringverwijzingen er op, dat er ergens in één of meer formules fouten staan. Maar soms wil je bewust gebruik maken van dit fenomeen.

Eigenlijk overal waar een eindstand van een periode weer gebruikt wordt als input voor de berekeningen in de volgende periode zouden we kringverwijzingen kunnen tegenkomen/gebruiken.

Bijvoorbeeld wanneer we het verloop van een hypotheek in de tijd willen berekenen, zullen we de schuldrest op het einde van de maand gebruiken om de rente en aflossing van de volgende maand te berekenen. Daardoor kennen we weer de schuldrest op het einde van die periode . Deze gebruiken we dan weer als input voor de volgende maand etc.

Een simpeler voorbeeld staat op het tabblad Corona van het Voorbeeldbestand .
De berekening kent twee input-variabelen: de zogenaamde R en het gemiddeld aantal dagen dat het duurt om een besmetting over te dragen.
Bij een R=2 en het aantal dagen=5 ligt, wanneer we op dag 0 met 1 besmetting starten, na 50 dagen het daaruit voorkomende aantal besmettingen al boven de 1.000.

In dit flink versimpelde model bepalen we het aantal besmettingen op het einde van een periode door de stand van de vorige periode te vermenigvuldigen met R.

Als we niet geïnteresseerd zijn in de tussenresultaten zouden we het eindresultaat (in het voorbeeld in cel G6) ook kunnen berekenen door het het vorige resultaat in die cel te vermenigvuldigen met de R-waarde.
Helaas: Excel voert die berekening normaal gesproken niet uit, want er ontstaat daar een kringverwijzing (zie op de statusbalk linksonder). En als die het wel zou kunnen/willen berekenen: wanneer moet die stoppen? Na 10 keer zoals in de berekening hiervoor of na 20 of na ???

NB1 in cel F6 staat de berekening =F6+GemOvDgn, dus ook daar ontstaat een kringverwijzing.

NB2 er ontstaan geen kringverwijzingen wanneer Opnieuw (de waarde in cel G2) gelijk is aan Ja. Dit gebruiken we om ons model op een standaard-waarde in te stellen (zie hierna).

We gaan de Excel-omgeving aanpassen zodat we de berekening in G6 WEL kunnen uitvoeren:

  1. kies in de menutab Bestand de button Opties
  2. klik op Formules en vul de Berekeningsopties in zoals hieronder, dus op handmatig berekenen en Iteratieve berekeningen inschakelen:
  3. klik op OK

Nu kunnen we gaan rekenen:

  1. vul cel G2 met de waarde Ja
  2. we hebben Excel op Handmatig berekenen gezet, dus druk op F9 of klik onderaan in de statusbalk op Berekenen
  3. geef cel G2 de waarde Nee
  4. en kies opnieuw Berekenen:
  5. telkens wanneer we herberekenen, verandert het resultaat:
  6. wil je opnieuw beginnen? Vul cel G2 met de waarde Ja, wijzig eventueel de R en het aantal dagen in kolom C, vul G2 met Nee en kies Herberekenen zoals in stap 2.

NB in de Berekeningsopties hebben we het aantal Iteraties op 1 gezet. Iedere keer wanneer we herberekenen, gaat Excel dus alle cellen één keer langs om formules in die cellen te berekenen. Zet je het aantal Iteraties hoger dan voert Excel de berekeningen dat aantal keren achter elkaar uit.

LET OP vergeet niet de Berekeningsopties weer op Automatisch berekenen te zetten wanneer je met een ander spreadsheet aan de slag gaat!

Game of life

Het vorige voorbeeld laat goed de werking van Kruisverwijzingen/Iteraties zien. Een ander bekend (en zinvoller) voorbeeld is Conway’s Game of Life.

De Britse wiskundige Conway bedacht dit ‘spel’ in 1970 om de groei van een organisme na te bootsen, gebaseerd op het werk van John von Neumann.

In een 2-dimensionaal rooster heeft iedere cel óf de waarde Bewoond óf de waarde Onbewoond. Voor elke levenscyclus is iedere cel afhankelijk van de status van zijn 8 buren:

  1. iedere bewoonde cel met minder dan 2 bewoonde buren sterft (onderbevolking)
  2. iedere bewoonde cel met 2 of 3 bewoonde buren blijft bewoond
  3. iedere bewoonde cel met meer dan 3 bewoonde buren sterft (overbevolking)
  4. iedere onbewoonde cel met precies 3 bewoonde buren wordt bewoond (reproductie)

Hiernaast zie je de ‘vertaling’ van een patroon naar het aantal buren.

Onderstaande uitwerking van de Game of Life in Excel is gebaseerd op een artikel uit 2007: www.microsoft.com/…./iteration-conways-game-of-life/

Om het ‘levenspel’ in Excel te implementeren maken we de volgende afspraken:

  • voor het Conway-rooster gebruiken we natuurlijk het raster van een tabblad
  • waar Conway een oneindig groot rooster gebruikt, beperken we ons tot 30 rijen en 40 kolommen
  • in een ‘bewoonde’ cel plaatsen we een 1, anders een 0
  • via Voorwaardelijke opmaak vullen we een bewoonde cel met een groene kleur, anders wordt de cel wit

Om iedere levenssstap na te bootsen vertalen we de Conway-regels in een formule:

  1. eerst als een cel bewoond is: als het aantal bewoonde buren 0 of 1 is dan wordt de cel 0, als dat aantal 2 of 3 is dan blijft de cel 1, anders wordt de cel 0. In een Excel-formule: KIEZEN(buren+1);0;0;1;1;0;0;0;0;0)
    NB de laagste waarde van de eerste parameter moet 1 zijn, dus we verhogen het aantal buren met 1.
    Iets compacter: KIEZEN(MIN(5;buren+1));0;0;1;1;0)
  2. als we te maken hebben met een onbewoonde cel: ALS(buren=3;1;0)

Gecombineerd: ALS(celwaarde=1;KIEZEN(MIN(5;.buren+1);0;0;1;1;0);ALS(buren=3;1;0))

In het tabblad 1.Game van het Voorbeeldbestand staat een implementatie van het spel.

De bewoonde buren van een cel liggen vast in een ander tabblad (2.Buren).

Het aantal bewoonde buren van bijvoorbeeld cel E6 in het tabblad 1.Game liggen vast in cel E6 van het tabblad 2.Buren.

De gecombineerde formule hierboven wordt dan voor cel E6: =ALS(E6=1;KIEZEN(MIN(5;.’2.Buren’!E6+1);0;0;1;1;0);ALS(‘2.Buren’!E6=3;1;0))

Nu zijn we er bijna: we moeten ook nog een start-situatie kunnen vastleggen.

Cel C2 (met de naam Reset) geven we een waarde WAAR als we het rooster in de begin-situatie willen krijgen; we passen daarom de formules in het rooster aan:
=ALS(Reset;Basis!E6;ALS(E6=1;KIEZEN(MIN(5;’2.Buren’!E6+1);0;0;1;1;0);ALS(‘2.Buren’!E6=3;1;0)))
en leggen de gewenste begin-situatie in het tabblad Basis vast.

NB1 om het systeem te resetten kun je ook een vinkje plaatsen in het hokje in kolom E; dit selectievakje is gekoppeld aan cel E2.

NB2 och ja, vergeet niet de Berekeningsopties aan te passen zoals hierboven beschreven en het spreadsheet handmatig te herberekenen door bijvoorbeeld F9 in te drukken.

NB3 cel C3 (met de naam Iteratie) bevat een teller voor het aantal uitgevoerde iteraties vanaf een reset: =ALS(Reset;0;Iteratie+1)

Voorbeeld-run (zorg dat de Berekeningsopties goed staan):

  1. vul de Reset-cel met de waarde WAAR (of vink het selectievakje aan)
  2. herbereken het spreadsheet (F9 of Berekenen in de statusbalk aanklikken)
  3. vul de Reset-cel met de waarde ONWAAR (of vink het selectievakje uit)
  4. herbereken het spreadsheet (F9 of Berekenen in de statusbalk aanklikken)
  5. herhaal stap 4 zo vaak als gewenst (dit is het resultaat na 6 iteraties)

Na 13 iteraties ziet het scherm er zo uit:

maar na 18 iteraties blijft een zogenaamde oscillator over:
<–>

NB1 dit voorbeeld eindigt in een 2-periode oscillator. Veel andere voorbeelden eindigen in een stabiele situatie (start bijvoorbeeld met een rijtje van 12 bewoonde cellen) of een leeg rooster (start met een rijtje van 6). Soms ontdek je een Life-game dat zich maar blijft ontwikkelen.

NB2 op internet zijn veel voorbeelden van dit spel te vinden (zie bijvoorbeeld en.wikipedia.org/wiki/…..Examples_of_patterns en playgameoflife.com).

NB3 Het rooster in het tabblad 1.Game (en alle anderen) is redelijk beperkt (30 x 40) maar voldoet voor de meeste bekende voorbeelden. Een mogelijke verbetering: pas op het tabblad 2.Buren de formules op de grenzen aan; vervang verwijzingen naar cellen buiten het rooster door verwijzingen naar cellen op de grens aan de andere kant van het rooster.

In het Voorbeeldbestand staan nog diverse tabbladen met mogelijke start-posities; de namen daarvan beginnen met Basis. Kopieer het rooster daaruit naar het tabblad Basis en herstart een run zoals hiervoor beschreven.

Wanneer je het tabblad Basis450 gebruikt zul je zien dat het heel lang duurt voordat er een stabiele situatie ontstaat. In dit soort gevallen is het handig om eerst het aantal iteraties per herberekeningsronde te verhogen in de Berekeningsopties:

NB In dit systeem hebben we gebruik gemaakt van de volgende werking van Excel: het herberekenen van cellen begint op het eerste tabblad, daarna het tweede etc. Dus pas als alle berekeningen op het tabblad 1.Game zijn uitgevoerd, worden de cellen van het tabblad 2.Buren berekend.

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

Aanmeldformulier

Snelle analyse

Excel, , , ,

Voorbeeldbestand: SnelAnalyse.xlsxDownload

Op de site van G-Info hebben we het al heel vaak gehad over allerlei methoden om met Excel gegevens te analyseren.

Uit alle vragen die we krijgen blijkt dat de meeste mensen daarbij worstelen met Voorwaardelijke opmaak, grafieken en draaitabellen. Daar hebben we dan ook al diverse keren aandacht aan besteed.

Waar we het nog niet over hebben gehad, is dat je Excel vaak automatisch het (voor)werk kunt laten doen!
Microsoft heeft (vanaf versie 2013) een optie ingebouwd, die ze Snelle Analyse genoemd hebben. Naar mijn idee zijn ze daarbij eigenlijk te bescheiden: ik zou dat eerder Zeer snelle analyse met heel erg veel mogelijkheden hebben genoemd, maar dat is natuurlijk wat lang in een menu 😉

De diverse resultaten van Snelle analyse kunnen nog handmatig naar wens worden aangepast; ook kan het een goede start zijn om de diverse onderdelen van Excel die daarbij gebruikt worden, beter te begrijpen.

Basis-gegevens

In het Voorbeeldbestand staat op het tabblad Data een blokje gegevens dat we in dit artikel zullen gebruiken om te laten zien hoe snel (en goed!) Snelle Analyse werkt.

De getallen worden door de functie ASELECTTUSSEN bij iedere wijziging in de werkmap (of na het drukken op F9) opnieuw gegenereerd. Het effect daarvan zie je dan direct in de verschillende analyses terug.

Snelle analyse starten

Selecteer je in Excel meer dan één cel tegelijk dan zie je rechtsonder bij die selectie de button Snelle analyse tevoorschijn komen. Zoals je kunt zien kun je ook de toetscombinatie Ctrl-Q gebruiken.

NB de button komt alleen tevoorschijn als je aaneengesloten cellen selecteert.

Klik je op de button dan verschijnt het Snelle analyse-menu:

Je kunt dus op 5 manieren je gegevens analyseren: met Opmaak (beter gezegd Voorwaardelijke opmaak), door middel van Grafieken, door automatisch Totalen (en andere statistieken) toe te laten voegen, door het gebruik van Tabellen (inclusief draaitabellen) of met Sparklines.

De mogelijkheden binnen deze opties kunnen afhankelijk zijn van de geselecteerde cellen.

Opmaak

De (voorwaardelijke) opmaak willen we alleen toepassen op de getallen in het overzicht. Dus selecteer eerst de cellen met die getallen en klik dan op de Analyse-button (of druk op Ctrl-Q). De Opmaak-optie is al geselecteerd. Wanneer je nu met de muis over de 6 verschillende menu-keuzes gaat zie je direct het resultaat daarvan in de brongegevens.

Wanneer je de opmaak daadwerkelijk aan je gegevens wilt toevoegen dan moet je op de betreffende keuze klikken (zie het tabblad Opmaak van het Voorbeeldbestand).
Ook kun je een combinatie van voorwaardelijke opmaak toevoegen door meerdere keuzes achter elkaar te maken.
Wil je de opmaak verwijderen kies dan de laatste optie in het Opmaak-menu.

Gegevensbalk

Via de eerste keuzemogelijkheid worden Gegevensbalken aan de geselecteerde cellen toegevoegd. Hierdoor krijg je snel inzicht in hoe de getallen zich ten opzichte van elkaar verhouden.

NB Excel zal om de lengte van de gegevensbalken te maken alle getallen uit de geselecteerde cellen vergelijken. Wil je dat Excel per kolom (of rij) de waardes vergelijkt dan moet je de opmaak voor iedere kolom (of rij) apart instellen.

Kleurschalen en Pictogrammen

Ook via de opties Kleurschalen en Pictogrammen kun je de onderlinge verhoudingen van de geselecteerde getallen zichtbaar maken.

NB de voorwaardelijke opmaak kan nog naar wens worden aangepast:

  1. selecteer één van de cellen met opmaak
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. kies Regels beheren….
  4. kies Regel bewerken
  5. pas de diverse opties naar wens aan

Groter dan

Door middel van de vierde opmaak-optie kun je getallen die groter zijn dan een bepaalde waarde een opmaak meegeven.

Kies je deze mogelijkheid dan moet je een grenswaarde opgeven; in het voorbeeld verwijzen we daarbij naar cel D32. Door deze cel te wijzigen zal de opmaak zich automatisch aanpassen.

Bovenste 10%

Deze optie spreekt voor zich. De cellen die waarden bevatten die bij de hoogste 10% horen worden gemarkeerd.

Door de opmaak-regel aan te passen kun je ook een heel ander gedeelte van de getallen een opmaak geven:

Opmaak wissen

Met de 6e keuzemogelijkheid kun je bestaande opmaak wissen.

NB op het tabblad Opmaak van het Voorbeeldbestand staan ‘gewone’ Excel-overzichten. Wanneer je deze uitbreidt met nieuwe gegevens moet je de opmaak zelf nog aan die nieuwe gegevens toevoegen.
In het tabblad Opmaak2 staan dezelfde overzichten, maar dan in de vorm van een Excel-tabel. Als je daar gegevens toevoegt, krijgen de nieuwe gegevens automatisch de bijbehorende opmaak.

Grafieken

Grafieken worden veel gebruikt als rapportagetool, maar kunnen ook een goed analyse-hulpmiddel zijn.

Met Snelle analyse is een grafiek maken een fluitje van een cent:

  1. voor een grafiek hebben we niet alleen de getallen nodig maar ook de omschrijvingen daar omheen.
  2. selecteer alle benodigde cellen en klik op de button Snelle analyse
    of
    selecteer één van de cellen met getallen en druk op Ctrl-Q
  1. klik in het submenu op de optie Grafieken en ga met de muis over de voorgestelde grafieken. Welke dit zijn is afhankelijk van de brongegevens.
  2. Klik op een van de grafieken, et voilà! Zie het tabblad Grafieken van het Voorbeeldbestand.

Komen er nieuwe gegevens bij, dan zul je óf de de brongegevens van de grafiek moeten aanpassen (rechtsklikken op de grafiek en Gegevens selecteren kiezen) óf de grafiek op bovenstaande manier opnieuw moeten maken.

Dat kun je ondervangen door de brongegevens als Excel-tabel vast te leggen (zie het tabblad Grafieken2 van het Voorbeeldbestand).
Helaas, dan werkt het selecteren van de gegevens met Ctrl-Q niet goed. Doe dan het volgende:

  1. selecteer één van de cellen met getallen en druk op Ctrl-A
  2. maar we hebben ook de kopregel nodig; druk nogmaals op Ctrl-A
  3. druk dan Ctrl-Q of kies de Snelle analyse-button.

Totalen

  1. we willen natuurlijk alleen van de getallen in het binnenblok de totalen berekenen (en bijvoorbeeld niet van de datums ook al zijn dat voor Excel ook getallen); dus selecteer de cellen C4:F15 (zie het tabblad Totalen van het Voorbeeldbestand)
  2. druk op Ctrl-Q of klik op de analyse-button
  3. kies de optie Totalen en klik op de gewenste functie (Som, Gemiddelde, Aantal, Totaal % of Voorlopig …)
  4. Excel plaatst formules onder het geselecteerde blok getallen en voert daarmee de gewenste berekening uit.

NB1 zijn de cellen onder de selectie niet leeg, dan krijg je een waarschuwing of je deze wilt overschrijven. Wil je deze gegevens bewaren maak dan eerst ruimte door een lege regel in te voegen voordat je bovenstaande handelingen uitvoert.

NB2 bij de eerste 5 opties is een regel blauw gekleurd; dit betekent dat Excel de formules onderaan in een regel plaatst (en totalen per kolom bepaalt). De 6e optie (en verder, klik op het pijltje aan de rechterkant) laten een gekleurde kolom zien. Als je die optie gebruikt zal Excel formules aan de rechterkant plaatsen en dus totaalberekeningen over de rij uitvoeren.

NB3 Excel plaatst formules in het tabblad. Deze zijn zodanig opgezet dat bij het toevoegen van nieuwe gegevens de berekeningen met een paar kleine aanpassingen weer kloppen.

Lopend totaal

In rij 19 staat een formule die het lopend totaal bepaalt (in Excel wordt deze analyse-optie met Voorlopig … aangeduid). In cel C19 staat het totaal van de regio Noord, in D19 het totaal van Noord én Oost etc.

In kolom J staat een lopend totaal over alle regio’s. Wil je per regio een lopend totaal dan moet je eerst tussen C en D een nieuwe kolom invoegen, de gegevens van kolom C selecteren en dan het lopend totaal invoegen. Doe dat ook voor de andere kolommen; zie het tabblad Totalen:

NB plaats je de gegevens in een Excel-tabel dan zien de ingevoegde formules er heel anders uit (zie het tabblad Totalen2 van het Voorbeeldbestand). Bij het toevoegen van nieuwe gegevens hoeft dan niets (of veel minder) aan de formules gewijzigd te worden.

Tabellen

Met de eerste keuze binnen de analyse-optie Tabellen wordt een gewoon bereik van cellen omgezet naar een Excel-tabel. Maar ik gebruik die (bijna) nooit; die optie kennen we al via Invoegen of door Ctrl-L te tikken.

In het tabblad Tabellen van het Voorbeeldbestand is het gebruikte bronbestand omgezet naar een database-vorm; dit om de mogelijkheden van een draaitabel makkelijker te benutten.

Per combinatie van kenmerken (Regio, Maand en Product) wordt het Aantal vastgelegd.

  1. selecteer één van de cellen in de Excel-tabel en druk op Ctrl-Q
  2. kies de analyse-optie Tabellen
  3. op basis van deze brongegevens stelt Excel 2 draaitabellen voor: Som van Aantal per regio en per product
  4. klik op één van de 2 mogelijkheden (of de 3e om zelf een draaitabel te maken)

NB de 2 draaitabellen zijn gebaseerd op dezelfde brongegevens maar laten ieder een ander totaal zien!
Dit is een inconsistentie binnen Excel. Maak je zelf verschillende draaitabellen op basis van dezelfde brongegevens dan worden alle draaitabellen tegelijkertijd vernieuwd. Zijn de draaitabellen via Snelle analyse aangemaakt dan moet iedere draaitabel afzonderlijk vernieuwd worden. Maar dan worden de brongegevens tussentijds aangepast door de Aselect-formule.

Op het tabblad Tabellen staat ook een draai-grafiek; deze is gemaakt op basis van de gegevens in de eerste kolommen door in de analyse-optie een grafiek te kiezen met het -teken.

Sparklines

Niets nieuws meer onder zon: selecteer alle cellen met getallen (dus niet de maanden en de kopregels), Ctrl-Q, kies de optie Sparklines en klik op één van de drie mogelijkheden (zie het tabblad Sparklines in het Voorbeeldbestand).

Het eerste type sparkline laat het globale verloop als lijngrafiek zien, de tweede globaal het verloop als kolomgrafiek en de derde (Winst/verlies) laat alleen maar zien of het resultaat positief of negatief is.

NB1 het uiterlijk van de sparklines kan makkelijk aangepast worden: klik op een cel die een sparkline bevat en kies bij Hulpmiddelen voor sparklines de optie Ontwerpen.

NB2 of je nu met een bereik van cellen werkt of met een Excel-tabel (zie het tabblad Sparklines2 van het Voorbeeldbestand), bij het uitbreiden van gegevens zul je de sparklines ook moeten aanpassen (of opnieuw maken). Het aanpassen gaat simpel door de vulgreep rechtsonder in de onderste cel met een sparkline naar beneden te trekken.

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

Aanmeldformulier

Data verrijken

Excel, , , , ,
Voorbeeldbestand: DataVerrijkenDownload

Iedere dag krijgen of vinden we wel gegevens, waarvan je denkt: interessant, maar als ik die met een andere set combineer (verrijken van data) kan ik er nog veel meer informatie uit halen.

In dit artikel geef ik uitleg over verschillende methoden zoals bestaande tabellen uitbreiden met extra kolommen, gebruik maken van het Excel-gegevensmodel en het gebruik van Power Query.

Brongegevens

We gaan als voorbeeld een Excel-tabel gebruiken met daarin het aantal verkochte producten, uitgesplitst naar datum, klant- en productcode.
In het tabblad Verkoop van het Voorbeeldbestand vindt u 1000 records in de Excel-tabel tblVerkoop.

Welke klanten komen hierin voor? Welke producten zijn verkocht? Over welke periode gaat dit bestand?
Meer vragen dan antwoorden.

Verkoopoverzicht

Om snel inzicht te krijgen in bovenstaande vragen maken we op basis van het bestand een draaitabel:

  1. Plaats de cursor ergens in de tabel tblVerkoop.
  2. Kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK.
  3. Sleep de Datum naar de Rijen, Aantal naar het Waarden-gebied en Prod naar Kolommen.

We zien dus direct (tabblad OvzVerkoop) dat het 6 producten betreft en dat het over de periode 2018-2020 gaat.

Klik je op de + voor een jaar, dan zie je welke kwartalen en maanden er in het overzicht voorkomen.

NB1 vanaf versie 2016 zal Excel datums, die in de kolommen of rijen worden geplaatst, direct groeperen naar Jaren, Kwartalen en Maanden.
Gebeurt dit niet automatisch, klik dan met de rechtermuisknop op een datum en kies Groeperen; selecteer daar dan de gewenste opties.

NB2 wil je geen groepering naar kwartaal? Klik rechts op een van de tijdaanduidingen, kies Groeperen en deselecteer de optie Kwartalen.

Maar hoe zit het met de klanten?

  1. Verwijder Prod uit de Kolommen.
  2. Verplaats Jaren van Rijen naar Kolommen.
  3. Sleep Klanten naar Rijen.

Zie het tabblad OvzVerkoop.

NB Excel plaatst de klanten bij het aanmaken van de draaitabel netjes in alfabetische volgorde, maar door de methode van codering komt K10 direct na K1. Gelukkig is dit snel verholpen: selecteer de cel met K10, ‘pak’ met de muis de rand en sleep de code naar beneden.

‘Toevallig’ weet ik dat de klanten over 2 regio’s zijn verdeeld: K2, K5, K7 en K8 horen bij de regio Zuid, de anderen bij Noord.
Die moeten we nu dus nog groeperen (zie Groeperen in een draaitabel): selecteer de draaitabel-rij met K2 (het muis-symbool is dan een pijltje naar rechts), houd Ctrl ingedrukt en selecteer op dezelfde manier K5, K7 en K8. Klik rechts en kies Groeperen. Nog even de namen van de groepen aanpassen (gewoon overschrijven) en de groep Noord naar boven verslepen: klaar!

Op deze manier hebben we handmatig de eerste verrijkingen aangebracht.

Verrijking door toevoegen kolommen

Maar we weten nog meer over onze klanten en de producten:

Tabblad Klant
Tabblad Product

Hiermee kunnen we onze dataset uitbreiden. Dezelfde gegevens van het tabblad Verkoop zijn allereerst gekopieerd naar VerkBerek van het Voorbeeldbestand.

Aan de Excel-tabel (met de naam tblVerkBerek) zijn nieuwe kolommen toegevoegd:

  1. Regio, in cel F3 staat de formule:
    =INDEX(tblKlant[Regio];VERGELIJKEN([@Klant];tblKlant[Klant];0))
    NB1 hier is een alternatief voor verticaal zoeken gebruikt. De avz-truc komt goed van pas! Zie zoeken-index-en-vergelijken.
    NB2 alle berekeningen worden binnen een Excel-tabel uitgevoerd, waardoor we gestructureerde verwijzingen kunnen gebruiken. Zie voor de voordelen van tabellen: kunst-en-excel.
    NB3 na het invoeren van de formule in F3 wordt deze automatisch naar beneden doorgevoerd, zodat alle cellen in die kolom een consistente formule hebben.
  2. In cel G3 halen we op een vergelijkbare manier de naam van het product op:
    =INDEX(tblProd[ProdNaam];VERGELIJKEN([@Prod];tblProd[Product];0))
  3. De totale kosten, die met een verkoop zijn gemoeid worden in cel H3 bepaald:
    =[@Aantal]* INDEX(tblProd[KostPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  4. In cel I3 bepalen we de omzet:
    =[@Aantal]* INDEX(tblProd[VerkPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  5. Daarmee kunnen we in J3 de bruto-winst berekenen:
    =[@Omzet]-[@Kosten]
  6. Maar (sommige) klanten krijgen korting; in cel K3:
    =INDEX(tblKlant[Korting];VERGELIJKEN([@Klant];tblKlant[Klant];0))
  7. Waarmee we ook een netto-winst kunnen bepalen in cel L3:
    =[@Omzet]*(1-[@Korting])-[@Kosten]

Op basis van deze nieuwe dataset kunnen we diverse analyses uitvoeren, uiteraard met behulp van een draaitabel. Bijvoorbeeld (zie tabblad OvzVerkBerek van het Voorbeeldbestand):

Verrijken m.b.v. gegevensmodel

Een nadeel van de vorige methode is, dat het bestand meteen een stuk groter wordt wanneer er kolommen aan een tabel worden toegevoegd. Dat valt nog wel mee als het over 1.000 records gaat, maar als het er een miljoen zijn en als het meer kolommen betreft….

De koppelingen, die we hiervoor met Index/Vergelijken hebben gemaakt, kunnen sinds versie 2013 ook intern in Excel met behulp van een gegevensmodel worden vastgelegd zonder dat dit extra ruimte in beslag neemt. Wel moeten de basisgegevens in Excel-tabellen vastliggen. In het Voorbeeldbestand bevatten de tabbladen Verkoop, Klant en Product onze basisgegevens.

Nu gaan we het gegevensmodel vullen door de relaties tussen deze tabellen vast te leggen:

  1. Kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Relaties (de button met 3 tabellen en lijntjes daar tussen).

  1. U legt een relatie vast door op de knop Nieuw te klikken:

    De eerste tabel moet de gegevens bevatten, die uitgebreid gaan worden; in dit geval dus tblVerkoop.
    We gaan via de klant-code gegevens opzoeken, dus geven als kolom Klant op.
    Daaronder moeten we aangeven in welke tabel we gaan zoeken, in het huidige voorbeeld tblKlant.
    Ook in deze tabel bevat de kolom Klant de overeenkomende gegevens.
    Klik op OK.
    NB1 in de laatste stap staat het woord primair; hiermee wordt bedoeld, dat de kolom unieke gegevens moet bevatten. Anders kan er geen relatie aangemaakt worden.
    NB2 heb je meer dan 1 kolom nodig om een relatie te leggen (bijvoorbeeld Naam en Afd) dan zul je een kunstgreep moeten uithalen: in beide tabellen moet je de 2 kolommen aan elkaar koppelen in een nieuwe kolom, bijvoorbeeld Naam&Afd.
  2. Maak op dezelfde manier ook een relatie tussen tblVerkoop en tblProd; de kolomnamen die daarbij gebruikt worden zijn respectievelijk Prod en Product.
    De namen hoeven dus niet hetzelfde te zijn.
  3. Sluit het scherm Relaties beheren.

Nu het gegevensmodel is gevuld kunnen we op basis daarvan overzichten maken met behulp van draaitabellen (in het tabblad OvzVerkModel1 van het Voorbeeldbestand staat een voorbeeld):

  1. Plaats de cursor in een lege cel. Hier zal het overzicht komen, dus meestal doe je dit op een nieuw tabblad.
  1. Start via Toevoegen/Draaitabel een nieuwe draaitabel
  2. Hiernaast is te zien, dat Excel nu automatisch weet, dat we ons gegevensmodel als bron willen gebruiken. Klik OK.

  1. Het overzicht van draaitabel-velden ziet er iets anders uit dan bij een ‘normale’ draaitabel.
    Alle tabellen uit het gegevensmodel zijn beschikbaar met alle bijbehorende kolommen.
    Hiernaast zijn alle 4 gebieden gevuld met gegevens uit 3 verschillende tabellen.
    NB1 bevat de werkmap nog meer Excel-tabellen (net als het Voorbeeldbestand), dan zijn die ook in dit overzicht zichtbaar.
    NB2 heb je velden naar de gebieden versleept en klik je bovenaan op Actief, dan zie je alleen de tabellen die gebruikt zijn in dit overzicht.

Helaas: op deze manier kun je alleen velden uit de basis-tabel (in het voorbeeld tblVerkoop) in het waarden-gebied plaatsen. Sleep je bijvoorbeeld Korting in het waarden-gebied dan krijg je de volgende melding:

Dit scherm krijg je ook als je niet alle tabellen via relaties met elkaar hebt verbonden, maar wel kolommen uit die tabellen gebruikt.

Wat nu wel kan: in de draaitabel kunnen unieke waarden geteld worden. Laten we even via een voorbeeld kijken (tabblad OvzVerkModel2 van het Voorbeeldbestand):

  1. Maak een draaitabel aan zoals hiervoor beschreven.
  2. Plaats Regio en Klant in de Rijen.
  3. Sleep Aantal naar het Waarden-gebied.
  4. Sleep daarna 2x de Datum naar het Waarden-gebied.
  5. Klik op het vinkje achter Datum2 en kies Waardeveldinstellingen.
  6. Helemaal onderaan krijg je nu de mogelijkheid om te kiezen voor Uniek aantal.

Telling van Datum geeft het aantal datums weer per klant, dus eigenlijk het aantal keer dat een klant voorkomt (als de datum is gevuld); het totaal is dan ook precies 1.000.

Unieke telling van Datum2 laat zien hoe vaak een unieke datum voorkomt bij een klant.

Verrijken m.b.v. Power Query

En dan nu een oplossing die als een soort combinatie van de vorige 2 gezien kan worden: Power Query. Binnen dit Excel-onderdeel leggen we de verbanden tussen de tabellen vast én we maken daar allerlei berekeningen die we nodig hebben:

  1. Selecteer een cel in de tabel tblKlant.
  2. Kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
  1. In principe zijn alle kolommen in orde. Aangezien Korting een percentage is, kunnen we de lay-out nog aanpassen:
    * klik op de 1.2 naast Korting
    * wijzig de instelling naar Percentage.
  1. Kies dan het vinkje naast Sluiten en laden.
  2. Klik op Sluiten en laden naar.
  3. Zorg dat de optie Alleen verbinding maken is geselecteerd en klik op de button Laden.
  4. Voer de vorige stappen ook uit voor de tabel tblProduct. Zorg dat KostPrijs en VerkPrijs de instelling Decimaal getal krijgen.
  1. Als laatste doet u hetzelfde met de tabel tblVerkoop, maar nog NIET sluiten. Wijzig de opmaak van de kolom Datum in alleen Datum.
  2. Voordat we Power Query afsluiten moeten we nog relaties leggen tussen de 3 tabellen.
    Kies het vinkje achter Query’s samenvoegen en kies de optie Samenvoegen als nieuw.

  1. Maak de verbindingen zoals hierboven en klik op OK. Wijzig de naam van de nieuwe query in PQtblVerkoop.
  2. Om de gegevens uit tblKlant als extra kolommen toe te voegen klikt u op het symbool rechts van tblKlant. Stel de opties in zoals hierboven en klik OK.
  3. Doe hetzelfde met tblProduct.
  4. Nu gaan we nog wat extra kolommen toevoegen: klik in de kolom Datum, klik op de menutab Kolom toevoegen en kies binnen de optie Datum voor het Jaar. Doe hetzelfde om de maand, de naam van de maand en het kwartaal toe te voegen.
  5. Kies daarna de optie Aangepaste kolom binnen Kolom toevoegen. De naam wordt Omzet en de formule =[Aantal]*[VerkPrijs].
  6. Voeg op dezelfde manier nog 3 kolommen toe:
    Kosten: =[Aantal]*[KostPrijs]
    Brutowinst: =[Omzet]-[Kosten]
    Nettowinst: =[Aantal]*([VerkPrijs]*(1-[Korting])-[Kostprijs])
  7. Zorg dat de 4 toegevoegde kolommen de instelling Decimaal getal hebben.
  1. Kies nu Sluiten en laden en zorg dat er alleen een verbinding tot stand wordt gebracht.
    LET OP zorg wel dat deze query PQtblVerkoop in de laatste stap wordt toegevoegd aan het gegevensmodel.

Nu staat alles klaar om overzichten te maken. Op het tabblad OvzVerkPQ van het Voorbeeldbestand vindt u een voorbeeld:

De gegevens van de bruto- en nettowinst zijn niet als getallen zichtbaar. Met behulp van Voorwaardelijke opmaak hebben die gegevensbalken gekregen:

  1. Selecteer een cel in een betreffende rij.
  2. Kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak.
  3. Kies dan Gegevensbalken en een kleur(overgang).
  4. Naast de cel staat nu het tekentje van een draaitabel, kies daar de onderste optie.
  5. Via Regels beheren/Regel bewerken de optie Alleen balk weergeven aanvinken.
  6. Pas de rijhoogte naar wens aan.

De Aantallen hebben op een vergelijkbare manier een Voorwaardelijke opmaak gekregen, namelijk Kleurenschalen.

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd? Door te klikken op de button

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd?
Door te klikken op de button Alles vernieuwen in het blok Verbindingen van de menutab Gegevens, worden alle koppelingen, het gegevensmodel en alle draaitabellen ververst.

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

Aanmeldformulier