Tagarchief: SOMPRODUCT

Grafieken verduidelijken en interactief maken



Een grafiek zegt meer dan 1000 getallen“.
Met deze parafrasering van een bekende zegswijze ben ik het meestal eens, maar soms valt de uitwerking tegen.

In dit artikel wil ik (aan de hand van cijfers over gemiddelde geboortegewichten) met enkele voorbeelden laten zien hoe je een grafiek aan duidelijkheid kunt laten winnen.

Basis-gegevens

Op zoek naar gegevens over de spreiding van gewichten van baby’s bij hun geboorte kon ik bij het CBS niet veel vinden dat bruikbaar was voor dit artikel. Wel zag ik op de website van het Nederlands Tijdschrift Voor Geneeskunde een onderzoek, waarvan de resultaten wel geschikt waren:

LET OP dit onderzoek stamt uit 1990 (over geboortes in de jaren 70-80); we weten dat het gemiddeld geboortegewicht in de loop van de tijd is gestegen. Volgens het CBS was dit gemiddelde in 2005 ongeveer 60 gram hoger dan in 1990. Wanneer we dit doortrekken zullen de gemiddelde gewichten nu zeker 100 tot 150 gram hoger uitvallen dan de cijfers in bovenstaand onderzoek.

In het tabblad Data van het Voorbeeldbestand zijn deze cijfers overgenomen:

NB1 de cijfers zijn iets anders gerubriceerd, zodat we daar makkelijker een grafiek van kunnen maken.

NB2 Week geeft de zwangerschapsduur aan, n het aantal waarnemingen in de betreffende categorie en de percentielkolommen (P5, P10 etc) dat gewicht waarbij 5%, 10% etc van de geboortegewichten kleiner zijn. Dus in de eerste regel: van de 65 meisjes (het eerste kind, geboren in de 36e week) is het gemiddelde gewicht 2486 gram, is 5% lichter dan 1790 gram en 95% lichter dan 3250 gram.

De gegevens zijn opgenomen in de Excel-tabel met de naam tblData.

Grafiek 1

Door de combinatie van 2 soorten geslacht en 2 soorten geborenen kent het onderzoek dus 4 verschillende categorieën.

Per categorie kunnen we een grafiek maken, maar in het tabblad Selectie van het Voorbeeldbestand staat een interactieve variant: afhankelijk van de keuze voor Geslacht en Eerst/later wordt de grafiek automatisch aangepast.

NB de cellen C2 en C3 zijn met behulp van Gegevensvalidatie afgeschermd, zodat alleen zinvolle invoer mogelijk is.

Op basis van deze cellen (C2 en C3) wordt een hulptabel ingevuld. Hierbij wordt de functie SOMMEN.ALS gebruikt:

  • in de eerste parameter wordt aangegeven waar de gegevens kunnen worden gevonden. In dit geval gebruiken we de functie INDIRECT om de juiste kolom in de tabel tblData te benaderen.
  • de tweede en derde parameter bepalen het eerste criterium waaraan de gegevens, die we willen ophalen, moeten voldoen (alle regels in de tabel tblData waar in de kolom Geslacht de waarde van cel C2 staat)
  • via het vierde en vijfde argument wordt de soort geboorte (al dan niet Eerstgeborene) geselecteerd
  • en het zesde en zevende argument bepalen de juiste week

NB door de 3 criteria voldoet altijd maar 1 cel uit de basis-tabel. Het resultaat is dus geen echte som, maar slechts die ene waarde.

Op basis van deze hulptabel kunnen we snel een grafiek genereren:

Maar wat zien we hier nu eigenlijk?
Met enkele aanpassingen krijgen we een grafiek die beter te begrijpen is:

NB misschien moet de betekenis van P5 etc nog toegelicht worden; dit is afhankelijk van de doelgroep.

Wat is er toegevoegd:

  1. uiteraard een legenda
  2. de reeks Gem is naast de P50 gezet: klik rechts in de grafiek, kies de optie Gegevens selecteren en gebruik in het vervolgscherm de pijltjes:

    NB het gemiddelde en de 50-percentiel zijn in de meeste gevallen ongeveer gelijk aan elkaar
  3. om duidelijker te maken welke lijn het gemiddelde is, heeft deze lijn markeringen gekregen
  4. bij de horizontale as is aangegeven wat de getallen voorstellen
  5. en misschien wel het belangrijkste: de grafiektitel, die aangeeft wat er in de grafiek staat en welke selectie daarbij is gemaakt.
    In cel L17 van het tabblad Selectie wordt de tekst voor de titel gemaakt:

    Tussen de &-tekens staan verwijzingen naar de cellen met de namen Geslacht (C2) en Eerst_later (C3).
    Achter het woord gram staat een ‘harde return’, druk bij het invoeren op Alt-Enter.
    Maak dan een willekeurige grafiektitel, klik daarin en zet in de formulebalk : =Selectie!L17
    LET OP vergeet niet ook de naam van het werkblad in te voeren; je kunt ook na het intikken van het =-teken met de muis op de cel met de tekst klikken.

Om de gegevens van een pasgeborene makkelijk te kunnen vergelijken is de invoer nog uitgebreid met een geboorteweek en in de hulptabel wordt dan met Voorwaardelijke opmaak de overeenkomende regel geaccentueerd:

Op het tabblad Selectie van het Voorbeeldbestand worden ook diverse gemiddeldes berekend:

In M2 wordt het gemiddelde bepaald van alle gemiddelde gewichten uit het onderzoek.
Maar …. dit is niet juist! Per geboorteweek is het aantal baby’s niet gelijk. De betreffende gemiddelde gewichten moeten gewogen worden met deze aantallen.

Om deze ‘weging’ makkelijk te kunnen uitvoeren is aan de brongegevens een berekende kolom toegevoegd, n*Gem (zie het tabblad Data).

In cel M3 wordt op de juiste manier het gemiddelde bepaald:
=SOM(tblData[n*Gem])/SOM(tblData[n])
ofwel de som van alle waardes in de kolom n*Gem in de tabel tblData gedeeld door de som van alle waardes in de kolom n van die tabel.

NB op de CBS-site is te lezen: “In de periode 1989-1991 woog een baby gemiddeld 3 372 gram bij geboorte, in de periode 2004-2006 was dat 3 434 gram.
Dus het gemiddelde gewicht van 1990 is ongeveer gelijk aan dat uit het onderzoek met geboortes uit 1970-1980.

Afhankelijk van de waardes in de cellen C2 (met de naam Geslacht) en C3 (met de naam Eerst_later) wordt het gemiddelde van die selectie bepaald:
=SOMMEN.ALS(tblData[n*Gem];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)/
SOMMEN.ALS(tblData[n];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)
Dus in plaats van een gewone SOM-formule gebruiken we nu SOMMEN.ALS, waarmee we criteria kunnen opgeven voor het optellen.

Op een vergelijkbare manier worden ook de gemiddeldes voor het geselecteerde geslacht en de geselecteerde soort geboorte berekend.

Wil je liever geen hulpkolom (n*Gem) gebruiken dan biedt de functie SOMPRODUCT uitkomst (zie cel N3 in het tabblad Selectie; deze functie vermenigvuldigt alle elementen uit een reeks met de overeenkomende elementen van de andere reeksen en sommeert de resultaten daarvan).
Ook de andere gemiddeldes kunnen op een vergelijkbare manier bepaald worden. In cel N4 staat bijvoorbeeld de formule:
=SOMPRODUCT(tblData[n];tblData[Gem];
1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))/
SOMPRODUCT(tblData[n];

1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))

De criteria die we willen meegeven, staan in aparte reeksen; bijvoorbeeld de kolom Geslacht wordt vergeleken met de waarde in de cel Geslacht (dit is cel C2). Dit levert een reeks op van diverse WAAR’s en ONWAAR‘s; door deze met 1 te vermenigvuldigen wordt dit een reeks 1’n en 0‘n.

Grafiek 2

Wanneer je alleen de grafiek toont (en dus niet de hulptabel) dan is het niet altijd even makkelijk om de exacte waardes af te leiden.
Daarom zijn in bovenstaande grafiek (zie het tabblad Selectie2 van het Voorbeeldbestand) ook de waardes van het gemiddelde en van de P25 en P75 opgenomen van de geselecteerde geboorte-week.
De hulptabel heeft daartoe een extra kolom (WkSel) gekregen:

NB de functie NB() levert als resultaat #N/B. Het voordeel hiervan is dat deze waarde in een grafiek niet wordt weergegeven (zie het artikel Grafiek zonder nullen).
In Excel wordt intern WAAR omgezet in de waarde 1. De reeks WkSel is aan de grafiek toegevoegd en aan de secundaire as gekoppeld. Nog een markering toevoegen aan deze ‘lijn’ en op de juiste plaats zien we een signalering.
Door ook een kolom Label toe te voegen aan de hulptabel kunnen we deze extra signalering van een Gegevenslabel voorzien. Voor de exacte implementatie, zie het tabblad Selectie2 van het Voorbeeldbestand.
Kies je nu een andere geboorteweek (cel C4) dan past de signalering zich automatisch aan:

Grafiek 3

Dit is eigenlijk geen andere grafiek, alleen de manier van selecteren is anders (makkelijker; zie het tabblad Selectie3 van het Voorbeeldbestand).

De hoofdselecties worden gemaakt met behulp van Keuzerondjes, de geboorteweek selecteer je met een Schuifbalk.

Deze voeg je toe via de menutab Ontwikkelaars. In het blok Besturingselementen kies je de optie Invoegen. Selecteer de gewenste optie binnen de Formulierbesturingselementen. Na de selectie kun je zo’n element op de gewenste plaats ‘met de muis tekenen’.

Bij Keuzerondjes is de volgende werkwijze het handigst:

  1. teken één keuzerondje
  2. klik rechts, verander de tekst in m, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N2)
  3. tik Ctrl-C en ergens anders Ctrl-V, verander de tekst in v
  4. voeg eventueel een groepsvak toe, die je om deze twee keuzerondjes tekent
  5. doe hetzelfde voor het soort geborene; maak daar een koppeling met een andere cel (in het voorbeeld N3)
  6. door een keuzerondje aan te klikken wordt er in cel N2, respectievelijk N3 de waarde 1 of 2 geplaatst; in de cellen daarnaast wordt een ‘vertaling’ gemaakt.

