При использовании формул поиска в Excel (таких как VLOOKUP, XLOOKUP или INDEX / MATCH) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке / столбце) в качестве результата.
Но в некоторых случаях вместо получения значения может потребоваться, чтобы формула возвращала адрес ячейки значения.
Это может быть особенно полезно, если у вас большой набор данных и вы хотите узнать точное положение результата формулы поиска.
В 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)
В приведенной выше формуле я использовал функцию ПОИСКПОЗ, чтобы узнать номер строки, содержащей данный идентификатор сотрудника.
И поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.
Эта формула отлично работает, но у нее есть один недостаток: она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.
Это связано с тем, что, когда я указываю второй аргумент (номер столбца) как 3, он жестко запрограммирован и не изменится.
Если я добавлю какой-либо столбец слева от набора данных, формула будет считать 3 столбца с начала рабочего листа, а не с начала набора данных.
Итак, если у вас есть фиксированный набор данных и вам нужна простая формула, это сработает.
Но если вам нужно, чтобы это было более надежным, используйте тот, который описан в следующем разделе.
Поиск и возврат адреса ячейки с помощью функции CELL
Хотя функция АДРЕС была создана специально, чтобы дать вам ссылку на ячейку с указанным номером строки и столбца, есть еще одна функция, которая также делает это.
Это называется функцией ЯЧЕЙКА (и она может дать вам гораздо больше информации о ячейке, чем функция АДРЕС).
Ниже приведен синтаксис функции ЯЧЕЙКА:
= ЯЧЕЙКА (тип_информации; [ссылка])
куда:
- info_type: информация о нужной ячейке. Это может быть адрес, номер столбца, имя файла и т. Д.
- [Справка]: Необязательный аргумент, в котором вы можете указать ссылку на ячейку, для которой вам нужна информация о ячейке.
Теперь давайте посмотрим на пример, в котором вы можете использовать эту функцию для поиска и получения ссылки на ячейку.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро узнать адрес ячейки, в которой находится отдел для идентификатора сотрудника KR256.
Ниже приведена формула, которая сделает это:
= ЯЧЕЙКА ("адрес", ИНДЕКС ($ A $ 1: $ D $ 20, ПОИСКПОЗ ("KR256", $ A $ 1: $ A $ 20,0), 3))
Приведенная выше формула довольно проста.
Я использовал формулу ИНДЕКС в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.
А затем просто обернул его в функцию CELL и попросил вернуть адрес ячейки с этим значением, которое я получаю из формулы ИНДЕКС.
Теперь вот секрет того, почему это работает — формула ИНДЕКС возвращает значение подстановки, когда вы даете ей все необходимые аргументы. Но в то же время он также вернет ссылку на эту результирующую ячейку.
В нашем примере формула ИНДЕКС возвращает «Продажи» в качестве результирующего значения, но в то же время вы также можете использовать ее, чтобы дать вам ссылку на ячейку этого значения вместо самого значения.
Обычно, когда вы вводите формулу ИНДЕКС в ячейку, она возвращает значение, потому что это именно то, что от нее ожидается. Но в сценариях, где требуется ссылка на ячейку, формула ИНДЕКС даст вам ссылку на ячейку.
В этом примере это именно то, что он делает.
И самое лучшее в использовании этой формулы — это то, что она не привязана к первой ячейке на листе. Это означает, что вы можете выбрать любой набор данных (который может находиться в любом месте рабочего листа), использовать формулу ИНДЕКС для регулярного поиска, и он все равно даст вам правильный адрес.
А если вы вставите дополнительную строку или столбец, формула изменится соответствующим образом, чтобы дать вам правильный адрес ячейки.
Итак, это две простые формулы, которые вы можете использовать для поиска и найти и вернуть адрес ячейки вместо значения в Excel.
Надеюсь, вы нашли этот урок полезным.
