Dit artikel helpt u alle oorzaken van #SPILL-fouten te begrijpen, evenals de oplossingen om ze in Excel 365 op te lossen.
#SPIL! is een nieuw soort Excel-fout die voornamelijk optreedt wanneer een formule die meerdere berekeningsresultaten produceert, probeert de uitvoer in een overloopbereik weer te geven, maar dat bereik al enkele andere gegevens bevat.
De blokkerende gegevens kunnen van alles zijn, inclusief tekstwaarde, samengevoegde cellen, een gewoon spatieteken of zelfs wanneer er niet genoeg plaats is om de resultaten te retourneren. De oplossing is eenvoudig: wis het bereik van blokkerende gegevens of selecteer een lege reeks cellen die geen enkel type gegevens bevatten.
Morsfouten treden meestal op bij het berekenen van dynamische matrixformules, omdat de dynamische matrixformule degene is die resultaten in meerdere cellen of een matrix uitvoert. Laten we in meer detail kijken en begrijpen wat deze fout in Excel veroorzaakt en hoe deze op te lossen.
Wat veroorzaakt een lekkagefout?
Sinds de lancering van dynamische arrays in 2018, kunnen Excel-formules meerdere waarden tegelijk verwerken en resultaten in meer dan één cel retourneren. Dynamische matrices zijn aanpasbare matrices waarmee formules meerdere resultaten kunnen retourneren naar een celbereik op het werkblad op basis van een formule die in een enkele cel is ingevoerd.
Wanneer een dynamische matrixformule meerdere resultaten retourneert, worden deze resultaten automatisch naar de aangrenzende cellen overgebracht. Dit gedrag wordt in Excel 'morsen' genoemd. En het bereik van cellen waar de resultaten in terechtkomen, wordt het 'Spill-bereik' genoemd. Het verspillingsbereik wordt automatisch groter of kleiner op basis van de bronwaarden.
Als een formule een morsbereik probeert te vullen met meerdere resultaten, maar wordt geblokkeerd door iets in dat bereik, treedt er een #SPILL-fout op.
Excel heeft nu 9 functies die Dynamic Array-functionaliteit gebruiken om problemen op te lossen, waaronder:
- VOLGORDE
- FILTER
- TRANSPEREN
- SOORT
- SORTEER OP
- RANDARRAY
- UNIEK
- XZOEKEN
- XMATCH
Dynamische matrixformules zijn alleen beschikbaar in 'Excel 365' en worden momenteel niet ondersteund door de offline Excel-software (bijv. Microsoft Excel 2016, 2019).
Morsfouten worden niet alleen veroorzaakt door het belemmeren van gegevens, er zijn verschillende redenen waarom u een #Spill-fout kunt krijgen. Laten we de verschillende situaties onderzoeken waarin u de #SPILL kunt tegenkomen! fout en hoe u deze kunt oplossen.
Morsbereik is niet leeg
Een van de belangrijkste oorzaken van overloopfouten is dat het overloopbereik niet leeg is. Als u bijvoorbeeld 10 resultaten probeert weer te geven, maar als er gegevens in een van de cellen in het overloopgebied staan, retourneert de formule een #SPILL! fout.
Voorbeeld 1:
In het onderstaande voorbeeld hebben we de TRANSPOSE-functie in cel C2 ingevoerd om het verticale bereik van cellen (B2:B5) om te zetten in een horizontaal bereik (C2:F2). In plaats van de kolom naar een rij te schakelen, toont Excel ons de #SPILL! fout.
En wanneer u op de formulecel klikt, ziet u een gestippelde blauwe rand die het overloopgebied/bereik (C2:F2) aangeeft dat nodig is om de resultaten weer te geven zoals hieronder weergegeven. Ook ziet u een geel waarschuwingsbord met een uitroepteken erop.
Om de reden achter de fout te begrijpen, klikt u op het waarschuwingspictogram naast de fout en ziet u het bericht in de eerste regel grijs gemarkeerd. Zoals je kunt zien, staat hier 'Leveringsbereik is niet leeg'.
Het probleem hier is dat de cellen in het overloopbereik D2 en E2 teksttekens hebben (niet leeg), vandaar de fout.
Oplossing:
De oplossing is eenvoudig: ofwel de gegevens wissen (verplaatsen of verwijderen) die zich in het overloopbereik bevinden, ofwel de formule verplaatsen naar een andere locatie waar er geen belemmering is.
Zodra u de blokkade verwijdert of verplaatst, vult Excel automatisch de cellen met de resultaten van de formule. Hier, wanneer we de tekst in D2 en E2 wissen, transponeert de formule de kolom naar rij zoals bedoeld.
Voorbeeld 2:
In het onderstaande voorbeeld, hoewel het morsbereik leeg lijkt, toont de formule nog steeds het morsen! fout. Het is omdat de lekkage niet echt leeg is, maar een onzichtbaar spatiekarakter heeft in een van de cellen.
Het is moeilijk om spatietekens of andere onzichtbare tekens te vinden die zich verbergen in wat lijkt op lege cellen. Om dergelijke cellen met ongewenste gegevens te vinden, klikt u op de fout floatie (waarschuwingsteken) en selecteert u 'Beperkende cellen selecteren' in het menu en u gaat naar de cel die de belemmerende gegevens bevat.
Zoals u kunt zien, heeft cel E2 in de onderstaande schermafbeelding twee spatietekens. Wanneer u die gegevens wist, krijgt u de juiste uitvoer.
Soms kan het onzichtbare teken een tekst zijn die is opgemaakt met dezelfde letterkleur als de vulkleur van de cel of een celwaarde die is opgemaakt met de cijfercode ;;;. Wanneer u een celwaarde op maat maakt met ;;;, wordt alles in die cel verborgen, ongeacht de letterkleur of celkleur.
Morsbereik bevat samengevoegde cellen
Soms, de #SPILL! er treedt een fout op wanneer het overloopbereik de samengevoegde cellen bevat. Dynamische matrixformule werkt niet met samengevoegde cellen. Om dit op te lossen, hoeft u alleen maar cellen in het overloopbereik op te heffen of de formule naar een ander bereik zonder samengevoegde cellen te verplaatsen.
In het onderstaande voorbeeld, ook al is het morsbereik leeg (C2:CC8), retourneert de formule de morsfout. Dat komt omdat de cellen C4 en C5 zijn samengevoegd.
Om er zeker van te zijn dat samengevoegde cellen de reden zijn dat u de fout krijgt, klikt u op dewaarschuwingsbord en verifieer de oorzaak - 'Spill range has merged cell'.
Oplossing:
Om de samenvoeging van de cellen ongedaan te maken, selecteert u de samengevoegde cellen en klikt u vervolgens op het tabblad 'Start' op de knop 'Samenvoegen en centreren' en selecteert u 'Cellen opheffen'.
Als u moeite heeft om de samengevoegde cellen in uw grote spreadsheet te vinden, klikt u op de optie 'Beperkende cellen selecteren' in het waarschuwingsbordmenu om naar de samengevoegde cellen te gaan.
Morsbereik in tabel
Gemorste matrixformules worden niet ondersteund in Excel-tabellen. Dynamische matrixformule mag alleen in een enkele afzonderlijke cel worden ingevoerd. Als u een gemorste matrixformule in een tabel invoert of wanneer het overloopgebied in een tabel valt, krijgt u de Morsfout. Als dit gebeurt, probeer dan de tabel te converteren naar een normaal bereik of verplaats de formule buiten de tabel.
Als we bijvoorbeeld de volgende formule voor gemorst bereik invoeren in een Excel-tabel, krijgen we een Morsfout in elke cel van de tabel, niet alleen in de formulecel. Dit komt omdat Excel automatisch elke formule die in een tabel is ingevoerd, kopieert naar elke cel in de kolom van de tabel.
U krijgt ook een morsfout wanneer een formule probeert resultaten in een tabel te morsen. In de onderstaande schermafbeelding valt het morsgebied binnen de bestaande tabel, dus we krijgen een Spill-fout.
Om de oorzaak achter deze fout te bevestigen, klikt u op het waarschuwingsbord en ziet u de oorzaak van de fout - 'Gemorste hoeveelheid in tabel'
Oplossing:
Om de fout op te lossen, moet u de Excel-tabel terugzetten naar het bereik. Om dat te doen, klikt u met de rechtermuisknop ergens in de tabel, klikt u op 'Tabel' en selecteert u vervolgens de optie 'Converteren naar bereik'. U kunt ook met de linkermuisknop ergens in de tabel klikken, vervolgens naar het tabblad 'Tabelontwerp' gaan en de optie 'Converteren naar bereik' selecteren.
Morsbereik is onbekend
Als Excel de grootte van de gemorste array niet kon vaststellen, wordt de Spill-fout geactiveerd. Soms zorgt de formule ervoor dat een dynamische array tussen elke berekeningsronde van formaat kan worden gewijzigd. Als de grootte van de dynamische array tijdens de berekeningen blijft veranderen en niet in evenwicht is, zal de #SPILL! Fout.
Dit type Spill-fout wordt meestal geactiveerd bij gebruik van vluchtige functies zoals RAND-, RANDARRAY-, RANDBETWEEN-, OFFSET- en INDIRECT-functies.
Als we bijvoorbeeld de onderstaande formule in cel B3 gebruiken, krijgen we de fout Spill:
=VOLGORDE(ASELECTTUSSEN (1, 500))In het voorbeeld retourneert de functie RANDBETWEEN een willekeurig geheel getal tussen de getallen 1 en 500, en de uitvoer verandert voortdurend. En de SEQUENCE-functie weet niet hoeveel waarden er in een spill-array moeten worden geproduceerd. Vandaar de fout #SPILL.
U kunt de oorzaak van de fout ook bevestigen door op het waarschuwingsbord 'Leveringsbereik is onbekend' te klikken.
Oplossing:
Om de fout voor deze formule op te lossen, is uw enige keuze om een andere formule voor uw berekening te gebruiken.
Morsbereik is te groot
Soms kunt u een formule uitvoeren die een gemorst bereik oplevert dat te groot is om door het werkblad te worden verwerkt, en het kan verder reiken dan de randen van het werkblad. Wanneer dat gebeurt, kunt u #SPILL krijgen! fout. Om dit probleem op te lossen, kunt u proberen te verwijzen naar een specifiek bereik of één cel in plaats van hele kolommen of het '@'-teken gebruiken om impliciete kruising in te schakelen
In het onderstaande voorbeeld proberen we 20% van de verkoopcijfers in kolom A te berekenen en de resultaten in kolom B te retourneren, maar in plaats daarvan krijgen we een Spill-fout.
De formule in B3 berekent 20% van de waarde in A3, dan 20% van de waarde in A4, enzovoort. Het produceert meer dan een miljoen resultaten (1.048.576) en morst ze allemaal in kolom B, beginnend in cel B3, maar het zal het einde van het werkblad bereiken. Er is niet genoeg ruimte om alle uitgangen weer te geven, met als resultaat dat we een #SPILL-foutmelding krijgen.
Zoals u kunt zien, is de oorzaak van deze fout dat het - 'Spill-bereik is te groot'.
Oplossingen:
Om dit probleem op te lossen, probeert u de hele kolom te wijzigen met een relevant bereik of een enkele celverwijzing, of voegt u de @-operator toe om een impliciete kruising uit te voeren.
1 . repareren: u kunt proberen om bereiken te verwijzen in plaats van hele kolommen. Hier veranderen we het hele bereik A:A met A3:A11 in de formule, en de formule vult automatisch het bereik met resultaten.
oplossing 2: Vervang de hele kolom door alleen de celverwijzing in dezelfde rij (A3) en kopieer de formule vervolgens door het bereik met behulp van de vulgreep.
repareren 3: U kunt ook proberen de operator @ vóór de verwijzing toe te voegen om een impliciete doorsnijding uit te voeren. Hierdoor wordt de uitvoer alleen in de formulecel weergegeven.
Kopieer vervolgens de formule van cel B3 naar de rest van het bereik.
Opmerking: Wanneer u een gemorste formule aan het bewerken bent, kunt u alleen de eerste cel in het gemorste gebied/bereik bewerken. U kunt de formule in andere cellen van het overloopbereik zien, maar ze worden grijs weergegeven en kunnen niet worden bijgewerkt.
Geen geheugen meer
Als u een gemorste matrixformule uitvoert waardoor Excel onvoldoende geheugen heeft, kan dit de #SPILL-fout veroorzaken. Probeer onder die omstandigheden te verwijzen naar een kleinere array of kleiner bereik.
Niet herkend / terugval
U kunt ook een Spill-fout krijgen, zelfs als Excel de oorzaak van de fout niet herkent of niet kan verzoenen. Controleer in dergelijke gevallen nogmaals uw formule en zorg ervoor dat alle parameters van de functies correct zijn.
Nu kent u alle oorzaken en oplossingen voor #SPILL! fouten in Excel365.