Categorie archief: VBA (Visual Basic for Applications)

Grafiek automatisch schalen en labels toevoegen

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

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

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

Test-gegevens

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

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

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

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

Grafiek

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

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

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

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

Assen handmatig aanpassen

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

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

Assen automatisch aanpassen

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

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

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

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

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

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

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

Labels toevoegen

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

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

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

Daar komt VBA weer goed van pas:

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

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

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

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

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

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

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

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

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

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

Titel van grafiek

De titel van de grafiek is dynamisch gemaakt:

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

 

Tabbladen en VBA

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

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.

 

Voetbalcompetities

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

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.

 

Formules, Namen, Eigen functies

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

De grote kracht van Excel (en ieder ander spreadsheet-programma) is dat je gebruik kunt maken van allerlei formules. Of het nu berekeningen zijn of logische constructies (bijvoorbeeld met behulp van de ALS-functie) of combinaties hiervan: ieder spreadsheet wordt met formules ‘aan elkaar geknoopt’.

Bij de opbouw van formules is het zeer aan te raden om Namen te gebruiken in plaats van cel-verwijzingen als C4 of bereik-aanduidingen als F5:F20. De leesbaarheid van formules gaat daarmee met sprongen vooruit.

In dit artikel daarom (nogmaals) aandacht voor het maken en gebruik van Namen. Ook het opslaan van formules in een Naam komt aan bod. Maar ik zal ook laten zien dat het gebruik van Eigen functies (met behulp van VBA) veel flexibeler is.

Namen definieren

Een naam vastleggen in Excel kan op diverse manieren:

  1. de eerste is een rechtstreekse handmatige invoer (zie tabblad VB1 in het Voorbeeldbestand): selecteer een cel (in dit voorbeeld C2), klik in het Naamvak, links van de Formulebalk, en tik de gewenste naam in (hier dus Korting1) en druk op Enter.
    LET OP een naam mag geen spaties bevatten en geen celverwijzing voorstellen zoals A1 of EXP10. Het is ook af te raden om bestaande functienamen in te voeren (zoals Som); dit is voor Excel geen echt probleem maar voor de gebruiker van het spreadsheet kan het zeer verwarrend zijn.
    NB1 gebruikelijk is om op de plaats waar je een spatie zou willen zetten het _-teken te plaatsen (de underscore; bijvoorbeeld Korting_1) of het tweede gedeelte met een hoofdletter te laten beginnen (bijvoorbeeld KortPerc).
    NB2 worden er meerdere cellen tegelijkertijd geselecteerd, dan krijgt het totale bereik de ingevoerde naam; de afzonderlijke cellen hebben dan geen naam.
  2. plaats een omschrijving links of boven de cel die een naam moet krijgen.
    Selecteer de omschrijving en de betreffende cel (in het voorbeeld B2:C2) en kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie.
    In het pop-up venster heeft Excel in dit geval al ‘gezien’ dat er links van de cel een omschrijving staat; deze zal als naam voor cel C2 worden gebruikt.
    NB1 als er spaties staan in de omschrijving dan vervangt Excel deze automatisch door een underscore.
    NB2 stelt de omschrijving een celverwijzing voor dan plaatst Excel een underscore achter de naam (Rij1 wordt dan Rij1_) .
    NB3 zijn er naast de omschrijving meerdere cellen geselecteerd dan krijgt het totale bereik van die cellen de tekst van de omschrijving als naam.
  3. selecteer de cel (of cellen) die een naam moet krijgen en kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
    Voer een naam en eventueel een opmerking in. Controleer of de verwijzing klopt.
    Het Bereik voor een naam staat standaard op Werkmap; de naam kan overal gebruikt worden. In dit geval is deze korting alleen maar geldig op het tabblad Vb1, dus moet het bereik daaraan aangepast worden.
    NB klik je op het ‘vinkje’ achter het Naamvak, dan zie je alle namen die voor dit tabblad geldig zijn (inclusief de namen die voor de hele werkmap gelden).
  4. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren en klik op de button Nieuw

NB wanneer op een van deze manieren een naam wordt ingevoerd zal Excel de verwijzing standaard ´absoluut´ maken; zie de $-tekens.

Namen beheren

Wil je kijken welke namen er in het Excel-bestand worden gebruikt en/of deze aanpassen kies dan in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren.

Hier is het mogelijk om een nieuwe naam toe te voegen of een bestaande te verwijderen.
Ook kan een naam bewerkt worden.

LET OP Bij bewerken kunnen de naam, opmerkingen en verwijzing aangepast worden, dus NIET het bereik. Wil je deze wijzigen dan moet je een nieuwe naam toevoegen met een ander bereik en de andere verwijderen.

Gebruik van Namen 1

In het tabblad Vb1 van het Voorbeeldbestand zijn netto-bedragen berekend met behulp van een zelf-gedefinieerde naam. In cel D7 staat de formule =C7*(1-Korting1).
Wanneer deze naar beneden gekopieerd wordt, wijzigt C7 automatisch in C8 etcetera. De korting is via de naam absoluut gedefinieerd en verwijst dus ook bij het kopiëren altijd naar cel C2.

Bij het invoeren van de formule laat Excel bij het intikken van de letters ko alle mogelijkheden zien, die voor dit tabblad gelden. De opties met ƒx zijn functies binnen Excel (standaard of eigen functies, zie hierna), de andere zijn namen.
Dubbelklik op de gewenste functie of naam of selecteer deze met de cursor en druk dan op de tab-toets.

Gebruik van Namen 2

Bij het definiëren van namen hoeft u zich niet te beperken tot cel-verwijzingen. Het is ook mogelijk om formules op te nemen.

Voor het tabblad Vb2 in het Voorbeeldbestand is voor de naam Kort2a als verwijzing de formule =1-Vb2!$C$2 ingevoerd. Dat houdt in dat de berekening van het netto-bedrag er dan iets anders kan uitzien:
het oorspronkelijke bedrag wordt vermenigvuldigd met Kort2a. Het mag duidelijk zijn dat vooral bij uitgebreide/complexe formules hierdoor het spreadsheet overzichtelijker blijft.

In kolom E staat een vergelijkbare formule, maar daar wordt gebruik gemaakt van Kort2b, die gedefinieerd is als =1-‘Vb2’!Korting2

LET OP wanneer binnen zo’n formule gebruik wordt gemaakt van een naam, die niet voor de hele werkmap geldig is, dan moet deze naam voorafgegaan worden door de naam van het tabblad, gevolgd door een !-teken. De aanhalingstekens zijn eigenlijk alleen maar nodig als de naam van het tabblad spaties bevat.

Gebruik van Namen 3

In het vorige voorbeeld is het kortingspercentage via een Naam omgezet in een kortingsfactor. Maar ook het netto-bedrag kan via zo’n constructie worden bepaald; zie het tabblad Vb3 in het Voorbeeldbestand.
Netto3a is gedefinieerd als
=’Vb3′!C7*(1-‘Vb3!Korting3)

LET OP C7 is relatief (zonder $-tekens) gedefinieerd. En dit geredeneerd vanuit cel D7. Dit betekent dat dezelfde naam Netto3a vanuit cel D8 verwijst naar cel C8. Selecteer maar eens een cel ergens in kolom D en kies dan Namen beheren. Of kies de optie Broncellen aanwijzen in het blok Formules controleren.

Maar wanneer we de naam Netto3a in een andere kolom plaatsen gaat het mis. Zie cel H21 in het tabblad Vb3. De naam Netto3a hanteert nu als bron de cellen uit kolom G.

Met de formule in Netto3b is dit opgelost: =’Vb3′!$C7*(1-!Korting3)
Dus de kolom is absoluut (altijd C), maar de rij relatief; zie cel H23.

NB de exacte locatie van de cel met de naam Korting3 is weggelaten; alleen het !-teken is voldoende. Een nadeel hiervan is dat Broncellen aanwijzen dan niet meer volledig is. In het Voorbeeldbestand is wel de naam van het tabblad opgenomen omdat Mac-gebruikers anders in de problemen komen.

In het tabblad Vb3 van het Voorbeeldbestand is nog een derde naam (Netto3c) opgenomen met als formule =’Vb3′!$C7:$C40*(1-!Korting3)

De werking is hetzelfde maar het voordeel is dat je nu het totaal van de netto-bedragen kunt bepalen met de formule =SOM(Netto3c). Deze formule moet wel ergens in rij 7 staan; zie het resultaat in de cellen H7 en H8.

