Tagarchief: Herhaling

Reviews

Excel, VBA (Visual Basic for Applications), , , , , ,
Voorbeeldbestand: Reviews.xlsmDownloaden

Alle (on-line) bedrijven willen tegenwoordig van hun klanten graag een review ontvangen.
Dit is niet alleen interessant voor de marketingafdeling maar ook voor nieuwe kopers. Ikzelf kijk tenminste ook altijd gauw door de lijst reviews om te zien wat anderen van het product vinden.

Meestal kun je een product waarderen met 1-5 sterren.
De betrouwbaarheid van de gemiddelde score (zoals hierboven de 4,7) wordt natuurlijk steeds groter met het aantal reviews dat er is uitgebracht. Maar de kwaliteit van de score wordt ook bepaald door de spreiding van de uitgebrachte scores. Vandaar dat je meestal nog kunt doorklikken:

In dit artikel gaan we wat mogelijkheden binnen Excel langs waarmee we bij een review-overzicht snel en interactief op de details kunnen inzoomen (het idee en de cijfers zijn ontleend aan een artikel van Chandoo).

Basis-gegevens

In het Voorbeeldbestand in het tabblad Reviews ziet u een bestand met voor enkele top-deals van Bol.com een reviewer-code met de daarbij behorende FICTIEVE score.

Door middel van een draaitabel kunnen we snel een overzicht met gemiddeldes genereren:

Maar om makkelijk een overzicht van de onderliggende details te kunnen tonen gaan we een andere methode hanteren.

Samenvattend overzicht

In kolom C hebben we de namen van de aanbiedingen geplaatst, waar we de gemiddelde Rating van willen weten (zie het tabblad Ovz1 van het Voorbeeldbestand).

NB wil je snel alle items hebben, die in het basis-bestand voorkomen, dan liggen er 3 methodes voor de hand:

  1. maak een draaitabel zoals hierboven en kopieer de namen naar de gewenste plaats
  2. gebruik de optie Geavanceerd in het blok Sorteren en filteren van de menutab Gegevens:
    en vul bij Kopiëren naar de gewenste plaats in
  3. kopieer de kolom met de aanbiedingen en kies dan de optie Dubbele waarden verwijderen in het blok Hulpmiddelen voor gegevens van de menutab Gegevens.

In cel D3 staat de formule =GEMIDDELDE.ALS(tblReviews[Aanbieding];C3;tblReviews[Rating])
Ofwel bepaal het Gemiddelde van alle Ratings als de naam in C3 gelijk is aan een naam in de kolom Aanbieding van de Excel-tabel tblReviews.

In kolom E wordt het gemiddelde grafisch weergegeven. Daarbij worden 3 symbolen uit het font Wingdings2 gebruikt: (zie het tabblad Berekeningen).

De cellen met de symbolen hebben respectievelijk de namen crkVol, crkHalf en crkLeeg gekregen.

Cel E3 bevat de formule:
=HERHALING(crkVol;GEHEEL(D3+0,25))
&HERHALING(crkHalf;ALS(EN(REST(D3;1)>0,25;REST(D3;1)<0,75);1;0))
&HERHALING(crkLeeg;GEHEEL(5-(D3-0,25)))

Eerst wordt het aantal volle cirkels bepaald door het gehele deel van de waarde in D3 te nemen (door daar 0,25 bij op te tellen wordt een waarde van bijvoorbeeld 1,8 ook voorzien van 2 volle cirkels); de functie Herhaling genereert een tekst met het gewenste aantal volle cirkels.

Als de Rest van deling van D3 door 1 groter is dan 0,25 EN kleiner dan 0,75 dan zal de tweede Herhaling-functie een halfvolle cirkel opleveren, anders niets.

Als laatste wordt het aantal toe te voegen lege cirkels gegeneerd.

Door middel van het &-teken worden de drie teksten aan elkaar gekoppeld.

