Адресация ячеек в Excel
В этой статье более подробно разберём виды адресации ячеек в Excel. В обзорном видео я уже об этом кратко рассказывал, ну а сейчас пришла пора разъяснить эту тему более подробно.
Относительный адрес может быть, например, таким:
Однако на другом листе тоже может быть ячейка B3. Чтобы однозначно определить ячейку в пределах книги Excel, можно перед её адресом написать имя листа.
Такой адрес в книге может выглядеть так:
То есть здесь уже идёт речь не о какой-то абстрактной ячейке В3, а о ячейке В3, расположенной на листе с именем “Лист2”.
Это только самые общие сведения об адресации ячеек в Excel, но для начала этого достаточно. Однако надо ещё рассказать о видах адресации.
Формат адреса ячейки в Excel
С одним форматом адреса вы уже знакомы. Это формат вида “буква-цифра”:
Однако в Excel есть и другой формат адресации ячейки:
Лично мне больше нравится формат “буква-цифра”. И по умолчанию обычно такой формат и используется (видимо, он больше нравится не только мне, но и разработчикам Excel).
Однако иногда (во всяком случае в Excel 2003 это случается) формат адреса ячейки почему-то сам собой меняется на RxCy. И тогда приходится менять его в настройках программы вручную.
Начинающих это может ввести в состояние паники, потому что с первого раза найти эти настройки практически ни у кого не получается.
Поэтому подсказываю. В Excel 2007 изменить стиль адреса ячеек можно так:
Если вы поставите галочку напротив надписи “Стиль ссылок R1C1”, то адреса ячеек будут иметь формат RxCy. Если снимите галочку, то будет использоваться формат “буква-цифра”.
30 функций Excel за 30 дней: АДРЕС (ADDRESS)
Вчера в марафоне 30 функций Excel за 30 дней мы находили элементы массива при помощи функции MATCH (ПОИСКПОЗ) и обнаружили, что она отлично работает в команде с другими функциями, такими как VLOOKUP (ВПР) и INDEX (ИНДЕКС).
20-й день нашего марафона мы посвятим изучению функции ADDRESS (АДРЕС). Она возвращает адрес ячейки в текстовом формате, используя номер строки и столбца. Нужен ли нам этот адрес? Можно ли сделать то же самое с помощью других функций?
Давайте обратимся к сведениям по функции ADDRESS (АДРЕС) и изучим примеры работы с ней. Если у Вас есть дополнительная информация или примеры, пожалуйста, делитесь ими в комментариях.
Функция 20: ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) возвращает ссылку на ячейку в виде текста, основываясь на номере строки и столбца. Она может возвращать абсолютный или относительный адрес в стиле ссылок A1 или R1C1. К тому же в результат может быть включено имя листа.
Как можно использовать функцию ADDRESS (АДРЕС)?
Функция ADDRESS (АДРЕС) может возвратить адрес ячейки или работать в сочетании с другими функциями, чтобы:
Синтаксис ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) имеет вот такой синтаксис:
Ловушки ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) возвращает лишь адрес ячейки в виде текстовой строки. Если Вам нужно значение ячейки, используйте её в качестве аргумента функции INDIRECT (ДВССЫЛ) или примените одну из альтернативных формул, показанных в примере 2.
Пример 1: Получаем адрес ячейки по номеру строки и столбца
При помощи функции ADDRESS (АДРЕС) Вы можете получить адрес ячейки в виде текста, используя номер строки и столбца. Если Вы введёте только эти два аргумента, результатом будет абсолютный адрес, записанный в стиле ссылок A1.
Абсолютная или относительная
Если не указывать значение аргумента abs_num (тип_ссылки) в формуле, то результатом будет абсолютная ссылка.
Чтобы увидеть адрес в виде относительной ссылки, можно подставить в качестве аргумента abs_num (тип_ссылки) значение 4.
A1 или R1C1
Чтобы задать стиль ссылок R1C1, вместо принятого по умолчанию стиля A1, Вы должны указать значение FALSE (ЛОЖЬ) для аргумента а1.
Название листа
Последний аргумент – это имя листа. Если Вам необходимо это имя в полученном результате, укажите его в качестве аргумента sheet_text (имя_листа).
Пример 2: Находим значение ячейки, используя номер строки и столбца
Функция ADDRESS (АДРЕС) возвращает адрес ячейки в виде текста, а не как действующую ссылку. Если Вам нужно получить значение ячейки, можно использовать результат, возвращаемый функцией ADDRESS (АДРЕС), как аргумент для INDIRECT (ДВССЫЛ). Мы изучим функцию INDIRECT (ДВССЫЛ) позже в рамках марафона 30 функций Excel за 30 дней.
Функция INDIRECT (ДВССЫЛ) может работать и без функции ADDRESS (АДРЕС). Вот как можно, используя оператор конкатенации “&“, слепить нужный адрес в стиле R1C1 и в результате получить значение ячейки:
Функция INDEX (ИНДЕКС) также может вернуть значение ячейки, если указан номер строки и столбца:
1:5000 – это первые 5000 строк листа Excel.
Пример 3: Возвращаем адрес ячейки с максимальным значением
В этом примере мы найдём ячейку с максимальным значением и используем функцию ADDRESS (АДРЕС), чтобы получить её адрес.
Функция MAX (МАКС) находит максимальное число в столбце C.
Далее в игру вступает функция ADDRESS (АДРЕС) в сочетании с MATCH (ПОИСКПОЗ), которая находит номер строки, и COLUMN (СТОЛБЕЦ), которая определяет номер столбца.
Примеры функции АДРЕС для получения адреса ячейки листа Excel
Функция АДРЕС в Excel: описание особенностей синтаксиса
Функция АДРЕС имеет следующую синтаксическую запись:
Первые два аргумента данной функции являются обязательными для заполнения.
Примеры использования функции АДРЕС в Excel
Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных условий. Для работы с актуальными данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.
На листе «Курсы» создана таблица с актуальными курсами валют:
На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):
В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).
Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).
Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:
Как получить адрес ссылки на ячейку Excel?
Пример 2. В таблице содержатся данные о цене товаров, отсортированные в порядке возрастания стоимости. Необходимо получить ссылки на ячейки с минимальной и максимальной стоимостью товаров соответственно.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:
Функция АДРЕС принимает следующие параметры:
Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:
Адрес по номерам строк и столбцов листа Excel в стиле R1C1
Пример 3. В таблице содержится ячейка, данные из которой используются в другом программном продукте. Для обеспечения совместимости необходимо предоставить ссылку на нее в виде R1C1.
Исходная таблица имеет следующий вид:
Исходная таблица.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel78-9.png» >
Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).
В результате получим ссылку:
Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:
Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.
АДРЕС (функция АДРЕС)
В этой статье описаны синтаксис формулы и использование функции АДРЕС в Microsoft Excel. Чтобы узнать больше о работе с почтовыми адресами и создании почтовых наклеек, см. по ссылкам в разделе См. также.
Описание
Синтаксис
Аргументы функции АДРЕС указаны ниже.
Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.
Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.
Тип_ссылки Необязательный аргумент. Задает тип возвращаемой ссылки.
Возвращаемый тип ссылки
Абсолютная строка; относительный столбец
Относительная строка; абсолютный столбец
A1 Необязательный аргумент. Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами. При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами. Если аргумент А1 имеет значение ИСТИНА или опущен, то функция АДРЕС возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ, функция АДРЕС возвращает ссылку типа R1C1.
Примечание: Чтобы изменить тип ссылок, используемый Microsoft Excel, откройте вкладку Файл, а затем нажмите кнопку Параметры и выберите пункт Формулы. В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1.
Имя_листа Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула = АДРЕС( 1;1;;;»Лист2″) возвращает значение Лист2!$A$1. Если аргумент имя_листа отсутствует, имя листа не используется, и адрес, возвращаемый функцией, ссылается на ячейку текущего листа.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Получение адреса ячейки в excel
Описание
синтаксис и аргументы
Синтаксис формулы
аргументы
Примечания:
Возвращаемое значение:
ADDRESS функция возвращает ссылку на ячейку в виде текста.
Использование и примеры
Здесь приведены несколько примеров, объясняющих, как использовать функцию ИНДЕКС.
Пример 1. Базовое использование: получение адреса ячейки из заданного столбца и строки
3) Если четвертый аргумент введен в ADDRESS Функция,
=ADDRESS(A4,B4,C4,D4))
D4 контролирует стиль ссылки, A1 или R1C1, 0 или False будет отображать результат в стиле A1, 1 или True отобразит результат в стиле R1C1, здесь он вернет
R1C1 
Была основана ADDRESS функция возвращает адрес ячейки в виде текста, если вы хотите показать значение ячейки в адресе ячейки, вы можете объединить ADDRESS функция и INDIRECT функция для достижения этой цели.
Вот формула в B4, которая будет получать значение ячейки в B1.
=INDIRECT(ADDRESS(B2,B3)) 
В этом случае я расскажу, как использовать ADDRESS функция для получения адреса ячейки с максимальным значением.
Затем используйте формулу
=ADDRESS(MATCH(E1,B1:B6,0),COLUMN(B1))
Примечание: Эта формула может найти максимальное значение только в одном столбце.
В этом примере я расскажу, как использовать ADDRESS функция для возврата буквы столбца на основе заданного номера столбца.
Например, вы хотите получить букву столбца для 29-го столбца, используйте следующую формулу:
=SUBSTITUTE(ADDRESS(1,A3,4),»1″,»»)
Что означают аргументы:
SUBSTITUTE функция: замените 1 пустой строкой, чтобы окончательный результат
AC 
Если вы хотите получить букву столбца текущей ячейки, вы можете использовать эту формулу
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),»1″,»») 
