Функция excel адрес: АДРЕС (функция АДРЕС) — Служба поддержки Майкрософт

Содержание

АДРЕС (функция АДРЕС) — Служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование функции АДРЕС в Microsoft Excel. Чтобы узнать больше о работе с почтовыми адресами и создании почтовых наклеек, см. по ссылкам в разделе См. также.

Описание


Функцию АДРЕС можно использовать для получения адреса ячейки на листе по номерам строки и столбца. Например, функция АДРЕС(2;3) возвращает значение $C$2. Еще один пример: функция АДРЕС(77;300) возвращает значение $KN$77. Чтобы передать функции АДРЕС номера строки и столбца в качестве ее аргументов, можно использовать другие функции (например, функции СТРОКА и СТОЛБЕЦ).



Синтаксис


АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])

Аргументы функции АДРЕС указаны ниже.


  • Номер_строки    Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.


  • Номер_столбца    Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.


  • Тип_ссылки    Необязательный аргумент. Задает тип возвращаемой ссылки.







Тип_ссылки


Возвращаемый тип ссылки

1 или опущен

Абсолютный

2

Абсолютная строка; относительный столбец

3

Относительная строка; абсолютный столбец

4

Относительный

  • org/ListItem»>


    A1    Необязательный аргумент. Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами. При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами. Если аргумент А1 имеет значение ИСТИНА или опущен, то функция АДРЕС возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ, функция АДРЕС возвращает ссылку типа R1C1.

    Примечание: Чтобы изменить тип ссылок, используемый Microsoft Excel, откройте вкладку Файл, а затем нажмите кнопку Параметры и выберите пункт Формулы. В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1.


  • Имя_листа    Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула =АДРЕС(1;1;;;»Лист2″) возвращает значение Лист2!$A$1. Если аргумент имя_листа отсутствует, имя листа не используется, и адрес, возвращаемый функцией, ссылается на ячейку текущего листа.

Пример


Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.








Формула


Описание


Результат

=АДРЕС(2;3)

Абсолютная ссылка

$C$2

=АДРЕС(2;3;2)

Абсолютная строка; относительный столбец

C$2

=АДРЕС(2;3;2;ЛОЖЬ)

Абсолютная строка; относительный столбец с типом ссылки R1C1

R2C[3]

=АДРЕС(2;3;1;ЛОЖЬ;»[Книга1]Лист1″)

Абсолютная ссылка на другую книгу и лист

‘[Книга1]Лист1’!R2C3

=АДРЕС(2;3;1;ЛОЖЬ;»ЛИСТ EXCEL»)

Абсолютная ссылка на другой лист

‘ЛИСТ EXCEL’!R2C3

Функция АДРЕС() в EXCEL.

Примеры и описание


Функция

АДРЕС()

, английский вариант ADDRESS(), возвращает адрес ячейки на листе, для которой указаны номера строки и столбца. Например, формула

АДРЕС(2;3)

возвращает значение

$C$2

.

Функция

АДРЕС()

возвращает

текстовое

значение в виде адреса ячейки.

Синтаксис функции


АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [a1], [имя_листа])


Номер_строки

Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.


Номер_столбца

Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.

Последние 3 аргумента являются необязательными.


[Тип_ссылки]

Задает тип возвращаемой ссылки:


  • 1

    или опущен:

    абсолютная ссылка

    , например

    $D$7


  • 2

    : абсолютная ссылка на строку; относительная ссылка на столбец, например

    D$7


  • 3

    : относительная ссылка на строку; абсолютная ссылка на столбец, например

    $D7


  • 4

    : относительная ссылка, например

    D7


[а1]

Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами, например

D7

. При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами, например R7C5 (R означает ROW — строка, С означает COLUMN — столбец). Если аргумент А1 имеет значение ИСТИНА или 1 или опущен, то функция

АДРЕС()

возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ (или 0), функция

АДРЕС()

возвращает ссылку типа R1C1.

Чтобы изменить тип ссылок, используемый Microsoft Excel, нажмите кнопку

Microsoft Office

, затем нажмите кнопку

Параметры Excel

(внизу окна) и выберите пункт

