Categorie archief: Excel

Rang(orde)

Excel,
Voorbeeldbestand: Rang.xlsxDownload

Hoe vaak gebeurt het niet?
Heb je een lijstje met scores, bedragen, rondetijden. Dan wil je natuurlijk ook weten hoe iedere regel zich verhoudt tot de anderen ofwel wat de rangorde van iedere score is.
Excel heeft daar een mooie functie voor, Rang (in de Engelstalige versies Rank).

Dit artikel gaat in op de werking van deze Excel-functie en zal ook laten zien hoe dit probleem getackeld kan worden als je de volgorde bijvoorbeeld per geslacht wilt weten.

Brongegevens

Als voorbeeld nemen we een score-overzicht van diverse personen, verdeeld naar afdeling en geslacht; zie het Voorbeeldbestand, tabblad Data.
We zullen hierna dit overzicht gaan sorteren naar rangorde van de score en dat ook nog op verschillende manieren.
Om het oorspronkelijke overzicht weer te kunnen herstellen hebben de namen ook een volgnummer meegekregen; deze kan in een sortering gebruikt worden.

De gegevens zijn in een Excel-tabel geplaatst met de naam tblNamen.

NB voor demonstratie-doeleinden (zie hierna) wordt de kolom Afd telkens opnieuw gevuld: =KIEZEN(ASELECTTUSSEN(1;4);”Afd1″;”Afd2″;”Afd3″;”Afd4″)

Rangorde 1: sortering

Sortering is natuurlijk de meest gehanteerde methode om een rangorde te bepalen:

  • klik met de rechter muisknop op één van de cellen in de score-kolom
  • kies de optie Sorteren
  • in het vervolgmenu kiest u de optie Oplopend of Aflopend

Uiteraard kunt u ook de knop Sortering uit de menubalk gebruiken.

NB Aangezien de gegevens in een Excel-tabel vastliggen hoeven we niet bang te zijn dat niet alle gegevens worden meegenomen bij de sortering.

Rangorde 2: Rang.Gelijk

De vorige methode heeft als nadeel, dat we niet direct zien wat de exacte plaats van een persoon is in de rangorde (behalve misschien de eerste 5).

Daarom is de tabel uitgebreid met een aparte kolom met de naam Rang.Gelijk.
De kolom is gevuld met de volgende formule:
=RANG.GELIJK([@Score];[Score];$G$2)

De functie Rang.Gelijk bepaalt aan de hand van de waarde in de Score-kolom in de betreffende regel (vandaar het @-teken in de eerste parameter) de rangorde in de totale Score-kolom (tweede parameter).
Zijn 2 of meer scores Gelijk dan krijgen de betreffende regels allemaal dezelfde rang toegewezen en wel de eerste (dus in het voorbeeld levert de score 62 voor alle betreffende regels de rang 11 op).
De derde parameter geeft aan of de rangorde van hoog naar laag bepaald moet worden of niet. Is deze parameter 0 of ONWAAR (of wordt dit derde argument weggelaten) dan zal de rangorde van hoog naar laag worden berekend; is deze parameter gelijk aan een ander getal of WAAR dan andersom.

In het voorbeeld wordt deze derde parameter gevuld met de waarde in cel G2.

Deze cel is op zijn beurt gekoppeld aan een Selectievak, zodat we de volgorde met het aan- en uitzetten van een vinkje kunnen beïnvloeden.

Hoe voeg je een Selectievak toe?

  • kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  • kies in het vervolgscherm binnen de Formulierbesturingselementen de derde optie (Selectievakje)
  • ’teken’ met de linkermuisknop ingedrukt op de gewenste plaats de omtrek van het selectievak
  • de grootte, plaats en tekst-inhoud kunnen nu nog aangepast worden
  • klik rechts op het nieuwe vakje en kies de optie Besturingselement opmaken
  • nu kunnen de kleuren en lijnen ingesteld worden en ook de koppeling met een cel. In het voorbeeld hebben het selectievakje gekoppeld aan de cel G2.

In het voorbeeld hiernaast is de tabel gesorteerd op de score-kolom.
De 2 regels met score 47 hebben allebei de rang 8 gekregen; score 48 heeft de rang 10.

Iets vergelijkbaars zien we bij de score 62: drie regels krijgen de rang 16; de eerstvolgende heeft rang 19.

NB Excel kent ook een functie Rang (zie de laatste kolom van de tabel in het tabblad Data van het Voorbeeldbestand).
De werking hiervan is exact gelijk aan de functie Rang.Gelijk; Rang is vanwege compatibiliteit met oude versies nog steeds beschikbaar.

