Dynamische matrices en gedrag van matrices op aangrenzende cellen

Excel-formules die een set waarden retourneren, ook wel een matrix genoemd, retourneren deze waarden naar aangrenzende cellen. Dit gedrag wordt overlopen genoemd.

Formules die matrices met een variabele grootte als resultaat kunnen geven, worden dynamische matrix -formules genoemd. Formules die op dit moment matrices retourneren die met succes overlopen, kunnen worden aangeduid als overloopmatrix-formules.

Hier volgen een paar notities om u te helpen dit type formules te begrijpen en te gebruiken.

Wat betekent overloop?

Opmerking: Oudere matrixformules, ook wel legacy matrix-formules genoemd, retourneren altijd een resultaat met een vaste grootte - ze lopen altijd over in hetzelfde aantal cellen. Het overloopgedrag dat in dit onderwerp wordt beschreven, is niet van toepassing op legacy matrixformules.

Overloop betekent dat een formule meerdere waarden oplevert en dat die waarden in naburige cellen zijn geplaatst. Met =SORTEREN(D2:D11;1;-1), wordt bijvoorbeeld een matrix in aflopende volgorde gesorteerd, en wordt een bijbehorende matrix geretourneerd die 10 rijen hoog is. Maar u hoeft alleen de formule in de cel linksboven in te voeren, of F2 in dit geval, en deze loopt automatisch over naar cel F11.

Sorteer de waarden in cellen D2:D11 met =SORTEREN(D2:D11;1;-1)

Belangrijke punten

Wanneer u op ENTER drukt om de formule te bevestigen, wordt het formaat van het uitvoerbereik dynamisch aangepast en worden de resultaten in elke cel in dat bereik ingevoegd.
Als u een dynamische matrixformule schrijft om toe te passen op een lijst met gegevens, kan het handig zijn om deze op te nemen in een Excel-tabel. Gebruik vervolgens gestructureerde verwijzingen om te verwijzen naar de gegevens. De reden hiervoor is dat gestructureerde verwijzingen automatisch worden aangepast wanneer er rijen worden toegevoegd aan of verwijderd uit de tabel.
Overgelopen matrixformules worden niet ondersteund in de Excel-tabellen zelf. U moet ze dus in het raster plaatsen buiten de tabel. Tabellen zijn het meest geschikt voor het bewaren van rijen en kolommen met onafhankelijke gegevens.
Nadat u een overgelopen matrixformule hebt ingevoerd en een cel binnen het overgelopen gebied selecteert, plaatst Excel een gemarkeerde rand rond het bereik. De rand verdwijnt als u een cel buiten het gebied selecteert.
Alleen de eerste cel in het overloopgebied kan worden bewerkt. Als u een andere cel in het overloopgebied selecteert, wordt de formule weergegeven op de formulebalk, maar de tekst is 'gedupliceerd' en kan niet worden gewijzigd. Als u de formule moet bijwerken, selecteert u de cel linksboven in het matrixbereik en wijzigt u deze indien nodig. Excel zal automatisch de rest van het overloopgebied voor u bijwerken wanneer u op ENTER drukt.
Overlappende formules: matrixformules kunnen niet worden ingevoerd als er iets is dat het uitvoerbereik blokkeert. en als dit gebeurt, retourneert Excel een #OVERLOOP!-fout die aangeeft dat er een blokkering is. Als u de blokkering verwijdert, loopt de formule over zoals verwacht. In het onderstaande voorbeeld overlapt het uitvoerbereik van de formule een ander bereik met gegevens en wordt het weergegeven met een gestippelde rand die cellen overlapt met waarden die aangeven dat het niet kan overlopen. Verwijder de blokkerende gegevens of kopieer deze ergens anders en de formule zal overlopen zoals verwacht.
Oudere matrixformules die zijn ingevoerd via CTRL+SHIFT+ENTER (CSE) worden nog steeds ondersteund om redenen van compatibiliteit, maar moeten niet langer worden gebruikt. Als u wilt, kunt u oudere matrixformules omzetten in dynamische matrixformules door de eerste cel in het matrixbereik te lokaliseren, de tekst van de formule te kopiëren, het hele bereik van de oude matrix te verwijderen en de formule bovenaan opnieuw in te voeren in de cel linksboven. Voordat u oudere matrixformules opwaardeert naar dynamische matrixformules, moet u rekening houden met enkele rekenverschillen tussen de twee.
Excel biedt beperkte ondersteuning voor dynamische matrices tussen werkmappen. Als u de bronwerkmap sluit, wordt voor gekoppelde dynamische matrixformules een #REF!-fout weergegeven wanneer ze worden vernieuwd.