Формулы

. В группе

Работа с формулами

установите или снимите флажок

Стиль ссылок R1C1

.


[Имя_листа]

Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула

=АДРЕС(1;1;;;»Лист2″)

возвращает значение Лист2!$A$1.

Примеры

Как видно из рисунка ниже (см.

файл примера

) функция

АДРЕС()

возвращает адрес ячейки во всевозможных форматах.

Чаще всего адрес ячейки требуется, чтобы вывести значение ячейки. Для этого используется другая функция

ДВССЫЛ()

.

Формула

=ДВССЫЛ(АДРЕС(6;5))

просто выведет значение из 6-й строки 5 столбца (Е). Эта формула эквивалентна формуле

=Е6

.

Возникает вопрос: «Зачем весь этот огород с функцией

АДРЕС()

?». Дело в том, что существуют определенные задачи, в которых использование функции

АДРЕС()

очень удобно, например

Транспонирование таблиц

или

Нумерация столбцов буквами

или

Поиск позиции ТЕКСТа с выводом значения из соседнего столбца.

Функция АДРЕС Excel для получения адреса ячейки и т. д.

Учебное пособие дает краткое введение в синтаксис функции АДРЕС и показывает, как использовать ее для возврата адреса ячейки Excel и т. д.

Чтобы создать ссылку на ячейку в Excel, вы можете вручную ввести координаты столбца и строки. Кроме того, вы можете получить адрес ячейки Excel из номеров строк и столбцов, предоставленных функции АДРЕС. Практически бесполезен сам по себе, в сочетании с другими функциями этот прием может быть единственным решением в ситуациях, когда нет возможности обратиться к ячейке напрямую.

Функция АДРЕС в Excel — синтаксис и основное использование

Функция АДРЕС предназначена для получения адреса ячейки в Excel на основе указанных номеров строки и столбца. Адрес ячейки возвращается в виде текстовой строки, а не фактической ссылки.

Функция доступна во всех версиях Excel для Microsoft 365 — Excel 2007.

Синтаксис функции АДРЕС следующий:

АДРЕС (номер_строки, номер_столбца, [номер_абс.], [a1], [текст_листа])

Обязательны первые два аргумента:

row_num — номер строки для использования в ссылке на ячейку.

номер_столбца — номер столбца для построения ссылки на ячейку.

Последние три аргумента, определяющие формат ссылки на ячейку, являются необязательными:

abs_num — тип ссылки, абсолютная или относительная. Он может принимать любое из следующих чисел; значение по умолчанию абсолютное.

  • 1 или опущен — абсолютная ссылка на ячейку, например $A$1
  • 2 — смешанная ссылка: относительный столбец и абсолютная строка, например A$1
  • 3 — смешанная ссылка: абсолютный столбец и относительная строка, например $A1
  • 4 — относительная ссылка на ячейку, например A1

a1 — стиль ссылки, A1 или R1C1. Если этот параметр опущен, используется стиль A1 по умолчанию.

  • 1 или TRUE или опущено — возвращает адрес ячейки в стиле ссылок A1, где столбцы — это буквы, а строки — числа.
  • 0 или FALSE — возвращает адрес ячейки в стиле ссылок R1C1, где строки и столбцы представлены числами.

sheet_text — имя рабочего листа для включения во внешнюю ссылку. Имя листа должно быть предоставлено в виде текстовой строки и заключено в кавычки, например. «Лист2». Если этот параметр опущен, имя рабочего листа не используется, и по умолчанию используется адрес текущего листа.

Например:

=АДРЕС(1,1) — возвращает адрес первой ячейки (т.е. ячейки на пересечении первой строки и первого столбца) в виде абсолютной ссылки на ячейку $A$1.

=АДРЕС(1,1,4) — возвращает адрес первой ячейки как относительную ссылку на ячейку A1.

В следующей таблице вы найдете еще несколько типов ссылок, которые могут быть возвращены формулами ADDRESS.