LET OP Excel weet niet dat de inhoud van zo’n naam een formule is en zal het resultaat niet automatisch herbereken (bijvoorbeeld bij het wijzigen van het kortingspercentage). Daarom is de SOM nog aangevuld met +0*NU(); aangezien NU ieder moment van waarde verandert zal Excel de totale formule bij iedere wijziging van het spreadsheet wel opnieuw berekenen.

Gebruik van Namen 4

Wanneer gebruik wordt gemaakt van Excel-tabellen (en zoals we allemaal weten heeft dat grote voordelen) dan moet goed opgelet worden bij het definiëren van Namen.

In het tabblad Vb4 van het Voorbeeldbestand bestaat het systeem uit de tabel tblNetto. Binnen dat tabblad is de naam Netto4a gedefinieerd als =tblNetto[Bedrag]*(1-!Korting4)

De opzet van de formule is vergelijkbaar met de naam Netto3c. De werking is nu echter anders: in iedere rij komt nu hetzelfde resultaat, namelijk het nettobedrag dat hoort bij de eerste regel. De SOM-formule werkt wel goed en is nu onafhankelijk van de plaats.

Voor de juiste berekening in de tabel hebben we een andere naam nodig:
=’Vb4′!$C7*(1-!Korting4) of =tblNetto[@Bedrag]*(1-!Korting4)

NB de @ zorgt er voor dat met het bedrag in de betreffende regel wordt gerekend.

Eigen functie

Zoals hiervoor duidelijk mag zijn geworden kleven er aan het gebruik van formules in Namen wel nogal wat problemen of laten we zeggen aandachtspunten.
Wel is een voordeel van die systematiek dat beginnende gebruikers niet zomaar formules kunnen aanpassen.

Met een klein beetje VBA lossen we voorgaande aandachtspunten dat snel en vakkundig op:

  1. druk op Alt-F11 of kies de optie Visual Basic in het blok Programmacode van de menutab Ontwikkelaars
  2. zoek het betreffende project (in dit geval Formules.xlsm) en klik daarop
  3. als er nog geen Modules zijn, kies dan eerst Invoegen/Module
  4. dubbelklik op een module en plaats daar de volgende functie-definitie
    Function BerekenNetto(Bedr, KortPerc)
          BerekenNetto = Bedr * (1 – KortPerc)
    End Function
    De eigen functie BerekenNetto kent 2 parameters: een bedrag en een kortingspercentage. Het resultaat van de gewenste berekening wordt aan de functienaam gekoppeld.

In het tabblad Vb5 van het Voorbeeldbestand wordt deze eigen functie gebruikt. Omdat we binnen een Excel-tabel werken kunnen we weer naar de Bedrag-kolom wijzen (denk aan de @).

Maar de formule kan ook ergens anders in het tabblad staan, en wordt dan iets als =BerekenNetto(C7;Korting5)

Overzicht Namen

Zeker als er veel namen gebruikt worden in een Excel-systeem is het belangrijk om het systeem goed te documenteren.
Eén van de onderdelen van de documentatie moet een goede beschrijving van de verschillende Namen zijn (zie het tabblad Docu in het Voorbeeldbestand).

Gelukkig kunnen we een groot gedeelte automatisch laten genereren:

  1. selecteer een tabblad met één of meer geldige namen
  2. selecteer ergens een lege cel; zorg dat rechts en daaronder genoeg lege cellen zijn
  3. kies in de menutab Formules in het blok Gedefinieerde namen de optie Gebruiken in formule; in het vervolgscherm kiest u Namen plakken
  4. wanneer u dan op Lijst plakken klikt, wordt er een overzicht in het tabblad geplaatst
  5. kopieer de gegevens naar een documentatie-tabblad
  6. herhaal alle stappen voor ieder tabblad

NB alle namen die geldig zijn voor de hele werkmap komen dan meerdere keren voor; dus is er nog wat schoning vereist.

 

Sudoku in Excel

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

Ik kreeg onlangs de volgende vraag: kun je geen artikel schrijven waarin je uitlegt hoe je met behulp van Excel Sudoku-puzzels op kunt lossen.
Mijn antwoord bestond uit 2 woorden ‘Ja’ en ‘maar’.

De Ja, omdat met Excel ‘alles’ mogelijk is en de maar omdat het oplossen van zo’n puzzel meer een programmeer-uitdaging is en niet zozeer iets om de mogelijkheden van Excel voor de dagelijkse praktijk te laten zien.

Maar toen ik er wat langer over nadacht, zag ik wel wat mogelijkheden.
Dus nu toch een Sudoku in Excel, maar niet een machine die de oplossing genereert (wat zou je daar ook mee moeten), wel een systeem dat het zoeken van een oplossing ondersteunt.

Met behulp van Voorwaardelijke opmaak laat Excel zien op welke plaatsen een cijfer geplaatst kan worden. Dubbele invoer in een rij, kolom of blok wordt ook door Voorwaardelijk opmaak gesignaleerd. Bij deze opmaak wordt veelvuldig gebruik gemaakt van de Namen-optie van Excel; niet alleen de standaard-toepassing daarvan maar ook enkele meer ingewikkelde en dus interessante mogelijkheden!

Basis van het G-Info/Sudoku-systeem

Allereerst hebben we natuurlijk een blok van 9 bij 9 nodig, die dan verder is onderverdeeld in kleinere blokken van 3 bij 3 en in rijen en kolommen. Daar gebruiken we een eigenschap van cellen voor; we kunnen de (dikte van de) randen aanpassen (druk op Ctrl-1). Het bereik van de 81 cellen heeft de naam Sudoku gekregen.

Om straks te weten over welke kolom of rij we het hebben, zijn in regel 3 en in kolom B de cijfers 1 t/m 9 ingevuld. De kruising van die twee, dus cel B3, heeft de naam Ref gekregen. Straks moeten we ook weten wat de inhoud van een geselecteerde cel is; die informatie komt in cel O3 met de naam Selectie.
Regel 3 en de kolommen B en O zijn ‘onzichtbaar’ gemaakt . Dit is te zien aan de +-jes; door daar op te klikken, open en sluit je een bepaalde groep cellen (zie het tabblad Sudoku in het Voorbeeldbestand).

Om het geheel wat mooier te maken is het standaard-raster weggehaald (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rasterlijnen weergeven uit).
Alle regels en kolommen, die we niet nodig hebben, zijn verborgen (selecteer ze eerst en dan rechts klikken).
En als laatste zijn de klom- en rijnamen weggelaten (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rij- en kolomkoppen weergeven uit).

Voor een soepele werking van het spel zijn nog een paar knoppen toegevoegd die bepaalde VBA-routines opstarten; hier komen we later op terug.

Controle 1

Het eerste wat we willen weten als we een Sudoku invullen, is waar overal (als voorbeeld) een 1 staat of waar een 2 etc.
Dat kunnen we met Voorwaardelijke opmaak goed zichtbaar maken; de enige voorwaarde is dat we weten welk cijfer we willen laten oplichten. Daarvoor hebben we een klein VBA-programmaatje nodig:

De subroutine Worksheet_SelectionChange is gekoppeld aan het werkblad Sudoku. Als de selectie in dit werkblad wordt gewijzigd, wordt deze routine automatisch door Excel aangeroepen. Op deze manier bevat de cel Selectie (O3) altijd de laatst geselecteerde waarde uit het bereik Sudoku.

NB bekijk de VBA-routine in het Voorbeeldbestand door bijvoorbeeld met de muis rechts te klikken op de tab Sudoku en dan te kiezen Programmacode weergeven. Eventueel nog dubbelklikken op het blad Sudoku in de Projectverkenner.

Nu kunnen we de Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en Fselecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we de 2e optie (Alleen cellen opmaken met) en geven dan aan dat de opmaak moet worden doorgevoerd als de celwaarde gelijk is aan de inhoud van de cel Selectie (denk aan het =-teken vóór Selectie)
  4. nog even de gewenste Opmaak instellen en OK

Controle 2

De volgende controle zorgt er voor dat we weten of er geen dubbele cijfers in de kolommen, rijen of kleine blokken voorkomen.

