Загальна формула
=ADDRESS(MAX(ROW(rng)),MAX(COLUMN(rng)))
Резюме
Щоб отримати адресу останньої комірки в діапазоні, ви можете використовувати функцію АДРЕСА разом із функціями РЯД, СТОЛЬБКА та МАКС. У наведеному прикладі формула в F5 має вигляд:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)))
де дані - іменований діапазон B5: D14.
Пояснення
Функція ADDRESS створює посилання на основі заданого номера рядка та стовпця. У цьому випадку ми хочемо отримати останній рядок і останній стовпець, що використовується іменованими даними діапазону (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)))
У цьому випадку ми використовуємо функцію INDEX, щоб отримати посилання на останню комірку в діапазоні, яку ми визначаємо, передаючи загальну кількість рядків та загальну кількість стовпців для даних діапазону в INDEX. Ми отримуємо загальну кількість рядків з функцією ROWS і загальну кількість стовпців з функцією COLUMNS:
ROWS(data) // returns 10 COLUMNS(data) // returns 3
За допомогою масиву, що надається як дані, INDEX потім повертає посилання на комірку D14:
INDEX(data,10,3) // returns reference to D14
Потім ми функціонуємо КЛІТИНА з адресою, щоб відобразити адресу.
Примітка: Функція CELL - це летюча функція, яка може спричинити проблеми з продуктивністю у великих або складних робочих книгах.
