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. 

Wat is een matrixformule / zonder array-formule :: PerfectXL

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:

Wat is een matrixformule - Array-formule met meerdere cellen :: PerfectXL

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

Wat is een array-formule - Matrixformule met meerdere cellen :: 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.

Wat is een array-formule - Resultaat matrixformule met meerdere cellen :: 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 matrixformule

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:

Wat is een array-formule - Matrixformule met een cel :: 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)’:

Wat is een array-formule - Matrixformule met een cel :: 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.

Wat is een array-formule - Resultaat van een matrixformule met een cel :: 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. 

Lees onze Guideline over het gebruik van array-formules

Probeer PerfectXL!

 

Over PerfectXL

Met PerfectXL heb je snel en effectief inzicht in alle risico’s, fouten en overige problemen in jouw spreadsheets, plus een uitgebreid plan voor verbetering. Je hoeft zelf geen expert te zijn en het kost je weinig tijd om je bestand te verbeteren.

Lees meer over de features van PerfectXL

 

Array-formules zijn verleden tijd

Lang geleden was Excel nog lang niet zo geavanceerd als tegenwoordig, en de zogenaamde array-formules (ook wel matrixformules genoemd) bestaan al heel lang. Desondanks zijn er maar weinig mensen bekend met deze formules, en er zijn nog minder mensen die ze goed begrijpen.
Lees onze guideline over de risico’s van array-formules

 

Meld je aan voor onze Excel Guidelines