Aquest article us ajudarà a entendre totes les causes dels errors #SPILL, així com les solucions per solucionar-los a Excel 365.
#VESTIMENT! és un nou tipus d'error d'Excel que es produeix principalment quan una fórmula que produeix múltiples resultats de càlcul intenta mostrar les seves sortides en un interval de vessament, però aquest interval ja conté altres dades.
Les dades de bloqueig poden ser qualsevol cosa, inclòs el valor del text, les cel·les combinades, un caràcter d'espai lliure o fins i tot quan no hi ha prou lloc per retornar els resultats. La solució és senzilla, esborreu l'interval de dades de bloqueig o seleccioneu una matriu buida de cel·les que no continguin cap tipus de dades.
L'error de vessament sol produir-se quan es calculen fórmules de matriu dinàmica, perquè la fórmula de matriu dinàmica és la que produeix resultats en diverses cel·les o en una matriu. Vegem més en detall i entenem què desencadena aquest error a Excel i com resoldre'l.
Què causa un error de vessament?
Des del llançament de les matrius dinàmiques el 2018, les fórmules d'Excel poden gestionar diversos valors alhora i retornar resultats en més d'una cel·la. Les matrius dinàmiques són matrius redimensionables que permeten que les fórmules retornin diversos resultats a un rang de cel·les del full de treball basant-se en una fórmula introduïda en una sola cel·la.
Quan una fórmula de matriu dinàmica retorna diversos resultats, aquests resultats s'aboquen automàticament a les cel·les veïnes. Aquest comportament s'anomena "Vesca" a Excel. I el rang de cel·les on s'aboquen els resultats s'anomena "interval de vessament". L'interval de vessaments s'ampliarà o es contraurà automàticament en funció dels valors d'origen.
Si una fórmula intenta omplir un interval de vessament amb diversos resultats però està bloquejada per alguna cosa d'aquest interval, es produeix un error #SPILL.
Excel té ara 9 funcions que utilitzen la funcionalitat Dynamic Array per resoldre problemes, aquestes inclouen:
- SEQUÈNCIA
- FILTRE
- TRANSPOSE
- ORDENAR
- ORDENAR PER
- RANDARRAY
- ÚNIC
- CERCA XL
- XMATCH
Les fórmules de matriu dinàmiques només estan disponibles a "Excel 365" i actualment no són compatibles amb cap programari d'Excel fora de línia (és a dir, Microsoft Excel 2016, 2019).
Els errors de vessament no només són causats per l'obstrucció de les dades, hi ha diverses raons per les quals podeu obtenir un error #Spill. Deixa'ns explorar les diferents situacions on et pots trobar amb el #SPILL! error i com solucionar-los.
L'interval de vessament no està en blanc
Una de les causes principals de l'error de vessament és que l'interval de vessament no és buit. Per exemple, si esteu intentant mostrar 10 resultats, però si hi ha dades a alguna de les cel·les de l'àrea de vessament, la fórmula retorna un #SPILL! error.
Exemple 1:
A l'exemple següent, hem introduït la funció TRANSPOSE a la cel·la C2 per convertir el rang vertical de cel·les (B2:B5) en un rang horitzontal (C2:F2). En lloc de canviar la columna a una fila, Excel ens mostra el #SPILL! error.
I quan feu clic a la cel·la de la fórmula, veureu una vora blava discontínua que indica l'àrea/interval de vessament (C2:F2) que es necessita per mostrar els resultats tal com es mostra a continuació. A més, notareu un senyal d'advertència groc amb un signe d'exclamació.
Per entendre el motiu de l'error, feu clic a la icona d'advertència que hi ha al costat de l'error i vegeu el missatge a la primera línia ressaltat en gris. Com podeu veure, aquí diu "L'interval de vessaments no està en blanc".
El problema aquí és que les cel·les de l'interval de vessament D2 i E2 tenen caràcters de text (no buits), per tant, l'error.
Solució:
La solució és senzilla, o bé esborrar les dades (ja sigui moure o suprimir) situades a l'interval de vessament o moure la fórmula a una altra ubicació on no hi hagi cap obstrucció.
Tan bon punt suprimiu o moveu el bloqueig, Excel omplirà automàticament les cel·les amb els resultats de la fórmula. Aquí, quan netegem el text a D2 i E2, la fórmula transposa la columna a la fila tal com es pretén.
Exemple 2:
A l'exemple següent, tot i que l'interval de vessament sembla buit, la fórmula encara mostra el vessament! error. És perquè el vessament no és realment buit, sinó que té un caràcter d'espai invisible en una de les cel·les.
És difícil localitzar caràcters espacials o qualsevol altre personatge invisible que s'amagui en el que semblen cel·les buides. Per trobar aquestes cel·les amb dades no desitjades, feu clic a l'error flotant (senyal d'advertència) i seleccioneu "Selecciona les cel·les que obstrueixen" al menú i us portarà a la cel·la que conté les dades que obstrueixen.
Com podeu veure, a la captura de pantalla següent, la cel·la E2 té dos espais. Quan esborreu aquestes dades, obtindreu la sortida adequada.
De vegades, el caràcter invisible pot ser un text formatat amb el mateix color de lletra que el color d'emplenament de la cel·la o un valor de cel·la personalitzat amb el codi numèric ;;;. Quan personalitzeu el format d'una cel·la amb ;;;, s'amagarà qualsevol cosa d'aquesta cel·la, independentment del color de la font o de la cel·la.
L'interval de vessaments conté cèl·lules fusionades
De vegades, el #SPILL! L'error es produeix quan l'interval de vessament conté les cel·les combinades. La fórmula de matriu dinàmica no funciona amb cel·les combinades. Per solucionar-ho, tot el que heu de fer és desfer les cel·les de l'interval de vessament o moure la fórmula a un altre interval que no tingui cel·les combinades.
A l'exemple següent, tot i que l'interval de vessament està buit (C2:CC8), la fórmula retorna l'error de vessament. És perquè les cel·les C4 i C5 estan fusionades.
Per assegurar-vos que les cel·les combinades són el motiu pel qual rebeu l'error, feu clic asenyal d'advertència i verifiqueu-ne la causa: "El rang de vessaments ha combinat la cel·la".
Solució:
Per desajuntar les cel·les, seleccioneu les cel·les combinades i, a continuació, a la pestanya "Inici", feu clic al botó "Combina i centra" i seleccioneu "Desajunta cel·les".
Si us costa trobar les cel·les combinades al vostre full de càlcul gran, feu clic a l'opció "Seleccioneu les cel·les obstruïdes" del menú del senyal d'advertència per anar a les cel·les combinades.
Interval de vessaments a la taula
Les fórmules de matriu vessades no s'admeten a les taules d'Excel. La fórmula de matriu dinàmica només s'ha d'introduir en una sola cel·la individual. Si introduïu una fórmula de matriu vessada en una taula o quan l'àrea de vessament cau en una taula, obtindreu l'error de vessament. Quan això passi, proveu de convertir la taula a un rang normal o moveu la fórmula fora de la taula.
Per exemple, quan introduïm la següent fórmula d'interval vessat en una taula d'Excel, obtindríem un error de vessament a totes les cel·les de la taula, no només a la cel·la de la fórmula. És perquè Excel copia automàticament qualsevol fórmula introduïda en una taula a cada cel·la de la columna de la taula.
A més, obtindreu un error de vessament quan una fórmula intenti vessar els resultats en una taula. A la captura de pantalla següent, l'àrea de vessament es troba dins de la taula existent, de manera que obtenim un error de vessament.
Per confirmar la causa d'aquest error, feu clic al senyal d'advertència i vegeu el motiu de l'error: "Interval de vessament a la taula"
Solució:
Per corregir l'error, haureu de revertir la taula d'Excel de nou a l'interval. Per fer-ho, feu clic amb el botó dret a qualsevol lloc de la taula, feu clic a "Taula" i, a continuació, seleccioneu l'opció "Convertir a interval". Alternativament, podeu fer clic amb el botó esquerre en qualsevol lloc de la taula, després anar a la pestanya "Disseny de la taula" i seleccionar l'opció "Convertir a interval".
L'abast de vessaments és desconegut
Si Excel no ha pogut establir la mida de la matriu vessada, activarà l'error de vessament. De vegades, la fórmula permet canviar la mida d'una matriu dinàmica entre cada passa de càlcul. Si la mida de la matriu dinàmica continua canviant durant els càlculs passa i no s'equilibra, provocarà el #SPILL! Error.
Aquest tipus d'error de vessament s'activa normalment quan s'utilitzen funcions volàtils com les funcions RAND, RANDARRAY, RANDBETWEEN, OFFSET i INDIRECT.
Per exemple, quan fem servir la fórmula següent a la cel·la B3, obtenim l'error de vessament:
=SEQÜÈNCIA(ALLANT (1, 500))A l'exemple, la funció RANDBETWEEN retorna un nombre enter aleatori entre els números 1 i 500 i la seva sortida canvia contínuament. I la funció SEQUENCE no sap quants valors produir en una matriu de vessament. Per tant, l'error #SPILL.
També podeu confirmar la causa de l'error fent clic al signe d'advertència: "L'interval de vessament és desconegut".
Solució:
Per corregir l'error d'aquesta fórmula, la vostra única opció és utilitzar una fórmula diferent per al vostre càlcul.
L'abast de vessaments és massa gran
De vegades, podeu executar una fórmula que produeixi un interval vessat massa gran per al full de treball i que es pot estendre més enllà de les vores del full de treball. Quan això succeeix, és possible que tinguis #SPILL! error. Per solucionar aquest problema, podeu provar de fer referència a un interval específic o a una cel·la en lloc de columnes senceres o utilitzar el caràcter "@" per habilitar la intersecció implícita.
A l'exemple següent, estem intentant calcular el 20% dels números de vendes a la columna A i retornar els resultats a la columna B, però en canvi, obtenim un error de vessament.
La fórmula de B3 calcula el 20% del valor en A3, després el 20% del valor en A4, i així successivament. Produeix més d'un milió de resultats (1.048.576) i els vessa tots a la columna B començant a la cel·la B3, però arribarà al final del full de treball. No hi ha prou espai per mostrar totes les sortides, com a resultat, obtenim un error #SPILL.
Com podeu veure, la causa d'aquest error és que el - "El rang de vessaments és massa gran".
Solucions:
Per resoldre aquest problema, proveu de canviar tota la columna amb un rang rellevant o una referència d'una sola cel·la, o bé afegiu l'operador @ per realitzar una intersecció implícita.
Correcció 1: podeu provar de fer referència a intervals en lloc de columnes senceres. Aquí, canviem tot l'interval A:A amb A3:A11 a la fórmula i la fórmula omplirà automàticament l'interval amb resultats.
Correcció 2: Substituïu la columna sencera amb només la referència de cel·la a la mateixa fila (A3) i després copieu la fórmula cap a l'interval amb el mànec d'emplenament.
Correcció 3: També podeu provar d'afegir l'operador @ abans de la referència per realitzar una intersecció implícita. Això només mostrarà la sortida a la cel·la de la fórmula.
A continuació, copieu la fórmula de la cel·la B3 a la resta de l'interval.
Nota: Quan editeu una fórmula vessada, només podeu editar la primera cel·la de l'àrea/interval de vessament. Podeu veure la fórmula a altres cel·les de l'interval de vessament, però quedaran en gris i no es podran actualitzar.
Sense memòria
Si executeu una fórmula de matriu vessada que fa que l'Excel es quedi sense memòria, pot ser que desencadeni l'error #SPILL. En aquestes circumstàncies, proveu de fer referència a una matriu o un interval més petit.
No reconegut/Recurs
També podeu obtenir un error de vessament fins i tot quan Excel no reconeix o no pot conciliar la causa de l'error. En aquests casos, comproveu la vostra fórmula i assegureu-vos que tots els paràmetres de les funcions siguin correctes.
Ara, coneixeu totes les causes i solucions per a #SPILL! errors a Excel 365.