Представьте, что вы даете другу записку: «Посмотри в коробке, которая стоит на второй полке». Друг идет к полке, находит коробку и достает оттуда сюрприз. ДВССЫЛ в Excel работает так же: она читает текст-указатель, а затем находит и возвращает значение, которое лежит по текстовому адресу.
Содержание
Обзор функции ДВССЫЛ
Excel ДВССЫЛ (на английском — функция INDIRECT) — это инструмент для создания косвенную ссылки. Обычная формула, например
=C5
,жестко привязана к ячейке C5. Удалили строку 5 — формула сломалась. Функция Excel ДВССЫЛ действует иначе. Она смотрит на текст «C5» и только потом идет по этому адресу. Поэтому удаление строк ей не страшно.
Как работает эта магия? ДВССЫЛ создает двойную ссылку: сначала программа видит текст, потом преобразует его в настоящий адрес. Такая связка называется ДВССЫЛ. Это очень полезно, когда вы хотите собрать данные из разных мест или защитить формулы от случайных изменений.
Важно знать: функция ДВССЫЛ — «тяжелая». Она пересчитывается каждый раз, когда что-то меняется в таблице. Если вы сделаете 10 000 таких формул, редактор Excel может начать тормозить. Поэтому используйте ее дозированно, только там, где нужна гибкость.
Синтаксис
ДВССЫЛ пишется на русском языке именно так. На английском — INDIRECT. Синтаксис (правила записи) выглядит так:
=ДВССЫЛ(ссылка_на_ячейку; [стиль])
Разберем каждый аргумент.
Первый аргумент — «ссылка_на_ячейку» — самый главный. Это текст, который указывает на нужный адрес. Вы можете написать адрес прямо в кавычках, например «B10». А можете положить текст «B10» в какую-то ячейку (скажем, в F5) и написать
=ДВССЫЛ(F5)
Тогда адрес отобразился в формуле правильно.
Второй аргумент — «стиль» — необязательный. Он отвечает за то, как Excel понимает адрес. Если вы ничего не пишете или пишете ИСТИНА (или 1), то используется привычный стиль A1: сначала буква столбца, потом номер строки. Если пишете ЛОЖЬ (или 0), то Excel переключается на стиль R1C1, где R — это строка (Row), а C — столбец (Column). Например, R3C2 означает третий ряд и второй столбец — то есть ячейку B3.
Для большинства задач достаточно просто писать
=ДВССЫЛ(«адрес»)
Второй аргумент можно не трогать.
Метод 1. Перенос данных по горизонтали и вертикали
Допустим, у вас есть список месяцев, записанный в строке: A1=Январь, B1=Февраль, C1=Март. Вам нужно быстро сделать столбец А с этими же названиями. Обычное копирование не поможет — Excel скопирует только первое слово. Нужно транспонирование (поворот данных).
На помощь приходит формула ДВССЫЛ в связке с функцией АДРЕС. Функция АДРЕС строит адрес ячейку в виде текста. Например, `АДРЕС(1;1)` вернёт текст «$A$1».
В ячейку G2 (под названием «Январь») пишем:
=ДВССЫЛ(АДРЕС(1; СТРОКА(A1); 4))
Что здесь происходит? СТРОКА(A1) равна 1. Значит, АДРЕС(1;1;4) дает текст «A1». ДВССЫЛ идет в ячейку A1 и берет оттуда слово «Январь».
Теперь протягиваем формулу вниз, до ячейки G4. Во второй строке СТРОКА(A2) равна 2. АДРЕС(1;2;4) превращается в «B1». ДВССЫЛ идет в B1 и берет «Февраль». В третьей строке — «Март» из C1. Горизонтальная строка превратилась в вертикальный столбец. И никаких лишних действий.
Этот метод работает и в обратную сторону: из столбца можно сделать строку. Нужно просто поменять аргументы в АДРЕС. Вместо `АДРЕС(1; СТРОКА(…))` пишем `АДРЕС(СТОЛБЕЦ(…); 1)`.
Метод 2. Выпадающий список по умной таблице
Представьте, что у вас в столбце A записаны 20 разных фруктов. Вы делаете дневник питания и хотите, чтобы выпадающий список показывал только первые 5 фруктов. А завтра вы решили, что нужно 8 фруктов. Без специальных формул вам пришлось бы каждый раз заходить в настройки и менять диапазон вручную.
С функцией ДВССЫЛ это становится простой игрой.
Пошаговая инструкция:
- Запишите в ячейку C1 цифру 5 (сколько пунктов показывать).
- Выделите ячейку, где будет выпадающий список.
- Перейдите в меню «Данные» → «Проверка данных» (в старых версиях — «Проверка»).
- В открывшемся окне выберите тип «Список».
- В поле «Источник» вставьте формулу:
= ДВССЫЛ(«$A$1:$A$» & $C$1)
Как это работает. Excel смотрит на ячейку C1, видит там цифру 5 и подставляет ее в текст «A1:A».
Получается текст «A1:A5». ДВССЫЛ превращает этот текст в настоящий диапазон ячеек от A1 до A5. Выпадающий список показывает ровно эти пять фруктов.
Теперь поменяйте в C1 цифру 5 на 8. Формула внутри перестроится сама: «A1:A8». Список покажет уже 8 фруктов. Вы можете менять это число сколько угодно — и вам не придется лезть в настройки проверки данных. Это удобно и быстро.
Метод 3. Суммирование по интервалу
У вас есть таблица продаж за каждый день месяца. Данные записаны в столбце B: B1 — продажи первого дня, B2 — второго, и так до B31. Вы хотите быстро узнать сумму продаж за первые 10 дней. А завтра — за первые 15 дней. Обычный способ — каждый раз переписывать формулу: `=СУММ(B1:B10)`, потом `=СУММ(B1:B15)`. Это долго и неудобно.
Функция ДВССЫЛ в Excel решает проблему одной формулой.
В ячейку E1 вы пишете нужное количество дней (например, 10). В ячейку E2 вставляете формулу:
=СУММ(ДВССЫЛ(«$B$1:$B$» & $E$1))
Разберем цепочку действий. Excel видит знак & и соединяет текст «$B$1:$B$» со значением из ячейки E1. Если E1 = 10, получается текст «$B$1:$B$10». ДВССЫЛ превращает этот текст в настоящий диапазон ячеек от B1 до B10. А СУММ складывает все числа в этом диапазоне.
Теперь самое интересное. Вы меняете цифру в E1 на 25. Формула автоматически превращается в
=СУММ(ДВССЫЛ(«$B$1:$B$25»))
Сумма пересчитывается для 25 дней. Вам не нужно трогать саму формулу. Вы просто меняете одно число в E1 — и все.
Этот метод работает не только с СУММ, но и с СРЗНАЧ, МАКС, МИН и другими функциями, которые работают с диапазонами. Главное — правильно склеить текст адреса.
Метод 4. Сбор данных с нескольких листов
Представьте, что у вас в одной книге Excel есть три листа: «Оля», «Петя», «Саша». На каждом листе в ячейке C3 стоит оценка за контрольную работу. На отдельном листе «Сводка» вы хотите видеть все эти оценки напротив имен.
Обычная ссылка выглядит так:
=’Оля’!C3
Но если имен много, вводить каждую ссылку вручную скучно. ДВССЫЛ на другой лист автоматизирует этот процесс.
На листе «Сводка» в столбце А пишем имена: A1=Оля, A2=Петя, A3=Саша. В ячейку B1 вставляем формулу:
=ДВССЫЛ(«‘»&$A$1&»‘!$C$3»)
Разберём по частям. Знак & склеивает элементы:
- одинарную кавычку `’` (она нужна на случай, если в имени листа есть пробел),
- значение из ячейки A1 (имя листа),
- снова одинарную кавычку и восклицательный знак `’!`,
- адрес ячейки C3.
В итоге для A1 получается текст «’Оля’!$C$3». ДВССЫЛ читает этот текст как команду: «Иди на лист Оля, найди ячейку C3, возьми оттуда значение». Для Пети и Саши формула работает так же.
Протягиваем формулу из B1 вниз, до B3. Все готово. Если вы добавите четвертого ученика на лист «Лена», просто напишите «Лена» в A4 и протяните формулу еще на одну ячейку.
Важное предупреждение. Если вы хотите собрать данные на другую книгу (другой файл), формула усложнится. Например:
=ДВССЫЛ(«'[Книга2.xlsx]Лист1′!A1»)
В том случае, если файл-источник закрыт, Excel выдаст ошибку. Примеры ДВССЫЛ в Excel с другими книгами требуют, чтобы обе книги были открыты одновременно. Иначе магия не сработает.
Метод 5. Несбиваемые ссылки
Самая частая боль в Excel — случайное удаление строки или столбца. Вы убрали пустую строку 5, а все формулы, которые ссылались на ячейки ниже, превратились в ошибку #ССЫЛКА!. Потому что обычные ссылки привязаны к позиции, а не к тексту.
ДВССЫЛ Excel решает эту проблему. Сравните два подхода.
Обычная формула:
=A5
Вы удаляете строку 5. Данные, которые были в A5, смещаются в A4. Но формула `=A5` теперь смотрит на пустую ячейку (или на другие данные). Это плохо.
Формула с ДВССЫЛ:
=ДВССЫЛ(«A5»)
Вы удаляете строку 5. Excel не пересчитывает текст «A5». Он по-прежнему говорит программе: «Посмотри, что лежит в том месте, которое называлось A5». Если данные сместились, вы все равно получите нужное значение — просто с новой позиции.
Это свойство называют «несбиваемые ссылки». Оно спасает, когда вы часто вставляете или удаляете строки в больших таблицах.
Однако есть ограничения.
- ДВССЫЛ не умеет работать с ячейками по цвету заливки. Она видит только текст и числа, а цвет для нее невидим.
- Если вы переименуете лист, на который ссылаетесь, формула сломается. Потому что в тексте жестко прописано старое имя.
- ДВССЫЛ нельзя использовать для ссылок на закрытые книги.
Также помните, что ДВССЫЛ не дружит с другими динамическими функциями вроде СМЕЩ. Но для защиты простых ссылок от случайных удалений она подходит идеально. Особенно если вам нужно, чтобы формула всегда смотрела на столбец А — и это пожалуйста — ДВССЫЛ с этим справится без ошибок.
В связке с функцией ПОИСКПОЗ или ВПР, ДВССЫЛ помогает создавать сложные, но надежные конструкции. Например, вы можете искать нужное значение в любом столбце, просто меняя текст в ячейке.
Рекомендации
- ДВССЫЛ превращает текст в ссылку. Запомните это как главное правило.
- Она незаменима для выпадающих списков, сбора данных с разных листов и создания гибких сумм.
- Она защищает формулы от удаления строк, но боится переименования страницу (листа).
- Не используйте ее в тысячах ячеек — компьютер может замедлиться.
- Для работы с другой книгой обе книги должны быть открыты.
Теперь вы знаете секрет работающие и устойчивые ссылки. Применяйте ДВССЫЛ аккуратно, и ваши таблицы будут радовать вас надежностью и гибкостью.
