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

Функция АДРЕС возвращает адресок определенной ячейки (текстовое значение), на которую указывают номера столбца и строчки. Например, в итоге выполнения функции =АДРЕС(5;7) будет выведено значение $G$5.

Примечание: наличие знаков «$» в адресе ячейки $G$5 свидетельствует о том, что ссылка на данную ячейку является абсолютной, другими словами не изменяется при копировании данных.

Функция АДРЕС в Excel: описание особенностей синтаксиса

Функция АДРЕС имеет последующую синтаксическую запись:

1-ые два аргумента данной функции являются неотклонимыми для наполнения.

Номер_строки – числовое значение, соответственное номеру строчки, в которой находится требуемая ячейка;
Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором размещена разыскиваемая ячейка;
[тип_ссылки] – число из спектра от 1 до 4, соответственное одному из типов возвращаемой ссылки на ячейку:

абсолютная на всю ячейку, к примеру — $A$4
абсолютная лишь на строчку, к примеру — A$4;
абсолютная лишь на столбец, к примеру — $A4;
относительная на всю ячейку, к примеру A4.

[a1] – логическое значение, определяющее один из 2-ух типов ссылок: A1 или R1C1;
[имя_листа] – текстовое значение, которое описывает имя листа в документе Excel. Употребляется для сотворения наружных ссылок.

Ссылки типа R1C1 употребляются для цифрового обозначения столбцов и строк. Для возврата ссылок такового типа в качестве параметра a1 обязано быть очевидно обозначено логическое значение ЛОЖЬ либо соответственное числовое значение 0.
Стиль ссылок в Excel быть может изменен методом установки/снятия флага пт меню «Стиль ссылок R1C1», который находится в «Файл – Характеристики – Формулы – Работа с Формулами».
Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно применять параметр [имя_листа], который воспринимает текстовое значение, соответственное наименованию требуемого листа, к примеру «Лист7».

Примеры использования функции АДРЕС в Excel

Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных критерий. Для работы с животрепещущими данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.

На листе «Курсы» сотворена таблица с животрепещущими курсами валют:

На отдельном листе «Цены» сотворена таблица с продуктами, отображающая стоимость в баксах США (Соединённые Штаты Америки — государство в Северной Америке) (USD):

В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем последующую формулу: =АДРЕС(3;2;1;1;»Курсы»).

3 – номер строчки, в которой содержится разыскиваемая ячейка;
2 – номер столбца с разыскиваемой ячейкой;
1 – тип ссылки – абсолютная;
1 – выбор стиля ссылок с буквенно-цифровой записью;
«Курсы» — заглавие листа, на котором находится таблица с разыскиваемой ячейкой.

Для расчета цены в рублях используем формулу: =B3*ДВССЫЛ(D3).

Функция ДВССЫЛ нужна для получения числового значения, хранимого в ячейке, на которую показывает ссылка. В итоге вычислений для других продуктов получим последующую таблицу:

Как получить адресок ссылки на ячейку Excel?

Пример 2. В таблице содержатся данные о стоимости продуктов, отсортированные в порядке возрастания цены. Нужно получить ссылки на ячейки с малой и наибольшей стоимостью продуктов соответственно.

Интересно почитать: Как в excel увеличить высоту ячейки

Начальная таблица имеет последующий вид:

Для получения ссылки на ячейку с малой стоимостью продукта используем формулу:

Функция АДРЕС воспринимает последующие характеристики:

число, соответственное номеру строчки с наименьшим значением цены (функция МИН делает поиск малого значения и возвращает его, функция ПОИСКПОЗ находит позицию ячейки, содержащей малое значение цены. К приобретенному значению добавлено 2, так как ПОИСКПОЗ производит поиск относительно спектра избранных ячеек.
2 – номер столбца, в котором находится разыскиваемая ячейка.

Аналогичным методом получаем ссылку на ячейку с наибольшей ценой продукта. В итоге получим:

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

Пример 3. В таблице содержится ячейка, данные из которой употребляются в другом программном продукте. Для обеспечения сопоставимости нужно предоставить ссылку на нее в виде R1C1.

Начальная таблица имеет последующий вид:

Для получения ссылки на ячейку B6 используем последующую формулу: =АДРЕС(6;2;1;0).

6 – номер строчки разыскиваемой ячейки;
2 – номер столбца, в котором содержится ячейка;
1 – тип ссылки (абсолютная);
0 – указание на стиль R1C1.

В итоге получим ссылку:

Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтоб различать абсолютные и относительные ссылки употребляются квадратные скобки «[]». К примеру, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку воспримет последующий вид:

Так смотрится абсолютный тип ссылок по строчкам и столбцам при использовании стиля R1C1.

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

Вчера в марафоне 30 функций Excel за 30 дней мы находили элементы массива с помощью функции MATCH (ПОИСКПОЗ) и нашли, что она непревзойденно работает в команде с иными функциями, таковыми как VLOOKUP (ВПР) и INDEX (ИНДЕКС).

20-й денек нашего марафона мы посвятим исследованию функции ADDRESS (АДРЕС). Она возвращает адресок ячейки в текстовом формате, используя номер строчки и столбца. Нужен ли нам этот адресок? Можно ли создать то же самое при помощи остальных функций?

Давайте обратимся к сведениям по функции ADDRESS (АДРЕС) и изучим примеры работы с ней. Если у Вас есть доборная информация либо примеры, пожалуйста, делитесь ими в комментах.

Функция 20: ADDRESS (АДРЕС)

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

Как можно применять функцию ADDRESS (АДРЕС)?

Функция ADDRESS (АДРЕС) может вернуть адресок ячейки либо работать в сочетании с иными функциями, чтоб:

Получить адресок ячейки, зная номер строчки и столбца.
Отыскать значение ячейки, зная номер строчки и столбца.
Вернуть адресок ячейки с самым огромным значением.

Интересно почитать: Excel подсчет количества символов в ячейке

Синтаксис ADDRESS (АДРЕС)

Функция ADDRESS (АДРЕС) имеет вот таковой синтаксис:

abs_num (тип_ссылки) – если равно 1 либо совершенно не обозначено, то функция вернет абсолютный адресок ($A$1). Чтоб получить относительный адресок (A1), используйте значение 4. Другие варианты: 2=A$1, 3=$A1.
a1 – если TRUE (ИСТИНА) либо совершенно не обозначено, функция возвращает ссылку в стиле A1, если FALSE (ЛОЖЬ), то в стиле R1C1.
sheet_text (имя_листа) – имя листа быть может обозначено, если Вы желаете созидать его в возвращаемом функцией итоге.

Ловушки 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 – это 1-ые 5000 строк листа Excel.

Пример 3: Возвращаем адресок ячейки с наибольшим значением

В этом примере мы найдём ячейку с наибольшим значением и используем функцию ADDRESS (АДРЕС), чтоб получить её адресок.

Функция MAX (МАКС) находит наибольшее число в столбце C.

Дальше в игру вступает функция ADDRESS (АДРЕС) в сочетании с MATCH (ПОИСКПОЗ), которая находит номер строчки, и COLUMN (СТОЛБЕЦ), которая описывает номер столбца.

Excel: Ссылки относительные и абсолютные

Нередко при использовании формул в Excel опосля ввода формулы в одну ячейку нужно скопировать либо распространить ее на блок ячеек.

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

Интересно почитать: Excel слишком много различных форматов ячеек

Ссылка в Excel — адресок ячейки либо связного спектра ячеек.

Адрес ячейки определяется пересечением столбца и строчки, к примеру: A1, C16.

Адрес спектра ячеек задается адресом верхней левой ячейки и нижней правой, к примеру: A1:C5.

Ссылки в Excel бывают 3-х типов:

Относительные ссылки (пример: A1);
Абсолютные ссылки (пример: $A$1);
Смешанные ссылки (пример: $A1 либо A$1).

Относительные ссылки

«Относительность» ссылки значит, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.

Пример.

В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца на право и на три (С3) и две (С4) ячейки выше.

При копировании либо «протаскивании» c помощью Маркера наполнения формулы, к примеру, в ячейку А7 формула меняется (Excel пересчитывает адреса всех относительных ссылок в ней в согласовании с новеньким положением ячейки).

Сейчас формула в ячейке А7 ссылается на ячейки С4 и С5. Наименования ссылок поменялись, но осталось постоянным их положение относительно ячейки, в которой находится формула (два столбца на право и на три (С4) и две (С5) ячейки выше).

Относительные ссылки целенаправлено применять в формулах в 2-ух вариантах:

Если формулу не предполагается копировать в остальные ячейки.
Если формулу нужно скопировать в схожие ячейки.

Абсолютные ссылки

Если формула просит, чтоб адресок ячейки оставался постоянным при копировании, то обязана употребляться абсолютная ссылка. Для этого перед знаками ссылки инсталлируются знаки "$" (формат записи $А$1).

Абсолютные ссылки в формулах употребляются в вариантах:

Необходимости внедрения в формулах констант.
Необходимости фиксации спектра для проведения расчетов.

Пример.

В спектре А1:А5 указаны заработной платы служащих отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем премию всякого сотрудника и поместим в спектре В1:В5.

Для расчета премии первого сотрудника введем в ячейку В1 формулу =А1*С1.

Если мы при помощи Маркера наполнения протянем формулу вниз, то получим в ячейке В2 формулу =А2*С2, в ячейке В3 — =А3*С3 и т.д. Потому что в ячейках спектра С2:С5 нет значений, то в спектре В2 : В5 получаем нули.
Для исправления ошибки, нужно зафиксировать в формуле ссылку на ячейку С1, т.е. поменять относительную ссылку С1 на абсолютную $C$1.

выделите ячейку В1
в Строке формул поставьте символ «$» перед буковкой столбца и адресом строчки $С$1. Наиболее резвый метод — в Строке формул поставьте курсор на ссылку С1 (можно перед С, перед либо опосля 1) и нажмите один раз кнопку «F4». Ссылка С1 выделится и перевоплотится в $C$1.
нажмите ENTER

Формула приняла вид « =А1*$С$1».
Маркером наполнения протяните полученную формулу вниз.

Сейчас спектр В2: В5 заполнен значениями премий служащих.

Резвый метод создать относительную ссылку абсолютной — выделить относительную ссылку и надавить один раз кнопку «F4», при всем этом Excel сам проставит знаки «$».