Den här artikeln hjälper dig att förstå alla orsaker till #SPILL-fel samt lösningarna för att fixa dem i Excel 365.
#SPILL! är en ny typ av Excel-fel som främst uppstår när en formel som ger flera beräkningsresultat försöker visa sina utdata i ett spillintervall men det intervallet redan innehåller andra data.
Blockeringsdata kan vara vad som helst, inklusive textvärde, sammanslagna celler, ett blanksteg eller till och med när det inte finns tillräckligt med plats för att returnera resultaten. Lösningen är enkel, antingen rensa intervallet för blockerande data eller välj en tom uppsättning celler som inte innehåller någon typ av data i den.
Spillfel inträffar vanligtvis vid beräkning av dynamiska matrisformler, eftersom den dynamiska matrisformeln är den som matar ut resultat i flera celler eller en matris. Låt oss undersöka mer i detalj och förstå vad som utlöser detta fel i Excel och hur man löser det.
Vad orsakar ett spillfel?
Sedan lanseringen av dynamiska arrayer 2018 kan Excel-formler hantera flera värden åt gången och returnera resultat i mer än en cell. Dynamiska arrayer är arrayer som kan ändras storlek som gör att formler kan returnera flera resultat till ett cellintervall på kalkylbladet baserat på en formel som anges i en enda cell.
När en dynamisk matrisformel returnerar flera resultat, kommer dessa resultat automatiskt att spelas in i de närliggande cellerna. Detta beteende kallas "Spill" i Excel. Och intervallet av celler där resultaten rinner ut kallas "Spillintervallet". Spillområdet kommer att utökas eller minska automatiskt baserat på källvärdena.
Om en formel försöker fylla ett spillintervall med flera resultat men blockeras av något i det intervallet, uppstår ett #SPILL-fel.
Excel har nu 9 funktioner som använder Dynamic Array-funktionalitet för att lösa problem, dessa inkluderar:
- SEKVENS
- FILTRERA
- TRANSPONERA
- SORTERA
- SORTERA EFTER
- RANDARRAY
- UNIK
- XLOOKUP
- XMATCH
Dynamiska arrayformler är endast tillgängliga i "Excel 365" och det stöds för närvarande inte av någon av Excel-programvaran offline (dvs. Microsoft Excel 2016, 2019).
Spillfel orsakas inte bara av blockerande data, det finns flera anledningar till varför du kan få #Spill error. Låt oss utforska de olika situationerna där du kan stöta på #SPILL! fel och hur man åtgärdar dem.
Spillområde är inte tomt
En av de främsta orsakerna till spillfel är att spillområdet inte är tomt. Om du till exempel försöker visa 10 resultat, men om det finns data i någon av cellerna i spillområdet, returnerar formeln en #SPILL! fel.
Exempel 1:
I exemplet nedan har vi angett TRANSPOSE-funktionen i cell C2 för att omvandla det vertikala intervallet av celler (B2:B5) till ett horisontellt intervall (C2:F2). Istället för att byta kolumn till en rad visar Excel oss #SPILL! fel.
Och när du klickar på formelcellen kommer du att se en streckad blå kant som indikerar spillområdet/intervallet (C2:F2) som behövs för att visa resultaten som visas nedan. Du kommer också att märka en gul varningsskylt med ett utropstecken på.
För att förstå orsaken till felet, klicka på varningsikonen bredvid felet och se meddelandet på första raden markerat i grått. Som du kan se står det "Spillintervallet är inte tomt" här.
Problemet här är att cellerna i spillområdet D2 och E2 har texttecken (inte tomma), därav felet.
Lösning:
Lösningen är enkel, antingen rensa data (antingen flytta eller ta bort) som finns i spillområdet eller flytta formeln till en annan plats där det inte finns något hinder.
Så snart du tar bort eller flyttar blockeringen kommer Excel automatiskt att fylla i cellerna med resultaten av formeln. Här, när vi rensar texten i D2 och E2, transponerar formeln kolumnen till rad som avsett.
Exempel 2:
I exemplet nedan, även om spillområdet verkar tomt, visar formeln fortfarande Spill! fel. Det beror på att utsläppet faktiskt inte är tomt, det har en osynlig rymdkaraktär i en av cellerna.
Det är svårt att hitta mellanslagstecken eller någon annan osynlig karaktär som gömmer sig i vad som verkar vara tomma celler. För att hitta sådana celler med oönskad data, klicka på Error floatie (varningsskylt) och välj "Select Obstructing Cells" från menyn och den tar dig till cellen som innehåller blockerande data.
Som du kan se, i skärmdumpen nedan, har cell E2 två mellanslagstecken. När du rensar dessa data får du rätt utdata.
Ibland kan det osynliga tecknet vara en text formaterad med samma teckensnittsfärg som cellens fyllnadsfärg eller ett cellvärde anpassat formaterat med nummerkoden ;;;. När du anpassat formaterar ett cellvärde med ;;; kommer det att dölja allt i den cellen, oavsett teckensnittsfärg eller cellfärg.
Spillintervall innehåller sammanslagna celler
Ibland #SPILL! fel uppstår när spillområdet innehåller de sammanslagna cellerna. Dynamisk matrisformel fungerar inte med sammanslagna celler. Allt du behöver göra för att fixa detta är att ta bort celler i spillområdet eller flytta formeln till ett annat område som inte har några sammanslagna celler.
I exemplet nedan, även om spillområdet är tomt (C2:CC8), returnerar formeln Spillfelet. Det beror på att cellerna C4 och C5 är sammanslagna.
För att se till att sammanslagna celler är orsaken till att du får felet, klicka påvarningsskylt och verifiera orsaken – "Spillintervall har sammanfogat cell".
Lösning:
För att ta bort sammanslagningen av cellerna, välj de sammanslagna cellerna och klicka sedan på "Sammanfoga och centrera"-knappen på fliken "Hem" och välj "Avaktivera celler".
Om du har svårt att hitta de sammanslagna cellerna i ditt stora kalkylblad, klicka på alternativet "Välj blockerande celler" från varningsskyltmenyn för att hoppa till de sammanslagna cellerna.
Spillintervall i tabell
Spillda matrisformler stöds inte i Excel-tabeller. Dynamisk matrisformel bör endast anges i en enskild cell. Om du anger en formel för spilld array i en tabell eller när spillområdet faller in i en tabell, skulle du få spillfelet. När detta händer, försök att konvertera tabellen till ett normalt intervall eller flytta formeln utanför tabellen.
Till exempel, när vi anger följande formel för spilld intervall i en Excel-tabell, skulle vi få ett spillfel i varje cell i tabellen, inte bara formelcellen. Det beror på att Excel automatiskt kopierar alla formler som anges i en tabell till varje cell i tabellens kolumn.
Du kommer också att få ett spillfel när en formel försöker spilla resultat i en tabell. I skärmdumpen nedan faller spillområdet inom den befintliga tabellen, så vi får ett spillfel.
För att bekräfta orsaken bakom detta fel, klicka på varningsskylten och se felorsaken - "Utspillsområde i tabell"
Lösning:
För att åtgärda felet måste du återställa Excel-tabellen till intervallet. För att göra det, högerklicka var som helst i tabellen, klicka på "Tabell" och välj sedan alternativet "Konvertera till intervall". Alternativt kan du vänsterklicka var som helst i tabellen, gå sedan till fliken "Borddesign" och välj alternativet "Konvertera till intervall".
Spillintervall är okänt
Om Excel inte kunde fastställa storleken på den utspillda arrayen kommer det att utlösa spillfelet. Ibland gör formeln det möjligt för en dynamisk array att ändra storlek mellan varje beräkningspass. Om storleken på den dynamiska arrayen fortsätter att ändras under beräkningarna passerar och inte balanserar ut, kommer det att orsaka #SPILL! Fel.
Denna typ av spillfel utlöses vanligtvis när du använder flyktiga funktioner som RAND, RANDARRAY, RANDBETWEEN, OFFSET och INDIREKTA funktioner.
Till exempel, när vi använder formeln nedan i cell B3 får vi spillfelet:
=SEKVENS(RANDMELLAN(1, 500))I exemplet returnerar funktionen RANDMELLAN ett slumpmässigt heltal mellan siffrorna 1 och 500, och dess utdata ändras kontinuerligt. Och SEQUENCE-funktionen vet inte hur många värden som ska produceras i en spillarray. Därför #SPILL-felet.
Du kan också bekräfta orsaken till felet genom att klicka på varningstecknet – "Spillintervall är okänt".
Lösning:
För att åtgärda felet för denna formel är ditt enda val att använda en annan formel för din beräkning.
Spillintervallet är för stort
Ibland kan du köra en formel som ger ett utspillt intervall som är för stort för att kalkylbladet ska kunna hantera, och det kan sträcka sig utanför kanterna på kalkylbladet. När det händer kan du få #SPILL! fel. För att åtgärda problemet kan du prova att referera till ett specifikt område eller en cell istället för hela kolumner eller använda tecknet "@" för att aktivera implicit skärningspunkt
I exemplet nedan försöker vi beräkna 20 % av försäljningssiffrorna i kolumn A och returnera resultaten i kolumn B, men istället får vi ett spillfel.
Formeln i B3 beräknar 20 % av värdet i A3, sedan 20 % av värdet i A4, och så vidare. Den ger över en miljon resultat ( 1 048 576) och spelar alla i kolumn B med början i cell B3, men den når slutet av kalkylbladet. Det finns inte tillräckligt med utrymme för att visa alla utgångar, som ett resultat får vi ett #SPILL-fel.
Som du kan se är orsaken till detta fel att "Spillintervallet är för stort".
Lösningar:
För att lösa det här problemet, försök att ändra hela kolumnen med ett relevant intervall eller en encellsreferens, eller lägg till @-operatorn för att utföra implicit skärning.
Fixa 1: Du kan prova att hänvisa intervall istället för hela kolumner. Här ändrar vi hela intervallet A:A med A3:A11 i formeln, och formeln kommer automatiskt att fylla intervallet med resultat.
Fix 2: Ersätt hela kolumnen med bara cellreferensen på samma rad (A3) och kopiera sedan formeln ner i intervallet med hjälp av fyllningshandtaget.
Fix 3: Du kan också försöka lägga till @-operatorn före referensen för att utföra implicit korsning. Detta visar endast utdata i formelcellen.
Kopiera sedan formeln från cell B3 till resten av intervallet.
Notera: När du redigerar en utspilld formel kan du bara redigera den första cellen i spillområdet/intervallet. Du kan se formeln i andra celler i spillområdet, men de kommer att vara nedtonade och kan inte uppdateras.
Slut på minne
Om du kör en utspilld matrisformel som får Excel att ta slut på minne, kan det utlösa #SPILL-felet. Under dessa omständigheter, försök att referera till en mindre array eller intervall.
Okänd / Fallback
Du kan också få ett spillfel även när Excel inte känner igen eller inte kan stämma av orsaken till felet. I sådana fall, dubbelkolla din formel och se till att alla parametrar för funktionerna är korrekta.
Nu vet du alla orsaker och lösningar för #SPILL! fel i Excel 365.