как узнать адрес ячейки excel
АДРЕС (функция АДРЕС)
В этой статье описаны синтаксис формулы и использование функции АДРЕС в Microsoft Excel. Чтобы узнать больше о работе с почтовыми адресами и создании почтовых наклеек, см. по ссылкам в разделе См. также.
Описание
Синтаксис
Аргументы функции АДРЕС указаны ниже.
Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.
Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.
Тип_ссылки Необязательный аргумент. Задает тип возвращаемой ссылки.
Возвращаемый тип ссылки
Абсолютная строка; относительный столбец
Относительная строка; абсолютный столбец
A1 Необязательный аргумент. Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами. При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами. Если аргумент А1 имеет значение ИСТИНА или опущен, то функция АДРЕС возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ, функция АДРЕС возвращает ссылку типа R1C1.
Примечание: Чтобы изменить тип ссылок, используемый Microsoft Excel, откройте вкладку Файл, а затем нажмите кнопку Параметры и выберите пункт Формулы. В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1.
Имя_листа Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула = АДРЕС( 1;1;;;»Лист2″) возвращает значение Лист2!$A$1. Если аргумент имя_листа отсутствует, имя листа не используется, и адрес, возвращаемый функцией, ссылается на ячейку текущего листа.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Примеры функции АДРЕС для получения адреса ячейки листа Excel
Функция АДРЕС в Excel: описание особенностей синтаксиса
Функция АДРЕС имеет следующую синтаксическую запись:
Первые два аргумента данной функции являются обязательными для заполнения.
Примеры использования функции АДРЕС в Excel
Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных условий. Для работы с актуальными данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.
На листе «Курсы» создана таблица с актуальными курсами валют:
На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):
В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).
Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).
Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:
Как получить адрес ссылки на ячейку Excel?
Пример 2. В таблице содержатся данные о цене товаров, отсортированные в порядке возрастания стоимости. Необходимо получить ссылки на ячейки с минимальной и максимальной стоимостью товаров соответственно.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:
Функция АДРЕС принимает следующие параметры:
Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:
Адрес по номерам строк и столбцов листа Excel в стиле R1C1
Пример 3. В таблице содержится ячейка, данные из которой используются в другом программном продукте. Для обеспечения совместимости необходимо предоставить ссылку на нее в виде R1C1.
Исходная таблица имеет следующий вид:
Исходная таблица.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel78-9.png» >
Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).
В результате получим ссылку:
Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:
Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.
Как вернуть адрес ячейки вместо значения в Excel (простая формула)
При использовании формул поиска в Excel (таких как VLOOKUP, XLOOKUP или INDEX / MATCH) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке / столбце) в качестве результата.
Но в некоторых случаях вместо получения значения может потребоваться, чтобы формула возвращала адрес ячейки значения.
Это может быть особенно полезно, если у вас большой набор данных и вы хотите узнать точное положение результата формулы поиска. В Excel есть несколько функций, которые предназначены именно для этого.
В этом руководстве я покажу вам, как найти и вернуть адрес ячейки вместо значения в Excel с помощью простых формул.
Поиск и возврат адреса ячейки с помощью функции ADDRESS
Функция ADDRESS в MS Excel предназначена именно для этого. Она берет строку и номер столбца и дает вам адрес ячейки этой конкретной ячейки.
Ниже приведен синтаксис функции ADDRESS :
Теперь давайте возьмем пример и посмотрим, как это работает.
Предположим, что есть набор данных, как показано ниже, где у меня есть идентификатор сотрудника, его имя и его отдел, и я хочу быстро узнать адрес ячейки, в которой находится отдел для идентификатора сотрудника KR256.
Ниже приведена формула, которая сделает это:
В приведенной выше формуле я использовал функцию MATCH, чтобы узнать номер строки, содержащей данный идентификатор сотрудника. И поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.
Эта формула отлично работает, но у нее есть один недостаток — она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.
Это потому, что, когда я указываю второй аргумент (номер столбца) как 3, он жестко запрограммирован и не изменится.
Если я добавлю какой-либо столбец слева от набора данных, формула будет считать 3 столбца с начала рабочего листа, а не с начала набора данных.
Итак, если у вас есть фиксированный набор данных и вам нужна простая формула, это сработает.
Но если вам нужно, чтобы это было более надежным, используйте тот, который описан в следующем разделе.
Поиск и возврат адреса ячейки с помощью функции CELL
Хотя функция ADDRESS была создана специально для предоставления вам ссылки на ячейку с указанным номером строки и столбца, есть еще одна функция, которая также делает это.
Это называется функцией CELL (и она может дать вам гораздо больше информации о ячейке, чем функция ADDRESS ).
Ниже приведен синтаксис функции CELL:
Теперь давайте посмотрим на пример, в котором вы можете использовать эту функцию для поиска и получения ссылки на ячейку.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро узнать адрес ячейки, в которой находится отдел для идентификатора сотрудника KR256.
Ниже приведена формула, которая сделает это:
Приведенная выше формула довольно проста.
Я использовал формулу INDEX в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.
А затем просто обернул его в функцию CELL и попросил вернуть адрес ячейки с этим значением, которое я получаю из формулы INDEX.
Вот секрет того, почему это работает: формула INDEX возвращает значение подстановки, когда вы даете ей все необходимые аргументы. Но в то же время он также вернет ссылку на эту результирующую ячейку.
В нашем примере формула INDEX возвращает «Продажи» в качестве результирующего значения, но в то же время вы также можете использовать ее, чтобы дать вам ссылку на ячейку этого значения вместо самого значения.
Обычно, когда вы вводите формулу INDEX в ячейку, она возвращает значение, потому что это именно то, что от нее ожидается. Но в сценариях, где требуется ссылка на ячейку, формула INDEX даст вам ссылку на ячейку.
В этом примере это именно то, что он делает.
И самое лучшее в использовании этой формулы — это то, что она не привязана к первой ячейке на листе. Это означает, что вы можете выбрать любой набор данных (который может быть где угодно на листе), использовать формулу INDEX для регулярного поиска, и он все равно даст вам правильный адрес.
А если вы вставите дополнительную строку или столбец, формула изменится соответствующим образом, чтобы дать вам правильный адрес ячейки.
Как узнать адрес ячейки excel
Как найти в файле Excel заданный текст и вернуть адрес ячейки?