Что такое адрес ячейки в 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 для 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. Найдите ссылки на информацию о работе с почтовыми адресами или создании почтовых ярлыков в разделе «См. также».

Описание

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

Синтаксис

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

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

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

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

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

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

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

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

A1 Необязательно. Логическое значение, указывающее стиль ссылок A1 или R1C1. В стиле A1 столбцы обозначаются в алфавитном порядке, а строки — в числовом. В стиле ссылок R1C1 и столбцы, и строки имеют числовые метки. Если аргумент A1 равен TRUE или опущен, функция ADDRESS возвращает ссылку в стиле A1; если FALSE, функция ADDRESS возвращает ссылку в стиле R1C1.

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

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

Пример

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

Функция АДРЕС 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 для возврата относительного, смешанного или абсолютного адреса.

< tr>

Примеры

Используйте ADDRESS, чтобы создать адрес из заданного номера строки и столбца. Например:

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

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

Данные из одной или нескольких смежных ячеек на листе.

Данные, содержащиеся в разных областях рабочего листа.

Данные на других листах в той же книге.

Эта формула:

И возвращает:

Значение в ячейке C2.

Ячейки от A1 до F4

Значения во всех ячейках, но вы должны нажать Ctrl+Shift+Enter после ввода формулы.

Примечание. Эта функция не работает в Excel для Интернета.

Ячейки с именами "Активы и обязательства"

Значение в ячейке с именем "Обязательство" вычитается из значения в ячейке с именем "Актив".

Диапазоны ячеек с именами Week1 и Week2

Сумма значений диапазонов ячеек с именами Week1 и Week 2 в виде формулы массива.

Ячейка B2 на Листе2

Значение в ячейке B2 на Листе2.

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства).

Выполните одно из следующих действий:

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

Вы можете перетащить границу выделения ячейки, чтобы переместить выделение, или перетащить угол границы, чтобы расширить выделение.

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

Нажмите F3, выберите имя в поле "Вставить имя" и нажмите "ОК".

Примечание. Если на границе с цветовой кодировкой нет квадратного угла, ссылка относится к именованному диапазону.

Выполните одно из следующих действий:

Если вы создаете ссылку в одной ячейке, нажмите Enter.

Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

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

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Вы можете ссылаться на ячейки, которые находятся на других листах в той же книге, добавляя имя рабочего листа, а затем восклицательный знак (!) в начале ссылки на ячейку.В следующем примере функция листа с именем СРЗНАЧ вычисляет среднее значение для диапазона B1:B10 на листе с именем Маркетинг в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства) и нужную формулу.

Перейдите на вкладку листа, на который нужно сослаться.

Выберите ячейку или диапазон ячеек, на которые нужно сослаться.

Примечание. Если имя другого рабочего листа содержит неалфавитные символы, необходимо заключить имя (или путь) в одинарные кавычки (').

Кроме того, вы можете скопировать и вставить ссылку на ячейку, а затем использовать команду «Связать ячейки», чтобы создать ссылку на ячейку. Вы можете использовать эту команду для:

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

Упростите создание ссылок на ячейки между листами и книгами. Команда «Связать ячейки» автоматически вставляет правильный синтаксис.

Нажмите ячейку, содержащую данные, на которые вы хотите установить ссылку.

Нажмите Ctrl+C или перейдите на вкладку "Главная" и в группе "Буфер обмена" нажмите "Копировать" .

Нажмите Ctrl+V или перейдите на вкладку "Главная", в группе "Буфер обмена" нажмите "Вставить" .

По умолчанию кнопка "Параметры вставки" появляется при вставке скопированных данных.

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

Выполните одно из следующих действий:

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

Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

В строке формул выберите ссылку в формуле, а затем введите новую ссылку.

Нажмите F3, выберите имя в поле "Вставить имя" и нажмите "ОК".

Нажмите клавишу ВВОД или, чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.

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

Выполните одно из следующих действий:

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

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

На вкладке "Формулы" в группе "Определенные имена" щелкните стрелку рядом с пунктом "Определить имя" и выберите "Применить имена".

В поле "Применить имена" выберите одно или несколько имен, а затем нажмите "ОК".

Выберите ячейку, содержащую формулу.

В строке формул выберите ссылку, которую нужно изменить.

Нажмите F4 для переключения между типами ссылок.

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

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства).

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

Выполните одно из следующих действий:

Если вы создаете ссылку в одной ячейке, нажмите Enter.

Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

Вы можете ссылаться на ячейки, которые находятся на других листах в той же книге, добавляя имя рабочего листа, а затем восклицательный знак (!) в начале ссылки на ячейку. В следующем примере функция листа с именем СРЗНАЧ вычисляет среднее значение для диапазона B1:B10 на листе с именем Маркетинг в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства) и нужную формулу.

Перейдите на вкладку листа, на который нужно сослаться.

Выберите ячейку или диапазон ячеек, на которые нужно сослаться.

Выполните одно из следующих действий:

Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

В строке формул выберите ссылку в формуле, а затем введите новую ссылку.

Нажмите клавишу ВВОД или, чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.

Выберите ячейку, содержащую формулу.

В строке формул выберите ссылку, которую нужно изменить.

Нажмите F4 для переключения между типами ссылок.

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

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

abs_num	Результат
1 (или опущен)	Абсолютный ($A$1)
2	Абсолютная строка, относительный столбец (A$1)
3	Относительная строка, абсолютный столбец ($A1)
4	Относительная (A1)