Cómo usar el rango de derrame en Excel

El rango de derrame de Excel es un producto del uso de funciones de matriz dinámica. Conozca cómo la gama de derrames cumple estas funciones.

En los últimos meses, he escrito varios artículos sobre las nuevas funciones de matrices dinámicas de Excel. En muchos casos, pueden reemplazar expresiones más antiguas y complejas. Las nuevas funciones hacen todo eso calculando internamente y devuelven un rango de valores de resultado. Este rango de valores resultantes se llama el rango de derrame. Al usar estas funciones de matriz dinámica, necesitará saber todo sobre el rango de derrame, así que siga leyendo.

VER: 83 consejos de Excel que todo usuario debe dominar (TechRepublic)

Uso Microsoft 365 en un sistema Windows 10 de 64 bits. Estas nuevas funciones de matriz dinámica solo están disponibles en Microsoft 365, Excel para la Web y Excel para tabletas y teléfonos Android. No hay archivo de demostración; no necesitarás uno.

Comencemos con una buena definición de rango de derrame

Cuando una función o expresión de matriz dinámica produce más de un valor de resultado, esos valores se extienden a las celdas adyacentes. En otras palabras, el rango de derrame es la matriz de valores devueltos por una expresión o función de matriz dinámica. Es importante tener en cuenta que son simplemente valores resultantes y la única celda que contiene una expresión o función es la celda donde la ingresó. La celda superior izquierda contiene la expresión o función y las celdas restantes en el rango de derrame muestran solo los valores resultantes. Esto significa que solo tiene una celda para modificar al modificar la expresión o función; sin embargo, todos los valores resultantes se actualizarán en consecuencia. Afortunadamente, no es tan complicado como parece al principio.

VER: Windows 11: Consejos sobre instalación, seguridad y más (PDF gratuito) (TechRepublic)

Cuando hace clic en cualquier celda dentro del rango de derrame, Excel muestra un borde azul alrededor de todo el rango de derrame. Todo dentro de ese límite azul es un valor de resultado. Figura A muestra un pequeño rango de dispersión para la función dinámica UNIQUE() en H5. Esta función dinámica devuelve una lista de valores únicos de la columna Región:

=ÚNICO(Ventas[Region],FALSO)

En la captura de pantalla, es difícil decir que el límite es azul porque es delgado. Actualmente, el rango de derrame es H5:H7. Además, la barra de fórmulas muestra el rango explícito en lugar de usar la referencia de tabla estructurada. Lo hice para mostrar ambos en uso; ambos trabajarán.

Figura A

Debido a que los datos de origen son un objeto de tabla, la función dinámica en H5 se actualizará a medida que actualice los datos de origen en la tabla. Por ejemplo, si agrega un nuevo registro que incluye una nueva región, la lista que comienza en H5 se actualizará en consecuencia, como se muestra en Figura B. Si no está utilizando una tabla para almacenar los datos de origen e ingresa un valor fuera del rango original (en este caso, la columna Región), el rango de derrame de la función ÚNICA () no actualizar. Si elimina la función dinámica en H5, el rango de derrame desaparece por completo.

Figura B

Hasta ahora, ha habido mucho espacio para los valores de los resultados. ¿Qué pasa cuando no lo hay?

¡Qué es el #DERRAME! ¿error?

Aunque la sección anterior ha sido fácil, sin errores, sin problemas, no siempre será así. Figura C muestra un error de rango de derrame, #SPILL!. La celda ancla, E5, no ha cambiado; la función es exactamente la misma. Sin embargo, en lugar de ver los valores de resultado que vimos antes, ahora vemos un error.

Figura C

Si adivinaste que el valor en H7, prueba, está en el camino, ¡tienes razón! Cuando ve este error, el problema casi siempre es un número inadecuado de celdas para cumplir con el cálculo original. Si la función dinámica no puede devolver todos los valores de los resultados, devuelve el #SPILL! error.

La solución es fácil. Puede eliminar los datos ofensivos en el rango de derrame, o puede mover la función dinámica original a una celda donde haya mucho espacio para cumplir con todos los valores de resultados calculados.

Cuando el problema es obvio, es fácil de resolver. Cuando el problema no es tan obvio como el ejemplo en Figura C, no te desesperes. En su lugar, consulte la etiqueta de error para obtener más información. La etiqueta de error es el signo de exclamación que se muestra cuando selecciona la expresión o función original (Figura C). Simplemente haga clic en la etiqueta inteligente para ver la lista de errores que se muestra en Figura D.

Figura D

Repasemos los posibles errores:

• El rango de derrame no está en blanco es probablemente el error más común. Este es nuestro problema en Figura C. Es obvio en nuestro ejemplo, pero ¿qué sucede si la celda problemática está fuera de la pantalla? Cuando no pueda encontrar el valor infractor, elija Seleccionar celdas de obstrucción. Si lo hace, lo llevará a la celda que está bloqueando los valores de los resultados. En este punto, puede decidir eliminar el valor o mover la función dinámica.
• Ayuda sobre este error abre los archivos de Ayuda con una lista de lo que significan estos mensajes de error.
• Seleccionar celdas obstructoras seleccionará la celda dentro del rango de derrame que está bloqueando los valores resultantes. También podría indicar celdas combinadas porque el rango de volcado no puede acomodar celdas combinadas.
• Mostrar pasos de cálculo muestra el cuadro de diálogo Evaluar fórmula en el que puede recorrer los cálculos, de forma similar a como podría recorrer el código VBA en el Editor de Visual Basic.
• Ignorar error le permite borrar la etiqueta inteligente y continuar trabajando a pesar del error. No recomiendo usar este a menos que tenga una razón específica para hacerlo.
• Editar en la barra de fórmulas da foco a la expresión o función real en la barra de fórmulas para que pueda editar.
• Opciones de comprobación de errores abre el cuadro de diálogo Opciones para que pueda revisar y posiblemente cambiar estas opciones.

En nuestro caso, que es probablemente el error más común, el primer elemento, El rango de derrame no está en blanco es directo e informativo. Si no puede encontrar la celda infractora, elija Seleccionar celdas obstructoras. No se encontrará a menudo con los otros errores.

Además, el objeto Table no admite funciones o expresiones de matriz dinámica. No ingrese estas expresiones y funciones en un objeto Tabla. Los datos de origen pueden y deben estar en una tabla, pero no ingrese la función real en una tabla.

Las funciones de matrices dinámicas integradas de Excel son fáciles de usar y de mantener, siempre que sepa cuál es el rango de dispersión y cómo adaptarse a sus necesidades.

Lea los siguientes artículos para obtener más información sobre estas nuevas funciones de matrices dinámicas: