Функция ДВССЫЛ (INDIRECT) в Excel и Google Sheets. Полное руководство

Кратко

Что делает:
Возвращает ссылку на ячейку или диапазон, заданную в виде текстовой строки .

Где работает:
✅ Excel (все версии)
✅ Google Sheets — поддерживается
✅ МойОфис Таблицы

Ключевая особенность:
Позволяет «собрать» ссылку из текстовых частей, делая формулы динамическими и адаптивными .

Сложность:
★★★☆☆ — Средняя (требует понимания работы со ссылками и текстовыми строками)

Для чего нужна функция

ДВССЫЛ (INDIRECT) — это функция, которая превращает текст в работающую ссылку . Она работает по принципу:

текст → ссылка → значение

Например:

• A1 содержит текст «B5»
• B5 содержит значение 100
• =ДВССЫЛ(A1) → "B5" → ссылка на B5 → 100

Это позволяет использовать текстовые строки для построения адресов ячеек, что открывает возможности для создания динамических и адаптивных формул:

• Ссылки, которые не изменяются при вставке и удалении строк — если адрес записан как текст, Excel не изменяет этот текст при структурных изменениях листа .
• Динамическая подстановка листа — построение ссылки на ячейку в зависимости от имени листа, указанного в другой ячейке.
• Гибкие выпадающие списки — создание источников данных, которые автоматически расширяются при добавлении новых записей.
• Сбор данных с нескольких листов — объединение однотипных отчётов с разных листов в одну формулу .

Важное предупреждение: Функция ДВССЫЛ является волатильной (volatile) . Это означает, что она пересчитывается при любом изменении на листе, что может замедлять работу в больших таблицах .

Совместимость

Платформа	Поддержка INDIRECT
Excel	✅ Да
Google Sheets	✅ Да
МойОфис Таблицы	✅ Да

ДВССЫЛ — давно существующая функция Excel и поддерживается в современных и более старых версиях программы.

Синтаксис

Логика аргументов функции одинакова в Excel и Google Sheets :

=ДВССЫЛ(ссылка_на_текст; [стиль_ссылки])

=INDIRECT(ref_text; [a1])

Разбор аргументов

Аргумент	Обязательный	Описание
ссылка_на_текст (ref_text)	✅ Да	Текстовая строка, содержащая ссылку на ячейку или диапазон. Может быть ссылкой в стиле A1 или R1C1, именем диапазона или ссылкой на ячейку с таким текстом .
стиль_ссылки (a1)	❌ Нет	Логическое значение: • ИСТИНА или опущен — ссылка в стиле A1 • ЛОЖЬ — ссылка в стиле R1C1

Важно: =ДВССЫЛ("B5") и =ДВССЫЛ(B5) — это не одно и то же. Во втором случае содержимое B5 должно само быть корректным адресом.

Создание адреса из частей

Динамическая ссылка

Задача: Собрать ссылку из текстовых частей.

Исходные данные: В ячейке A1 указан номер строки (например, 15). В ячейке B1 указан номер столбца (например, C).

Формула: =ДВССЫЛ(B1&A1)

Результат: Ссылка на ячейку C15.

Объяснение: ДВССЫЛ склеивает «C» и «15» в текст «C15», а затем превращает его в работающую ссылку .

Динамическая ссылка с изменяемой строкой

Задача: Создать ссылку, которая меняется при изменении номера строки.

Формула: =ДВССЫЛ("B"&A1)

Результат: Если A1 = 10 → ссылка на B10. Если изменить A1 на 25 → ссылка на B25.

Объяснение: Это показывает, как ДВССЫЛ позволяет создавать ссылки, которые адаптируются к изменениям данных.

Простые примеры

Пример 1. Получение значения из ячейки по текстовой ссылке

Исходные данные: В ячейке A1 находится текст «B5». В ячейке B5 находится значение 100.

Задача: Получить значение из ячейки, адрес которой указан в A1.

Формула: =ДВССЫЛ(A1)

Результат: 100

Объяснение: ДВССЫЛ «читает» содержимое A1 (текст «B5») и превращает его в ссылку на ячейку B5, затем возвращает значение из этой ячейки .

Пример 2. Прямая текстовая ссылка

Формула: =ДВССЫЛ("B5")

Результат: Значение из ячейки B5.

Объяснение: Текст в кавычках сразу интерпретируется как ссылка .

Пример 3. Суммирование диапазона по текстовой ссылке

Исходные данные: В ячейке A1 находится текст «A1:C5».

Формула: =СУММ(ДВССЫЛ(A1))

Результат: Сумма всех чисел в диапазоне A1:C5.

Объяснение: ДВССЫЛ возвращает ссылку на диапазон, который затем суммируется функцией СУММ .

Динамическая ссылка на другой лист

Задача: Создать ссылку на ячейку на другом листе, имя которого задано в текстовой строке.

Исходные данные: В ячейке A1 находится «Продажи» (имя листа). В ячейке B1 находится «B5» (адрес ячейки).

Формула: =ДВССЫЛ("'"&A1&"'!"&B1)

