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

Содержание

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

97082
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)


 

Ячейка и ее адрес.

Excel. Мультимедийный курс

Ячейка и ее адрес

Лист книги состоит из ячеек. Ячейка – это прямоугольная область листа. Щелкните кнопкой мыши на любом участке листа, в этом месте будет выделена прямоугольная область (обведена жирной рамкой). Вы выделили отдельную ячейку.

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

Обратите внимание на поле, расположенное слева от строки формул. В данном поле указан адрес выделенной ячейки. Что представляет собой адрес? Адрес – это координаты ячейки, определяемые столбцом и строкой, в которых эта ячейка находится. Столбцы и строки имеют нумерацию (цифровую или буквенную). Адрес ячейки может быть представлен в двух форматах. По умолчанию используется числовая нумерация строк и буквенный индекс столбцов. Так, если ячейка находится в девятой строке столбца G, то адрес этой ячейки – G9. Если ячейка расположена в третьей строке столбца B, адресом ячейки будет B3. Все очень просто, помните знаменитую игру «Морской бой»?

Однако формат адреса ячейки можно изменить. Рассмотрим, что это за формат.


1. Нажмите Кнопку «Office».

2. В появившемся меню нажмите кнопку Параметры Excel.

3. В открывшемся диалоговом окне щелкните кнопкой мыши на строке Формулы в списке, расположенном в левой части окна. Содержимое диалогового окна изменится.

4. Установите флажок Стиль ссылок R1C1, после чего нажмите кнопку ОК, чтобы применить изменения.

Обратите внимание, теперь и столбцы, и строки пронумерованы цифрами. Каким же образом указываются координаты ячейки в данном случае? Выделите любую ячейку и посмотрите на поле Имя (слева от строки формул). Теперь адрес ячеек выглядит как RXCY, где X – это номер строки, аY– номер столбца; R – это первая буква слова Row (Строка), а C – Column (Столбец). Иными словами, если ячейка имеет адрес R4C7, значит, эта ячейка находится в четвертой строке седьмого столбца. Как видите, и здесь все просто. При создании сложных таблиц с различными перекрестными ссылками часто используют именно такой формат адресов ячеек.

Для чего нужен адрес ячейки? В первую очередь для того, чтобы ячейка могла быть источником данных для формул в других ячейках. О формулах мы будем говорить ниже, но, забегая вперед, поясню. Допустим, в ячейке R1C3 указана формула =R1C1+R1C2. Как только вы введете в ячейки R1C1 и R1C2 числа, результат сложения этих чисел отобразится в ячейке R1C3, то есть формула в ячейке R1C3 использует в качестве переменных значения, указанные в ячейках R1C1 и R1C2.

Адрес ячейки может быть абсолютным или относительным. В абсолютном адресе (его мы только что рассмотрели) указывается ссылка на конкретную строку и конкретный столбец. Однако при создании различных формул часто используют относительный адрес, в котором указывается позиция ячейки относительно какой-либо другой (чаще всего той, в которую введена формула). Например, адрес RC[-1] означает, что ячейка находится в той же строке, но на один столбец левее, а адрес R[3]C[-2] указывает, что эта ячейка находится на три строки ниже и на два столбца левее. Таким образом, формула, которую мы рассматривали в ячейке R1C3, в относительном виде будет выглядеть так: =RC[-2]+RC[-1] (сумма содержимого ячейки, расположенной двумя столбцами левее, и ячейки, расположенной одним столбцом левее). Относительный адрес автоматически указывается в формулах, когда вы не вводите адрес ячеек, а выделяете их с помощью мыши.

Если в формуле участвует ячейка, находящаяся на другом листе, нужен дополнительный идентификатор, поскольку адреса ячеек на разных листах совпадают. Если необходимо добавить ссылку на ячейку, расположенную на другом листе, следует в начало адреса поместить имя листа и восклицательный знак (без пробелов). Например, адрес Лист3!R2C3 говорит о том, что данная ячейка находится по адресу R2C3 на листе Лист3.

На первый взгляд это может показаться сложным, но не нужно пугаться. На самом деле почти никто не указывает адреса ячеек вручную. При вводе формулы достаточно щелкнуть кнопкой мыши на нужной ячейке, и ее относительный адрес автоматически будет подставлен в формулу. Когда мы доберемся до создания формул, вы убедитесь в этом.

