Функция столбец адрес в 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

.

Возникает вопрос: «Зачем весь этот огород с функцией

АДРЕС()

?». Дело в том, что существуют определенные задачи, в которых использование функции

АДРЕС()

очень удобно, например

Транспонирование таблиц

или

Нумерация столбцов буквами

или

Поиск позиции ТЕКСТа с выводом значения из соседнего столбца.

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

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

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

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

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

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

Описание аргументов:

• Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
• Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
• [тип_ссылки] – число из диапазона от 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.

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

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

Аргументы функции:

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

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

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

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

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

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

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

​Смотрите также​Перейдите в ячейку D6​ тип ссылок: А1​Исходная таблица имеет следующий​ столбца, в котором​.​ можете добавлять комментарии​​Discount​​ добавить отступ, редактор​

​ начинающегося с ячейки​​ строке диапазона, номер​​ 2 в первой​ требуется возвратить значение.​

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

​INDIRECT​(тип_ссылки) – если​

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

​ или R1C1. Если​​ вид:​ расположена искомая ячейка;​В раскрывающемся списке​

​ в отдельных строках​, до двух дробных​

​ Visual Basic автоматически​​ B2 и заканчивающегося​

• ​ столбца 1 —​​ строке и значения​ Если аргумент «номер_строки»​
• ​(ДВССЫЛ) позже в​​ равно​ адрес, возвращаемый функцией,​ из соседнего столбца.​ ADDRESS(), возвращает адрес​
• ​ из выпадающего списка:​​ аргумент а1 имеет​Для получения ссылки на​[тип_ссылки] – число из​Управление​
• ​ или в правой​​ разрядов:​ вставит его и​

​ пересечением пятой строки​​ его первому столбцу​ 3 и 4​ опущен, аргумент «номер_столбца»​ рамках марафона​1​ ссылается на ячейку​В этой статье описаны​ ячейки на листе,​ «Главная»-«Сумма»-«Число».​ значение ИСТИНА или​ ячейку с минимальной​ диапазона от 1​выберите​ части строк, содержащих​Discount = Application.Round(Discount, 2)​ для следующей строки.​ и второго столбца​ и т. д.​ во второй строке.​ является обязательным.​30 функций Excel за​или вообще не​ текущего листа.​ синтаксис формулы и​ для которой указаны​На этот рас нам​

​ опущен, то функция​ стоимостью товара используем​ до 4, соответствующее​​Надстройки Excel​​ код VBA. Советуем​​В VBA нет функции​​ Чтобы сдвинуть строку​ диапазона A2:A6, т. е.​​ Ссылка, возвращаемая функцией​​2​​Номер_столбца​​ 30 дней​ указано, то функция​​Скопируйте образец данных из​​ использование функции​

​ номера строки и​​ не подходит автоматическое​ АДРЕС возвращает ссылку​ формулу:​ одному из типов​. Затем нажмите кнопку​ начинать длинный блок​

Примеры

​ округления, но она​ на один знак​ сумма значений из​ ИНДЕКС, указывает на​Значение ячейки на пересечении​

​     — необязательный аргумент.​.​ возвратит абсолютный адрес​ следующей таблицы и​АДРЕС​

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

​Перейти​ кода с комментария,​ есть в Excel.​ табуляции влево, нажмите​ диапазона B2:B6.​ пересечение строки «номер_строки»​ второй строки и​ Выбирает столбец в​=INDIRECT(ADDRESS(C2,C3))​ ($A$1). Чтобы получить​ вставьте их в​в Microsoft Excel.​ возвращает значение​

excel2.ru

Функция АДРЕС

​ поэтому его нужно​ этот аргумент имеет​ параметры:​​ ячейку:​​.​ в котором объясняется​ Чтобы использовать округление​SHIFT+TAB​2,42​ и столбца «номер_столбца».​ второго столбца в​​ массиве, из которого​​=ДВССЫЛ(АДРЕС(C2;C3))​

Описание

​ относительный адрес (A1),​​ ячейку A1 нового​​ Чтобы узнать больше​$C$2​ исправить на D2:D4.​ значение ЛОЖЬ, то​число, соответствующее номеру строки​​абсолютная на всю ячейку,​​В диалоговом окне​​ его назначение, а​​ в этом операторе,​​.​К началу страницы​​Если указать в качестве​​ массиве, указанном выше.​​ требуется возвратить значение.​​Функция​​ используйте значение​​ листа Excel. Чтобы​​ о работе с​.​ После чего жмем​ функция АДРЕС возвращает​ с минимальным значением​​ например — $A$4​​Надстройки​​ затем использовать встроенные​​ необходимо указать VBA,​

Синтаксис

​Теперь вы готовы использовать​

​Хотя в Excel предлагается​ аргумента «номер_строки» или​

• ​4​​ Если аргумент «номер_столбца»​INDIRECT​4​

• ​ отобразить результаты формул,​​ почтовыми адресами и​Функция АДРЕС() возвращает​ Enter.​

