Функция ДВССЫЛ в Excel используется, когда у вас есть текстовые ссылки, и вы хотите получить значения из этих ссылок.
Что возвращает функция
Функция возвращает ссылку, заданную текстовой строкой.
Синтаксис
=ДВССЫЛ(ref_text,[a1]) — английская версия
=ДВССЫЛ(ссылка_на_текст;[a1]) — русская версия
Аргументы функции
- ref_text (link_to_text) — текстовая строка, содержащая ссылку на ячейку или именованный диапазон;
- [a1] — логическое значение, указывающее тип ссылки, используемой в аргументе ref_text (link_to_text). Значения аргументов могут быть ИСТИНА (ссылка в формате «А1») или ЛОЖЬ (ссылка в формате «R1C1»). Если вы не укажете этот аргумент, Excel автоматически установит для него значение TRUE.
Дополнительная информация
- Функция ДВССЫЛ является энергозависимой функцией (используйте ее с осторожностью);
- Пересчитывает значения каждый раз, когда вы открываете файл Excel и каждый раз, когда выполняется расчет в электронной таблице Excel;
- Поскольку изменчивые функции постоянно обновляются и выполняют вычисления, это, в свою очередь, замедляет работу вашего файла Excel.
- Аргумент текстовой ссылки может выглядеть так:
— ссылка на ячейку, содержащая ссылку на ячейку в формате «A1» или «R1C1».
— ссылка на ячейку в двойных кавычках.
— именованный диапазон, который возвращает ссылку
Примеры использования функции ДВССЫЛ в Excel
Пример 1. Используем ссылку на ячейку для получения значения
Функция ДВССЫЛ принимает ссылку на ячейку в качестве входных данных и возвращает значение ячейки в этой ссылке (как показано в следующем примере):
Формула в ячейке C1:
=ДВССЫЛ(«A1») — английская версия
=ДВССЫЛ(«А1») — Русская версия
Функция берет ссылку на ячейку (заключенную в двойные кавычки) и возвращает значение этой ячейки, равное «123”.
Вам может быть интересно, почему мы просто не используем «=A1» вместо использования функции ДВССЫЛ)?
И вот почему…
В этом случае, если вы введете формулу «=A1» или «=$A$1» в ячейку C1, вы получите тот же результат, что и в ячейке A1. Но если вы вставите строку выше в таблицу, вы можете заметить, что ссылка на ячейку изменится автоматически.
Эта функция очень полезна, если вы хотите заблокировать ссылку на ячейку таким образом, чтобы она не менялась при вставке строк/столбцов на лист.
Пример 2. Получаем данные по ссылке на ячейку
Также с помощью функции можно извлечь из ячейки значения, ссылка на которые хранится в самой ячейке.
В приведенном выше примере ячейка «A1» содержит число «123”.
Ячейка «С1» относится к ячейке «А1».
Теперь с помощью функции можно указать ячейку C1 в качестве аргумента функции, которая в результате вернет значение ячейки A1.
Важный. Вам не нужно использовать кавычки в ячейке C1, значение ссылки на ячейку должно быть в текстовом формате.
Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)
Вы можете создать ссылку на ячейку, используя комбинацию текстовых и числовых значений.
Например, если ячейка С1 содержит число «2», по формуле =ДВССЫЛ(«А» и С1) или =ДВССЫЛ(«А» и С1) вы получите ссылку на значение ячейки «А2».
Практическое применение этого метода актуально, когда вы хотите динамически ссылаться на ячейки на основе значения в какой-то другой ячейке.
Пример 4. Ссылаемся на диапазон ячеек с помощью функции INDIRECT (ДВССЫЛ)
С помощью функции вы можете ссылаться на диапазон ячеек.
Например, =ДВССЫЛ(«A1:A5») или =ДВССЫЛ(«A1:A5») будет ссылаться на данные в диапазоне ячеек «A1:A5».
Используя вместе функции СУММ (SUM) и INDIRECT (ДВССЫЛ) можно вычислить сумму, а также максимальное и минимальное значения диапазона.
Пример 5. Ссылаемся на именованный диапазон значений с использованием функции INDIRECT (ДВССЫЛ)
Если вы создали именованный диапазон в Excel, вы можете запросить его с помощью функции ДВССЫЛ).
Например, предположим, что у вас есть оценки 5 учащихся по трем предметам, как показано ниже:
Назовем следующие клетки:
- B2:B6: Математика
- C2:C6: Физика
- D2:D6: Химия
Чтобы дать имя любому диапазону значений, просто выберите ячейки и дайте им имя в соответствующем поле:
После этого вы можете запросить указанные диапазоны по формуле:
=ДВССЫЛ(«Именованный диапазон») — английская версия
= ДВССЫЛ(«Именованный диапазон
Например, если вы хотите узнать средний балл среди студентов-математиков, используйте следующую формулу:
=СРЗНАЧ(ДВССЫЛ(«Математика»)) — английская версия
=СРЗНАЧ(ДВССЫЛ(«Математика»)) — русская версия
Если имя диапазона находится в ячейке («F2» в приведенном ниже примере отображается как «Mat»), вы можете использовать ссылку на ячейку непосредственно в формуле. В следующем примере показано, как вычислить среднее значение с использованием именованных диапазонов.
Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)
С помощью этой функции вы можете создать зависимый выпадающий список.
Например, предположим, что у вас есть два столбца с именами «Россия» и «США», строки содержат города этих стран, как показано в следующем примере:
Чтобы создать зависимый выпадающий список, вы должны создать два именованных диапазона для ячеек «A2: A5» с именем «Россия» и для ячеек «B2: B5» с именем «США”.
Теперь в ячейке «D2» создайте выпадающий список для «Россия» и «США». Это создаст первый раскрывающийся список, в котором пользователь может выбрать одну из двух стран.
Теперь, чтобы создать зависимый выпадающий список:
- Выберите ячейку E2 (или любую другую ячейку, для которой вы хотите создать зависимый раскрывающийся список);
- Нажмите на вкладку «Данные» -> «Проверка данных”;
- На вкладке «Настройки» в разделе «Разрешить» выберите Список;
- В разделе «Источник» укажите ссылку: =ДВССЫЛ($D$2) или =ДВССЫЛ($D$2);
- Нажмите ОК
Теперь, если в первом выпадающем списке выбрать, например, страну «Россия», то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Такая же ситуация, если выбрать страну «США» из первого выпадающего списка.
Узнайте больше полезных советов по работе со списками данных и функциями в Excel в практическом курсе от новичка до мастера Excel. Смело регистрируйтесь по ссылке!