Een Excel array-formule is een formule die berekeningen uitvoert op de waarden in een of meer arrays in plaats van op een enkele gegevenswaarde. In spreadsheetprogramma's is een array een bereik of reeks gerelateerde gegevenswaarden die zich gewoonlijk in aangrenzende cellen in een werkblad bevinden.
Deze instructies zijn van toepassing op Excel 2019, 2016, 2013, 2010 en Excel voor Microsoft 365.
Wat zijn matrixformules?
Array-formules zijn vergelijkbaar met gewone formules. zij:
- Begin met een gelijkteken (=)
- Gebruik dezelfde syntaxis als reguliere formules
- Gebruik dezelfde wiskundige operatoren
- Volg dezelfde volgorde van bewerking
Arrayformules zijn echter omgeven door accolades { }. En je kunt ze niet zomaar intypen; u moet ze toevoegen door op de toetsen Ctrl, Shift en Enter te drukken nadat u de formule in een cel of cellen. Om deze reden wordt een matrixformule soms een CSE-formule genoemd in Excel.
Elke keer dat u een matrixformule bewerkt, verdwijnen de accolades. Om ze terug te krijgen, drukt u nogmaals op de toetsen Ctrl, Shift en Enter.
Er zijn twee hoofdtypen matrixformules:
- Eencellige matrixformules die meerdere berekeningen in één werkbladcel uitvoeren
- Meercellige matrixformules die zich in meer dan één werkbladcel bevinden
Een matrixformule maken
- Voer de formule in een cel in.
- Houd de Ctrl en Shift toetsen op het toetsenbord ingedrukt.
- Druk op de Enter-toets en laat deze los om de matrixformule te maken.
- Laat de toetsen Ctrl en Shift los.
- Als het correct wordt gedaan, zullen accolades de formule omringen.
Eencellige matrixformules
Een matrixformule met één cel gebruikt een functie, zoals SUM, AVERAGE of COUNT, om de uitvoer van een matrixformule met meerdere cellen te combineren tot een enkele waarde in een enkele cel. Hieronder is een voorbeeld:
{=SOM(A1:A2B1:B2)}
De bovenstaande formule telt het product van A1B1 en A2B2 bij elkaar op, en geeft dan een enkel resultaat in een enkele cel in het werkblad. Een andere manier om die formule te presenteren is:
=(A1B1)+(A2B2)
Multi-Cell array-formules
Zoals de naam al doet vermoeden, bevinden matrixformules met meerdere cellen zich in meerdere werkbladcellen en retourneren ze een matrix als antwoord. Met andere woorden, dezelfde formule staat in twee of meer cellen en geeft in elke cel verschillende antwoorden.
Elke kopie, of instantie, van de matrixformule voert dezelfde berekening uit in elke cel die hij bewoont, maar elke cel gebruikt andere gegevens. Daarom levert elk verschillende resultaten op. Een voorbeeld van een matrixformule met meerdere cellen is:
{=A1:A2B1:B2}
Als de bovenstaande matrixformule in cellen C1 en C2 in een werkblad staat, dan zouden de resultaten als volgt zijn:
- De gegevens in A1 worden vermenigvuldigd met de gegevens in B1, en de resultaten verschijnen in cel C1.
- De gegevens in A2 worden vermenigvuldigd met de gegevens in B2, en de resultaten verschijnen in cel C2.
Array-formules en Excel-functies
U kunt veel van de ingebouwde functies van Excel gebruiken, zoals SUM, AVERAGE en COUNT , in een matrixformule. Er zijn ook een paar functies, zoals TRANSPOSE, die altijd een matrixformule moeten zijn om correct te werken. (De functie TRANSPOSE kopieert gegevens van een rij naar een kolom of omgekeerd.)
U kunt ook het nut van vele functies uitbreiden, zoals INDEX en MATCH of MAXen IF door ze samen te gebruiken in een matrixformule.
Maak een eenvoudige formule voor eencellige matrix
Eencellige matrixformules voeren meestal eerst een meercellige berekening uit en gebruiken dan een functie zoals GEMIDDELDE of SUM om te combineren de uitvoer van de array in een enkel resultaat.
Negeer foutwaarden bij het vinden van gegevens
Deze matrixformule gebruikt de AVERAGE, IF en ISNUMBER functies om de gemiddelde waarde voor de bestaande gegevens terwijl foutwaarden zoals DIV/0! en NAME? worden genegeerd
Tel gegevenscellen
Gebruik de SUM en IF functies in een matrixformule om cellen met gegevens te tellen die aan een of meerdere voorwaarden voldoen; deze techniek verschilt van het gebruik van Excel's COUNTIFS functie, die vereist dat aan alle gestelde voorwaarden wordt voldaan voordat de cel wordt geteld.
Zoek het grootste positieve of negatieve getal
Dit voorbeeld combineert de functie MAX en IF in een matrixformule die de grootste of maximale waarde voor een bereik van gegevens wanneer deze aan bepaalde criteria voldoen. Hier staat de grootste waarde voor de langzaamste tijd.
Zoek het kleinste positieve of negatieve getal
Net als in het bovenstaande voorbeeld, kunt u de functies MIN en IF combineren in een matrixformule om de kleinste of minimale waarde te vinden voor een reeks gegevens wanneer deze aan specifieke criteria voldoet.
Vind de middelste of mediane waarde
De functie MEDIAN in Excel vindt de middelste waarde voor een lijst met gegevens. Door het te combineren met de functie IF in een matrixformule, kunt u de middelste waarde vinden voor verschillende groepen gerelateerde gegevens.
Maak een opzoekformule met meerdere criteria
Deze matrixformule omvat het nesten van de functies MATCH en INDEX om specifieke informatie in een database te vinden.
Formule voor links opzoeken
De functie VLOOKUP zoekt meestal alleen naar gegevens in kolommen aan de rechterkant, maar door deze te combineren met de functie CHOOSE kan een formule voor links opzoeken maken die kolommen met gegevens links van het argument Lookup_value zoekt.
Bij het invoeren van een array als argument voor een functie die meestal slechts een enkele waarde of celverwijzing bevat, kunt u de accolades rechtstreeks typen in plaats van de Ctrl+Shift+Entertoetsaanslagcombinatie, zoals in het bovenstaande voorbeeld.
