Tagarchief: SOMPRODUCT

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: