Загрузите образец книги
В этом руководстве показано, как использовать Функция АДРЕС Excel в Excel, чтобы вернуть адрес ячейки в виде текста.
Обзор функции АДРЕС
Функция ADDRESS Возвращает адрес ячейки в виде текста.
Чтобы использовать функцию ADDRESS Excel Worksheet, выберите ячейку и введите:
(Обратите внимание, как появляются входные данные формулы)
Функция АДРЕС Синтаксис и входные данные:
| 1 | = АДРЕС (номер_строки; номер_столбца; абс_номер; C1; лист_текст) |
row_num - Номер строки для ссылки. Пример: 5 для строки 5.
col_num - Номер столбца для ссылки. Пример: 5 для столбца E. Вы не можете ввести «E» для столбца E.
abs_num - [необязательно] Число, указывающее, должна ли ссылка иметь абсолютные или относительные ссылки на строку / столбец. 1 для абсолютного. 2 для абсолютной строки / относительного столбца. 3 для относительной строки / абсолютного столбца. 4 для родственника.
а1 - [по желанию]. Число, указывающее, следует ли использовать стандартный (A1) формат ссылки на ячейку или формат R1C1. 1 / TRUE для стандартного (по умолчанию). 0 / ЛОЖЬ для R1C1.
лист_текст - [необязательно] Имя используемого рабочего листа. По умолчанию текущий лист.
Что такое функция АДРЕС?
Функция АДРЕС - уникальная функция. В большинстве случаев в электронной таблице мы сообщаем компьютеру ссылку на ячейку, и он дает нам значение из этой ячейки. Используя АДРЕС, мы вместо этого построим имя ячейки. Этот адрес может быть относительным или абсолютным, в стиле A1 или R1C1 и может включать или не включать имя листа. По большей части у этой функции не так много практических применений, но она может быть интересной для изучения. Когда он используется, он обычно используется в сочетании с другими функциями, как мы увидим ниже.
Базовый пример
Допустим, мы хотим создать ссылку на ячейку в 4th столбец и 1ул строка, также известная как ячейка D1. Мы можем использовать макет, изображенный здесь:
Наша формула в A3 просто
| 1 | = АДРЕС (B1; B2) |
Обратите внимание, что, не указывая конкретного аргумента для относительного / абсолютного, мы получили все абсолютное. У нас также есть стиль по умолчанию для ссылок типа A1 без имени листа.
В сочетании с КОСВЕННЫМ
Как мы уже говорили, функция АДРЕС сама по себе не дала нам ничего полезного. Мы могли бы объединить его с функцией INDIRECT, чтобы получить некоторую функциональность. Рассмотрим этот макет, в котором у нас есть список элементов в столбце D. Если мы сохраним ту же формулу, что и раньше, мы сгенерируем ссылку на D1 следующим образом
| 12 | = АДРЕС (B1; B2)= $ D $ 1 |
Поместив адресную функцию внутри КОСВЕННОЙ функции, мы сможем использовать сгенерированную ссылку на ячейку и использовать ее на практике. INDIRECT берет ссылку на «$ D $ 1» и использует ее для извлечения значения из этой ячейки.
| 123 | = КОСВЕННО (АДРЕС (B1; B2)= КОСВЕННО ($ D $ 1)= "Яблоко" |
Примечание: Хотя приведенное выше является хорошим примером использования функции АДРЕС, это не лучшая формула для обычного использования. Для этого требовалось две функции, и из-за КОСВЕННОСТИ он будет непостоянным по своей природе. Лучшей альтернативой было бы использовать ИНДЕКС следующим образом: = ИНДЕКС (1: 1048576, B1, B2)
Адрес особой ценности
Иногда, когда у вас есть большой список элементов, вам нужно знать, где в списке находится элемент. Рассмотрим эту таблицу с оценками студентов. Мы пошли дальше и вычислили минимальное, среднее и максимальное значения этих оценок в ячейках E2: G2.
Мы могли бы отфильтровать нашу таблицу для каждого из этих элементов, чтобы найти, где он находится (опять же, представьте, что это гораздо больший список), или мы могли бы даже применить условное форматирование, чтобы он визуально отображался для пользователя. Но если список состоит из тысяч строк, нам не нужно прокручивать так далеко, чтобы увидеть то, что мы хотим. Вместо этого мы будем использовать значения в E2: G2 для определения адреса ячеек, содержащих наши значения. Для этого воспользуемся функцией ПОИСКПОЗ с АДРЕС. Помните, что ПОИСКПОЗ вернет относительное положение значения в пределах диапазона.
Тогда наша формула в E3:
| 1 | = АДРЕС (ПОИСКПОЗ (E2; $ B: $ B; 0); 2) |
Мы можем скопировать ту же формулу в G3, и изменится только ссылка E2, поскольку это единственная относительная ссылка. Оглядываясь назад на E3, функция ПОИСКПОЗ смогла найти значение 98 в 5th строка столбца B. Наша функция ADDRESS затем использовала это для построения полного адреса «$ B $ 5».
Переведите буквы столбца из цифр
До этого момента во всех наших примерах функция ADDRESS возвращала абсолютную ссылку. Следующий пример вернет относительную ссылку. В этой таблице мы хотим ввести число в столбец A и вернуть имя соответствующей буквы столбца.
Для достижения нашей цели функция ADDRESS должна возвращать ссылку на строку 1 в относительном формате, а затем мы удалим «1» из текстовой строки, чтобы у нас остались только буквы. Рассмотрим строку 3 в нашей таблице, где мы вводим 13. Наша формула в B3:
| 1 | = ПОДСТАВИТЬ (АДРЕС (1; A3; 4); "1"; "") |
Обратите внимание, что мы дали 3rd аргумент в функции ADDRESS, который управляет относительной и абсолютной привязкой. Функция АДРЕС выведет «M1», а затем функция ЗАМЕНА удаляет «1», так что у нас остается только «M».
Найдите адрес именованных диапазонов
В Excel вы можете присвоить имя диапазону или диапазонам ячеек, что позволит вам просто ссылаться на именованный диапазон вместо ссылки на ячейку.
Большинство именованных диапазонов статичны, что означает, что они всегда относятся к одному и тому же диапазону. Однако вы также можете создавать динамические именованные диапазоны, размер которых изменяется в зависимости от формулы (формул).
При использовании динамического именованного диапазона вам может потребоваться знать точный адрес, на который указывает ваш именованный диапазон. Мы можем сделать это с помощью функции ADDRESS.
В этом примере мы рассмотрим, как определить адрес для нашего именованного диапазона, который называется «Оценки».
Давайте вернем нашу таблицу из прошлого:
Чтобы получить адрес диапазона, вам нужно знать верхнюю левую ячейку и нижнюю левую ячейку. Первую часть достаточно легко выполнить с помощью функций СТРОКА и СТОЛБЕЦ. Наша формула в E1 может быть
| 1 | = АДРЕС (СТРОКА (оценки); КОЛОНКА (оценки)) |
Функция ROW вернет строку первой ячейки в нашем диапазоне (которая будет равна 1), а COLUMN сделает то же самое для столбца (также 1).
Чтобы получить нижнюю правую ячейку, воспользуемся функцией СТРОКИ и СТОЛБЦЫ. Поскольку мы можем определить начальную точку нашего диапазона, если мы посчитаем, насколько высок диапазон, и вычтем нашу начальную точку, мы получим правильную конечную точку. Формула для этого выглядит так
| 1 | = АДРЕС (СТРОКИ (оценки) -ROW (оценки) +1, COLUMNS (оценки) -COLUMN (оценки) +1) |
Наконец, чтобы собрать все это в одну строку, мы можем просто объединить значения вместе с двоеточием посередине. Формулу в E3 можно
| 1 | = E1 & ":" & E2 |
Примечание: Хотя мы смогли определить адрес диапазона, наша функция ADDRESS определила, следует ли перечислять ссылки как относительные или абсолютные. Ваши динамические диапазоны будут иметь относительные ссылки, которые этот метод не улавливает.
2nd Примечание: Этот метод работает только с непрерывным диапазоном имен. Если у вас есть именованный диапазон, который был определен как что-то вроде этой формулы
| 1 | = A1: B2, A5: B6 |
то описанный выше метод приведет к ошибкам.
Дополнительные замечания
Используйте функцию АДРЕС, чтобы сгенерировать адрес из заданного номера строки и столбца. Важный: Вы должны войти в столбец количество. Ввод буквы столбца вызовет ошибку. При необходимости вы можете использовать функцию столбца, чтобы вычислить номер столбца для ссылки на ячейку.
Abs_num позволяет переключать абсолютные и относительные ссылки на ячейки.
1,2,3,4 a1, $ a $ 2… относительный / абсолютный и т. Д.
Затем укажите, использовать ли a1 или R1C1. Режим a1 - это стандартный режим, в котором ячейки обозначаются буквой столбца и номером строки (например, a4). В режиме R1C1 ячейки указываются по их номерам строк и столбцов (например, R4C1). a1 - режим по умолчанию. Используйте это, если у вас нет веской причины не делать этого.
В последнем аргументе вы можете ввести имя листа, если ссылка на ячейку будет на другом листе. Введите имя листа в скобках (например, «sheet3»).
Вернуться к списку всех функций в Excel
АДРЕС функция в Google Таблицах
Функция АДРЕС работает в Google Таблицах точно так же, как и в Excel.