Данный текст является ознакомительным фрагментом.

Адрес отправителя и адрес получателя IPv6

Адрес отправителя и адрес получателя IPv6
IPv6-адрес отправителя обычно определяется при помощи функции bind. Но если адрес отправителя поставляется вместе с данными, это может снизить непроизводительные затраты. Этот параметр также позволяет серверу гарантировать, что

Адрес закольцовки

Адрес закольцовки
По соглашению адрес 127.0.0.1 присвоен интерфейсу закольцовки на себя (loopback interface). Все, что посылается на этот IP-адрес, получается самим узлом. Обычно этот адрес используется при тестировании клиента и сервера на одном узле. Этот адрес известен под именем

Неопределенный адрес

Неопределенный адрес
Адрес, состоящий из 32 нулевых битов, является в IPv4 неопределенным (unspecified) адресом. В пакете IPv4 он может появиться только как адрес получателя в тех пакетах, которые посланы узлом, находящимся в состоянии загрузки, когда узел еще не знает своего

Адрес закольцовки

Адрес закольцовки
Адрес IPv6 ::1, состоящий из 127 нулевых битов и единственного единичного бита, является адресом закольцовки IPv6. В API сокетов он называется in6addr_loopback или

Неопределенный адрес

Неопределенный адрес
Адрес IPv6, состоящий из 128 нулевых битов, записываемый как 0::0 или просто ::, является неопределенным адресом IPv6 (unspecified address). В пакете IPv6 он может появиться только как адрес получателя в пакетах, посланных узлом, который находится в состоянии загрузки и

Электронный адрес

Электронный адрес
Рассмотрим поподробнее адрес из нашего примера: [email protected]. Он состоит из домена почтового сервера (в данном случае это pupkin.ru) и имени адресата (vasiliy). Имя адресата не обязательно должно совпадать с вашим собственным – это любое понравившееся вам

5.18.5 Ограничения на IP-адрес

