- Inleiding
- Matrixformules met een enkele waarde
- Matrixformules met meerdere waarden
- Voor- en nadelen
- EN + OF operatoren
- Gebruik van constanten
- Voorbeelden
- Conclusie
- Vragen / suggesties
1. Inleiding
Voor veel gebruikers van Excel zijn matrixformules onbekend. Dat is jammer, want matrixformules vormen een zeer krachtig gereedschap binnen Excel. Deze onbekendheid komt onder andere doordat in de Excel-help niet of nauwelijks aandacht aan matrixformules wordt besteed en het daardoor lastig is om überhaupt maar van het bestaan van deze functionaliteit op de hoogte te zijn. In deze tutorial wordt uiteen gezet hoe matrixformules werken en wat er mee gedaan kan worden. Er wordt begonnen met de uitleg van de werking aan de hand van eenvoudige voorbeelden, maar aan het eind komen ook complexere voorbeelden aan de orde. In nieuwere Excel-versies (met name in Excel voor Office 365) zijn voor sommige voorbeelden alternatieve kortere formules beschikbaar, maar dat wordt hier buiten beschouwing gelaten. De gebruikte voorbeelden werken in alle gangbare Excel-versies.
Matrixformules verwerken een reeks van gegevens in plaats van gegevens uit een enkele cel. Zo’n reeks wordt ook wel matrix of array genoemd. Matrixformules moeten afgesloten worden met Ctrl+Shift+Enter. Daarna verschijnen er automatisch accolades om deze formules ten teken dat het om een matrixformule gaat. Dus deze accolades moeten niet ingetypt worden. In deze workshop worden de accolades wél weergeven om daarmee duidelijk te maken dat het om een matrixformule gaat. Hoewel matrixformules afwijkend moeten worden afgesloten, kunnen ze wel normaal gekopieerd worden naar andere cellen. Vanwege het afsluiten met Ctrl+Shift+Enter worden matrixformules ook wel CSE-formules genoemd.
In Excel voor Office 365 hoeven matrixformules trouwens meestal niet afgesloten te worden met Ctrl+Shift+Enter, maar het mag wel. Als in Excel 365 een matrixformule niet afgesloten wordt met Ctrl+Shift+Enter dan 'denkt' Excel in sommige gevallen dat het een matrixformule betreft over een heel bereik (meerdere waarden), terwijl het de bedoeling is om één waarde terug te geven. In dat soort gevallen kan met Ctrl+Shift+Enter afgedwongen worden dat de functie één waarde teruggeeft.
Matrixformules bestaan in 2 varianten, namelijk formules die één waarde teruggeven en formules die een matrix van waarden genereren.
2. Matrixformules met een enkele waarde
De werking van een matrixformule kan het best worden uitgelegd aan de hand van een eenvoudig voorbeeld. In onderstaand overzicht zijn voor een aantal verkopers verkoopcijfers weergegeven.
Stel nu dat je van verkoper 'Jan' de hoogst verkochte prijs/stuk zou willen weten. In Excel 2019 of Excel voor Office 365 kan hiervoor de formule MAX.ALS.VOORWAARDEN gebuikt worden, maar in oudere versies kan dat met een matrixformule eenvoudig uitgerekend worden. Dat kan bijvoorbeeld met de volgende formule:
{=MAX(ALS(A2:A9="jan";D2:D9))}Deze formule moet uiteraard ingegeven worden met Ctrl+Shift+Enter. De werking van deze formule is als volgt. Eerst wordt gekeken in A2:A9 waar de waarde 'jan' staat. Het resultaat van deze vergelijking is: WAAR,onwaar,onwaar,WAAR,onwaar,WAAR,onwaar,onwaar.
Vervolgens wordt het maximum van D2:D9 uitgerekend, maar alleen voor die gevallen waar in kolom A2:A9 de uitkomst WAAR is. Dus het maximum wordt bepaald van de getallen 3,4,7 zodat het resultaat van de formule 7 is. Bovenstaande formule is goed en werkt, maar kan nog iets korter:
{=MAX((A2:A9="jan")*D2:D9)}Deze laatste kortere schrijfwijze werkt overigens alleen goed bij maxima groter of gelijk aan nul. Vooral bij deze laatste formule is te zien dat de syntax van een matrixformule behoorlijk afwijkt van de syntax van een gewone formule. Als je bovenstaande formules ingeeft als gewone formule (dus zonder Ctrl+Shift+Enter), dan volgt er een foutmelding. Ook is het van groot belang dat de matrices in de formule precies dezelfde omvang hebben. Het gaat in dit geval om A2:A9 en D2:D9.
Stel nu dat je de hoogst verkochte prijs/eenheid wilt berekenen voor verkoper 'jan' voor artikelgroep A. Dan kan de formule als volgt uitgebreid worden:
{=MAX(ALS((A2:A9="jan")*(B2:B9="A");D2:D9))}Er wordt dus een extra vergelijkingscriterium aan de formule toegevoegd. De uitkomst van deze formule wordt dan 4.
Als het maximum altijd groter of gelijk dan nul is dan kan de formule nog ingekort worden tot:
{=MAX((A2:A9="jan")*(B2:B9="A")*D2:D9)}Als vervolgens de omzet van bovenstaande tabel uitgerekend zou moeten worden, dan zou je bijvoorbeeld eerst per regel de prijs/stuk maal het aantal moeten uitrekenen, waarna tenslotte met de SOM-functie de totale omzet kan worden bepaald. Maar met een matrixformule kan dat in één keer heel eenvoudig uitgerekend worden. Zet bijvoorbeeld in cel C10 de volgende matrixformule:
{=SOM(C2:C9*D2:D9)}De uitkomst van deze formule is 260. In feite wordt met deze formule de volgende berekening uitgevoerd:
=C2*D2 + C3*D3 + C4*D4 + C5*D5 + C6*D6 + C7*D7 + C8*D8 + C9*D9
Bij grote bereiken is dit met gewone formules vrijwel niet meer te doen en ook de kans op fouten wordt dan erg groot. Dit is dus een voorbeeld van een erg korte matrixformule waarmee heel snel een erg uitgebreide berekening kan worden uitgevoerd.
3. Matrixformules met meerdere waarden
In voorgaande paragraaf kwamen matrixformules aan de orde die één waarde teruggeven. Maar het is ook mogelijk om met een matrixformule in één keer heel veel waarden tegelijk te genereren. Voortbordurend op voorgaand voorbeeld zou je bijvoorbeeld in één keer de omzet per regel kunnen invoeren met één matrixformule. Selecteer hiervoor eerst het bereik E2:E9 en type vervolgens in de formulebalk de volgende matrixformule:
{=C2:C9*D2:D9}Na het afsluiten met Ctrl+Shift+Enter wordt in één keer het hele gekozen bereik gevuld met de omzet per regel (aantal x prijs/stuk).
Een ander voorbeeld gaat over een standaard Excel-functie die altijd als matrixformule moet worden ingegeven voor één gebied ineens. Dit betreft de functie TRANSPONEREN. In onderstaand voorbeeld moeten de gegevens die nu in horizontaal staan weergeven in cel D1:I1, verticaal weergeven worden.
Om dat te doen moet eerst het bereik waar de gegevens terecht moeten komen geselecteerd worden (in dit geval D3:D8). Daarna kan in de functiebalk ingevoerd worden:
{=TRANSPONEREN(D1:I1)}Ook deze formule dient weer afgesloten te worden met Ctrl+Shift+Enter. De gegevens uit D1:I1 verschijnen vervolgens in D3:D8.
Als nu geprobeerd wordt om bijvoorbeeld cel D5 te verwijderen dan verschijnt de volgende foutmelding:
Dit is een teken dat de formule als het ware één geheel vormt over D3:D8 en dat het dus niet mogelijk is om een deel hiervan te verwijderen. Ook dit is een voorbeeld van een matrixformule waarbij in één keer een reeks van gegevens wordt gegenereerd.
4. Voor- en nadelen
Zoals reeds aangegeven vormen matrixformules een krachtig gereedschap. De belangrijkste voor- en nadelen zijn:
voordelen matrixformules:
- er kunnen (complexe) zaken mee uitgerekend worden die met gewone formules niet uitgerekend kunnen worden
- maken vaak het gebruik van hulpkolommen, zoals die soms nodig zijn bij gewone formules, overbodig
- de formules kunnen veel korter zijn dan traditionele formules
- het is mogelijk om een groot bereik in één keer te vullen met gegevens door middel van het ingeven van slechts één matrixformule
nadelen matrixformules:
- de formules zijn vaak minder goed te begrijpen voor minder ervaren Excel-gebruikers
- de formules moeten altijd afgesloten worden met Ctrl+Shift+Enter. Zo niet, dan worden onjuiste waarden teruggegeven of foutmeldingen
- het gebruik van een grote hoeveelheid of complexe matrixformules kan ten koste gaan van de snelheid
- er is in Excel weinig tot geen documentatie aanwezig over het gebruik van matrixformules
5. EN + OF operatoren
Matrixformules kunnen gebruikt worden om gegevens te filteren op meerdere criteria. In een voorgaande paragraaf kwam de volgende formule aan de orde, waarmee de hoogst verkochte prijs/eenheid berekend kon worden voor verkoper 'jan' voor artikelgroep A
{=MAX(ALS((A2:A9="jan")*(B2:B9="A");D2:D9))}Het *-teken fungeert hierbij als de EN-operator. De verkoper moet 'jan' zijn EN de artikelgroep moet 'A' zijn. Maar als je bijvoorbeeld de maximum verkoopprijs wilt berekenen voor verkoper 'piet' OF 'klaas', dan moet daarvoor het +-teken gebruikt worden. Het +-teken fungeert dus als een soort OF-operator. De formule wordt dan:
{=MAX(ALS((A2:A9="piet")+(A2:A9="klaas");D2:D9))}Bij maxima die groter of gelijk aan nul zijn kan ook de volgende formule worden gebruikt:
{=MAX(((A2:A9="piet")+(A2:A9="klaas"))*D2:D9)}Let er hierbij wel op dat er een extra stel haken wordt geplaatst om de gehele OF-constructie. De uitkomst van deze functie is 9. EN- en OF-operatoren kunnen op allerlei manieren gecombineerd/genest worden. Bij OF-operatoren is het wél van belang dat er geen overlap is tussen de criteria, omdat er anders dubbeltellingen kunnen ontstaan. Dus bijvoorbeeld de formule {=SOM(((C2:C9>4)+(C2:C9<9))*(C2:C9))} geeft onjuiste uitkomsten. Hierbij worden alle waarden van C2:C9 worden gesommeerd (dat is correct want elke waarde is groter dan 4 of kleiner dan 9), maar de waarden tussen 4 en 9 worden dubbel geteld. Dus de uitkomst is geen 43 maar 68 (de getallen 5, 7, 8 en 5 worden dubbel geteld).
6. Gebruik van constanten
In matrixformules kunnen ook constanten gebruikt worden. Stel de som van de getallen 1 tot en met 5 tot de macht 2 moet uitgerekend worden. Dat kan natuurlijk met een gewone formule zoals:
=1^2+2^2+3^2+4^2+5^2Maar dit kan ook met een matrixformule, waarbij de getallen 1 tot en met 5 als een constante zijn ingevoerd:
{=SOM({1;2;3;4;5}^2)}Let er hierbij wel op dat de accolades die om de constanten staan wél ingetypt moeten worden. Dit in tegenstelling tot de accolades die om de gehele formule staan. Als je de som van getallen 1 tot en met 20 tot de macht 2 wilt bepalen, dan wordt het erg bewerkelijk om dit in zowel de normale formule als in bovenstaande matrixformule in te voeren. Een alternatieve compacte matrixformule is dan:
{=SOM(RIJ(1:20)^2)}De getallen 1 tot en met 20 worden hierbij gegenereerd door de functie RIJ(1:20). Dit komt overeen met de constanten:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}Leuk en aardig allemaal, maar wat is nu het praktische nut van zulke constanten in matrixformules? Om dat te laten zien volgt hierna een voorbeeld waarbij gebruik wordt gemaakt van constanten. Stel er is een camping waarbij per dag een bepaald tarief moet worden betaald en in de maanden juli, augustus en september een verhoogd tarief:
De volgende matrixformule kan gebruikt worden om uit te rekenen wat betaald moet worden op basis van bovenstaande gegevens:
{=SOM((1+B2-B1)*B3;ISGETAL(VERGELIJKEN(MAAND(RIJ(INDIRECT(B1&":"&B2)));{7;8;9};0))*(B4-B3))}In deze formule worden eerst alle dagen uitgerekend met het normale tarief ((1+B2-B1)*B3) en vervolgens worden de dagen in juli, augustus en september verhoogd met het verschil tussen het piektarief en daltarief. Om te bepalen of een dag in het piektarief valt wordt gebruik gemaakt van de functie: RIJ(INDIRECT(B1&":"&B2)). De data die in B1 en B2 staan worden omgezet naar rijnummers. Immers in Excel wordt een datum opgeslagen als getal. In bovenstaand voorbeeld zijn dat de rijnummers 43956 tot en met 44024. Van al deze rijnummers (wat in feite alle data zijn van B1 tot en met B2) wordt de maand bepaald. Als deze maand gelijk is aan 7,8 of 9 dan komt per dag hier een bedrag van € 2,50 bij (dat is verschil van piektarief en daltarief). De getallen 7,8, en 9 zijn als constanten ingevoerd in de formule en moeten voorzien worden van accolades. De uitkomst van deze formule wordt dan 720. Dat is 69 dagen x 10 euro + 12 dagen x 2,50 euro.
7. Voorbeelden
Hierna volgen een drietal andere voorbeelden waar matrixformules uitkomst kunnen bieden.
7.1. Competitiestand bepalen
Excel wordt veel gebruikt voor het bijhouden van uitslagen en standen van competities. In onderstaand plaatje staat een voorbeeld van een competitie.
Stel van bovenstaande competitie moet een tussenstand worden weergegeven. In dit geval kan dat met normale Excel-formules gedaan worden. De punten kunnen eenvoudig van hoog naar laag weergegeven worden in D2:D7 met de formule:
=GROOTSTE(B$2:B$7;RIJ(A1))en het bijbehorende team kan opgezocht worden met:
=INDEX(A$2:A$7;VERGELIJKEN(D2;B$2:B$7;0))Maar wat nu als 2 teams hetzelfde aantal punten hebben? Dan gaat bovenstaande formule niet meer werken, omdat bij gelijke stand dan twee keer hetzelfde team weergegeven wordt. Hier kunnen matrixformules uitkomst bieden. Er zijn meerdere mogelijkheden om dat te doen. Twee mogelijkheden komen hier aan de orde. Uitgangspunt hierbij dat in D2:D7 de ranglijstposities zijn weergegeven met: =GROOTSTE(B$2:B$7;RIJ(A1)).
Een eerste formule om de juiste stand weer te geven is:
{=INDEX(A$2:A$7;KLEINSTE(ALS(ISNB(VERGELIJKEN(A$2:A$7;E$1:E1;0));ALS(B$2:B$7=D2;RIJ(A$2:A$7)-1));1))}Hoe werkt deze formule? De formule bepaalt het kleinste rijnummer waarvoor geldt dat de waarde uit kolom B2:B7 overeenkomt met cel D2 en waarvoor geldt dat het bijbehorende team nog niet eerder is weergegeven. Dat laatste voorkomt dat een team bij gelijke stand twee keer wordt weergegeven. Dit wordt gecheckt met de functie: ISNB(VERGELIJKEN(A$2:A$7;E$1:E1;0)). Er mag alleen maar gekozen uit teams die nog niet eerder zijn voorgekomen in de stand. Dat wordt gedaan door de waarden van A2:A7 te vergelijken met de waarden in E$1:E1 om vervolgens alleen de waarden te kunnen gebruiken die niet gevonden kunnen worden en waarbij dus de vergelijken functie #N/B oplevert. Doordat de functie naar beneden wordt gekopieerd wordt de laatste nummer van E$1:E1 telkens aangepast (E1 wordt E2 enz.) en wordt zekergesteld dat een team niet twee keer kan voorkomen. Het gevonden rijnummer tenslotte wordt verlaagd met 1 omdat de eerste waarde van de matrix op rij 2 begint, terwijl bij de INDEX-functie de eerste waarde een 1 is.
Een andere formule om hetzelfde te bereiken is:
{=INDEX(A$2:A$7;KLEINSTE(ALS(B$2:B$7=D2;RIJ(B$2:B$7)-1);AANTAL.ALS(D$2:D2;D2)))}Deze formule gebruikt een andere insteek om te voorkomen dat dubbele waarden bij gelijke stand worden weergegeven. Met AANTAL.ALS(D$2:D2;D2) wordt de positie van de functie KLEINSTE ingesteld. Bij dubbele waarden wordt eerst de kleinste rij weergegeven die voldoet aan de voorwaarde dat B2:B7=D2 en vervolgens de op één na kleinste rij. In het eerste geval heeft de functie AANTAL.ALS(D$2:D2;D2) de waarde 1 en in het tweede geval de waarde 2.
Bovenstaande formules werken ook als er meer dan 2 teams op dezelfde plaats zijn geëindigd.
7.2. Alfabetisch sorteren
Stel dat onderstaande lijst alfabetisch gesorteerd moet worden.
Dit alfabetisch sorteren kan met behulp van de volgende matrixformule:
{=INDEX(A$1:A$10;VERGELIJKEN(KLEINSTE(AANTAL.ALS(A$1:A$10;"<"&A$1:A$10);RIJ(A1));AANTAL.ALS(A$1:A$10;"<"&A$1:A$10);0))}Dit sorteren wordt gedaan met behulp van de functie AANTAL.ALS. Per regel wordt bekeken hoeveel waarden kleiner zijn dan de waarde op de betreffende regel. In feite ontstaat hierbij virtueel de volgende tabel:
In deze tabel is de volgorde al bepaald van het alfabetisch sorteren. En met de functie KLEINSTE wordt deze tabel in feite van laag naar hoog uitgelezen, doordat de positie van de kleinste wordt ingesteld met AANTAL.ALS(A$1:A$10;”<“&A$1:A$10).
7.3 Positie van 1e hoofdletter in een woord vinden
In cel A1 staat een woord van willekeurige lengte waarvan de positie van de eerste hoofdletter bepaald moet worden. Hiervoor kan de deze matrixformule gebruikt worden:
{=MIN(ALS((CODE(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1))>=65)*(CODE(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1))<=90);RIJ(INDIRECT("1:"&LENGTE(A1)))))}In deze formule wordt met RIJ(INDIRECT(“1:”&LENGTE(A1))) een virtuele matrix gevormd met getallen van 1 tot en met de lengte van het woord. Daarna wordt het woord letter voor letter gecontroleerd op hoofdletters. Deze controle wordt gedaan met de functie CODE. Met de functie CODE wordt de numerieke waarde van een karakter bepaald. De numerieke waarden van hoofdletters liggen tussen 65 en 90. Vervolgens wordt de eerste positie weergegeven, omdat de functie MIN wordt gebruikt en daarmee het laagste getal wordt weergegeven. Om de laatste positie van een hoofdletter van een woord weer te geven moet de functie MAX gebruikt worden in plaats van MIN.
8. Conclusie
In deze tutorial zijn aan de hand van een aantal voorbeelden soms redelijk complexe matrixformules besproken. Met matrixformules kan nog veel meer, maar het is onmogelijk om dat allemaal te behandelen. De tutorial is vooral bedoeld om een indruk te geven wat zoal met matrixformules gedaan kan worden. Matrixformules vormen een zeer krachtig gereedschap. Er kunnen dingen mee gedaan worden die met normale formules niet mogelijk zijn. Hoewel matrixformules in eerste instantie onbegrijpelijk over kunnen komen, zit er zeker een bepaalde logica in. Deze logica kan het best ontdekt worden door veel met matrixformules te oefenen. Hierdoor ontstaat ook inzicht in hoe matrixformules precies werken en wat allemaal mee gedaan kan worden.
9. Vragen / suggesties
Hopelijk heeft dit artikel geholpen bij het begrijpen en toepassen van matrix formules. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.
