В этой статье мы покажем, как может быть создана ссылка ДВССЫЛ на лист или книгу Excel.
Функция ДВССЫЛ (или INDIRECT на английском) в Excel — это мощный инструмент для создания динамических ссылок на данные, которые могут находиться на разных листах или даже в других книгах.
Главная цель этой функции — дать пользователям возможность ссылаться на ячейки, передавая их адрес в виде текста. Это особенно полезно для создания гибких и легко обновляемых формул.
Принцип работы функции ДВССЫЛ
Функция ДВССЫЛ интерпретирует текстовое значение как ссылку на ячейку. Она может моментально изменять адрес ссылки, что делает её особенно полезной для создания отчётов, которые автоматически обновляются по мере изменения данных.
Эта функция идеально подходит для ситуаций, когда источники данных меняются, позволяя динамически формировать адреса ссылок на основе других ячеек.
Например, с помощью формулы ДВССЫЛ, определяющей имя листа и ячейки, можно быстро обновлять ссылки без ручного редактирования каждой формулы.
Статичная ссылка ДВССЫЛ на лист
Чтобы создать ссылку на данные с помощью ДВССЫЛ, нужно знать имя рабочего листа и адрес ячейки или диапазона, на который ссылается формула. Синтаксис функции включает три основных компонента: имя листа в кавычках, знак ‘!’ и адрес ячейки.
Пример: Если вы хотите сослаться на ячейку A1 на листе с именем «Продажи», формула будет выглядеть так:
=ДВССЫЛ("Продажи!A1")После ввода этой формулы в нужной ячейке, Excel будет отображать значение из указанной ячейки на другом листе.
Теперь рассмотрим пример, когда все значения записаны в отдельных ячейках.
Допустим, у вас есть данные на листе 1, и вам нужно получить их на листе 2. Вот как это сделать с помощью формулы:
=ДВССЫЛ("'"&A2&"'!"&B2&C2)
Разберёмся, как работает эта формула.
Обычный способ ссылки на другой лист в Excel — это указание имени листа, за которым следует восклицательный знак и адрес ячейки или диапазона, например Лист1!A1:C10.
Если имя листа содержит пробелы, заключите его в одинарные кавычки, чтобы избежать ошибок: ‘Лист 1’!A1 или для диапазона — ‘Лист 1’!A1:C10.
Динамическая ссылка ДВССЫЛ на лист
- Запишите имя листа в одну ячейку.
- Имя столбца — в другую.
- Номер строки — в третью.
- Объедините всё это в одну текстовую строку.
- Передайте строку функции ДВССЫЛ.
Важно заключать каждый элемент (кроме адреса или номера ячейки) в двойные кавычки и связывать их оператором объединения (&). Пример:
=ДВССЫЛ("'" & имя листа & "'!" & имя столбца & номер строки)Вернёмся к примеру: вы помещаете имя листа в ячейку A2, а адреса столбца и строки — в B2 и C2. В результате формула будет выглядеть так:
=ДВССЫЛ("'"&A2&"'!"&B2&C2)Если вы копируете формулу в несколько ячеек, зафиксируйте ссылку на имя листа, используя абсолютные ссылки, например $A$2.
Замечания
- Если ячейки с именем листа и адресами ячеек (A2, B2, C2) пусты, формула вернет ошибку. Чтобы этого избежать, оберните функцию ДВССЫЛ в функцию ЕСЛИ:
=ЕСЛИ(ИЛИ(A2="";B2="";C2=""); ""; ДВССЫЛ("'"&A2&"'!"&B2&C2))- Для корректной работы формулы лист должен быть открыт. Иначе формула вернёт ошибку #ССЫЛКА. Используйте функцию ЕСЛИОШИБКА чтобы не показывать сообщение об ошибке:
=ЕСЛИОШИБКА(ДВССЫЛ("'"&A2&"'!"&B2&C2); "")ДВССЫЛ ссылка на книгу Excel
Создание ссылки на другую книгу в Excel аналогично, но требует указания имени книги. Начнём с обычной ссылки:
‘[Имя_книги.xlsx]Имя_листа’!Адрес_ячейки
Для универсальности всегда добавляйте апострофы. Предположим, название книги в ячейке A2, имя листа — в B2, адрес ячейки — в C2 и D2. Формула будет выглядеть так:
=ДВССЫЛ("'["&$A$2&".xlsx]"&$B$2&"'!"&C2&D2)Используйте абсолютные ссылки для фиксации ячеек с именами книг и листов: $A$2 и $B$2. Пример промежуточного результата:
=ДВССЫЛ("'[INDIRECT.xlsx]Продажи'!D3")Итоговый результат виден на скриншоте ниже.
Теперь вы можете создать свою динамическую ссылку на другую книгу, используя шаблон:
=ДВССЫЛ("'[" & Название книги & "]" & Имя листа & "'!" & Адрес ячейки)Примечание
Рабочая книга, на которую ссылается формула, должна быть открыта, иначе функция ДВССЫЛ вернёт ошибку #ССЫЛКА.
Используйте функцию ЕСЛИОШИБКА, чтобы предотвратить это:
=ЕСЛИОШИБКА(ДВССЫЛ("'[" & A2 & "]" & $A$1 & "'!" & B1); "")Использование функции ДВССЫЛ открывает множество возможностей для динамического управления данными в Excel.
Итак, ссылка ДВССЫЛ на лист и книгу Excel значительно упрощает работу, особенно когда речь идёт о ссылках на другие рабочие листы и книги.
ДВССЫЛ позволяет пользователям взаимодействовать с данными более динамично и эффективно, создавая ссылки, которые автоматически обновляются при изменении источников данных.
Вот еще несколько полезных материалов: