Tagarchief: SOMPRODUCT

Wie, wat, waar, hoe?

14 december 2022ExcelAantal.als, CSE, Evaluatie, F9, SOMPRODUCTGijs Verbruggen

Voorbeeldbestand: VrWoorden.xlsxDownloaden

Ik kwam pas een (oud) artikel van Chandoo tegen, waarin hij aangeeft dat hij af en toe door de bomen het bos niet meer ziet: zijn artikelen en video’s genereren zoveel reacties, dat hij niet snel meer ziet of er vragen bijzitten die beantwoord moeten worden.

Zijn oplossing gaan we in dit artikel van G-Info eens onder de loep nemen.

Probleem

Chandoo wilde dus snel zijn reacties filteren, zodat hij alleen vragen overhield. Door alleen te selecteren op het feit of een zin een vraagteken bevat, levert meestal niet het gewenste resultaat op (zoals uit het simpele voorbeeld hiernaast mag blijken).

Zijn oplossing was om om te onderzoeken of bepaalde woorden voorkomen in de reacties.
Dat bleek nog niet voldoende: ook bepaalde combinaties van woorden kunnen aangeven dat het een vraag betreft:

Oplossing

Om te controleren of een zin een vraag bevat, moeten we analyseren of één of meerdere ‘Vraag-woorden‘ in de zin voorkomen.

In het Voorbeeldbestand zijn de woorden die aangeven of we te maken hebben met een vraag opgenomen in een Excel-tabel met de naam tblVrWoorden (zie kolom B in het tabblad Vraag). De tabel-kolom met woorden heeft de naam Vraag-woorden.

NB voor uitleg over de voordelen van en toelichting op het gebruik van Excel-tabellen zie de artikelen Kunst en Excel en Tabellen (deel 2).

In cel E3 staat de formule =AANTAL.ALS(D3;”*”&tblVrWoorden[Vraag-woorden]&”*”)

Ofwel: bepaal het aantal keren dat één van de mogelijke Vraag-woorden voorkomt in D3.

Controleer de werking van de formule in E3 door middel van de menu-optie Formule evalueren in de menutab Formules.
Klik op Evalueren en Excel zal de stap die onderstreept is uitvoeren:

Blijf op Evalueren klikken om alle berekeningstappen na te lopen. Doe je dit ook voor de cellen E4, E5 etcetera dan is duidelijk te zien dat Excel telkens maar één vraagwoord onder de loep neemt in plaats van de hele reeks.

NB de functie Aantal.Als herkent het gebruik van wildcards; door vóór en achter de vraagwoorden een * te plaatsen geven we aan, dat er nog iets vóór of achter het woord (of woordcombinatie) mag staan ofwel dat zo’n woord willekeurig ergens in de tekst kan voorkomen.

Maar doe nu eens het volgende:

selecteer cel E6
klik in de formulebalk achter de formule
druk op het toetsenbord op de functietoets F9 (Berekenen)
de formule verandert in: ={0;0;0;1;0;0;1;0;0;0;0}
druk NIET op Enter maar op Esc

NB je kunt ook een gedeelte van een formule laten berekenen: selecteer bijvoorbeeld in de formulebalk alleen het gedeelte achter de punt-komma en vóór het haakje-sluiten en druk dan op F9.

Nu is te zien, dat Excel toch slimmer is dan we dachten! Hij heeft wel alle Vraag-woorden vergeleken met de inhoud van cel D6 en geconstateerd dat er 2x sprake is van een vraag (de 4^e en 7^e optie). De functie Aantal.Als levert een array van resultaten op en daar kunnen we gebruik van maken.

In kolom F bepalen we de Som van de waardes uit de array, die door Aantal.Als is gegenereerd.

LET OP we moeten Excel wel ‘influisteren’ dat we te maken hebben met een array-berekening: sluit de formule NIET af met Enter maar met Ctrl-Shift-Enter, de zogenaamde CSE-methode.

Een alternatieve oplossing, waarbij de CSE-methode niet nodig is, is het gebruik van de functie SomProduct (zie kolom G). Lees ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT.

Ander voorbeeld

Een garage heeft een goederenoverzicht met codes en omschrijvingen (zie het tabblad Onderdelen van het Voorbeeldbestand).
Om de efficiency en de consistentie te waarborgen wordt afgesproken dat aan de servicecorner deze codes ook gebruikt moeten worden bij het aanmaken van de werkbonnen.

Om verschil te maken met bijvoorbeeld klantnummers wordt vóór een goederencode een # geplaatst.

In kolom C kunnen we met een vergelijkbare formule als hiervoor beoordelen of er sprake is van een onderdeel (let op het # achter het eerste sterretje).

Als we de garage dan toch automatiseren: in kolom D zoeken we, als er sprake is van een Onderdeel, het betreffende nummer op:
=ALS([@[Onderdeel?]]=”Ja”;WAARDE(DEEL([@Opmerking];VIND.ALLES(“#”;[@Opmerking])+1;7));””)
en in kolom E de daarbij behorende omschrijving:
=ALS([@[Onderdeel?]]=”Ja”;INDEX(tblOnderdelen[Omschrijving];
VERGELIJKEN([@[Welk?]];tblOnderdelen[Goederencode];0));””)

Nog een voorbeeld

Een school werkt niet met cijfers voor proefwerken met met lettercodes (zie het tabblad Proefwerk van het Voorbeeldbestand).

Ook ligt in een tabelletje vast welke codes overeenkomen met een voldoende.

Nu wordt per leerling in kolom P de tabel met voldoende-codes vergeleken met de 10 proefwerkkolommen en wordt het aantal voldoendes bepaald. Nog wat Voorwaardelijke opmaak en we zien snel hoe de diverse leerlingen er voor staan.

LET OP dit is weer een CSE-formule.

NB1 telt nv niet mee als voldoende (de betekenis is ineens niet voldoende 😉) dan verwijderen we de betreffende rij in de tabel tblVoldCd. Het resultaat in kolom P past zich direct aan.

NB2 om te zien wat de invloed is van de proefwerk-resultaten op kolom P staat vanaf rij 10 in het tabblad Proefwerk een overzicht waarbij de resultaten bij iedere druk op de F9-toets willekeurig veranderen.

Cadeaus

Decembermaand is de tijd van cadeaus. Voor de liefhebbers hier 2 links:

Chandoo: uitleg van diverse ‘interessante’ formules
How to Excel: een overzicht van diverse Excel-websites

Grafieken verduidelijken en interactief maken

8 oktober 2020ExcelIndex, Indirect, SOMMEN.ALS, SOMPRODUCT, VergelijkenGijs Verbruggen

Voorbeeldbestand: GebGewicht.xlsx Download

“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 (P₅, P₁₀ 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:

uiteraard een legenda
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
om duidelijker te maken welke lijn het gemiddelde is, heeft deze lijn markeringen gekregen
bij de horizontale as is aangegeven wat de getallen voorstellen
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:

teken één keuzerondje
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)
tik Ctrl-C en ergens anders Ctrl-V, verander de tekst in v
voeg eventueel een groepsvak toe, die je om deze twee keuzerondjes tekent
doe hetzelfde voor het soort geborene; maak daar een koppeling met een andere cel (in het voorbeeld N3)
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:

teken deze op de plaats waar je hem wilt hebben (kun je later natuurlijk nog aanpassen)
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:

zoek eerst de rij op van de geboorteweek (in cel N10 met de functie VERGELIJKEN)

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.
in cel N12 bepalen we de percentiel-waarde die bij die kolom hoort
ook het bijbehorende gewicht wordt dan opgezocht in cel N13
in kolom O worden de vorige 2 stappen uitgevoerd voor een hogere percentielkolom
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:

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.
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.

DB-functies

30 september 2017ExcelDB, Draaitabel, SOMPRODUCT, TabelGijs Verbruggen

Voorbeeldbestand: DB.xlsx Download

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.

Elf-proef

19 februari 2016Excel, VBA (Visual Basic for Applications)Gegevensvalidatie, Herhaling, Rest, SOMPRODUCT, voorwaardelijke opmaakGijs Verbruggen

BSN: 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.
Van 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.

Deze 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:

VBA-voorbeeld 1

Om al die hulpkolommen te vermijden is in kolom B gebruik gemaakt van een eigen functie ElfProef1, die dezelfde resultaten oplevert:
In 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:

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).
‘groene’ teksten is voor VBA commentaar; hebben verder geen functie. Invoeren door een enkele aanhalingsteken in te tikken.
met Dim geven we aan welke variabelen we in het programma/de functie gaan gebruiken
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)
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
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.
we gaan er van uit, dat het bankrekeningnummer voldoet, dus geven standaard als output voor de functie ok mee
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).
We 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:

de input-parameter getal is gedeclareerd als String (tekst)
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.
in de functie vertalen we het getal naar een tekst van 10 tekens (vergelijkbaar met wat we in Excel hebben gedaan)
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:

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.
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

In 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.

SOMPRODUCT: meer dan SOM en PRODUCT

29 januari 2016ExcelCSE, ISGETAL, Som, SOMPRODUCT, VERT.ZOEKEN, VIND.ALLESGijs Verbruggen

SOMPRODUCT: éé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

Laten we als simpel voorbeeld een bedrijfje nemen met 4 producten met verschillende prijzen. Tijdens 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:

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.
De omzet komt in kolom H: in iedere regel wordt de cel in kolom F vermenigvuldigd met de cel in G.
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:

selecteer cel H29
kies in de menutab Formules in het blok Formules controleren de optie Formule evalueren
er verschijnt een nieuw scherm:
Klik 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.

Met 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. Waar 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: ……
etc

SOMPRODUCT voor gevorderden 2

Maar we hoeven het natuurlijk niet bij één filter/selectie te houden.
In 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:

In 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.