LET OP op deze manier wordt een tekst gemaakt die uit symbolen uit het font Wingdings2 bestaat. Om deze tekst in Excel zichtbaar te maken moeten de betreffende cellen in kolom E dan ook het lettertype Wingdings2 hebben.

Zoals hiervoor gezegd hebben we details over het aantal reviews en de spreiding in de scores nodig om de gemiddeldes op waarde te schatten.

Detail overzicht

In het tabblad Berekeningen van het Voorbeeldbestand vindt u een overzicht van de scores van een bepaalde aanbieding.
Afhankelijk van de waarde in cel C2 (met de naam aanbNr) wordt in cel D2 (met de naam aanbNm) de naam van de betreffende aanbieding opgehaald door middel van de functie Index (lstAanbiedingen is de lijst producten in kolom C van het tabblad Ovz1).

Cellen B1:B9 bevatten de getallen 1 t/m 5; alleen hebben die een Aangepaste notatie gekregen: “Score “0
Selecteer daartoe de cellen, druk op Ctrl-1 (Celeigenschappen) en kies het tabblad Getal:

In cel C5 staat de formule: =AANTALLEN.ALS(tblReviews[Aanbieding];aanbNm;tblReviews[Rating];B5)
Ofwel bepaal het aantal als de waarde in de kolom Aanbieding van de Excel-tabel tblReviews gelijk is aan aanbNm en de waarde in Rating gelijk is aan de waarde in B5 (het woord Score daar is opmaak!).
Cel C5 heeft ook weer een Aangepaste notatie (0) gekregen.

In cel C10 wordt de som bepaald van de cellen daarboven met als Aangepaste notatie 0 “reviews”. Dit levert het totaal aantal reviews voor de betreffende aanbieding. Dit totaal wordt in de cellen D5:D9 overgenomen. Dit hebben we nodig voor het maken van de bijbehorende grafiek.

Detail grafiek

Van het detail-overzicht kunnen we nu makkelijk een grafiek maken:

  1. selecteer de cellen B4:D9 in het tabblad Berekeningen
  2. kies in de menutab Invoegen in het blok Grafieken de optie Kolom- of staafdiagram
  3. kies het eerste staafdiagram (Gegroepeerde staaf)

We hebben op deze manier een grafische weergave van de aantallen per score vergeleken met het totaal aantal reviews.

Deze grafiek is gekopieerd naar het tabblad Ovz1; zoals te zien is doet wat opmaak wonderen:

  1. verwijder de horizontale as, de gridlijnen en de legenda
  2. klik op Grafiektitel, plaats de cursor in de Formulebalk, tik in = en klik met de muis op cel C10 van het tabblad Berekeningen
  1. klik ergens in de grafiek met de rechter muisknop en kies Gegevens selecteren. Verplaats Aantal naar beneden
  2. klik rechts op één van de staven en kies de optie Gegevensreeks opmaken.
    Zet de Overlapping van reeks op 100%.
  3. klik rechts op één van de scores en kies As opmaken.
    Zet het vinkje aan bij Categorieën in omgekeerde volgorde.
  4. klik rechts op één van de grote staven en kies de optie Gegevenslabels toevoegen.
    Klik rechts op één van de labels en kies de optie Gegevenslabels opmaken. Kies de optie Waarde uit cellen en selecteer de cellen C5:C9. Vink de optie Waarde uit.
  5. pas de overige opmaak naar wens aan.

Interactief details tonen (1)

Nu moeten we nog een methode vinden om vanuit het review-overzicht de detail-grafiek te tonen. Op het tabblad Ovz1 van het Voorbeeldbestand hebben we gebruik gemaakt van Keuzerondjes:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen

    en kies daar de optie Keuzerondje (Formulierbesturingselement).
    NB zie je geen menutab Ontwikkelaars? Klik ergens in het menugedeelte met de rechter muisknop en kies de optie Het lint aanpassen. Zorg dan dat in het rechterblok de optie Ontwikkelaars is aangevinkt.
  2. klik in de cel waar je het eerste rondje wilt hebben. Verschuif het rondje eventueel nog zodat hij precies op de gewenste plaats staat.
  3. pas de standaard-tekst Keuzerondje 1 aan; in dit geval wissen we de tekst helemaal.
  4. klik rechts op het rondje en kies de optie Besturingselement opmaken. Zorg dat bij Koppeling met cel een verwijzing naar de cel Berekeningen!$C$2 komt.
  5. klik rechts op het rondje en kies Kopiëren
  6. toets 8 keer Ctrl-V (plakken) in
  7. plaats het onderste rondje op de juiste plaats
  8. selecteer alle rondjes door er op te klikken met Ctrl ingedrukt
  9. kies in de menutab Hulpmiddelen voor tekenen/Opmaak in het blok Rangschikken de optie Uitlijnen en kies daarna Links uitlijnen; daarna nogmaals Uitlijnen met de optie Verticaal verdelen.

Wanneer je nu een rondje aanklikt zal het overeenkomende nummer in cel C2 van het tabblad Berekeningen worden geplaatst. Het detail-overzicht en de bijbehorende grafiek(en) passen zich dan automatisch aan.

NB aan de cellen C3:E11 is een voorwaardelijke opmaak gekoppeld die er voor zorgt dat de achtergrond van de gekozen aanbieding wordt gekleurd:

Interactief details tonen (2)

Een andere methode is in het tabblad Ovz2 van het Voorbeeldbestand geïmplementeerd.

Als je daar op één van de cellen C3:E11 klikt dan start een VBA-routine die er voor zorgt dat cel C2 (met de naam aanbNr) van het tabblad Berekeningen wordt aangepast.

  1. klik met de rechter muisknop op de naam van één van de tabbladen en kies de optie Programmacode weergeven
  2. klik in het Project-overzicht aan de linkerkant van het scherm dubbel op het blad Ovz2
  3. plaats in het codescherm de volgende programma-regels:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim topRij As Integer
         If Not (Application.Intersect(Target, Range(“lstOvz2”).Cells) Is Nothing) Then
              topRij = Range(“lstOvz2”).Cells(1, 1).Row
              [aanbNr] = Target.Row() – topRij + 1
         End If
    End Sub
    Kortweg: als in dit werkblad de selectie wordt veranderd (door bijvoorbeeld het klikken met de muis of het gebruik van de cursortoetsen) dan zal de VBA-routine kijken of er een overlap is tussen de cel die is geselecteerd (de Target) en de cellen van lstOvz2 (dus C3:E11). Is dat zo dan wordt het rijnummer van de eerste cel van deze lijst in de variabele topRij geplaatst. Door deze topRij af te trekken van het rijnummer van de Target-cel weten we welke aanbieding aangeklikt is (nog wel even corrigeren met +1).

Trechtergrafieken

Excel,
Voorbeeldbestand: TrechterDownload

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

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

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

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

Basis-gegevens

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

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

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

Draaitabel

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

Analyse

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

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

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

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

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

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

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

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

Laten we de grafieken maken:

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

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

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

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

Ramingen

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

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

Pasen

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

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

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

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

Voorwaardelijke opmaak met sterren

Excel, ,
Voorbeeldbestand: Sterren.xlsxDownload

In het vorige artikel (Heatmap en voorwaardelijke opmaak) zijn diverse (standaard-)opties van voorwaardelijke opmaak langs gekomen. Ik realiseerde me later dat ik iets te weinig over pictogrammen heb verteld.

Dus deze keer nog maar eens: voorwaardelijke opmaak.

Pictogrammen/classificaties

In het Voorbeeldbestand in het tabblad Classif staat een overzicht van de productie per maand.
Voor demonstratie-doeleinden worden de (meeste) maanden gevuld met willekeurige bedragen tussen 0 en een op te geven bovengrens (druk op F9 voor andere waarden).

