Rango de Derrames de Excel Explicado: Domina el Arte de la Referenciación
¿Alguna vez te has encontrado perdido entre fórmulas y celdas en Excel, preguntándote cómo funcionan realmente los rangos de derrame? No estás solo. La versatilidad de Excel puede ser abrumadora, pero entender el concepto de "rangos de derrames" es clave para desbloquear todo su potencial. En este artículo, desglosaremos este término aparentemente complejo en un lenguaje sencillo y práctico, ayudándote a mejorar tus habilidades y a optimizar tu flujo de trabajo. Prepárate para transformar tu experiencia con Excel y llevar tus hojas de cálculo a un nuevo nivel. ¡Vamos a sumergirnos en el fascinante mundo de los rangos de derrames!
El uso de Microsoft Excel se ha vuelto una herramienta indispensable en muchos ámbitos, desde el trabajo hasta los estudios. Sin embargo, es común encontrarse con un vasto número de funciones y herramientas que pueden confundir a cualquier usuario. En este artículo, te explicaremos de manera clara y concisa los diferentes rangos de derrames en Excel y cómo aprovechar al máximo esta función para optimizar tus tareas diarias. ¡Sigue leyendo para descubrir todos los secretos detrás de los derrames de Excel!
El tutorial explica la idea de «rango de derrame» en un lenguaje sencillo y brinda respuestas a las preguntas más comunes.
La dispersión es una de las nuevas características introducidas en Excel 365 como parte de la funcionalidad de matrices dinámicas. Y para aprovecharlo al máximo, es importante comprender los términos clave, para que pueda seguir ejemplos y crear de manera eficiente sus propias fórmulas de matriz dinámica. Comprender el concepto de rango de derrame es el objetivo de este tutorial.
Rango de derrame en Excel
Cuando una fórmula de matriz dinámica produce múltiples valores como resultado del cálculo, genera o derrama todos esos valores en la hoja.
Rango de derrame se refiere a una matriz de valores devueltos por una fórmula de matriz dinámica en celdas vecinas.
Cuando selecciona cualquier celda en el área derramada, todo el rango se resalta con un borde azul que indica que todo lo que contiene se calcula mediante la fórmula de la celda superior.
>Anteriormente, con las fórmulas de matriz CSE tradicionales, teníamos que adivinar en cuántas celdas copiar una fórmula. Ahora, simplemente ingresa la fórmula en un unicelular y deja que Excel se encargue del resto.
El rango de derrame es dinámico y se actualiza automáticamente a medida que cambian los datos de origen. Cuando agrega o elimina elementos a/de los datos de origen, el rango derramado puede expandirse o contraerse.
Si elimina la fórmula en la primera celda, todos los resultados desaparecerán.
Referencia del rango de derrame (# operador)
Para hacer referencia a un rango completo de derrames, coloque una etiqueta hash (#) después de la dirección de la celda superior izquierda del rango (es decir, la celda que contiene la fórmula que devuelve una matriz de valores). En términos de Excel, esto se llama referencia del rango de derrame.
Por ejemplo, tenemos una fórmula ÚNICA en C2, que devuelve múltiples resultados. Para hacer referencia a todos esos valores, escriba:
=C2#
Para referirse al mismo rango de derrames desde otra hojaincluya el nombre de la hoja seguido del signo de exclamación:
=Sheet1!C2#
Para hacer referencia al rango de derrames desde otro libro de trabajoincluya también el nombre del archivo entre corchetes:
=[Book1.xlsx]Sheet1!$C2#
>Hacer referencia a C2# es lo mismo que hacer referencia a todo el rango (=C2:C5). Pero a diferencia de una referencia de rango normal, la referencia de derrame refleja automáticamente los cambios en el tamaño del rango, por lo que no es necesario actualizar manualmente todas las referencias de fórmula a medida que cambian los datos de origen. Este comportamiento es especialmente útil cuando «alimenta» el rango de derrame a otra función, ya sea dinámica o convencional. Incluso puede usarlo para rangos con nombre y validación de datos, por ejemplo, para crear una lista desplegable dependiente dinámica.
En nuestro caso, para encontrar cuántos nombres únicos devuelve la fórmula de matriz dinámica en C2, proporcione la referencia del rango de derrame a la función CONTAR:
=COUNTA(C2#)
>
Consejo. Para consultar rápidamente un rango de derrame, seleccione todas las celdas dentro del cuadro azul con el mouse. Excel creará automáticamente una referencia de derrame adecuada.
Nota. El operador de rango extendido solo admite referencias a abrir libros. Si el libro de origen está cerrado, aparecerá un mensaje #REF! se producirá un error. Para corregir el error, simplemente abra el libro de trabajo referido.
3 cosas que debes saber sobre el rango de derrames de Excel
El rango de derrames es una característica realmente maravillosa que facilita mucho la vida de los usuarios de Excel. A continuación se presentan algunos datos interesantes que dan una idea del concepto.
Solo se puede editar la celda superior izquierda
Como solo la celda superior en el área derramada contiene la fórmula, solo esa celda es editable. Cuando selecciona cualquier otra celda dentro del área del borde azul, la fórmula aún se muestra en la barra de fórmulas, pero aparece atenuada.
>Cuando cambias la fórmula en la primera celda y presionas IngresarExcel actualiza automáticamente todos los demás valores en el área derramada.
Qué está y qué no está incluido en el rango de derrames
Como se mencionó anteriormente, el comportamiento de derrame es dinámico: cuando los datos originales cambian, el área derramada se ajusta en consecuencia. Por ejemplo, si cambia la lista original para que contenga un nombre exclusivo más (Carretero), el rango de derrame se expande automáticamente para incluir ese nombre.
>Sin embargo, el rango de derrames no se actualiza cuando se agregan nuevas entradas. fuera del rango referido.
Por ejemplo, si la fórmula hace referencia a A2:A10 y se ingresa un nuevo elemento en A11, no aparecerá en los resultados:
>Para que el nuevo elemento se incluya en los resultados de la fórmula, debe cambiar el rango referido a A2:A11.
Si desea que dichos cambios se reflejen sobre la marcha, coloque sus datos de origen en una tabla de Excel y utilice referencias estructuradas en sus fórmulas. A diferencia de los rangos, las tablas de Excel se expanden automáticamente para incorporar nuevas filas. Se puede lograr el mismo efecto con un rango dinámico con nombre.
#ErrorDERRAME
Si algo bloquea el rango de derrame (por ejemplo, otros datos, espacios, caracteres que no se imprimen, fórmulas en las celdas siguientes, etc.), se produce un error #SPILL. Para resolver el error, elimine las celdas obstructoras. Para obtener más información, consulte Error DERRAME en Excel: causas y soluciones.
>Para terminar, publicamos respuestas a las tres preguntas más frecuentes (3 parece ser un número mágico en este tutorial 🙂
¿Cómo se borra el rango de derrame en Excel?
Dependiendo de lo que intentes lograr, existen dos soluciones:
Para resolver un error #SPILL que se produce porque el rango de derrame no está en blanco, elimine los datos de bloqueo del área derramada o mueva la fórmula a una nueva ubicación donde haya suficientes celdas vacías para generar todos los resultados. Siga el enlace anterior para obtener más detalles.
Para eliminar todos los valores en el área derramada, elimine la fórmula en la primera celda.
¿Cómo se cambia el rango de derrame en Excel?
Como el rango de derrame es el resultado de una fórmula, no se puede cambiar manualmente.
En caso de que necesite modificar o actualizar la fórmula, seleccione la celda superior izquierda en el área derramada, realice los cambios necesarios y presione la tecla Enter.
¿Cómo se arregla una estufa de derrames si es demasiado grande?
Excel determina el tamaño de un rango de derrame y cambia automáticamente a medida que se actualizan los datos de origen o la fórmula.
En algunas situaciones, puede establecer el tamaño del rango de derrame desde la propia fórmula. Como ejemplo, consulte Cómo limitar el número de filas devueltas por FILTER.
Si se enfrenta a un error #DERRAME, consulte estas soluciones: ¡Cómo solucionar un #DERRAME! error en Excel.
¿Cómo deshacerse del rango de derrame en Excel?
Nuevamente, la respuesta depende de su objetivo final.
Si estas buscando desactivar la función de derrame globalmente, no es posible; no existe tal configuración en Excel. De hecho, es una de las nuevas capacidades más útiles y no tiene sentido rechazarla por completo. Sería más prudente aprender a utilizarlo a tu favor 🙂
Para eliminar un rango de derrame particularelimine una fórmula en la primera celda.
A evitar que la fórmula se derrame en varias celdas, utilice el operador @ que reduce varios valores a un solo valor. En términos de Excel, esto se llama intersección implícita.
Por ejemplo, la siguiente fórmula de matriz dinámica multiplica cada valor en A2:A5 por un 10%. El resultado es un rango de derrame que ocupa 4 celdas.
= A2:A5*10%
Para procesar solo un valor (en la misma fila que la fórmula) y devolver el resultado en un unicelularcambie la fórmula de la siguiente manera y luego cópiela en tantas celdas como sea necesario.
=@A:A*10%
o
=A2*10%
>Ahora ya no eres un novato en lo que respecta al rango de derrames de Excel, ¿verdad? ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Robert ioan: ¡Excelente artículo! La verdad es que el rango de derrames en Excel siempre me había confundido un poco. Una vez, durante un proyecto importante, no me di cuenta de que mis fórmulas estaban derramándose por todas partes y terminé sacando gráficas locas. Desde entonces, siempre reviso el rango con pinzas. ¡Gracias por la explicación clara!
Terrassa: ¡Totalmente de acuerdo! A mí también me pasó una vez que dejé un rango mal configurado y terminé con un desmadre de datos, fue un estrés. Desde entonces, no me atrevo a soltarme con las fórmulas sin revisarlas bien. Este artículo me pareció súper útil, ¡gracias por compartir!
Buscandolaluzzg: ¡Genial el artículo! A mí me pasó algo similar hace un tiempo, dejé que una fórmula se derramara sin darme cuenta y cuando fui a revisar, todo estaba patas arriba. Desde entonces, cada vez que uso Excel, estoy súper pendiente de esos rangos. ¡Gracias por compartir!
Restituimxr: ¡Totalmente de acuerdo! A mí también me ha pasado lo mismo, me dio un dolor de cabeza enorme cuando mis fórmulas comenzaron a hacer lo que querían. Desde que leí sobre este tema, he aprendido a no dejarlo al azar. ¡Gracias por la aclaración!
Diego leon: ¡Uff, me siento súper identificado! A mí me pasó algo parecido hace unos meses; dejé que un rango se expandiera sin querer y, cuando me di cuenta, mis datos estaban desordenados y tuve que volver a hacer todo. Desde entonces, siempre reviso las fórmulas antes de darle al «enter». ¡Gracias por el artículo, me ayudó un montón!
Prestonjf: ¡Totalmente de acuerdo! A mí también me pasó algo parecido hace un tiempo, dejé un rango abierto sin darme cuenta y terminé con un batiburrillo de información que me costó horas organizar. Desde entonces, siempre le echo un ojo a los rangos antes de hacer cualquier cosa. Este artículo es muy útil, ¡gracias por compartirlo!
Saumlaustg: ¡Exacto! A mí también me pasó algo similar, y qué mal rato pasé. Una vez dejé un rango de celdas que no debí y de repente fue un caos total, mis gráficos eran un desastre. Desde ese día estoy más pendiente de mis rangos. Este artículo es una joya, ¡gracias por compartirlo!