Как получить ссылку на ячейку по значению в excel

Обновлено: 30.06.2024

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

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

АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [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 .

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

Функция INDIRECT (ДВССЫЛ) в Excel используется когда у вас есть ссылки в виде текста, и вы хотите получить значения из этих ссылок.

Что возвращает функция

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

Синтаксис

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

Дополнительная информация

Примеры использования функции ДВССЫЛ в Excel

Пример 1. Используем ссылку на ячейку для получения значения

Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):

Формула в ячейке С1:

Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.

Если в данном случае вы введете в ячейку С1 формулу “=A1” или “=$A$1”, то она выдаст вам тот же результат, что находится в ячейке А1. Но если вы вставите в таблице строку выше, вы можете заметить, что ссылка на ячейку будет автоматически изменена.

Функция очень полезна, если вы хотите заблокировать ссылку на ячейку таким образом, чтобы она не изменялась при вставке строк / столбцов в рабочий лист.

Пример 2. Получаем данные по ссылке на ячейку

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

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

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

Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)

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

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

Пример 4. Ссылаемся на диапазон ячеек с помощью функции INDIRECT (ДВССЫЛ)

С помощью функции вы можете ссылаться на диапазон ячеек.

Используя функцию SUM (СУММ) и INDIRECT (ДВССЫЛ) вместе, вы можете рассчитать сумму, а также максимальные и минимальные значения диапазона.

Пример 5. Ссылаемся на именованный диапазон значений с использованием функции INDIRECT (ДВССЫЛ)

Если вы создали именованный диапазон в Excel, вы можете обратиться к нему с помощью функции INDIRECT (ДВССЫЛ) .

Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:

Зададим для следующих ячеек названия:

B2:B6: Математика
C2:C6: Физика
D2:D6: Химия

Для того чтобы задать название любому диапазону значений, достаточно выбрать ячейки и присвоить им название в соответствующем боксе:

После этого вы можете обратиться к указанным диапазонам с помощью формулы:

Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)

C помощью этой функции вы можете создавать зависимый выпадающий список.

Теперь, для создания зависимого выпадающего списка:

Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
Кликните по вкладке “Data” -> “Data Validation”;
На вкладке “Настройки” в разделе “Allow” выберите List;
В разделе “Source” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2) ;
Нажмите ОК

При использовании формул поиска в Excel (таких как VLOOKUP, XLOOKUP или INDEX / MATCH) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке / столбце) в качестве результата.

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

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

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

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

Функция ADDRESS в MS Excel предназначена именно для этого. Она берет строку и номер столбца и дает вам адрес ячейки этой конкретной ячейки.

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

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

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

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

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

В приведенной выше формуле я использовал функцию MATCH, чтобы узнать номер строки, содержащей данный идентификатор сотрудника. И поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.

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

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

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

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

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

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

Это называется функцией CELL (и она может дать вам гораздо больше информации о ячейке, чем функция ADDRESS ).

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

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

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

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

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

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

Я использовал формулу INDEX в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.

А затем просто обернул его в функцию CELL и попросил вернуть адрес ячейки с этим значением, которое я получаю из формулы INDEX.

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

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

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

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

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

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

Всем привет! Сегодня начинаем разбирать функцию ДВССЫЛ (INDIRECT) в Excel.

Сама по себе, функция очень проста - она превращает текст в ссылку. Например, две нижеследующие формулы аналогичны:

=ДВССЫЛ (A1) (оно же в англоязычном интерфейсе INDIRECT (A1))

После такого описания может показаться, что функция ДВССЫЛ - это какой-то атавизм из древних версий Excel, ведь, если можно простым способом обращаться к ячейке, то зачем его усложнять? Но это типичное заблуждение, с которым я и сам когда-то столкнулся на начальном этапе изучения Excel. На самом деле, функция достаточно интересная - в некоторых случаях она может заметно облегчить жизнь, хоть многие и считают, что использовать ДВССЫЛ - это моветон:)

Начнем с простого. Синтаксис функции :

ссылка_на_текст. Обязательный аргумент, в котором указывается ссылка на ячейку в формате текста. Это может быть ссылка, на ячейку с этим текстом, например, =ДВССЫЛ(A1). Или это может быть сразу текст, например, так: =ДВССЫЛ("A1") - помним, что текст в формулах прописывается в кавычках.
[a1]. Необязательный аргумент для определения типа ссылки. Если имеет значение ИСТИНА (TRUE) или опущен, то ссылка на текст воспринимается, как ссылка типа A1. Если указано значение ЛОЖЬ (FALSE), то воспринимается, как ссылка в стиле R1C1.

Посмотрим, как это работает.

1. Классический способ применения функции ДВССЫЛ (INDIRECT)

Имеем таблицу с наименованием городов и численностью их населения. Задача: сослаться на ячейку с численностью - пусть это будет ячейка. B2. Наши действия:

привычный способ - обратиться через знак ровно: =B2
через ДВССЫЛ эта же формула будет выглядеть так: ДВССЫЛ("B2"). Помним, что в кавычках указывается текст, поэтому функция сразу воспринимает указанное, как адрес необходимой ячейки.
если в файле в отдельной ячейке указана текстом ссылка на ячейку B2 (в нашем примере это значение прописано в H2), то функция будет выглядеть следующим образом: =ДВССЫЛ(H2). Здесь мы не указываем ковычки, поэтому функция понимает, что адрес необходимой ячейки прописан в ячейке H2. Иллюстрация ниже.

Excel. Пример прямого использования функции ДВССЫЛ (INDIRECT) Excel. Пример прямого использования функции ДВССЫЛ (INDIRECT)

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

2. Транспонирование данных с помощью функции ДВССЫЛ (INDIRECT)

Задача: имеем вертикальный диапазон с названиями, но хотим преобразовать его в горизонтальный.

ДВССЫЛ в данном случае не является классическим решением задачи, но тоже с ней справится. Для этого нам нужно прописать такую формулу, которая при горизонтальном протягивании будет каждый раз ссылаться на ячейку ниже. Можно было бы прописывать руками номер строки, но в примере ниже мы воспользуемся функцией СТОЛБЕЦ (COLUMN), которая вернет номер столбца текущей ячейки. В моем примере они совпадут - данные расположены удобно, чтобы не перегружать формулу лишними вычислениями для вашей наглядности (исходник начинается со 2й строки, транспонирую в диапазон, который начинается со 2го столбца). На практике может потребоваться скорректировать полученный результат фунции СТОЛБЕЦ, например, вычитанием разницы.

Excel. Пример использования функции ДВССЫЛ (INDIRECT) для транспонирования данных Excel. Пример использования функции ДВССЫЛ (INDIRECT) для транспонирования данных

3. Зависимый выпадающий список

Задача: при заполнении отчета необходимо, чтобы в выпадающем списке были не все значения, а лишь те, которые соответствуют какому-то ранее указанному признаку. Например, имеем три филиала с названиями Московский, Тверской, Тульский - каждый филиал отвечает за определенные города, находящиеся поблизости >> хотим, чтобы в отчете при выборе московского филиала в выпадающем списке появлялись только его города.

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