Формула Результат Описание
=АДРЕС(1,2) $B$1 Абсолютная ссылка на ячейку
=АДРЕС(1,2,4) В1 Относительная ссылка на ячейку
=АДРЕС(1,2,2) B$1 Относительный столбец и абсолютная строка
=АДРЕС(1,2,3) $B1 Абсолютный столбец и относительная строка
=АДРЕС(1,2,1,ЛОЖЬ) Р1С2 Абсолютная ссылка в стиле R1C1
=АДРЕС(1,2,4,ЛОЖЬ) Р[1]С[2] Относительная ссылка в стиле R1C1
=АДРЕС(1,2,1,»Лист2″) Лист2!$B$1 Абсолютная ссылка на другой лист
=АДРЕС(1,2,4,»Лист2″) Лист2!B1 Относительная ссылка на другой лист

Как использовать функцию АДРЕС в Excel — примеры формул

В приведенных ниже примерах показано, как использовать функцию АДРЕС внутри больших формул для выполнения более сложных задач.

Вернуть значение ячейки в заданной строке и столбце

Если ваша цель — получить значение из определенной ячейки на основе номеров строк и столбцов, используйте функцию АДРЕС вместе с ДВССЫЛ:

НЕПРЯМОЙ (АДРЕС (номер_строки, номер_столбца))

Функция АДРЕС выводит адрес ячейки в виде текста. Функция ДВССЫЛ превращает этот текст в обычную ссылку и возвращает значение из соответствующей ячейки.

Например, чтобы получить значение ячейки на основе номера строки в E1 и номера столбца в E2, используйте следующую формулу:

=ДВССЫЛ(АДРЕС(E1,E2))

Получить адрес ячейки с наибольшим или наименьшим значением

В этом примере мы сначала найдем самые высокие и самые низкие значения в диапазоне B2:B7, используя функции MAX и MIN, и выведем эти значения в специальные ячейки:

Ячейка E2: =MAX(B2:B7)

Ячейка F2: = МИН(B2:B7)

Затем мы будем использовать АДРЕС в сочетании с функцией ПОИСКПОЗ, чтобы получить адреса ячеек.

Ячейка с максимальным значением:

=АДРЕС(СООТВЕТСТВИЕ(E2,B:B,0), СТОЛБЦ(B2))

Ячейка с минимальным значением:

=АДРЕС(СООТВЕТСТВИЕ(F2,B:B,0), СТОЛБЦ(B2))

Если вам не нужны самые высокие и самые низкие значения в отдельных ячейках, вы можете вложить функцию MAX/MIN в первый аргумент ПОИСКПОЗ. Например:

Ячейка с наибольшим значением:

=АДРЕС(СООТВЕТСТВИЕ(МАКС(B2:B7),B:B,0), СТОЛБЦ(B2))

Ячейка с наименьшим значением:

=АДРЕС(СООТВЕТСТВИЕ(МИН(B2:B7),B:B,0), СТОЛБЦ(B2))

Как работают эти формулы

Чтобы найти номер строки, вы используете функцию ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления]), которая возвращает относительную позицию искомого_значения в искомом_массиве. В нашей формуле искомое значение — это число, возвращаемое функцией MAX или MIN, а искомый массив — это весь столбец. Следовательно, относительное положение искомого значения в массиве точно соответствует номеру строки на листе.

Чтобы найти номер столбца, вы используете функцию COLUM. Конечно, ничто не мешает вводить число прямо в формулу, но СТОЛБЦ избавляет от хлопот ручного подсчета в случае, если целевой столбец находится в середине листа.

Получить букву столбца из столбца с номером

Чтобы превратить любое заданное число в букву столбца, используйте функцию АДРЕС внутри ПОДСТАВКИ:

ПОДСТАВИТЬ (АДРЕС (1, номер_столбца , 4), «1», «»)

В качестве примера найдем букву столбца, соответствующую числу в A2:

=ЗАМЕНИТЬ(АДРЕС(1,A2,4),"1","")

Глядя на результаты ниже, мы можем сказать, что первый столбец на листе — это A, что очевидно; 10-й столбец — J, 50-й столбец -й — AX, а 100-й -й столбец — CV:

.

Как работает эта формула