De Schuifbalk is eenvoudiger:

  1. teken deze op de plaats waar je hem wilt hebben (kun je later natuurlijk nog aanpassen)
  2. klik rechts, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N4).
    Stel ook de minimum- (in het voorbeeld 36) en de maximumwaarde (43) in.

Grafiek 4

Met behulp van het systeem in het tabblad Vergelijken van het Voorbeeldbestand kun je snel een eigen waarneming afzetten tegen de populatie uit het onderzoek.

Voer de naam, de zwangerschapsduur (in weken) en het geboortegewicht in. In de grafiek is direct de relatieve positie tussen de percentiellijnen te zien. Door wat extra berekeningen wordt via een label ook een schatting voor de absolute positie weergegeven.

Een korte toelichting op de berekening:

  1. zoek eerst de rij op van de geboorteweek (in cel N10 met de functie VERGELIJKEN)
  1. met behulp van deze waarde en de functies VERGELIJKEN en VERSCHUIVING wordt in cel N11 de kolom opgezocht waarvan de score gelijk of kleiner is aan het geboortegewicht uit cel D4.
    LET OP1 de derde parameter in de VERGELIJKEN-functie is weggelaten; meestal is deze parameter gelijk aan 0 (nul; dan wordt een exacte match gezocht). Vaak zal het gezochte geboortegewicht namelijk niet in de tabel voorkomen.
    LET OP2 wanneer je de derde parameter weglaat (beter gezegd: als die niet gelijk is aan 0) dan moet de reeks waarin je zoekt, gesorteerd zijn.
  2. in cel N12 bepalen we de percentiel-waarde die bij die kolom hoort
  3. ook het bijbehorende gewicht wordt dan opgezocht in cel N13
  4. in kolom O worden de vorige 2 stappen uitgevoerd voor een hogere percentielkolom
  5. we interpoleren tussen de hiervoor bepaalde waardes om een schatting te krijgen voor het percentiel dat bij het opgegeven geboortegewicht hoort (zie cel O14)

Om de markering op de juiste plaats te krijgen voegen we een nieuwe kolom in de hulptabel toe met voor elke geboorteweek de waarde NB() en alleen in de betreffende geboorteweek komt het geboortegewicht (zie de kolom Verg in de hulptabel op het tabblad Vergelijken).

Net als bij de vorige grafiek moet aan de hierbij behorende ‘lijn’ een markering meegegeven worden, voordat het markeringspunt zichtbaar zal zijn. Geef deze reeks ook een label mee (zie kolom VergLabel).

Als laatste moeten nog de verticale en horizontale lijnen ingevoegd worden:

  1. de verticale kan het makkelijkst door een nieuwe reeks te maken net als in de vorige grafiek (zie kolom VergWk in de hulptabel van het tabblad Vergelijken). Kies als grafiektype een Kolomdiagram en koppel deze aan de secundaire as.
  2. de horizontale lijn wordt geproduceerd door een reeks toe te voegen met voor iedere week dezelfde waarde, namelijk het geboortegewicht uit cel D4.

NB het ‘systeem’ is nog niet fool-proof: vul je een hoog geboortegewicht in, dan zal het zoeken naar het overeenkomende percentiel problemen opleveren.


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


DB-functies



Data-analyse: een belangrijk item voor menig bedrijf en daarom zijn diverse artikelen op deze site geschreven met dat onderwerp in het achterhoofd.
De gereedschapskist van een data-analist moet flink gevuld zijn om zijn of haar werk goed, effectief en efficiënt uit te kunnen voeren.

Naar aanleiding van het artikel SOMPRODUCT: meer dan SOM en PRODUCT kwam de opmerking langs om in dat kader ook aandacht te besteden aan de DB-functies, ofwel de database-functies van Excel.

Hieronder zal ik enkele mogelijke tools in een analyse-traject naast elkaar zetten: de draaitabel, SOMPRODUCT en de DB-functies. Op die manier wordt vanzelf duidelijk wanneer je welk gereedschap het best kunt inzetten.

Database

In dit artikel gebruiken we een omzet-overzicht, gesplitst naar Regio, Product en Datum (zie het tabblad VbData in het Voorbeeldbestand).
De database is opgeslagen als Excel-tabel met als naam tblOmzet; voor de voordelen zie de artikelen over tabellen deel 1 en deel 2.

Deze database bevat 1.000 records (regels) en een kopregel, waarin de namen van de velden (kolommen) staan.

Analyse dmv draaitabellen

Wat mij betreft is dit in verreweg de meeste gevallen het handigste hulpmiddel voor data-analyse.

Selecteer één van de cellen in de database, klik op Invoegen en dan Draaitabel en bevestig met OK.

Sleep Regio naar Rijlabels, Product naar Kolomlabels en de Omzet naar het Waardegebied.
De laatste stap nog 2 keer herhalen en Som veranderen in Aantal, respectievelijk Gemiddelde.
Nog wat opmaak regelen  en in een mum van tijd hebben we een goed inzicht in de verdeling van de omzet naar de diverse categorieën (zie het tabblad Draai in het Voorbeeldbestand).

Een andere draaitabel laat de verdeling over de maanden zien. Daartoe is de Datum in het vak Rijlabels gesleept. Even rechtsklikken op één van de datums in het overzicht dat ontstaat, Groeperen kiezen (zie Groeperen in een draaitabel), alleen de Jaren en de Maanden selecteren en OK.

