Разбор функции ДВССЫЛ (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. Сбор данных с нескольких листов - Замечательный пример!!!
Когда я попытался его воспроизвести - то понял, что для реального мира он не приспособлен
по любому какой-то "Хороший человек" свою таблицу заполнит не так как нужно и мне придётся проверять каждого... Поэтому я капельку изменил способ!!!
В основе была связка ИНДЕКС + ПОИСКПОЗ. А вот где искать номера столбцов и строк - эту задачу я реализовал с помощью ДВССЫЛ и & и вашего урока. Теперь мне всё равно в какой последовательности идут товары или столбцы с месяцами!!!
СПАСИБО!!!
Позволяет нам (внутри функции) выходить не на саму ячейку, а на её координаты,
чтобы результатом поиска был не просто адрес ячейки, а её содержимое.
Спасибо автору за отличные простые примеры, чтобы понять именно логику этой функции, чтобы в дальнейшем хитрить ей там, где проще выйти на адрес ячейки, нежели ссылаться на неё напрямую.
Ваши уроки очень познавательны.
Но у меня вопрос возможно отыскать ФИО в одной таблице по 3 критериям не используя макросов и подставить их в другую? Если возможно подскажите как такое чудо возможно.
или
=ИНДЕКС(D8:D14;ПОИСКПОЗ(;ЕСЛИ((A8:A14=C2)*(B8:B14=C3)*(C8:C14=C4)
формулами массива
Возник вопрос в примере 2.
Если пишу, как у Вас E3+1 и E4+1 - считает неверно. Если пишу E3 и E4, то всё в порядке.
Не получилось приложить скрин, .
Укажите мне на ошибку, пожалуйста.
Если в ячейке не указать имя столбца "Выручка" - считает с ошибкой, если указать - всё ок.
Теперь надо разобраться почему так выходит.
Столкнулся с проблемой сборки данных из других книг. Никак не выходит. Постоянно берет данные с того листа где написана формула, а нужно тянуть из другой книги(((
С уважением..
Спасибо.
Подскажите, как сделать, чтобы работала эта формула в случае ссылки на ячейку в другой книге.
Причём, если книга (с данными) закрыта, то формула не работает. Т.е. работает, когда оба файла открыты.
Заранее спасибо.