Для начала настройте функцию АДРЕС так, чтобы она возвращала относительную ссылку на первую ячейку в целевом столбце:

  • Для номера строки используйте 1.
  • В качестве номера столбца укажите ссылку на ячейку, содержащую число, в нашем примере A2.
  • В качестве аргумента число_абс введите 4.

В результате ADDRESS(1,A2,4) вернет A1.

Чтобы избавиться от координаты строки, оберните приведенную выше формулу функцией ПОДСТАВИТЬ и замените «1» пустой строкой («»). Сделанный!

Получить адрес именованного диапазона

Чтобы найти адрес именованного диапазона в Excel, сначала необходимо получить первую и последнюю ссылки на ячейки, а затем соединить их вместе. Это работает немного по-другому в преддинамическом Excel (2019 г.и старше) и Dynamic Array Excel (Office 365 и Excel 2021). Приведенные ниже примеры относятся к Excel 2019 — Excel 2007. Инструкции для Excel 365 и Excel 2021 находятся здесь.

Как получить адрес первой ячейки в диапазоне

Чтобы вернуть ссылку на первую ячейку в именованном диапазоне, используйте следующую общую формулу:

АДРЕС(СТРОКА( диапазон ),СТОЛБЦ( диапазон ))

Предполагая, что диапазон называется «Продажи», реальная формула выглядит следующим образом:

=АДРЕС(СТРОКА(Продажи), СТОЛБЦ(Продажи))

И возвращает адрес верхней левой ячейки в диапазоне:

В этой формуле функции СТРОКА и СТОЛБЦ возвращают массив всех номеров строк и столбцов в диапазоне соответственно. На основе этих чисел функция АДРЕС строит массив адресов ячеек. Но поскольку формула вводится в одну ячейку, отображается только первый элемент массива, соответствующий первой ячейке диапазона.

Как получить адрес последней ячейки в диапазоне

Чтобы найти адрес последней ячейки в именованном диапазоне, используйте эту общую формулу:

АДРЕС(СТРОКА( диапазон )+СТРОКИ( диапазон )-1,СТОЛБОН( диапазон )+СТОЛБЦ( диапазон )-1)

Применительно к нашему диапазону «Продажи» формула принимает следующий вид:

=АДРЕС(СТРОКА(Продажи) + СТРОКИ(Продажи)-1, СТОЛБЦ(Продажи) + СТОЛБЦЫ(Продажи)-1)

И возвращает ссылку на правую нижнюю ячейку диапазона:

На этот раз нам нужны более сложные вычисления для определения номера строки. Как и в предыдущем примере, функция ROW дает нам массив всех номеров строк в диапазоне, {4;5;6;7} в нашем случае. Нам нужно «сдвинуть» эти числа на общее количество строк минус 1, чтобы первый элемент в массиве стал номером последней строки. Чтобы найти общее количество строк, мы используем функцию СТРОКИ и вычитаем 1 из ее результата: (4-1=3). Затем мы добавляем 3 к каждому элементу исходного массива, чтобы сделать требуемый сдвиг: {4;5;6;7} + 3 = {7;8;9;10}.

Номер столбца вычисляется аналогичным образом: {2,3,4}+3-1 = {4,5,6}

Из приведенных выше массивов номеров строк и столбцов функция АДРЕС собирает массив адресов ячеек, но возвращает только первый, соответствующий последней ячейке в диапазоне.

Тот же результат можно получить, выбрав максимальные значения из массивов номеров строк и столбцов. Однако это работает только в формуле массива, для правильного завершения которой необходимо нажать Ctrl + Shift + Enter:

=АДРЕС(МАКС(СТРОКА(Продажи)), МАКС(СТОЛБЦ(Продажи)))

Как получить полный адрес именованного диапазона

Чтобы вернуть полный адрес именованного диапазона, вам просто нужно соединить две формулы из предыдущих примеров и вставить оператор диапазона (:) между ними.

АДРЕС(СТРОКА( диапазон ), СТОЛБЦ( диапазон )) & «:» & АДРЕС(СТРОКА( диапазон ) + СТРОКИ( диапазон )-1, СТОЛБЦ( диапазон ) + СТОЛБЦ( диапазон )-1)

