Hoe werkt een array formule in Excel?

Een array formule, ook wel matrixformule of CSE-formule genoemd, kan meerdere berekeningen tegelijk maken, of één 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 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.

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

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

MAAR RUSTIG AAN! Want 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.

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.

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

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)

Vergeet niet om CTRL+SHIFT+ENTER te gebruiken voor het correcte 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.

Array functies 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…