Qué es el error de derrame Cómo resolver el error #SPILL! en Excel 365

Como todos sabemos Office 365 viene con Excel 365 incluido.Microsoft ha añadido varias novedades a Excel 365.Una de estas características esFórmulas de matrices dinámicas.Normalmente, la fórmula devolvería un solo valor como resultado a una celda.Pero ahora, con esta nueva función, se pueden devolver múltiples valores.

Por ejemplo, en Excel 2019 y versiones anteriores digamos que aplicas la fórmula =D2:D5 en la celda, el resultado se limitaría a la primera celda.

Cuando tuvimos que aplicar la fórmula en todas las celdas correspondientes, hicimos uso de la notación Array(Ctrl+Shift+Enter).Sin embargo, en Excel 365 no es así.Al aplicar la misma fórmula, los valores se derraman automáticamente en todas las celdas correspondientes.Consulte la siguiente imagen para obtener más detalles.

La zona de celdas en la que se derrama el resultado se llamaAlcance del derrame. Consulte la siguiente imagen

NOTA:

• ElDerramese habilita automáticamente con las matrices dinámicas (actualmente esta función sólo se admite en Excel 365) y la función no se puede desactivar.
• La función de derrame se activa en todas las fórmulas con o sin funciones.

Errores de derramese ven cuando la fórmula está destinada a devolver varios valores, sin embargo, los resultados no se pueden poner en las celdas.El error tiene el siguiente aspecto:

Las posibles razones para encontrar el error #SPILL son :

• El rango de derrame contiene algún valor debido al cual los resultados no pueden ser rellenados en las celdas
• El rango de derrame tiene celdas fusionadas.
• Cuando se abren en Excel365 hojas antiguas (creadas con Excel 2016 o anterior) con fórmulas que admiten la intersección implícita.
• Cuando se aplica la fórmula de matriz dinámica en una tabla de Excel.

Si está viendo el error #SPILL en Excel, no se preocupe.En este artículo, mostraremos diferentes maneras de identificar la causa raíz de este problema y también veremos las formas de solucionar el error #SPILL

Identificar la causa del error #SPILL

Cuando vea un error de derrame, compruebe primero por qué está viendo el error, para hacerlo,

Paso 1: Haga clic en la celda que muestra#¡DERRAME!error

Paso 2: Haga clic en el botónSigno de exclamacióncomo se muestra a continuación

Paso 3: La primera línea nos indica la causa del error.Por ejemplo, en este caso, el error se ve porque el rango de derrame no está en blanco

Correcciones a seguir cuando el rango de derrame no está en blanco

Siga las siguientes correcciones cuando vea que elEl rango de derrame no está en blanco

Solución 1: Eliminar los datos que bloquean el rango de derrame

Si ya hay datos dentro de las celdas del rango de derrame, verá un error #SPILL al aplicar la fórmula.

Cuando pueda ver claramente los datos que bloquean el rango de derrame

Considere el siguiente ejemplo, Cuando se aplica la fórmula =D2:D5 en los datos, se lanza un error de SPILL ya que hayEstoy aquídentro del rango de derrame.

Para deshacerse del error #SPILL basta con mover los datos o eliminarlos del rango de derrame.

Cuando los datos que bloquean el rango de derrame están ocultos

En algunos casos, los datos que bloquean el rango de derrame están ocultos y no son muy obvios, como se ve en el caso 1.Considere el siguiente ejemplo,

En estos casos, para encontrar la celda que está bloqueando el rango de derrame, siga los siguientes pasos:

Paso 1: Haga clic en la celda que muestra#¡DERRAME!error

Paso 2: Haga clic en el botónSigno de exclamacióncomo se muestra a continuación, se puede ver que el error se debe a que elEl rango de derrame no está en blanco.

Paso 3: En el menú desplegable, haga clic enSeleccione las células que obstruyen

Paso 4: La celda que está bloqueando el rango de derrame se resalta como se muestra a continuación

Ahora que sabe qué célula está bloqueando, compruebe qué es exactamente lo que está causando el problema.

Paso 5: Al examinar cuidadosamente la celda, puede ver algunos datos ocultos dentro de las celdas.

Como se ve en la imagen anterior, hay algunos datos.Como la fuente tiene un color blanco, no es fácil reconocer el bloqueo.Para eliminar el error, borre los datos de la celda dentro del rango de derrame.

Arreglar2:Eliminar el formato de Número Personalizado ; ; ; aplicado en la celda

A veces, cuando un formato de número personalizado; ; ;se aplica en una celda, hay posibilidades de ver el error SPILL.En estos casos,

Paso 1: Haga clic en la celda que muestra¡#SEPARACIÓN!error

Paso 2: Haga clic en el botónSigno de exclamacióncomo se muestra a continuación.

Paso 3: En el menú desplegable, haga clic enSeleccione las células que obstruyen