Daaronder staan overzichten met dezelfde waarden, maar die we verschillende soorten voorwaardelijke opmaak hebben gegeven.

De eerste reeks (rij 8) heeft als volgt een opmaak gekregen:

  1. eerst zijn alle cellen geselecteerd, die een voorwaardelijke opmaak moeten krijgen; hier dus C8:N8
  2. daarna is in het blok Stijlen de optie Voorwaardelijke opmaak gekozen
  3. en dan bij Pictogramseries/Classificaties de optie met de 5 staafdiagrammen

Maar hoe komt de opmaak nu precies tot stand:

  1. selecteer één van de cellen met opmaak en kies dan opnieuw Voorwaardelijke opmaak
  2. dan de opties Regels beheren en Regel bewerken

Dus het eerste pictogram wordt getoond als de waarde in de cel groter of gelijk is aan 80% et cetera.
Maar waar is dit nu een percentage van? Dat wordt nergens duidelijk.

Let op wanneer je opmaak gebruikt op basis van procenten (zoals hierboven) dan bepaalt Excel op de achtergrond het verschil tussen de laagste en de hoogste waarde en zal daar de procent-berekening op los laten.
In het voorbeeld van tabblad Classif heeft de eerste maand de waarde 0 gekregen en de laatste maand de bovengrens uit cel D2. Op deze manier is de werking van de voorwaardelijke opmaak goed te bestuderen (druk op F9).

NB in het tabblad Classif2 van het Voorbeeldbestand kun je zien wat er met de opmaak gebeurt als de ondergrens en bovengrens niet zijn “vastgepind”.

In de regels 11 en 14 staan 2 andere voorbeelden van voorwaardelijke opmaak. Waar de opmaak hiervoor 5 verschillende vormen kan aannemen (geen, 1, 2, 3 of 4 balkjes gekleurd) hebben deze voorbeelden 4 respectievelijk 3 opties.
Een ster is helemaal, half of niet ingekleurd.

NB Alle voorbeelden hiervoor zijn gebaseerd op een serie gegevens (in dit geval iedere keer een rij) waarbij de voorwaardelijke opmaak aangeeft hoe de waarde van een cel relatief ten opzichte van de serie scoort.
Maar soms wil je op basis van een waarde in één cel een opmaak weergeven; bijvoorbeeld op basis van een score tussen 0 en 5 een aantal sterren laten zien.

Aantal sterren obv een score

De meest simpele vorm staat hiernaast (zie het tabblad 5Ster in het Voorbeeldbestand).

In kolom C wordt een getal tussen 0 en 5 gecreëerd met 1 decimaal (door eerst een heel getal tussen 0 en 50 te genereren en dat te delen door 10).

Met behulp van de functie Herhaling wordt in kolom D een overeenkomend aantal sterretjes geplaatst. In cel D3 staat de formule =HERHALING(“*”;C3)

Maar dat moet natuurlijk mooier kunnen.
Hiernaast worden maximaal 5 sterren ingekleurd op basis van een score. Wanneer het decimaal gedeelte groter of gelijk is aan 0,5 dan wordt er ook een halve ster gekleurd.

Hoe is dit overzicht opgebouwd? In iedere regel wordt de eerste ster ingekleurd als de score groter of gelijk is aan 1, de tweede ster als de score groter of gelijk is aan 2 enzovoort.
Maar de voorwaarde voor de 2e ster kunnen we ook anders formuleren: als de score minus 1 groter of gelijk is aan 1 dan moet die ingekleurd worden. Iets vergelijkbaars geldt voor de volgende kolommen.
Op deze tweede manier hebben we er voor gezorgd dat in iedere kolom de voorwaarde hetzelfde is.

