Функция ДВССЫЛ в Excel: описание и применение
Содержание
Работая в Microsoft Excel, каждый пользователь сталкивается с функциями. Одни очень простые, а другие предназначены для сложных, многокомпонентных вычислений. Существует также функция INDIRECT, которая в русскоязычном варианте программы называется ДВССЫЛ. Она не выполняет какие-либо вычисления, не находит значения по параметрам или условиям. Тем не менее эта функция очень полезна для каждого, кто работает в Excel. В этом мы убедимся на примерах, представленных в данном материале.
Как работает ДВССЫЛ
Основная задача данного инструмента — создание косвенных ссылок на ячейки, строки, столбцы или целые диапазоны. Также функция ДВССЫЛ может применяться при переносе значений на другой лист или в другую книгу. С ее помощью можно получить адрес и создать динамическую ссылку на определенное значение, не указывая их напрямую. Это позволяет менять ссылки, не изменяя содержимого формул, а также сохранять их даже в том случае, если ячейки-адресаты были заменены или удалены.
В общих случаях функция ДВССЫЛ имеет 2 аргумента:
- Ссылка на ячейку — адрес одной из клеток либо нужного диапазона (обязательный атрибут функции).
- Логическое значение для заданной ссылки — необязательный аргумент. При значении ИСТИНА (по умолчанию) запись воспринимается как прямая ссылка на ячейку. Если установить значение ЛОЖЬ — запись воспринимается, как ссылка на ячейку с другой ссылкой.
Необязательный аргумент функции используется крайне редко. Для выполнения большинства операций достаточно явного указания адреса ячеек со значением. Понять принцип работы данной функции проще всего на простом примере. Предположим в таблице есть ячейка с числовым значением (в нашем случае А1). При помощи функции ДВССЫЛ можно сделать ссылку в любом другом месте таблицы.
Стоит обратить внимание на важный нюанс. При указании аргумента адрес ячейки А1 указан в кавычках. Это связано с тем, что ссылка создается конкретно для ячейки, а не ее содержимое. Поэтому если поле, на которое ссылается формула, было смещено, например в результате добавления новых строк, результат в клетке, в которой прописан ДВССЫЛ также изменится.
Без функции ссылка на значение ячейки А1 сохранилось бы независимо от того, куда она перенесена. Адрес автоматически меняется при добавлении новых строк или при других вариантах смещения. Поэтому функция ДВССЫЛ удобно использовать в том случае, если нужно получить строгую, неизменяемую ссылку на ячейку, а не на конкретное значение, адрес которого может измениться.
Варианты применения
Очевидно, что функция ДВССЫЛ в редакторе Excel имеет широкую область применения. Ее можно использовать для любых операций с различными ссылочными данными и изменяемыми значениями. Чтобы разобраться, как работает ДВССЫЛ в Excel, рассмотрим примеры использования данной формулы ниже в этой статье. Они позволят понять на практике особенности ее работы.
Пример 1: получаем значение по ссылке на ячейку-источник
Один из популярных вариантов использования ДВССЫЛ — получение значений из ячейки по ссылке, указанной в другой ячейке. К примеру, в клетке А1 имеется данные, в нашем случае в виде числа. В С1 в качестве значения указан адрес клетки А1, причем без каких-либо дополнительных символов. При помощи функции ДВССЫЛ можно получить данные из А1 через промежуточную ссылку в С1.
В данном случае при указании аргументов прописывать кавычки не требуется. Программа самостоятельно определяет содержимое, как ссылку в текстовом формате.
Пример 2: поворот строк
Одна из очень распространенных задач в Экселе — превращение вертикального диапазона превратить в горизонтальный. Данная операция называется транспонированием. Существует множество вариантов решения подобной задачи. Один из простейших инструментов транспонирования — формула с ДВССЫЛ.
Для примера, в таблице присутствует определенное количество заполненных строк. В нашем случае это числа от 1 до 10.
Применяем формулу ДВССЫЛ. В качестве аргумента указывается весь столбец «А». Программа автоматически получит ссылки для всех заполненных ячеек в данном диапазоне. Далее необходимо использовать спецсимвол «&», отвечающий за склейку, и прописать оператор «столбец».
Пример 3: создание ссылок на диапазон
В программе Microsoft Excel функция INDIRECT подходит для создания косвенных ссылок на любое количество ячеек. Это удобный инструмент, который позволяет существенно упростить применения формул в том случае, когда в качестве аргументов необходимо вставлять множество значений.
Рассмотрим, как работает данный инструмент в случае с диапазонами. Прежде всего нужно определить ссылку на несколько ячеек с использованием функции ДВССЫЛ. В нашем случае речь идет о столбце, где в качестве значений каждой клетки указаны числа. Ссылку на диапазон можно вставить в любую функцию. К примеру, посчитаем значение всех чисел при помощи функции СУММ. Для этого ссылку на диапазон добавляем в качестве аргумента.
Очевидно, что в случае с функцией СУММ использовать ссылку на диапазон не всегда целесообразно. В качестве аргументов явно указываются адрес диапазона, в котором необходимо выполнить вычисления. Однако здесь существенное значение имеет тот факт, что при изменении положения клеток с числовыми значениями результат также будет меняться. Об этом уже было сказано выше в этом материале. Формула СУММ применяется только к заданным ссылкам и за счет функции ДВССЫЛ изначальные значения не сохраняются.
Пример 4: ссылки на именованные диапазоны
При наличии в листе именованного диапазона, к нему можно обращаться при помощи функции ДВССЫЛ. В качестве примера приведем таблицу с данными, которая выглядит следующим образом:
Для создания именованного диапазона, выбираем необходимые ячейки. Название нужно прописать в блоке, находящемся в левой части таблицы под Панелью инструментов. Для примера, в столбце «С» описан вес, поэтому озаглавим его соответствующим образом.
После этого можно использовать функцию ДВССЫЛ, чтобы обращаться к нужному именованному диапазону при заполнении формул. Для примера, посчитаем средний рост и пользователей, занесенных в таблицу. Используем формулу СРЗНАЧ в качестве аргумента, которой указываем функцию ДВССЫЛ с аргументом «ИМЯ_ДИАПАЗОНА».
Получение данных из другого листа
Рассматривая примеры функции ДВССЫЛ в Excel, обратим внимание на еще одну полезную особенность данного инструмента. С его помощью можно быстро получить данные из таблицы, оформленной на другом листе. Для примера, у нас присутствует таблица с определенным набором данных на листе 1. Значения любых ячеек можно получить при помощи специальной ссылки.
Переходим на пустой лист. Для примера, необходимо получить значения из ячейки А1 на первом листе таблицы. В данном случае прописываем формулу ДВССЫЛ. В качестве аргумента указываем название листа, а также адрес клетки, из которой нужно получить значение. Между названием листа и адресом ячейки должен присутствовать разделитель в виде восклицательного знака.
В результате выполнения будет отображено значение из заданного адреса в первом листе таблицы. Способ очень удобен тем, что при изменении данных в первой таблице, во второй они будут автоматически подстраиваться под внесенные коррективы.
Похожим образом можно получить данные из определенного диапазона ячеек. В нашем примере предусмотрена небольшая таблица, которую можно перенести на новый лист полностью. Для этого используем функцию ДВССЫЛ, а в качестве аргумента указываем название листа и диапазон заполненных ячеек.
В результате выполнения будет заполнена только та ячейка, в которой прописана формула. Однако растянув маркер заполнения, будут получены значения и для остальных клеток.
Таким образом, мы получим динамическую копию таблицы из первого листа. В том случае, если в оригинальной таблице одно из значений будет удалено, либо скорректировано, те же самые изменения будут применены и в дубликате, полученном через ДВССЫЛ.
Что нужно учитывать при использовании
Функция ДВССЫЛ является довольно специфическим инструментом. Ее использование не во всех случаях оправдано, а нарушение логики ее работы может стать причиной различных ошибок при выполнении вычислительных операций.
Первое, что следует учесть — функция ДВССЫЛ создает относительную ссылку. Поэтому если применить данную формулу к одной ячейке, ее нельзя растянуть на остальные. Значение будет просто копироваться из заданного адреса.
Эта проблема частично решаема, если в качестве аргумента указывать не одну клетку, а диапазон строк, данные из которых нужно получить. Такой способ удобен при условии, что количество необходимых ссылок известно заранее. В ином случае можно получить клетки с вариантом «#ЗНАЧ!». К слову, аналогичный способ действует не только с числовыми значениями. Он вполне применим для строк и столбцов с текстовыми данными.
Функция ДВССЫЛ относится к числу волатильных. Это означает, что значение в ячейке с данной формулой постоянно пересчитывается. Подобные формулы следует использовать максимально осторожно и внимательно, чтобы не потерять какие-либо промежуточные данные. Эксперты программ Microsoft Office настоятельно рекомендуют не использовать волатильные функции там, где в них отсутствует прямая необходимость.
Один из недостатков функции ДВССЫЛ — нагрузка на вычислительные ресурсы устройства. Значения, как уже было сказано выше, систематически обновляются, поэтому постоянно происходят расчетные операции. В случае с небольшими документами это незаметно. Однако если таблица большая, в ней присутствуют большие числа, множество ссылок, перекрестных функций, использование такой формулы может оказать негативное влияние на быстродействие.
Еще один нюанс, который нужно учитывать. Если функция применяется к данным из другого листа или документа, она остается доступной для использования только пока исходная таблица открыта. Если ее закрыть или удалить, вместе ячейки с формулой ДВССЫЛ появится ошибка #ССЫЛКА!. При работе с именованными диапазонами обратите внимание на то, что он должен быть статическим. Ссылку на динамический именованный диапазон создать невозможно.
Подводим итоги
Функция ДВССЫЛ, или ее английский вариант INDIRECT — полезный инструмент, позволяющий получить ссылку на определенную ячейку или диапазон. С ее помощью можно выполнять множество различных операций, связанных с извлечением данных, оформлением формул и переносом значений между листами или документами. Вариантов применения такой функции очень много, однако при ее использовании необходимо соблюдать ряд правил, чтобы исключить риск возникновения ошибок.