Overloop en dynamische matrices

Wat overlopen is

Normaal geeft één formule één waarde in één cel. Een formule die meerdere waarden teruggeeft, zoals een kolom van 6 btw-bedragen, schrijft ze nu uit over de cellen eronder en ernaast.

Dat is overlopen, en het blok cellen dat daarbij gevuld wordt is het overloopbereik. Een resultaat dat zo van grootte kan veranderen is een dynamische matrix.

Alleen de cel linksboven, waar je de formule typte, bevat de formule. De andere cellen bevatten waarden:

• selecteer je zo'n cel, dan toont de formulebalk de formule in grijs en kun je die niet bewerken
• een dunne blauwe rand markeert het overloopbereik
• verwijder je de formule linksboven, dan verdwijnt de hele overloop
• wijzig je de brongegevens, dan groeit of krimpt de overloop vanzelf

Een matrix is een verzameling waarden die Excel als één geheel behandelt, in rijen en kolommen. Een bereik als B2:B7 is een matrix, een getypte lijst als {1\2\3} ook, en het resultaat van FILTER eveneens. Overlopen is de manier waarop Excel een matrix met meer dan één waarde laat zien.

Welke Excel-versies overlopen

Overlopen kwam met de dynamische matrices van Microsoft 365 en Excel 2021.

Oudere versies lopen niet over: een formule voor meerdere cellen moet je invoeren als matrixformule met Ctrl+Shift+Enter over een blok cellen dat je eerst selecteert, en Excel toont die dan met {accolades} in de formulebalk. Die accolades kun je niet zelf typen.

Controleer de versie voordat je een werkmap naar een cliënt of collega stuurt. Een formule met FILTER of UNIEK toont #NAAM? in een versie die de functie niet kent, en een overloopformule die in een oudere versie opent, wordt getoond als matrixformule over de oude grootte van de overloop, die de gegevens niet meer volgt.

De overloopverwijzing #

Om naar een hele overloop te verwijzen schrijf je het adres van de cel linksboven gevolgd door #. E2# betekent alles wat de formule in E2 op dit moment laat overlopen. Drie voorbeelden:

• =SOM(E2#) telt alles op
• =AANTALARG(E2#) telt hoeveel waarden het er zijn
• in SOMMEN.ALS(C2:C9; A2:A9; E2#) is de lijst een reeks criteria

E2# is altijd precies zo groot als de overloop, dus je telt nooit rijen met de hand en mist er geen.

De operator werkt alleen op de cel met een overloopformule. Wijst hij naar een cel die niet overloopt, dan geeft hij #VERW!.

=SOM(E2#)

=SOM(E2#)

=AANTALARG(E2#)

=AANTALARG(E2#)

=SOMMEN.ALS(C2:C9, A2:A9, E2#)

=SOMMEN.ALS(C2:C9; A2:A9; E2#)

=SOM(E2:E4)

=SOM(E2:E4)

Wat er overloopt

Verschillende functies zijn gemaakt om veel waarden terug te geven. Een normale formule loopt ook over, zodra die een bereik of matrix krijgt op een plek waar een enkele waarde wordt verwacht: =A2:A10*1,21 geeft 9 resultaten, en =LINKS(A2:A10; 4) ook. Een functie die al een bereik optelt, zoals SOM, geeft één waarde en loopt niet over.

Overloopformules kun je nesten. =SORTEREN(UNIEK(A2:A9)) toont elke waarde één keer en zet de lijst op volgorde; het binnenste resultaat is een matrix die de buitenste functie helemaal leest.

=UNIEK(A2:A9)

=UNIEK(A2:A9)

=A2:A10*1.21

=A2:A10*1,21

=X.ZOEKEN(A2:A9, F2:F20, G2:G20)

=X.ZOEKEN(A2:A9; F2:F20; G2:G20)

=TEKST.SPLITSEN(B2, "-")

=TEKST.SPLITSEN(B2; "-")

Als je #OVERLOPEN! krijgt

#OVERLOPEN! betekent dat de formule juist is maar het resultaat geen plek heeft. De fout staat alleen in de cel linksboven. Selecteer die cel en Excel noemt de oorzaak in het waarschuwingsmenu. De vier oorzaken hieronder komen het vaakst voor.

Herstel #OVERLOPEN! niet met ALS.FOUT: dat verbergt de melding dat er iets in de weg staat, en de lijst blijft leeg.

Het @-teken in oudere bestanden

Vóór de dynamische matrices liep een formule die een bereik kreeg op een plek waar één waarde werd verwacht niet over. Excel nam stilzwijgend een waarde uit dezelfde rij of kolom als de formule. Dat is het impliciete snijpunt: =A2:A10*1,21 getypt in D4 betekende A4*1,21.

Opent Microsoft 365 zo'n bestand, dan zet Excel een @ voor het deel dat meerdere waarden kan teruggeven, zoals in =@A2:A10*1,21, zodat de formule dezelfde enkele waarde blijft geven.

Haal je de @ weg, dan loopt de formule over. Doe dat bewust en leg het vast: de cijfers veranderen van één waarde naar een kolom, en de nieuwe overloop kan stuiten op cellen die al gegevens bevatten.

=@A2:A10

=@A2:A10

Matrixconstanten

Een matrixconstante is een lijst vaste waarden die je tussen accolades typt. Met Engelse instellingen scheidt een komma de kolommen en een puntkomma de rijen.

Met Nederlandse instellingen scheidt een backslash de kolommen en scheidt een puntkomma nog steeds de rijen, omdat de komma het decimaalteken is. Excel toont de constante in de stijl van de regio-instelling van de computer.

Een constante kan getallen, tekst tussen aanhalingstekens, WAAR en ONWAAR en foutwaarden bevatten, maar geen celverwijzingen en geen functies.

Een rij en een kolom samen vormen een raster, zoals {0,005\0,01} naast een kolom omzet. Twee kolommen van verschillende lengte laten #N/B achter waar de tegenhanger ontbreekt.

=B2:B4*{0.005,0.01}

=B2:B4*{0,005\0,01}

=X.ZOEKEN(C2:C7, {1,2,3}, {0.21,0.09,0})

=X.ZOEKEN(C2:C7; {1\2\3}; {0,21\0,09\0})

=SOM(AANTAL.ALS(B2:B100, {"MEM","BNK"}))

=SOM(AANTAL.ALS(B2:B100; {"MEM"\"BNK"}))

Verwante onderwerpen

• Celverwijzingen: relatief, absoluut en gemengdEen celverwijzing is het adres dat een formule leest. Dollartekens bepalen of het adres meeschuift als je de formule kopieert, en dat goed doen laat een formule een hele kolom of tabel vullen.
• FoutwaardenEen foutwaarde is een resultaat dat met # begint en aangeeft wat er misging, zoals #N/B of #DEEL/0!. Ze plant zich voort door formules, en wie ze verbergt, kan een gat in het controlebewijs verbergen.
• OperatorenOperatoren zijn de symbolen in een formule waarmee je rekent, vergelijkt, tekst samenvoegt of verwijzingen combineert. Excel past ze in een vaste volgorde toe, wat veel ogenschijnlijk foute uitkomsten verklaart.