Формула двссыл в excel примеры

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

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

Синтаксис функции с описанием

Функция ДВССЫЛ в Excel: примеры

Начнем с хрестоматийного примера, чтобы понять принцип работы функции.

Имеется таблица с данными:

Примеры функции ДВССЫЛ:

Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).

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

Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.

Теперь из итоговой таблицы извлечем только нечетные строки. Для удобства пронумеруем столбцы и строки.

Чтобы получить только нечетные записи, используем формулу:

Для выведения четных строк:

Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.

Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.

Чтобы функция сработала, все отчеты поместим на один лист.

Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.

Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.

Если мы используем только функцию ВПР, появится ошибка:

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

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

Читайте также:  Hp elitepad 900 как переустановить windows

Функции ВПР и ДВССЫЛ в Excel

Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.

Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:

В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:

Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:

  • $A$12 – ссылка с образованием (можно выбирать из выпадающего списка);
  • $C11 – ячейка, в которой содержится первая часть названия листа с отчетом (все листы переименованы: «2012_отчет», 2013_отчет» и т.д.);
  • _отчет!A3:B10 – общая часть названия всех листов и диапазон с отчетом. Она соединяется со значением в ячейке С11 (&). В результате получается полное имя нужного диапазона.

Таким образом, эти две функции выполняют подобного рода задачи на отлично.

В этой статье описаны синтаксис формулы и использование функции ДВССЫЛ в Microsoft Excel.

Описание

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

Синтаксис

Аргументы функции ДВССЫЛ описаны ниже.

Ссылка_на_текст — обязательный аргумент. Ссылка на ячейку, которая содержит ссылку в стиле А1 или R1C1, имя, определенное как ссылка, или ссылку на ячейку в виде текстовой строки. Если значение аргумента "ссылка_на_текст" не является допустимой ссылкой, функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.

Если значение аргумента "ссылка_на_ячейку" является ссылкой на другую книгу (внешней ссылкой), другая книга должна быть открыта. В противном случае функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!. Примечание.Поддержка внешних ссылок в Excel Web App отсутствует.

Если значение аргумента "ссылка_на_текст" является ссылкой на диапазон ячеек, превышающий предельное число строк 1 048 576 или столбцов 16 384 (XFD), функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.

Примечание. Этот результат отличается от результата, получаемого в более ранних версиях приложения Excel, чем Microsoft Office Excel 2007, в которых превышение предела игнорируется и возвращается значение.

A1 — необязательный аргумент. Логическое значение, определяющее тип ссылки, содержащейся в ячейке "ссылка_на_текст".

Если аргумент "a1" имеет значение ИСТИНА или опущен, "ссылка_на_ячейку" интерпретируется как ссылка в стиле A1.

Если аргумент "a1" имеет значение ЛОЖЬ, "ссылка_на_ячейку" интерпретируется как ссылка в стиле R1C1.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция 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 студентам по трем предметам как показано ниже:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock detector