Excel адрес функция: Примеры функции АДРЕС для получения адреса ячейки листа Excel

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

Описание

Синтаксис и аргументы

Использование и примеры

• Пример 1. Базовое использование: получение адреса ячейки из заданного столбца и строки
• Пример 2-ячеек из номера строки и столбца
• Пример 3 — получение адреса максимального значения
• Пример 4 — Вернуть букву столбца на основе номера столбца

Описание

Наблюдения и советы этой статьи мы подготовили на основании опыта команды ADDRESS Функция возвращает ссылку на адрес ячейки в виде текста на основе заданного номера столбца и номера строки. Например, формула =ADDRESS(1,1) возвращает $ A $ 1. В ADDRESS Функция может возвращать относительный или абсолютный адрес, а также возвращать в стиле A1 или R1C1, имя листа также может быть включено в результат.

синтаксис и аргументы

Синтаксис формулы

аргументы

Примечания:

Аргумент A1	Стиль	Описание
1 или TRUE или опущено	A1	Столбцы обозначены в алфавитном порядке, а строки — в цифрах.
0 или False	R1C1	И столбцы, и строки помечаются численно.

Возвращаемое значение:

ADDRESS функция возвращает ссылку на ячейку в виде текста.

Использование и примеры

Здесь приведены несколько примеров, объясняющих, как использовать функцию ИНДЕКС.

Пример 1. Базовое использование: получение адреса ячейки из заданного столбца и строки

1) Если вы просто введете аргументы строки и столбца в ADDRESS Функция,
=ADDRESS(A2,B2)
A2 и B2 — числовые значения строки и столбца, и он возвращает
1 австралийских доллара

2) Если вы вводите аргументы строки, столбца и абс в ADDRESS Функция,
=ADDRESS(A3,B3,C3)
C3 — аргументы abs, 2 указывает на отображение ссылки в виде относительной строки и абсолютного столбца, и он возвращает
A $ 1

3) Если четвертый аргумент введен в ADDRESS Функция,
=ADDRESS(A4,B4,C4,D4))
D4 контролирует стиль ссылки, A1 или R1C1, 0 или False будет отображать результат в стиле A1, 1 или True отобразит результат в стиле R1C1, здесь он вернет
R1C1

4) Если все аргументы введены в ADDRESS Функция,
=ADDRESS(A6,B6,C6,D6,E6)
E6 — пятый аргумент, указывающий на ссылку на лист, он возвращает
Базовое использование! $ A1

Пример 2 — Значение ячейки из номера строки и столбца

Наблюдения и советы этой статьи мы подготовили на основании опыта команды ADDRESS функция возвращает адрес ячейки в виде текста, если вы хотите показать значение ячейки в адресе ячейки, вы можете объединить ADDRESS функция и INDIRECT функция для достижения этой цели.

Вот формула в B4, которая будет получать значение ячейки в B1.

=INDIRECT(ADDRESS(B2,B3))

Пример 3 — Получить адрес максимального значения

В этом случае я расскажу, как использовать ADDRESS функция для получения адреса ячейки с максимальным значением.

Во-первых, вам нужно получить максимальное значение по этой формуле =MAX(B2:B6).

Затем используйте формулу

=ADDRESS(MATCH(E1,B1:B6,0),COLUMN(B1))

MATCH(E1,B1:B6,0) найдет номер строки, E1 — максимальное значение, B1: B6 — столбец, из которого вы найдете максимальное значение;

COLUMN(B1) найдет номер столбца, B1 — это столбец, из которого вы найдете значение.

Примечание: Эта формула может найти максимальное значение только в одном столбце.

Пример 4 — Возврат буквы столбца на основе номера столбца

В этом примере я расскажу, как использовать ADDRESS функция для возврата буквы столбца на основе заданного номера столбца.

Например, вы хотите получить букву столбца для 29-го столбца, используйте следующую формулу:

=SUBSTITUTE(ADDRESS(1,A3,4),»1″,»»)

Что означают аргументы:

ADDRESS функция: 1 — строка 1, A3 — номер столбца, относительную букву столбца которого вы хотите получить, 4 — аргумент abs, возвращающий ссылку в относительной, в этой части ADDRESS функция получает результат AC1;

SUBSTITUTE функция: замените 1 пустой строкой, чтобы окончательный результат
AC

Если вы хотите получить букву столбца текущей ячейки, вы можете использовать эту формулу

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),»1″,»»)

Файл примера

Лучшие инструменты для работы в офисе

Разбор функции ДВССЫЛ (INDIRECT) на примерах

126159
14.04.2017
Скачать пример

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной.  Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.

«Ну ОК», — скажете вы. «И что тут полезного?». 

Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.

Пример 1. Транспонирование

Классика жанра: нужно превратить вертикальный диа

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN).

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C2, C2=R1C3, D2=R1C4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE), то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET). Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM):

Пример 3.

Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

Пример 4. Несбиваемые ссылки

Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:

Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

Допустим, что форма, размеры, положение и последовательность товаров и месяцев во всех таблицах одинаковые — различаются только числа.

Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

• Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
• С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический — без проблем.
• ДВССЫЛ является волатильной (volatile) или «летучей» функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.

Ссылки по теме

• Как создать динамический диапазон с автоподстройкой размеров
• Суммирование по диапазону-окну на листе функцией СМЕЩ (OFFSET)

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

В этом учебнике Excel объясняется, как использовать функцию АДРЕС Excel с синтаксисом и примерами.

Описание

Функция АДРЕС Microsoft Excel возвращает текстовое представление адреса ячейки.

Функция АДРЕС — это встроенная функция Excel, относящаяся к категории Функция поиска/справки . Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию АДРЕС можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции АДРЕС в Microsoft Excel:

 АДРЕС( строка, столбец, [тип_ссылки], [стиль_ссылки], [имя_листа] ) 

Параметры или аргументы

строка

Номер строки для использования в адресе ячейки.

столбец

Номер столбца для использования в адресе ячейки.

ref_type

Дополнительно. Это тип ссылки для использования. Если этот параметр опущен, предполагается, что ref_type имеет значение 1. Это может быть любое из следующих значений:

Значение	Пояснение
1	Абсолютная привязка. Например: $A$1
2	Относительный столбец; абсолютная строка Например: A$1
3	Абсолютный столбец; относительная строка Например: $A1
4	Относительная ссылка. Например: A1

ref_style

Дополнительно. Это стандартный стиль для использования: либо A1, либо R1C1. Если этот параметр опущен, предполагается, что для ref_style установлено значение TRUE. Это может быть любое из следующих значений:

Значение	Пояснение
ИСТИНА	Ссылка на стиль A1
ЛОЖЬ	Стиль R1C1 со ссылкой на

имя_листа

Дополнительно. Это имя листа для использования в адресе ячейки. Если этот параметр опущен, то в адресе ячейки не используется имя листа.

Возвращает

Функция АДРЕС возвращает строковое/текстовое значение.

Применимо к

• Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

• Функция рабочего листа (WS)

Пример (как функция рабочего листа)

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

 =АДРЕС(4, 5)
  Результат:  "$E$4"
=АДРЕС(4, 5, 1)
  Результат:  "$E$4"
=АДРЕС(4, 5, 2)
  Результат:  "E$4"
=АДРЕС(4, 5, 3)
  Результат:  "$E4"
=АДРЕС(4, 5, 4)
  Результат:  "Е4"
=АДРЕС(4, 5, 1, ИСТИНА)
  Результат:  "$E$4"
=АДРЕС(4, 5, 1, ЛОЖЬ)
  Результат:  "R4C5"
=АДРЕС(4, 5, 1, ИСТИНА, "Лист1")
  Результат:  "Лист1!$E$4" 

Поделись:

Рекламные объявления

Функция АДРЕС Excel • My Online Training Hub

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

Excel АДРЕС Функция Синтаксис

Примечание. Аргументы в квадратных скобках необязательны.

По своей сути функция АДРЕС требует два аргумента, номера строки и столбца:

 =АДРЕС(1,1) 

 =$A$1 

row_num 9 0051	Числовое значение, указывающее номер строки ссылки на ячейку
номер_столбца	Числовое значение, указывающее номер столбца ссылки на ячейку
[abs_num]	Числовое значение, указывающее, является ли возвращаемая ссылка абсолютной или относительной. Абсолютная ссылка возвращается, если этот аргумент опущен. См. таблицу ниже.

	ИСТИНА или 1	возвращает стиль ссылок A1, в котором строки нумеруются, а столбцы помечаются в алфавитном порядке.
	ЛОЖЬ или 0	возвращает стиль ссылок R1C1, в котором строки и столбцы пронумерованы.

Загрузить рабочую тетрадь

Введите свой адрес электронной почты ниже, чтобы загрузить образец рабочей тетради.

Отправляя свой адрес электронной почты, вы соглашаетесь с тем, что мы можем отправить вам наш информационный бюллетень Excel по электронной почте.

Примеры функций Excel ADDRESS

В таблице ниже приведены примеры с различными аргументами abs_num и style [A1] (обратите внимание, что аргумент sheet_text опущен в этих примерах):

 =АДРЕС(СТРОКА(Rng),СТОЛБЦ(Rng),1,1) 

 =АДРЕС(53,2,1,1) 

 =$B$53 

 = АДРЕС(СТРОКА(Rng)+СТРОКИ(Rng)-1,СТОЛБЦ(Rng)+СТОЛБЦЫ(Rng)-1,1,1) 

 =АДРЕС(53+6-1,2+2-1,1,1) 

 =$C$58 

 =АДРЕС(СТРОКА(Rng),СТОЛБОН(Rng),1 ,1)&":"&АДРЕС(СТРОКА(Rng)+СТРОКИ(Rng)-1,СТОЛБЦ(Rng)+СТОЛБЦЫ(Rng)-1,1,1) 

 =АДРЕС(53,2,1,1) &":"&ADDRESS(53+6-1,2+2-1,1,1) 

 =$B$53:$C$58 

 =ЯЧЕЙКА("адрес",Rng) 

 =$B$53