Содержание:
Вчера на марафоне 30 функций Excel за 30 дней мы нашли элементы массива с помощью функции МАТЧ (ПОИСК) и обнаружил, что он отлично работает в сочетании с другими функциями, такими как ВПР (ВПР) и ИНДЕКС (ИНДЕКС).
20-й день нашего марафона мы посвятим изучению функции АДРЕС (АДРЕС). Он возвращает адрес ячейки в текстовом формате, используя номер строки и столбца. Нужен ли нам этот адрес? Можно ли то же самое сделать с другими функциями?
Давайте посмотрим на детали функции АДРЕС (АДРЕС) и изучите примеры работы с ним. Если у вас есть дополнительная информация или примеры, поделитесь ими в комментариях.
Функция 20: АДРЕС
Функция АДРЕС (АДРЕС) возвращает ссылку на ячейку в виде текста на основе номера строки и столбца. Он может возвращать абсолютный или относительный адрес в виде ссылки. A1 or R1C1. Кроме того, в результат можно включить имя листа.
Как можно использовать функцию АДРЕС?
Функция АДРЕС (ADDRESS) может возвращать адрес ячейки или работать совместно с другими функциями для:
- Получите адрес ячейки по номеру строки и столбца.
- Найдите значение ячейки, зная номер строки и столбца.
- Верните адрес ячейки с наибольшим значением.
Синтаксис АДРЕС (АДРЕС)
Функция АДРЕС (АДРЕС) имеет следующий синтаксис:
ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])
- абс_номер (link_type) – если равно 1 или не указан вообще, функция вернет абсолютный адрес ($A$1). Чтобы получить относительный адрес (A1), используйте значение 4. Другие варианты: 2= 1 доллар США, 3=$А1.
- a1 – если TRUE (ИСТИНА) или не указано вообще, функция возвращает ссылку в стиле A1, если ЛОЖЬ (FALSE), то в стиле R1C1.
- Простыня_текст (sheet_name) – имя листа можно указать, если вы хотите видеть его в результате, возвращаемом функцией.
Ловушки АДРЕС
Функция АДРЕС (АДРЕС) возвращает только адрес ячейки в виде текстовой строки. Если вам нужно значение ячейки, используйте его как аргумент функции. КОСВЕННЫЕ (НЕПРЯМОЙ) или используйте одну из альтернативных формул, показанных в примере 2.
Пример 1. Получите адрес ячейки по номеру строки и столбца.
Использование функций АДРЕС (АДРЕС) Вы можете получить адрес ячейки в виде текста, используя номер строки и столбца. Если вы введете только эти два аргумента, результатом будет абсолютный адрес, записанный в стиле ссылки. A1.
=ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)
Абсолютный или относительный
Если вы не укажете значение аргумента абс_номер (reference_type) в формуле, результатом является абсолютная ссылка.
Чтобы увидеть адрес как относительную ссылку, вы можете подставить его в качестве аргумента абс_номер (тип_ссылки) значение 4.
=ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)
А1 или R1C1
Оформление ссылок R1C1, вместо стиля по умолчанию A1, Вы должны указать FALSE для аргумента a1.
=ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)
Имя листа
Последний аргумент — это имя листа. Если вам нужно это имя в результате, укажите его в качестве аргумента лист_текст (имя_листа).
=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")
Пример 2. Найдите значение ячейки, используя номер строки и столбца.
Функция АДРЕС (АДРЕС) возвращает адрес ячейки в виде текста, а не в виде действительной ссылки. Если вам нужно получить значение ячейки, вы можете использовать результат, возвращаемый функцией АДРЕС (АДРЕС), в качестве аргумента в пользу КОСВЕННЫЕ (КОСВЕННО). Мы будем изучать функцию КОСВЕННЫЕ (КОСВЕННО) позже в марафоне 30 функций Excel за 30 дней.
=INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))
Функция КОСВЕННЫЕ (ДВССЫЛ) может работать без функции АДРЕС (АДРЕС). Вот как это можно сделать, используя оператор конкатенации «&«, слепите нужный адрес в стиле R1C1 и в результате получим значение ячейки:
=INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)
Функция ИНДЕКС (ИНДЕКС) также может возвращать значение ячейки, если указан номер строки и столбца:
=INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)
1:5000 — это первые 5000 строк листа Excel.
Пример 3: Возврат адреса ячейки с максимальным значением
В этом примере мы найдем ячейку с максимальным значением и воспользуемся функцией АДРЕС (АДРЕС), чтобы узнать ее адрес.
Функция MAX (MAX) находит максимальное число в столбце C.
=MAX(C3:C8)
=МАКС(C3:C8)
Далее идет функция АДРЕС (АДРЕС) в сочетании с МАТЧ (MATCH), который находит номер строки, и КОЛОНКА (COLUMN), который указывает номер столбца.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))