Denne artikel hjælper dig med at forstå alle årsagerne til #SPILL-fejl samt løsningerne til at rette dem i Excel 365.
#SPILDE! er en ny form for Excel-fejl, der hovedsageligt opstår, når en formel, der producerer flere beregningsresultater, forsøger at vise sine output i et spildområde, men det område allerede indeholder nogle andre data.
De blokerende data kan være alt, herunder tekstværdi, flettede celler, et almindeligt mellemrum, eller endda når der ikke er nok plads til at returnere resultaterne. Løsningen er enkel, enten ryd intervallet for eventuelle blokerende data eller vælg et tomt array af celler, der ikke indeholder nogen form for data i det.
Spildfejl opstår normalt ved beregning af dynamiske matrixformler, fordi den dynamiske matrixformel er den, der udsender resultater til flere celler eller en matrix. Lad os se nærmere på og forstå, hvad der udløser denne fejl i Excel, og hvordan man løser det.
Hvad forårsager en spildfejl?
Siden lanceringen af dynamiske arrays i 2018, kan Excel-formler håndtere flere værdier ad gangen og returnere resultater i mere end én celle. Dynamiske arrays er arrays, der kan ændres størrelse, og som gør det muligt for formler at returnere flere resultater til en række celler på regnearket baseret på en formel, der er indtastet i en enkelt celle.
Når en dynamisk matrixformel returnerer flere resultater, overføres disse resultater automatisk til de tilstødende celler. Denne adfærd kaldes 'spild' i Excel. Og rækken af celler, hvor resultaterne vælter ind, kaldes 'spildområdet'. Spildområdet udvides eller trækkes automatisk sammen baseret på kildeværdierne.
Hvis en formel forsøger at udfylde et spildområde med flere resultater, men er blokeret af noget på det område, opstår der en #SPILL-fejl.
Excel har nu 9 funktioner, der bruger Dynamic Array-funktionalitet til at løse problemer, disse inkluderer:
- SEKVENS
- FILTER
- TRANSPOSERER
- SORTERE
- SORTER EFTER
- RANDARRAY
- ENESTÅENDE
- XLOOKUP
- XMATCH
Dynamiske matrixformler er kun tilgængelige i 'Excel 365', og det understøttes i øjeblikket ikke af nogen af de offline Excel-software (dvs. Microsoft Excel 2016, 2019).
Spildfejl er ikke kun forårsaget af at blokere data, der er flere grunde til, at du kan få #Spill fejl. Lad os udforske de forskellige situationer, hvor du kan støde på #SPILLET! fejl og hvordan de rettes.
Spildområde er ikke tomt
En af de primære årsager til spildfejl er, at spildområdet ikke er tomt. For eksempel, hvis du prøver at vise 10 resultater, men hvis der er data i nogen af cellerne i spildområdet, returnerer formlen #SPILL! fejl.
Eksempel 1:
I eksemplet nedenfor har vi indtastet TRANSPOSER-funktionen i celle C2 for at konvertere det lodrette område af celler (B2:B5) til et vandret område (C2:F2). I stedet for at skifte kolonnen til en række, viser Excel os #SPILL! fejl.
Og når du klikker på formelcellen, vil du se en stiplet blå kant, der angiver det spildområde/område (C2:F2), der er nødvendigt for at vise resultaterne som vist nedenfor. Du vil også bemærke et gult advarselsskilt med et udråbstegn på.
For at forstå årsagen til fejlen skal du klikke på advarselsikonet ved siden af fejlen og se meddelelsen i den første linje fremhævet med gråt. Som du kan se, står der 'Spill range isn't blank' her.
Problemet her er, at cellerne i spildområdet D2 og E2 har teksttegn (ikke tomme), og derfor fejlen.
Opløsning:
Løsningen er enkel, enten ryd dataene (enten flyt eller slet) placeret i spildområdet eller flyt formlen til et andet sted, hvor der ikke er nogen hindring.
Så snart du sletter eller flytter blokeringen, vil Excel automatisk udfylde cellerne med resultaterne af formlen. Her, når vi rydder teksten i D2 og E2, transponerer formlen kolonnen til række efter hensigten.
Eksempel 2:
I eksemplet nedenfor, selvom spildområdet ser ud til at være tomt, viser formlen stadig Spild! fejl. Det er fordi spildet faktisk ikke er tomt, det har en usynlig mellemrumskarakter i en af cellerne.
Det er svært at finde mellemrumstegn eller andre usynlige tegn, der gemmer sig i, hvad der ser ud til at være tomme celler. For at finde sådanne celler med uønskede data, skal du klikke på Error floatie (advarselstegn) og vælge 'Select Obstructing Cells' fra menuen, og det vil tage dig til den celle, der indeholder de blokerende data.
Som du kan se, i nedenstående skærmbillede, har celle E2 to mellemrumstegn. Når du rydder disse data, får du det korrekte output.
Nogle gange kan det usynlige tegn være en tekst, der er formateret med samme skriftfarve som cellens fyldfarve eller en celleværdi, der er tilpasset formateret med talkoden ;;;. Når du brugerdefineret formaterer en celleværdi med ;;;, vil den skjule alt i den pågældende celle, uanset skriftfarve eller cellefarve.
Spildområde indeholder flettede celler
Nogle gange kan #SPILT! fejl opstår, når spildområdet indeholder de flettede celler. Dynamisk matrixformel virker ikke med flettede celler. For at rette op på dette skal du blot fjerne fletningen af celler i spildområdet eller flytte formlen til et andet område, der ikke har nogen flettede celler.
I eksemplet nedenfor, selvom spildområdet er tomt (C2:CC8), returnerer formlen spildfejlen. Det er fordi cellerne C4 og C5 er slået sammen.
For at sikre, at flettede celler er årsagen til, at du får fejlen, skal du klikke påadvarselsskilt og bekræft årsagen - 'Udslipsområde har sammenflettet celle'.
Opløsning:
For at ophæve fletningen af cellerne skal du vælge de flettede celler og derefter på fanen "Hjem" klikke på knappen "Flet og centrere" og vælge "Fjern fletning af celler".
Hvis du har svært ved at finde de flettede celler i dit store regneark, skal du klikke på valgmuligheden 'Vælg blokerende celler' fra advarselsskiltmenuen for at hoppe til de flettede celler.
Spildområde i tabel
Spildte matrixformler understøttes ikke i Excel-tabeller. Dynamisk matrixformel bør kun indtastes i en enkelt individuel celle. Hvis du indtaster en formel for spildt array i en tabel, eller når spildområdet falder ind i en tabel, vil du få spildfejlen. Når dette sker, kan du prøve at konvertere tabellen til et normalt område eller flytte formlen uden for tabellen.
For eksempel, når vi indtaster følgende spildte områdeformel i en Excel-tabel, vil vi få en Spild-fejl i hver celle i tabellen, ikke kun formelcellen. Det er fordi Excel automatisk kopierer enhver formel, der er indtastet i en tabel, til hver celle i tabellens kolonne.
Du får også en spildfejl, når en formel forsøger at spilde resultater i en tabel. I nedenstående skærmbillede falder spildområdet inden for den eksisterende tabel, så vi får en Spild-fejl.
For at bekræfte årsagen bag denne fejl, klik på advarselsskiltet og se fejlårsagen - 'Udslipsområde i tabel'
Opløsning:
For at rette fejlen skal du vende Excel-tabellen tilbage til området. For at gøre det skal du højreklikke hvor som helst i tabellen, klikke på 'Tabel' og derefter vælge indstillingen 'Konverter til område'. Alternativt kan du venstreklikke hvor som helst i tabellen og derefter gå til fanen 'Tabeldesign' og vælge indstillingen 'Konverter til område'.
Spildområde er ukendt
Hvis Excel ikke var i stand til at fastslå størrelsen af det spildte array, vil det udløse spildfejlen. Nogle gange gør formlen det muligt for et dynamisk array at ændre størrelsen mellem hver beregningsgennemgang. Hvis størrelsen af det dynamiske array bliver ved med at ændre sig under udregninger og ikke balancerer, vil det forårsage #SPILL! Fejl.
Denne type spildfejl udløses normalt ved brug af flygtige funktioner såsom RAND, RANDARRAY, RANDBETWEEN, OFFSET og INDIREKTE funktioner.
For eksempel, når vi bruger nedenstående formel i celle B3, får vi spildfejlen:
=SEKVENS(RANDMELLEM(1; 500))I eksemplet returnerer funktionen RANDMELLEM et tilfældigt heltal mellem tallene 1 og 500, og dets output ændrer sig konstant. Og SEQUENCE-funktionen ved ikke, hvor mange værdier der skal produceres i et spild-array. Derfor #SPILL fejlen.
Du kan også bekræfte årsagen til fejlen ved at klikke på advarselstegnet – 'Udslipsområde er ukendt'.
Opløsning:
For at rette fejlen for denne formel er dit eneste valg at bruge en anden formel til din beregning.
Spildområdet er for stort
Til tider kan du udføre en formel, der udsender et spildt område, der er for stort til, at regnearket kan håndtere, og det kan strække sig ud over kanterne af regnearket. Når det sker, kan du få #SPILL! fejl. For at løse dette problem kan du prøve at henvise til et bestemt område eller én celle i stedet for hele kolonner eller bruge tegnet '@' for at aktivere implicit skæringspunkt
I eksemplet nedenfor forsøger vi at beregne 20 % af salgstallene i kolonne A og returnere resultaterne i kolonne B, men i stedet får vi en spildfejl.
Formlen i B3 beregner 20% af værdien i A3, derefter 20% af værdien i A4, og så videre. Det producerer over en million resultater ( 1.048.576) og spilder dem alle i kolonne B startende i celle B3, men det vil nå slutningen af regnearket. Der er ikke plads nok til at vise alle output, som et resultat får vi en #SPILL fejl.
Som du kan se, er årsagen til denne fejl, at "spildområdet er for stort".
Løsninger:
For at løse dette problem skal du prøve at ændre hele kolonnen med et relevant område eller en enkeltcelle-reference, eller tilføje @-operatoren for at udføre implicit skæring.
Rette 1: Du kan prøve at henvise intervaller i stedet for hele kolonner. Her ændrer vi hele området A:A med A3:A11 i formlen, og formlen vil automatisk udfylde området med resultater.
Ret 2: Erstat hele kolonnen med kun cellereferencen i den samme række (A3), og kopier derefter formlen ned i området ved hjælp af udfyldningshåndtaget.
Ret 3: Du kan også prøve at tilføje @-operatoren før referencen for at udføre implicit skæring. Dette vil kun vise output i formelcellen.
Kopier derefter formlen fra celle B3 til resten af området.
Bemærk: Når du redigerer en spildt formel, kan du kun redigere den første celle i spildområdet/-området. Du kan se formlen i andre celler i spildområdet, men de bliver nedtonet og kan ikke opdateres.
Ikke mere hukommelse
Hvis du udfører en spildt matrixformel, der får Excel til at løbe tør for hukommelse, kan det udløse #SPILL-fejlen. Under disse omstændigheder kan du prøve at henvise til et mindre array eller område.
Ikke genkendt / Fallback
Du kan også få en Spild-fejl, selvom Excel ikke genkender eller ikke kan afstemme årsagen til fejlen. I sådanne tilfælde skal du dobbelttjekke din formel og sikre dig, at alle parametre for funktionerne er korrekte.
Nu kender du alle årsagerne og løsningerne til #SPIL! fejl i Excel 365.