Общая формула
=ADDRESS(MAX(ROW(rng)),MAX(COLUMN(rng)))
Резюме
Чтобы получить адрес последней ячейки в диапазоне, вы можете использовать функцию ADDRESS вместе с функциями ROW, COLUMN и MAX. В показанном примере формула в F5:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)))
где data - именованный диапазон B5: D14.
Объяснение
Функция АДРЕС создает ссылку на основе заданного номера строки и столбца. В этом случае мы хотим получить последнюю строку и последний столбец, используемые данными именованного диапазона (B5: D14).
Чтобы получить последнюю используемую строку, мы используем функцию ROW вместе с функцией MAX следующим образом:
MAX(ROW(data))
Поскольку данные содержат более одной строки, ROW возвращает массив номеров строк:
(5;6;7;8;9;10;11;12;13;14)
Этот массив переходит непосредственно в функцию MAX, которая возвращает наибольшее число:
MAX((5;6;7;8;9;10;11;12;13;14)) // returns 14
Чтобы получить последний столбец, мы используем функцию COLUMN таким же образом:
MAX(COLUMN(data))
Поскольку данные содержат три строки, COLUMN возвращает массив с тремя номерами столбцов:
(2,3,4)
и функция MAX снова возвращает наибольшее число:
MAX((2,3,4)) // returns 4
Оба результата возвращаются непосредственно в функцию ADDRESS, которая создает ссылку на ячейку в строке 14, столбце 4:
=ADDRESS(14,4) // returns $D$14
Если вам нужен относительный адрес вместо абсолютной ссылки, вы можете указать 4 для третьего аргумента следующим образом:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)),4) // returns D14
Альтернатива функции ЯЧЕЙКА
Хотя это не очевидно, функция INDEX возвращает ссылку, поэтому мы можем использовать функцию CELL с INDEX, чтобы получить адрес последней ячейки в таком диапазоне:
=CELL("address",INDEX(data,ROWS(data),COLUMNS(data)))
В этом случае мы используем функцию ИНДЕКС, чтобы получить ссылку на последнюю ячейку в диапазоне, который мы определяем путем передачи итоговых строк и итоговых столбцов для данных диапазона в ИНДЕКС. Мы получаем итоговые строки с помощью функции ROWS и итоговые столбцы с помощью функции COLUMNS:
ROWS(data) // returns 10 COLUMNS(data) // returns 3
С массивом, предоставленным в виде данных, ИНДЕКС затем возвращает ссылку на ячейку D14:
INDEX(data,10,3) // returns reference to D14
Затем мы выполняем функцию CELL с «адресом», чтобы отобразить адрес.
Примечание. Функция ЯЧЕЙКА - это непостоянная функция, которая может вызвать проблемы с производительностью в больших или сложных книгах.
