Excel адрес ячейки формулой

АДРЕС (функция АДРЕС)

В этой статье описаны синтаксис формулы и использование функции АДРЕС в Microsoft Excel. Чтобы узнать больше о работе с почтовыми адресами и создании почтовых наклеек, см. по ссылкам в разделе См. также.

Описание

Функцию АДРЕС можно использовать для получения адреса ячейки на листе по номерам строки и столбца. Например, функция АДРЕС( 2;3) возвращает значение $C$2. Еще один пример: функция АДРЕС( 77;300) возвращает значение $KN$77. Чтобы передать функции АДРЕС номера строки и столбца в качестве ее аргументов, можно использовать другие функции (например, функции СТРОКА и СТОЛБЕЦ).

Синтаксис

Аргументы функции АДРЕС указаны ниже.

Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.

Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.

Тип_ссылки Необязательный аргумент. Задает тип возвращаемой ссылки.

Возвращаемый тип ссылки

Абсолютная строка; относительный столбец

Относительная строка; абсолютный столбец

A1 Необязательный аргумент. Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами. При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами. Если аргумент А1 имеет значение ИСТИНА или опущен, то функция АДРЕС возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ, функция АДРЕС возвращает ссылку типа R1C1.

Примечание: Чтобы изменить тип ссылок, используемый Microsoft Excel, откройте вкладку Файл, а затем нажмите кнопку Параметры и выберите пункт Формулы. В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1.

Имя_листа Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула = АДРЕС( 1;1;;;”Лист2″) возвращает значение Лист2!$A$1. Если аргумент имя_листа отсутствует, имя листа не используется, и адрес, возвращаемый функцией, ссылается на ячейку текущего листа.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник: support.office.com

Функция АДРЕС() в EXCEL

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

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

Синтаксис функции

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

Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.

Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.

Последние 3 аргумента являются необязательными.

[Тип_ссылки] Задает тип возвращаемой ссылки:

  • 1 или опущен: абсолютная ссылка , например $D$7
  • 2 : абсолютная ссылка на строку; относительная ссылка на столбец, например D$7
  • 3 : относительная ссылка на строку; абсолютная ссылка на столбец, например $D7
  • 4 : относительная ссылка, например D7

[а1] Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами, например D7 . При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами, например R7C5 (R означает ROW – строка, С означает COLUMN – столбец). Если аргумент А1 имеет значение ИСТИНА или 1 или опущен, то функция АДРЕС() возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ (или 0), функция АДРЕС() возвращает ссылку типа R1C1.

Чтобы изменить тип ссылок, используемый Microsoft Excel, нажмите кнопку Microsoft Office , затем нажмите кнопку Параметры Excel (внизу окна) и выберите пункт Формулы . В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1 .

[Имя_листа] Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула =АДРЕС(1;1;;;”Лист2″) возвращает значение Лист2!$A$1.

Примеры

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

Чаще всего адрес ячейки требуется, чтобы вывести значение ячейки. Для этого используется другая функция ДВССЫЛ() .

Формула =ДВССЫЛ(АДРЕС(6;5)) просто выведет значение из 6-й строки 5 столбца (Е). Эта формула эквивалентна формуле =Е6 .

Возникает вопрос: “Зачем весь этот огород с функцией АДРЕС() ?”. Дело в том, что существуют определенные задачи, в которых использование функции АДРЕС() очень удобно, например Транспонирование таблиц или Нумерация столбцов буквами или Поиск позиции ТЕКСТа с выводом значения из соседнего столбца.

Источник: excel2.ru

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

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

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

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

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

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

  • Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
  • Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
  • [тип_ссылки] – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:
  1. абсолютная на всю ячейку, например – $A$4
  2. абсолютная только на строку, например – A$4;
  3. абсолютная только на столбец, например – $A4;
  4. относительная на всю ячейку, например A4.
  • [a1] – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
  • [имя_листа] – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.
  1. Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
  2. Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
  3. Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр [имя_листа], который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».
Читайте также:  Эксель формула умножения ячеек



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

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