Результат: Ссылка на ячейку B5 на листе «Продажи».

Объяснение: Склейка через & создаёт текст вида 'Продажи'!B5, который ДВССЫЛ превращает в работающую ссылку. Одинарные кавычки обязательны, если имя листа содержит пробелы: 'Продажи за январь'!B5.

Стили ссылок: A1 и R1C1

В Excel и Google Sheets существуют два стиля ссылок:

Стиль	Пример	Описание
A1	=ДВССЫЛ("C5")	Буквы столбцов + номера строк
R1C1	=ДВССЫЛ("R5C3"; ЛОЖЬ)	Номера строк и столбцов

Объяснение: R5C3 в стиле R1C1 указывает на строку 5, столбец 3 — это та же ячейка, что и C5 в стиле A1.

Практические сценарии использования

Ссылки, которые не изменяются при вставке и удалении строк

Задача: Создать ссылку, которая не «ломается» при удалении или вставке строк/столбцов .

Обычная ссылка: =B2 — при удалении строки 2 вернёт #ССЫЛКА!.

Ссылка через ДВССЫЛ: =ДВССЫЛ("B2") — останется корректной, так как ссылка задана текстом, а не адресом .

Важно: Однако это не означает, что такая ссылка всегда будет указывать на нужные данные. Если структура таблицы изменилась, текстовый адрес может остаться прежним и начать указывать на другую ячейку.

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

Задача: Собрать данные с листов с именами сотрудников (Михаил, Елена, Иван) в одну таблицу .

Формула: =ДВССЫЛ(B$1&"!"&$A2)

Объяснение: B$1 содержит имя листа, $A2 — адрес ячейки на этом листе. Склейка через & создаёт текст вида «Михаил!A2», который ДВССЫЛ превращает в работающую ссылку .

Динамический диапазон с СЧЁТЗ

Задача: Создать диапазон, который автоматически расширяется при добавлении новых данных .

Исходные данные: A1 содержит заголовок, а A2:A15 — 14 заполненных значений.

Формула: =ДВССЫЛ("A2:A"&СЧЁТЗ(A:A))

Результат: формула вернёт диапазон A2:A15.

Объяснение: СЧЁТЗ подсчитывает количество непустых ячеек, ДВССЫЛ подставляет это число в текст ссылки .

Важно: Такой способ предполагает, что данные расположены без пропусков. СЧЁТЗ считает количество непустых ячеек, а не определяет номер последней заполненной строки.

ДВССЫЛ с именованными диапазонами

ДВССЫЛ может обращаться к именованным диапазонам, если текстовая строка содержит корректное имя диапазона. Однако с динамическими именами, созданными сложными формулами, возможны ограничения. Поэтому для сложных динамических диапазонов лучше использовать прямые ссылки, ИНДЕКС или другие подходы.

ДВССЫЛ и другие книги Excel

Формула для внешней книги: =ДВССЫЛ("'[Отчет.xlsx]Январь'!B5")

Важно: Для внешней книги она должна быть открыта. Если файл закрыт, ДВССЫЛ вернёт ошибку #ССЫЛКА! .

Этот сценарий относится к Excel. В Google Sheets ссылки на данные из другой таблицы обычно выполняются с помощью IMPORTRANGE.

ДВССЫЛ + другие функции

ДВССЫЛ + СУММ

=СУММ(ДВССЫЛ("B2:B10")) — суммирует диапазон, заданный текстом.

ДВССЫЛ + СЧЁТЗ

=СЧЁТЗ(ДВССЫЛ("A2:A100")) — подсчитывает количество непустых ячеек в диапазоне.

ДВССЫЛ + ИНДЕКС

=ИНДЕКС(ДВССЫЛ("A1:C10"); 3; 2) — возвращает значение из заданной позиции в динамическом диапазоне.

ДВССЫЛ + ЕСЛИ

Задача: Выбрать данные с разных листов в зависимости от условия.

Формула: =ДВССЫЛ(ЕСЛИ(A1="Январь"; "Январь!B5"; "Февраль!B5"))

Объяснение: В зависимости от значения в A1, ДВССЫЛ обратится к нужному листу.

Когда НЕ стоит использовать ДВССЫЛ

Не стоит применять её просто ради динамичности, если задачу можно решить обычной ссылкой, ИНДЕКС, ПОИСКПОЗX, ПРОСМОТРX, ФИЛЬТР и т.д.

Причины:

• Функция волатильная — пересчитывается при любом изменении на листе
• Сложнее отлаживать формулы
• Текстовые ссылки хуже читаются
• Сложнее контролировать ошибки
• Есть ограничения с внешними книгами (должны быть открыты)

Microsoft также рекомендует учитывать производительность: INDIRECT является волатильной функцией и вычисляется в однопоточном режиме. Поэтому большое количество формул ДВССЫЛ может негативно влиять на скорость пересчёта большой книги.

ДВССЫЛ — плюсы и минусы

Преимущества

• Позволяет создавать адреса из текста
• Позволяет динамически выбирать лист
• Удобно использовать для динамических диапазонов
• Поддерживает A1 и R1C1
• Полезна в шаблонах и сложных отчётах

