Разбор функции ДВССЫЛ (INDIRECT) на примерах
На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:
Обратите внимание, что аргумент функции - ссылка на А1 - введен в кавычках, поэтому что, по сути, является здесь текстом.
"Ну ОК", - скажете вы. "И что тут полезного?".
Но не судите по первому впечатлению - оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.
Пример 1. Транспонирование
Классика жанра: нужно превратить вертикальный диапазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:
Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом "&" букву "А" и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN).
Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического "морского боя". В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C2, C2=R1C3, D2=R1C4 и т.д.
Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE), то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:
Пример 2. Суммирование по интервалу
Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET). Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM):
Пример 3. Выпадающий список по умной таблице
Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные - Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:
Если же "обернуть" ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:
Пример 4. Несбиваемые ссылки
Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:
Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.
Пример 5. Сбор данных с нескольких листов
Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):
Допустим, что форма, размеры, положение и последовательность товаров и месяцев во всех таблицах одинаковые - различаются только числа.
Собрать данные со всех листов (не просуммировать, а положить друг под друга "стопочкой") можно всего одной формулой:
Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в "живую". Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа - номера строк, которые нужно взять с каждого листа.
Подводные камни
При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:
- Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
- С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический - без проблем.
- ДВССЫЛ является волатильной (volatile) или "летучей" функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.
Ссылки по теме
- Как создать динамический диапазон с автоподстройкой размеров
- Суммирование по диапазону-окну на листе функцией СМЕЩ (OFFSET)
Это, пожалуй, самое красивое решение для создания «расширяемого» выпадающего списка в Excel:
=ДВССЫЛ("Люди[Сотрудники]")
Как вариант, можно ссылаться сразу на таблицу, если в таблице один столбец:
=ДВССЫЛ("Люди")
Я этот способ всегда использую для создания выпадающих списков = справочников для заполнения различных форм в Excel правильными данными.
В ближайшее время напишу статью в своем блоге на тему "Справочники":
В каждом комментарии реклама)))
thank you!
=ДВССЫЛ("Люди[Сотрудники]")=$J10
=ДВССЫЛ("Люди["&A$1&"]")=A$6
т.е. без ДВССЫЛ это не работает…
Меня зовут Анатолий.
И как всегда, то- что вы даёте людям имеет высокий уровень качества, очень высокий уровень у формы подачи информации и у самой информации. Если кратко и понятно, то вы замечательный педагог и даёте то, что нужно!!!
Пример 5. Сбор данных с нескольких листов - Замечательный пример!!!
Когда я попытался его воспроизвести - то понял, что для реального мира он не приспособлен
по любому какой-то "Хороший человек" свою таблицу заполнит не так как нужно и мне придётся проверять каждого... Поэтому я капельку изменил способ!!!
В основе была связка ИНДЕКС + ПОИСКПОЗ. А вот где искать номера столбцов и строк - эту задачу я реализовал с помощью ДВССЫЛ и & и вашего урока. Теперь мне всё равно в какой последовательности идут товары или столбцы с месяцами!!!
СПАСИБО!!!
А примером можете поделиться как реализовано?
Я хотела, чтобы с нескольких, в целом однотипных, листов собирался итог, содержащий в себе номер акта/документа и его дату. С номером проблем не возникло, я в точности повторила формулу из примера. А вот с датой пришлось повозиться, т.к. она выводилась в конце таблицы и вероятность смещения ячейки с ней была велика (простым добавлением дополнительных строк, например, или наоборот, удалением). И формула в итоге такая:
=ИНДЕКС(ДВССЫЛ("'"&$A2&"'!$H$1:$H500");ПОИСКПОЗ("Дата";ДВССЫЛ("'"&$A2&"'!$H$1:$H500");0)+1))
индекс просматривает массив на листе, на который(массив имею ввиду) отсылает двссыл, ищет в этом массиве посредством поискпоз ячейку со словом "Дата", смещается на одну ячейку вниз и выводит, в конечном итоге, ее значение. Простите, если не совсем ясно, возможно по самой формуле будет лучше понять.
Позволяет нам (внутри функции) выходить не на саму ячейку, а на её координаты,
чтобы результатом поиска был не просто адрес ячейки, а её содержимое.
Спасибо автору за отличные простые примеры, чтобы понять именно логику этой функции, чтобы в дальнейшем хитрить ей там, где проще выйти на адрес ячейки, нежели ссылаться на неё напрямую.
Ваши уроки очень познавательны.
Но у меня вопрос возможно отыскать ФИО в одной таблице по 3 критериям не используя макросов и подставить их в другую? Если возможно подскажите как такое чудо возможно.
или
=ИНДЕКС(D8:D14;ПОИСКПОЗ(;ЕСЛИ((A8:A14=C2)*(B8:B14=C3)*(C8:C14=C4)
формулами массива
Возник вопрос в примере 2.
Если пишу, как у Вас E3+1 и E4+1 - считает неверно. Если пишу E3 и E4, то всё в порядке.
Не получилось приложить скрин, .
Укажите мне на ошибку, пожалуйста.
Если в ячейке не указать имя столбца "Выручка" - считает с ошибкой, если указать - всё ок.
Теперь надо разобраться почему так выходит.
Столкнулся с проблемой сборки данных из других книг. Никак не выходит. Постоянно берет данные с того листа где написана формула, а нужно тянуть из другой книги(((
С уважением..
Спасибо.
Подскажите, как сделать, чтобы работала эта формула в случае ссылки на ячейку в другой книге.
Причём, если книга (с данными) закрыта, то формула не работает. Т.е. работает, когда оба файла открыты.
Заранее спасибо.
Прошу помощи зала.
Не получается получить данные из закрытытого файла
Прописываю формулой полный путь на сетевом диске путем сбора данных из разных ячеек
и с использованием фукнции =АДРЕС(1;1;4;1;C3) (результат функции адрес: '\\Disk\Folder\[File.xlsx]Page'!A1)
оба варианта
=ДВССЫЛ(A9;ИСТИНА)
=ДВССЫЛ(АДРЕС(1;1;4;1;C3);1)
дают ошибку #ССЫЛКА! (при открытом файле с данными ошибка тоже остается).
если прописать в ячейке = и скопировать результат функции адрес, то выдается правильное значение из закрытого файла
Заранее спасибо.
Если исходный файл закрыт, то получите ошибку #ССЫЛКА
Возможно ли одновременное применение функции ДВССЫЛ и функции ЕСЛИ?
Выглядеть это должно следующим образом: =ЕСЛИ($А$1="";"";ДВССЫЛ($А$1))
Всю голову сломал пробуя различные варианты написания - не получается:(
=СУММ(л:щ!A1)
а вот написал с двссыл
=СУММ(ДВССЫЛ("л"&":щ"&"!"&АДРЕС(СТРОКА(A1);СТОЛБЕЦ(A1);4;1)))
результат #ССЫЛКА!
хотя если убрать сумм и двссыл ="л"&":щ"&"!"&АДРЕС(СТРОКА(A1);СТОЛБЕЦ(A1);4;1)
результат такой, как в первой функции л:щ!A1
не пойму почему так
Приводили пример использования ДВССЫЛ для сохранение ссылок на источник. У меня обратная задача - при заведении листа за новый период копированием старого все ссылки в формулах (на листе и в диспетчере имен) ведут назад на старый лист. (Например был лист Август, копированием листа получили лист Сентябрь, а ссылки из листа Сенябрь по прежнему ведут в лист Август). Получил совет использовать для предотвращения такой ситуации ДВССЫЛ, но не разобрался, как. Подскажите?
С уважением И. Мешков
Есть отдельная таблица где построчно пишутся названия листов:
Сводная таблица Петра
Сводная таблица Екатерины
и .тд.
Далее в своей таблицы я через ДВССЫЛ ссылаюсь на колонку с названием листов, далее указываю координаты данных которые хочу забрать. Но не тут то было. Если имя листа цельное "Сводная_таблица_Петра" - то все работает. А если имя листа имеет пробелы, то выдает ошибку.
Есть ли решение?
Перебрала что можно, или работает не корректно или ошибки выдает