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

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Простой пример на ДВССЫЛ

Обратите внимание, что аргумент функции - ссылка на А1 - введен в кавычках, поэтому что, по сути, является здесь текстом.

"Ну ОК", - скажете вы. "И что тут полезного?". 

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

Пример 1. Транспонирование

Классика жанра: нужно превратить вертикальный диа

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Транспонирование столбца в строку

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом "&" букву "А" и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN).

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического "морского боя". В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C2, C2=R1C3, D2=R1C4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE), то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

ДВССЫЛ в режиме R1C1

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET). Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM):

Сумма по интервалу

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные - Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

Ошибка при создании вып.списка

Если же "обернуть" ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

ДВССЫЛ для создания динамического выпадающего списка

Пример 4. Несбиваемые ссылки

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

Несбиваемые ссылки

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

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

Отчеты для сборки

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

Собрать данные со всех листов (не просуммировать, а положить друг под друга "стопочкой") можно всего одной формулой:

Сборка данных функцией ДВССЫЛ

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в "живую". Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа - номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

  • Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
  • С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический - без проблем.
  • ДВССЫЛ является волатильной (volatile) или "летучей" функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.

Ссылки по теме

 



17.04.2017 12:22:28
Николай, спасибо за «Пример 3. Выпадающий список по умной таблице»
Это, пожалуй, самое красивое решение для создания «расширяемого» выпадающего списка в Excel:

=ДВССЫЛ("Люди[Сотрудники]")

Как вариант, можно ссылаться сразу на таблицу, если в таблице один столбец:
=ДВССЫЛ("Люди")

Я этот способ всегда использую для создания выпадающих списков = справочников для заполнения различных форм в Excel правильными данными.
В ближайшее время напишу статью в своем блоге на тему "Справочники": http://biweb.ru/blog
31.05.2017 11:01:32
Странно, что Николай ещё не заблокировал Вас :)
В каждом комментарии реклама)))
19.04.2017 09:31:14
полезная статья!!!
thank you!
21.04.2017 12:31:31
Еще вспомнил, в формулах для «Условного форматирования» функция ДВССЫЛ разрешает писать формулы в стиле «Умных таблиц», например:
=ДВССЫЛ("Люди[Сотрудники]")=$J10
=ДВССЫЛ("Люди["&A$1&"]")=A$6
т.е. без ДВССЫЛ это не работает…
14.05.2017 17:53:54
Николай, СПАСИБО!!!
Меня зовут Анатолий.
И как всегда, то- что вы даёте людям имеет высокий уровень качества,  очень высокий уровень у формы подачи информации и у самой информации. Если кратко и понятно, то вы замечательный педагог и даёте то, что нужно!!!  
Пример 5. Сбор данных с нескольких листов - Замечательный пример!!!
Когда я попытался его воспроизвести - то понял, что для реального мира он не приспособлен:)

по любому какой-то "Хороший человек" свою таблицу заполнит не так как нужно и мне придётся проверять каждого... Поэтому я капельку изменил способ!!! :)))
В основе была связка ИНДЕКС + ПОИСКПОЗ. А вот где искать  номера столбцов и строк - эту задачу я реализовал с помощью ДВССЫЛ и & и вашего урока. Теперь мне всё равно в какой последовательности идут товары или столбцы с месяцами!!!

СПАСИБО!!!:D
12.07.2017 16:10:59
Я бы описал функцию (итоговая польза) так:

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

Спасибо автору за отличные простые примеры, чтобы понять именно логику этой функции, чтобы в дальнейшем хитрить ей там, где проще выйти на адрес ячейки, нежели ссылаться на неё напрямую.
12.09.2017 13:26:31
Спасибо за урок,я ничего не понял!
06.10.2017 12:39:38
Николай Доброе время суток.
Ваши уроки очень познавательны.
Но у меня вопрос возможно отыскать ФИО в одной таблице по 3 критериям не используя макросов и подставить их в другую? Если возможно подскажите как такое чудо возможно.
22.11.2018 17:46:52
=ВПР(C2;ЕСЛИ((B8:B14=C3)*(C8:C14=C4);A8:D14;"";);4;0)

или

=ИНДЕКС(D8:D14;ПОИСКПОЗ(;ЕСЛИ((A8:A14=C2)*(B8:B14=C3)*(C8:C14=C4););0);)

формулами массива
22.11.2018 18:11:05
Спасибо;)
29.11.2017 12:25:58
Здравствуйте, Павел!

Возник вопрос в примере 2.
Если пишу, как у Вас E3+1 и E4+1 - считает неверно. Если пишу E3 и E4, то всё в порядке.
Не получилось приложить скрин, ссылка на облако.
Укажите мне на ошибку, пожалуйста.
29.11.2017 12:31:42
Нашёл в чём ошибка.
Если в ячейке не указать имя столбца "Выручка" - считает с ошибкой, если указать - всё ок.
Теперь надо разобраться почему так выходит.  
29.11.2017 17:28:43
Всё разобрал, извините.
22.06.2018 19:22:41
Николай, у меня возник вопрос по Примеру №1, в части когда транспонируем из горизонтального в вертикальный. При смещении данных, например, в строку R15, формула выдает "0". В чем причина?
Наверх