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