Paso 4: La celda que está bloqueando el rango de derrame se resalta como se muestra a continuación

Paso 5: Haga clic con el botón derecho del ratón en la celda que obstruye.

Paso 6: ElegirFormatear celdas

Paso 7: Se abre la ventana de Formato de Celdas.Ir a laNúmeroficha

Paso 8: En el panel izquierdo, seleccionePersonalizado

Paso 9: En el panel de la derecha, cambie el Tipo de ; ; ; aGeneral

Paso 10: Haga clic en el botónOkbotón

Corrección a seguir cuando el rango de derrame tiene celdas fusionadas

Si ves que el error es porque elEl rango de derrame ha fusionado celdascomo se muestra a continuación,

Paso 1: Haga clic enSeleccione las células que obstruyenen el menú desplegable

Paso 2: La célula de obstrucción serádestacado

Paso 3: Bajo elIniciohaga clic enFusión & Centro

Paso 4: En el menú desplegable, seleccioneDescomponer las células

Fijación que debe seguirse cuandoRango de derrame en la tabla

Las fórmulas de matrices dinámicas no son compatibles con las tablas de Excel.Si ve el error #SPILL en una tabla de Excel como se muestra a continuación con el mensajeRango de derrame en la tabla,

Paso 1: Seleccionar la tabla por completo

Paso 2: Haga clic en el botónDiseño de la mesaen la barra de menú superior

Paso 3: ElegirConvertir a rango

Paso 4: Verás que aparece un diálogo de confirmación, haz clic enSí

Arreglo a seguir cuando el rango de derrame está fuera de la memoria

Cuando intente identificar la causa del error #SPILL, si ve que el error indicaFuera de la memoriaentonces es porque la fórmula de matriz dinámica que está utilizando hace referencia a un rango grande, en estos casos, excel ejecutasin memoriacausando un error de derrame.Para superar el error, se puede intentar referenciar un rango menor.

Fijación a seguir cuando se desconoce el alcance del vertido

Este error se produce cuando el tamaño de la matriz derramada cambia y Excel no es capaz de establecer el tamaño del rango derramado.Generalmente, cuando se utilizan funciones aleatorias como RANDARRAY, RAND, o RANDBETWEEN junto con funciones de matrices dinámicas como SEQUENCE se ve este error.

Para entenderlo mejor, consideremos el siguiente ejemplo, digamos que se utiliza la función SEQUENCE(RANDBETWEEN(1,100)).Aquí RANDBETWEEN genera un entero aleatorio que es mayor o igual a 1 y menor o igual a 100.Y la SECUENCIA genera números secuenciales (por ejemplo, SECUENCIA(5) genera 1,2,3,4,5).Sin embargo, RANDBETWEEN es una función volátil y sigue cambiando su valor cada vez que se abre o modifica una hoja de Excel.Debido a esto, la función SEQUENCE no podrá determinar el tamaño del array que tiene que generar.No sabría cuántos valores generar y, por lo tanto, arroja un error de SPILL.

Cuando está identificando la causa del error, veSe desconoce el alcance del vertido

Para solucionar este tipo de error,intente tener una fórmula diferente que se adapte a su necesidad.

Correcciones a seguir cuando el rango de derrame es demasiado grande

Digamos que estás identificando la causa y notas que el error se ve porque elEl rango de derrame es demasiado grandecomo se muestra a continuación.

Cuando la Matriz Dinámica no existía, había algo llamado intersección implícita en excel que obligaba a devolver un solo resultado incluso cuando la fórmula tenía el potencial de devolver múltiples resultados.Veamos un ejemplo, si la fórmula=B:B*5%se aplica en Excel 2019 o versiones anteriores, con la intersección implícita en su lugar, el resultado sería el siguiente:

Sin embargo, cuando se utiliza la misma fórmula en Excel 365, aparece el siguiente error

Para solucionarlo, intente las siguientes correcciones

Solución 1: Aplicar la intersección implícita mediante el operador @

Cuando decimos=B:Bla matriz dinámica hará referencia a toda la columna B.En su lugar, podemos forzar a excel a imponer la intersección implícita utilizando el operador @

Cambia la fórmula por[email protected]:B*5%

Como se añade la intersección implícita, la fórmula se aplicará a una sola celda.Para ampliar la fórmula,

1.Sólo tiene que hacer clic en el punto como se muestra a continuación

2.Arrástrelo hacia abajo en las celdas según sea necesario.Esto aplicará la misma fórmula a estas celdas.

Arreglo 2: En lugar de referirse a la columna, refiérase al rango

En la fórmula,=B:B*5% ,nos referimos a la columna B.En cambio, se refiere a un rango particular,=B2:B4*5%

Eso es todo

Esperamos que este artículo haya sido informativo.

Por favor, dale a "me gusta" y comenta si has podido solucionar el problema con los métodos anteriores.

Gracias por leer.