Tutorial Excel matrixformules (vervolg)

  1. inleiding
  2. en + of-operatoren
  3. gebruik van constanten
  4. competitiestand bepalen
  5. alfabetisch sorteren
  6. positie van 1e hoofdletter in woord vinden
  7. ten slotte

1. INLEIDING

In de tutorial Matrixformules (basis) komen de basisbeginselen van matrixformules aan de orde. In deze tutorial zal dieper ingegaan worden op het gebruik van matrixformules en zullen aan de hand van voorbeelden een aantal complexere matrixformules besproken worden. Matrixformules moeten afgesloten worden met Ctrl+Shift+Enter. Daarna verschijnen er automatisch accolades om deze formules ten teken dat het om een matrixformule gaat. Let wel, deze accolades moeten dus niet ingetypt worden. In deze workshop worden de accolades wél weergeven om daarmee duidelijk te maken dat het om een matrixformule gaat.

2. EN + OF-OPERATOREN

Matrixformules kunnen gebruikt worden om gegevens te filteren op meerdere criteria. In de tutorial Matrixformules (basis) is het volgende voorbeeld gebruikt:

matrixformules1_290x200

Met de volgende formule kon de hoogst verkochte prijs/eenheid berekend 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).

3. 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^2

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

matrixformules7_180x135

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 worden data opgeslagen als getal. In bovenstaand voorbeeld zijn dat de rijnummers 41399 tot en met 41467. 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.

4. COMPETITIESTAND BEPALEN

Excel wordt veel gebruikt voor het bijhouden van uitslagen en standen van competities. In onderstaand plaatje staat een voorbeeld van een competitie.

matrixformules4_250x220

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 worden hier besproken. Uitgangspunt hierbij dat in D2:D7 de punten 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.

5. ALFABETISCH SORTEREN

Stel dat onderstaande lijst alfabetisch gesorteerd moet worden.

matrixformules5_125x245

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:

matrixformules6_130x200

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

6. POSITIE VAN 1e HOOFDLETTER IN 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.

7. TEN SLOTTE

In deze tutorial zijn aan de hand van een aantal voorbeelden 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.

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.

4 reacties op Excel matrixformules vervolg

  • is het ook mogelijk om een automatische rooster-indeling te maken met behulp van matrixformules? Bijvoorbeeld: je hebt verschillende werknemers van een supermarkt; Albert, Hein, Dirk etc. Albert kan elke functie vervullen die er in de supermarkt is. Hein kan alleen bij de groente werken en achter de kassa. Dirk bij de groente en de zuivelafdeling. Per dag zijn er x functies die vervuld moeten worden. Als je een schema hebt van de werknemers die op een dag beschikbaar zijn, kan excel dan automatisch een indeling maken?
    Om het nog iets complexer te maken: kan het ook met voorkeuren? Of 60% van de tijd op een functie? En ad random; dus niet dat Albert altijd als eerste wordt ingedeeld en dus altijd dezelfde functie heeft?

    • Bas, dank voor je reactie.
      Het is normaal gesproken wel mogelijk om met matrixformules een roosterindeling te maken op basis van beschikbaarheid en competenties. In zijn algemeenheid is niet te zeggen hoe deze formules eruit zien, omdat dit geheel afhankelijk is van de opbouw van je bestand. Wellicht zijn er ook wel matrixformules te maken die rekening houden met je aanvullende wensen zoals voorkeuren en random. Maar deze formules zullen wel heel erg complex worden en bij grote aantallen zal het bestand trager worden. Ik denk dat je dan beter kunt denken aan een VBA-oplossing. Ik zou dat ook wel kunnen maken. Mocht je hierover meer willen weten, neem dan even contact met me op.
      Vriendelijke groet, Manfred

  • Hele leerzame en informatieve tutorial.
    Eén kleine onvolkomenheid bij de ‘camping’-formule onder punt 3:
    Het basistarief wordt berekend exclusief de vertrekdag, de toeslag inclusief de vertrekdag.
    (1+B2-B1)*B3 zou consequenter zijn, alles inclusief.

    • Koos, bedankt voor je reactie.
      Scherp opgemerkt!
      Ik heb de formule aangepast.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.