Функция INDIRECT (ДВССЫЛ) в Excel. Как использовать?

Logo PDF шпаргалка с ТОП горячих клавиш в Excel Получить

Функция INDIRECT (ДВССЫЛ) в Excel используется когда у вас есть ссылки в виде текста, и вы хотите получить значения из этих ссылок.

Что возвращает функция

Функция возвращает ссылку, заданную текстовой строкой.

Синтаксис

=INDIRECT(ref_text, [a1]) — английская версия

=ДВССЫЛ(ссылка_на_текст;[a1]) — русская версия

Аргументы функции

  • ref_text (ссылка_на_текст) — текстовая строка, которая содержит в себе ссылку на ячейку или именованный диапазон;
  • [a1] — логическое значение, которое определяет тип ссылки используемой в аргументе ref_text (ссылка_на_текст). Значения аргумента могут быть TRUE (ссылка указана в формате «А1») или FALSE (ссылка указана в формате «R1C1»). Если не указать этот аргумент, то Excel автоматически определит его значение как TRUE.

Дополнительная информация

  • Функция INDIRECT (ДВССЫЛ) это волатильная функция (используйте с осторожностью);
  • Она пересчитывает значения каждый раз, когда вы открываете Excel файл, и каждый раз когда вычисление запускается на рабочем листе Excel;
  • Так как волатильные функции постоянно обновляются и производят вычисления, это, в свою очередь, замедляет работу вашего Excel файла.
  • Аргумент текстовой ссылки может выглядеть как:
    — ссылка на ячейку, которая содержит ссылку на ячейку в формате «A1» или «R1C1».
    — ссылка на ячейку в двойных кавычках.
    — именованный диапазон, возвращающий ссылку

Примеры использования функции ДВССЫЛ в Excel

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

Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):

INDIRECT (ДВССЫЛ) в Excel - 1

Формула в ячейке С1:

=INDIRECT(“A1”) — английская версия

=ДВССЫЛ(«A1») — русская версия

Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.

Вы можете спросить — почему бы нам просто не использовать «=A1» вместо использования функции INDIRECT (ДВССЫЛ)?

И вот почему…

Если в данном случае вы введете в ячейку С1 формулу “=A1” или “=$A$1”, то она выдаст вам тот же результат, что находится в ячейке А1. Но если вы вставите в таблице строку выше, вы можете заметить, что ссылка на ячейку будет автоматически изменена.

Функция очень полезна, если вы хотите заблокировать ссылку на ячейку таким образом, чтобы она не изменялась при вставке строк / столбцов в рабочий лист.

Пример 2. Получаем данные по ссылке на ячейку

Также, с помощью функции вы можете извлечь значения из ячейки, ссылка на которую хранится в самой ячейке.

INDIRECT (ДВССЫЛ) в Excel - 2

На примере выше, ячейка «А1» содержит в себе число “123”.

Ячейка «С1» ссылается на ячейку «А1».

Теперь, используя с помощью функции вы можете указать ячейку С1 как аргумент функции, который выведет по итогу значение ячейки А1.

Важно. Вам не нужно использовать кавычки в ячейке С1, значение ссылки на ячейку должно быть указано в текстовом формате.

Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)

Вы можете создать ссылку на ячейку используя комбинацию текстовых и числовых значений.

Например, если в ячейке С1 указано число “2”, то используя формулу =INDIRECT(“A”&C1) или =ДВССЫЛ(«A»&C1) вы получите ссылку на значение ячейки «А2».

Функция INDIRECT (ДВССЫЛ) в Excel - 3

Практическое применение этого способа актуально, когда вы хотите динамически ссылаться на ячейки на основе значения в какой-либо другой ячейке.

Пример 4. Ссылаемся на диапазон ячеек с помощью функции INDIRECT (ДВССЫЛ)

С помощью функции вы можете ссылаться на диапазон ячеек.

Например, =INDIRECT(“A1:A5”) или =ДВССЫЛ(«A1:A5») будет ссылаться на данные из диапазона ячеек «A1:A5».

Используя функцию SUM (СУММ) и INDIRECT (ДВССЫЛ) вместе, вы можете рассчитать сумму, а также максимальные и минимальные значения диапазона.

Функция INDIRECT (ДВССЫЛ) в Excel - 4

Пример 5. Ссылаемся на именованный диапазон значений с использованием функции INDIRECT (ДВССЫЛ)

Если вы создали именованный диапазон в Excel, вы можете обратиться к нему с помощью функции INDIRECT (ДВССЫЛ).

Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:

Функция INDIRECT (ДВССЫЛ) в Excel - 5

Зададим для следующих ячеек названия:

  • B2:B6: Математика
  • C2:C6: Физика
  • D2:D6: Химия

Для того чтобы задать название любому диапазону значений, достаточно выбрать ячейки и присвоить им название в соответствующем боксе:

Функция INDIRECT (ДВССЫЛ) в Excel

После этого вы можете обратиться к указанным диапазонам с помощью формулы:

=INDIRECT(“Именованный диапазон”) — английская версия

=ДВССЫЛ(“Именованный диапазон”) — русская версия

Например, если вы хотите узнать средний балл среди студентов по математике — используйте следующую формулу:

=AVERAGE(INDIRECT(“Математика”)) — английская версия

=СРЗНАЧ(ДВССЫЛ(«Математика»)) — русская версия

Если имя диапазона указано в ячейке («F2» в приведенном ниже примере указан как “Матем”), вы можете использовать ссылку на ячейку прямо в формуле. В следующем примере показано, как вычислять среднее значение с использованием именных диапазонов.

Функция INDIRECT (ДВССЫЛ) в Excel

Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)

C помощью этой функции вы можете создавать зависимый выпадающий список.

Например, предположим, что у вас есть две колонки с названиями «Россия» и «США», в строках указаны города этих стран, как указано на примере ниже:

Функция Indirect (ДВССЫЛ) в Excel

Для того, чтобы создать зависимый выпадающий список вам нужно создать два именованных диапазона для ячеек «A2:A5» с именем “Россия” и для ячеек «B2:B5» с названием “США”.

Теперь, в ячейке «D2» создайте выпадающий список для «России» и «США». Так мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

функция INDIRECT (ДВССЫЛ) в Excel

Теперь, для создания зависимого выпадающего списка:

  • Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
  • Кликните по вкладке “Data” -> “Data Validation”;
  • На вкладке “Настройки” в разделе “Allow” выберите List;
  • В разделе “Source” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2);Функция INDIRECT (ДВССЫЛ) в Excel. Как использовать?
  • Нажмите ОК

Теперь, если вы выберите в первом выпадающем списке, например, страну «Россия», то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Такая же ситуация, если вы выберите страну «США» из первого выпадающего списка.

функция INDIRECT (ДВССЫЛ) в Excel

VK Logo Больше лайфхаков в нашем ВК Подписаться
Оцени запись
Написать комментарий

Подпишись на наш Telegram канал

с лайфхаками в Excel

blank