Eén formule, veel resultaten: lijsten die overlopen naar de cellen eronder.
Moderne Excel en snelheid · 5 korte oefeningen in een live spreadsheet: je typt elke formule zelf en beantwoord vragen die je dwingen resultaten te voorspellen of bugs te vinden. Elk antwoord wordt direct gecontroleerd.
Sommige functies geven meerdere waarden terug. Ze *lopen over* naar de cellen eronder, automatisch. UNIEK (Engels: UNIQUE) geeft elke waarde één keer terug.
Met dynamische matrices kan één formule veel waarden teruggeven, en die vullen automatisch de cellen eronder of ernaast. Dit heet overlopen. Het zet veel oude taken van meerdere stappen, zoals het bouwen van een gesorteerde lijst met unieke waarden, om in één formule.
Eén formule, veel cellen
Stel je een lijst van een dierenasiel voor: A2:A7 bevat het diertype (Hond, Kat, Hond, Konijn, Kat, Hond) en B2:B7 de leeftijd (3, 5, 2, 1, 7, 4). Typ =UNIEK(A2:A7) in D2 en Excel vult D2:D4 met Hond, Kat en Konijn. Je hebt maar één formule getypt. De gekleurde omlijning rond D2:D4 is het overloopbereik.
Verwijs naar het hele overloopbereik met het #-teken: =AANTALARG(D2#) telt hoeveel items er nu zijn, ook als de data later verandert.
Leeftijden van oud naar jong (7, 5, 4, 3, 2, 1). Dieren ouder dan 3 (Kat, Kat, Hond). Verschillende diertypen in alfabetische volgorde (Hond, Kat, Konijn).
SORTEREN (Engels: SORT) neemt het bereik, de kolom waarop je sorteert, en 1 voor oplopend of -1 voor aflopend. Om de hele tabel op leeftijd te sorteren, geef je beide kolommen: =SORTEREN(A2:B7;2;-1). UNIEK (Engels: UNIQUE) geeft elke waarde één keer terug. FILTER houdt de rijen waarvoor de test TRUE is.
Voor twee voorwaarden vermenigvuldig je de tests, wat EN betekent: =FILTER(B2:B7;(A2:A7="Hond")*(B2:B7>2)) geeft de leeftijden 3 en 4 terug. Als niets past, geeft FILTER #CALC!. Voeg een derde argument toe om zelf een bericht te kiezen: =FILTER(A2:A7;B2:B7>10;"geen").
Functies aan elkaar koppelen
Omdat elke functie een lijst teruggeeft, kun je de ene in de volgende voeren. Begin weer met de dierenlijst (A2:A7 dier, B2:B7 leeftijd). Om alleen de dieren ouder dan 2 te krijgen, de oudste eerst, met beide kolommen zichtbaar, nest je het filter in een sortering:
=SORTEREN(FILTER(A2:B7;B2:B7>2);2;-1)
Het filter houdt vier rijen over (Hond 3, Kat 5, Kat 7, Hond 4). De sortering zet ze daarna op de tweede kolom, grootste eerst: Kat 7, Kat 5, Hond 4, Hond 3.
Lees geneste formules van binnen naar buiten: eerst het filter, daarna de sortering. Als een lange keten moeilijk te volgen is, bouw hem dan stap voor stap in aparte cellen en combineer ze pas als elke stap klopt.
De fout #SPILL!
#SPILL! betekent dat de cellen waar het resultaat naartoe wil niet leeg zijn. Maak de blokkerende cel leeg, of verplaats de formule. Dezelfde fout verschijnt als je een overlopende formule in een Excel-tabel zet, omdat tabellen geen overloop kunnen bevatten. Houd overlopende formules daarom naast de tabel.
Oude bestanden en het @-teken
In een werkmap die in een oudere Excel is gemaakt, kun je een @-teken in een formule zien. Het markeert het oude gedrag van één waarde, impliciete snijding genoemd. Excel voegt het toe zodat het bestand precies dezelfde antwoorden blijft geven als altijd. In een nieuwe formule heb je het meestal niet nodig: schrijf de formule zonder @ en laat hem overlopen.
Tips
Dynamische matrices bestaan in Excel 2021, Excel 2024 en Microsoft 365. Oudere versies tonen #NAME?.
Typ niets in een overloopbereik. Je kunt alleen de formule in de linkerbovencel bewerken. Wil je in plaats daarvan vaste waarden, kopieer het overloopbereik en plak het als waarden.
Combineer ze: =AANTALARG(UNIEK(A2:A7)) geeft 3, het aantal verschillende diertypen.