Fórmula genérica
=ADDRESS(MAX(ROW(rng)),MAX(COLUMN(rng)))
Resumo
Para obter o endereço da última célula em um intervalo, você pode usar a função ADDRESS junto com as funções ROW, COLUMN e MAX. No exemplo mostrado, a fórmula em F5 é:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)))
onde os dados são o intervalo nomeado B5: D14.
Explicação
A função ADDRESS cria uma referência com base em um determinado número de linha e coluna. Neste caso, queremos obter a última linha e a última coluna usadas pelos dados do intervalo nomeado (B5: D14).
Para obter a última linha usada, usamos a função ROW junto com a função MAX como esta:
MAX(ROW(data))
Como os dados contêm mais de uma linha, ROW retorna uma matriz de números de linha:
(5;6;7;8;9;10;11;12;13;14)
Essa matriz vai diretamente para a função MAX, que retorna o maior número:
MAX((5;6;7;8;9;10;11;12;13;14)) // returns 14
Para obter a última coluna, usamos a função COLUMN da mesma maneira:
MAX(COLUMN(data))
Como os dados contêm três linhas, COLUMN retorna uma matriz com três números de coluna:
(2,3,4)
e a função MAX retorna novamente o maior número:
MAX((2,3,4)) // returns 4
Ambos os resultados são retornados diretamente para a função ADDRESS, que constrói uma referência à célula na linha 14, coluna 4:
=ADDRESS(14,4) // returns $D$14
Se você quiser um endereço relativo em vez de uma referência absoluta, pode fornecer 4 para o terceiro argumento, como este:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)),4) // returns D14
Alternativa de função CELL
Embora não seja óbvio, a função INDEX retorna uma referência, então podemos usar a função CELL com INDEX para obter o endereço da última célula em um intervalo como este:
=CELL("address",INDEX(data,ROWS(data),COLUMNS(data)))
Nesse caso, usamos a função INDEX para obter uma referência para a última célula no intervalo, que determinamos passando o total de linhas e colunas para os dados do intervalo em INDEX. Obtemos o total de linhas com a função ROWS e o total de colunas com a função COLUMNS:
ROWS(data) // returns 10 COLUMNS(data) // returns 3
Com a matriz fornecida como dados, INDEX retorna uma referência à célula D14:
INDEX(data,10,3) // returns reference to D14
Em seguida, a função CELL com "endereço", para exibir o endereço.
Observação: a função CELL é uma função volátil que pode causar problemas de desempenho em pastas de trabalho grandes ou complexas.