Чтобы это работало для нашего примера набора данных, мы заменяем общий «диапазон» реальным именем диапазона «Продажи»:

=АДРЕС(СТРОКА(Продажи), СТОЛБЦ(Продажи)) & ":" & АДРЕС(СТРОКА(Продажи) + СТРОКИ(Продажи)-1, СТОЛБЦ(Продажи) + СТОЛБЦЫ(Продажи)-1)

И получить полный адрес диапазона как абсолютную ссылку $B$4:$D$7:

Чтобы вернуть адрес диапазона как относительную ссылку (без знака $, например, B4:D7), установите для аргумента abs_num в обеих функциях ADDRESS значение 4: 9.0005

=АДРЕС(СТРОКА(Продажи), СТОЛБЦ(Продажи), 4) & ":" & АДРЕС(СТРОКА(Продажи) + СТРОКИ(Продажи)-1, СТОЛБЦЫ(Продажи) + СТОЛБЦЫ(Продажи)-1, 4)

Естественно, такие же изменения можно внести в отдельные формулы для первой и последней ячейки, и результат будет выглядеть примерно так:

Как получить адрес именованного диапазона в Excel 365 и Excel 2021

В отличие от традиционного поведения «одна формула — одна ячейка» в старых версиях, в новом Excel любая формула, которая потенциально может возвращать несколько значений, делает это автоматически. Такое поведение называется разливом.

Например, вместо возврата адреса первой ячейки приведенная ниже формула выводит адреса каждой ячейки в именованном диапазоне:

=АДРЕС(СТРОКА(Продажи), СТОЛБЦ(Продажи))

Чтобы получить адрес только первой ячейки , вам необходимо включить неявное пересечение, которое срабатывает по умолчанию в Excel 2019 и более ранних версиях. Для этого перед именами диапазонов поставьте символ @ (оператор неявного пересечения):

=АДРЕС(@СТРОКА(Продажи), @СТОЛБЦ(Продажи))

Аналогичным образом можно исправить и другие формулы.

Чтобы получить последнюю ячейку в диапазоне:

=АДРЕС(@СТРОКА(Продажи) + СТРОКИ(Продажи)-1, @СТОЛБЦ(Продажи) + СТОЛБЦЫ(Продажи)-1)

Чтобы получить адрес именованного диапазона :

=АДРЕС(@СТРОКА(Продажи), @СТОЛБЦ(Продажи)) & ":" & АДРЕС(@СТРОКА(Продажи) + СТРОКИ(Продажи)-1, @СТОЛБЦЫ(Продажи) + СТОЛБЦЫ(Продажи)-1)

На снимке экрана ниже показаны результаты:

Совет. При открытии листа с формулами, созданными в более старой версии, в динамическом массиве Excel неявный оператор пересечения вставляется Excel автоматически.

Вот как вы возвращаете адрес ячейки в Excel. Чтобы поближе ознакомиться со всеми формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Функция АДРЕС Excel — примеры формул (файл .xlsx)

Вас также может заинтересовать

Как использовать функцию АДРЕС (WS)

В этом учебнике Excel объясняется, как использовать функцию АДРЕС Excel с синтаксисом и примерами.

Описание

Функция АДРЕС Microsoft Excel возвращает текстовое представление адреса ячейки.

Функция АДРЕС — это встроенная функция Excel, относящаяся к категории Функция поиска/справки . Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию АДРЕС можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции АДРЕС в Microsoft Excel:

 АДРЕС( строка, столбец, [тип_ссылки], [стиль_ссылки], [имя_листа] )

Параметры или аргументы

строка
Номер строки для использования в адресе ячейки.
столбец
Номер столбца для использования в адресе ячейки.
ref_type

Дополнительно. Это тип ссылки для использования. Если этот параметр опущен, предполагается, что ref_type имеет значение 1. Это может быть любое из следующих значений:

Значение Пояснение
1 Абсолютная привязка.
Например: $A$1
2 Относительный столбец; абсолютная строка
Например: A$1
3 Абсолютный столбец; относительная строка
Например: $A1
4 Относительная ссылка.

Читайте также: