Двссыл что за функция
Функция ДВССЫЛ() в EXCEL
history 10 апреля 2014 г.
Функция ДВССЫЛ() имеет простой синтаксис.
Синтаксис функции
ДВССЫЛ( ссылка_на_ячейку ;a1 )
Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ() возвращает значение ошибки #ССЫЛКА!
Рассмотрим несколько задач
Сформируем итоговую таблицу Продажи за год на другом листе. В этой таблице будут присутствовать данные с 4-х листов.
Для удобства в строке 9 на листе, где будет итоговая таблица, пронумеруем столбцы С, D, E, F как 1, 2, 3, 4 в соответствии с номером квартала и пронумеруем строки таблицы (см. столбец А).
Чтобы вывести данные с других листов используем формулу =ДВССЫЛ(«Лист»&C$9&»!B»&$A10+3)
Записав формулу =ДВССЫЛ(СИМВОЛ(65+H$26)&$A12*2+11) и скопировав ее в нужное количество ячеек, получим только четные записи из исходной таблицы. Формула в ячейке H 12 эквивалентна =ДВССЫЛ(«B13»)
С помощью нижеуказанной формулы можно транспонировать исходную таблицу ( подробнее читайте здесь ):
Примечание : О других применениях функции ДВССЫЛ() можно прочитать в статьях, список которых расположен ниже.
Функция ДВССЫЛ в Excel с примерами использования
Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.
Функция применяется для изменения ссылки на ячейку без изменения самой формулы, выведения значений лишь из четных/ нечетных строк, транспонирования таблиц и др.
Синтаксис функции с описанием
Функция ДВССЫЛ в Excel: примеры
Начнем с хрестоматийного примера, чтобы понять принцип работы функции.
Имеется таблица с данными:
Примеры функции ДВССЫЛ:
Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).
Нужно на основе имеющихся таблиц составить итоговую таблицу на отдельном листе, собрав данные с пяти листов. Сделаем это с помощью функции ДВССЫЛ.
Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.
Теперь из итоговой таблицы извлечем только нечетные строки. Для удобства пронумеруем столбцы и строки.
Чтобы получить только нечетные записи, используем формулу:
Для выведения четных строк:
Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.
Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.
Чтобы функция сработала, все отчеты поместим на один лист.
Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.
Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.
Если мы используем только функцию ВПР, появится ошибка:
Программа не воспринимает ссылку D2 как ссылку на именованный диапазон, где и находится отчет определенного года. Excel считает значение в ячейке текстом.
Исправить положение помогла функция ДВССЫЛ, которая возвращает ссылку, заданную текстовой строкой.
Функции ВПР и ДВССЫЛ в Excel
Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.
Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:
В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:
Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:
Таким образом, эти две функции выполняют подобного рода задачи на отлично.
Функция ДВССЫЛ в Excel
Рассмотрим примеры использования функции ДВССЫЛ в Excel, которая позволяет преобразовывать текст из ячейки в ссылку.
Функция ДВССЫЛ чрезвычайно полезна тем, что при использовании данной функции есть возможность изменять ссылки на ячейки и диапазоны в формуле не изменяя при этом саму формулу.
Другими словами, введенная формула =B2 идентична формуле =ДВССЫЛ(«B2»), однако в первом варианте мы оперируем ссылкой, а во втором — текстом, который можно изменять.
Описание функции ДВССЫЛ
ДВССЫЛ(ссылка_на_ячейку; [a1])
Возвращает ссылку, заданную текстовой строкой.
Примеры использования функции ДВССЫЛ
Пример 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)), где с помощью функции ПОИСКПОЗ находим относительное положение оценки кандидата в критерии оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для каждого кандидата.
Обратите внимание, что функция ДВССЫЛ не работает, если ссылка указывает на ячейку или диапазон в закрытой книге.
Подробно ознакомиться со всеми разобранными примерами — скачать пример.
Функция INDIRECT (ДВССЫЛ) в Excel. Как использовать?
Функция INDIRECT (ДВССЫЛ) в Excel используется когда у вас есть ссылки в виде текста, и вы хотите получить значения из этих ссылок.
Что возвращает функция
Функция возвращает ссылку, заданную текстовой строкой.
Синтаксис
=INDIRECT(ref_text, [a1]) – английская версия
=ДВССЫЛ(ссылка_на_текст;[a1]) – русская версия
Аргументы функции
Дополнительная информация
Примеры использования функции ДВССЫЛ в Excel
Пример 1. Используем ссылку на ячейку для получения значения
Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):
Формула в ячейке С1:
=INDIRECT(“A1”) – английская версия
=ДВССЫЛ(“A1”) – русская версия
Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.
Если в данном случае вы введете в ячейку С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 (ДВССЫЛ)
Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:
Зададим для следующих ячеек названия:
Для того чтобы задать название любому диапазону значений, достаточно выбрать ячейки и присвоить им название в соответствующем боксе:
После этого вы можете обратиться к указанным диапазонам с помощью формулы:
=INDIRECT(“Именованный диапазон”) – английская версия
=ДВССЫЛ(“Именованный диапазон”) – русская версия
Например, если вы хотите узнать средний балл среди студентов по математике – используйте следующую формулу:
=AVERAGE(INDIRECT(“Математика”)) – английская версия
=СРЗНАЧ(ДВССЫЛ(“Математика”)) – русская версия
Если имя диапазона указано в ячейке (“F2” в приведенном ниже примере указан как “Матем”), вы можете использовать ссылку на ячейку прямо в формуле. В следующем примере показано, как вычислять среднее значение с использованием именных диапазонов.
Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)
C помощью этой функции вы можете создавать зависимый выпадающий список.
Например, предположим, что у вас есть две колонки с названиями “Россия” и “США”, в строках указаны города этих стран, как указано на примере ниже:
Для того, чтобы создать зависимый выпадающий список вам нужно создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”.
Теперь, в ячейке “D2” создайте выпадающий список для “России” и “США”. Так мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.
Теперь, для создания зависимого выпадающего списка:
Теперь, если вы выберите в первом выпадающем списке, например, страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Такая же ситуация, если вы выберите страну “США” из первого выпадающего списка.
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!
Функция ДВССЫЛ
В этой статье описаны синтаксис формулы и использование функции ДВССЫЛ в 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, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.