Как использовать функцию АДРЕС в Excel

Функция Excel АДРЕС принимает номера строк и столбцов в качестве аргументов и возвращает стандартную ссылку на ячейку (адрес ячейки). Например, если вы перейдете к строке номер 4 и столбцу номер 3, функция вернет C4. Функция АДРЕС Excel может возвращать абсолютную или относительную ссылку в любом из двух форматов ссылок Excel.

Узнайте, как использовать функцию АДРЕС в Excel.

• Типы ссылок
• 2 типа ссылок на ячейки
• Как использовать функцию АДРЕС в Excel

Типы ссылок

• Относительные ссылки выражаются в виде номеров столбцов и строк (например, M290 ). Когда вы копируете формулу, содержащую относительную ссылку на ячейку, ссылка — номер строки и буква столбца — корректируется с учетом места, в которое вы скопировали формулу.
• Абсолютные ссылки имеют знак $ перед буквой столбца и номером строки (например, $M$290 ). При копировании формулы, содержащей абсолютную ссылку на ячейку, ссылка не изменяется.
• Смешанные ссылки имеют символ $ перед буквой столбца или номером строки (например, $M290 или M$290 ). При копировании формулы, содержащей смешанные ссылки на ячейки, часть ссылки с символом $ не меняется, а другая часть изменяется.

На следующем изображении показана электронная таблица, в которой ввод формулы с относительной ссылкой на ячейку вызывает проблемы. Итоговая сумма является результатом прибавления налога к первоначальной сумме. Ставка налога составляет 7,5% (0,075). Это соотношение находится в ячейке C1 и на него ссылаются формулы. Первая формула вводится в ячейку C7 и имеет следующее содержание: =B7*(1 + C1) .

Формула в ячейке C7 верна. Он ссылается на ячейку C1 для расчета суммы. Но если вы используете маркер заполнения (черную точку в правом нижнем углу выбранной ячейки) для копирования формулы из ячейки C7 в ячейки C8 и C9, возникнет проблема. Ссылка на ячейку C1 изменилась на ячейки C2 и C3. Поскольку эти ячейки пусты, результаты в ячейках C8 и C9 неверны (результаты такие же, как сумма слева, а налоги не добавлены).

Чтобы лучше понять, давайте посмотрим на столбец D, показывающий формулы, расположенные в столбце C. Когда формула в ячейке C7 была перетянута вниз, ссылка C1 изменилась на C2 в ячейке C8 и C3 в ячейке C9. Обычно пользователи хотят, чтобы Excel автоматически менял ссылки на ячейки при копировании формул. Но иногда (как в этой ситуации) этого делать не хочется. Вам нужна абсолютная ссылка на ячейку.

Формула в ячейке C17 почти идентична формуле в ячейке C7, за исключением того, что ссылка на ячейку C1 сделана абсолютной путем помещения символа $ перед номером строки. Формула в ячейке C17 выглядит следующим образом: =B17*(1 + C$1) . Когда эту формулу перетаскивают вниз на C18 и C19, ссылка не корректируется, но все равно указывает на ячейку C1. Обратите внимание, что в этом примере только строковая часть ссылки является абсолютной. Вы могли бы создать полностью абсолютную ссылку, введя следующий синтаксис: =B17*(1 + $C$1) . Результат будет тот же, но в данном примере это не требуется.

2 типа ссылок на ячейки

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

Excel поддерживает два типа ссылок на ячейки: старый стиль A1 и стиль R1C1. Стиль R1C1 использует систему нумерации как для строк, так и для столбцов, например: R4C10. В этом примере R4C10 означает строку 4, столбец 10.

Чтобы изменить стиль ссылки на ячейку, выберите «Файл» > «Параметры» и проверьте стиль ссылки R1C1 в области «Работа с формулами» на вкладке «Формулы». Использование формата R1C1 также приводит к тому, что столбцы электронной таблицы отображаются в виде цифр, а не букв. Это полезно при работе с большим количеством столбцов. Например, позиционный столбец CV — это столбец 100. Запомнить число 100 легче, чем запомнить слово CV.

Чтобы вернуться к функции АДРЕС Excel, требуется до 5 аргументов:

• Номер строки ссылки
• Номер графы ссылки
• Число, которое сообщает функции, как вернуть ссылку. По умолчанию это число равно 1 , но также может быть:
  • 1 : И строки, и столбцы являются абсолютными.
  • 2 : Абсолютные строки и относительные столбцы.
  • 3 : Относительные строки и абсолютные столбцы.
  • 4 : И строки, и столбцы относительны.
• Значение 0 или 1 сообщает функции, какой тип ссылки использовать:
  • 0 использует стиль R1C1.
  • 1 (значение по умолчанию и опущено, если используется) использует стиль A1.
• Внешняя электронная таблица или книга и ссылка на электронную таблицу.

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

Синтаксис	Результат	Объяснять
=ADDRESS(5,2)	$B$5	В качестве аргументов предоставляются только столбцы и строки. Функция возвращает полный абсолютный адрес.
=ADDRESS(5,2,1)	$B$5	Если в качестве третьего аргумента используется 1, возвращается полный абсолютный адрес. Результат тот же, если третий аргумент опущен.
=ADDRESS(5,2,2)	B$5	Если в качестве третьего аргумента используется значение 2, возвращается составная ссылка с относительным значением столбца и абсолютным значением строки.
=ADDRESS(5,2,3)	$B5	Если в качестве третьего аргумента используется значение 3, возвращается составная ссылка с абсолютными значениями столбца и относительными значениями строк.
=ADDRESS(5,2,4)	B5	Если в качестве третьего аргумента используется число 4, возвращается полная относительная ссылка.
=ADDRESS(5,2,1,0)	Р5С2	Если четвертый аргумент имеет значение false, возвращается ссылка типа R1C1.
=ADDRESS(5,2,3,0)	R[5]C2	В этом примере показана функция, возвращающая смешанную ссылку в стиле R1C1.
=ADDRESS(5,2,1,,"Sheet4")	Sheet4!$B$5	Пятый аргумент возвращает ссылку на внешний лист или книгу. Этот синтаксис возвращает ссылку на стиль A1 на ячейку B5 на листе 4.
=ADDRESS(5,2,1,0,"Sheet4")	Sheet4!R5C2	Этот синтаксис возвращает ссылку на стиль R1C1 на ячейку B5 на листе 4.

Как использовать функцию АДРЕС в Excel

Используйте функцию АДРЕС в Excel следующим образом:

1. Щелкните ячейку, в которой вы хотите отобразить результаты.

2. Введите =АДРЕС(, чтобы запустить функцию.

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

4. Если вы хотите, чтобы результат возвращался в виде смешанной или полной ссылки, введите запятую (,) и соответствующее число: 2, 3 или 4 .

5. Если вы хотите, чтобы результат возвращался в стиле R1C1, введите запятую (,) и число 0 .

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

Если вы хотите, чтобы результаты ссылались на внешнюю книгу, введите запятую (,), имя книги и лист вместе. Имя книги заключено в скобки, а вся ссылка заключена в кавычки, например: «[Книга1] Лист2».

7. Enter ) и нажмите Enter.

Вместо ввода номеров строк и столбцов непосредственно в АДРЕС вы можете ввести ссылки на ячейки. Однако значения, которые вы найдете в этих ячейках, должны оцениваться как числа, которые можно использовать в качестве номеров строк и столбцов.