В этой статье объясняется как работает функция ДВССЫЛ, указаны основные способы ее использования.
Мы разберем синтаксис функции ДВССЫЛ и основные способы ее использования.
Функция ДВССЫЛ (INDIRECT в английской версии) выделяется своей способностью не выполнять вычислений, не оценивать условия и не искать значения. Она служит для создания динамических ссылок.
Функция ДВССЫЛ в Excel — синтаксис
ДВССЫЛ позволяет создать динамическую ссылку и использовать её внутри формулы. Это означает, что вы можете изменить ссылку в формуле, не редактируя саму формулу.
Кроме того, созданные таким образом ссылки остаются неизменными при вставке новых строк или столбцов или удалении существующих. Формула всегда будет ссылаться на одни и те же ячейки или диапазон.
Прежде чем рассматривать пример, постараемся разобраться, как работает функция ДВССЫЛ.
Синтаксис ДВССЫЛ в Excel выглядит следующим образом:
Функция ДВССЫЛ возвращает ссылку на ячейку, адрес который записан в текстовой строке.
ДВССЫЛ(ссылка_на_ячейку; [a1])ссылка_на_ячейку – это ссылка в виде текстовой строки, либо название именованного диапазона.
a1 — логическое значение, указывающее, какой тип ссылки содержится в первом аргументе:
- Если значение ИСТИНА или опущено, то используется ссылка на ячейку в стиле A1.
- Если ЛОЖЬ, то возвращается ссылка в виде R1C1.
Хотя тип ссылки R1C1 может быть полезен в определенных ситуациях, вам, вероятно, удобнее использовать привычные ссылки типа A1. Так как этот аргумент является необязательным, мы будем просто его опускать и работать с привычными нам ссылками.
Как работает функция ДВССЫЛ
Для начала создадим простую формулу, чтобы показать, как можно применить ДВССЫЛ в Excel.
Предположим, на рабочем листе записано число 5 в ячейке A1 и текст «A1» в ячейке C1. Теперь поместите формулу
=ДВССЫЛ(C1)в любую другую ячейку и посмотрите, что произойдет:
- Функция ДВССЫЛ обращается к значению в ячейке C1, которое представляет собой текст «A1».
- Функция направляется в ячейку A1, где она выбирает ее значение, то есть число 5.
Итак, в этом примере функция ДВССЫЛ преобразует текст в ссылку на ячейку.
Как видно из предыдущего примера, функция ДВССЫЛ позволяет использовать текст для создания динамической ссылки на другую ячейку и получения её значения. Но возможности этой функции гораздо больше.
Мы можем предварительно создавать даже самые сложные ссылки в виде текста. Это может быть сложный текст, созданный из значений других ячеек и результатов, возвращаемых другими формулами Excel. Далее покажем это на примерах.
Создание ссылок из значений ячеек
Функция ДВССЫЛ в Excel может использовать стили ссылок A1 и R1C1. Обычно в Excel нельзя одновременно использовать оба стиля. Вы можете переключаться между ними через настройки «Файл» > «Параметры» > «Формулы» > R1C1. Согласитесь что это не очень удобно.
В формуле ДВССЫЛ вы можете использовать любой тип ссылки на одном и том же листе.
Давайте более подробно рассмотрим разницу между стилями ссылок A1 и R1C1.
Стиль A1: это обычный тип ссылки в Excel, где сначала указан столбец, затем номер строки. Например, B2 — это ячейка на пересечении столбца B и строки 2.
Стиль R1C1: в этом стиле координаты ячейки указаны наоборот — сначала номер строки, затем столбец. Например, R5C1 указывает на ячейку A5 (строка 5, столбец 1). Если после буквы нет никакой цифры, это означает ссылку на ту же строку или столбец, где записана сама формула.
А теперь покажем это на простом примере, как функция ДВССЫЛ обрабатывает ссылки вида A1 и R1C1:
Как видите, две разные формулы ДВССЫЛ возвращают один и тот же результат.
Формула в ячейке D1:
=ДВССЫЛ(C1)Эта простая формула обращается к ячейке C1. Берет из нее значение — текстовую строку «A2». Преобразует ее в ссылку, идет к ячейке A2 и возвращает ее значение 456. Формула в ячейке D3:
=ДВССЫЛ(C3;ЛОЖЬ)Значение ЛОЖЬ во втором аргументе указывает, что текст из ячейки C3 нужно рассматривать как ссылку вида R1C1. Таким образом, функция ДВССЫЛ преобразует текст из C3 («R2C1») в ссылку на ячейку на пересечении строки 2 и столбца 1, то есть A2.
Создание ссылок из значений ячеек и текста
Подобно тому, как создаются ссылки из значений ячеек, вы можете комбинировать текстовые строки и ссылки на ячейки прямо в формуле ДВССЫЛ с помощью оператора объединения (&).
Рассмотрим пример, в котором формула =ДВССЫЛ(«А»&C1) возвращает значение из ячейки A1 на основе следующей логической цепочки:
· Функция ДВССЫЛ объединяет элементы в первом аргументе ссылка_на_ячейку — текст «А» и значение из ячейки C1.
· Значение в ячейке C1 — это число 1, что в результате формирует текстовую ссылку на ячейку A1.
· Формула переходит к ячейке A1 и возвращает ее значение — 555.
Использование функции ДВССЫЛ с именованными диапазонами
Кроме создания ссылок на ячейки, можно использовать функцию ДВССЫЛ для создания ссылок на именованные диапазоны.
Предположим, у вас есть следующие именованные диапазоны на вашем листе:
- Яблоки – С2:E2
- Лимоны — C3: E3
- Апельсины – C4:E4 и так далее по каждому товару.
Для создания динамической ссылки Excel на любой из указанных выше диапазонов:
- запишите его имя в любую ячейку, скажем, H1,
- обратитесь к этой ячейке с помощью формулы:
=ДВССЫЛ(H1)Теперь вы можете пойти еще дальше и использовать эту формулу внутри других функций Excel. Например, чтобы вычислить сумму и среднее значение в заданном именованном диапазоне. Или найдем максимальное и минимальное значение продаж в нём:
=СУММ(ДВССЫЛ (H1))=СРЗНАЧ(ДВССЫЛ (H1))=МАКС(ДВССЫЛ (H1))=МИН(ДВССЫЛ (H1))
Еще больше информации по работе с этой функцией вы можете найти здесь: Примеры использования функции ДВССЫЛ в Excel.
Функция ДВССЫЛ Excel — возможные ошибки и проблемы
Из этих примеров видно, что функция ДВССЫЛ (INDIRECT) в Excel чрезвычайно полезна для работы со ссылками на ячейки и диапазоны. Однако не все пользователи легко принимают этот метод. Ведь постоянное использование ДВССЫЛ в формулах Excel затрудняет понимание смысла формул.
Вы не видите формуле адрес ячейки, из которой она берет данные. Это делает понимание формулы сложным, особенно если она большая и сложная.
Основные ограничения функции ДВССЫЛ (INDIRECT)
Ссылки на внешние файлы. Если создана ссылка на другую книгу Excel, функция будет работать только при открытом исходном файле. Если он закрыт, вы получите ошибку #ССЫЛКА!
Динамические именованные диапазоны. К сожалению, при помощи функции ДВССЫЛ вы не можете создать ссылку на динамический именованный диапазон, который автоматически расширяется при добавлении новых данных.. Однако ссылки на обычные именованные диапазоны работают без проблем.
Волатильность функции. ДВССЫЛ является волатильной функцией. Это означает, что она пытается заново сформировать ссылку при любом изменении любой ячейки рабочего листа. Это может замедлить вычисления, особенно при работе с большими таблицами.
Распространенные ошибки и проблемы
Ошибка #ССЫЛКА!
Недопустимая ссылка. Если аргумент ссылка_на_ячейку записан некорректно, например «A1B», формула вернет ошибку #ССЫЛКА!. Убедитесь, что все аргументы верны.
Превышен допустимый размер диапазона. Если полученная при помощи формулы ДВССЫЛ ссылка выходит за пределы существующих ограничений строк и столбцов в Excel (не более чем 1 048 576 строк и 16 384 столбца), вы получите ошибку #ССЫЛКА.
Закрытые файлы. Если функция ДВССЫЛ ссылается на ячейки в другой книге Excel, этот файл должен быть открыт, иначе появится ошибка #ССЫЛКА!
Ошибка #ИМЯ?
Самый очевидный случай, если вы ошиблись в названии функции.
Ошибка из-за региональных настроек
В разных странах используют разные разделители для списка и десятичных знаков. В европейских странах запятую используют как десятичный знак, а точку с запятой — как разделитель списка. В Северной Америке всё наоборот. Поэтому если вы создали текстовую строку ссылки для одного регионального стандарта, то она может не подойти для другого. Вы вновь получите ошибку #ССЫЛКА. В этом случае просто замените все запятые точками с запятой, или наоборот.
Чтобы узнать, какие разделители нужно использовать, откройте панель управления Windows и перейдите в раздел «Регион и язык» > «Дополнительные настройки».
Часто задаваемые вопросы (FAQ)
Надеемся, что это руководство помогло вам лучше понять, как можно использовать функцию ДВССЫЛ в Excel. Теперь вы знаете её сильные стороны, возможности и ограничения.
Эти статьи также могут быть полезны: