Categorie archief: Excel

Grafiek automatisch schalen en labels toevoegen

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

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

Test-gegevens

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

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

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

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

Grafiek

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

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

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

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

Assen handmatig aanpassen

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

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

Assen automatisch aanpassen

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

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

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

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

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

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

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

Labels toevoegen

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

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

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

Daar komt VBA weer goed van pas:

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

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

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

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

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

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

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

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

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

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

Titel van grafiek

De titel van de grafiek is dynamisch gemaakt:

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

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

Absoluut en relatief in Excel-tabellen

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

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

Voorbeeld-bestand

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

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

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

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

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

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

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

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

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

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

Tabel-verwijzingen

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

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

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

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

Totalen

Aan een tabel kunnen snel totalen toegevoegd worden:

  1. klik op een cel van de tabel
  2. kies in de menutab Tabelontwerp?? de optie Ontwerp
  3. en vink de optie Totalen aan
  4. in de nieuwe regel onderaan de tabel kan dan per kolom het soort totalen gekozen worden (som, aantal, gemiddelde etc.)

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

Externe verwijzing naar een tabel

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

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

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

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

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

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

Absoluut en relatief in gestructureerde verwijzingen

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

LET OP de syntax is net anders als van SOM.ALS.

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

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

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

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

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

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

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


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

Tabbladen en VBA

Het werken met tabbladen in Excel is eenvoudig: je kunt ze een kleur geven (via rechtsklikken), verplaatsen (door ze met de muis “vast te pakken”), verbergen/zichtbaar maken (via rechtsklikken) etc.

Maar wat te doen, als je werkmap uit tientallen werkbladen bestaat? Dan kan het nogal wat muisklikken kosten om één van bovenstaande handelingen te verrichten.

Daarom in dit artikel enkele VBA-routines, die ons werk flink kunnen vereenvoudigen.

Overzicht van tabbladen (1)

We beginnen met het maken van een overzicht van alle tabbladen, die in een werkmap voorkomen (zie Voorbeeldbestand).
Niet alleen de naam van het tabblad plaatsen we daarin, maar ook of het tabblad verborgen is of niet, de tabkleur en de positie er van.

NB1 de kleur-code -4142 geeft aan, dat het tabblad geen kleur heeft meegekregen.

NB2 het is een goede gewoonte om tabbladen met dezelfde functie eenzelfde kleur te geven; er ontstaat dan niet zo’n kermis als in mijn voorbeeld 😉

In het tabblad Param van het Voorbeeldbestand heeft de cel met de tekst BladNamen de naam BladStart gekregen; binnen de VBA-routine zullen we daar veelvuldig naar refereren (let op de rechte haken rond een naam).

De VBA-routine BladReset genereert een nieuw overzicht:Deze routine staat in Module1 van het VBAproject BladenVBA.xlsm. Via Alt-F11 komt u in de Visual Basic editor (dit kan natuurlijk ook via de menukeuzes Ontwikkelaars/Programmacode/Visual Basic).
Een module toevoegen doet u in de editor via de menukeuzes Invoegen/Module.

Uitleg van enkele belangrijke onderdelen van bovenstaande routine:

  1. na de naam van de subroutine worden 3 variabelen gedeclareerd met het commando Dim. Deze variabelen hebben we dadelijk nodig.
    NB String betekent dat de variabele een tekst zal bevatten, Integer dat het een ‘gewoon’ getal is.
  2. om later enkele tabbladen op een aparte manier te kunnen behandelen zijn die in een Excel-tabel met de naam tblNiet opgenomen.
    In vorige versies van Excel werden tabellen Lijsten genoemd; in de VBA-syntax is nog steeds sprake van een ListObject.
    Wanneer we in VBA de verschillende cellen uit de tabel zonder meer  in een variabele zouden plaatsen, dan zou dat in de vorm van een 2-dimensionale array zijn; via de functie Application.Transpose wordt de ingelezen kolom Niet verbergen omgezet in een 1-dimensionale array.
    Met de functie Join worden de array-elementen in één tekst-variabele geplaatst met (in dit geval) als scheiding een ; (punt-komma).
    NB1 vanwege lay-out-technische redenen is de totale opdracht verdeeld  over 2 regels; dit door middel van een spatie en een underscore (_).
    NB2 we gebruiken DataBodyRange in plaats van Range omdat we de kop van de kolom niet mee willen nemen.
  3. voordat we een nieuw overzicht genereren moeten we een eventueel reeds bestaand overzicht verwijderen:
    [BladStart].Offset(1, 0).Resize([aantBladen], 4).Clear
    Vanuit de cel met de naam BladStart verschuiven (Offset) we 1 regel naar beneden. Deze selectie breiden we uit (Resize) met het aantal rijen in het bestaande overzicht; dat ligt in cel F2 van het tabblad Param vast. Deze cel heeft de naam aantBladen. De selectie moet 4 kolommen breed zijn. De inhoud van deze selectie wordt gewist (Clear).
  4. De For-Next-lus loopt dan alle tabbladen langs. De teller i loopt van 1 tot en met het aantal werkbladen.
    Eerst wordt de naam van het werkblad in rij i geplaatst (met behulp van Offset).
    Standaard is een blad niet verborgen; er wordt wel gecontroleerd of dit toch het geval is. De status (wel of niet verborgen) wordt 1 kolom naar rechts in rij i vastgelegd.
    Met behulp van de functie InStr wordt gecontroleerd op welke positie de sheet-naam in de uitzonderingslijst voorkomt; komt die niet voor dan is het resultaat gelijk aan 0. In dat geval wordt de huidige tabkleur weggeschreven; komt de naam wel voor in de uitzonderingslijst dan plaatsen we de code voor de kleur rood in het overzicht.

Om de routine makkelijk te kunnen uitvoeren is deze aan een knop gekoppeld:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. klik dan op het eerste symbool (de Knop)
  3. wijs met de muis de gewenste positie van de nieuwe knop aan
  4. koppel in het vervolgscherm een macro/subroutine aan de knop
  5. wijzig de tekst van de knop

Overzicht van tabbladen (2)

Overzichten in Excel zijn handiger als ze in tabel-formaat staan (zie menig ander artikel van G-Info). Of dat ook geldt wanneer we via VBA daarmee aan de slag gaan, laat ik aan u zelf over.

Om eenzelfde overzicht als hierboven in tabel-vorm te maken, moeten we eerst de kop van de tabel aanmaken. De tabel op het tabblad Param van het Voorbeeldbestand heeft de naam tblKeus gekregen.

De bijbehorende subroutine BladReset2 lijkt veel op de voorgaande. De volgende afwijkingen behoeven een toelichting:

  1. het aantal regels in het bestaande overzicht laten we niet in een cel bepalen, maar we berekenen die in VBA zelf via ListRows.Count
  2. als er regels zijn (dus aantBladOud niet gelijk aan nul) dan worden de rijen uit de DataBodyRange verwijderd (de tabel wordt dus kleiner)
  3. informatie aan het overzicht toevoegen gaat nu niet via Offset, maar door een variabele nwRij te ‘setten’; deze is van het type ListRow

Tabbladen aanpassen (1)

De overzichten zoals hiervoor aangemaakt kunnen we ook andersom gebruiken:  we veranderen de Verberg-status, de kleur of positie van een tabblad en laten VBA deze wijziging voor het betreffende tabblad uitvoeren.

De volgende opmerkingen:

  1. als het aantal bladen in de werkmap (aantBlad) niet gelijk is aan het aantal rijen in het overzicht (de cel met de naam aantBladen) dan verschijnt er een melding op het scherm en stopt de routine (Exit Sub)
    LET OP de routine controleert alleen het aantal; als de namen niet overeenkomen zal de routine daarop stuk lopen.
  2. ieder tabblad wordt eerst zichtbaar gemaakt.
    Dan, als het geen uitzondering is, en in de 2e kolom van het overzicht staat JA dan wordt het betreffende tabblad verborgen.
    NB de inhoud van de 2e kolom wordt omgezet naar hoofdletters (UCase, uppercase), dus ook ja, Ja of jA zullen een verberging tot gevolg hebben

Ook deze routine is aan een knop gekoppeld.

LET OP de volgorde veranderen van 1 of 2 tabbladen gaat op deze manier (meestal) zonder problemen. Bij heel veel wijzigingen kan het zijn, dat de volgorde niet meteen goed is. Herhaal de procedure dan nogmaals en ….

Tabbladen aanpassen (2)

Ook via de tabel tblKeus kunnen de aanpassingen doorgevoerd worden:

  1. om de routine leesbaar te houden hebben we gebruik gemaakt van de VBA-optie With-End With
    With Worksheets(“Param”).ListObjects(“tblKeus”)
    Als in de regels tussen With en End With een optie begint met een . (punt) dan weet VBA dat dit betrekking heeft op het ListObject tblKeus
  2. binnen de For-Next-lus wordt nog een keer With gebruikt. Alle .Cells-verwijzingen hebben daardoor betrekking op de Range in rij i (van tblKeus).

Kleur tabbladen

Met de toewijzing .Tab.Colorindex kunnen slechts 56 kleuren gebruikt worden (nou ja eigenlijk 57; de code -4142 zorgt er voor dat het tabblad geen kleur krijgt).

In het tabblad Param van het Voorbeeldbestand staat een overzicht van codes en bijbehorende kleuren.

 


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

Voetbalcompetities

Je hoeft niet per se van voetballen te houden om dit artikel te kunnen waarderen.
Ik kreeg van Nico van der Meer een mooi Excel-systeem toegestuurd, dat hij graag met anderen wil delen: het verwerken van voetbaluitslagen tot overzichten en standen, inclusief consequenties voor nacompetities, Champions League etcetera.

In eerste instantie dacht ik: deze sheet bevat wel veel formules, daar zullen we snel wat fouten of mogelijke problemen uit kunnen halen.
Maar niets was minder waar; dus ik geef de werkmap door met maar een paar kleine aanpassingen (verbeteringen?).

Met het systeem van Nico kun je de hele competitie 2018-2019 plezier hebben; alles is al voorbereid, inclusief speeldata en zelfs tijden!
Maar, zoals uit het plaatje hierboven mag blijken, zijn dit soort competities behoorlijk onderhevig aan wijzigingen, dus voor volgend jaar zul je wel weer flink wat aanpassingen moeten doen.

Hieronder zal ik de werking van het systeem kort toelichten en ook een handreiking geven over de aanpassingen voor een volgende competitie-ronde.
Uiteraard zal ik ook de meest gebruikte Excel-functies en de gebruikte VBA-routines toelichten.

Voetbal-competitie

Voordat er zo’n mooi overzicht ontstaat moet er natuurlijk wat voorwerk verricht worden.
In het tabblad Info van het Voorbeeldbestand heeft Nico al diverse aanwijzingen gegeven.

Het systeem omvat 3 competities, Ere-, Eerste – en Tweede Divisie (de EersteDivisie heeft nu de naam KeukenKampioen).
In het tabblad Data liggen de gegevens van de competitieronde en van de teams per divisie vast. Ook kan daar aangegeven worden of de beveiliging (ter voorkoming van het overschrijven van formules) wel of niet moet worden aangezet.

Het tabblad Speelschema bevat per divisie een overzicht van alle rondes en de wedstrijden die daarbinnen gespeeld moeten worden; dit alles inclusief de betreffende datum en tijd. Deze gegevens zijn op internet terug te vinden; via de hyperlink boven iedere divisie zijn de relevante zaken te benaderen.

Klik rechts op een Hyperlink en dan Hyperlink bewerken om te zien hoe deze opgebouwd is.

LET OP om een hyperlink te bekijken moet de beveiliging van het tabblad verwijderd zijn. Dit kan via rechtsklikken op de tab onderaan of door in het tabblad Data aan te geven dat alle beveiligingen verwijderd moeten worden.

Wilt u met dit systeem de resultaten van een competitie volgen dan moeten op dit tabblad Speelschema de uitslagen handmatig ingevoerd worden (voor de Eredivisie in de kolommen C en D).

Resultaten en stand Eredivisie

In het tabblad Eredivisie van het Voorbeeldbestand worden de ingevulde wedstrijdresultaten automatisch verwerkt.

In cel G6 wordt bijvoorbeeld het aantal doelpunten opgehaald voor de thuisclub van de wedstrijd PSV – AZ Alkmaar mbv de formule
=ALS(ISLEEG(VERT.ZOEKEN(TEKST($B6;)&” – ” & TEKST($G$5;);SchemaED;2;0)); “”;VERT.ZOEKEN(TEKST($B6;)&” – “&TEKST($G$5;);SchemaED;2;0))

Via Verticaal Zoeken wordt de wedstrijd (B6, dus PSV, tegen G5, dus AZ) opgezocht in het bereik met de naam SchemaED. De inhoud van de 2e kolom wordt als resultaat teruggegeven. De derde parameter (een 0, nul) geeft aan dat een exacte zoekactie naar de wedstrijdnaam moet worden uitgevoerd.
Als de zoekactie niets oplevert (er is nog niets ingevuld) dan komt er in dit schema ook niets te staan (de dubbele aanhalingstekens), anders wordt het resultaat van de zoekactie in de cel geplaatst.

NB1 de namen van de teams worden door een spatie, een min-teken en opnieuw een spatie van elkaar gescheiden. Bij de opvoer in Speelschema moet dit ook exact zo zijn gedaan.

NB2 Nico heeft de team-namen niet rechtstreeks aan elkaar gekoppeld, maar heeft de functie Tekst gebruikt. Waarschijnlijk om er zeker van te zijn dat Excel deze betreffende cellen zeker als tekst zal zien. Naar mijn idee overbodig.

De overige formules onder en naast het schema wijzen zichzelf.

Ook de standentabel wordt automatisch ingevuld.
Via Vergelijken wordt gekeken op welke positie het betreffende team in de vorige tabel staat en dan wordt via Index het gewenste aantal winst-punten opgehaald (uit en thuis opgeteld).

De formules in de overige kolommen zijn vergelijkbaar of ‘rechttoe-rechtaan’.

