Эксель двссыл как работает

Функция INDIRECT (ДВССЫЛ) в 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. Используем ссылку на ячейку для получения значения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Разбор функции ДВССЫЛ (INDIRECT) на примерах

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.

«Ну ОК», — скажете вы. «И что тут полезного?».

Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.

Пример 1. Транспонирование

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN) .

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE) , то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET) . Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM) :

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

Пример 4. Несбиваемые ссылки

Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:

Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

Допустим, что форма, размеры, положение и последовательность товаров и месяцев во всех таблицах одинаковые — различаются только числа.

Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

Рассмотрим примеры использования функции ДВССЫЛ в Excel, которая позволяет преобразовывать текст из ячейки в ссылку.

Функция ДВССЫЛ чрезвычайно полезна тем, что при использовании данной функции есть возможность изменять ссылки на ячейки и диапазоны в формуле не изменяя при этом саму формулу.
Другими словами, введенная формула =B2 идентична формуле =ДВССЫЛ("B2"), однако в первом варианте мы оперируем ссылкой, а во втором — текстом, который можно изменять.

Описание функции ДВССЫЛ

ДВССЫЛ(ссылка_на_ячейку; [a1])
Возвращает ссылку, заданную текстовой строкой.

• Ссылка на ячейку(обязательный аргумент) — ссылка в виде текста вида A1 или R1C1;
• A1(необязательный аргумент) — вид ссылки, в случае когда аргумент принимает значение ИСТИНА (или опущен), то ссылка трактуется как вид A1, когда принимает значение ЛОЖЬ, то как вид R1C1.

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

Пример 1. Ссылка на ячейку

Начнем с простой задачи, который мы уже частично разобрали.
Введем произвольное значение в ячейку A1, теперь чтобы сделать ссылку на ячейку введем формулу =ДВССЫЛ("A1"), например, в ячейку A2:

Пример 2. Ссылка на другой лист

Немного усложним задачу, и применим формулу ДВССЫЛ для ссылки на другой лист.
Перейдем на любой другой лист книги и вводим формулу =ДВССЫЛ("Пример_1!A1"), где лист Пример_1 — лист из первого примера:

Пример 3. Функции

Рассмотрим примеры с одновременным применением функции ДВССЫЛ и других функций.

Функция СУММ

Предположим, что у нас имеется таблицами с помесячными данными о продажах фруктов за 12 месяцев, и мы хотим просуммировать продажи накопленным итогом за переменное количество месяцев:

Функция СУММ с прямой ссылкой на диапазон решает эту задачу, например, можно применить формулу =СУММ(B2:B5) для подсчета продаж апельсинов.
Однако тогда при изменении периода нам придется менять и диапазон в исходной формуле.
Обойдем эту проблему записав диапазон в текстовом виде с использованием ссылок на другие ячейки — запишем формулу =СУММ(ДВССЫЛ(B15&2&":"&B15&(1+$A16))), где ячейка A16 отвечает за номер периода:

Расписывая по шагам данную формулу, мы в конце получим формулу =СУММ(B2:B5), что нам и требовалось.

Функция ПОИСКПОЗ

Рассмотрим еще один пример. Предположим, что у нас есть результаты тестирования группы кандидатов и критерии их оценки:

Записываем в оценку кандидатов формулу =ДВССЫЛ("G"&ПОИСКПОЗ(B2;$F$1:$F$6;1)), где с помощью функции ПОИСКПОЗ находим относительное положение оценки кандидата в критерии оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для каждого кандидата.

Обратите внимание, что функция ДВССЫЛ не работает, если ссылка указывает на ячейку или диапазон в закрытой книге.
Подробно ознакомиться со всеми разобранными примерами — скачать пример.

Функция ДВССЫЛ в Microsoft Excel

Одной из встроенных функций программы Excel является ДВССЫЛ. Её задача состоит в том, чтобы возвращать в элемент листа, где она расположена, содержимое ячейки, на которую указана в ней в виде аргумента ссылка в текстовом формате.

Казалось бы, что ничего особенного в этом нет, так как отобразить содержимое одной ячейки в другой можно и более простыми способами. Но, как оказывается, с использованием данного оператора связаны некоторые нюансы, которые делают его уникальным. В некоторых случаях данная формула способна решать такие задачи, с которыми другими способами просто не справиться или это будет гораздо сложнее сделать. Давайте узнаем подробнее, что собой представляет оператор ДВССЫЛ и как его можно использовать на практике.

Применение формулы ДВССЫЛ

Само наименование данного оператора ДВССЫЛ расшифровывается, как «Двойная ссылка». Собственно, это и указывает на его предназначение – выводить данные посредством указанной ссылки из одной ячейки в другую. Причем, в отличие от большинства других функций, работающих со ссылками, она должна быть указана в текстовом формате, то есть, выделена с обеих сторон кавычками.

Данный оператор относится к категории функций «Ссылки и массивы» и имеет следующий синтаксис:

Таким образом, формула имеет всего два аргумента.

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

Аргумент «A1» не является обязательным и в подавляющем большинстве случаев его вообще не нужно указывать. Он может иметь два значения «ИСТИНА» и «ЛОЖЬ». В первом случае оператор определяет ссылки в стиле «A1», а именно такой стиль включен в Excel по умолчанию. Если значение аргумента не указывать вовсе, то оно будет считаться именно как «ИСТИНА». Во втором случае ссылки определяются в стиле «R1C1». Данный стиль ссылок нужно специально включать в настройках Эксель.

Если говорить просто, то ДВССЫЛ является своеобразным эквивалентом ссылки одной ячейки на другую после знака «равно». Например, в большинстве случаев выражение

будет эквивалентно выражению

Но в отличие от выражения «=A1» оператор ДВССЫЛ привязывается не к конкретной ячейке, а к координатам элемента на листе.

Рассмотрим, что это означает на простейшем примере. В ячейках B8 и B9 соответственно размещена записанная через «=» формула и функция ДВССЫЛ. Обе формулы ссылаются на элемент B4 и выводят его содержимое на лист. Естественно это содержимое одинаковое.

Добавляем в таблицу ещё один пустой элемент. Как видим, строки сдвинулись. В формуле с применением «равно» значение осталось прежним, так как она ссылается на конечную ячейку, пусть даже её координаты и изменились, а вот данные выводимые оператором ДВССЫЛ поменялись. Это связано с тем, что он ссылается не на элемент листа, а на координаты. После добавления строки адрес B4 содержит другой элемент листа. Его содержимое теперь формула и выводит на лист.

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

Нужно отметить, что оператор применим для ссылок на другие листы и даже на содержимое других книг Excel, но в этом случае они должны быть запущены.

Теперь давайте рассмотрим конкретные примеры применения оператора.

Пример 1: одиночное применение оператора

Для начала рассмотрим простейший пример, в котором функция ДВССЫЛ выступает самостоятельно, чтобы вы могли понять суть её работы.

Имеем произвольную таблицу. Стоит задача отобразить данные первой ячейки первого столбца в первый элемент отдельной колонки при помощи изучаемой формулы.

1. Выделяем первый пустой элемент столбца, куда планируем вставлять формулу. Щелкаем по значку «Вставить функцию».
2. Происходит запуск окошка Мастера функций. Перемещаемся в категорию «Ссылки и массивы». Из перечня выбираем значение «ДВССЫЛ». Щелкаем по кнопке «OK».
3. Происходит запуск окошка аргументов указанного оператора. В поле «Ссылка на ячейку» требуется указать адрес того элемента на листе, содержимое которого мы будем отображать. Конечно, его можно вписать вручную, но гораздо практичнее и удобнее будет сделать следующее. Устанавливаем курсор в поле, после чего щелкаем левой кнопкой мыши по соответствующему элементу на листе. Как видим, сразу после этого его адрес отобразился в поле. Затем с двух сторон выделяем ссылку кавычками. Как мы помним, это особенность работы с аргументом данной формулы.

В поле «A1», так как мы работает в обычном типе координат, можно поставить значение «ИСТИНА», а можно оставить его вообще пустым, что мы и сделаем. Это будут равнозначные действия.

Пример 2: использование оператора в комплексной формуле

А теперь давайте посмотрим на пример гораздо более частого применения оператора ДВССЫЛ, когда он является составной частью комплексной формулы.

Имеем помесячную таблицу доходов предприятия. Нам нужно подсчитать сумму дохода за определенный период времени, например март – май или июнь – ноябрь. Конечно, для этого можно воспользоваться формулой простого суммирования, но в этом случае при необходимости подсчета общего результата за каждый период нам все время придется менять эту формулу. А вот при использовании функции ДВССЫЛ можно будет производить изменение суммированного диапазона, просто в отдельных ячейках указав соответствующий месяц. Попробуем использовать данный вариант на практике сначала для вычисления суммы за период с марта по май. При этом будет использована формула с комбинацией операторов СУММ и ДВССЫЛ.

1. Прежде всего, в отдельных элементах на листе вносим наименования месяцев начала и конца периода, за который будет производиться расчет, соответственно «Март» и «Май».
2. Теперь присвоим имя всем ячейкам в столбце «Доход», которое будет являться аналогичным названию соответствующего им месяца. То есть, первый элемент в столбце «Доход», который содержит размер выручки, следует назвать «Январь», второй – «Февраль» и т.д.

В целом количество аргументов может достигать значения 255. Но все эти аргументы являются однородными. Они представляют собой число или координаты ячейки, в которой это число содержится. Также они могут выступать в виде встроенной формулы, которая рассчитывает нужное число или указывает на адрес элемента листа, где оно размещается. Именно в этом качестве встроенной функции и будет использоваться нами оператор ДВССЫЛ в данном случае.

Как видим, несмотря на то, что функцию ДВССЫЛ нельзя назвать одной из наиболее популярных у пользователей, тем не менее, она помогает решить задачи различной сложности в Excel гораздо проще, чем это можно было бы сделать при помощи других инструментов. Более всего данный оператор полезен в составе сложных формул, в которых он является составной частью выражения. Но все-таки нужно отметить, что все возможности оператора ДВССЫЛ довольно тяжелы для понимания. Это как раз и объясняет малую популярность данной полезной функции у пользователей.