• ​ ссылку типа R1C1.​​ цены (функция МИН​абсолютная только на строку,​

• ​ выделите их и​​ создании почтовых наклеек,​текстовое​Из D6 в ячейку​Имя_листа — это​ выполняет поиск минимального​ например — A$4;​ с именем книги,​ отдельных операторов.​ Round следует искать​ Закройте редактор Visual​ функций, в нем​ (ноль), функция ИНДЕКС​ Это формула массива, которую​ является обязательным.​​ и без функции​​2​ нажмите клавишу F2,​ см. по ссылкам​значение в виде​​ E6 скопируйте функцию​​ текст, определяющий имя​ значения и возвращает​

  ​абсолютная только на столбец,​​ как показано ниже.​Кроме того, рекомендуется присваивать​ в объекте Application​​ Basic, выделите ячейку​​ может не быть​ возвратит ссылку на​​ нужно вводить с​​Если используются оба аргумента​​ADDRESS​​=A$1,​​ а затем —​​ в разделе​ адреса ячейки.​​ =СЧЕТ() – это​​ рабочего листа, который​

• ​ его, функция ПОИСКПОЗ​​ например — $A4;​Создав нужные функции, выберите​ макросам и пользовательским​ (Excel). Для этого​ G7 и введите​​ той функции, которая​​ целый столбец или​​ помощью клавиш CTRL+SHIFT+ВВОД.​​ — и «номер_строки»,​​(АДРЕС). Вот как​​3​ клавишу ВВОД. При​См. также​АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [a1],​ функция Excel для​

Пример

​используется для формирования​ находит позицию ячейки,​относительная на всю ячейку,​Файл​ функциям описательные имена.​ добавьте слово​ следующий код:​ нужна для ваших​ целую строку соответственно.​ Excel автоматически заключит​ и «номер_столбца», —​ можно, используя оператор​=$A1.​

support.office.com

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

​ цены. К полученному​​[a1] – логическое значение,​Сохранить как​​ название​перед словом Round.​Excel вычислит 10%-ю скидку​​ разработчики Excel не​​ «номер_области» должны указывать​ скобки {}. Если​ значение ячейки на​&​– если TRUE​​ все данные.​​АДРЕС​​ Обязательный аргумент. Номер строки,​​На данном примере хорошо​

​ внешние листы не​ значению добавлено 2,​ определяющее один из​​.​​MonthLabels​ Используйте этот синтаксис​ для 200 единиц​ могли предугадать все​ на ячейку внутри​ вы попытаетесь ввести​ пересечении указанных строки​«, слепить нужный адрес​ (ИСТИНА) или вообще​Формула​

​можно использовать для​ используемый в ссылке​​ видно, что функция​​ используются.​ поскольку ПОИСКПОЗ осуществляет​ двух типов ссылок:​В диалоговом окне​вместо​ каждый раз, когда​ по цене 47,50​

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

​ потребности пользователей. Однако​​ аргумента «ссылка»; в​​ их вручную, Excel​ и столбца.​ в стиле​ не указано, функция​Описание​ получения адреса ячейки​ на ячейку.​ =СЧЕТ() игнорирует ячейки,​​Abram pupkin​​ поиск относительно диапазона​​ A1 либо R1C1;​​Сохранить как​Labels​ нужно получить доступ​ ₽ и вернет​

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

​ в Excel можно​​ противном случае функция​​ отобразит формулу как​Если указать в качестве​R1C1​ возвращает ссылку в​Результат​

• ​ на листе по​Номер_столбца​ которые не содержат​
• ​: внесу и я​ выбранных ячеек.​[имя_листа] – текстовое значение,​
• ​откройте раскрывающийся список​, чтобы более точно​

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

​ к функции Excel​​ 950,00 ₽.​​ создавать собственные функции,​ ИНДЕКС возвращает значение​

​ текст. Если при​
​ аргумента "номер_строки" или​

• ​и в результате​​ стиле​=АДРЕС(2;3)​​ номерам строки и​​ Обязательный аргумент. Номер столбца,​ числа или пусты.​ свои 5 копеек.​2 – номер столбца,​ которое определяет имя​Тип файла​​ указать его назначение.​​ из модуля VBA.​​В первой строке кода​​ и ниже вы​​ ошибки #ССЫЛКА!. Если​​ вводе формулы не​
• ​ «номер_столбца» значение 0​​ получить значение ячейки:​A1​Абсолютная ссылка​ столбца. Например, функция​ используемый в ссылке​​В ячейки D7 и​​Если сказать что​ в котором находится​​ листа в документе​​и выберите значение​
• ​ Описательные имена макросов​​Пользовательские функции должны начинаться​​ VBA функция DISCOUNT(quantity,​ найдете все нужные​ аргументы «номер_строки» и​ нажать клавиши CTRL+SHIFT+ВВОД,​ (ноль), функция ИНДЕКС​=INDIRECT(«R»&C2&»C»&C3,FALSE)​

Ловушки ADDRESS (АДРЕС)

​, если FALSE (ЛОЖЬ),​​$C$2​​АДРЕС(​ на ячейку.​ E7 введем логическую​ информация находится в​ искомая ячейка.​ Excel. Используется для​Надстройка Excel​​ и пользовательских функций​​ с оператора Function​ price) указывает, что​ для этого инструкции.​ «номер_столбца» опущены, функция​

Пример 1: Получаем адрес ячейки по номеру строки и столбца

​ она возвратит ошибку​​ возвратит массив значений​​=ДВССЫЛ(«R»&C2&»C»&C3;ЛОЖЬ)​ то в стиле​=АДРЕС(2;3;2)​2;3)​Последние 3 аргумента являются​ функцию, которая позволит​ 4-ом столбце и​Аналогичным способом получаем ссылку​ создания внешних ссылок.​. Сохраните книгу с​​ особенно полезны, если​​ и заканчиваться оператором​

​ функции DISCOUNT требуется​
​Пользовательские функции (как и​

Абсолютная или относительная

​ ИНДЕКС возвращает область​ #ЗНАЧ!​​ для целого столбца​​Функция​R1C1​Абсолютная строка; относительный столбец​

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

​ End Function. Помимо​
​ два аргумента:​

A1 или R1C1

​ макросы) записываются на​​ в аргументе «ссылка»,​​К началу страницы​ или целой строки​​INDEX​​.​C$2​$C$2​​[Тип_ссылки]​​ ли студенты имеют​

​ вы сразу понимаете​
​ максимальной ценой товара.​

Название листа

​Ссылки типа R1C1 используются​ как​ с похожим назначением.​ названия функции, оператор​quantity​ языке программирования​​ заданную аргументом «номер_области».​​Возвращает ссылку на ячейку,​

​ соответственно. Чтобы использовать​
​(ИНДЕКС) также может​

Пример 2: Находим значение ячейки, используя номер строки и столбца

​sheet​​=АДРЕС(2;3;2;ЛОЖЬ)​​. Еще один пример:​ Задает тип возвращаемой ссылки:​ оценки. Пример использования​ что это будет​ В результате получим:​ для цифрового обозначения​MyFunctions​То, как вы документируйте​​ Function обычно включает​​(количество) и​Visual Basic для приложений​​Результатом вычисления функции ИНДЕКС​​ расположенную на пересечении​ значения, возвращенные как​​ вернуть значение ячейки,​​_text​Абсолютная строка; относительный столбец​​ функция​1​​ функции ЕСЛИ:​

​ ячейка D2.​
​Пример 3. В таблице​

​ столбцов и строк.​​.​​ свои макросы и​ один или несколько​​price​​ (VBA)​ является ссылка, которая​ указанной строки и​​ массив, введите функцию​​ если указан номер​(имя_листа) – имя​​ с типом ссылки​​АДРЕС(​или опущен: абсолютная​

​Перейдите в ячейку D7​
​Гораздо сложнее найти​

​ содержится ячейка, данные​​ Для возвращения ссылок​​Сохранив книгу, выберите​ пользовательские функции, —​ аргументов. Однако вы​(цена). При вызове​

​. Они отличаются от​
​ интерпретируется в качестве​

​ указанного столбца. Если​​ ИНДЕКС как формула​ строки и столбца:​ листа может быть​

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

​ R1C1​77;300)​ ссылка, например $D$7​ и выберите инструмент:​​ 154-ый столбец.​​ из которой используются​ такого типа в​

​Сервис​​ ваше личное дело,​​ можете создать функцию​ функции в ячейке​ макросов двумя вещами.​

​ таковой другими функциями.​
​ ссылка составлена из​

​ массива в горизонтальный​=INDEX(1:5000,C2,C3)​​ указано, если Вы​​R2C[3]​возвращает значение​​2​​ «Формулы»-«Логические»-«ЕСЛИ».​используем адрес​​ в другом программном​​ качестве параметра a1​>​

​ но важно выбрать​
​ без аргументов. В​

​ листа необходимо указать​ Во-первых, в них​
​ В зависимости от​
​ несмежных выделенных диапазонов,​

​ диапазон ячеек для​

office-guru.ru

ИНДЕКС (функция ИНДЕКС)

​=ИНДЕКС(1:5000;C2;C3)​ желаете видеть его​=АДРЕС(2;3;1;ЛОЖЬ;»[Книга1]Лист1″)​$KN$77​

​: абсолютная ссылка на​Заполняем аргументы функции в​

• ​=АДРЕС (2;154) машина​ продукте. Для обеспечения​ должно быть явно​Надстройки Excel​ определенный способ и​

• ​ Excel доступно несколько​ эти два аргумента.​ используются процедуры​ формулы значение, возвращаемое​

Форма массива

Описание

​ можно указать, какой​ строки и в​1:5000​ в возвращаемом функцией​

​Абсолютная ссылка на другую​. Чтобы передать функции​ строку; относительная ссылка​ диалоговом окне как​

Синтаксис

​ выдаст $EX$2​

​ совместимости необходимо предоставить​ указано логическое значение​

• ​.​​ придерживаться его.​ встроенных функций (например,​ В формуле =DISCOUNT(D7;E7)​

  • ​Function​ функцией ИНДЕКС, может​ из них требуется​ вертикальный — для столбца.​– это первые​ результате.​

  • ​ книгу и лист​АДРЕС​ на столбец, например​ показано на рисунке​и еще 1​ ссылку на нее​ ЛОЖЬ или соответствующее​В диалоговом окне​Чтобы использовать функцию, необходимо​ СЛЧИС и ТДАТА),​

• ​ аргумент​​, а не​ использоваться как ссылка​ использовать.​ Чтобы ввести формулу​ 5000 строк листа​Функция​'[Книга1]Лист1′!R2C3​

• ​номера строки и​​ D$7​ и жмем ОК​ пример но уже​ в виде R1C1.​ числовое значение 0.​Надстройки​ открыть книгу, содержащую​

Замечания

• ​ в которых нет​quantity​Sub​ или как значение.​ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области])​ массива, нажмите сочетание​ Excel.​

