Функция ДВССЫЛ() в 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. Т.к. это не ссылка, то функция вернет ошибку.