Rangorde 3: Rang.Gemiddelde

De werking hiervan is vergelijkbaar met de functie Rang.Gelijk.
Maar in het geval van gelijke scores wordt nu niet de laagste rang toegekend maar het gemiddelde van de posities van die score. Dus hiernaast krijgt de score 83 niet op 2 plaatsen de rang 4 maar het gemiddelde van rang 4 en 5, dus 4½.

Rangorde 4: Draaitabel1

Een rangorde kunnen we ook aanbrengen met behulp van een draaitabel (zie tabblad Draai in het Voorbeeldbestand).

  • maak een draaitabel op basis van de Excel-tabel tblNamen
  • plaats het veld Naam in de Rijlabels
  • Plaats het veld Score in het waardengebied
  • idem voor het veld Rang
  • en nogmaals voor het veld Score
  • wijzig de koppen in de draaitabel door in de betreffende cellen een nieuwe tekst te tikken (eventueel extra spatie aan de tekst toevoegen om deze uniek te maken)
  • klik rechts op een van de waarden in de kolom RangNeer, kies de optie Waarden weergeven als en kies dan de optie Positie van groot naar klein.
  • klik rechts op één van de namen, selecteer de optie Sorteren en dan Meer sorteeropties
  • vul het vervolgscherm in zoals hiernaast weergegeven
  • klik op OK

LET OP Zoals in het voorbeeld te zien is werkt de draaitabel-functionaliteit iets anders dan de functie Rang!
Gelijke scores krijgen ook eenzelfde rangnummer, maar de volgende score krijgt gewoon het volgende rangnummer (zie bijvoorbeeld hierboven bij score=82).

NB als de brongegevens wijzigen of de volgorde van ranking, dan moet de draaitabel vernieuwd worden (rechts klikken en Vernieuwen kiezen).

Rangorde 5: Draaitabel2

Maar….
het mooie van de draaitabel is wel, dat wanneer er nog meer velden in de Rijlabels worden geplaatst, de ranking rekening houdt met deze indeling: de rangnummers worden binnen de categorieën bepaald (hiernaast een aparte ranking voor de mannen en voor de vrouwen).

Bekijk ook het resultaat per afdeling op het tabblad Draai van het Voorbeeldbestand.

Aangezien de afdeling telkens aselect wordt toegekend, zal Vernieuwen iedere keer een ander resultaat opleveren.

Werkdagen

Access, Excel, VBA (Visual Basic for Applications),
Voorbeeldbestand: Werkdagen.zipDownload

Voor rapportages is het nog wel eens handig om te bepalen hoeveel dagen er zitten tussen een begin- en einddatum. In Excel is dat heel eenvoudig te berekenen door deze datums van elkaar af te trekken. In Access gaat dat op dezelfde manier.

Maar als je wilt weten hoeveel werkdagen er tussen zitten, wordt het wat lastiger. In Excel gaat dat nog vrij eenvoudig met behulp van de ingebouwde functie NETTO.WERKDAGEN, maar in Access komt er wat meer werk om de hoek kijken.

Hieronder een uitleg over de werkwijze in Excel en een voorbeeld hoe de berekening in Access kan worden uitgevoerd.

Dagen verschil in Excel

In het Voorbeeldbestand ziet u op het tabblad Kalender van de Excelsheet Werkdagen.xlsx een Excel-tabel met alle dagen van 2019. In de tweede kolom van die tabel (met de naam tblKalender) wordt het verschil bepaald tussen die dagen en een vaste begindatum (Refdatum). Simpel door ze van elkaar af te trekken via de formule =[@Datum]-RefDatum

NB alle datums in de voorbeelden hebben de volgende celopmaak meegekregen: ddd d mmmm jjjj (zie ook het artikel ginfo.nl/datums-data-dates).


Dus de cel uit de kolom Datum in dezelfde rij (vandaar de @) minus de referentiedatum (in dit geval de inhoud van cel C2).
Een negatief resultaat wil zeggen, dat de datum vóór de referentiedatum ligt.

NB de kleuren in de datum-kolom worden automatisch gegenereerd door middel van Voorwaardelijke opmaak.

Moet de dag zelf ook meetellen dan moet de berekening iets aangepast worden: =[@Datum]-RefDatum+ALS([@Datum]>=RefDatum;1;-1)
Als de dag op of na de referentiedatum ligt, dan moet het resultaat 1 groter worden, anders 1 meer negatief (zie kolom Verschil2).

Zonder weekenden in Excel

Maar als we willen weten hoeveel werkdagen er tussen 2 datums zitten, dan volstaat zo’n simpele berekening niet; zaterdagen en zondagen mogen niet meetellen.
Gelukkig heeft Excel daar een standaard-functie voor: NETTO.WERKDAGEN.

Geef je maar 2 parameters op (de begin- en einddatum), dan bepaalt deze functie het verschil in dagen, exclusief de weekenden.

NB ook hier telt de dag zelf mee; zie in het voorbeeld hierboven het resultaat bij 9 januari.

Echte werkdagen in Excel

Met echte werkdagen bedoelen we dat feestdagen/vrije dagen niet mee mogen tellen.

In het tabblad Feestdagen van het Excel-Voorbeeldbestand is daartoe een overzicht opgenomen met alle mogelijke feestdagen.
Waarschijnlijk zijn dit voor u niet allemaal vrije dagen (helaas?); in de praktijk zult u het overzicht dus moeten aanpassen.
De gegevens zijn opgenomen in de Excel-tabel met de naam tblFeestdagen.

De formule voor de werkdagen is in kolom F uitgebreid met een derde parameter, de verwijzing naar de feestdagen die, net als de weekenden, niet meegerekend moeten worden (alle dagen uit de kolom Datum van de tabel tblFeestdagen).

Dagen verschil in Access

In het Voorbeeldbestand zit ook een Access-database (Werkdagen.accdb). Deze heeft de volgende onderdelen: 2 tabellen (met dezelfde feestdagen en kalender als in de Excel-sheet), een query (VerschilBepalen) waarmee de verschillen tussen begin- en einddatums bepaald worden en een Module1, die een eigen functie bevat waarmee we het verschil in werkdagen kunnen bepalen.

Het eerste stuk van de query ziet er als volgt uit:

Voor alle datums uit de Access-tabel tblKalender wordt het verschil bepaald met een referentiedatum.
Deze referentie ligt nergens vast. Access kent het veld tussen rechte haken met de naam Wat is de referentiedatum? niet.

Bij de weergave van de query zal dan ook een pop-up komen, waarin gevraagd wordt om de inhoud van de parameter op te geven.

NB met de functie CDate zorgen we er voor, dat Access ‘weet’ dat de parameter een datum bevat.

Zonder weekenden in Access

Zoals gezegd kent Access geen functie om werkdagen te bepalen; die moeten we dus zelf programmeren.

De eerste regel definieert de functie met de naam Werkdagen. De functie kent 3 parameters: de eerste is de begindatum en de tweede de einddatum. De derde parameter is optioneel en kan alleen de waarde WAAR of ONWAAR bevatten (Boolean); moet er wel of niet met feestdagen rekening worden gehouden.
Na wat commentaarregels (groene tekst) krijgt de routine de opdracht om bij een mogelijk optredende fout naar het gedeelte te gaan dat begint met Err_Werkdagen.
Met het commando Dim worden diverse variabelen gedefinieerd, die we hierna nodig hebben.
De variabele Start wordt gevuld met het datum-gedeelte van de parameter StartDatum; iets vergelijkbaars gebeurt met de variabele Eind.
In de variabele StartKleinerEind wordt vastgelegd of de variabele Start kleiner of gelijk is aan Eind (WAAR of ONWAAR).
Wanneer de parameter Feestdagen de waarde WAAR bevat, dan worden alle datums uit de tabel tblFeestdagen in de variabele rst gestopt.

Nu kunnen we gaan rekenen:

De variabele intCount wordt op 0 gezet.
In het geval Start kleiner of gelijk is aan Eind dan wordt de eerste Do While-loop doorlopen; als dat niet zo is, de tweede.
Als binnen de loop de variabele Start geen zondag of zaterdag is dan wordt de variabele intCount met 1 opgehoogd.
Moet er rekening gehouden worden met feestdagen dan wordt er via de opdracht FirstFind in de variabele rst gekeken of Start daarin voorkomt.

LET OP Access verwacht #’s rond de datum en de FirstFind-opdracht werkt alleen goed als de datum in Amerikaans formaat wordt meegegeven.

Als de datum Start voor komt in rst (Not rst.NoMatch) dan moet de teller weer verlaagd worden.
De variabele Start wordt opgehoogd en via Loop wordt de berekening opnieuw uitgevoerd met de nieuwe startdatum (als de nieuwe Start tenminste nog kleiner of gelijk is aan Eind)

NB de tweede loop werkt ongeveer hetzelfde maar nu vanuit Start terugtellend naar Eind.

Als de berekening klaar is dan wordt de inhoud van de variabele intCount als resultaat aan de functie teruggeven. Daarbij wordt wel gekeken of het resultaat positief of negatief moet zijn.
Dan is onze functie klaar: Exit Function.

Onderaan staat nog wat de functie moet doen als er een fout optreedt.

Met behulp van deze functie kunnen we de query VerschilBepalen uitbreiden:

NB Omdat de onbekende parameter bij Verschil en ZonderWeekend exact hetzelfde is, zal Access slechts 1 keer vragen om hiervoor een referentiedatum in te voeren.

Het resultaat van de query is nu precies gelijk aan het resultaat van de Excel-functie NETTO.WERKDAGEN, waarbij de derde parameter niet is opgegeven.

Echte werkdagen in Access

Het zal u niet verbazen, dat de uitbreiding van de query met het veld EchteWerkdagen …

… een vergelijkbaar resultaat oplevert als NETTO.WERKDAGEN, rekening houdend met allerlei feestdagen.

Trechtergrafieken

Excel,
Voorbeeldbestand: TrechterDownload

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

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

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

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

Basis-gegevens

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

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

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

Draaitabel

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

Analyse

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

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

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

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

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

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

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

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

Laten we de grafieken maken:

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

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

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

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

Ramingen

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

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

Pasen

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

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

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

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

Bulk-berekeningen

Excel, VBA (Visual Basic for Applications), ,
Voorbeeldbestand: BulkBerekDownload

LET OP: na het downloaden de extensie wijzigen in xlsm

Vroeger…. toen was het allemaal anders (en beter?).
Je had toen speciale bureaus, die niets anders deden dan (standaard)berekeningen uitvoeren … “met de hand”.

Tegenwoordig wordt Excel vaak ingezet voor berekeningen. Maar in de praktijk zie ik daar vaak nog veel hand-arbeid aan te pas komen. Daar is op zich niets mis mee, maar het is wel veel foutgevoeliger dan wanneer dit soort processen worden geautomatiseerd.
Daarom deze keer aan de hand van een hypotheek-voorbeeld uitleg hoe met (een beetje) VBA bulk-processen binnen Excel kunnen worden uitgevoerd.

Hypotheek-berekening

De basis van dit artikel is een hypotheekberekening (zie het tabblad BerekHyp van het Voorbeeldbestand). Aan de hand van enkele input-gegevens wordt een overzicht van rente en aflossing gegenereerd; voor de overzichtelijkheid op jaar-basis.

Alle input-cellen (C2:C7) hebben een naam gekregen gelijk aan de tekst in de kolom links er van (via Formules/Namen/Maken obv selectie en dan de optie Linkerkolom).
Via Gegevens/Gegevensvalidatie zijn de invoer-mogelijkheden van enkele cellen ingeperkt.
Het schema van rente en aflossing is in een Excel-tabel weergegeven (met de naam tblBerek).

Cel B11 bevat een harde waarde, gelijk aan 1, cel C11 heeft als formule =Bedrag (het gewenste hypotheekbedrag dus).
In cel D11 wordt de verschuldigde rente voor dat jaar bepaald (voor een annuïteiten- of lineaire hypotheek is dat hetzelfde):
=[@BeginBedr]*Rente, dus het BeginBedr uit dezelfde regel vermenigvuldigd met het verschuldigde rentepercentage (cel C5).

De berekening van de aflossing het eerste jaar (cel E11) is iets ingewikkelder en afhankelijk of het een lineaire of annuïteiten-hypotheek betreft:
=ALS(Soort=”Lin”;
Bedrag/Looptijd;
-BET(Rente;Looptijd;Bedrag) – [@Rente])

Dus: als het een een lineaire hypotheek is dan is de aflossing gelijk aan het Bedrag gedeeld door de beoogde totale Looptijd. Anders (dus bij een annuïteit) is het gelijk aan het resultaat van de functie BET(Rente;Looptijd;Bedrag) minus de verschuldigde Rente van dat jaar.

NB1 de functie BET berekent aan de hand van de parameters rentepercentage, aantal termijnen en beginbedrag het verschuldigde termijnbedrag

NB2 als het beginbedrag in de functie BET positief is, dan is het resultaat van de functie negatief; vandaar het min-teken voor BET.

NB3 moeten de berekeningen exacter dan zul je het schema op maandbasis moeten doorrekenen; de Rente door 12 delen en de Looptijd en de rentevastperiode (RVP) met 12 vermenigvuldigen.

Het volgende Jaar-nummer (cel B12) is gelijk aan het vorige Jaar plus 1; het BeginBedr in cel C12 is gelijk aan het resultaat van cel F11. De formules in de overige cellen van rij 12 zijn gelijk aan die in de vorige regel. De formules uit rij 12 kunnen daarna allemaal naar beneden gekopieerd worden.

NB het schema mag alleen maar gevuld worden voor die jaren, die binnen de rentevastperiode (RVP) vallen. Dat is met behulp van de ALS-functie makkelijk op te lossen (zie Voorbeeldbestand).

In hetzelfde tabblad BerekHyp staat ook een samenvatting van de resultaten: de totale bedragen aan rente en aflossing en de restant-schuld op het einde van de RVP.

De eerste 2 berekeningen zijn rechttoe rechtaan: neem de som van de betreffende kolom in de tabel tblBerek. De bepaling van het eindbedrag gaat via de formule =MIN(tblBerek[EindBedr]), ofwel bepaal het minimum van de kolom EindBedr in de tabel tblBerek.

Bulk-invoer

Moet je nu 10 verschillende berekeningen uitvoeren, dan zullen per berekening de betreffende gegevens moeten worden gewijzigd, Excel berekent de resultaten en deze moeten overgenomen worden of geprint.
Maar wat te doen als er 100 berekeningen nodig zijn of misschien 2.000? Het overnemen van invoer-gegevens is behoorlijk foutgevoelig; de meest voorkomende fouten zijn:
* een 0 te veel of te weinig bij het bedrag
* een decimaal fout in het rentepercentage
* vergeten Ann te veranderen in Lin of andersom

Dus het is zaak om zo min mogelijk gegevens handmatig in te voeren.
Zorg bijvoorbeeld, dat gegevens door een ander systeem in Excel-formaat worden aangeleverd óf laat (als je zelf een overzicht in Excel hebt gevuld) iemand anders het overzicht aan de hand van brongegevens controleren.
Een voorbeeldbestand met 2.000 regels (met de naam tblInput) is opgenomen in het tabblad Input van het Voorbeeldbestand.

Bulk-berekeningen (handmatig)

Als de invoer-gegevens gecontroleerd zijn kunnen die verwerkt worden tot resultaat-gegevens.

In het tabblad Bereken van het Voorbeeldbestand staat een vergelijkbaar schema als in het tabblad BerekHyp.
Het enige verschil is, dat de invoergegevens met behulp van VERT.ZOEKEN-formules worden opgehaald uit de tabel tblInput op basis van het corresponderende Nr.

NB voor het verticaal zoeken hebben we een Hulp-kolom gebruikt om aan te geven uit welke kolom van tblInput het betreffende veld gelezen moet worden.

Op deze manier kunnen redelijk vlot alle berekening doorlopen worden. Maar wat doe je met de resultaten. Eén mogelijkheid is om deze te verwerken in een apart output-tabblad (zie Output1 van het Voorbeeldbestand):

  • Vul in cel D2 van het tabblad Bereken een nummer in
  • Kopieer de cellen D2:D7 van het tabblad Bereken
  • Kies Plakken Speciaal/Waarden en Transponeren in een nieuwe regel in het tabblad Output1
  • Kopieer de cellen H2:H4
  • Plak die op een vergelijkbare manier achter de vorige gegevens
  • herhaal bovenstaande voor alle nummers uit Input

Bulk-berekeningen (met VBA)

Maar we kunnen het hele proces natuurlijk nog verder automatiseren; daar komt dan wat VBA (programmeerwerk) om de hoek kijken.
In het tabblad Bereken staan enkele buttons; de eerste met de titel Vullen 1 start de subroutine OutputVullen1 (rechtsklikken op de button en dan kiezen Macro toewijzen):

