Как найти ссылку на ячейку в Excel
Обновлено: 21.11.2024
При использовании формул поиска в Excel (таких как ВПР, ВПР или ИНДЕКС/ПОИСКПОЗ) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке/столбце) в качестве результата.
Но в некоторых случаях вместо получения значения может потребоваться, чтобы формула возвращала адрес ячейки значения.
Это может быть особенно полезно, если у вас большой набор данных и вы хотите узнать точную позицию результата формулы поиска.
В Excel есть несколько функций, предназначенных именно для этого.
В этом руководстве я покажу вам, как с помощью простых формул найти и вернуть адрес ячейки вместо значения в Excel.
Это руководство охватывает:
Поиск и возврат адреса ячейки с помощью функции ADDRESS
Функция АДРЕС в Excel предназначена именно для этого.
Он берет строку и номер столбца и дает вам адрес этой конкретной ячейки.
Ниже приведен синтаксис функции АДРЕС:
- номер_строки: номер строки ячейки, для которой требуется адрес ячейки.
- column_num: номер столбца ячейки, для которой вы хотите получить адрес
- [abs_num]: необязательный аргумент, в котором можно указать, будет ли ссылка на ячейку абсолютной, относительной или смешанной.
- [a1]: необязательный аргумент, в котором можно указать, хотите ли вы использовать ссылку в стиле R1C1 или стиле A1.
- [sheet_text]: необязательный аргумент, в котором вы можете указать, хотите ли вы добавить имя листа вместе с адресом ячейки или нет.
Теперь давайте рассмотрим пример и посмотрим, как это работает.
Предположим, что есть набор данных, как показано ниже, где у меня есть идентификатор сотрудника, его имя и его отдел, и я хочу быстро узнать адрес ячейки, которая содержит отдел для идентификатора сотрудника KR256.
Ниже приведена формула, которая это сделает:
В приведенной выше формуле я использовал функцию ПОИСКПОЗ, чтобы узнать номер строки, содержащей заданный идентификатор сотрудника.
А поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.
Эта формула прекрасно работает, но у нее есть один недостаток: она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.
Это связано с тем, что когда я указываю второму аргументу (номер столбца) значение 3, оно жестко закодировано и не изменится.
Если я добавлю любой столбец слева от набора данных, формула будет считать 3 столбца с начала рабочего листа, а не с начала набора данных.
Итак, если у вас есть фиксированный набор данных и вам нужна простая формула, это подойдет.
Но если вам нужно, чтобы это было более надежным, используйте вариант, описанный в следующем разделе.
Поиск и возврат адреса ячейки с помощью функции CELL
Хотя функция АДРЕС была создана специально для предоставления ссылки на ячейку с указанным номером строки и столбца, существует и другая функция, которая делает то же самое.
Она называется функцией ЯЧЕЙКА (и может дать гораздо больше информации о ячейке, чем функция АДРЕС).
Ниже приведен синтаксис функции ЯЧЕЙКА:
- info_type: информация о нужной ячейке. Это может быть адрес, номер столбца, имя файла и т. д.
- [ссылка]: необязательный аргумент, в котором вы можете указать ссылку на ячейку, для которой вам нужна информация о ячейке.
Теперь давайте рассмотрим пример, в котором вы можете использовать эту функцию для поиска и получения ссылки на ячейку.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите быстро узнать адрес ячейки, в которой находится отдел для идентификатора сотрудника KR256.
Ниже приведена формула, которая это сделает:
Приведенная выше формула довольно проста.
Я использовал формулу ИНДЕКС в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.
А затем просто завернул его в функцию CELL и попросил вернуть адрес ячейки этого значения, которое я получаю из формулы INDEX.
Теперь вот секрет того, почему это работает: формула ИНДЕКС возвращает искомое значение, когда вы даете ей все необходимые аргументы. Но в то же время он также вернет ссылку на результирующую ячейку.
В нашем примере формула ИНДЕКС возвращает "Продажи" в качестве результирующего значения, но в то же время вы также можете использовать ее для получения ссылки на ячейку этого значения вместо самого значения.
Обычно, когда вы вводите формулу ИНДЕКС в ячейку, она возвращает значение, потому что это то, что от нее ожидается. Но в случаях, когда требуется ссылка на ячейку, формула ИНДЕКС даст вам ссылку на ячейку.
В этом примере это именно то, что он делает.
Лучшее в использовании этой формулы то, что она не привязана к первой ячейке листа. Это означает, что вы можете выбрать любой набор данных (который может находиться в любом месте рабочего листа), использовать формулу ИНДЕКС для обычного поиска, и он все равно даст вам правильный адрес.
И если вы вставите дополнительную строку или столбец, формула изменится соответствующим образом, чтобы дать вам правильный адрес ячейки.
Итак, это две простые формулы, которые можно использовать для поиска и возврата адреса ячейки вместо значения в Excel.
Как узнать, есть ли ссылка на ячейку в другой ячейке Excel?
Как узнать, упоминается ли ячейка в каких-либо формулах или нет? На самом деле в Excel есть встроенная функция под названием «Отслеживание зависимостей», которая может помочь вам легко узнать, есть ли ссылка на ячейку в какой-либо формуле или нет.
Узнать, есть ли ссылка на ячейку в Excel
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!
Включить функцию трассировки зависимостей в Excel очень просто. Сделайте следующее.
<р>1. Выберите ячейку, на которую вы хотите узнать, ссылаются ли на нее какие-либо формулы. <р>2. Затем нажмите «Формулы» > «Отслеживание зависимостей». Смотрите скриншот:<р>3. После активации функции Trace Dependents на листе отображаются стрелки.
Как показано на снимке экрана ниже, эти две стрелки указывают на то, что на активную ячейку A2 ссылаются две формулы, расположенные в ячейке B2 и ячейке B5.
Примечания:
<р>1. Если формулы, ссылающиеся на активную ячейку, отсутствуют, появится диалоговое окно подсказки, как показано на снимке экрана ниже.<р>2. Стрелки можно удалить, нажав Формулы > Удалить стрелки > Удалить зависимые стрелки.
Чтобы получить адрес результата поиска, полученного с помощью функции ИНДЕКС, вы можете использовать функцию ЯЧЕЙКА. В показанном примере формула в ячейке G8 выглядит следующим образом:
Который возвращает адрес $C$8, адрес ячейки, возвращенный ИНДЕКСОМ.
Однако, хотя результат отображается на листе как 60, функция ИНДЕКС фактически возвращает ссылку на ячейку C8. Как и любой другой справочник, Excel показывает значение в ячейке.
Помещая ИНДЕКС в функцию ЯЧЕЙКА, мы можем заставить Excel показывать нам адрес ячейки, возвращаемый функцией ИНДЕКС. После того как ИНДЕКС возвращает ссылку, формула разрешается следующим образом:
и функция CELL возвращает абсолютный адрес $C$8 в качестве конечного результата.
В этом примере цель состоит в том, чтобы выполнить поиск с учетом регистра имени в столбце B на основе значения поиска в ячейке F6.По умолчанию Excel не учитывает регистр, и это относится к стандартным формулам поиска, таким как ВПР , КСПР и.
Функция ИНДЕКС Excel возвращает значение в заданном месте в диапазоне или массиве. Вы можете использовать ИНДЕКС для получения отдельных значений или целых строк и столбцов. Функция ПОИСКПОЗ часто используется вместе с ИНДЕКС для предоставления строки и столбца.
ПОИСКПОЗ — это функция Excel, используемая для поиска положения искомого значения в строке, столбце или таблице. ПОИСКПОЗ поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для частичного совпадения. Часто ПОИСКПОЗ сочетается.
Функция Excel CELL возвращает информацию о ячейке на листе. Тип возвращаемой информации указывается как info_type. CELL может получить такие данные, как адрес и имя файла, а также подробную информацию об используемом форматировании.
В этом видео мы рассмотрим, как настроить ИНДЕКС для получения значения по строке и столбцу. Это первая половина уравнения ИНДЕКС/ПОИСКПОЗ.
Обучение формулам Excel
Формулы — это ключ к выполнению задач в Excel. В этом ускоренном обучении вы научитесь использовать формулы для работы с текстом, работы с датами и временем, поиска значений с помощью ВПР и ИНДЕКС и ПОИСКПОЗ, подсчета и суммирования с использованием критериев, динамического ранжирования значений и создания динамических диапазонов. Вы также узнаете, как устранять неполадки, отслеживать ошибки и устранять проблемы. Мгновенный доступ. Подробности здесь.
Загрузить более 100 важных функций Excel
Получите более 100 функций Excel, которые вы должны знать, в одном удобном PDF-файле.
Темы
Ключевые функции
Здравствуйте! Меня зовут Дэйв Брунс. Я управляю компанией Exceljet вместе со своей женой Лизой. Наша цель — помочь вам быстрее работать в Excel. Мы создаем короткие видеоролики и наглядные примеры формул, функций, сводных таблиц, условного форматирования и диаграмм. Подробнее.
Функция АДРЕС Excel возвращает адрес ячейки на основе заданного номера строки и столбца. Например, =АДРЕС(1,1) возвращает $A$1. АДРЕС может возвращать адрес в относительном, смешанном или абсолютном формате и может использоваться для создания ссылки на ячейку внутри формулы.
- номер_строки – номер строки, который будет использоваться в адресе ячейки.
- col_num — номер столбца, который будет использоваться в адресе ячейки.
- abs_num – [необязательно] Тип адреса (т. е. абсолютный, относительный). По умолчанию абсолютное значение.
- a1 – [необязательно] Эталонный стиль, A1 и R1C1. По умолчанию используется стиль A1.
- лист – [необязательно] имя используемого рабочего листа. По умолчанию используется текущий лист.
Функция АДРЕС возвращает адрес ячейки на основе заданного номера строки и столбца. Например, =АДРЕС(1,1) возвращает $A$1. ADDRESS может возвращать относительную, смешанную или абсолютную ссылку и может использоваться для создания ссылки на ячейку внутри формулы. Важно понимать, что ADDRESS возвращает ссылку в виде текстового значения. Если вы хотите использовать этот текст внутри ссылки на формулу, вам нужно будет привести текст к правильной ссылке с помощью функции ДВССЫЛ. Если вы хотите указать номер строки и столбца и получить обратно значение по этому адресу, используйте функцию ИНДЕКС.
Функция АДРЕС принимает пять аргументов: строка, столбец, abs_num, a1 и лист_текст. Строка и столбец являются обязательными, другие аргументы необязательны. Аргумент abs_num определяет, является ли возвращаемый адрес относительным, смешанным или абсолютным, со значением по умолчанию 1 для абсолютного. Аргумент a1 — это логическое значение, которое переключает ссылки на стили A1 и R1C1 со значением по умолчанию TRUE для ссылок на стили A1.Наконец, аргумент sheet_text предназначен для хранения имени листа, которое будет добавлено к адресу.
Параметры АБС
В таблице ниже показаны параметры, доступные для аргумента abs_num для возврата относительного, смешанного или абсолютного адреса.
| abs_num | Результат |
|---|---|
| 1 (или опущен) | Абсолютный ($A$1) |
| 2 | Абсолютная строка, относительный столбец (A$1) | 3 | Относительная строка, абсолютный столбец ($A1) |
| 4 | Относительная (A1) |