¿Qué es el error de derrame?? Cómo resolver el #spill! Error en Excel 365

Como todos sabemos, Office 365 viene con Excel 365 incluido con él. Microsoft ha agregado varias características nuevas a Excel 365. Una de esas características es Fórmulas de matriz dinámica. Por lo general, la fórmula devolvería solo un valor solo como resultado a una celda. Pero ahora, con esta nueva característica en su lugar, se pueden devolver múltiples valores.

Por ejemplo, en Versiones de Excel 2019 y anteriores, digamos que aplica la fórmula = D2: D5 en la célula, el resultado se limitaría a la primera célula.

Cuando tuvimos que aplicar la fórmula en todas las celdas correspondientes, utilizamos la notación de matriz (Ctrl+Shift+Enter). Sin embargo, en Excel 365 no es tan. Cuando aplica la misma fórmula, automáticamente los valores se derraman en todas las celdas correspondientes. Consulte la imagen a continuación, para más detalles.

El área de las células en la que se derrama el resultado se llama Rango de derrames. Consulte la imagen a continuación

NOTA:

• El Derramar está habilitado automáticamente con matrices dinámicas (actualmente esta característica es compatible solo en Excel 365) y la función no se puede deshabilitar.
• La función de derrame está habilitada en todas las fórmulas con o sin funciones.

Errores de derrame se ven cuando la fórmula está destinada a devolver múltiples valores, sin embargo, los resultados no se pueden colocar en las celdas. El error se ve de la siguiente manera:

Las posibles razones para encontrar el error #spill son:

• El rango de derrame contiene algún valor debido a que los resultados no se pueden poblar en las celdas
• El rango de derrames ha fusionado las células.
• Cuando las hojas antiguas (creadas usando Excel 2016 o antes) con fórmulas que respaldan la intersección implícita se abren en Excel365.
• Cuando 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, demostraremos diferentes formas de identificar la causa raíz de este problema y también buscaremos formas de corregir el error #spill

Tabla de contenido

• Identificar qué está causando el error #spill
• Las correcciones a seguir cuando el rango de derrames no está en blanco
• FIJA a seguir cuando el rango de derrame ha fusionado las celdas
• Se corrige a seguir cuando el derrame en la tabla
• Se corrige a seguir cuando el rango de derrame está fuera de memoria
• FIJA a seguir cuando se desconoce el rango de derrames
• Las correcciones a seguir cuando el rango de derrames es demasiado grande

Identificar qué está causando el error #spill

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

Paso 1: haga clic en la celda que muestra #DERRAMAR! error

Paso 2: haga clic en el Signo de exclamación Como se muestra abajo

Paso 3: La primera línea nos dice qué está causando el error. Por ejemplo, en este caso, el error se ve como el rango de derrames no está en blanco

Las correcciones a seguir cuando el rango de derrames no está en blanco

Siga las siguientes correcciones cuando vea que el El rango de derrames no está en blanco

FIJO 1: Elimine los datos que bloquean el rango de derrames

Si ya hay algunos datos dentro de las celdas en el rango de derrame, verá un error #spill al aplicar la fórmula.

Cuando puede ver claramente los datos que bloquean el rango de derrames

Considere el siguiente ejemplo, cuando aplica la fórmula = D2: D5 en los datos, se lanza un error de derrame como hay Estoy aquí Dentro del rango de derrames.

Para deshacerse del error #spill, simplemente mueva los datos o elimine los datos del rango de derrames.

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

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

En tales casos, para encontrar la celda que bloquea el rango de derrames, siga los pasos a continuación:

Paso 1: haga clic en la celda que muestra #DERRAMAR! error

Paso 2: haga clic en el Signo de exclamación Como se muestra a continuación, puede ver que el error se debe a que el El rango de derrames no está en blanco.

Paso 3: desde el menú desplegable, haga clic en Seleccionar obstrucción de células

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

Ahora, que sabe qué celda está bloqueando, verifique qué está causando exactamente el problema.

Paso 5: En un examen cuidadoso de la celda, puede ver algunos datos ocultos dentro de las celdas.

