Excel glossary

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 verwezen wordt in een array-formule 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-formule 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-formule te gebruiken.

Repetitive

Multicel array-formule

De multicel array-formule (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:

Select a cell range

Dan noteren we de array-formule in de geselecteerde cel D2:

Enter

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.

Result of a multi cell array formula // 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 matrix- 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:

Revenue calculation with SUM function // 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)’:

Example of a single cell array formula // 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.

Result of a single cell array formula in Excel // PerfectXL

Array-formules zijn riskant

Wij beschouwen array-formules als zeer risicovol, omdat het delen van een spreadsheet met meerdere gebruikers hierdoor zeer foutgevoelig wordt. Misschien is jouw collega niet bekend met array-formules en verandert hij iets zonder CTRL+SHIFT+ENTER te drukken… Daarom detecteert PerfectXL alle array-formules in je spreadsheet en markeert ze als mogelijke risico’s.