Общая формула
=ADDRESS(ROW(nr),COLUMN(nr))&":"&ADDRESS(ROW(nr)+ROWS(nr)-1,COLUMN(nr)+COLUMNS(nr)-1)
Резюме
Чтобы получить полный адрес именованного диапазона с помощью формулы Excel, вы можете использовать функцию ADDRESS вместе с функциями ROW и COLUMN. В показанном примере формула в G5:
=ADDRESS(ROW(data),COLUMN(data),4)&":"&ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
где «данные» - именованный диапазон B5: D10
Объяснение
Ядром этой формулы является функция АДРЕС, которая используется для возврата адреса ячейки на основе заданной строки и столбца. К сожалению, формула несколько усложняется, потому что нам нужно использовать АДРЕС дважды: один раз, чтобы получить адрес первой ячейки в диапазоне, и один раз, чтобы получить адрес последней ячейки в диапазоне. Два результата объединяются с помощью конкатенации и оператора диапазона (:), и полный диапазон возвращается в виде текста.
Чтобы получить первую ячейку в диапазоне, мы используем это выражение:
=ADDRESS(ROW(data),COLUMN(data))
СТРОКА возвращает номер первой строки, связанной с диапазоном, 5 *.
COLUMN возвращает номер первого столбца, связанный с диапазоном, 2.
Если для abs_num установлено значение 4 (относительное), АДРЕС возвращает текст «B5».
=ADDRESS(5,2,4) // returns "B5"
Чтобы получить последнюю ячейку в диапазоне, мы используем это выражение:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
См. Эту страницу для подробного объяснения.
По сути, мы следуем той же идее, что и выше, добавляя простую математику для вычисления последней строки и последнего столбца диапазона, которые, как и раньше, передаются в ADDRESS с параметром abs_num, равным 4. Это сводится к следующему выражению, которое возвращает текст " D10 »:
=ADDRESS(10,4,4) // returns "D10"
Оба результата объединяются двоеточием, чтобы получить окончательный адрес диапазона в виде текста:
="B5"&":"&"D10" ="B5:D10
Именованный диапазон из другой ячейки
Чтобы получить адрес для именованного диапазона в другой ячейке, вам нужно использовать функцию ДВССЫЛ. Например, чтобы получить адрес имени в A1, вы должны использовать:
=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, чтобы получить относительный адрес.
* Фактически, во всех случаях, когда мы используем ROW и COLUMN с именованным диапазоном, состоящим из нескольких ячеек, мы возвращаем массив чисел вместо одного значения. Однако, поскольку мы не используем формулу массива, обработка ограничивается первым элементом в этих массивах.
