Excel

Rango dinámico con nombre con OFFSET

Dynamic Named Range With Offset

Fórmula de Excel: rango dinámico con nombre con OFFSETFórmula genérica
= OFFSET (origin,0,0, COUNTA (range), COUNTA (range))
Resumen

Una forma de crear una dinámica rango con nombre con una fórmula es utilizar la función OFFSET junto con la función CONTAR. Los rangos dinámicos también se conocen como rangos en expansión: se expanden y contraen automáticamente para adaptarse a datos nuevos o eliminados.





Nota: OFFSET es una función volátil, lo que significa que se recalcula con cada cambio en una hoja de trabajo. Con una máquina moderna y un conjunto de datos más pequeño, esto no debería causar ningún problema, pero es posible que observe un rendimiento más lento en conjuntos de datos grandes. En ese caso, considere la posibilidad de construir un rango dinámico con nombre con la función INDICE en su lugar.

En el ejemplo que se muestra, la fórmula utilizada para el rango dinámico es:





|_+_| Nota: esta fórmula está destinada a definir un rango con nombre que se puede utilizar en otras fórmulas. Explicación

Esta fórmula utiliza la función DESPLAZAMIENTO para generar un rango que se expande y contrae ajustando la altura y el ancho en función de un recuento de celdas no vacías.

El primer argumento en OFFSET representa la primera celda de los datos (el origen), que en este caso es la celda B5. Los siguientes dos argumentos son compensaciones para filas y columnas, y se proporcionan como cero.



obteniendo #### en Excel

Los dos últimos argumentos representan alto y ancho. La altura y el ancho se generan sobre la marcha utilizando COUNTA, lo que hace que la referencia resultante sea dinámica.

Para la altura, usamos la función CONTAR para contar valores no vacíos en el rango B5: B100. Esto asume que no hay valores en blanco en los datos ni valores más allá de B100. COUNTA devuelve 6.

Para el ancho, usamos la función CONTAR para contar valores no vacíos en el rango B5: Z5. Esto supone que no hay celdas de encabezado ni encabezados más allá de Z5. COUNTA devuelve 6.

En este punto, la fórmula se ve así:

|_+_|

Con esta información, OFFSET devuelve una referencia a B5: G10, que corresponde a un rango de 6 filas de altura por 6 columnas de ancho.

Nota: Los rangos utilizados para la altura y el ancho deben ajustarse para que coincidan con el diseño de la hoja de trabajo.

Variación con referencias completas de columnas / filas

También puede usar referencias completas de columnas y filas para la altura y el ancho de la siguiente manera:

|_+_|

Tenga en cuenta que la altura se está ajustando con -2 para tener en cuenta los valores de encabezado y título en las celdas B4 y B2. La ventaja de este enfoque es la simplicidad de los rangos dentro de COUNTA. La desventaja proviene del enorme tamaño de las columnas y filas completas; se debe tener cuidado para evitar valores erróneos fuera del rango, ya que pueden desequilibrar fácilmente la cuenta.

Determinando la última fila

Hay varias formas de determinar la última fila (última posición relativa) en un conjunto de datos, según la estructura y el contenido de los datos en la hoja de trabajo:

Autor Dave Bruns


^