• ​ADDRESS​=АДРЕС(2;3;1;ЛОЖЬ;»ЛИСТ EXCEL»)​ столбца в качестве​3​ (обратите внимание вторая​ посложнее.​Исходная таблица имеет следующий​Стиль ссылок в Excel​нажмите кнопку «Обзор»,​ модуль, в котором​ аргументов.​имеет значение D7,​. Это значит, что​ Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2))​Аргументы функции ИНДЕКС описаны​ клавиш CTRL+SHIFT+ВВОД.​В этом примере мы​(АДРЕС) возвращает лишь​

  ​Абсолютная ссылка на другой​​ ее аргументов, можно​: относительная ссылка на​ ссылка $A$4 -​

• ​Откройте чистый лист​ вид:​ может быть изменен​ найдите свою надстройку,​ она была создана.​После оператора Function указывается​ а аргумент​

Примеры

Пример 1

​ они начинаются с​ эквивалентна формуле ЯЧЕЙКА(«ширина»;B1).​ ниже.​Примечание:​ найдём ячейку с​ адрес ячейки в​

​ лист​ использовать другие функции​ строку; абсолютная ссылка​ абсолютная):​ и поставьте «1»​Для получения ссылки на​ путем установки/снятия флажка​ нажмите кнопку​ Если такая книга​ один или несколько​price​ оператора​ Функция ЯЧЕЙКА использует​

Пример 2

​Вчера в марафоне​СТРОКА​ $D7​ в E7.​ от A1до K15​ следующую формулу: =АДРЕС(6;2;1;0).​ ссылок R1C1», который​

​, а затем установите​ попытке использования функции​ проверят соответствия условиям​ Если скопировать формулу​, а не​ ИНДЕКС, как ссылку.​ на один или​ формулы массива.​ADDRESS​ значение ячейки, используйте​30 функций Excel за​и​

​ – Параметры –​​ надстройкой в поле​ При ссылке на​ с использованием аргументов,​ вы получите указанные​, и заканчиваются оператором​ такая формула, как​Если в качестве аргумента​ должны указывать на​ её адрес.​ аргумента функции​мы находили элементы​).​ D7​ у нас количество​

​ любую свободную ячейку​

Ссылочная форма

Описание

​ искомой ячейки;​ Формулы – Работа​Доступные надстройки​ функцию, хранящуюся в​ переданных функции. Наконец,​ ниже результаты.​End Function​ 2*ИНДЕКС(A1:B2;1;2), преобразует значение,​ «ссылка» используется несмежный​

Синтаксис

​ ячейку внутри заданного​

​Функция​INDIRECT​

• ​ массива при помощи​​АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])​[а1]​ всех студентов, а​

  • ​=АДРЕС (СУММПРОИЗВ ((A1:K15=1)*СТРОКА​2 – номер столбца,​ с Формулами».​.​

  • ​ другой книге, необходимо​ в процедуру функции​Рассмотрим, как Excel обрабатывает​, а не​ возвращаемое функцией ИНДЕКС,​ диапазон, его необходимо​ массива; в противном​MAX​(ДВССЫЛ) или примените​ функции​

• ​Аргументы функции АДРЕС указаны​​    Логическое значение, которое определяет​ в ячейке D6​ (A1:K15));СУММПРОИЗВ ((A1:K15=1)*СТОЛБЕЦ (A1:K15)))​ в котором содержится​Если требуется ссылка на​

• ​После выполнения этих действий​​ указать перед ее​ следует включить оператор,​ эту функцию. При​End Sub​ в число в​

• ​ заключить в скобки​​ случае функция ИНДЕКС​(МАКС) находит максимальное​ одну из альтернативных​MATCH​ ниже.​ тип ссылок: А1​ и E6 –​Функции в Excel –​ ячейка;​ ячейку, которая находится​ ваши пользовательские функции​ именем название книги.​ назначающий значение переменной​ нажатии клавиши​. Во-вторых, они выполняют​ ячейке B1.​Если каждая область в​ возвратит значение ошибки​ число в столбце​ формул, показанных в​(ПОИСКПОЗ) и обнаружили,​Номер_строки​ или R1C1. При​ количество оценок. Функция​ это вычислительные инструменты​1 – тип ссылки​ в другом листе​ будут доступны при​ Например, если вы​ с тем же​ВВОД​ различные вычисления, а​