Недостатки

• Волатильная функция
• Сложнее читать и отлаживать формулы
• Ошибки в текстовом адресе приводят к #ССЫЛКА!
• Внешние книги требуют особого внимания
• Во многих задачах можно использовать более производительные альтернативы

ДВССЫЛ или ИНДЕКС — что выбрать?

ДВССЫЛ стоит использовать, когда адрес ячейки или диапазона необходимо сформировать именно как текст — например, при динамическом выборе листа или построении ссылки из нескольких частей.

ИНДЕКС лучше подходит, когда положение нужной ячейки можно определить через номер строки и столбца без преобразования текста в ссылку. Такой подход обычно предпочтительнее в больших книгах, поскольку ИНДЕКС не является волатильной функцией.

Поэтому ДВССЫЛ — это скорее специализированный инструмент для динамических текстовых ссылок, а не универсальная замена ИНДЕКС.

Сравнение ДВССЫЛ с альтернативами

Что использовать для разных задач

Задача	Что использовать
Построить ссылку из текста	ДВССЫЛ
Получить значение по номеру строки/столбца	ИНДЕКС
Найти позицию элемента	ПОИСКПОЗ / ПОИСКПОЗX
Найти значение по условию	ПРОСМОТРX
Отобрать динамический диапазон	ФИЛЬТР
Динамическая ссылка на другой лист	ДВССЫЛ

Таблица сравнения

Критерий	ДВССЫЛ (INDIRECT)	ИНДЕКС (INDEX)	ПРОСМОТРX (XLOOKUP)
Сборка ссылки из текста	✅ Да	❌ Нет	❌ Нет
Волатильность	✅ Волатильна	❌ Неволатильна	❌ Неволатильна
Работа с внешними ссылками	⚠️ Есть ограничения	✅ Да	✅ Да
Динамические диапазоны	✅ Да	✅ Да	❌ Нет
Производительность	Может снижаться при большом количестве формул	Обычно предпочтительнее для больших моделей	Высокая

Частые ошибки и их решение

Ошибка #ССЫЛКА! (#REF!)

Причина:

• Указанный адрес не существует .
• Ссылка ведёт на другой файл, который закрыт .
• Ссылка выходит за пределы допустимого количества строк/столбцов .

Решение: Проверьте корректность адреса и откройте связанный файл.

Ошибка #ЗНАЧ! (#VALUE!)

Причина: Аргумент ссылка_на_текст не является допустимой ссылкой .

Решение: Убедитесь, что текст в ячейке или в кавычках — это корректный адрес ячейки или диапазона.

ДВССЫЛ не работает с динамическими именованными диапазонами

ДВССЫЛ может обращаться к именованным диапазонам, если текстовая строка содержит корректное имя диапазона. Однако с динамическими именами, созданными сложными формулами, возможны ограничения. Поэтому для сложных динамических диапазонов лучше использовать прямые ссылки, ИНДЕКС или другие подходы.

Часто задаваемые вопросы

Вопрос: В каких версиях Excel работает ДВССЫЛ (INDIRECT)?
Ответ: ДВССЫЛ — давно существующая функция Excel и поддерживается в современных и более старых версиях программы .

Вопрос: Можно ли использовать ДВССЫЛ в Google Sheets?
Ответ: Да, функция поддерживается с тем же синтаксисом .

Вопрос: Почему ДВССЫЛ возвращает #ССЫЛКА! при ссылке на другой файл?
Ответ: Другой файл должен быть открыт. Если он закрыт, ДВССЫЛ не может получить к нему доступ .

Вопрос: Как заменить ДВССЫЛ для создания динамического диапазона?
Ответ: Используйте ИНДЕКС (INDEX) — она неволатильна и работает быстрее на больших данных.

Вопрос: ДВССЫЛ работает с динамическими именованными диапазонами?
Ответ: ДВССЫЛ может обращаться к именованным диапазонам, если текстовая строка содержит корректное имя диапазона. Однако с динамическими именами, созданными сложными формулами, возможны ограничения.

Заключение

ДВССЫЛ (INDIRECT) — это мощный инструмент для создания динамических ссылок, доступный во всех версиях Excel и Google Sheets. Она позволяет «собирать» ссылки из текстовых частей, что делает формулы гибкими и адаптивными к изменениям структуры данных.

Ключевые правила:

1. ДВССЫЛ превращает текст в работающую ссылку .
2. При ссылке на другой файл он должен быть открыт .
3. ДВССЫЛ — волатильная функция, она пересчитывается при любом изменении на листе .
4. ДВССЫЛ может обращаться к именованным диапазонам, если текстовая строка содержит корректное имя диапазона.
5. В больших таблицах рассмотрите альтернативу — ИНДЕКС (INDEX).

Освойте ДВССЫЛ — и вы сможете создавать формулы, которые адаптируются к изменениям структуры таблиц, автоматически подставляют нужные листы и диапазоны, а также собирают данные из множества источников. Но помните о волатильности: в больших таблицах предпочтительнее использовать неволатильные альтернативы.