Samenvatting
Een #SPILL-fout treedt op wanneer een overloopbereik wordt geblokkeerd door iets op het werkblad. De oplossing is meestal om het morsbereik van eventuele belemmerende gegevens te verwijderen. Zie hieronder voor meer informatie en stappen om op te lossen.
Uitleg
Over morsen en de #SPILL! fout
Met de introductie van Dynamic Arrays in Excel, "morsen" formules die meerdere waarden retourneren deze waarden rechtstreeks naar het werkblad. De rechthoek die de waarden omsluit, wordt het "spill-bereik" genoemd. Wanneer gegevens veranderen, wordt het spill-bereik naar behoefte groter of kleiner. Mogelijk ziet u dat er nieuwe waarden worden toegevoegd of dat bestaande waarden verdwijnen.
Video: Morsen en het overloopbereik
Een #SPILL-fout treedt op wanneer een overloopbereik wordt geblokkeerd door iets op het werkblad. Soms wordt dit verwacht. U hebt bijvoorbeeld een formule ingevoerd in de verwachting dat deze zal morsen, maar bestaande gegevens in het werkblad staan in de weg. De oplossing is om alle belemmerende gegevens uit het morsbereik te verwijderen.
Soms is de fout echter onverwacht en daarom verwarrend. Lees hieronder hoe deze fout kan worden veroorzaakt en wat u kunt doen om dit op te lossen.
Morsen is inheems
Het is belangrijk om te begrijpen dat morsen automatisch en native is. In Dynamic Excel (momenteel alleen Office 365 Excel) kan elke formule, zelfs een eenvoudige formule zonder functies, resultaten opleveren. Hoewel er manieren zijn om te voorkomen dat een formule meerdere resultaten retourneert, kan morsen zelf niet worden uitgeschakeld met een algemene instelling.
Evenzo is er geen optie in Excel om "#SPILL-fouten uit te schakelen. Om een #SPILL-fout te herstellen, moet u de hoofdoorzaak van het probleem onderzoeken en oplossen.
Fix # 1 - Ruim het morsbereik op
Dit is het eenvoudigste geval om op te lossen. De formule zou meerdere waarden moeten morsen, maar in plaats daarvan retourneert het #SPILL! omdat er iets in de weg zit. Om de fout op te lossen, selecteert u een cel in het overloopbereik, zodat u de grenzen kunt zien. Verplaats vervolgens de blokkerende gegevens naar een nieuwe locatie of verwijder de gegevens helemaal. Let op: cellen in het overloopbereik moeten leeg zijn, dus let op cellen die onzichtbare tekens bevatten, zoals spaties.
In het onderstaande scherm blokkeert de 'x' het spill-bereik:
Als de "x" is verwijderd, levert de UNIEKE functie normaal resultaat op:
Fix # 2 - voeg @ -teken toe
Vóór Dynamic Arrays paste Excel stilzwijgend een gedrag toe dat "impliciete intersectie" wordt genoemd om ervoor te zorgen dat bepaalde formules met de mogelijkheid om meerdere resultaten te retourneren slechts één resultaat opleverden. In niet-dynamische matrix Excel retourneren deze formules een normaal ogend resultaat zonder fouten. In bepaalde gevallen kan dezelfde formule die is ingevoerd in Dynamic Excel echter een #SPILL-fout genereren. In het onderstaande scherm bevat cel D5 bijvoorbeeld deze formule, naar beneden gekopieerd:
=$B$5:$B$10+3
Deze formule zou geen fout veroorzaken, bijvoorbeeld Excel 2016, omdat impliciete kruising zou voorkomen dat de formule meerdere resultaten retourneert. In Dynamic Excel retourneert de formule echter automatisch meerdere resultaten naar het werkblad en die tegen elkaar botsen, aangezien de formule wordt gekopieerd van D5: D10.
Een oplossing is om het @ -teken te gebruiken om impliciete intersectie als volgt mogelijk te maken:
= @$B$5:$B$10+3
Met deze wijziging retourneert elke formule opnieuw een enkel resultaat en verdwijnt de #SPILL-fout.
Opmerking: dit verklaart gedeeltelijk waarom u plotseling het teken "@" zou kunnen zien verschijnen in formules die zijn gemaakt in oudere versies van Excel. Dit wordt gedaan om de compatibiliteit te behouden. Omdat formules in oudere versies van Excel niet in meerdere cellen kunnen overlopen, wordt de @ toegevoegd om hetzelfde gedrag te garanderen wanneer de formule wordt geopend in Dynamic Excel.
Fix # 3 - native dynamische matrixformule
Een andere (betere) manier om de hierboven getoonde #SPILL-fout op te lossen, is door een native dynamische matrixformule in D5 als volgt te gebruiken:
=B5:B10+3
In Dynamic Excel zal deze enkele formule resultaten opleveren in het bereik D5: D10, zoals te zien is in de onderstaande schermafbeelding:
Merk op dat het niet nodig is om een absolute referentie te gebruiken.