Dus op basis van de score vullen we de kolommen daarachter met 2 verschillende formules: in de eerste kolom wordt de score overgenomen, de waardes in de andere kolommen zijn gelijk aan de vorige kolom minus 1 (ziet het tabblad 5Ster).

De cellen waar een ster moet komen krijgen dan allemaal de bovenstaande opmaak. Dus bij Type staat nu niet Procent maar wordt er aan een hard Getal gerefereerd.
Als de waarde in een cel groter of gelijk is aan 1 dan wordt die ster helemaal ingekleurd, is de waarde groter of gelijk aan 0,5 dan half en anders blijft de ster leeg.

Aantal sterren obv een score met schaling

Bovenstaand voorbeeld werkt prima als scores tussen 0 en 5 liggen. In de praktijk zullen scores vaak een andere range bestrijken.
In het tabblad Geschaald van het Voorbeeldbestand staan in kolom C bedragen tussen 0 en 100.
Voordat we daar op bovenstaande manier een aantal sterren (maximaal 5) aan kunnen koppelen moeten die bedragen eerst geschaald worden: =5*$C5/$C$2

Als dat gebeurd is kunnen er weer sterren uitgedeeld worden. Om de resultaten makkelijk te kunnen beoordelen is in het voorbeeld een hulpkolom (#Ster) toegevoegd waarin het aantal toe te kennen sterren wordt berekend:
=GEHEEL(D5)+ALS(D5-GEHEEL(D5)>=0,5;0,5;0)

Het aantal toe te kennen hele sterren wordt bepaald met behulp van de functie GEHEEL (de decimalen worden afgehakt); daarna wordt er gekeken of er nog een halve ster bij moet door te kijken of het decimale gedeelte groter of gelijk is aan 0,5.

NB1 voor de liefhebbers, bovenstaande formule kan compacter:
=GEHEEL(D5)+0,5*(D5-GEHEEL(D5)>=0,5)
Hierbij maken we gebruik van de interne werking van Excel: iets wat WAAR is, is gelijk aan 1 en iets wat ONWAAR is, is gelijk aan 0.

NB2 uiteraard zijn de hulpkolommen Geschaald en #Ster niet nodig; zie het tabblad Gesch2 in het Voorbeeldbestand.

NB3 in de vorige voorbeelden is het aantal sterren gebaseerd op een te behalen bovengrens (staat op 100 ingesteld maar is te wijzigen). Soms kan het nodig zijn om het aantal toe te kennen sterren relatief ten opzichte van de maximale score te bepalen; zie het tabblad Gesch3 in het Voorbeeldbestand.

Zoals u kunt zien kunt u ook desgewenst, via Regels beheren, pictogrammen weglaten:

NB4 in het tabblad Gesch4 is het overzicht iets flexibeler ingericht. Voegt u bijvoorbeeld op de plaats van kolom F een nieuwe kolom toe dan zal het systeem direct de juiste berekening uitvoeren (wel in de nieuwe kolom de formules uit de kolom daarachter kopiëren!).

Elf-proef

Excel, VBA (Visual Basic for Applications), , , ,

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.

Aantallen turven

Access, Excel, , , , , , , , , , , ,

De afgelopen weken kreeg ik in diverse situaties vragen over het tellen van aantallen.

Wat simpel lijkt, wil in geautomatiseerde systemen nog wel eens ingewikkelder uitpakken.
Of het nu het tellen is van het aantal regels in een Excel-bestand, het aantal lege velden in een Excel-kolom, het aantal records in een Access-tabel of het aantal niet-lege namen in een Access-record: het blijft goed opletten wat en hoe er geteld moet worden.

ToepenAantallen turven:
toen ik dit intikte, gingen mijn gedachten weer terug naar de vele gezellige avonden, die we lang geleden met een groepje hebben doorgebracht met het kaartspel Toepen. Bij dit spel worden de strafpunten op een bier- (of sinas-?)viltje geturfd door het zetten van streepjes.

Voor de liefhebbers volgt hieronder, na wat uitleg over het turven van aantallen in Excel en Access, een “geautomatiseerd bierviltje”.
Ik heb daarbij ook een weinig bekende manier van kopiëren gebruikt.

Excel

AantallenIn het Voorbeeldbestand staat in het tabblad Aantal vanaf cel  D15 een overzicht van de omzet per maand en regio van een kleine witgoedhandel (ze verkopen alleen stofzuigers, strijkijzers en broodroosters).

Uiteraard willen we de totale omzet weten (dat lossen we simpel op via een Som-formule);  maar vandaag zijn we meer geïnteresseerd in Aantallen.

AantallenExcel kent diverse standaard-functies om aantallen te berekenen.
De meest simpele (en meest gebruikte) functie is Aantal. Maar als we die toepassen op het bereik D15:D158 (de maanden) dan krijgen we als resultaat 0; idem voor Regio en Product. Pas het turven van aantallen in de kolom Bedrag levert een aantal groter dan nul op, namelijk 142. De functie Aantal telt alleen maar de inhoud van numerieke cellen!

NB een datum is voor Excel ook een getal; dus de functie Aantal turft ook datums (zie ook de cellen L18:M21 in het Voorbeeldbestand).

De functie Aantalarg voldoet al beter aan de verwachtingen (zie het overzicht hierboven): deze functie telt hoeveel cellen er gevuld zijn, onafhankelijk van de inhoud.

Mbv de functie Aantal.lege.cellen (denk aan de puntjes!) kunnen we analyseren of en hoeveel lege cellen er in een cellenbereik voorkomen.

Voor de volledigheid heb ik ook nog enkele voorbeelden van de functies Aantal.als en Aantallen.als opgenomen; zie voor nadere uitleg het artikel Tellen met voorwaarden.

Turf-alternatief

Om snel te kunnen achterhalen in welke regel er gegevens ontbreken, heb ik achter iedere regel, in kolom H, mbv de functie Aantalarg geteld hoeveel velden er gevuld zijn.
AantallenDoor cellen, die NIET 4 opleveren, via Voorwaardelijke opmaak te kleuren zien we direct in  welke regels het fout gaat
(in één van de komende artikelen zal ik op Voorwaardelijke opmaak terugkomen).

Voor de liefhebbers nog een andere manier van tellen waarbij gebruik wordt gemaakt van zogenaamde Array- of Matrix-formules.
Wanneer we in Excel in een cel intypen =D15<>”” (we kijken dus of cel D15 NIET leeg is) dan levert dit de waarde WAAR op (als die cel gevuld is tenminste!). Maar intern vertaalt Excel dit naar een 1 (en ONWAAR naar een 0; het blijft een computerprogramma!). Daar kunnen we gebruik van maken:
typ in een cel =1*(D15<>””) en we krijgen als resultaat 1.
NB denk aan de haakjes rond de voorwaarde.

We zouden in cel I15 kunnen intikken =1*(D15<>””)+1*(E15<>””)+1*(F15<>””)+1*(G15<>””) om te achterhalen hoeveel cellen er niet leeg zijn, maar dat kan makkelijker:

  1. tik in cel I15 in =Som(1*(
  2. selecteer de cellen D15 t/m G15
  3. en tik dan in (allemaal in cel I15) <>””))
  4. niet op Enter drukken, maar Ctrl-Shift-Enter

Excel zal nu voor alle cellen in het bereik D15:G15 beoordelen of die leeg is; vermenigvuldigd met 1 levert dit een serie 0’en en 1’en op, die door de functie Som bij elkaar worden opgeteld.

Door de combinatie Ctrl-Shift-Enter wordt er een Array- of Matrix-formule ingevoerd; in de formulebalk wordt dit weergegeven door {} rond de formule.

NB alleen op bovenstaande manier kun je een array-formule invoeren; het plaatsen van accolades rond een formule werkt niet!

Kopieer de array-formule naar beneden (via de vulgreep rechtsonder in cel I15 natuurlijk; zie het artikel Doorvoeren en sorteren).

Access

Binnen dit programma moeten we het doen met veel minder (functie-)mogelijkheden. Voor het tellen van het aantal records in een tabel kunnen we een Totaal-query maken en dan per veld in de regel Totaal: aangeven dat we het Aantal willen zien.
Aantal AccessHet resultaat hiervan komt overeen met het gebruik van de functie Aantalarg in Excel; dus het aantal niet-lege velden wordt geturfd voor ieder type veld.

Willen we het aantal niet-lege velden binnen een record weten (net als de berekening in de cellen H15 en I15 hiervoor in Excel) dan zullen we zelf een formule moeten intypen in de query.
Aantal Access

Optie 1:
Gevuld: IIf([Maand]<>””;1;0) + IIf([Regio]<>””;1;0) + IIf([Product]<>””;1;0) + IIf([Bedrag]<>0;1;0)

De eerste als-voorwaarde (in Access Iif) levert een 1 als Maand niet leeg is, anders 0; de tweede voorwaarde hetzelfde voor Regio enz. Door de 4 resultaten van de Iif’s op te tellen zien we per record hoeveel velden er gevuld zijn.

NB Bedrag is in Access een numeriek veld; de inhoud kunnen we dan niet vergelijken met een lege tekst, dus we controleren of de waarde niet gelijk is aan 0.

Optie 2:
Gevuld2: IIf(Not IsNull([Maand]);1;0) + IIf(Not IsNull([Regio]);1;0) + IIf(Not IsNull([Product]);1;0) + IIf(Not IsNull([Bedrag]);1;0)

Vergelijkbaar met Optie 1, maar we controleren mbv een Access-functie IsNull of een veld leeg is.

Optie 3:
Gevuld3: -(Not IsNull([Maand])) – (Not IsNull([Regio])) – (Not IsNull([Product])) – (Not IsNull([Bedrag]))

Dit is iets korter dan optie 2, waarbij we gebruik maken van de eigenschap dat de waarde WAAR in Access door -1 wordt voorgesteld (en ONWAAR door 0; dit laatste is WEL hetzelfde als in Excel!?).

NB denk aan de juiste plaats en het aantal van de haakjes.

Toepen

Zoals beloofd een “Excel-bierviltje”; zie het Voorbeeldbestand.
De (straf)puntentelling bij het kaartspel Toepen gebeurt van oudsher door het zetten van streepjes, waarbij een vijftal door een dwarsstreep wordt aangegeven. Heb je 15 strafpunten, dan lig je er uit; 14 punten is ook een bijzondere situatie: de tegenstanders krijgen bij verlies 2 strafpunten (of ze passen voor 1 punt). In het Brabants wordt dit “Pulleke” genoemd, op het “scorebord” aangeduid door een P.

In het tabblad ToepBasis worden alle benodigdheden voor het scorebord klaar gezet:

  1. Toepenin het blok B2:C4 staan de gebruikte symbolen, waarbij de cellen C2 t/m C4 een overeenkomende naam hebben gekregen (zie het Naamvak, linksboven)
  2. in de cellen B8:D12 staan de spelersnamen en hun scores: de stand binnen het lopende ‘potje’ en het aantal gewonnen ‘potjes’.
  3. ToepenF6:H12 is het scorebord/Excel-bierviltje.
    In kolom F worden de deelnemers gecombineerd met hun resultaten door cellen en teksten met behulp van het &-teken te koppelen; in cel F8 staat bijvoorbeeld =B8&”   (“&C8&”/”&D8&”)”
  4. kolom G is de meest interessante: hier wordt de score uit kolom C (de cellen C8:C12 hebben samen de naam Score gekregen) omgezet in de juiste streepjes:
    =ALS(Score>=15;Vijftal&Vijftal&Vijftal;ALS(Score=14;Vijftal&Vijftal&Paal;HERHALING(Vijftal;INTEGER(Score/5))&HERHALING(Streepje;REST(Score;5))))
    * als de Score groter of gelijk aan 15 is, dan wordt 3x de tekst uit de cel met de naam Vijftal geplaatst
    * bij een Score van 14, twee vijftallen en het teken voor het “Pulleke”
    * anders via de functie HERHALING zoveel vijftallen als we krijgen wanneer we Score door 5 delen (INTEGER betekent geheel getal) en zoveel streepjes als de REST is bij het delen van de Score door 5.
  5. kolom H bevat een simpelere vorm van de formule uit kolom G
  6. nog wat Voorwaardelijke opmaak toevoegen (‘kapot’: rode achtergrond, ‘Pulleke’: oranje achtergrond, meer dan 9 punten: oranje letters) en het Scorebord is klaar!

LET OP: de naam Score bevat de cellen C8 t/m C12. Wanneer we in kolommen rechts hiervan in een formule verwijzen naar Score, dan neemt Excel de met de rij overeenkomende waarde uit deze reeks. Dus in cel G8 wijst Score naar de waarde in cel C8, in G9 naar cel C9 etc.

ToepenHet “Excel-bierviltje” vinden we terug op het tabblad ToepScore.
Om het turven nog makkelijker te maken bevat het tabblad diverse Besturingselementen, een 10-tal zogenaamde Kringvelden Kringveld. Hiermee is het mogelijk om de scores snel te verhogen en te verlagen. De Kringvelden zijn gekoppeld aan de diverse scores in het vorige tabblad; klik met de rechtermuisknop op een Kringveld en bekijk de instellingen onder de optie Besturingselement opmaken …
Kringvelden (en andere besturingselementen) vinden we onder de menu-tab Ontwikkelaars.

Het scoreformulier zelf is GEEN serie cellen maar een Excel-afbeelding; wanneer je er op klikt, zie je in de hoeken en in het midden van de randen plaatsen waar je met de cursor de afbeelding kunt vergroten en verkleinen.
En niet zo maar een statisch plaatje; nee, een zogenaamde Gekoppelde afbeelding. De inhoud van de afbeelding verandert met de inhoud van de cellen, waaraan deze gekoppeld is! Mooier kunnen we het niet maken….

Hoe maken we een gekoppelde afbeelding?

  1. selecteer de cellen, die in de afbeelding moeten worden weergegeven (voor ons scorebord de cellen F6:H12 in het tabblad ToepBasis)
  2. klik rechts in de selectie en kies Kopiëren
  3. Toepenga naar de cel waar de afbeelding moet komen, klik rechts en kies de optie Plakken speciaal ….
    In het submenu kies je onder Andere plakopties de 4e mogelijkheid, Gekoppelde afbeelding
  4. pas via de grepen aan de zijkant van de afbeelding de grootte aan en/of verplaats het plaatje naar de gewenste locatie.

NB Gekoppelde afbeeldingen kunnen een belangrijke rol spelen in rapportages. Wanneer je daar meerdere overzichten onder elkaar wilt weergeven, zal het meestal zo zijn, dat de gewenste kolom-breedtes van die overzichten niet met elkaar overeenkomen; maar Excel kan standaard maar één breedte weergeven.
Zet  de overzichten naast elkaar op een tabblad (of nog beter op verschillende tabbladen) en maak van deze overzichten Gekoppelde afbeeldingen; deze kun je dan onder elkaar zetten, zonder dat de kolombreedtes elkaar nog beïnvloeden.

LET OP Gekoppelde afbeeldingen vergen veel van Excel en van je PC! Sla je werk regelmatig op en gebruik niet teveel dynamische afbeeldingen in één werkmap.