Fórmula genérica
=ADDRESS(MAX(ROW(rng)),MAX(COLUMN(rng)))
Resumen
Para obtener la dirección de la última celda de un rango, puede usar la función ADDRESS junto con las funciones FILA, COLUMNA y MAX. En el ejemplo que se muestra, la fórmula en F5 es:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)))
donde los datos son el rango con nombre B5: D14.
Explicación
La función DIRECCIÓN crea una referencia basada en un número de fila y columna dado. En este caso, queremos obtener la última fila y la última columna utilizada por los datos del rango con nombre (B5: D14).
Para usar la última fila, usamos la función FILA junto con la función MAX de esta manera:
MAX(ROW(data))
Dado que los datos contienen más de una fila, FILA devuelve una matriz de números de fila:
(5;6;7;8;9;10;11;12;13;14)
Esta matriz va directamente a la función MAX, que devuelve el número más grande:
MAX((5;6;7;8;9;10;11;12;13;14)) // returns 14
Para obtener la última columna, usamos la función COLUMNA de la misma manera:
MAX(COLUMN(data))
Dado que los datos contienen tres filas, COLUMN devuelve una matriz con tres números de columna:
(2,3,4)
y la función MAX vuelve a devolver el número más grande:
MAX((2,3,4)) // returns 4
Ambos resultados se devuelven directamente a la función DIRECCIÓN, que construye una referencia a la celda en la fila 14, columna 4:
=ADDRESS(14,4) // returns $D$14
Si desea una dirección relativa en lugar de una referencia absoluta, puede proporcionar 4 para el tercer argumento como este:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)),4) // returns D14
Alternativa a la función CELDA
Aunque no es obvio, la función INDICE devuelve una referencia, por lo que podemos usar la función CELDA con INDICE para obtener la dirección de la última celda en un rango como este:
=CELL("address",INDEX(data,ROWS(data),COLUMNS(data)))
En este caso, usamos la función INDICE para obtener una referencia a la última celda del rango, que determinamos pasando filas totales y columnas totales para los datos del rango en INDICE. Obtenemos filas totales con la función FILAS y columnas totales con la función COLUMNAS:
ROWS(data) // returns 10 COLUMNS(data) // returns 3
Con la matriz proporcionada como datos, INDICE luego devuelve una referencia a la celda D14:
INDEX(data,10,3) // returns reference to D14
A continuación, la función CELDA con "dirección", para mostrar la dirección.
Nota: La función CELL es una función volátil que puede causar problemas de rendimiento en libros de trabajo grandes o complejos.