На листе «Курсы» создана таблица с актуальными курсами валют:

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

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

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

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

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

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

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

Исходная таблица имеет следующий вид:

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

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

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

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

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

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

Исходная таблица имеет следующий вид:

Исходная таблица.” src=”https://exceltable.com/funkcii-excel/images/funkcii-excel78-9.png” >

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

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

В результате получим ссылку:

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

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

Источник: exceltable.com

Excel. Формула адреса ячейки с максимальным (минимальным) значением в диапазоне

Формулы массива с каждым днем нравятся мне всё больше и больше! Недавно у меня возникла задача – найти адрес ячейки, содержащей максимальное значение в диапазоне. Для начала я создал тестовый диапазон А1:F10 (рис. 1), заполнив его случайными целыми числами от 1 до 100 с помощью функции =СЛУЧМЕЖДУ(1;100).

Рис. 1. Исходный диапазон

Скачать заметку в формате Word, примеры в формате Excel

Примечания. Если рисунки выглядят мелко, загрузите их на свой ПК. Мне не удалось «укротить» кавычки внутри формул, они все время изображались как «лапочки», поэтому пришлось использовать рисунки. Текст формул можно получить в файле Word или Excel.

Чтобы было легче визуально следить за максимальным и минимальным значениями в диапазоне, я задал условное форматирование (рис. 2).

Рис. 2. Условное форматирование по всем ячейкам диапазона позволяет выделять цветом ячейки, содержащие максимальное и минимальное значения

Напомню, что функция =СЛУЧМЕЖДУ() обновляется всякий раз, когда в Excel выполняется какая-либо операция с числами (но не форматирование). Можно просто жать F9 (эквивалентно команде «пересчитать»).

Читайте также:  Как в excel пользоваться формулами в

Кто не знаком с формулами массива, рекомендую начать с прочтения заметки Введение в формулы массива.

Аналог формулы для вычисления адреса ячейки с максимальным значением в диапазоне я нашел в книге Уокенбаха «Формулы в MS Excel 2010»:

Эта формула массива возвращает номер строки, в которой находится максимальное значение одномерного вертикального диапазона «массив»:

Для того, чтобы воспользоваться формулой Уокенбаха, надо присвоить имя нашему диапазону А1:F10. Я создал динамический массив под именем массив (рис. 3). Для упрощения можно создать статический массив, набрав вместо формулы, выделенной на рис. 3, следующее: =Лист1!$A$1:$F$10. Преимущество динамического массива на основе функции СМЕЩ проявятся позже, если вам понадобится расширить рамки вашего диапазона. Например, добавить строки и/или столбцы. Если при этом вы создали статический диапазон, вам понадобится руками изменить область определения массива под именем массив. Если же у вас был создан динамический массив, никаких изменений вносить не потребуется! Кто не знаком с работой функции СМЕЩ, рекомендую почитать Автоматическое обновление сводной таблицы.

Рис. 3. Присвоение имени двумерному диапазону

Рассмотрим как работает формула Уокенбаха:

Функция ЕСЛИ создает виртуальный массив, соответствующий диапазону массив. Если ячейка содержит максимальное значение, то соответствующий элемент в виртуальном массиве равен номеру строки этой ячейки, в противном случае элемент массива равен пустой строке. Функция МИН использует виртуальный массив в качестве своего аргумента и возвращает минимальный номер строки, где содержится максимальное значение диапазона массив. Если в диапазоне массив имеется несколько ячеек с максимальными значениями, то возвращается номер первой строки, где содержится это значение.

К сожалению, формула Уокенбаха предназначена для одномерного вертикального диапазона, поэтому она всегда возвращает адрес ячейки из столбца А, например, $A$8, когда правильное значение было бы $D$8.

Чтобы приспособить формулу Уокенбаха для вычисления адреса ячейки с максимальным значением в двумерном диапазоне, создадим еще один динамический массив – столбМакс (рис. 4)

Рис. 4. Присвоение имени одномерному вертикальному диапазону, содержащему один столбец, в который входит ячейка с максимальным значением

