Загальна формула
=ADDRESS(ROW(nr),COLUMN(nr))&":"&ADDRESS(ROW(nr)+ROWS(nr)-1,COLUMN(nr)+COLUMNS(nr)-1)
Резюме
Щоб отримати повну адресу іменованого діапазону за допомогою формули Excel, ви можете використовувати функцію АДРЕСА разом із функціями РЯДОК та СТОЛБОК. У наведеному прикладі формула в G5 має вигляд:
=ADDRESS(ROW(data),COLUMN(data),4)&":"&ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
де "дані" - названий діапазон B5: D10
Пояснення
Ядром цієї формули є функція ADDRESS, яка використовується для повернення адреси комірки на основі заданого рядка та стовпця. На жаль, формула дещо ускладнюється, тому що нам потрібно використовувати АДРЕСУ двічі: один раз, щоб отримати адресу першої комірки в діапазоні, і один раз, щоб отримати адресу останньої комірки в діапазоні. Два результати поєднуються за допомогою конкатенації та оператора діапазону (:), а повний діапазон повертається у вигляді тексту.
Щоб отримати першу комірку в діапазоні, ми використовуємо такий вираз:
=ADDRESS(ROW(data),COLUMN(data))
ROW повертає номер першого рядка, пов'язаний з діапазоном, 5 *.
COLUMN повертає номер першого стовпця, пов'язаний з діапазоном, 2.
Коли abs_num встановлено на 4 (відносний), ADDRESS повертає текст "B5".
=ADDRESS(5,2,4) // returns "B5"
Щоб отримати останню комірку в діапазоні, ми використовуємо такий вираз:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
Детальне пояснення див. На цій сторінці.
По суті, ми дотримуємося тієї ж ідеї, що і вище, додаючи просту математику для обчислення останнього рядка та останнього стовпця діапазону, які подаються, як і раніше, в АДРЕСУ з abs_num, встановленим на 4. Це зводиться до наступного виразу, який повертає текст " D10 ":
=ADDRESS(10,4,4) // returns "D10"
Обидва результати об'єднуються двокрапкою, щоб отримати остаточну адресу діапазону як текст:
="B5"&":"&"D10" ="B5:D10
Іменований діапазон від іншої комірки
Щоб отримати адресу для названого діапазону в іншій комірці, вам потрібно буде використовувати функцію INDIRECT. Наприклад, щоб отримати адресу імені в А1, ви повинні використовувати:
=ADDRESS(ROW(INDIRECT(A1)),COLUMN(INDIRECT(A1)))&":"&ADDRESS(ROW(INDIRECT(A1))+ROWS(INDIRECT(A1))-1,COLUMN(INDIRECT(A1))+COLUMNS(INDIRECT(A1))-1)
Встановіть abs_num на 4 всередині ADDRESS, щоб отримати відносну адресу.
* Насправді, у всіх випадках, коли ми використовуємо РЯДОК і СТОЛЬБУ з багатоклітинним іменованим діапазоном, ми повернемо масив чисел замість одного значення. Однак, оскільки ми не використовуємо формулу масиву, обробка обмежується першим елементом у цих масивах.
