Verklarende woordenlijst Excel

Wat is een array formule?

Een array formule, ook wel matrixformule of cse-formule genoemd, kan meerdere berekeningen tegelijk maken, of een of meerdere berekeningen meerdere keren uitvoeren binnen een bepaald celbereik.

De celwaarden waarnaar de formule verwijst kunnen zich voordoen als waarden in een rij, een kolom of in een matrix (rijen en kolommen), vandaar het synoniem ‘matrixformule’. Om het principe van de array functie uit te leggen, zullen we een aantal voorbeelden gebruiken.

Zonder de array formule

In het onderstaande voorbeeld vind je een overzicht van verschillende soorten fruit in (A2:A9), het aantal dat op een dag verkocht wordt (B2:B9) en de prijs per product (C2:C9). De berekening van de omzet per fruitsoort wordt cel per cel uitgerekend in de omzetkolom (D2:D9). Dit kan sneller, door een multicel array functie te gebruiken.

Berekening zonder array formule // Verklarende Woordenlijst Excel // PerfectXL

Multicel array formule

De multicel array functie (of matrixformule) berekent meerdere resultaten binnen een rij of kolom, met behulp van slechts één formule. Om alle waarden van de omzetkolom uit het voorbeeld hierboven te berekenen, beginnen we met het selecteren van het celbereik waarbinnen we de resultaten willen publiceren. Vervolgens klikken we op F2 om de eerste cel van dit celbereik te selecteren:

Multicel matrix formule voorbeeld // Verklarende Woordenlijst Excel // PerfectXL

Dan noteren we de array functie in de geselecteerde cel D2:

Multicel matrix formule voorbeeld // Verklarende Woordenlijst Excel // PerfectXL

MAAR GA NIET TE SNEL! Zodra je op ENTER klikt na het noteren van je formule, berekent Excel enkel het resultaat van de huidige rij! Dit type formule wordt ook wel CSE-formule genoemd, omdat hij alleen maar correct uitgevoerd wordt als je CTRL+SHIFT+ENTER gebruikt.

Multicel matrix formule voorbeeld // Verklarende Woordenlijst Excel // PerfectXL

Zoals je hierboven ziet heeft de array-formule alle waarden binnen de reeks in één keer berekend. Excel heeft automatisch accolades om de formule heen geplaatst, om duidelijk te maken dat het om een matrixformule gaat.

Enkele cel array formule

Deze array formule berekent slechts één resultaat in één cel, maar voert wel meerdere berekeningen uit.

In ons voorbeeld kun je de totale fruit omzet natuurlijk berekenen door de SUM functie (of SOM in de Nederlandstalige versie van Excel) te gebruiken:

Voorbeeld van een formule zonder array functie // Verklarende Woordenlijst Excel // PerfectXL

Als we echter gebruik maken van de array functie, dan hebben we de subtotalen niet nodig om de totale omzet te berekenen. We selecteren simpelweg de cel waarin we het totaal willen publiceren en vervolgens typen we daarin de formule ‘=SUM(B2:B9*C2:C9)’:

Voorbeeld enkele cel matrixformule // Verklarende Woordenlijst Excel // PerfectXL

Vergeet niet om CTRL+SHIFT+ENTER te klikken voor het resultaat! Als je slechts op ENTER klikt, dan zal Excel de standaard error’#VALUE!’ geven. Als je de array formule correct uitvoert dan verschijnt de totale omzet in de geselecteerde cel en zie je accolades rondom de formule. Accolades geven aan dat het een array formule betreft.

Voorbeeld resultaat enkele cel matrixformule // Verklarende Woordenlijst Excel // PerfectXL

Array formules zijn riskant

Bij PerfectXL beschouwen we dit type formule als zeer risicovol, omdat het delen van een spreadsheet met meerdere gebruikers hierdoor zeer foutgevoelig wordt. Misschien is jouw collega niet bekend met de werking van een array functie en verandert hij iets zonder CTRL+SHIFT+ENTER te drukken… Daarom detecteren PerfectXL Risk Eliminator en de PerfectXL Add-in alle array formules in je spreadsheet en markeren ze als mogelijke risico’s.