​Примечание:​ ссылке содержит только​ #ССЫЛКА!.​ C.​ примере 2.​ что она отлично​    Обязательный аргумент. Номер строки,​ использовании ссылок типа​ ЕСЛИ() проверяет, совпадают​

Замечания

• ​ пригодны для самых​ (абсолютная);​ данного документа Excel,​ каждом запуске Excel.​ создали функцию DISCOUNT​ именем, что у​Excel ищет имя​ не действия. Некоторые​  Функция ЯЧЕЙКА недоступна​ одну строку или​В этих примерах функция​=MAX(C3:C8)​При помощи функции​ работает в команде​ используемый в ссылке​ А1 столбцы обозначаются​

• ​ ли показатели D6​ разных отраслей деятельности:​0 – указание на​ полезно использовать параметр​ Если вы хотите​ в книге Personal.xlsb​ функции. Это значение​

• ​DISCOUNT​ операторы (например, предназначенные​ в Excel Web​ один столбец, аргумент​ ИНДЕКС используется для​=МАКС(C3:C8)​ADDRESS​ с другими функциями,​ на ячейку.​ буквами, а строки​ и E6 со​ финансы, статистика, банковское​

• ​ стиль R1C1.​ [имя_листа], который принимает​ добавить функции в​ и хотите вызвать​ возвращается в формулу,​в текущей книге​ для выбора и​ App.​ «номер_строки» или «номер_столбца»​ поиска значения ячейки,​Далее в игру вступает​(АДРЕС) Вы можете​ такими как​Номер_столбца​ — цифрами, например​ значением A4. Если​ дело, бухгалтерский учет,​В результате получим ссылку:​ текстовое значение, соответствующее​ библиотеку, вернитесь в​

​ ее из другой​​ которая вызывает функцию.​ и определяет, что​ форматирования диапазонов) исключаются​

Примеры

​Скопируйте образец данных из​ соответственно является необязательным.​ находящейся на пересечении​ функция​ получить адрес ячейки​VLOOKUP​    Обязательный аргумент. Номер столбца,​ D7. При использовании​ совпадают, то получаем​ инженерство и проектирование,​Практическое применение функции АДРЕС:​ названию требуемого листа,​ редактор Visual Basic.​

​ например R7C5 (R​

support.office.com

Создание пользовательских функций в Excel

​В логическом выражении для​ вычислительных функций включено​ строкам.​ Excel содержится ячейка,​ с таким же​.​ только возвращать значение​quantity​ пользовательские функции. Для​ отобразить результаты формул,​Номер_строки​ вставьте их в​(ПОИСКПОЗ), которая находит​ эти два аргумента,​ мы посвятим изучению​

Создание простой пользовательской функции

​ возвращаемой ссылки.​ означает ROW -​ удобства применяется 2​​ в данный учебник​Примечание: при использовании стиля​​ отображающая динамически изменяемые​ названием, как у​Чтобы вставить пользовательскую функцию​ в формулу на​​и​​ создания функций и​​ выделите их и​​    — обязательный аргумент. Номер​ ячейку A1 нового​ номер строки, и​​ результатом будет абсолютный​​ функции​​Тип_ссылки​​ строка, С означает​​ типа ссылок: относительная​​ с уроками по​​ R1C1 запись абсолютной​​ данные в зависимости​ файла надстройки (но​ быстрее (и избежать​ листе или в​price​ макросов используется​ нажмите клавишу F2,​ строки в диапазоне,​ листа Excel. Чтобы​COLUMN​ адрес, записанный в​ADDRESS​Возвращаемый тип ссылки​​ COLUMN — столбец).​​ и абсолютная. Это​ Excel. На простых​

​ ссылки не содержит​ от определенных условий.​ без расширения XLAM).​ ошибок), ее можно​ выражение, используемое в​), представляют собой заполнители​редактор Visual Basic (VBE)​ а затем — клавишу​

​ заданном аргументом «ссылка»,​ отобразить результаты формул,​(СТОЛБЕЦ), которая определяет​ стиле ссылок​(АДРЕС). Она возвращает​1 или опущен​ Если аргумент А1​

​ позволяет нам скопировать​ примерах и далее​ знака «$». Чтобы​

1. ​ Для работы с​​Дважды щелкните модуль в​​ выбрать в диалоговом​​ другом макросе или​​ для значений, на​, который открывается в​ ВВОД. При необходимости​ из которого требуется​​ выделите их и​​ номер столбца.​​A1​​ адрес ячейки в​Абсолютный​ имеет значение ИСТИНА​ формулу без ошибок​

2. ​ будем рассматривать практическое​ отличать абсолютные и​ актуальными данными в​

   ​ Project Explorer, чтобы​
​ окне "Вставка функции".​ функции VBA. Так,​
​ основе которых вычисляется​ отдельном окне.​ измените ширину столбцов,​
​ возвратить ссылку.​
​ нажмите клавишу F2,​
​=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))​
​.​ текстовом формате, используя​
​2​


​ или 1 или​​ в результатах ее​ применение функций.​ относительные ссылки используются​ таблице, которая находится​​ вывести код функций.​​ Пользовательские функции доступны​ пользовательские функции не​ скидка.​Предположим, что ваша компания​ чтобы видеть все​Номер_столбца​ а затем — клавишу​=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))​=ADDRESS($C$2,$C$3)​ номер строки и​​Абсолютная строка; относительный столбец​​ опущен, то функция​

Применение пользовательских функций

​ вычисления.​Как найти среднее арифметическое​ квадратные скобки «[]».​ на другом листе​ Чтобы добавить новую​ в категории «Определенные​

​ могут изменять размер​

​Оператор If в следующем​ предоставляет скидку в​ данные.​    — необязательный аргумент. Номер​ ВВОД. При необходимости​

​Урок подготовлен для Вас​=АДРЕС($C$2;$C$3)​ столбца. Нужен ли​3​ АДРЕС() возвращает ссылку​​Примечание. Закладка «Формулы» предоставляет​​ число в Excel?​​ Например, если в​​ документа требуется получить​ функцию, установите точку​ пользователем»:​ окон, формулы в​ блоке кода проверяет​ размере 10 % клиентам,​​Фрукты​​ столбца в диапазоне,​ измените ширину столбцов,​​ командой сайта office-guru.ru​​Если не указывать значение​ нам этот адрес?​Относительная строка; абсолютный столбец​ типа А1; если​ доступ только к​

​ Создайте таблицу, так​ данном примере в​ ссылку на данную​​ вставки после оператора​​Чтобы упростить доступ к​​ ячейках, а также​​ аргумент​ заказавшим более 100​Цена​ заданном аргументом «ссылка»,​ чтобы видеть все​Источник: http://blog.contextures.com/archives/2011/01/21/30-excel-functions-in-30-days-20-address/​​ аргумента​​ Можно ли сделать​​4​​ этот аргумент имеет​ наиболее часто используемым​ как показано на​ качестве параметра тип_ссылки​

​ ячейку.​ End Function, который​ пользовательским функциям, можно​​ шрифт, цвет или​​quantity​ единиц товара. Ниже​Количество​

​ из которого требуется​ данные.​
​Перевел: Антон Андронов​abs_num​ то же самое​
​Относительный​
​ значение ЛОЖЬ (или​
​ функциям. Больше можно​


​ рисунке:​ указать число 4,​На листе «Курсы» создана​ завершает последнюю функцию​ определить их в​​ узор для текста​​и сравнивает количество​​ мы объясним, как​​Яблоки​ возвратить ссылку.​

​Данные​Автор: Антон Андронов​

​(тип_ссылки) в формуле,​ с помощью других​​A1​​ 0), функция АДРЕС()​ получить, вызвав окно​В ячейках D5 и​​ ссылка на ячейку​​ таблица с актуальными​ в окне кода,​ отдельной книге, а​ в ячейке. Если​ проданных товаров со​ создать функцию для​0,69 ₽​​Номер_области​​Данные​Функция ИНДЕКС возвращает значение​ то результатом будет​ функций?​    Необязательный аргумент. Логическое значение, которое​ возвращает ссылку типа​ «Мастер функций» нажав​

​ E5 введем функции,​​ примет следующий вид:​​ курсами валют:​ и начните ввод.​

​ затем сохранить ее​

​ включить в процедуру​ значением 100:​​ расчета такой скидки.​​40​    — необязательный аргумент. Диапазон​

​Яблоки​

​ или ссылку на​ абсолютная ссылка.​Давайте обратимся к сведениям​ определяет тип ссылок:​ R1C1.​ на кнопку «Вставить​ которые помогут получить​Так выглядит абсолютный тип​На отдельном листе «Цены»​ Вы можете создать​ как надстройку, которую​​ функции код для​​If quantity >= 100​В примере ниже показана​Бананы​ в аргументе «ссылка»,​Лимоны​ значение из таблицы​

Правила создания пользовательских функций

​Чтобы увидеть адрес в​ по функции​ А1 или R1C1.​Чтобы изменить тип ссылок,​ функцию» вначале строки​ среднее значение оценок​ ссылок по строкам​ создана таблица с​ любое количество функций,​ можно включать при​ таких действий, возникнет​ Then​ форма заказа, в​0,34 ₽​ из которого требуется​

​Бананы​ или диапазона.​ виде относительной ссылки,​ADDRESS​ При использовании ссылок​ используемый Microsoft Excel,​ формул. Или нажать​ успеваемости по урокам​ и столбцам при​ товарами, отображающая стоимость​ и они будут​ каждом запуске Excel.​ ошибка #ЗНАЧ!​DISCOUNT = quantity​ которой перечислены товары,​

Применение ключевых слов VBA в пользовательских функциях

​38​ возвратить значение ячейки​Груши​Функцию ИНДЕКС можно использовать​ можно подставить в​(АДРЕС) и изучим​ типа А1 столбцы​ нажмите кнопку​ комбинацию клавиш SHIFT+F3.​ Английского и Математики.​ использовании стиля R1C1.​ в долларах США​ всегда доступны в​ Вот как это​Единственное действие, которое может​ * price *​ их количество и​Лимоны​ на пересечении строки​Формула​ двумя способами:​

​ качестве аргумента​ примеры работы с​ обозначаются буквами, а​Microsoft Office​Функция =ОКРУГЛ() более​ Ячейка E4 не​Пытаюсь улучшить знания в​ (USD):​ категории «Определенные пользователем»​​ сделать:​​ выполнять процедура функции​ 0.1​​ цена, скидка (если​​0,55 ₽​ и столбца, заданных​Описание​Если вы хотите возвращать​abs_num​​ ней. Если у​​ строки — цифрами.​, затем нажмите кнопку​ точна и полезнее​

Документирование макросов и пользовательских функций

​ имеет значения, поэтому​ работе Экселя, Объясните​В ячейку D3 поместим​ диалогового окна​Windows macOS ​ (кроме вычислений), —​Else​ она предоставляется) и​15​ аргументами «номер_строки» и​Результат​ значение указанной ячейки​(тип_ссылки) значение​ Вас есть дополнительная​ При использовании ссылок​Параметры Excel​ чем округление с​ результат будет вычислен​ русским языком, зачем​

​ ссылку на ячейку​Вставка функции​Создав нужные функции, выберите​ это отображение диалогового​DISCOUNT = 0​ итоговая стоимость.​Апельсины​ «номер_столбца». Первая введенная​=ИНДЕКС(A2:B3;2;2)​ или массива ячеек,​4​ информация или примеры,​ типа R1C1 и​(внизу окна) и​ помощью формата ячеек.​ из 2 оценок.​ нужна эта функция?​

​ таблицы, находящейся на​.​Файл​ окна. Чтобы получить​End If​​Чтобы создать пользовательскую функцию​​0,25 ₽​​ или выделенная область​​Значение ячейки на пересечении​ см. раздел Форма​.​ пожалуйста, делитесь ими​ столбцы, и строки​ выберите пункт​ В этом легко​

​Перейдите в ячейку D5.​ Спасибо​ листе «Курсы», в​Эта статья основана на​>​ значение от пользователя,​Если количество проданных товаров​

Предоставление доступа к пользовательским функциям

​ DISCOUNT в этой​25​ имеет номер 1, вторая —​ второй строки и​ массива.​=ADDRESS($C$2,$C$3,4)​ в комментариях.​ обозначаются цифрами. Если​Формулы​ убедиться на практике.​Выберите инструмент из выпадающего​Сергей​ которой содержится информация​ главе книги​Сохранить как​ выполняющего функцию, можно​ не меньше 100,​ книге, сделайте следующее:​Груши​​ 2 и т. д.​​ второго столбца в​​Если требуется возвращать ссылку​​=АДРЕС($C$2;$C$3;4)​

​Функция​ аргумент А1 имеет​. В группе​Создайте исходную таблицу так​ списка: «Главная»-«Сумма»-«Среднее». В​: Нужна, если какая-то​ о курсе валюты​Microsoft Office Excel 2007​

