Функция INDIRECT в Excel

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

Что делает Функция INDIRECT в Excel ?

Функция INDIRECT в Excel создает ссылку из текста.

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

Действительная ссылка на рабочий лист.

Аргументы

• ref_text — ссылка, представленная в виде текста.
• a1 — [необязательно] Логическое значение, обозначающее ссылку в стиле A1 или R1C1. По умолчанию TRUE = стиль A1.

Синтаксис

=INDIRECT(ref_text, [a1])

Примечания по использованию

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

INDIRECT принимает два аргумента: ref_text и a1. Ref_text — это текстовая строка, которая будет оцениваться как ссылка. A1 указывает стиль ссылки для входящего текстового значения. Если a1 имеет значение TRUE (значение по умолчанию), стиль — «A1». Если a1 имеет значение FALSE, стиль — «R1C1». Например:

=INDIRECT("A1") // возвращает ссылку типа =A1
=INDIRECT("R1C1",FALSE) // возвращает ссылку типа =R1C1

Цель INDIRECT на первый взгляд может показаться непонятной (т. е. зачем использовать текст, если можно просто предоставить правильную ссылку?), но существует множество ситуаций, когда возможность создавать ссылку из текста полезна, в том числе:

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

Примечание. ДВССЫЛ — это нестабильная функция, которая может вызвать проблемы с производительностью на больших или сложных листах. Используйте с осторожностью.

Пример № 1 — имя переменной рабочего листа

В примере, показанном выше, INDIRECT настроен на использование имени переменного листа, например:

=INDIRECT(B5&"!A1") // имя листа в B5 является переменным

Скопированная формула в B5 объединяет текст в B5 со строкой «!A1» и возвращает результат в INDIRECT. Затем функция  оценивает текст и преобразует его в правильную ссылку. Результаты в C5:C9 — это значения из ячейки A1 на 5 листах, перечисленных в столбце B.

Формула является динамической и реагирует на значения в столбце B. Другими словами, если в столбце B5 введено другое имя листа, возвращается значение из ячейки A1 на новом листе. Используя тот же подход, вы можете позволить пользователю выбрать имя листа с помощью  раскрывающегося списка, а затем создать ссылку на выбранный лист с помощью INDIRECT.

Примечание. Имена листов, содержащие знаки препинания или пробелы, должны быть заключены в одинарные кавычки (‘), как описано в этом примере. Это не характерно для функции INDIRECT; то же ограничение справедливо для всех формул.

Пример №2 — таблица поиска переменных

На приведенном ниже листе VLOOKUP используется для получения затрат для двух поставщиков, A и B. Используя поставщика, указанного в столбце F, VLOOKUP  автоматически  использует правильную таблицу:

Формула в G5:

=VLOOKUP(E5,INDIRECT("vendor_"&F5),2,0)

Пример №3 — Фиксированная ссылка

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

=INDIRECT("A1:A100") // не изменится

Пример №4 — именованный диапазон

Функцию ДВССЫЛ можно легко использовать с именованными диапазонами. На листе ниже есть два именованных диапазона: Группа1 (B5:B12) и Группа2 (C5:C12). Когда в ячейку F5 вводится «Группа1» или «Группа2», формула в ячейке F6 суммирует соответствующий диапазон с помощью INDIRECT следующим образом:

=SUM(INDIRECT(F5))

Значением F5 является текст, но INDIRECT преобразует текст в допустимый диапазон.

Конкретным примером этого подхода является использование именованных диапазонов для создания зависимых раскрывающихся списков.

Пример №5 – Создание числового массива

Более продвинутое использование INDIRECT — создание числового массива с помощью функции ROW следующим образом:

ROW(INDIRECT("1:10")) // create {1;2;3;4;5;6;7;8;9;10}

В этой формуле объясняется один вариант использования , который суммирует нижние n значений в диапазоне. Вы также можете столкнуться с идеей ROW + INDIRECT в более сложных формулах, в которых необходимо собрать числовой массив «на лету». Одним из примеров является эта формула, предназначенная для  удаления числовых символов из строки.

Примечания

• Ссылки, созданные INDIRECT, оцениваются в режиме реального времени, и отображается содержимое ссылки.
• Если ref_text является внешней ссылкой на другую книгу, книга должна быть открыта.
• а1 не является обязательным. Если этот параметр опущен, a1 имеет значение TRUE = ссылка на стиль A1.
• Если для a1 установлено значение FALSE, INDIRECT создаст ссылку в стиле R1C1.
• ДВССЫЛ — это нестабильная функция в эксель, которая может вызвать проблемы с производительностью на больших или сложных листах.