In kolom BW staat een code, die gebruikt wordt om de teams in de juiste volgorde te kunnen sorteren en de Plaats in de stand te bepalen.
Wanneer namelijk nieuwe resultaten worden toegevoegd in Speelschema, dan worden wel alle berekeningen direct uitgevoerd en ook de Plaats wordt bepaald, maar de volgorde in de standenlijst verandert niet. Wil je ook de volgorde juist hebben, klik dan op de knop Standen bijwerken. De betreffende VBA-routine (zie hierna) zal dan de sortering uitvoeren.

In cel BW6 staat de formule =WAARDE( TEKST(BS6;”000″)&TEKST(100+BT6;”000″)&TEKST(BU6;”000″)&TEKST(100-BN6;”00″)&TEKST(AANTAL.ALS($BM$6:$BM$23;”>=”&BM6);”00″))

Ook hier wordt de functie Tekst gebruikt; in dit geval om zeker te weten dat het betreffende getal altijd met exact hetzelfde aantal cijfers wordt weergegeven.
Met de functie Waarde wordt van de resulterende tekst weer een getal gemaakt.

Nico heeft in een opmerking bij Code netjes vermeld hoe de code is opgebouwd.

NB bij de Eredivisie zijn na 3 speelrondes al geen gelijke standen meer; bij de KK-divisie na 2 speelrondes nog wel.

In kolom BL wordt de plaats van het team in de stand bepaald: =RANG(BW6;$BW$6:$BW$23)
Met behulp van Voorwaardelijke opmaak krijgen bepaalde Plaatsen ook nog een kleurcode; dit is dus onafhankelijk van de sortering.

Overige divisies en nacompetitie

Bij de KK-divisie heeft Nico ook nog overzichten gemaakt ten behoeve van de bepaling van de periode-kampioenen. Zover ik nu heb kunnen zien heeft hij alle lastige afwijkingen en problemen daarmee uitstekend opgelost.

Wat de nacompetitie betreft: de maker van het systeem is nog niet helemaal zeker over de juistheid daarvan.

“De nacompetitie is voor mij nog experimenteel gezien de complexe regels.
Ik had niet verwacht, dat ik tot iets zou kunnen komen, maar al werkende daaraan ontstond er toch iets”.

Nieuwe competitieronde

Wilt u dit systeem ook een volgend seizoen gebruiken, dat moet er nogal wat aangepast worden:

  1. in het tabblad Data van het Voorbeeldbestand het seizoen aanpassen
  2. ook in Data de teams in de drie divisies aanpassen (hoeven niet per se gesorteerd te zijn)
  3. op het tabblad Data de teams van de Eredivisie selecteren en Ctrl-C (kopiëren) drukken; in het tabblad Eredivisie op de cel BM6 rechts klikken en Plakken speciaal/Waarden kiezen
  4. dezelfde werkwijze hanteren voor de teams van de eerste en tweede divisie
    LET OP bij de eerste divisie moeten de teams vijf keer geplakt worden.
  5. in het tabblad Speelschema alle uitslagen wissen en de wedstrijden die gespeeld zullen gaan worden overschrijven.
    LET OP zorg dat de namen van de clubs exact gelijk zijn aan die in het tabblad Data en dat de 2 ploegen gescheiden worden door een spatie, min-teken en nog een spatie

VBA

Met behulp van de knoppen Standen bijwerken worden bijbehorende VBA-routines uitgevoerd.
Voor de Eredivisie ziet die er als volgt uit:
Het bereik met de naam StandEredivisie wordt gesorteerd op kolom BW  en wel in Aflopende volgorde (grootste getal eerst) en het bereik heeft geen Kop.

Alle andere sorteringen gaan op dezelfde manier. Bij de KK-divisie worden 5 sorteringen achter elkaar uitgevoerd; dus voor alle schema’s van het betreffende tabblad.

In het Excel-object ThisWorkbook staat de volgende routine:

De For-Next-loop loopt alle tabbladen langs en afhankelijk van de inhoud van de cel met de naam BevJN (deze staat in het tabblad Data) wordt de beveiliging aan of uit gezet (zonder wachtwoord).

Application.ScreenUpdating zorgt er voor, dat het scherm niet gaat ‘flikkeren’ bij het selecteren van een tabblad.


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

Subtotaal

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

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

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

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

Brongegevens

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

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

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

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

Draaitabel

Wat te doen?

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

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

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

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

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

Filter

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

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

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

SOM.ALS

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

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

Hetzelfde idee gaat op voor Aantal, Gemiddelde etc.

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

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

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

Excel-tabel

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

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

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

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

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

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

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

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

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

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

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

SUBTOTAAL

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

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

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

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

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

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

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

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

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

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

SUBTOTAAL 2

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

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

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

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

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

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


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