Voordat we dit makkelijk en flexibel kunnen doen moeten we wat voorwerk verrichten.
Excel moet automatisch beoordelen bij welke rij, kolom en blok een bepaalde cel hoort. Dan heb ik gedaan door enkele nieuwe Namen in te voeren:

  1. plaats de cursor in de eerste cel van de Sudoku
  2. een nieuwe naam creëren: kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  3. in het vervolgscherm kiezen we als Naam een handige omschrijving, in dit geval RijSel en bij Verwijst naar: =Sudoku!$B5
    Denk aan het dollar-teken voor de kolom-letter.
    Afhankelijk waar de cursor staat (nu in regel 5) zal deze naam de waarde uit kolom B van die regel ophalen.
  4. op gelijkwaardige manier bestaat er ook de naam KolomSel met als formule =Sudoku!D$3
    Deze naam geeft dus altijd de waarde uit regel 3 van de kolom waar de cursor staat.
  5. om te bepalen in welk blok de cursor staat is wat ingewikkelder:
    =GEHEEL((Sudoku!RijSel-1)/3)*3+GEHEEL((Sudoku!KolomSel-1)/3)+1
    Hier is de naam BlokSel aan gekoppeld.
  6. nu moeten we nog de inhoud van zo’n rij, kolom en blok kunnen ophalen om de inhoud te kunnen testen. Daartoe zijn nog 3 extra namen gedefinieerd:
    RijTest met de formule =VERSCHUIVING(Sudoku!Ref;Sudoku!RijSel+1;2;1;9)
    KolomTest met =VERSCHUIVING(Sudoku!Ref;2;Sudoku!KolomSel+1;9;1) en
    BlokTest met =VERSCHUIVING(Sudoku!Ref;GEHEEL((Sudoku!BlokSel-1)/3)*3+2;REST((Sudoku!BlokSel-1);3)*3+2;3;3)

Nu kunnen we een nieuwe Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =OF(AANTAL.ALS(BlokTest;D5)>1;
    OF(AANTAL.ALS(RijTest;D5)>1;
    AANTAL.ALS(KolomTest;D5)>1))
    Aantal.Als kijkt hoe vaak in een bereik (de eerste parameter) de waarde van de tweede parameter voorkomt.
  4. nog even een Opmaak instellen en OK

NB1 wil je de gebruikte opmaak bekijken kies dan Regels beheren binnen de optie Voorwaardelijke opmaak

NB2 achteraf gezien was het waarschijnlijker makkelijker geweest om voor het testen de functie INDEX te gebruiken in plaats van VERSCHUIVING. Misschien een andere keer.

Controle 3

Als laatste controle voegen we nog wat opmaak toe, die aangeeft op welke plaatsen een bepaald geselecteerd cijfer nog kan worden ingevuld:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =EN(D5<=0;
    EN(Selectie>0;
    EN(AANTAL.ALS(BlokTest;Selectie)=0;
    EN(AANTAL.ALS(RijTest;Selectie)=0;
    AANTAL.ALS(KolomTest;Selectie)=0))))
    Dus als de betreffende cel, waarvan de opmaak moet worden ingesteld, een waarde kleiner of gelijk aan nul heeft (dus er is nog geen cijfer ingevuld) EN via Selectie weten we welk cijfer we onder de loep nemen EN in het betreffende blok komt het cijfer nog niet voor EN ook niet in de rin EN niet in de kolom dan moet de opmaak toegepast worden.

Buttons/knoppen

Onder de Sudoku zijn een paar knoppen opgenomen, die het opzetten van een spel kunnen vergemakkelijken (zie het Voorbeeldbestand):

  1. Wissen: de beveiliging van het tabblad wordt verwijderd, het speelveld leeg gemaakt, lettertypes en eventuele kleuren worden gereset. Ook de Selectie en de Starttijd worden leeg gemaakt.
  2. de volgende stap is dat een nieuw spel moet worden opgezet. Doe dit op het tabblad Makkelijk of Moeilijk door onder een bestaand spel ‘met de hand’ cijfers op de juiste plaats te zetten.
    Kies dan op het tabblad Sudoku de knop Kopieer. Er komt een pop-up waarmee kan worden aangegeven welk spel uit de voorraad moet worden opgezet. Selecteer daartoe het betreffende blok met de muis en klik dan op OK.
  3. Als je klaar bent om te gaan spelen, klik dan op de knop Start. Allereerst worden alle opgezette cijfers geblokkeerd en vet gemaakt, het tabblad beveiligd (zonder wachtwoord) en de Starttijd ingevuld.
  4. Wil je tussentijds wat zetten gaan proberen, bewaar dan de tussenstand via de knop Save. De stand van dat moment wordt gekopieerd naar het tabblad SaveGame.

Handleiding

In het tabblad Handleiding staat de werkwijze van het systeem ook uitgelegd en ik heb enkele tips toegevoegd.

Veel speel-plezier!