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

При использовании формул поиска в Excel (таких как ВПР, ПРОСМОТРX или ИНДЕКС/ПОИСКПОЗ) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке / столбце) в качестве результата.

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

Это может быть особенно полезно, если у вас большой набор данных и вы хотите узнать точное положение результата формулы поиска.

В Excel есть несколько функций, которые предназначены именно для этого.

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

Поиск и возврат адреса ячейки с помощью функции АДРЕС

Функция АДРЕС в Excel предназначена именно для этого.

Она берёт номер строки и номер столбца и даёт вам адрес ячейки этой конкретной ячейки.

Ниже приведён синтаксис функции АДРЕС:

=АДРЕС(row_num, column_num, [abs_num], [a1], [sheet_text])

где:

• row_num: номер строки ячейки, для которой вы хотите получить адрес ячейки
• column_num: номер столбца ячейки, для которой вы хотите адрес
• [abs_num]: необязательный аргумент, в котором вы можете указать, хотите ли вы, чтобы ссылка на ячейку была абсолютной, относительной или смешанной
• [a1]: необязательный аргумент, в котором вы можете указать, хотите ли вы использовать ссылку в стиле R1C1 или в стиле A1
• [sheet_text]: необязательный аргумент, в котором вы можете указать, хотите ли вы добавить имя листа вместе с адресом ячейки

Теперь давайте возьмём пример и посмотрим, как это работает.

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

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

=АДРЕС(ПОИСКПОЗ("KR256",A1:A20,0),3)

Эта формула отлично работает, но у неё есть один недостаток: она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.

Это связано с тем, что, когда вы указываете второй аргумент (номер столбца) как 3, он жёстко запрограммирован и не изменится.

Если вы добавите какой-либо столбец слева от набора данных, формула будет считать 3 столбца с начала рабочего листа, а не с начала набора данных.

Поиск и возврат адреса ячейки с помощью функции ЯЧЕЙКА

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

Это функция ЯЧЕЙКА (и она может дать вам гораздо больше информации о ячейке, чем функция АДРЕС).

Ниже приведён синтаксис функции ЯЧЕЙКА:

=ЯЧЕЙКА(тип_информации, [ссылка])

где:

• тип_информации: информация о нужной ячейке. Это может быть адрес, номер столбца, имя файла и т. д.
• [ссылка]: необязательный аргумент, в котором вы можете указать ссылку на ячейку, для которой вам нужна информация

Теперь давайте посмотрим на пример, в котором вы можете использовать эту функцию для поиска и получения ссылки на ячейку.

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

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

=ЯЧЕЙКА("адрес",ИНДЕКС($A$1:$D$20,ПОИСКПОЗ("KR256",$A$1:$A$20,0),3))

Приведённая выше формула довольно проста.

Здесь используется формула ИНДЕКС в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.

А затем она просто обёрнута в функцию ЯЧЕЙКА с запросом вернуть адрес ячейки с этим значением, которое получено из формулы ИНДЕКС.

В этом примере формула ИНДЕКС возвращает «Продажи» в качестве результирующего значения, но в то же время её также можно использовать, чтобы получить ссылку на ячейку этого значения вместо самого значения.

Обычно, когда вы вводите формулу ИНДЕКС в ячейку, она возвращает значение, потому что это именно то, что от неё ожидается. Но в сценариях, где требуется ссылка на ячейку, формула ИНДЕКС даёт вам ссылку на ячейку.

В этом примере это именно то, что она делает.

А самое лучшее в использовании этой формулы — то, что она не привязана к первой ячейке на листе. Это означает, что вы можете выбрать любой набор данных (который может находиться в любом месте рабочего листа), использовать формулу ИНДЕКС для регулярного поиска, и она всё равно даст вам правильный адрес.

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

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

Надеюсь, вы нашли этот урок полезным.