Acest articol vă va ajuta să înțelegeți toate cauzele erorilor #SPILL, precum și soluțiile pentru a le remedia în Excel 365.
#SPIL! este un nou tip de eroare Excel care apare în principal atunci când o formulă care produce mai multe rezultate de calcul încearcă să-și afișeze rezultatele într-un interval de scurgere, dar acel interval conține deja alte date.
Datele de blocare pot fi orice, inclusiv valoarea textului, celulele îmbinate, un caracter de spațiu simplu sau chiar atunci când nu există suficient loc pentru a returna rezultatele. Soluția este simplă, fie ștergeți intervalul oricăror date de blocare, fie selectați o matrice goală de celule care nu conțin niciun tip de date în ea.
Eroarea de scurgere apare de obicei la calcularea formulelor matrice dinamice, deoarece formula matricei dinamice este cea care scoate rezultate în mai multe celule sau într-o matrice. Să privim mai în detaliu și să înțelegem ce declanșează această eroare în Excel și cum să o rezolvăm.
Ce cauzează o eroare de scurgere?
De la lansarea matricelor dinamice în 2018, formulele Excel pot gestiona mai multe valori simultan și pot returna rezultate în mai multe celule. Matricele dinamice sunt matrice redimensionabile care permit formulelor să returneze mai multe rezultate într-un interval de celule din foaia de lucru pe baza unei formule introduse într-o singură celulă.
Când o formulă matrice dinamică returnează mai multe rezultate, aceste rezultate se revarsă automat în celulele învecinate. Acest comportament se numește „Spill” în Excel. Iar intervalul de celule în care se revarsă rezultatele se numește „interval de scurgere”. Intervalul de scurgere se va extinde sau se va contracta automat pe baza valorilor sursei.
Dacă o formulă încearcă să umple un interval de scurgere cu rezultate multiple, dar este blocată de ceva din acel interval, atunci apare o eroare #SPILL.
Excel are acum 9 funcții care utilizează funcționalitatea Dynamic Array pentru a rezolva probleme, acestea includ:
- SECVENŢĂ
- FILTRU
- TRANSPUNE
- FEL
- FILTREAZĂ DUPĂ
- RANDARRAY
- UNIC
- CĂUTARE XL
- XMECI
Formulele matrice dinamice sunt disponibile numai în „Excel 365” și nu sunt acceptate în prezent de niciunul dintre software-urile Excel offline (adică Microsoft Excel 2016, 2019).
Erorile de scurgere nu sunt cauzate doar de obstrucția datelor, există mai multe motive pentru care este posibil să obțineți o eroare #Spill. Permiteți-ne să explorăm diferitele situații în care ați putea întâlni #SPILL! eroare și cum să le remediați.
Intervalul de scurgere nu este gol
Una dintre cauzele principale ale erorii de scurgere este faptul că intervalul de scurgere nu este gol. De exemplu, dacă încercați să afișați 10 rezultate, dar dacă există date în oricare dintre celulele din zona de scurgere, formula returnează un #SPILL! eroare.
Exemplul 1:
În exemplul de mai jos, am introdus funcția TRANSPOSE în celula C2 pentru a converti intervalul vertical de celule (B2:B5) într-un interval orizontal (C2:F2). În loc să comute coloana pe un rând, Excel ne arată #SPILL! eroare.
Și când faceți clic pe celula formulei, veți vedea un chenar albastru punctat care indică zona/intervalul de scurgere (C2:F2) care este necesară pentru a afișa rezultatele așa cum se arată mai jos. De asemenea, veți observa un semn de avertizare galben cu un semn de exclamare pe el.
Pentru a înțelege motivul din spatele erorii, faceți clic pe pictograma de avertizare de lângă eroare și vedeți mesajul din prima linie evidențiat cu gri. După cum puteți vedea, aici scrie „Intervalul de scurgere nu este necompletat”.
Problema aici este că celulele din intervalul de scurgere D2 și E2 au caractere text (nu goale), prin urmare, eroarea.
Soluţie:
Soluția este simplă, fie ștergeți datele (fie mutați, fie ștergeți) aflate în intervalul de scurgere, fie mutați formula într-o altă locație în care nu există nicio obstacol.
De îndată ce ștergeți sau mutați blocajul, Excel va popula automat celulele cu rezultatele formulei. Aici, când ștergem textul din D2 și E2, formula transpune coloana în rând, așa cum este prevăzut.
Exemplul 2:
În exemplul de mai jos, deși intervalul de scurgere pare gol, formula arată în continuare Spill! eroare. Se datorează faptului că scurgerea nu este de fapt goală, are un caracter de spațiu invizibil într-una dintre celule.
Este greu să găsești caractere spațiale sau orice alt caracter invizibil care se ascunde în ceea ce par a fi celule goale. Pentru a găsi astfel de celule cu date nedorite, faceți clic pe butonul de eroare (semn de avertizare) și selectați „Selectați celulele obstrucționate” din meniu și vă va duce la celula care conține datele obstrucționate.
După cum puteți vedea, în captura de ecran de mai jos, celula E2 are două caractere de spațiu. Când ștergeți aceste date, veți obține rezultatul corespunzător.
Uneori, caracterul invizibil poate fi un text formatat cu aceeași culoare a fontului ca culoarea de umplere a celulei sau o valoare a celulei formatată personalizat cu codul numeric ;;;. Când formatați o valoare a unei celule cu ;;;, va ascunde orice din acea celulă, indiferent de culoarea fontului sau de culoarea celulei.
Intervalul de scurgere conține celule îmbinate
Uneori, #SPILL! eroarea apare atunci când intervalul de scurgere conține celulele îmbinate. Formula matrice dinamică nu funcționează cu celulele îmbinate. Pentru a remedia acest lucru, tot ce trebuie să faceți este să anulați îmbinarea celulelor din intervalul de scurgere sau să mutați formula într-un alt interval care nu are celule îmbinate.
În exemplul de mai jos, chiar dacă intervalul de scurgere este gol (C2:CC8), formula returnează eroarea de scurgere. Se datorează faptului că celulele C4 și C5 sunt îmbinate.
Pentru a vă asigura că celulele îmbinate sunt motivul pentru care primiți eroarea, faceți clic pesemn de avertizare și verificați cauza – „Intervalul de scurgere are celula îmbinată”.
Soluţie:
Pentru a dezintegra celulele, selectați celulele îmbinate, apoi în fila „Acasă”, faceți clic pe butonul „Îmbinare și centrare” și selectați „Anulați unirea celulelor”.
Dacă vă este greu să găsiți celulele îmbinate în foaia de calcul mare, faceți clic pe opțiunea „Selectați celulele obstrucționate” din meniul semnului de avertizare pentru a trece la celulele îmbinate.
Intervalul de scurgere în tabel
Formulele matrice turnate nu sunt acceptate în tabelele Excel. Formula matrice dinamică trebuie introdusă doar într-o singură celulă individuală. Dacă introduceți o formulă de matrice vărsată într-un tabel sau când zona de scurgere se încadrează într-un tabel, veți obține eroarea Spill. Când se întâmplă acest lucru, încercați să convertiți tabelul într-un interval normal sau mutați formula în afara tabelului.
De exemplu, atunci când introducem următoarea formulă pentru intervalul vărsat într-un tabel Excel, vom obține o eroare Spill în fiecare celulă a tabelului, nu doar celula formulei. Se datorează faptului că Excel copiază automat orice formulă introdusă într-un tabel în fiecare celulă din coloana tabelului.
De asemenea, veți primi o eroare de deversare atunci când o formulă încearcă să deverseze rezultatele într-un tabel. În captura de ecran de mai jos, zona de scurgere se încadrează în tabelul existent, așa că obținem o eroare Spill.
Pentru a confirma cauza din spatele acestei erori, faceți clic pe semnul de avertizare și vedeți motivul erorii - „Interval de scurgere în tabel”
Soluţie:
Pentru a remedia eroarea, va trebui să reveniți tabelul Excel înapoi la interval. Pentru a face acest lucru, faceți clic dreapta oriunde în tabel, faceți clic pe „Tabel”, apoi selectați opțiunea „Convertire în interval”. Alternativ, puteți face clic stânga oriunde în tabel, apoi mergeți la fila „Design tabel” și selectați opțiunea „Convertire în interval”.
Intervalul de scurgere este necunoscut
Dacă Excel nu a reușit să stabilească dimensiunea matricei vărsate, va declanșa eroarea Spill. Uneori, formula permite redimensionarea unei matrice dinamice între fiecare trecere de calcul. Dacă dimensiunea matricei dinamice continuă să se schimbe în timpul calculelor și nu se echilibrează, va provoca #SPILL! Eroare.
Acest tip de eroare Spill este de obicei declanșată atunci când se utilizează funcții volatile, cum ar fi funcțiile RAND, RANDARRAY, RANDBETWEEN, OFFSET și INDIRECT.
De exemplu, când folosim formula de mai jos în celula B3, obținem eroarea Spill:
=SECVENȚA(RANDBINTRE(1, 500))În exemplu, funcția RANDBETWEEN returnează un număr întreg aleatoriu între numerele 1 și 500, iar rezultatul său se modifică continuu. Și funcția SEQUENCE nu știe câte valori trebuie să producă într-o matrice spill. Prin urmare, eroarea #SPILL.
De asemenea, puteți confirma cauza erorii făcând clic pe semnul de avertizare – „Intervalul de scurgere este necunoscut”.
Soluţie:
Pentru a remedia eroarea pentru această formulă, singura ta alegere este să folosești o formulă diferită pentru calcul.
Intervalul de scurgere este prea mare
Uneori, puteți executa o formulă care scoate un interval vărsat care este prea mare pentru a fi gestionat de foaia de lucru și se poate extinde dincolo de marginile foii de lucru. Când se întâmplă asta, s-ar putea să primești #SPILL! eroare. Pentru a remedia această problemă, puteți încerca să faceți referire la un anumit interval sau o celulă în loc de coloane întregi sau să utilizați caracterul „@” pentru a activa intersecția implicită
În exemplul de mai jos, încercăm să calculăm 20% din cifrele vânzărilor din coloana A și să returnăm rezultatele în coloana B, dar în schimb, obținem o eroare Spill.
Formula din B3 calculează 20% din valoarea din A3, apoi 20% din valoarea din A4 și așa mai departe. Produce peste un milion de rezultate (1.048.576) și le împrăștie pe toate în coloana B începând cu celula B3, dar va ajunge la sfârșitul foii de lucru. Nu există suficient spațiu pentru a afișa toate ieșirile, ca urmare, obținem o eroare #SPILL.
După cum puteți vedea, cauza acestei erori este că – „Intervalul de scurgere este prea mare”.
Solutii:
Pentru a rezolva această problemă, încercați să schimbați întreaga coloană cu un interval relevant sau o referință într-o singură celulă sau adăugați operatorul @ pentru a efectua intersecția implicită.
Fix 1: puteți încerca să faceți referire mai degrabă decât coloane întregi. Aici, schimbăm întregul interval A:A cu A3:A11 în formulă, iar formula va completa automat intervalul cu rezultate.
Remedierea 2: Înlocuiți întreaga coloană cu doar referința de celulă de pe același rând (A3), apoi copiați formula în jos în interval folosind mânerul de umplere.
Remedierea 3: De asemenea, puteți încerca să adăugați operatorul @ înainte de referință pentru a efectua intersecția implicită. Aceasta va afișa rezultatul numai în celula formulei.
Apoi, copiați formula din celula B3 în restul intervalului.
Notă: Când editați o formulă vărsată, puteți edita doar prima celulă din zona/intervalul de scurgere. Puteți vedea formula în alte celule din intervalul de scurgere, dar acestea vor fi incolore și nu pot fi actualizate.
Fara memorie
Dacă executați o formulă de matrice vărsată care face ca Excel să rămână fără memorie, poate declanșa eroarea #SPILL. În aceste circumstanțe, încercați să faceți referire la o matrice sau un interval mai mic.
Nerecunoscut/Retur
De asemenea, puteți obține o eroare Spill chiar și atunci când Excel nu recunoaște sau nu poate reconcilia cauza erorii. În astfel de cazuri, verificați de două ori formula și asigurați-vă că toți parametrii funcțiilor sunt corecti.
Acum, știți toate cauzele și soluțiile pentru #SPILL! erori în Excel 365.