Dynamische matrices in Excel 365: spillbereik uitgelegd (2026)
Dynamische matrices in Excel 365 zijn formules die automatisch meerdere waarden retourneren en deze “uitspillen” (spill) naar aangrenzende cellen. Met het spillbereik en de #-operator maak je krachtige formules die automatisch meegroeien met je data, zonder formules te kopieren.
Laatst bijgewerkt: januari 2026
In het kort
- Dynamische matrices retourneren meerdere waarden die automatisch “spillen” naar lege cellen
- Het spillbereik is het gebied waar de resultaten naartoe verspreiden
- Gebruik de #-operator om naar een volledig spillbereik te verwijzen (bijvoorbeeld
A2#) - De @-operator (impliciete intersectie) beperkt het resultaat tot een enkele waarde
- De #OVERLOOP! fout (#SPILL! in het Engels) verschijnt wanneer het spillbereik niet leeg is
- Dynamische matrices vervangen oude CSE-arrayformules (Ctrl+Shift+Enter)
Wat zijn dynamische matrices?
Dynamische matrices zijn een fundamentele vernieuwing in Excel 365 die de manier waarop formules werken volledig heeft veranderd. In tegenstelling tot klassieke formules die een enkele waarde per cel retourneren, kunnen dynamische matrixformules meerdere waarden tegelijk retourneren.
Traditionele formules vs. dynamische matrices
Traditionele formules (Excel 2019 en ouder):
– Een formule retourneert altijd precies een waarde
– Voor meerdere resultaten moet je de formule naar beneden of opzij kopieren
– Arrayformules vereisten Ctrl+Shift+Enter (CSE-formules)
Dynamische matrices (Excel 365 en 2021+):
– Een formule kan meerdere waarden retourneren
– Resultaten verspreiden automatisch naar aangrenzende cellen (spillen)
– Geen Ctrl+Shift+Enter meer nodig
– Nieuwe functies zoals FILTER, UNIEK en SORTEREN zijn speciaal ontworpen voor dynamische matrices
Versievereisten
Dynamische matrices werken in:
– Excel 365 (Microsoft 365 abonnement) – volledige ondersteuning
– Excel 2021 – volledige ondersteuning
– Excel voor het web – volledige ondersteuning
In Excel 2019 en ouder krijg je een #NAAM? fout bij dynamische matrixfuncties.
Het spillbereik begrijpen
Wanneer een formule meerdere waarden retourneert, “spillen” deze naar aangrenzende cellen. Dit gebied heet het spillbereik (spill range in het Engels).
Hoe herken je een spillbereik?
- Klik op een cel met een dynamische matrixformule
- Excel toont een blauwe rand om het volledige resultaatgebied
- De formule staat alleen in de eerste cel (de “ankerceel”)
- De andere cellen in het spillbereik tonen de formule grijs in de formulebalk
Voorbeeld: Een eenvoudig spillbereik
Stel je hebt namen in A2:A6:
| A |
|---|
| Jan |
| Piet |
| Klaas |
| Marie |
| Anna |
In cel C2 typ je:
=SORTEREN(A2:A6)
Resultaat in C2:C6:
| C |
|---|
| Anna |
| Jan |
| Klaas |
| Marie |
| Piet |
De formule staat alleen in C2, maar de resultaten spillen automatisch naar C3:C6.
Belangrijk: spillbereik moet leeg zijn
Als er data staat in het gebied waar Excel wil spillen, krijg je een #OVERLOOP! fout (of #SPILL! in Engelstalige Excel). Maak de cellen leeg of verplaats je formule.
De #-operator: verwijzen naar het spillbereik
De hash-operator (#) is een van de krachtigste toevoegingen aan Excel 365. Hiermee verwijs je naar het volledige resultaat van een dynamische matrixformule, ongeacht hoeveel rijen of kolommen het bevat.
Syntaxis van de #-operator
celadres#
Waarbij celadres de cel is met de dynamische matrixformule (de ankerceel).
Stap-voor-stap: De #-operator gebruiken
Stap 1: Maak een dynamische matrixformule
In cel D2:
=UNIEK(A2:A100)
Dit geeft een lijst van unieke waarden die naar beneden spillt.
Stap 2: Verwijs naar het spillbereik
In een andere cel kun je naar het hele resultaat verwijzen:
=AANTAL(D2#)
Dit telt alle cellen in het spillbereik, ongeacht hoeveel waarden er zijn.
Praktische voorbeelden met de #-operator
Voorbeeld 1: Aantal unieke waarden tellen
=AANTALARG(D2#)
Telt hoeveel unieke waarden er zijn in het spillbereik.
Voorbeeld 2: Som van gefilterde waarden
Als D2 een FILTER-formule bevat:
=SOM(D2#)
Berekent de som van alle gefilterde waarden.
Voorbeeld 3: Spillbereik gebruiken in VERT.ZOEKEN
=VERT.ZOEKEN(G2;D2#;2;ONWAAR)
Zoekt in het dynamische spillbereik dat begint in D2.
Voorbeeld 4: Combineren met andere functies
=GEMIDDELDE(FILTER(Verkoop[Omzet];Verkoop[Regio]="Noord")#)
De @-operator: impliciete intersectie
De @-operator is het tegenovergestelde van spillgedrag. Deze operator dwingt Excel om slechts een enkele waarde te retourneren in plaats van een hele array.
Wat is impliciete intersectie?
In oudere Excel-versies was impliciete intersectie het standaardgedrag: als een formule meerdere waarden kon retourneren, koos Excel automatisch de waarde op dezelfde rij of kolom. In Excel 365 moet je dit expliciet aangeven met @.
Syntaxis van de @-operator
=@formule
Wanneer gebruik je de @-operator?
- Compatibiliteit met oude spreadsheets – Excel voegt automatisch @ toe bij het openen van oudere bestanden
- Opzettelijk een enkele waarde forceren – Wanneer je specifiek de impliciete intersectie wilt
- In klassieke formulestructuren – Wanneer spill-gedrag niet gewenst is
Voorbeeld: @-operator in actie
Stel je hebt deze formule in C2:
=A2:A10*B2:B10
Zonder @: De formule zou alle producten berekenen en spillen naar C2:C10.
Met @:
=@A2:A10*@B2:B10
Dit retourneert alleen het product van A2*B2 (de waarden op dezelfde rij als de formule).
@-operator in VERT.ZOEKEN
In Excel 365 gedraagt VERT.ZOEKEN zich anders dan voorheen:
=VERT.ZOEKEN(A2;Tabel;2;ONWAAR)
Als A2 een bereik is, zou dit normaal spillen. Met @:
=VERT.ZOEKEN(@A2;Tabel;2;ONWAAR)
Dit retourneert alleen de waarde voor de huidige rij.
De #OVERLOOP! fout (#SPILL!) oplossen
De #OVERLOOP! fout (in het Engels: #SPILL!) is de meest voorkomende fout bij dynamische matrices. Deze verschijnt wanneer Excel de resultaten niet kan spillen.
Oorzaken van de #OVERLOOP! fout
- Niet-lege cellen in het spillbereik – De meest voorkomende oorzaak
- Spillbereik valt buiten het werkblad – Te veel resultaten voor de beschikbare ruimte
- Samenvoegde cellen in het spillbereik – Samenvoegen blokkeert spillen
- Tabelstructuur – Binnen Excel-tabellen kan spillen beperkt zijn
Stap-voor-stap: #OVERLOOP! oplossen
Stap 1: Identificeer het probleem
- Klik op de cel met de #OVERLOOP! fout
- Bekijk het foutmenu (klik op het waarschuwingsicoon)
- Excel toont welke cellen het spillen blokkeren
Stap 2: Maak ruimte vrij
- Verwijder data uit de blokkerende cellen
- Of verplaats je formule naar een locatie met voldoende lege ruimte
Stap 3: Controleer op samengevoegde cellen
- Selecteer het gebied onder je formule
- Ga naar Start > Cellen samenvoegen > Samenvoeging opheffen
Stap 4 (Windows):
– Selecteer het spillgebied
– Druk op Delete om de inhoud te wissen
– Of gebruik Ctrl+Z om recent toegevoegde data ongedaan te maken
Stap 4 (Mac):
– Selecteer het spillgebied
– Druk op Fn+Delete of Delete
– Of gebruik Cmd+Z voor ongedaan maken
Tip: Voorkom #OVERLOOP! fouten
Reserveer altijd voldoende lege ruimte onder en naast dynamische matrixformules. Een goede vuistregel: laat minimaal evenveel lege rijen als je brondata bevat.
Dynamische arrayfuncties in Excel 365
Excel 365 heeft een reeks nieuwe functies die speciaal zijn ontworpen voor dynamische matrices:
Overzicht dynamische arrayfuncties
| Functie | Beschrijving | Artikel |
|---|---|---|
| FILTER | Filtert data op voorwaarden | FILTER functie |
| UNIEK | Haalt unieke waarden op | UNIEK functie |
| SORTEREN | Sorteert data | SORTEREN functie |
| SORTEERVOLG | Sorteert op aparte kolom | Gerelateerd aan SORTEREN |
| REEKS | Genereert een getallenreeks | Geavanceerde functies |
| TEKST.SPLITSEN | Splitst tekst naar cellen | Tekstfuncties |
| STAPELV/STAPELH | Stapelt bereiken verticaal/horizontaal | Geavanceerde functies |
| NEMEN | Neemt eerste/laatste n rijen | Geavanceerde functies |
| LATEN.VALLEN | Slaat eerste/laatste n rijen over | Geavanceerde functies |
Voorbeeld: Functies combineren
Een krachtige combinatie van dynamische arrayfuncties:
=SORTEREN(UNIEK(FILTER(A2:C100;B2:B100="Noord")))
Dit:
1. Filtert alle rijen waar kolom B = “Noord”
2. Haalt unieke waarden uit het resultaat
3. Sorteert het eindresultaat alfabetisch
Het resultaat spillt automatisch naar alle benodigde cellen.
Dynamische matrices vs. oude CSE-arrayformules
Voor Excel 365 moest je voor arrayberekeningen Ctrl+Shift+Enter (CSE) gebruiken. Dit zorgde voor veel verwarring en fouten.
Vergelijking
| Aspect | CSE-formules (oud) | Dynamische matrices (nieuw) |
|---|---|---|
| Invoer | Ctrl+Shift+Enter | Gewoon Enter |
| Resultaat | Vast bereik | Automatisch spillen |
| Formule bewerken | Hele bereik selecteren | Alleen ankerceel |
| Grootte aanpassen | Handmatig | Automatisch |
| Accolades | { } zichtbaar | Niet nodig |
| Excel-versie | Alle versies | Excel 365/2021+ |
Voorbeeld: Dezelfde berekening
Oude CSE-methode (Excel 2019 en ouder):
- Selecteer het doelbereik (bijv. D2:D10)
- Typ:
=A2:A10*B2:B10 - Druk op Ctrl+Shift+Enter
- De formule toont
{=A2:A10*B2:B10}met accolades
Nieuwe dynamische methode (Excel 365):
- Klik in cel D2
- Typ:
=A2:A10*B2:B10 - Druk op Enter
- De resultaten spillen automatisch naar D2:D10
Migratie van oude spreadsheets
Wanneer je een oud bestand opent in Excel 365:
– Bestaande CSE-formules blijven werken
– Excel kan de @-operator toevoegen voor compatibiliteit
– Je kunt CSE-formules handmatig vervangen door dynamische versies
Praktische voorbeelden
Voorbeeld 1: Dynamisch dashboard met FILTER
Maak een dashboard dat automatisch bijwerkt wanneer je een dropdown wijzigt:
Stap 1: Maak een dropdown in cel G1 met regio’s
Stap 2: Gebruik FILTER om data te tonen:
=FILTER(A2:D100;B2:B100=G1;"Geen data voor deze regio")
Stap 3: Verwijs naar het spillbereik voor berekeningen:
=SOM(INDEX(F2#;;4))
Voorbeeld 2: Top 10 lijst die automatisch bijwerkt
=SORTEREN(FILTER(A2:C100;C2:C100>1000);3;-1)
Deze formule:
1. Filtert alle rijen met waarde > 1000 in kolom C
2. Sorteert aflopend op kolom 3
3. Het resultaat spillt en update automatisch bij nieuwe data
Voorbeeld 3: Unieke waarden voor een validatielijst
=SORTEREN(UNIEK(FILTER(A2:A100;A2:A100<>"")))
Dit geeft een gesorteerde lijst van unieke, niet-lege waarden – perfect voor dropdown-validatie.
Sneltoetsen voor dynamische matrices
| Actie | Windows | Mac |
|---|---|---|
| Formule bevestigen | Enter | Return |
| Naar spillbereik navigeren | Ctrl+/ | Cmd+/ |
| Hele spillbereik selecteren | Ctrl+Shift+End (vanaf ankerceel) | Cmd+Shift+End |
| Bereik met naam definiëren | Ctrl+F3 | Cmd+F3 |
Veelgemaakte fouten
Fout 1: #OVERLOOP! door niet-lege cellen
Probleem: Je dynamische formule geeft #OVERLOOP! omdat er data in het spillbereik staat.
Oplossing:
– Identificeer de blokkerende cellen via het foutmenu
– Verwijder de data of verplaats je formule naar een lege locatie
Fout 2: Formule spillt niet – #NAAM! fout
Probleem: De formule geeft #NAAM! in plaats van te spillen.
Oorzaak: Je gebruikt een dynamische arrayfunctie in Excel 2019 of ouder.
Oplossing: Upgrade naar Excel 365 of Excel 2021. Er is geen workaround voor oudere versies.
Fout 3: Onverwachte @-operator in formules
Probleem: Excel voegt automatisch @ toe aan je formules bij het openen van oudere bestanden.
Oorzaak: Excel 365 converteert impliciete intersectie naar expliciete @-operator voor compatibiliteit.
Oplossing: Verwijder de @ handmatig als je spillgedrag wilt, of laat staan voor compatibiliteit met oudere Excel-versies.
Fout 4: Spillbereik verwijst naar verkeerde grootte
Probleem: Je A2# referentie omvat niet alle verwachte waarden.
Oorzaak: De formule in A2 retourneert minder waarden dan verwacht, of er is een fout.
Oplossing: Controleer de bronformule in de ankerceel. Los eventuele fouten op in de FILTER- of andere formule.
Fout 5: Dynamische formule binnen Excel-tabel werkt niet
Probleem: Dynamische matrices gedragen zich anders binnen gestructureerde tabellen (Ctrl+T).
Oorzaak: Excel-tabellen hebben beperkingen voor spillgedrag om de tabelstructuur te behouden.
Oplossing: Plaats dynamische matrixformules buiten de tabel, of gebruik de tabel als databron met verwijzingen.
FAQ
Wat is het verschil tussen een spillbereik en een normaal bereik?
Een spillbereik is dynamisch: het groeit en krimpt automatisch wanneer de brondata verandert. Een normaal bereik (zoals A1:A10) is statisch en verandert niet automatisch. Met de #-operator (zoals A1#) verwijs je naar het volledige spillbereik, ongeacht de huidige grootte.
Kan ik dynamische matrices gebruiken in draaitabellen?
Ja, sinds recente updates van Excel 365 kun je spillbereiken gebruiken als bron voor draaitabellen. Definieer een benoemd bereik dat naar het spillbereik verwijst (bijvoorbeeld =Sheet1!$A$2#) en gebruik dit als databron.
Werken dynamische matrices in gedeelde werkmappen?
Ja, dynamische matrices werken in Excel 365 gedeelde werkmappen en co-authoring. Alle gebruikers moeten wel Excel 365 of 2021 hebben om de formules correct te zien.
Hoe voorkom ik dat mijn spillbereik te groot wordt?
Combineer dynamische functies met beperkingen. Gebruik bijvoorbeeld =NEMEN(SORTEREN(data);10) om alleen de eerste 10 resultaten te tonen, of voeg filtervoorwaarden toe om de dataset te beperken.
Waarom toont mijn oude spreadsheet ineens @-operators?
Excel 365 voegt automatisch de @-operator toe aan formules die in oudere versies impliciete intersectie gebruikten. Dit zorgt ervoor dat de formules hetzelfde resultaat geven als voorheen. Je kunt de @ verwijderen als je het nieuwe spillgedrag wilt.
Kan ik naar een spillbereik op een ander werkblad verwijzen?
Ja, gebruik de bladnaam in de verwijzing: =Sheet2!A2#. Dit verwijst naar het spillbereik dat begint in cel A2 op werkblad “Sheet2”.
Hoe combineer ik meerdere spillbereiken?
Gebruik STAPELV (verticaal stapelen) of STAPELH (horizontaal stapelen):
=STAPELV(Blad1!A2#;Blad2!A2#)
Dit combineert twee spillbereiken tot een nieuwe dynamische array.
Zijn dynamische matrices sneller dan oude CSE-formules?
In de meeste gevallen ja. Dynamische matrices zijn geoptimaliseerd voor de Excel 365-rekenengine. Bij zeer grote datasets (100.000+ rijen) kunnen beide methoden traag zijn, maar dynamische matrices bieden betere schaalbaarheid.
Hulp nodig bij Excel?
Loop je vast met dynamische matrices, spillbereiken of de #-operator? Wij helpen je graag verder met persoonlijk advies.
Direct contact:
– WhatsApp: Stuur een bericht
– E-mail: w.bouwmeester@bouwmeesterconsultancy.nl
– Telefoon: +31 6 28963636
Stuur je Excel-bestand mee en beschrijf kort wat je wilt bereiken. We reageren meestal binnen 24 uur.
