Tutorial Excel matrixformules (basis)

  1. inleiding
  2. matrixformules met een enkele waarde
  3. matrixformules met meerdere waarden
  4. voor- en nadelen

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 worden hoe matrixformules werken en wat er mee gedaan kan worden.
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. 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.

matrixformules1_290x200

Stel nu dat je van verkoper “jan” de hoogst verkochte prijs/stuk zou willen weten. Dit is met gewone formules niet zo maar even uit te rekenen, maar met een matrixformule kan dat eenvoudig uitgerekend worden. Dit kan 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 korte schrijfwijze werkt overigens alleen goed bij maxima groter of gelijk aan nul. Vooral bij de 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 door middel van de SOM 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 niet meer te doen, waarbij de kans op fouten erg groot is. Dit is dus een voorbeeld van een erg korte matrixformule waarmee heel snel een erg uitgebreide berekening kan worden uitgevoerd.

 

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.

matrixformules2_395x215

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:

matrixformules3_305x115

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

In de tutorial Matrixformules (vervolg) wordt dieper ingegaan op matrixformules en komen een aantal complexe formules aan de orde.

 

Interesse in andere tutorials? Ga dan naar ons tutorials-overzicht of neem een kijkje op onze pagina met tips&trucs.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

2 reacties op Excel matrixformules basis

  • Hallo,

    Kan het zo zijn dat wanneer je cellen vult middels een matrix formule, het dan niet mogelijk is de inhoud van de cel te kopiëren of in een opvolgende formule te gebruiken?
    Zou het mogelijk zijn om met een matrix formule een complete grafiek op te zetten?

    • Matrixformules kun je zonder problemen kopiëren. Je hoeft dan geen Ctrl+Shift+Enter te gebruiken.
      Ook het gebruiken van matrixformules voor brongegevens van een grafiek is geen probleem.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.