Categorie archief: Excel

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.

 

Subtotaal

Excel, , , ,
Voorbeeldbestand: Subtotaal.xlsxDownload

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

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

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

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

Brongegevens

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

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

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

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

Draaitabel

Wat te doen?

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

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

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

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

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

Filter

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

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

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

SOM.ALS

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

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

Hetzelfde idee gaat op voor Aantal, Gemiddelde etc.

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

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

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

Excel-tabel

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

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

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

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

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

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

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

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

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

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

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

SUBTOTAAL

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

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

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

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

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

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

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

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

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

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

SUBTOTAAL 2

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

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

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

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

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

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

 

Functies grafisch weergeven

Excel, , , ,
Voorbeeldbestand: FunctiesGraf.xlsxDownload

Excel is inzetbaar op veel gebieden; ook binnen het (wiskunde-)onderwijs is het goed bruikbaar.
Ik moest daar deze week aan denken, toen ik bedacht hoe ik 20 jaar geleden met iemand tijdens bijlessen heb zitten worstelen om hem het gevoel bij te brengen hoe 1e- en 2e-graads-functies er in grafiekvorm uitzien.

Eerstegraadsfunctie

Zoals we allemaal (?) weten is de algemene vorm van een eerstegraadsfunctie zoals hiernaast weergegeven.
Door voor a en b waarden in te vullen krijg je een specifieke functie.

In het Voorbeeldbestand in het tabblad 1e graads is de algemene functie ingevoerd door in de menutab Invoegen in het blok Symbolen de optie Vergelijking te kiezen.

De cellen C5 en C6 (naast de teksten a en b) hebben een naam gekregen:

  1. kies de menutab Formules
  2. dan in het blok Gedefinieerde namen de optie Naam definiëren
  3. voer een Naam in, in dit geval _a
  4. kies een Bereik. Standaard is de naam geldig voor de hele Werkmap, maar in dit geval moet het bereik beperkt worden tot het werkblad 1e graads
  5. pas zo nodig de verwijzing nog aan en klik op OK

NB Excel accepteert niet alle letters als Naam; ik heb er in dit geval voor gekozen om alle namen door een _ (underscore) te laten voorafgaan.

In cel B8 wordt de functie tekstueel opgebouwd:
=”ƒ(x) = ” & ALS(_a=0;””;ALS(_a=1;”x”;ALS(_a=-1;”-x”;_a&”x”))) & ALS(_b=0;””;ALS(_b<0;” – “;” + “) & ABS(_b))

De diverse tekstblokken worden m.b.v. het &-teken aan elkaar gekoppeld.

In het eerste blok is de ƒ ingevoerd via Invoegen/Symbool. Kies daar allereerst rechtsonder de optie ASCII (decimaal) en blader dan door de mogelijkheden.
Wiskundige tekens kunnen ook op de volgende site gevonden worden:  Lijst_van_wiskundige_symbolen; gebruik daar kopiëren en plak dan in Excel.

Met de diverse ALS-functies wordt de opmaak geregeld, zodat er bijvoorbeeld geen 0x of 1x in het resultaat komt te staan.

De ABS-functie levert de absolute waarde van een getal; het resultaat is dan altijd groter of gelijk aan 0.

Om de grafiek te kunnen tekenen hebben we een tabel nodig met de bij de functie horende x,y-waarden. In het tabblad 1e graads van het Voorbeeldbestand worden 100 combinaties berekend in de cellen vanaf B32. In cel C32 komt dan de formule =_a*B32+_b

NB Om het effect van veranderingen in a of b goed te kunnen zien zijn de assen ‘vastgezet’: ze lopen allebei van -5 tot +5.

Bij  eerstegraadsfuncties zijn de snijpunten met de assen altijd belangrijk. Die worden in de cellen C10 en C11 berekend met de formules =”(0 , “&_b&”)”, respectievelijk =ALS(_a=0;”geen”;”(“&-_b/_a&” , 0)”)