5.18.5 Ограничения на IP-адрес
Набор доступных IP-адресов существенно сокращается из-за применения специальных форматов для широковещательных рассылок и таблиц маршрутизации. Стандарт RFC 1122 Requirements for Internet Hosts — Communication Layers (Требования к хостам Интернета — уровни

5.18.6 Кольцевой адрес

5.18.6 Кольцевой адрес
Полной противоположностью широковещательной рассылке является метод, когда сообщение вообще не покидает хоста. Существует множество хостов, совмещающих функции клиента и сервера. Локальные сервер и хост взаимодействуют друг с другом через IP внутри

Строчки для комбобокса Адрес

Строчки для комбобокса Адрес
При нажатии на треугольник в комбобоксе Адрес вываливается список вводимых вами адресов.
Вы можете отредактировать этот список (добавить, удалить, изменить). Войдите в раздел: HKCUSoftwareMicrosoftInternet ExplorerTypedURLsТам присутствует список ваших адресов,

R.13.3 Адрес перегруженной функции

R. 13.3 Адрес перегруженной функции
Когда функция с некоторым именем используется без параметров, среди всех функций с таким именем в текущей области видимости выбирается единственная, которая точно соответствует назначению. Назначением может быть:• инициализируемый

Сетевой адрес для сервера

Сетевой адрес для сервера
Для связи через TCP/IP вы должны соединяться с хостом, который имеет известный адрес IP. Определение того, какой это адрес IP (или каким должен быть, если он отсутствует), зависит от вида сетевого оборудования вашей хост-машины.* Если вы находитесь в

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

Как получить постоянный IP-адрес
Простого подключения к локальной сети недостаточно – вам необходим постоянный адрес, по которому ваш компьютер можно будет найти в Сети. Обычно при подключении к выделенному каналу (в частности, через «домашнюю» сеть) вам предоставляется

13-я КОМНАТА: Ячейка, она же клетка, она же… Cell

13-я КОМНАТА: Ячейка, она же клетка, она же… Cell
У читателя, ознакомившегося с материалами рубрики «Архитектура ХХ века» в прошлом номере, могло сложиться ощущение, что в процессоростроении все мыслимое и немыслимое давным-давно изобретено и придумать что-то более

Электронный адрес

Электронный адрес
Электронный адрес имеет вид:
Ваше_Красивое_Имя@Адрес_Почтового_Сервера.
То, что мы здесь обозначили как Ваше_Красивое_Имя, — в терминах интернет-технологий называется логин. Логин может состоять из букв латинского алфавита, цифр, точек, тире и символа

14.5.1. Адрес ячейки

14.5.1. Адрес ячейки
Сначала разберемся с адресом ячейки. Все мы играли в детстве в морской бой. Принцип адресации ячеек в Excel такой же. Ячейка, которая стоит на пересечении первой колонки (имя которой А) и первого ряда (его номер 1), будет называться А1 — это и есть адрес ячейки.

Как использовать функцию АДРЕС (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 (простая формула)

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

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

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

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

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

Это руководство охватывает:

Поиск и возврат адреса ячейки с помощью функции АДРЕС

Функция АДРЕС в Excel предназначена именно для этого.

Он берет номер строки и столбца и дает вам адрес этой конкретной ячейки.

Ниже приведен синтаксис функции АДРЕС:

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

где:

  • номер_строки: номер строки ячейки, для которой вы хотите сотовый адрес
  • номер_столбца: номер столбца ячейки, для которой вы хотите адрес
  • [абс_номер]: необязательный аргумент, в котором вы можете указать, хотите ли вы, чтобы ссылка на ячейку была абсолютной, относительной или смешанной.
  • [a1]: необязательный аргумент, где вы можете указать, хотите ли вы ссылку в стиле R1C1 или стиле A1
  • [лист_текст]: необязательный аргумент, где вы можете указать, хотите ли вы добавить имя листа вместе с адресом ячейки или not

Теперь давайте возьмем пример и посмотрим, как это работает.

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

Ниже приведена формула, которая сделает это:

 =АДРЕС(ПОИСКПОЗ("KR256",A1:A20,0),3)

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

А поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.

Эта формула прекрасно работает, но у нее есть один недостаток — она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.

Это потому, что когда я указываю второй аргумент (номер столбца) равным 3, он жестко запрограммирован и не изменится.

Если я добавлю любой столбец слева от набора данных, формула будет считать 3 столбца с начала рабочего листа, а не с начала набора данных.

Итак, если у вас есть фиксированный набор данных и вам нужна простая формула, это подойдет.

Но если вам нужно, чтобы это было более надежно, используйте тот, который описан в следующем разделе.

Поиск и возврат адреса ячейки с помощью функции CELL

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

Это называется функцией ЯЧЕЙКА (и она может дать вам гораздо больше информации о ячейке, чем функция АДРЕС).

Ниже приведен синтаксис функции CELL:

 =CELL(info_type, [reference])

где:

  • info_type : информация о нужной ячейке. Это может быть адрес, номер столбца, имя файла и т. д.
  • [ссылка] : необязательный аргумент, в котором вы можете указать ссылку на ячейку, для которой вам нужна информация о ячейке.

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

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

Ниже приведена формула, которая сделает это:

 =ЯЧЕЙКА("адрес", ИНДЕКС($A$1:$D$20,MATCH("KR256",$A$1:$A$20,0),3 ))

Приведенная выше формула довольно проста.

Я использовал формулу ИНДЕКС в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.

А затем просто завернул его в функцию CELL и попросил вернуть адрес ячейки этого значения, которое я получаю из формулы ИНДЕКС.

Вот и секрет, почему это работает — формула ИНДЕКС возвращает значение поиска, когда вы даете ей все необходимые аргументы. Но в то же время он также вернет ссылку на эту результирующую ячейку.

В нашем примере формула ИНДЕКС возвращает «Продажи» в качестве результирующего значения, но в то же время вы также можете использовать ее для получения ссылки на ячейку этого значения вместо самого значения.

Обычно, когда вы вводите формулу ИНДЕКС в ячейку, она возвращает значение, потому что это то, что от нее ожидается. Но в сценариях, где требуется ссылка на ячейку, формула ИНДЕКС даст вам ссылку на ячейку.

В этом примере это именно то, что он делает.

Самое приятное в использовании этой формулы то, что она не привязана к первой ячейке рабочего листа. Это означает, что вы можете выбрать любой набор данных (который может находиться где угодно на рабочем листе), использовать формулу ИНДЕКС для регулярного поиска, и он все равно даст вам правильный адрес.

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

Читайте также: