#OVERLOPEN! fout: breidt zich uit tot 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

De overgelopen matrixformule die u probeert in te voeren, gaat verder dan 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 te verplaatsen naar cel F1 en loopt de formule correct over.

#SPILL! fout waarbij =SORTEREN(D:D) in cel F2 de randen van de werkmap overschrijdt. Verplaats de app naar cel F1 en werkt goed.

Veelvoorkomende oorzaken: Volledige kolomverwijzingen

Er is een vaak verkeerd begrepen methode voor het maken van VERT.ZOEKEN-formules door het argument lookup_value op te geven. Voordat Excel geschikt is voor dynamische matrixen , zou Excel alleen rekening houden met de waarde in dezelfde rij als de formule en alle andere waarden negeren, omdat VERT.ZOEKEN slechts één waarde verwachtte. Met de introductie van dynamische matrices houdt Excel rekening met alle waarden die zijn opgegeven voor de lookup_value. Dit betekent dat als een hele kolom wordt opgegeven als het argument lookup_value, Excel alle 1.048.576 waarden in de kolom opzoekt. Zodra dit is gebeurd, wordt geprobeerd ze naar het raster te morsen en zal het zeer waarschijnlijk aan het einde van het raster komen, wat resulteert in een #SPILL! fout.

Als bijvoorbeeld de formule =VERT.ZOEKEN(A:A;A:C;2;ONWAAR) in cel E2 wordt geplaatst, zoals in het onderstaande voorbeeld, werd voorheen alleen de id in cel A2 opgezocht. In Excel met dynamische matrices veroorzaakt de formule echter een #OVERLOOP!- fout, omdat Excel de hele kolom opzoekt, 1.048.576 resultaten retourneert en naar het einde van het Excel-raster gaat.

#SPILL! fout veroorzaakt met =VERT.ZOEKEN(A:A;A:D;2;ONWAAR) in cel E2, omdat de resultaten buiten de werkbladrand zouden overlopen. Verplaats de formule naar cel E1, waarna deze goed werkt.

U kunt dit probleem op drie eenvoudige manieren verhelpen:

#	Methode	Formule
1	Verwijs slechts 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 geendynamische 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 geendynamische matrix.	=VERT.ZOEKEN(@A:A2;A:C;2;ONWAAR)