Een korte toelichting:

  • Allereerst worden met behulp van het commando Dim de variabelen gedeclareerd, die we binnen de routine gaan gebruiken.
  • Na de declaraties wordt de variabele MaxHyp gevuld met de waarde van de Range met de naam AantHyp (cel I2 in het tabblad Input)
  • Dan een vreemd blok (met de #-tekens): dit is een stuk met zogenaamde Conditional Compilation. De regel, die begint met If MaxHyp> 500, wordt alleen uitgevoerd op een MAC-computer. Omdat een MAC veel langzamer is dan een Windows-machine, beperken we het aantal uit te voeren berekeningen tot maximaal 500.
  • De regel na het #-blok plaatst een pop-up op het scherm, die aangeeft hoeveel berekeningen er uitgevoerd zullen gaan worden. Wordt binnen deze pop-up op Cancel (of Annuleren) gedrukt dan wordt de subroutine afgebroken.
  • Om straks te kunnen bepalen hoeveel tijd de routine heeft gekost, wordt de starttijd vastgelegd in de variabele StartTijd (beter gezegd de datum en tijd worden bewaard).
  • Dan wordt een andere subroutine (OutputLeegMaken1) uitgevoerd (zie hieronder).
  • Het belangrijkste gedeelte van de subroutine wordt gevormd door een zogenaamde For-Next-loop. Het stukje programma tussen For en Next wordt een bepaald aantal keren uitgevoerd, afhankelijk van de inhoud van (in dit geval) MaxHyp. De teller i houdt bij welke hypotheek-berekening wordt uitgevoerd.
  • Binnen de loop wordt allereerst de waarde van de teller i in de cel met de naam Nr geplaatst (zoals we hiervoor handmatig deden).
  • Excel herberekent op dat moment direct alle relevante cellen.
  • Het volgende gedeelte van het programma plaatst dan alle relevante waardes in de resultaat-tabel tblOutput1. Dit wordt gedaan door telkens, geredeneerd vanuit de tabelkop (Header) met de naam Nr, een aantal regels i naar beneden en 0, 1 et cetera kolommen naar rechts te gaan.
  • De overige regels hebben geen functionele bijdrage.

In bovenstaand programma wordt de routine OutputLeegMaken1 aangeroepen:

Via de Range-opdracht wordt de uitvoer-tabel leeg gemaakt. Als deze toevalligerwijs al leeg zou zijn, zou het programma stoppen met een foutmelding. De regels er omheen zorgen er voor, dat bij een eventuele fout het programma gewoon doorgaat.

Op mijn oude laptop heeft Excel ruim een minuut nodig om de berekeningen uit te voeren en de resultaten weg te schrijven.
Op Windows-computers is goed te zien, dat Excel aan het werk is; cellen veranderen continu van inhoud.

In de praktijk blijkt dat het bijwerken van het scherm het merendeel van de benodigde tijd in beslag neemt.
In de routine OutputVullen1b is daarom een regel toegevoegd: Application.ScreenUpdating = False

Via de button Vullen 1b start u deze routine; nu blijkt er nog maar 13 seconden nodig te zijn voor de verwerking van de 2.000 hypotheken!

LET OP vergeet niet in de VBA-routine de schermverversing weer aan te zetten. Onderaan komt dan Application.ScreenUpdating = True

Bulk-berekeningen (met VBA) vervolg

Maar het kan nog sneller. Wanneer we de For-Next-Loop vervangen door:

dan duurt het totale proces nog maar een paar seconden (klik op de button Vullen 2). De bijbehorende VBA-routine heeft de naam OutputVullen2.

Om dit mogelijk te maken is in het tabblad Output2 van het Voorbeeldbestand een aantal extra cellen toegevoegd, die samen de naam ResultRng hebben gekregen. De inhoud van deze cellen wordt in één keer in de Range vanaf (i,0) tot (i,8) geplaatst.

De cellen van ResultRng bevatten twee matrix-formules TRANSPONEREN, waarmee de benodigde gegevens uit het tabblad Bereken worden opgehaald (zie het artikel Transponeren).
Matrix-formules worden ingevoerd door in plaats van af te sluiten met Enter, de toetscombinatie Control-Shift-Enter te gebruiken (de CSE-methode). Excel plaatst dan automatisch accolades rond de formule.

Scrollen in dashboard

Excel, VBA (Visual Basic for Applications), , , , ,
Voorbeeldbestand: DatumVerschil.xlsxDownload

LET OP: na het downloaden de extensie wijzigen in xlsb

Excel wordt steeds vaker gebruikt om het management via dashboards van actuele informatie te voorzien.
Maar ook in dit soort rapportages is de ruimte beperkt. Daarnaast is niet iedere manager geïnteresseerd in dezelfde informatie.

Dit soort problemen is handig op te lossen door gebruikers in de digitale rapportages door de overzichten te laten scrollen.

In dit artikel komen enkele methoden aan bod hoe dit te realiseren. En als we toch bezig zijn: met een beetje VBA kunnen we de overzichten ook makkelijk sorteren.

Basis-materiaal

We hebben een overzicht van afdelingen (het tabblad Data in het Voorbeeldbestand bevat 30 regels) met per afdeling het toegekende kosten-budget (altijd 100.000), de werkelijk gemaakte kosten, het aantal uitgebrachte offertes en het gerealiseerde aantal contracten. Daarnaast bevat het overzicht nog enkele KPI’s: kosten/budget, kosten/offertes en kosten/contracten.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAfd. In cel L2 (met de naam AantAfd) wordt het aantal afdelingen in de tabel geteld:
=AANTALARG(tblAfd[Afdeling])
Dit aantal kunnen we later goed gebruiken om bepaalde zaken te automatiseren.

NB de nummering van de afdelingen is zodanig dat er altijd 2 cijfers gebruikt worden; dit om een juiste sortering te krijgen. Anders krijg je Afd1, Afd10, Afd11, … , Afd2, Afd20 etc.

Overzicht 1

Wanneer nou blijkt, dat we in een bepaalde rapportage slechts ruimte voor 10 regels hebben; welke afdelingen (van de 30) moeten we dan laten zien? Dat hangt natuurlijk af van de wensen van de ontvangers en die kunnen wel eens tegenstrijdig zijn.
Daarom gaan we nu het overzicht met 10 regels zo aanpassen, dat hierbij gekozen kan worden welke regels zichtbaar zijn.

In het tabblad SelIndex van het Voorbeeldbestand geven we in cel C2 (met de naam Start) aan, welke regel van de 30 als bovenste moet worden weergegeven; de volgende 9 worden ook getoond.

In cel F3 staat de formule:
=INDEX(tblAfd[Afdeling];RIJ()-RIJ(AfdKop)+Start-1)
Ofwel: zoek met de functie INDEX in de kolom Afdeling van de tabel tblAfd die rij op die overeenkomt met de RIJ() van (in dit geval) cel F3, minus het rij-nummer van de kop van het overzicht plus de waarde van de cel Start.

NB1 cel F2 heeft de naam AfdKop gekregen.

NB2 voor het bepalen van de juiste regel hadden we natuurlijk ook een hulpkolom kunnen gebruiken met daarin de waardes 1 t/m 10. De gebruikte berekening maakt het mogelijk om de hulpkolom weg te laten en is flexibel genoeg om het overzicht eventueel later nog te verplaatsen. Ook kunnen we deze formule zonder verdere aanpassingen naar beneden kopiëren.

NB3 de formule kan ook naar rechts gekopieerd worden; Excel wijzigt de kolom-verwijzing Afdeling automatisch naar Budget etc.

NB4 om de juiste regel te selecteren moeten we de berekening nog corrigeren met -1.

Wanneer nu de waarde in de cel Start wordt gewijzigd zal het overzicht zich automatisch aanpassen.

Dat aanpassen kan natuurlijk nog veel mooier en makkelijker met behulp van een schuifbalk:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. in het vervolgmenu kiest u binnen het blok Formulierbesturingselementen de optie Schuifbalk
  3. de cursor wordt dan een kruisje; teken, met de linkerknop ingedrukt, de gewenste vorm van de schuifbalk op de gewenste plaats (dit kan allemaal later nog aangepast worden).
  4. klik met de rechtermuisknop op de nieuwe schuifbalk en kies Besturingselement opmaken
  5. zorg dat een koppeling met cel C2 wordt gemaakt (u kunt hier ook de naam Start gebruiken), de minimumwaarde wordt 1 en het maximum 21.

LET OP kies NIET een ActiveX-besturingselement; deze leveren in de praktijk nogal eens crashes van Excel op.

NB als je de schuifbalk wilt verplaatsen of de grootte corrigeren, klik dan eerst rechts op de schuifbalk.

Overzicht 2

Een andere methode om zo’n overzicht met de juiste gegevens te vullen is door gebruik te maken van de functie VERSCHUIVING.

In het tabblad SelVersch1 van het Voorbeeldbestand ziet u in cel F3 de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];
RIJ()-RIJ(AfdKop)+Start-1;
KOLOM()-KOLOM(AfdKop))
ofwel: haal de waarde op uit de cel, die gevonden wordt door vanuit de Afdelings-koptekst van de tabel tblAfd een aantal rijen naar beneden te gaan en een aantal kolommen naar rechts.

