Fórmula genérica
=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))
Resumen
Una forma de crear un rango dinámico con nombre con una fórmula es utilizar la función DESPLAZAMIENTO 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 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:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
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.
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 asume 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í:
=OFFSET(B5,0,0,6,6)
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 esta manera:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
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 desviar 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:
- Última fila en datos mixtos con espacios en blanco
- Última fila en datos mixtos sin espacios en blanco
- Última fila en datos de texto
- Última fila en datos numéricos
