Hva er #SPILL-feil i Excel og hvordan fikser jeg det?

Denne artikkelen vil hjelpe deg å forstå alle årsakene til #SPILL-feil, samt løsningene for å fikse dem i Excel 365.

#SØLE! er en ny type Excel-feil som hovedsakelig oppstår når en formel som produserer flere beregningsresultater prøver å vise sine utdata i et spillområde, men det området allerede inneholder noen andre data.

Blokkeringsdataene kan være alt inkludert tekstverdi, sammenslåtte celler, et vanlig mellomromstegn, eller til og med når det ikke er nok plass til å returnere resultatene. Løsningen er enkel, enten fjern området for blokkeringsdata eller velg en tom rekke celler som ikke inneholder noen type data.

Spillfeil oppstår vanligvis ved beregning av dynamiske matriseformler, fordi den dynamiske matriseformelen er den som gir resultater til flere celler eller en matrise. La oss se nærmere på og forstå hva som utløser denne feilen i Excel og hvordan du løser den.

Hva forårsaker en utslippsfeil?

Siden lanseringen av dynamiske matriser i 2018, kan Excel-formler håndtere flere verdier om gangen og returnere resultater i mer enn én celle. Dynamiske matriser er matriser som kan endre størrelsen og lar formler returnere flere resultater til en rekke celler på regnearket basert på en formel som er angitt i en enkelt celle.

Når en dynamisk matriseformel returnerer flere resultater, kommer disse resultatene automatisk inn i nabocellene. Denne oppførselen kalles "spill" i Excel. Og celleområdet der resultatene kommer inn i, kalles "Spill-området". Spillområdet vil utvides eller trekke seg sammen automatisk basert på kildeverdiene.

Hvis en formel prøver å fylle et utslippsområde med flere resultater, men blokkeres av noe i det området, oppstår en #SPILL-feil.

Excel har nå 9 funksjoner som bruker Dynamic Array-funksjonalitet for å løse problemer, disse inkluderer:

  • SEKVENS
  • FILTER
  • TRANSPONERER
  • SORTERE
  • SORTER ETTER
  • RANDARRAY
  • UNIK
  • XLOOKUP
  • XMATCH

Dynamiske matriseformler er bare tilgjengelige i "Excel 365", og det støttes foreløpig ikke av noen av de frakoblede Excel-programvarene (dvs. Microsoft Excel 2016, 2019).

Spillfeil er ikke bare forårsaket av blokkering av data, det er flere grunner til at du kan få #Spill-feil. La oss utforske de forskjellige situasjonene der du kan støte på #SPILL! feil og hvordan du fikser dem.

Spillområde er ikke tomt

En av hovedårsakene til utslippsfeil er at utslippsområdet ikke er tomt. For eksempel, hvis du prøver å vise 10 resultater, men hvis det er data i noen av cellene i utslippsområdet, returnerer formelen #SPILL! feil.

Eksempel 1:

I eksemplet nedenfor har vi lagt inn TRANSPOSE-funksjonen i celle C2 for å konvertere det vertikale området av celler (B2:B5) til et horisontalt område (C2:F2). I stedet for å bytte kolonne til en rad, viser Excel oss #SPILL! feil.

Og når du klikker på formelcellen, vil du se en stiplet blå kant som indikerer utslippsområdet/området (C2:F2) som er nødvendig for å vise resultatene som vist nedenfor. Du vil også legge merke til et gult advarselsskilt med et utropstegn på.

For å forstå årsaken bak feilen, klikk på advarselsikonet ved siden av feilen og se meldingen i den første linjen uthevet i grått. Som du kan se, står det "Spillområde er ikke tomt" her.

Problemet her er at cellene i utslippsområdet D2 og E2 har teksttegn (ikke tomme), derav feilen.

Løsning:

Løsningen er enkel, enten fjern dataene (enten flytt eller slett) som ligger i utslippsområdet, eller flytt formelen til et annet sted der det ikke er noen hindring.

Så snart du sletter eller flytter blokkeringen, vil Excel automatisk fylle ut cellene med resultatene av formelen. Her, når vi sletter teksten i D2 og E2, transponerer formelen kolonnen til rad som ment.

Eksempel 2:

I eksemplet nedenfor, selv om utslippsområdet virker tomt, viser formelen fortsatt Spill! feil. Det er fordi utslippet faktisk ikke er tomt, det har en usynlig mellomromskarakter i en av cellene.

Det er vanskelig å finne mellomromstegn eller andre usynlige tegn som gjemmer seg i det som ser ut til å være tomme celler. For å finne slike celler med uønskede data, klikk på Error floatie (advarselstegnet) og velg "Select Obstructing Cells" fra menyen, og den vil ta deg til cellen som inneholder obstructing data.

Som du kan se, i skjermbildet nedenfor, har celle E2 to mellomrom. Når du sletter disse dataene, får du riktig utgang.

Noen ganger kan det usynlige tegnet være en tekst formatert med samme skriftfarge som cellens fyllfarge eller en celleverdi tilpasset formatert med tallkoden ;;;. Når du tilpasser formaterer en celleverdi med ;;;, vil den skjule alt i den cellen, uavhengig av skriftfarge eller cellefarge.

Spillområde inneholder sammenslåtte celler

Noen ganger kan #SPILL! feil oppstår når utslippsområdet inneholder de sammenslåtte cellene. Dynamisk matriseformel fungerer ikke med sammenslåtte celler. For å fikse dette, er alt du trenger å gjøre å oppheve sammenslåingen av celler i spillområdet eller flytte formelen til et annet område som ikke har sammenslåtte celler.

I eksemplet nedenfor, selv om spillområdet er tomt (C2:CC8), returnerer formelen spillfeilen. Det er fordi cellene C4 og C5 er slått sammen.

For å være sikker på at sammenslåtte celler er årsaken til at du får feilen, klikk påadvarselsskilt og verifiser årsaken – ‘Sølsområde har slått sammen celle’.

Løsning:

For å oppheve sammenslåingen av cellene, velg de sammenslåtte cellene, og klikk deretter på "Slå sammen og senter"-knappen på "Hjem"-fanen og velg "Opphev sammenslåing av celler".

Hvis du har vanskelig for å finne de sammenslåtte cellene i det store regnearket ditt, klikker du på alternativet "Velg blokkerende celler" fra varselskiltmenyen for å hoppe til de sammenslåtte cellene.

Spillområde i tabell

Spillede matriseformler støttes ikke i Excel-tabeller. Dynamisk matriseformel skal bare angis i én enkelt celle. Hvis du skriver inn en sølt array-formel i en tabell eller når spillområdet faller inn i en tabell, vil du få spillfeilen. Når dette skjer, prøv å konvertere tabellen til et normalt område eller flytt formelen utenfor tabellen.

For eksempel, når vi skriver inn følgende formel for sølt område i en Excel-tabell, vil vi få en spillfeil i hver celle i tabellen, ikke bare formelcellen. Det er fordi Excel automatisk kopierer enhver formel angitt i en tabell til hver celle i tabellens kolonne.

Du vil også få en spillfeil når en formel prøver å søle resultater i en tabell. I skjermbildet nedenfor faller utslippsområdet innenfor den eksisterende tabellen, så vi får en spillfeil.

For å bekrefte årsaken bak denne feilen, klikk på advarselsskiltet og se årsaken til feilen - "Sølsområde i tabell"

Løsning:

For å fikse feilen, må du tilbakestille Excel-tabellen til området. For å gjøre det, høyreklikk hvor som helst i tabellen, klikk på "Tabell", og velg deretter alternativet "Konverter til område". Alternativt kan du venstreklikke hvor som helst i tabellen, deretter gå til kategorien "Borddesign" og velge alternativet "Konverter til område".

Utslippsområde er ukjent

Hvis Excel ikke var i stand til å fastslå størrelsen på sølt array, vil det utløse spillfeilen. Noen ganger gjør formelen det mulig for en dynamisk matrise å endre størrelsen mellom hver beregningsgjennomgang. Hvis størrelsen på den dynamiske matrisen fortsetter å endre seg mens beregningene går og ikke balanserer seg, vil det føre til #SPILL! Feil.

Denne typen spillfeil utløses vanligvis ved bruk av flyktige funksjoner som RAND, RANDARRAY, RANDBETWEEN, OFFSET og INDIREKTE funksjoner.

For eksempel, når vi bruker formelen nedenfor i celle B3, får vi spillfeilen:

=SEKVENS(RANDMELLOM(1; 500))

I eksemplet returnerer RANDBETWEEN-funksjonen et tilfeldig heltall mellom tallene 1 og 500, og dens utdata endres kontinuerlig. Og SEQUENCE-funksjonen vet ikke hvor mange verdier som skal produseres i en spill-array. Derfor #SPILL-feilen.

Du kan også bekrefte årsaken til feilen ved å klikke på advarselstegnet – ‘Utsølsområde er ukjent’.

Løsning:

For å fikse feilen for denne formelen, er ditt eneste valg å bruke en annen formel for beregningen.

Spillområdet er for stort

Noen ganger kan du utføre en formel som gir ut et sølt område som er for stort til at regnearket kan håndtere, og det kan strekke seg utover kantene på regnearket. Når det skjer kan du få #SPILL! feil. For å fikse dette problemet kan du prøve å referere til et spesifikt område eller én celle i stedet for hele kolonner eller bruke "@"-tegnet for å aktivere implisitt skjæringspunkt

I eksemplet nedenfor prøver vi å beregne 20 % av salgstallene i kolonne A og returnere resultatene i kolonne B, men i stedet får vi en spillfeil.

Formelen i B3 beregner 20 % av verdien i A3, deretter 20 % av verdien i A4, og så videre. Den produserer over en million resultater ( 1 048 576) og søl alle av dem i kolonne B starter i celle B3, men den vil nå slutten av regnearket. Det er ikke nok plass til å vise alle utdataene, som et resultat får vi en #SPILL-feil.

Som du kan se, er årsaken til denne feilen at - "Spillområdet er for stort".

Løsninger:

For å løse dette problemet, prøv å endre hele kolonnen med et relevant område eller en enkeltcellereferanse, eller legg til @-operatøren for å utføre implisitt kryss.

Reparer 1: Du kan prøve å henvise områder i stedet for hele kolonner. Her endrer vi hele området A:A med A3:A11 i formelen, og formelen vil automatisk fylle ut området med resultater.

Fix 2: Erstatt hele kolonnen med bare cellereferansen på samme rad (A3), og kopier deretter formelen nedover i området ved å bruke fyllhåndtaket.

Fix 3: Du kan også prøve å legge til @-operatøren før referansen for å utføre implisitt kryss. Dette vil kun vise utdataene i formelcellen.

Deretter kopierer du formelen fra celle B3 til resten av området.

Merk: Når du redigerer en sølt formel, kan du bare redigere den første cellen i spillområdet/området. Du kan se formelen i andre celler i utslippsområdet, men de vil være nedtonet og kan ikke oppdateres.

Tomt for minne

Hvis du kjører en sølt matriseformel som får Excel til å gå tom for minne, kan det utløse #SPILL-feilen. Under disse omstendighetene, prøv å referere til en mindre matrise eller rekkevidde.

Ugjenkjent / Fallback

Du kan også få en spillfeil selv når Excel ikke gjenkjenner eller ikke kan avstemme årsaken til feilen. I slike tilfeller må du dobbeltsjekke formelen og sørge for at alle parametere til funksjonene er riktige.

Nå vet du alle årsakene og løsningene til #SPILL! feil i Excel 365.