#OVERLOPEN! fout: breidt zich uit buiten de rand van het werkblad

Van toepassing op

Excel voor Microsoft 365 Excel voor Microsoft 365 voor Mac Excel voor iPad Excel Web App Excel voor iPhone Excel voor Android-tablets Excel voor Android-telefoons

Hulp bij Copilot

De overlopende matrixformule die u wilt invoeren, overschrijdt het bereik van het werkblad. Probeer het opnieuw met een kleiner bereik of een kleinere matrix.

In het volgende voorbeeld wordt de fout opgelost door de formule naar cel F1 te verplaatsen en loopt de formule correct over.

Schermafbeelding met #SPILL fout, waarbij =SORT(D:D) in cel F2 buiten de randen van de werkmap valt. Verplaats het naar cel F1 en het werkt goed.

Veelvoorkomende oorzaak: Volledige kolomverwijzingen

Bij het maken van VERT.ZOEKEN-formules ontstaat een veelvoorkomend misverstand doordat te veel lookup_value argument wordt opgegeven. Vóór Excel die geschikt is voor dynamische matrices , hield Excel alleen rekening met de waarde in dezelfde rij als de formule en werden alle andere waarden genegeerd, omdat VERT.ZOEKEN slechts één waarde verwachtte. Met de introductie van dynamische matrices houdt Excel rekening met alle waarden die aan de lookup_value zijn doorgegeven. Deze wijziging houdt in dat als u een hele kolom opgeeft als het lookup_value argument, wordt geprobeerd alle 1.048.576 waarden in de kolom op te zoeken. Nadat het is voltooid, probeert het ze naar het raster te morsen, en zeer waarschijnlijk wordt het einde van het raster bereikt, wat resulteert in een #SPILL! fout.

Wanneer bijvoorbeeld de formule =VERT.ZOEKEN(A:A,A:C,2,ONWAAR) in cel E2 werd geplaatst, zoals in het volgende voorbeeld, werd voorheen alleen de id in cel A2 opgezocht. In Excel met dynamische matrices veroorzaakt de formule echter een #SPILL! fout, omdat Excel de hele kolom doorzoekt, 1.048.576 resultaten retourneert en het einde van het Excel-raster bereikt.

Schermafbeelding met #SPILL veroorzaakt met =VERT.ZOEKEN(A:A,A:D,2,ONWAAR) in cel E2, omdat de resultaten overlopen buiten de rand van het werkblad. Verplaats de formule naar cel E1 en deze werkt correct.

Gebruik een van de volgende oplossingen om dit probleem op te lossen:

#	Methode	Formule
1	Verwijs alleen naar de opzoekwaarden waarin u geïnteresseerd bent. Deze formulestijl retourneert een dynamische matrix, maar werkt niet met Excel-tabellen.	=VERT.ZOEKEN(A2:A7;A:C;2;ONWAAR)
2	Verwijs alleen naar de waarde op dezelfde rij en kopieer de formule vervolgens omlaag. Deze traditionele formulestijl werkt in tabellen, maar retourneert geen dynamische matrix.	=VERT.ZOEKEN(A2;A:C;2;ONWAAR)
3	Vraag of Excel impliciet snijpunt uitvoert met behulp van de @-operator en kopieer vervolgens de formule omlaag. Deze formulestijl werkt in tabellen, maar retourneert geen dynamische matrix.	=VERT.ZOEKEN(@A:A2;A:C;2;ONWAAR)