Via opmaak van de draaitabel kan deze veelal in de gewenste vorm gebracht worden. Is dat niet het geval dan kan de informatie met de functie DRAAITABEL.OPHALEN nog anders weergegeven worden (zie het gelijknamige artikel).

Analyse met SOMPRODUCT

In het artikel SOMPRODUCT: meer dan SOM en PRODUCT heb ik al laten zien hoe de functie Somproduct gebruikt kan worden om gegevens uit een database op te halen.

Deze methode is ontzettend flexibel is: op iedere willekeurige plaats, in iedere willekeurige volgorde kan informatie verzameld worden uit de database.
Maar … de gebruikte formules zijn niet altijd op het eerste gezicht duidelijk, zie bijvoorbeeld cel C4 in het tabblad SomProd van het Voorbeeldbestand:

=SOMPRODUCT((tblOmzet[Regio]=$C$2)*(tblOmzet[Product]=$C$3)*tblOmzet[Omzet])

NB een groot nadeel van deze methode is, dat je wel al moet weten welke Regio’s en welke Producten in de database voorkomen. Een draaitabel laat automatisch alle voorkomende namen zien.

Analyse met DB-functies

Excel kent diverse database-functies; de naam daarvan beginnen allemaal met DB (in de Engelstalige versie alleen D).
De naam geeft vaak goed weer waar de functie voor bedoeld is: DBSOM, DBAANTAL, DBGEMIDDELDE, DBMAX, DBMIN etc.

Alle database-functies verwachten 3 parameters/argumenten: allereerst waar de database te vinden is (het betreffende cel-bereik), dan het veld (kolomnaam) waarvan gegevens moeten worden verzameld en als derde een bereik waarin criteria zijn aangegeven waaraan de gezochte records moeten voldoen.

Op het tabblad DB1 van het Voorbeeldbestand staat in cel F3 een formule, waarmee de totale omzet wordt bepaald uit Regio Noord voor het Product met de naam Vier:
=DBSOM(tblOmzet[#Alle];tblOmzet[[#Kopteksten];[Omzet]];Criteria)

De 1e parameter (tblOmzet[#Alle]) verwijst naar alle cellen in de omzet-tabel. Voegt u records toe dan zal deze verwijzing de toevoegingen automatisch meenemen.

LET OP tblOmzet is als parameter niet voldoende; de kopregel met veldnamen wordt dan niet meegenomen.

Achter de eerste punt-komma komt de 2e parameter (tblOmzet[[#Kopteksten];[Omzet]]); een rechtstreekse verwijzing dus naar het veld Omzet.

NB in plaats van een verwijzing kun je ook een letterlijke tekst intikken, in dit geval “Omzet” (denk aan de aanhalingstekens). Maar let op: wanneer de veldnaam in de kopregel van de database wordt aangepast, dan werkt deze formule niet meer!
Ook is het toestaan om als 2e parameter een volgnummer van de betreffende kolom mee te geven (in dit geval zou dit 4 zijn). Maar ook hier opletten: komt er een kolom in de database bij, dan werkt de formule niet meer naar behoren.

Achteraan staat de 3e parameter (Criteria). Dit is een zelf-gedefinieerde bereiknaam,  een verwijzing naar de cellen B3:C4.

In het tabblad DB1 staan ook 2 gegevenstabellen, die gebaseerd op DB-functies een soort draaitabel nabootsen; informatie over gegevenstabellen vindt u in het betreffende artikel.

In het tabblad DB2 van het Voorbeeldbestand heb ik een methode uitgewerkt waarmee het ook mogelijk is om de totalen van een bepaalde maand te selecteren. Zoals u ziet kun je in de criteria ook twee keer hetzelfde veld opgeven.

De voordelen van de DB-methode liggen er vooral in, dat de criteria voor de selectie apart  en duidelijk herkenbaar vastliggen.

De selectiecriteria hoeven niet beperkt te blijven tot 1 regel.
In het tabblad DB3 van het Voorbeeldbestand staat een voorbeeld waarbij de criteria uit 2 regels bestaan (waarbij het bereik de naam Crit2 heeft gekregen). In dit geval worden alle records van de Regio Noord meegenomen, waarbij de Productnaam Een OF Twee is.

Bij het opgeven van criteria moet u het volgende altijd goed in het achterhoofd houden:

  • aan de voorwaarden in dezelfde regel  moet tegelijkertijd voldaan worden (in het voorbeeld Regio=Noord EN Product=Een)
  • voorwaarden in verschillende regels selecteren díe records, die aan de ene regel OF aan de andere OF aan allebei voldoen

LET OP1 in de 2e regel van het voorbeeld mag u niet Noord weglaten, want dan zal Excel alle records selecteren, waarbij (Regio gelijk is aan Noord EN Product gelijk aan Een) OF (Product gelijk is aan Twee).

LET OP2 als de criteria meer dan 1 regel beslaan vergeet dan niet alle regels te vullen; eventueel met dezelfde voorwaarden.

NB een leeg veld binnen de criteria betekent dat er geen voorwaarde aan dat veld wordt opgelegd (in die regel).

In dit voorbeeld vooral ziet u de flexibiliteit van het gebruik van de DB-functies.
Probeer dit maar eens snel in een draaitabel klaar te krijgen! Zeker als je later bedenkt dat de grens van de Omzet beter bij 1250 kan liggen.


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

 

 

 

Elf-proef

bsnBSN: iedereen heeft er een, maar weinig mensen zullen dit nummer uit hun hoofd kennen.
Bij het invullen worden dan ook vaak fouten gemaakt. Iedere instantie wil daarom graag geautomatiseerd checken of het ingevoerde getal juist is.

De controle die je op een BSN kunt uitvoeren is een variant van de elf-proef. Vóór het IBAN-tijdperk werd deze gebruikt om bankrekeningnummers te controleren.

Hoewel de toepassing van de elf-proef voor bankrekeningnummers niet meer geldt, komt deze in de praktijk toch nog regelmatig voor. In Excel is die in te bouwen, maar vergt nogal wat hulpkolommen; de elf-proef is dan ook bij uitstek geschikt om via VBA in een eigen formule te verwerken.
Hieronder (na uitleg van de elf-proef) dan ook voorbeelden in Excel en met behulp van VBA (deze functie is dan ook zonder meer bruikbaar in Access).
Daarna behandelen we een alternatieve functie die ook geschikt is voor de controle van een BSN.

Elf-proef

Wikipedia: “De elfproef (11-proef) is een test die in het Nederlandse elektronische betalingsverkeer werd uitgevoerd op negen- en tiencijferige Nederlandse bankrekeningnummers, voor de invoering van het IBAN, om te controleren of het nummer een geldig rekeningnummer kan zijn. Varianten van de elfproef die gebruikmaken van een controlecijfer, worden toegepast bij andere belangrijke nummers, zoals het burgerservicenummer en het betalingskenmerk op een acceptgiro.

Het laatste cijfer van het rekeningnummer wordt met 1 vermenigvuldigd, het voorlaatste met 2, het op twee na laatste met 3, enzovoorts. De producten worden bij elkaar opgeteld en vervolgens wordt de som gedeeld door 11. Het resultaat van deze deling moet voor een geldig rekeningnummer een geheel getal zijn.

Duidelijk toch: ieder cijfer van het rekeningnummer moeten we vermenigvuldigen met zijn gewicht, die producten optellen, de som moet dan deelbaar zijn door 11 oftewel de rest (na deling; bijvoorbeeld bij het delen van 24 door 11 is de rest 2) moet dan nul zijn.

Excel-voorbeeld 1

Ik bedacht me dat ik ooit eens voor een toepassing de functie Elfproef had gemaakt en die later ook bij cursussen gebruikt had. Even zoeken en ja hoor gevonden.

Maar voordat we die gaan gebruiken kijken we eerst in Excel hoe de elfproef werkt: ziet tabblad Vb1 van het Voorbeeldbestand.
Elfproef1Van het banknummer in kolom A wordt telkens, van achter af, een cijfer geïsoleerd (kolommen D, F, H etcetera; bekijk ook de formules in de kolommen E, G enzovoort) en die cijfers worden in kolom C met hun gewicht vermenigvuldigd. In kolom C worden die producten ook nog opgeteld en wordt de Rest bepaald bij deling door 11. Als daar 0 uitkomt dan is het antwoord ok, anders Geen juist nr.

Elfproef2Deze elfproef is bedoeld voor bankrekeningnummers bestaande uit 9 of 10 cijfers. Kolom A bevat dan ook een input-controle, zodat we in de berekeningen niet allemaal controles hoeven in te bouwen.
De input-controle is geïmplementeerd met behulp van Gegevens-validatie: kies de menutab Gegevens en dan in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie. De drie tabbladen zijn als volgt ingevuld:

Elfproef3

Elfproef4Elfproef5

VBA-voorbeeld 1

Om al die hulpkolommen te vermijden is in kolom B gebruik gemaakt van een eigen functie ElfProef1, die dezelfde resultaten oplevert:
Elfproef6In het Voorbeeldbestand is de functie te vinden door de VBA-editor te starten (Alt-F11 of  via het tabblad Ontwikkelaars of door onderaan rechts te klikken op de naam van het tabblad Vb1 en Module1 te kiezen).

Enige uitleg is op zijn plaats:

  1. In Excel wordt de functie aangeroepen met als parameter (de verwijzing tussen de haakjes) een bankrekeningnummer (in het voorbeeld door te verwijzen naar de cellen in kolom A). In de definitie van de Function ElfProef1 staat dan ook dat er een parameter getal wordt doorgegeven van het type Long (een groot geheel getal). Consequentie hiervan is wel, dat als de functie in Excel verwijst naar een tekst (bijvoorbeeld naar cel B1) de functie niets doet en als resultaat #WAARDE! teruggeeft.
    Achter de functie staat As String: het resultaat van de functie is een tekst (string).
  2. ‘groene’ teksten is voor VBA commentaar; hebben verder geen functie. Invoeren door een enkele aanhalingsteken in te tikken.
  3. met Dim geven we aan welke variabelen we in het programma/de functie gaan gebruiken
  4. om hierna makkelijk de cijfers één voor één te kunnen ‘losknippen’ maken we met behulp van de VBA-functie Str van het getal een tekst (string)
  5. door middel van de For-loop wordt de berekening uitgevoerd: de eerste keer wordt i gelijk aan 1, via Next wordt die 2 enzovoort net zolang tot i groter is dan de lengte van de tekst; dan gaat het programma verder met de opdracht na Next
  6.  in de loop wordt de waarde (Val) bepaald van de diverse cijfers en die waarde wordt met i vermenigvuldigd en bij het (reeds bestaande) lngResult opgeteld.
    Omdat het laatste cijfer het gewicht 1 krijgt etc moeten we met het losknippen achteraan beginnen: dus we nemen het gedeelte (Mid) van de tekst dat start op positie lengte + 1  – i; de laatste parameter van de Mid-functie (het getal 1) geeft aan dat we 1 positie uit de tekst knippen.
  7. we gaan er van uit, dat het bankrekeningnummer voldoet, dus geven standaard als output voor de functie ok mee
  8. maar we moeten nog testen wat de deling door 11 oplevert: hier delen we de som door 11, nemen dan het gehele deel daarvan (de functie Int), vermenigvuldigen dan weer met 11 en kijken  of dit weer gelijk is aan de oorspronkelijke som. Zo niet (<> betekent niet gelijk) dan wordt de output van de functie ONGELDIG.

NB de kolommen B en C zijn van een voorwaardelijke opmaak voorzien, zodat snel duidelijk is wanneer een banknummer aan de elfproef voldoet.

Excel-voorbeeld 2

In het Voorbeeldbestand is een tweede voorbeeld opgenomen (zie tabblad Vb2).
Elfproef7We maken van het bankrekeningnummer uit cel A3 (met dezelfde gegevensvalidatie als in voorbeeld 1) eerst een tekst van 10 tekens: we zetten daartoe vóór het rekeningnummer eerst 10 nullen (mbv het &-teken) en nemen dan de 10 rechtse tekens daarvan (cel B3).

NB in cel B4 staat een alternatief met de functie Herhaling, waardoor het duidelijker is dat we 10 keer de nul er voor zetten.

In de kolommen ontleden we de tekst weer in 10 losse gedeelten (van achter naar voren); er wordt een 0 bij opgeteld om Excel automatisch de tekst te laten omzetten naar een getal. In rij 4 wordt de vermenigvuldiging uitgevoerd, waarna in cel D4 de Som daarvan wordt bepaald. In cel D3 bepalen we dan of een deling door 11 als rest 0 oplevert.

In cel D5 wordt de vermenigvuldiging, optelling en check in één formule uitgevoerd:

=ALS(REST(SOMPRODUCT(E2:N2;E3:N3);11)=0;”ok”;”ONGELDIG”)

Voor uitleg over de functie SOMPRODUCT zie mijn vorige artikel.

VBA-voorbeeld 2

In de VBA-editor is ook een functie ElfProef2 opgenomen (alles met isBSN even overslaan).

De verschillen met de vorige functie:

  1. de input-parameter getal is gedeclareerd als String (tekst)
  2. een voordeel daarvan is, dat we de functie exacter kunnen laten aangeven wanneer de invoer niet aan onze eisen voldoet:
    If getal = “” Or IsNull(getal) Or Not IsNumeric(getal) Then
            ElfProef2 = “Geen juiste invoer”
            Exit Function
        End If
    Dus als de invoer leeg is (op 2 manieren) of als het geen numerieke waarde (getal) is dan wordt de output van de functie Geen juiste invoer en wordt deze afgebroken.
    NB deze test kan uitgebreid en nog concreter gemaakt worden afhankelijk van de behoefte.
  3. in de functie vertalen we het getal naar een tekst van 10 tekens (vergelijkbaar met wat we in Excel hebben gedaan)
  4. de constructie lngResult Mod 11 is vergelijkbaar met de functie Rest in Excel

BSN

Zoals hiervoor aangegeven wordt voor de controle van het BSN een vorm van de elfproef wordt gebruikt: het enige verschil met de standaard is, dat het laatste cijfer niet met +1 maar met -1 wordt vermenigvuldigd.

Bij de functie ElfProef2 is daar al rekening mee gehouden:

  1. optioneel (dus niet verplicht) kan aan de functie een tweede parameter isBSN worden meegegeven (Optional isBSN As Boolean = False). Een variabele van het type Boolean kan alleen de waarde WAAR of ONWAAR (True of False) hebben;  als de parameter niet wordt meegegeven dan krijgt deze de waarde ONWAAR/False.
  2. dmv de regel
    If    i = 1   And    isBSN     Then    hulp   =   – hulp
    zorgen we er voor, dat als i gelijk is aan 1 EN isBSN waar is dat dan het tegenovergestelde van hulp wordt opgeteld

Elfproef8In het tabblad BSN van het Voorbeeldbestand zien we dat het standaard-gebruik van de functie ElfProef2 in cel C3 aangeeft dat we te maken zouden hebben met een ongeldig BSN.

Gebruiken we echter de vorm =ElfProef2(B3;WAAR), zoals in cel D3, dan levert dit als resultaat ok op.


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

SOMPRODUCT: meer dan SOM en PRODUCT

SomProductSOMPRODUCT: één van de vele functies van Excel. Waar kan die voor gebruikt worden?
In dit artikel zal ik laten zien dat het meer kan zijn dan een optelling van getallen nadat die vermenigvuldigd zijn, zoals de formule hiernaast weergeeft.
Nog sterker: ik denk dat in de praktijk het (meer oneigenlijke) gebruik van de functie vaker wordt toegepast dan zoals de functie oorspronkelijk bedoeld was.

Standaard-gebruik van SOMPRODUCT

SomProduct1Laten we als simpel voorbeeld een bedrijfje nemen met 4 producten met verschillende prijzen. SomProduct2Tijdens de verkoop in de winkel wordt het aantal verkochte producten netjes bijgehouden. Op het einde van de dag willen we natuurlijk wel snel weten wat de omzet is geweest.
In het Voorbeeldbestand in het tabblad Vb1 heb ik de fictieve verkoopaantallen opgenomen. Om de omzet te bepalen moeten we de aantallen nog vermenigvuldigen met de bijbehorende prijs en alle bedragen optellen.
De standaardmethode is dan als volgt:

  1. Voeg een kolom toe waar de prijs van het product komt te staan. Dit kan makkelijk met een zoek-formule in cel G4 (in het voorbeeld staan Product en Aantal in de kolommen E en F):
    =VERT.ZOEKEN(E4;B$4:$C$7;2;ONWAAR)
    Kopieer deze formule naar beneden.
  2. De omzet komt in kolom H: in iedere regel wordt de cel in kolom F vermenigvuldigd met de cel in G.
  3. Dan onderaan in H27 een SOM-formule en we zijn klaar.

Met de Excel-functie SOMPRODUCT kunnen we de stappen 2 en 3 vervangen door één formule (in cel H28):
=SOMPRODUCT(F4:F26;G4:G26)

Wat doet deze formule: de inhoud van cel F4 wordt vermenigvuldigd met die in cel G4, F5 met G5 enzovoort tot en met de vermenigvuldiging van F26 en G26; de resultaten van de vermenigvuldigingen (PRODUCT) worden opgeteld (SOM).

NB bij het ‘nette’ gebruik van SOMPRODUCT worden de arrays gescheiden door een ; (punt-komma). Het is echter ook toegestaan om de matrices met elkaar te vermenigvuldigen. De formule wordt dan:
=SOMPRODUCT(F4:F26*G4:G26) (zie cel H29)
We zullen hieronder zien, dat deze vorm allerlei mogelijkheden biedt voor filtering van resultaten.
Een ander voordeel is, dat de formule geëvalueerd kan worden:

  1. SomProduct3selecteer cel H29
  2. kies in de menutab Formules in het blok Formules controleren de optie Formule evalueren
  3. er verschijnt een nieuw scherm:
    SomProduct4Klik op Evalueren en Excel zal de onderstreepte bewerking gaan uitvoeren (in dit geval de vermenigvuldiging van de 2 arrays).
    Je ziet dan de tussenresultaten (zoals we zelf ook hadden berekend in de cellen H4 t/m H26) omgeven door accolades ten teken dat het resultaat ook weer een array/matrix is. Bij de volgende evaluatiestap worden de tussenresultaten opgeteld.

SomProduct5Met wat creativiteit kan de standaard-formule ook nog anders gebruikt worden.
Stel dat we in de winkel niet de aantallen, maar de omzet bijhouden en we willen weten hoeveel stuks er over de toonbank zijn gegaan, dan doen we iets vergelijkbaars als hiervoor: een kolom met de prijs toevoegen en daarachter geen vermenigvuldiging maar een deling (omzet/prijs) en dan die aantallen onderaan optellen (zie Voorbeeldbestand in het tabblad Vb1).
Ook dit kan sneller met SOMPRODUCT:
=SOMPRODUCT(K4:K26;1/(L4:L26))

Verbaasd? Weet je nog bij wiskunde: delen door een getal is hetzelfde als vermenigvuldigen met het omgekeerde.
Dus we vermenigvuldigen de omzet (kolom K) met het omgekeerde van de prijs (dus 1 gedeeld door kolom L).

SOMPRODUCT voor gevorderden 1

Maar nu begint het pas leuk te worden!
Nu we ongeveer weten hoe SOMPRODUCT werkt, gaan we nog eens wat andere arrays toevoegen.

In het Voorbeeldbestand heb ik op tabblad Vb2 hetzelfde verkoopoverzicht staan. SomProduct6Waar we hiervoor een totale omzet hebben berekend, gaan we nu de omzet per product uitrekenen zonder  gebruik van een hulpkolom.

De formule: =SOMPRODUCT(($E$4:$E$26=I4)*($F$4:$F$26)*($G$4:$G$26)) in cel J4 volstaat om het resultaat 1.590 te berekenen.
De laatste 2 matrices kennen we al van de totalen-berekening (nu absoluut gemaakt zodat de formule naar beneden naar de andere drie producten kan worden gekopieerd).
Met de eerste array is iets ‘geks’ aan de hand: we gebruiken niet de inhoud zelf van de cellen E4 t/m E26 maar het resultaat van een vergelijking met cel I4: als de inhoud van cel E4 gelijk is aan de inhoud van cel I4 dan wordt het resultaat WAAR anders ONWAAR.
Voor Excel is WAAR gelijk aan 1 en ONWAAR gelijk aan 0; dus bij vermenigvuldigen wordt alleen de omzet meegeteld als de vergelijking met I4 WAAR is.

Bekijk met Formule evalueren de tussenstappen:  SomProduct7  SomProduct8  SomProduct9  ……
SomProduct10 etc

SOMPRODUCT voor gevorderden 2

Maar we hoeven het natuurlijk niet bij één filter/selectie te houden.
SomProduct11In het Voorbeeldbestand op het tabblad Vb3 staat een iets uitgebreider omzetoverzicht. Aangezien we nu ook  informatie over de regio hebben kunnen we daar ook op selecteren/filteren bij het berekenen van de totale omzet:
in cel H4 komt dan =SOMPRODUCT(($B$3:$B$1002=$H$2)*($C$3:$C$1002=$H$3)*$E$3:$E$1002)

Op dezelfde manier turven we ook het aantal keren, dat er voor de regio Noord het product Een is verkocht: =SOMPRODUCT(($B$3:$B$1002=$H$2)*($C$3:$C$1002=$H$3))

Tik in cel H2 Zuid in en de resultaten staan in de cellen H4 en H5!

Voor Draaitabel-adepten (net als ik!) is dit allemaal spielerei en moeilijk gedoe: met 8 muisklikken (en nog een paar extra om de opmaak te regelen) heb je al een totaaloverzicht met alle gewenste uitsplitsingen (zie tabblad Vb3).
Maar ik moet toegeven dat de mogelijkheden van SOMPRODUCT soms verder gaan: je kunt de formules op iedere willekeurige plaats gebruiken en bent dus niet gebonden aan het draaitabel-stramien.
En ook de filteringsmogelijkheden gaan verder:

SomProduct12In het omzetoverzicht is een verkoopdatum opgenomen; door een juiste filtering bepalen we de totalen per maand (ik weet het: met groeperen kan dat ook simpel in een draaitabel!). De formule voor het aantal wordt dan:
=SOMPRODUCT(1*(MAAND($D$3:$D$1002)=$H$7))

LET OP omdat we maar 1 selectie/filtering hebben, hebben we dus ook maar 1 array met daarin de waardes WAAR en ONWAAR. Bij de vorige voorbeelden werden die door Excel naar 1 en 0 vertaald bij de vermenigvuldiging. In dit geval forceren we een berekening door de waarden van de array met 1 te vermenigvuldigen. Een andere berekening is 0 erbij optellen (zie cel I9) of om het tegenovergestelde ervan te pakken (min-teken ervoor zetten; is hetzelfde als vermenigvuldigen met -1; zie cel J9). Doe dat laatste 2x om het juiste teken te krijgen!
Bekijk de tussenstappen met Formule evalueren.

In het tabblad Vb3 zijn nog andere voorbeelden opgenomen, die met een draaitabel niet of moeizaam kunnen worden opgelost. Via de juiste formules is het ook mogelijk om op stukken tekst te filteren.  In cel H12 staat:
=SOMPRODUCT(ISGETAL(VIND.ALLES(H11;$B$3:$B$1002))*$E$3:$E$1002)

Als de tekst uit H11 voorkomt in de cellen in kolom B (VIND.ALLES levert dan een getal op, namelijk de positie waar de tekst gevonden is) dan levert ISGETAL de waarde WAAR op, anders ONWAAR. Bij de vermenigvuldiging met de waardes uit kolom E worden deze automatisch vertaald in 1 en 0. Ook hier: bekijk de tussenstappen met Formule evalueren.

LET OP de functie VIND.ALLES is hoofdletter-gevoelig.

SOM voor gevorderden

Voor de liefhebbers: de eigenschap van SOMPRODUCT, dat berekeningen worden uitgevoerd op reeksen/arrays/matrices kunnen we ook toepassen bij veel ‘gewone’ Excel-functies, zoals SOM.

In I16 wordt SOM gebruikt ipv SOMPRODUCT. Maar om Excel te laten weten, dat er een matrix-berekening moet worden uitgevoerd moet je na het invoeren van de formule NIET op Enter drukken, maar op Ctrl-Shift-Enter (soms afgekort als CSE-invoer).
Excel plaatst dan automatisch accolades rond de formule.

LET OP alleen CSE-invoer kan gebruikt worden voor matrix-berekeningen; zelf plaatsen van accolades werkt niet!

SOMPRODUCT voor zeer gevorderden

Ik kwam deze week toevallig een voorbeeld tegen van SOMPRODUCT waarbij ik toch even op mijn hoofd moest krabben.
Ik heb de formule aangepast voor de Nederlandse versie van Excel en nog wat opmaak toegevoegd:

=”06 “&TEKST(SOMPRODUCT(10^(8-RIJ(INDIRECT(“1:8″)))*ZOEKEN(CODE(HOOFDLETTERS(DEEL(LINKS(SPATIES.WISSEN(SUBSTITUEREN(C3;” “;””))&HERHALING(“0”;8);8);RIJ(INDIRECT(“1:8″));1)));{48;49;50;51;52;53;54;55;56;57;64;68;71;74;77;80;84;87};{0;1;2;3;4;5;6;7;8;9;2;3;4;5;6;7;8;9}));”000 000 00”)

Deze formule zet een telefoonnummer in letters (in reclames werd je er ooit mee dood gegooid; nu zie ik ze niet meer) om naar een echt telefoonnummer:
dus 06 DIZ PK ROI wordt 06 349 757 64.

Gebruik Formule evalueren om de formule te doorgronden op het tabblad Vb4 van het Voorbeeldbestand.

Kom je er niet uit: neem contact op met G-Info.


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