NB1 de constructie na het eerste (-haakje hoeft u niet zelf in te tikken; klik gewoon op de betreffende cel en Excel vult de formule vanzelf aan.

NB2 de ingegeven minimum- en maximumwaardes voor de schuifbalk zorgen er voor dat het overzicht geen blanco regels zal bevatten. Helaas is dat niet het geval als in de cel Start een te grote of te kleine waarde wordt ingevoerd.

Overzicht 3

De functie VERSCHUIVING kent nog meer parameters/argumenten. Daar hebben we in het overzicht op het tabblad SelVersch2 van het Voorbeeldbestand gebruik van gemaakt. Cel F3 bevat de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];Start;0;10;8)
Ofwel: selecteer een bereik van cellen, die, gerekend vanaf de koptekst Afdeling, een aantal rijen gelijk aan Start lager begint. Het begin is 0 kolommen verschoven. Het resulterende bereik moet 10 regels hoog en 8 kolommen breed zijn.

LET OP deze formule levert een blok van 80 cellen op. Daarom moet de formule op een speciale manier worden ingevoerd:

  • selecteer eerst met de muis alle cellen waar het overzicht moet komen (in het voorbeeld de cellen F3 tot en met M12)
  • voer dan bovenstaande formule in
  • druk in plaats van op Enter tegelijkertijd op Ctrl-Shift-Enter (de zogenaamde CSE-methode).
  • Excel plaats dan accolades rond de formule