Como se ve en la imagen de arriba, hay algunos datos. Dado que la fuente tiene un color blanco, no es fácil reconocer el bloqueo. Para deshacerse del error, elimine los datos de la celda dentro del rango de derrames.

Arreglar 2: Eliminar el formato de número personalizado; ; ; aplicado en la celda

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

Paso 1: haga clic en la celda que muestra #DERRAMAR! error

Paso 2: haga clic en el Signo de exclamación Como se muestra abajo.

Paso 3: desde el menú desplegable, haga clic en Seleccionar obstrucción de células

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

Paso 5: haga clic con el botón derecho en la celda obstructiva.

Paso 6: Elija Formato de celdas

Paso 7: Se abre la ventana de las celdas de formato. Ve a la Número pestaña

Paso 8: desde el panel izquierdo, seleccione Costumbre

Paso 9: Desde el panel lateral derecho, cambie el tipo desde; ; ; a General

Paso 10: haga clic en el De acuerdo botón

FIJA a seguir cuando el rango de derrame ha fusionado las celdas

Si ve que el error es porque el El rango de derrames ha fusionado las células Como se muestra abajo,

Paso 1: haga clic en Seleccionar obstrucción de células del menú desplegable

Paso 2: la celda obstructiva será destacado

Paso 3: debajo del Hogar pestaña, haga clic en Fusionar y centro

Paso 4: desde el menú desplegable, seleccione Células inquietudes

Arreglar para ser seguido cuando Rango de derrames en la mesa

Las fórmulas de matriz dinámica 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 mensaje Rango de derrames en la mesa,

Paso 1: seleccione la tabla por completo

Paso 2: haga clic en el Diseño de mesa Pestaña desde la barra de menú superior

Paso 3: Elija Convertir a rango

Paso 4: Verá aparecer un diálogo de confirmación, haga clic en Sí

Se corrige a seguir cuando el rango de derrame está fuera de memoria

Cuando intenta identificar la causa del error #spill, si ve que el error establece Sin memoria, entonces eso se debe a que la fórmula de matriz dinámica que está utilizando referencias un rango grande, en tales casos, Excel funciona sin memoria causando un error de derrame. Para superar el error, uno puede intentar hacer referencia a un rango más pequeño.

FIJA a seguir cuando se desconoce el rango de derrames

Este error se ve cuando el tamaño de la matriz derramado cambia y Excel no puede establecer el tamaño del rango derramado. En general, cuando usa funciones aleatorias como Randarray, Rand o Randbetween junto con funciones de matriz dinámica como la secuencia, este error se ve.

Para comprenderlo mejor, considere el siguiente ejemplo, digamos que se usa la secuencia de funciones (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 (eg-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 cambia una hoja de Excel. Porque, de esto, la función de secuencia no podrá determinar el tamaño de la matriz que tiene que generar. No sabría cuántos valores generar y, por lo tanto, arroja un error de derrame.

Cuando está identificando la causa del error, ves Se desconoce el rango de derrames

Para corregir este tipo de error, Intente tener una fórmula diferente que se adapte a sus necesidades.

Las correcciones a seguir cuando el rango de derrames es demasiado grande

Digamos que está identificando la causa y nota que el error se ve porque el El rango de derrames es demasiado grande Como se muestra abajo.

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

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

Para resolver esto, intente las siguientes soluciones

SEX 1: Aplicar intersección implícita usando @ operador

Cuando decimos = B: B, La matriz dinámica hará referencia a toda la columna B. En cambio, podemos forzar a Excel a imponer una intersección implícita usando @ operador

Cambiar la fórmula a =@B: b*5%

Debido a que se agrega la intersección implícita, la fórmula se aplicará a una sola celda. Para extender la fórmula,

1. Simplemente haga clic en el punto como se muestra a continuación

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

FIJAR 2: En lugar de referirse a la columna, consulte el rango

En la fórmula, = B: B*5% , Nos referimos a la columna B. En su lugar, consulte un rango en particular, = B2: B4*5%

Eso es todo

Esperamos que este artículo haya sido informativo.

Por favor, me guste y comente si pudo solucionar el problema con los métodos anteriores.

Gracias por leer.