Omdat het resultaat van de laatste formule vaak geen geheel getal oplevert, is het inzichtelijker om het resultaat zo nodig als een breuk weer te geven (zie cel C15):
=ALS(_a=0;”geen”;”(“&TEKST(-_b/_a;”# ??/??”)&”, 0)”)

M.b.v. de functie TEKST wordt een opmaak meegegeven aan het resultaat -b/a: eventueel een geheel getal (#) en als het nodig is een breuk (met maximaal 2 cijfers in de teller en noemer).

Om alle overbodige spaties te verwijderen, kan de functie SUBSTITUEREN worden gebruikt (zie bijvoorbeeld cel C19 in het tabblad 1e graads).

In de grafiek is ook de functie weergegeven. Dit is als volgt gedaan:

  1. selecteer de cel met de functie en kies kopiëren (Ctrl-C)
  2. klik ergens anders op het werkblad met de rechter muistoets
  3. kies Plakken speciaal …
  4. en in het vervolgscherm de laatste optie van Andere plakopties (Gekoppelde afbeelding)

De inhoud van de oorspronkelijke cel wordt dan dynamisch in een grafisch object weergegeven. Dit object kan via slepen overal in de sheet geplaatst worden, dus ook in de grafiek.

Tweedegraadsfunctie

De opbouw van het tabblad 2e graads van het Voorbeeldbestand is vergelijkbaar met bovenstaande.

Het bepalen van de eventuele snijpunten met de x-as is echter wat ingewikkelder. Weet u nog?

Maar dit levert alleen maar een resultaat op als het getal onder de wortel (de discriminant) niet kleiner is dan nul; dus dat wordt eerste even gecontroleerd.

In C14 staat daarom de volgende formule:
=ALS(Oplos?=”geen”;”geen”;”(“&(-_b-WORTEL(Discr))/(2*_a)&” , 0)”)

Derdegraadsfunctie

In het tabblad 3e graads van het Voorbeeldbestand wordt de grafiek van een derdegraadsfunctie weergegeven. Het bepalen van de snijpunten met de x-as wordt nu wat lastiger; dat valt buiten de scope van dit artikel.

 

Olympische spelen

Excel, ,
Voorbeeldbestand: Olympics-2018-vs2.xlsxDownload

De Giro voorbij, de Tour moet nog komen, Nederland niet op het WK: dan maar nog even dromen over de afgelopen winterspelen.

Een poosje geleden kwam ik een database tegen met daarin alle medaillewinnaars van de (moderne) Olympische spelen.

Een mooie aanleiding om eens te kijken of we wat leuke overzichten kunnen maken (met behulp van draaitabellen natuurlijk).

Basis-materiaal

De gegevens over alle Olympische Spelen zijn verzameld door Shane Devenshire. Die heeft in zijn sheet ook enkele voorbeelden opgenomen.

“The latest version of my free Excel Olympic Database is complete and may be downloaded by using the following link:
http://bit.ly/2BWvAte
Please share the link not the file because I want to know how often this file is downloaded so I can decide if I should continue updating it in the future. The 2016 version had very few downloads so I am thinking of discontinuing the project in the future.”

Ik heb een nieuw, aangepast Voorbeeldbestand gemaakt, maar vergeet niet óók op bovenstaande link te klikken.

Shane gebruikt op zijn welkom-pagina  een onderdeel van Excel, dat ik nog niet vaak ben tegengekomen: SmartArt-afbeeldingen.

Volgens Microsoft: “Een SmartArt-graphic is een visuele weergave van uw gegevens en ideeën. U maakt er een door een indeling te kiezen die past bij uw bericht. In sommige indelingen (zoals organigrammen en Venn-diagrammen) worden bepaalde soorten gegevens weergegeven, terwijl andere indelingen alleen een uitbreiding zijn van de weergave van een lijst met opsommingstekens.
Als u SmartArt-graphics combineert met andere functies, zoals thema’s, kunt u hiermee professionele illustraties maken met slechts een paar muisklikken.”

SmartArt is te vinden via de menutab Invoegen in het blok Illustraties.

Wanneer u in het tabblad Welkom van het Voorbeeldbestand op één van de ringen klikt, opent vanzelf het hulp-menu van SmartArt.

De gegevens zijn allemaal verzameld in het tabblad OlympData van het Voorbeeldbestand.

De betekenis van de meeste gegevens wijst zichzelf.
Alleen de kolom MedAantal (het aantal medailles) behoeft toelichting. Bij een individuele wedstrijd is dit aantal uiteraard gelijk aan 1. Bij een team-prestatie krijgt iedere deelnemer een “evenredig deel van de medaille”.
Dit betekent dat als je het aantal voorkomens in deze kolom telt, dat je dan het aantal uitgedeelde medailles krijgt. Neem je de som dan wordt een team-prestatie slechts als 1 medaille geteld (wat de bedoeling is als je bijvoorbeeld een landenklassement wilt maken).

De kolom Medaille heeft een Voorwaardelijke opmaak gekregen.

In het tabblad Locatie staat een overzicht van alle landen en plaatsen waar Olympische Spelen zijn gehouden.

Overzichten

In het Voorbeeldbestand zijn diverse overzichten opgenomen; de tabbladen Ovz:

  1. het eerste is een landenoverzicht. In cel C2 kan het gewenste jaar worden geselecteerd.
    In het Waarden-gebied van de draaitabel is de Som van het veld MedAantal weergegeven.
    De landen zijn gesorteerd naar aflopend totaal aantal medailles; klik rechts op een land, kies de optie Sorteren en dan Meer sorteeropties.
    NB1 de Medaille-velden heb ik dezelfde voorwaardelijke opmaak gegeven als de kolom in de bron-gegevens. Het woord Medaille is overschreven door MedSrt.
    NB2 gediskwalificeerde winnaars hebben wel een MedSrt, maar geen waarde voor MedAantal. In de draaitabel staat dus ook geen waarde in die kolom.
    Wilt u weten welke deelnemers gediskwalificeerd zijn: dubbel-klik dan op het eindtotaal van de betreffende kolom.
    In hetzelfde tabblad Ovz1 staat ook een landenoverzicht, maar daar zijn de diskwalificaties weggelaten door een filtering op MedSrt (klik op het vinkje achter MedSrt; hebt u een filtering aangebracht dan krijgt dit de vorm van een trechter).
  2. in Ovz2 is het landenoverzicht verder uitgesplitst naar Sport en Event.
  3. Ovz3 geeft de verdeling van het aantal medailles naar geslacht; uitgesplitst per sport.
    Duidelijk is te zien dat bobsleeën nog een mannensport is.
  4. ook zo benieuwd bij welke sport de meeste diskwalificaties plaats vinden en bij welk land? Kijk dan in Ovz4.
    LET OP als nu in het waarden-gebied het aantal van het veld MedAantal wordt bepaald, dan zal Excel niets laten zien omdat dat veld in deze situatie niet is gevuld. Kies dus voor het bepalen van het aantal voorkomens een ander (wel gevuld) veld.
  5. geïnteresseerd in de resultaten van Nederland op de winterspelen in de loop van de tijd? Kijk in Ovz5.
    NB wilt u weten welke atleten de medailles hebben binnengesleept: dubbel-klik op het betreffende aantal.
  6. in Ovz6 ziet u een verdeling naar leeftijd van de winnaars.
    De leeftijden zijn in categorieën ingedeeld.
    Hoe gaat dat in zijn werk?
    * voeg de leeftijd als Rijlabel toe aan de draaitabel
    * klik rechts op een willekeurige leeftijd en kies de optie Groeperen
    * u krijgt dan de mogelijkheid om aan te geven hoe groot het interval moet zijn
    * helaas: aangezien in de kolom Lft ook de tekst NB voorkomt, kan Excel deze groepering niet uitvoeren dus komt er wat ‘handwerk’ aan te pas: selecteer de cellen met de leeftijden 10 t/m 15 en klik rechts op één van die waardes, kies dan de optie Groeperen, wijzig de naam Groep1 in een meer relevante omschrijving (hier 10-15).
    NB1 het Waarden-gebied van de draaitabel heeft een voorwaardelijke opmaak gekregen, waarbij gebruik is gemaakt van de optie Gegevensbalken.
    NB2 er zijn meerdere jaren geselecteerd (2000 t/m 2018); Excel laat dan alleen de tekst “(Meerdere items)” zien. Het verdient aanbeveling om zelf in de cel daarnaast aan te geven welke selectie op dat moment zichtbaar is (in dit geval: resultaten vanaf 2000).