We willen er ook voor zorgen, dat er geen lege regels komen.
De cel Start heeft daartoe een gegevens-validatie gekregen: bij Toestaan is de optie Aangepast ingevuld en bij Formule:
=EN(Start>0;Start<=AantAfd-9)
Dus de waarde in de cel Start moet aan 2 voorwaarden voldoen: groter dan nul EN kleiner of gelijk aan het aantal regels in de bron (minus 9).

LET OP vergeet het eerste =-teken niet

Maximum in schuifbalk

Maar wat als er nu een afdeling bij komt? De tabel tblAfd wordt automatisch aangepast, de teller van het aantal afdelingen zal ook direct opgehoogd worden, de gegevensvalidatie uit het vorige overzicht zal daarom ook goed werken.
Maar … de schuifbalken hebben nog steeds een maximum van 21.

Alleen met behulp van een (kleine) VBA-routine kunnen we het maximum van de schuifbalk automatisch laten meelopen met het aantal regels in de bron-gegevens.

In het overzicht van tabblad SelVersch3 van het Voorbeeldbestand is deze routine geïmplementeerd. Probeer maar eens uit: voeg een regel in het tabblad Data toe en beweeg de schuifbalk op en neer.

Hoe kun je de eigenschappen van de schuifbalk door VBA laten aanpassen?

  1. klik rechts op de schuifbalk
  2. kies de optie Macro toewijzen
  3. als er nog geen macro aan de schuifbalk gekoppeld is dan kiest u Nieuw, anders Bewerken
  4. vul onderstaande programmacode in en sluit de Visual Basic-editor

NB1 afhankelijk van de Excel-versie kan de omschrijving van de subroutine iets anders zijn, bijvoorbeeld Sub Schuifbalk1_BijWijzigen. Laat de naam staan zoals die door Excel is gegenereerd.

NB2 afhankelijk van de situatie kan er ook sprake zijn van Schuifbalk2 etc.

NB3 VBA is altijd Engelstalig. Dus binnen de subroutine is sprake van een ‘shape’ met de naam Scroll Bar 1.

NB4 met de constructie Range(“AantAfd”).Value wordt de waarde uit de cel met de naam AantAfd opgehaald.

‘Automatisch’ sorteren

Zoals al eerder aangegeven zal niet ieder ontvanger van de rapportage de focus op dezelfde KPI leggen. We maken de rapportage zodanig dat de gebruiker zelf kan aangeven welke sortering de gegevens moeten hebben.

Klik op één van de keuzerondjes en de gegevens worden op de betreffende kolom gesorteerd (zie het tabblad Sortering in het Voorbeeldbestand).

  1. keuzerondjes worden op een vergelijkbare manier als schuifbalken aan het tabblad toegevoegd.
  2. klik rechts op één van de keuzerondjes en kies de optie Besturingselement opmaken
  3. koppel het besturingselement aan een cel in Excel; in het voorbeeld C4 ofwel KolomNr
  4. klik rechts op het eerste keuzerondje en kies de optie Macro toewijzen en zorg dat de volgende routine gekoppeld wordt:

NB in dit geval heeft het eerste rondje al het volgnummer 2.

De macro-toewijzing moet voor ieder keuzerondje apart worden uitgevoerd.

Bovenstaand subroutine roept een andere routine aan, Sortering. Deze ziet er als volgt uit (de basis is gemaakt door een macro op te nemen terwijl de sortering handmatig wordt uitgevoerd):


  • als eerste wordt de variabele Kolom gevuld: op basis van de waarde in cel KolomNr wordt één van de kolomkoppen gekozen
  • met het commando With wordt er voor gezorgd dat alle volgende opdrachten betrekking hebben op een Sort van de tabel (ListObject) tblAfd
  • vorige sorteringen verwijderen
  • nieuwe sortering toevoegen
  • het te sorteren bereik heeft een Header/kop
  • de sortering is niet gevoelig voor hoofd- en kleine letters
  • de sortering-orientatie is verticaal (inhoud van een kolom is bepalend)
  • de PinYin-regel mag ook weggelaten worden; alleen interessant bij Chinese tekens
  • en als laatste wordt de sortering toegepast