Este artículo lo ayudará a comprender todas las causas de los errores #SPILL, así como las soluciones para solucionarlos en Excel 365.
#¡DERRAMAR! es un nuevo tipo de error de Excel que ocurre principalmente cuando una fórmula que produce múltiples resultados de cálculo intenta mostrar sus salidas en un rango de derrame, pero ese rango ya contiene algunos otros datos.
Los datos de bloqueo pueden ser cualquier cosa, incluido el valor de texto, las celdas combinadas, un carácter de espacio simple o incluso cuando no hay suficiente lugar para devolver los resultados. La solución es simple, borre el rango de los datos de bloqueo o seleccione una matriz vacía de celdas que no contenga ningún tipo de datos.
El error de derrame generalmente ocurre al calcular fórmulas de matriz dinámica, porque la fórmula de matriz dinámica es la que genera los resultados en varias celdas o una matriz. Analicemos con más detalle y comprendamos qué desencadena este error en Excel y cómo resolverlo.
¿Qué causa un error de derrame?
Desde el lanzamiento de las matrices dinámicas en 2018, las fórmulas de Excel pueden manejar varios valores a la vez y devolver resultados en más de una celda. Las matrices dinámicas son matrices de tamaño variable que permiten que las fórmulas devuelvan múltiples resultados a un rango de celdas en la hoja de trabajo según una fórmula ingresada en una sola celda.
Cuando una fórmula de matriz dinámica devuelve varios resultados, estos resultados se derraman automáticamente en las celdas vecinas. Este comportamiento se denomina "Derrame" en Excel. Y el rango de celdas donde se derraman los resultados se denomina "rango de derrame". El rango de derrame se expandirá o contraerá automáticamente en función de los valores de origen.
Si una fórmula intenta llenar un rango de derrame con varios resultados, pero está bloqueada por algo en ese rango, se produce un error #SPILL.
Excel ahora tiene 9 funciones que usan la funcionalidad Dynamic Array para resolver problemas, estas incluyen:
- SECUENCIA
- FILTRAR
- TRANSPONER
- CLASIFICAR
- ORDENAR POR
- RANDARRAY
- ÚNICO
- XLOOKUP
- XMATCH
Las fórmulas de matriz dinámica solo están disponibles en "Excel 365" y actualmente no es compatible con ningún software de Excel sin conexión (es decir, Microsoft Excel 2016, 2019).
Los errores de derrame no solo se deben a la obstrucción de los datos, hay varias razones por las que puede obtener el error #Spill. ¡Permítanos explorar las diferentes situaciones en las que puede encontrar el #SPILL! error y cómo solucionarlo.
El rango de derrame no está en blanco
Una de las principales causas del error de derrame es que el rango del derrame no está vacío. Por ejemplo, si está intentando mostrar 10 resultados, pero si hay datos en cualquiera de las celdas del área del derrame, la fórmula devuelve un #SPILL. error.
Ejemplo 1:
En el siguiente ejemplo, ingresamos la función TRANSPONER en la celda C2 para convertir el rango vertical de celdas (B2: B5) en un rango horizontal (C2: F2). En lugar de cambiar la columna a una fila, Excel nos muestra el #SPILL! error.
Y cuando haga clic en la celda de fórmula, verá un borde azul punteado que indica el área / rango de derrame (C2: F2) que se necesita para mostrar los resultados como se muestra a continuación. Además, notará una señal de advertencia amarilla con un signo de exclamación.
Para comprender el motivo del error, haga clic en el icono de advertencia junto al error y vea el mensaje en la primera línea resaltado en gris. Como puede ver, aquí dice "El rango de derrame no está en blanco".
El problema aquí es que las celdas en el rango de derrame D2 y E2 tienen caracteres de texto (no vacíos), de ahí el error.
Solución:
La solución es simple, ya sea borrar los datos (mover o eliminar) ubicados en el rango del derrame o mover la fórmula a otra ubicación donde no haya obstrucciones.
Tan pronto como elimine o mueva el bloqueo, Excel completará automáticamente las celdas con los resultados de la fórmula. Aquí, cuando borramos el texto en D2 y E2, la fórmula transpone la columna a la fila según lo previsto.
Ejemplo 2:
En el siguiente ejemplo, aunque el rango de derrame aparece vacío, la fórmula aún muestra el Derrame! error. Es porque el derrame no está realmente vacío, tiene un carácter de espacio invisible en una de las celdas.
Es difícil ubicar los espacios o cualquier otro carácter invisible escondido en lo que parecen ser celdas vacías. Para encontrar esas celdas con datos no deseados, haga clic en el flotador Error (señal de advertencia) y seleccione "Seleccionar celdas de obstrucción" en el menú y lo llevará a la celda que contiene los datos de obstrucción.
Como puede ver, en la siguiente captura de pantalla, la celda E2 tiene dos caracteres de espacio. Cuando borre esos datos, obtendrá la salida adecuada.
A veces, el carácter invisible podría ser un texto formateado con el mismo color de fuente que el color de relleno de la celda o un valor de celda con formato personalizado con el código numérico ;;;. Cuando personaliza el formato de un valor de celda con ;;;, ocultará cualquier cosa en esa celda, independientemente del color de la fuente o el color de la celda.
El rango de derrames contiene celdas fusionadas
A veces, el #SPILL! El error ocurre cuando el rango de derrame contiene las celdas combinadas. La fórmula de matriz dinámica no funciona con celdas combinadas. Para solucionar esto, todo lo que tiene que hacer es separar las celdas en el rango de derrame o mover la fórmula a otro rango que no tenga celdas combinadas.
En el siguiente ejemplo, aunque el rango de derrame está vacío (C2: CC8), la fórmula devuelve el error Derrame. Es porque las celdas C4 y C5 están fusionadas.
Para asegurarse de que las celdas combinadas son la razón por la que recibe el error, haga clic en elseñal de advertencia y verifique la causa: "El rango del derrame se ha fusionado en la celda".
Solución:
Para separar las celdas, seleccione las celdas combinadas, luego, en la pestaña "Inicio", haga clic en el botón "Combinar y centrar" y seleccione "Separar celdas".
Si tiene dificultades para localizar las celdas combinadas en su hoja de cálculo grande, haga clic en la opción "Seleccionar celdas que obstruyen" del menú de señales de advertencia para saltar a las celdas combinadas.
Rango de derrame en la tabla
Las fórmulas de matriz derramada no son compatibles con las tablas de Excel. La fórmula de matriz dinámica solo debe ingresarse en una sola celda individual. Si ingresa una fórmula de matriz derramada en una tabla o cuando el área del derrame cae en una tabla, obtendrá el error Derrame. Cuando esto suceda, intente convertir la tabla a un rango normal o mueva la fórmula fuera de la tabla.
Por ejemplo, cuando ingresamos la siguiente fórmula de rango derramado en una tabla de Excel, obtendríamos un error de Derrame en cada celda de la tabla, no solo en la celda de la fórmula. Es porque Excel copia automáticamente cualquier fórmula ingresada en una tabla en cada celda de la columna de la tabla.
Además, obtendrá un error de derrame cuando una fórmula intente derramar resultados en una tabla. En la siguiente captura de pantalla, el área del derrame se encuentra dentro de la tabla existente, por lo que obtenemos un error de Derrame.
Para confirmar la causa detrás de este error, haga clic en el signo de advertencia y vea el motivo del error: "Rango de derrame en la tabla".
Solución:
Para corregir el error, deberá revertir la tabla de Excel al rango. Para hacerlo, haga clic con el botón derecho en cualquier lugar de la tabla, haga clic en "Tabla" y luego seleccione la opción "Convertir en rango". Alternativamente, puede hacer clic con el botón izquierdo en cualquier lugar de la tabla, luego ir a la pestaña "Diseño de tabla" y seleccionar la opción "Convertir en rango".
El alcance del derrame es desconocido
Si Excel no pudo establecer el tamaño de la matriz derramada, activará el error Derrame. A veces, la fórmula permite que una matriz dinámica cambie de tamaño entre cada pasada de cálculo. Si el tamaño de la matriz dinámica sigue cambiando durante los cálculos y no se equilibra, ¡causará el #SPILL! Error.
Este tipo de error de Derrame generalmente se activa cuando se utilizan funciones volátiles como las funciones RAND, RANDARRAY, RANDBETWEEN, OFFSET e INDIRECT.
Por ejemplo, cuando usamos la siguiente fórmula en la celda B3, obtenemos el error Derrame:
= SECUENCIA (AL AZAR ENTRE (1, 500))En el ejemplo, la función RANDBETWEEN devuelve un número entero aleatorio entre los números 1 y 500, y su salida cambia continuamente. Y la función SECUENCIA no sabe cuántos valores producir en una matriz de derrame. De ahí el error #SPILL.
También puede confirmar la causa del error haciendo clic en el signo de advertencia: "Se desconoce el alcance del derrame".
Solución:
Para corregir el error de esta fórmula, su única opción es utilizar una fórmula diferente para su cálculo.
El alcance del derrame es demasiado grande
A veces, puede ejecutar una fórmula que genera un rango derramado que es demasiado grande para que lo maneje la hoja de trabajo, y puede extenderse más allá de los bordes de la hoja de trabajo. Cuando eso suceda, ¡puede recibir #SPILL! error. Para solucionar este problema, puede intentar hacer referencia a un rango específico o una celda en lugar de columnas enteras o usar el carácter "@" para habilitar la intersección implícita
En el siguiente ejemplo, estamos tratando de calcular el 20% de los números de Ventas en la columna A y devolver los resultados en la columna B, pero en su lugar, obtenemos un error de Derrame.
La fórmula en B3 calcula el 20% del valor en A3, luego el 20% del valor en A4, y así sucesivamente. Produce más de un millón de resultados (1.048.576) y los derrama todos en la columna B comenzando en la celda B3, pero llegará al final de la hoja de trabajo. No hay suficiente espacio para mostrar todos los resultados, como resultado, obtenemos un error #SPILL.
Como puede ver, la causa de este error es que el "rango de derrame es demasiado grande".
Soluciones:
Para resolver este problema, intente cambiar toda la columna con un rango relevante o una referencia de celda única, o agregue el operador @ para realizar una intersección implícita.
Arreglar 1: Puede intentar hacer referencia a rangos en lugar de columnas completas. Aquí, cambiamos todo el rango A: A con A3: A11 en la fórmula, y la fórmula completará automáticamente el rango con resultados.
Arreglo 2: Reemplace toda la columna con solo la referencia de celda en la misma fila (A3) y luego copie la fórmula en el rango usando el controlador de relleno.
Arreglo 3: También puede intentar agregar el operador @ antes de la referencia para realizar una intersección implícita. Esto mostrará la salida solo en la celda de fórmula.
Luego, copie la fórmula de la celda B3 al resto del rango.
Nota: Cuando está editando una fórmula derramada, solo puede editar la primera celda en el área / rango de derrame. Puede ver la fórmula en otras celdas del rango de derrame, pero aparecerán atenuadas y no se pueden actualizar.
Sin memoria
Si ejecuta una fórmula de matriz derramada que hace que Excel se quede sin memoria, puede desencadenar el error #SPILL. En esas circunstancias, intente hacer referencia a una matriz o rango más pequeño.
No reconocido / Retroceso
También puede obtener un error de Derrame incluso cuando Excel no reconoce o no puede conciliar la causa del error. En tales casos, vuelva a verificar su fórmula y asegúrese de que todos los parámetros de las funciones sean correctos.
¡Ahora, conoce todas las causas y soluciones para #SPILL! errores en Excel 365.