Excel es una potencia de una aplicación de hoja de cálculo. Una de las características es una fórmula que le permite realizar cálculos simples a complejos de forma rápida y sencilla. ¡Obtienes el #derrame! error en Microsoft Excel cuando la fórmula devuelve múltiples resultados. Esto es matemáticamente incorrecto, por lo que Excel, en lugar de mostrar el resultado, muestra el error DERRAME. Comprendamos con más detalle qué significan el derrame y la matriz en Excel, cómo identificar dichos errores y cómo solucionarlos.
¿Qué es un error de derrame y cómo detectar uno?
Un error de derrame es cuando ingresa una fórmula en Excel y regresa con un error porque el rango es demasiado grande o no hay suficiente memoria, por ejemplo. Entonces, Excel mostrará un error o mostrará estos resultados en diferentes celdas. Entonces, estos valores múltiples (respuesta a la consulta) se ingresan en las celdas vecinas. Algunos otros errores de derrame que puede encontrar son:
- el rango de derrame está vacío
- se desconoce el rango de derrame
- el rango de derrame no tiene memoria
- se produjo un derrame debido a un retroceso
- el rango de derrame es demasiado grande
- el rango de derrame no está en blanco
- el rango de derrame ha fusionado la celda
Una forma sencilla de solucionar el error de rango de derrame es pegar la fórmula en una hoja de Excel vacía y volver a crear el error. De esa forma, no perderá datos valiosos ni horas de arduo trabajo.
Dejame darte un ejemplo.
Tiene una tabla con los nombres, la identificación del cliente y las direcciones de los empleados. Ingresa una fórmula para obtener el número de teléfono móvil de los empleados de otra hoja cuando la identificación del cliente coincide. Excel arrojará un error de derrame cuando más de dos números de teléfono estén asociados con la misma ID de cliente. Ahora, el rango es más grande y no se puede mostrar en una sola celda, lo que provoca un derrame en las celdas vecinas.
¿Este ejemplo ayuda a corregir el error de derrame en su hoja de Excel? Si no, aquí hay algunas soluciones.
Solucionar error de rango de derrame en Microsoft Excel
¡He creado una hoja ficticia con una secuencia que devuelve un #DERRAME! error en excel. Veamos qué podemos hacer para solucionar el problema. Así es como se ve.
1. Cuando el rango de derrame es demasiado grande
Echemos un vistazo a lo que ingresé en la celda primero.
El rango es demasiado amplio para calcular el tamaño de la matriz, lo que genera un error de derrame. Si la matriz es de naturaleza dinámica, Excel responderá y derramará un error para asegurarse de que se hayan completado otros cálculos en la hoja de cálculo. Intente reducir el ancho del rango si puede para ver si eso ayuda.
2. Cuando el rango de derrame no está en blanco
Al ingresar la fórmula en la hoja de Excel, pero el rango de derrame para la matriz no está en blanco, esto es lo que obtiene: error de derrame con una línea de borde discontinua alrededor. El borde denota el área del rango de derrame.
Este error de derrame se puede solucionar rápidamente. Haga clic con el botón derecho para seleccionar el error flotante y elija la opción Seleccionar celdas obstructoras para ir a la(s) celda(s) obstructiva(s). A continuación, puede borrar el error eliminando o moviendo la entrada de la celda que obstruye. Tan pronto como se elimine la obstrucción, la fórmula de matriz se derramará según lo previsto.
En este caso, el número 4 debe eliminarse o moverse para despejar el bloqueo para que ocurra el derrame.
3. Cuando Excel se queda sin memoria
Este es más fácil de arreglar. Todo lo que necesita hacer es hacer referencia a un rango o matriz más pequeño porque el rango actual es demasiado grande y está causando que Excel «se quede sin memoria».
4. Cuando el error de derrame se extiende más allá del borde de la hoja
¿Sabes cuántas celdas hay en una columna de Excel? La respuesta es 1.048.576. Sí, hay un límite y la fórmula que acaba de ingresar (¿era BUSCARV?) hace que Excel busque o busque en toda la columna y arroje 1,048,576 resultados. Pero luego intenta ir más allá del rango de la hoja, lo que resulta en un error de derrame.
La solución entonces es probar con un rango más pequeño. En este caso, mover la fórmula a la celda F2 debería resolver el error de derrame. Las matrices dinámicas no siempre formaron parte de las hojas de Excel. Con su adición, Excel buscará todos los valores en lookup_value, lo que significa que si se ofrece una columna completa como argumento (en nuestro caso, D:D), Excel buscará 1,048,576 valores en esa columna. Después de eso, intentará derramarlos en la cuadrícula, pero golpeará el final o el borde de la hoja de Excel, lo que dará como resultado el error de derrame.
Existen tres formas de corregir este error de derrame y el que elijas dependerá del resultado final que desees.
- Haga referencia a los valores de búsqueda que desee. Esta fórmula devolverá una matriz dinámica pero no funcionará con tablas de Excel, así que téngalo en cuenta.
- Simplemente haga referencia al valor que se encuentra en la misma fila y luego copie la fórmula a continuación. Este enfoque tradicional funcionará con tablas pero no devolverá una matriz dinámica.
- Use el operador @ para realizar una intersección (implícita) y luego copie la fórmula a continuación. Esto volverá a funcionar con tablas pero no devolverá una matriz dinámica.
5. Fórmula de tabla
Las tablas en Excel simplemente no admiten fórmulas de derrame, por lo que usar una dentro de una tabla generará un error de rango de derrame. Quite la tabla, que es más fácil decirlo que hacerlo, o saque la fórmula derramada de la tabla. Otra solución, un término medio, puede ser convertir la mesa en una gama.
Para hacerlo, haga clic en Diseño de tablas > Herramientas > Convertir a rango.
6. Derrame en celdas combinadas
¿Tiene celdas combinadas en su hoja de cálculo de Excel? En caso afirmativo, la fórmula de matriz derramada no puede derramarse con éxito en las celdas combinadas. Para resolver este error de derrame en Excel, debe separar las celdas. Si eso no es posible, mueva la fórmula a una celda diferente para que las celdas combinadas ya no estorben o se crucen con el rango.
Como notará en la captura de pantalla anterior, al seleccionar la fórmula se creará una línea de borde discontinua que resaltará el rango de derrame. Nuevamente, haga clic derecho y seleccione el Seleccionar celdas obstructoras opción para encontrar las celdas combinadas si no está seguro de dónde están.
Derrámalo
Los errores de derrame en las hojas de Excel pueden ocurrir debido a varias razones. Pueden variar dependiendo de los datos que haya ingresado. Encontrar la razón detrás del derrame en el rango de la matriz es más importante. Solo entonces puede aplicar una solución adecuada.
Para leer más, recomendamos aprender a proteger con contraseña un archivo de Excel.