Рассмотрим подробнее, как функция СМЕЩ формирует этот одномерный вертикальный диапазон (рис. 5). Кстати, здесь, в отличие от выше описанного случая, не обойтись без динамического массива, так как заранее номер столбца, содержащего ячейку с максимальным значением, не известен…

Рис. 5. Функция СМЕЩ, динамически формирующая одномерный вертикальный диапазон

Функция ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»») создает виртуальный массив, соответствующий диапазону массив. Если ячейка содержит максимальное значение, то соответствующий элемент в виртуальном массиве равен номеру столбца этой ячейки, в противном случае элемент массива равен пустой строке.

Функция МИН(ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»»)) использует виртуальный массив в качестве своего аргумента и возвращает минимальный номер столбца, где содержится максимальное значение диапазона массив. Если в диапазоне массив имеется несколько ячеек с максимальными значениями, то возвращается номер самого левого столбца, где содержится это максимальное значение.

Итоговая формула для нахождения адреса ячейки с максимальным значением в двумерном диапазоне имеет следующий вид (рис. 6):

Рис. 6. Формула для нахождения адреса ячейки с максимальным значением в двумерном диапазоне

Подробно изучить, как работает формула можно с помощью сервиса Excel – пошаговое вычисление формулы (рис. 7).

Рис. 7. Пошаговое вычисление формулы

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

Источник: baguzin.ru

Канал в Telegram

Вы здесь

Типы адресации в Excel. Ссылки на ячейки

Что из себя представляет ссылка в Excel?

Ранее мы уже встречались с ссылками, когда задавали тот или иной диапазон, или просто писали формулы. Ссылки выглядели следующим образом: «=A1+A2+B1….» или диапазон «=$A$1:$B$3». Т.е. ссылки(или адреса) ячеек в двухмерной матрице (таблице), где буква это столбец, цифра номер строки. Теперь давайте рассмотрим, какие типы ссылок есть в Excel.

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

Если при работе с книгой, в формулах Вы поставите ссылку на лист или другие листы этой же книги, то получите относительные ссылки вида «=A1» или «=Лист2!A21». Данный вид ссылок запоминает, на каком расстоянии находится ячейка (ячейки) и при протяжке формул по столбцам или строкам, меняется и адрес в формуле относительно ячейки, в которой прописана формула. Например, в ячейке B3 стоит формула «=С3+D4», то при протяжке формулы в ячейке B3 вниз, вся формула будет изменяться следующим образом:

Или если скопировать формулу в ячейке B4 и вставить в E4, то получим «=F4+G5»

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

Абсолютные ссылки это прямая противоположность относительным ссылкам. Абсолютная ссылка на ячейку остается неизменной при любых манипуляция с формулой. Записываются абсолютные ссылки следующим образом: «=$C$1+$D$5». В этом случае знак «$» фиксирует столбцы и строки. Теперь если протянуть формулу вниз или в сторону, адреса по-прежнему будут «=$C$1+$D$5». Использование абсолютных ссылок полезно в тех случаях, когда необходимо в расчетах использовать один общий для всех формул параметр, при этом, если этот параметр изменяется, его достаточно исправить только в одной ячейке. Рассмотрим пример, в котором необходимо найти среднесуточную сумму продаж, за месяц, в котором 31 день:

Читайте также:  Как создать таблицу в excel пошаговая инструкция с формулами

В этом примере были использованы как относительные ссылки, так и абсолютные.

Смешанные ссылки

Смешанный тип ссылок это и абсолютный и относительный тип, вместе взятые. Этот вид ссылок позволяет зафиксировать либо столбец, либо строки и имеет следующий вид: «=$C1+D$5».

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

Для удобства изменения типа ссылок, в Excel предусмотрена клавиша F4. При указании ссылки (или если формула содержит несколько ссылок, выделите нужную) нажмите F4. При повторных нажатиях, тип ссылки будет изменяться.

Источник: www.programm-school.ru

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

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

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

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

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

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

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

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

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

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

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

ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])

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

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

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

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

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

Источник: office-guru.ru