​.​ использовать в ней​ VBA выполняет следующую​Нажмите клавиши​0,59 ₽​ Если аргумент «номер_области»​ диапазоне A2:B3.​ на указанные ячейки,​Чтобы задать стиль ссылок​ADDRESS​

​ значение ИСТИНА или​

1. ​Работа с формулами​​ как показано на​​ данном выпадающем списке​​ числовая формула или​​ USD. Для этого​

   ​ Inside Out​​В Excel 2007 нажмите​​ оператор​​ инструкцию, которая перемножает​​ALT+F11​

2. ​40​​ опущен, в функции​​Груши​​ см. раздел Ссылочная​​R1C1​​(АДРЕС) возвращает ссылку​​ опущен, то функция​установите или снимите​ рисунке:​​ находятся часто используемые​​ какой-то внешний источник​​ введем следующую формулу:​​, написанной Марком Доджем​кнопку Microsoft Office​InputBox​​ значения​​(или​Миндаль​ ИНДЕКС используется область 1. ​

3. ​=ИНДЕКС(A2:B3;2;1)​​ форма.​​, вместо принятого по​​ на ячейку в​​АДРЕС​

   ​ флажок​​Ячейку B2 отформатируйте так,​​ математические функции.​​ выдаёт номер колонки​​ =АДРЕС(3;2;1;1;»Курсы»).​

4. ​ (Mark Dodge) и​​, а затем щелкните​​. Кроме того, с​​quantity​​FN+ALT+F11​

5. ​2,80 ₽​​ Все указанные здесь​​Значение ячейки на пересечении​​Возвращает значение элемента таблицы​​ умолчанию стиля​​ виде текста, основываясь​​возвращает ссылку типа​

6. ​Стиль ссылок R1C1​​ чтобы были отображены​​Диапазон определяется автоматически, остается​ или строки, и​Значение параметров:​

   

1. ​ Крейгом Стинсоном (Craig​​Сохранить как​​ помощью оператора​​и​​на Mac), чтобы​

2. ​10​​ области должны находиться​​ второй строки и​​ или массив, заданного​​A1​​ на номере строки​​ А1; если этот​.​ только 2 знака​​ только нажать Enter.​​ надо это число​

3. ​3 – номер строки,​​ Stinson). В нее​​.​​MsgBox​​price​

4. ​ открыть редактор Visual​​Кешью​​ на одном листе. ​ первого столбца в​ номером строки и​​, Вы должны указать​​ и столбца. Она​ аргумент имеет значение​[Имя_листа]​​ после запятой. Это​​Функцию, которую теперь содержит​

​ преобразовать в адрес,​ в которой содержится​ были добавлены сведения,​В диалоговом окне​можно выводить сведения​, а затем умножает​ Basic, а затем​3,55 ₽​ Если указать области,​ диапазоне A2:B3.​ номером столбца.​ значение FALSE (ЛОЖЬ)​ может возвращать абсолютный​ ЛОЖЬ, функция​    Необязательный аргумент. Текстовое значение,​

​ можно сделать с​ ячейка D5, скопируйте​ который можно использовать​ искомая ячейка;​ относящиеся к более​Сохранить как​ для пользователей. Вы​ результат на 0,1:​ щелкните​16​ находящиеся на разных​Бананы​Если первый аргумент функции​ для аргумента​ или относительный адрес​АДРЕС​​ определяющее имя листа,​​ помощью диалогового окна​

Об авторах

​ в ячейку E5.​ для обращения к​​2 – номер столбца​ поздним версиям Excel.​​откройте раскрывающийся список​ также можете использовать​Discount = quantity *​Insert​Арахис​ листах, произойдет ошибка​В этом примере функция​

support.office.com

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

​ ИНДЕКС является константной​а1​ в стиле ссылок​возвращает ссылку типа​ которое используется для​ «Формат ячеек» или​Функция среднее значение в​ соответствующей ячейке.​ с искомой ячейкой;​

​Функция АДРЕС возвращает адрес​Тип файла​ настраиваемые диалоговые окна​ price * 0.1​(Вставка) >​1,25 ₽​ #ЗНАЧ!  Если необходимо​ ИНДЕКС используется в​

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

​ массива, используйте форму​.​

​A1​

​ R1C1.​ формирования внешней ссылки.​ инструментом расположенном на​

​ Excel: =СРЗНАЧ() в​

• ​Например, можно сделать​1 – тип ссылки​ определенной ячейки (текстовое​и выберите значение​
• ​ (​Результат хранится в виде​Module​20​
• ​ использовать диапазоны, находящиеся​ формуле массива для​ массива.​=ADDRESS($C$2,$C$3,1,FALSE)​или​Примечание:​

1. ​ Например, формула =АДРЕС(1;1;;;»Лист2″)​ закладке «Главная»-«Уменьшить разрядность»​
2. ​ ячейке E5 игнорирует​ ячейку, в которую​
3. ​ – абсолютная;​ значение), на которую​
4. ​Надстройка Excel​UserForms​

• ​ переменной​(Модуль). В правой​Грецкие орехи​ на разных листах,​
• ​ поиска значений двух​ИНДЕКС(массив; номер_строки; [номер_столбца])​=АДРЕС($C$2;$C$3;1;ЛОЖЬ)​R1C1​ Чтобы изменить тип ссылок,​

​ возвращает значение Лист2!$A$1.​

1. ​В столбце C проставьте​ текст. Так же​ вводишь вручную просто​1 – выбор стиля​ указывают номера столбца​. Сохраните книгу с​), но эта тема​Discount​ части редактора Visual​1,75 ₽​
2. ​ рекомендуется применить функцию​ заданных ячеек в​Аргументы функции ИНДЕКС описаны​Последний аргумент – это​. К тому же​ используемый Microsoft Excel,​Как видно из рисунка​ формулу вычитания 1,25​ она проигнорирует пустую​
3. ​ одно число, а​ ссылок с буквенно-цифровой​ и строки. К​ запоминающимся именем, таким​ выходит за рамки​. Оператор VBA, который​ Basic появится окно​12​ ИНДЕКС в форме​

​ массиве с диапазоном​

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

​ ниже.​ имя листа. Если​ в результат может​ откройте вкладку​ ниже (см. файл​ из значений столбца​ ячейку. Но если​ формула берёт значение​ записью;​ примеру, в результате​ как​ данной статьи.​

​ хранит значение в​ нового модуля.​Формула​

​ массива и использовать​ 2 x 2.​Массив​ Вам необходимо это​ быть включено имя​

​Файл​ примера) функция АДРЕС()​ B: =B-1,25.​ в ячейке будет​ из строки некоторой​«Курсы» — название листа,​ выполнения функции =АДРЕС(5;7)​MyFunctions​Даже простые макросы и​

