Функция ДВССЫЛ() в MS EXCEL

Функция ДВССЫЛ(), английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой. Например, формула =ДВССЫЛ("Лист1!B3") эквивалентна формуле =Лист1!B3. Мощь этой функции состоит в том, что саму ссылку (Лист1!B3) также можно изменять формулами, ведь для ДВССЫЛ() это просто текстовая строка! С помощью этой функции можно транспонировать таблицы, выводить значения только из четных/ нечетных строк, складывать цифры числа и многое другое.

Функция ДВССЫЛ() имеет простой синтаксис.

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

ДВССЫЛ(ссылка_на_ячейку;a1)

Ссылка_на_ячейку — это текстовая строка в формате ссылки (т.е. указаны столбец и строка): =ДВССЫЛ("B3") или =ДВССЫЛ("Лист1!B3") или =ДВССЫЛ("[Книга1.xlsx]Лист1!B3"). Первая формула эквивалентна формуле =B3, вторая - =Лист1!B3, третья =[Книга1.xlsx]Лист1!B3 Если какая-либо ячейка (например, А1) содержит текстовую строку в формате ссылки (например, Лист1!B3), то в ДВССЫЛ() можно указать ссылку на эту ячейку =ДВССЫЛ(А1) Эта запись будет эквивалентна =ДВССЫЛ("Лист1!B3"), которая в свою очередь будет эквивалентна =Лист1!B3. Зачем все это нужно - читайте ниже (см. раздел решение задач).

Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ), указывающее, какого типа ссылка содержится в аргументе Ссылка_на_ячейку.

• Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.
• Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Примечание: Формат ссылки =Лист1!B3 называется ссылкой в стиле А1, когда явно указывается адрес ячейки. Формат ссылки в стиле R1C1 - это относительная ссылка на ячейку (относительная относительно ячейки с формулой). Например, если в ячейке С5 имеется формула =R[-1]C, то это ссылка на ячейку С4.  Чтобы записывать ссылки в стиле R1C1 необходимо переключить EXCEL в режим работы со ссылками в стиле R1C1 (Кнопка Офис/ Параметры Excel/ Формулы/ Работа с формулами).

Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ() возвращает значение ошибки #ССЫЛКА!

Рассмотрим несколько задач

Задача1 - Формируем ссылки на листы

Пусть на листах Лист1, Лист2, Лист3 и Лист4 в одних и тех же ячейках находятся однотипные данные (Продажи товаров за квартал) См. файл примера.

Сформируем итоговую таблицу Продажи за год на другом листе. В этой таблице будут присутствовать данные с 4-х листов.

Для удобства в строке 9 на листе, где будет итоговая таблица, пронумеруем столбцы С, D, E, F как 1, 2, 3, 4 в соответствии с номером квартала и пронумеруем строки таблицы (см. столбец А).

Чтобы вывести данные с других листов используем формулу =ДВССЫЛ("Лист"&C$9&"!B"&$A10+3)

Такая запись возможна, т.к. все листы имеют однотипные названия: Лист1, Лист2, Лист3 и Лист4, и все таблицы на этих листах имеют одинаковую структуру (одинаковое количество строк и столбцов, наименования товаров, также должны совпадать).

Вышеуказанная формула в ячейке С12 эквивалентна формуле =ДВССЫЛ("Лист1!B4"), формула в ячейке D12 эквивалентна =ДВССЫЛ("Лист2!B4"), т.е. ссылается на другой лист! Весь смысл использования функции ДВССЫЛ() состоит в том, чтобы написать формулу в ячейке С12 и затем ее скопировать в другие ячейки (вправо и вниз), например с помощью Маркера заполнения. Теперь данные с 4-х различных листов сведены в 1 таблицу!

Примечание: Обратите внимание на использование в формуле смешанных ссылок (C$9 и $A12).

Задача2 - ссылки на четные/ нечетные строки

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

Записав формулу =ДВССЫЛ(СИМВОЛ(65+H$26)&$A12*2+11) и скопировав ее в нужное количество ячеек, получим только четные записи из исходной таблицы. Формула в ячейке H12 эквивалентна =ДВССЫЛ("B13") 

Примечание: С помощью функции СИМВОЛ() можно вывести любой символ, зная его код. =СИМВОЛ(65) выведет букву А (английскую), =СИМВОЛ(66) выведет В, =СИМВОЛ(68) выведет D.

C помощью формулы =ДВССЫЛ(СИМВОЛ(65+N$26)&$A12*2+10) можно вывести только нечетные строки, а с помощью формулы =ДВССЫЛ(СИМВОЛ(65+B$26)&$A28+11) вообще произвольные строки, номера которых заданы в столбце А.

Задача3 - транспонирование таблиц/ векторов

С помощью нижеуказанной формулы можно транспонировать исходную таблицу (подробнее читайте здесь): 

=ДВССЫЛ(
АДРЕС(СТОЛБЕЦ(C12)+СТРОКА($B$11)-СТОЛБЕЦ($B$11);
СТРОКА(C12)-СТРОКА($B$11)+СТОЛБЕЦ($B$11))

О транспонировании таблиц можно прочитать в этом разделе. 

Примечание: О других применениях функции ДВССЫЛ() можно прочитать в статьях, список которых расположен ниже.

Задача 4 - использование с именами

Создадим несколько имен.

Имена Имя1 и Имя4 - это именованные диапазоны, т.е. эти имена возвращают ссылки.

Имя Имя2 - это константа массива, т.е. массив чисел, а не ссылка.

Также массив значений будет возвращать функция СМЕЩ(). см. Имя5.

Имя Имя3 - это именованная формула, которая возвращает число, а не ссылку.

Создадим табличку, в которой укажем эти имена. Постараемся найти сумму значений, которые вернут эти имена, использовав формулу =СУММ(ДВССЫЛ(A2)).

Как видим, работают только те формулы, которые ссылаются на ячейки содержащие Имя1 и Имя4. Только эти имена ссылаются на диапазоны ячеек. Если вспомним синтаксис функции ДВССЫЛ(), то в качестве первого аргумента можно использовать "текстовую строку в формате ссылки", а не числовые массивы.

Формула =СУММ(ДВССЫЛ(A2)) эквивалентна =СУММ(ДВССЫЛ("имя1")) Вместо "имя1" подставляется ссылка =Имена!$A$14:$A$17 (текстовая строка в формате ссылки), которая успешно разрешается функцией ДВССЫЛ(). В итоге функция ДВССЫЛ() возвращает массив {1:2:3:4} из диапазона $A$14:$A$17, который затем суммируется.

В случае с Имя2 все по-другому. Формула =СУММ(ДВССЫЛ(A3)) эквивалентна =СУММ(ДВССЫЛ("имя2")) Вместо "имя2" подставляется массив {10:20}, который не является текстовой строкой и не может быть обработан функцией ДВССЫЛ(). Поэтому она возвращает ошибку.
Аналогичный результат получим для имен: Имя3 и Имя5. 

В чем разница между =СУММ(ДВССЫЛ(имя5)) и =СУММ(ДВССЫЛ("имя5")) ? Когда мы записываем =СУММ(ДВССЫЛ("имя5")) мы говорим функции ДВССЫЛ() работать с имя5 как с адресом. Это сработает, если имя5 содержит "Имена!$A$14:$A$17" или что-то в этом роде. Но, имя5 указывает на формулу, которая возвращает значения из диапазона Имена!$A$14:$A$17. Т.к. это не ссылка, то функция вернет ошибку.