Fórmula genérica
=$A$1:INDEX($A:$A,lastrow)
Resumen
Una forma de crear un rango dinámico con nombre en Excel es usar la función INDICE. En el ejemplo que se muestra, el rango con nombre "datos" se define mediante la siguiente fórmula:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
que se resuelve en el rango $ A $ 2: $ A $ 10.
Nota: esta fórmula está destinada a definir un rango con nombre que se puede utilizar en otras fórmulas.
Explicación
Esta página muestra un ejemplo de un rango dinámico con nombre creado con la función INDICE junto con la función COUNTA. Los rangos dinámicos con nombre se expanden y contraen automáticamente cuando se agregan o eliminan datos. Son una alternativa al uso de una tabla de Excel, que también cambia de tamaño a medida que se agregan o eliminan datos.
La función INDICE devuelve el valor en una posición determinada en un rango o matriz. Puede usar INDICE para recuperar valores individuales o filas y columnas completas en un rango. Lo que hace que INDEX sea especialmente útil para rangos dinámicos con nombre es que en realidad devuelve una referencia. Esto significa que puede usar INDEX para construir una referencia mixta como $ A $ 1: A100.
En el ejemplo que se muestra, el rango con nombre "datos" se define mediante la siguiente fórmula:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
que se resuelve en el rango $ A $ 2: $ A $ 10.
Cómo funciona esta fórmula
Tenga en cuenta primero que esta fórmula se compone de dos partes que se encuentran a cada lado del operador de rango (:). A la izquierda, tenemos la referencia inicial para el rango, codificada como:
$A$2
A la derecha está la referencia final para el rango, creado con ÍNDICE como este:
INDEX($A:$A,COUNTA($A:$A))
Aquí, alimentamos INDICE toda la columna A para la matriz, luego usamos la función CONTAR para calcular la "última fila" en el rango. COUNTA funciona bien aquí porque hay 10 valores en la columna A, incluida una fila de encabezado. CONTAR, por tanto, devuelve 10, que va directamente a ÍNDICE como número de fila. INDEX luego devuelve una referencia a $ A $ 10, la última fila utilizada en el rango:
INDEX($A:$A,10) // resolves to $A$10
Entonces, el resultado final de la fórmula es este rango:
$A$2:$A$10
Una gama bidimensional
El ejemplo anterior funciona para un rango unidimensional. Para crear un rango dinámico bidimensional donde el número de columnas también es dinámico, puede usar el mismo enfoque, expandido así:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Como antes, COUNTA se usa para calcular la "última columna", y usamos COUNTA nuevamente para obtener la "última columna". Estos se suministran al índice como número_fila y núm_columna, respectivamente.
Sin embargo, para la matriz, proporcionamos la hoja de trabajo completa, ingresada como las 1048576 filas, lo que permite que INDEX devuelva una referencia en un espacio 2D.
Nota: Excel 2003 solo admite 65535 filas.
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