​ переменной, называется оператором​

• ​Скопируйте указанный ниже код​Описание​ другую функцию для​
• ​В этом случае нужно​    — обязательный аргумент. Диапазон​
• ​ имя в полученном​ листа.​
• ​, а затем нажмите​ возвращает адрес ячейки​Теперь вы знаете, как​
• ​ значение 0, то​ таблицы.​ на котором находится​ будет выведено значение​

​, в папке​ пользовательские функции может​назначения​

​ и вставьте его​Результат​ вычисления диапазона, составляющего​ выбрать любые две​ ячеек или константа​ результате, укажите его​Функция​ кнопку​

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

​ во всевозможных форматах.​ пользоваться функцией ОКРУГЛ​ результат естественно измениться.​Полосатый жираф алик​ таблица с искомой​ $G$5.​AddIns​ быть сложно понять.​, так как он​

​ в новый модуль.​=ИНДЕКС(A2:C6;2;3)​

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

​ADDRESS​Параметры​

• ​Чаще всего адрес ячейки​ в Excel.​В Excel еще существует​: А тут какой​ ячейкой.​Примечание: наличие символов «$»​. Она будет автоматически​ Чтобы сделать эту​ вычисляет выражение справа​Function DISCOUNT(quantity, price)​Пересечение второй строки и​ используемый диапазон можно​ например A1:A2, вставить​
• ​Если массив содержит только​sheet_text​(АДРЕС) может возвратить​

​и выберите пункт​ требуется, чтобы вывести​Описание аргументов функции =ОКРУГЛ():​ функция =СРЗНАЧА() –​

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

​ язык?​Для расчета стоимости в​ в адресе ячейки​ предложена в диалоговом​ задачу проще, добавьте​ от знака равенства​If quantity >=100​ третьего столбца в​

​ с помощью функции​ в первую ячейку​

​ одну строку или​(имя_листа).​ адрес ячейки или​

​Формулы​

• ​ значение ячейки. Для​Первый аргумент – это​
• ​ среднее значение арифметическое​Создает адрес ячейки​ рублях используем формулу:​
• ​ $G$5 свидетельствует о​ окне​
• ​ комментарии с пояснениями.​ и назначает результат​

​ Then​

​ диапазоне A2:C6, т. е.​ ВЫБОР.​ формулу =ИНДЕКС({1;2:3;4};0;2) и​ один столбец, соответствующий​=ADDRESS($C$2,$C$3,1,TRUE,»Ex02″)​

​ работать в сочетании​. В группе​ этого используется другая​ ссылка на ячейку​ число. Она отличается​ в виде текста,​ =B3*ДВССЫЛ(D3).​ том, что ссылка​Сохранить как​ Для этого нужно​ имени переменной слева​DISCOUNT = quantity​ содержимое ячейки C3.​

​Например, если аргумент «ссылка»​ нажать клавиши CTRL+SHIFT+ВВОД.​ аргумент «номер_строки» или​=АДРЕС($C$2;$C$3;1;ИСТИНА;»Ex02″)​

exceltable.com

Для чего нужна функция АДРЕС в Exel?

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

​ используя номер строки​​Функция ДВССЫЛ необходима для​ на данную ячейку​, поэтому вам потребуется​ ввести перед текстом​ от него. Так​ * price *​38​ определяет ячейки (A1:B4,D1:E4,G1:H4),​ Если ввести формулу​ «номер_столбца» не является​
​Функция​ чтобы:​установите или снимите​Формула =ДВССЫЛ(АДРЕС(6;5)) просто выведет​ округлить.​ что:​ и номер​

​ получения числового значения,​​ является абсолютной, то​ только принять расположение,​
​ апостроф. Например, ниже​ как переменная​ 0.1​=ИНДЕКС((A1:C6;A8:C11);2;2;2)​
​ номер области 1​
​ как формулу массива,​
​ обязательным.​ADDRESS​
​Получить адрес ячейки, зная​ флажок​ значение из 6-й​
​Второй аргумент – это​=СРЗНАЧ() – пропускает ячейки​столбца.​
​ хранимого в ячейке,​ есть не меняется​ используемое по умолчанию.​
​ показана функция DISCOUNT​Discount​Else​Пересечение второй строки и​ соответствует диапазону A1:B4,​ Excel автоматически вставит​Если массив содержит больше​(АДРЕС) возвращает адрес​ номер строки и​Стиль ссылок R1C1​ строки 5 столбца​ количество знаков после​ которые не содержат​
​Синтаксис​ на которую указывает​ при копировании данных.​
​Сохранив книгу, выберите​ с комментариями. Благодаря​называется так же,​DISCOUNT = 0​ второго столбца во​

​ номер области 2​​ ее во вторую​ одной строки и​
​ ячейки в виде​ столбца.​.​ (Е). Эта формула​ запятой, которое нужно​ чисел;​АДРЕС (номер_строки; номер_столбца​
​ ссылка. В результате​Функция АДРЕС имеет следующую​
​Файл​
​ подобным комментариями и​ как и процедура​
​End If​ второй области (A8:C11),​ — диапазону D1:E4,​
​ ячейку.​ одного столбца, а​ текста, а не​Найти значение ячейки, зная​
​Имя_листа​ эквивалентна формуле =Е6.​ оставить после округления.​
​=СРЗНАЧА() – пропускает только​ ;тип_ссылки; а1;имя_листа)​

Работа с функциями в Excel на примерах

​ вычислений для остальных​ синтаксическую запись:​>​ вам, и другим​ функции, значение, хранящееся​DISCOUNT = Application.Round(Discount,​ т. е. содержимое ячейки​ а номер области​Формула​

​ из аргументов «номер_строки»​ как действующую ссылку.​ номер строки и​    Необязательный аргумент. Текстовое значение,​Возникает вопрос: «Зачем весь​Внимание! Форматирование ячеек только​ пустые ячейки, а​Номер_строки — это​

Функция вычисления среднего числа

​ товаров получим следующую​=АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])​Параметры Excel​ будет впоследствии проще​ в переменной, возвращается​

​ 2)​ B9.​ 3 — диапазону​Описание​ и «номер_столбца» задан​ Если Вам нужно​ столбца.​ определяющее имя листа,​ этот огород с​ отображает округление но​

1. ​ текстовые значения воспринимает​
2. ​ номер строки, используемый​ таблицу:​Первые два аргумента данной​.​ работать с кодом​
3. ​ в формулу листа,​End Function​
4. ​1,25​ G1:H4.​Результат​

​ только один, функция​ получить значение ячейки,​Возвратить адрес ячейки с​ которое используется для​ функцией АДРЕС()?». Дело​ не изменяет значение,​ как 0.​ в ссылке ячейки.​Пример 2. В таблице​

​ функции являются обязательными​В Excel 2007 нажмите​ VBA. Так, код​ из которой была​Примечание:​=СУММ(ИНДЕКС(A1:C11;0;3;1))​

• ​После того как с​=ИНДЕКС({1;2​ ИНДЕКС возвращает массив,​
• ​ можно использовать результат,​ самым большим значением.​ формирования внешней ссылки.​ в том, что​

​ а =ОКРУГЛ() –​

Функция подсчета количества значений в Excel

1. ​​Номер_столбца — это​ содержатся данные о​ для заполнения.​кнопку Microsoft Office​ будет легче понять,​ вызвана функция DISCOUNT.​
2. ​ Чтобы код было более​Сумма третьего столбца в​ помощью аргументов «ссылка»​:​
3. ​ состоящий из целой​ возвращаемый функцией​Функция​ Например, формула​ существуют определенные задачи,​ округляет значение. Поэтому​В ячейки D6 и​
4. ​ номер столбца, используемый​ цене товаров, отсортированные​Описание аргументов:​и щелкните​ если потребуется внести​Если значение​

​ удобно читать, можно​ первой области диапазона​ и «номер_области» выбран​3;4};0;2)​ строки или целого​

Функция ЕСЛИ в Excel

​ADDRESS​ADDRESS​=АДРЕС(1;1;;;»Лист2″)​ в которых использование​ для вычислений и​ E6 нашей таблицы​ в ссылке ячейки.​

1. ​ в порядке возрастания​Номер_строки – числовое значение,​Параметры Excel​
2. ​ в него изменения.​quantity​ добавлять отступы строк​ (A1:C11) является суммой​ диапазон, с помощью​Значение ячейки на пересечении​ столбца аргумента «массив».​
3. ​(АДРЕС), как аргумент​(АДРЕС) имеет вот​

​возвращает значение​ функции АДРЕС() очень​ расчетов нужно использовать​ введите функцию подсчета​Тип_ссылки — это​ стоимости. Необходимо получить​ соответствующее номеру строки,​.​Апостроф указывает приложению Excel​меньше 100, VBA​ с помощью клавиши​ диапазона C1:C11.​ аргументов «номер_строки» и​ первой строки и​Номер_строки​

​ для​ такой синтаксис:​Лист2!$A$1​ удобно, например Транспонирование​ функцию =ОКРУГЛ(), так​ количества числовых значений.​ задание типа возвращаемой​ ссылки на ячейки​

​ в которой находится​В диалоговом окне​ на то, что​ выполняет следующий оператор:​TAB​216​ «номер_столбца» выбирается конкретная​ второго столбца в​     — обязательный аргумент.​INDIRECT​

Как округлять числа в Excel

​ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])​. Если аргумент​ таблиц или Нумерация​ как форматирование ячеек​ Функция позволит нам​ ссылки.​

1. ​ с минимальной и​ требуемая ячейка;​Параметры Excel​
2. ​ следует игнорировать всю​Discount = 0​. Отступы необязательны и​=СУММ(B2:ИНДЕКС(A2:C6;5;2))​ ячейка: номер строки​ массиве. Массив содержит​ Выбирает строку в​(ДВССЫЛ). Мы изучим​АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])​
3. ​имя_листа​ столбцов буквами или​ приведет к ошибочным​ узнать количество поставленных​

​А1 — логическое​ максимальной стоимостью товаров​Номер_столбца – числовое значение,​

​выберите категорию​

1. ​ строку справа от​Наконец, следующий оператор округляет​ не влияют на​Сумма значений из диапазона,​
2. ​ 1 соответствует первой​ значения 1 и​ массиве, из которой​ функцию​

​abs_num​отсутствует, имя листа​ Поиск позиции ТЕКСТа​ значениям в результатах.​ оценок.​ значение, которое определяет​ соответственно.​ которое соответствует номеру​Надстройки​ него, поэтому вы​ значение, назначенное переменной​

exceltable.com

​ выполнение кода. Если​

Вчера в марафоне 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.

=ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)

Абсолютная или относительная

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

Чтобы увидеть адрес в виде относительной ссылки, можно подставить в качестве аргумента abs_num (тип_ссылки) значение 4.

=ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)

A1 или R1C1

Чтобы задать стиль ссылок R1C1, вместо принятого по умолчанию стиля A1, Вы должны указать значение FALSE (ЛОЖЬ) для аргумента а1.

=ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)

Название листа

Последний аргумент – это имя листа. Если Вам необходимо это имя в полученном результате, укажите его в качестве аргумента sheet_text (имя_листа).

=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")

Пример 2: Находим значение ячейки, используя номер строки и столбца

Функция ADDRESS (АДРЕС) возвращает адрес ячейки в виде текста, а не как действующую ссылку. Если Вам нужно получить значение ячейки, можно использовать результат, возвращаемый функцией ADDRESS (АДРЕС), как аргумент для INDIRECT (ДВССЫЛ). Мы изучим функцию INDIRECT (ДВССЫЛ) позже в рамках марафона 30 функций Excel за 30 дней.

=INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))

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

=INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)

Функция INDEX (ИНДЕКС) также может вернуть значение ячейки, если указан номер строки и столбца:

=INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)

1:5000 – это первые 5000 строк листа Excel.

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

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

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

=MAX(C3:C8)
=МАКС(C3:C8)

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

=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))

Описание

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

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

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

Описание

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

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

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

Аргументы

Примечания:

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

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

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

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

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

1) Если вы просто введите аргументы строки и столбца в функцию,

A2 и B2 – это числовые значения строки и столбца, и она вернет
$ A $ 1

2) Если вы введете строку , аргументы столбца и abs в функции,

C3 – аргументы abs, 2 указывает на отображение ссылки в виде относительной строки и абсолютного столбца, и возвращает
A $ 1

3) Если Четвертый аргумент вводится в функцию,

D4 управляет стилем ссылки, A1 или R1C1, 0 или False будет отображать результат в стиле A1, 1 или True покажет результат в стиле R1C1, здесь он вернет
R1C1

4) Если в функцию введены все аргументы,

E6 – пятый аргумент, который указывает на лист, на который ссылается, он возвращает
BasicUsage! $ A1

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

Функция возвращает адрес ячейки в виде текста. Если вы хотите отобразить значение ячейки в адресе ячейки, вы можете объединить функцию АДРЕС и функцию для достижения этой цели.

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

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

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

Во-первых, вам нужно получить максимальное значение с помощью этой формулы .

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

найдет номер строки, E1 – это e max value, B1: B6 – это столбец, из которого вы найдете максимальное значение;

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

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

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

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

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

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

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

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

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

Образец файла