Как работает функция двссыл в excel примеры

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

Функция ДВССЫЛ() , английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой . Например, формула = ДВССЫЛ(«Лист1!B3») эквивалентна формуле = Лист1!B3 . Мощь этой функции состоит в том, что саму ссылку ( Лист1!B3 ) также можно изменять формулами, ведь для ДВССЫЛ() это просто текстовая строка! С помощью этой функции можно транспонировать таблицы, выводить значения только из четных/ нечетных строк, складывать цифры числа и многое другое.

Функция ДВССЫЛ() имеет простой синтаксис.

Синтаксис функции

ДВССЫЛ( ссылка_на_ячейку ;a1 )

Ссылка_на_ячейку — это текстовая строка в формате ссылки (т.е. указаны столбец и строка): = ДВССЫЛ(«B3») или = ДВССЫЛ(«Лист1!B3») или =ДВССЫЛ(«[Книга1.xlsx]Лист1!B3») . Первая формула эквивалентна формуле = B3 , вторая — = Лист1!B3 , третья = [Книга1.xlsx] Лист1!B3 Если какая-либо ячейка (например, А1 ) содержит текстовую строку в формате ссылки (например, Лист1!B3 ), то в ДВССЫЛ() можно указать ссылку на эту ячейку = ДВССЫЛ(А1) Эта запись будет эквивалентна = ДВССЫЛ(«Лист1!B3») , которая в свою очередь будет эквивалентна = Лист1!B3 . Зачем все это нужно — читайте ниже (см. раздел решение задач).

Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ), указывающее, какого типа ссылка содержится в аргументе Ссылка_на_ячейку .

• Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.
• Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Примечание : Формат ссылки = Лист1!B3 называется ссылкой в стиле А1, когда явно указывается адрес ячейки. Формат ссылки в стиле R1C1 — это относительная ссылка на ячейку (относительная относительно ячейки с формулой). Например, если в ячейке С5 имеется формула =R[-1]C, то это ссылка на ячейку С4 . Чтобы записывать ссылки в стиле R1C1 необходимо переключить EXCEL в режим работы со ссылками в стиле R1C1 ( Кнопка Офис/ Параметры Excel/ Формулы/ Работа с формулами ).

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

Рассмотрим несколько задач

Задача1 — Формируем ссылки на листы

Пусть на листах Лист1 , Лист2 , Лист3 и Лист4 в одних и тех же ячейках находятся однотипные данные (Продажи товаров за квартал) См. файл примера .

Сформируем итоговую таблицу Продажи за год на другом листе. В этой таблице будут присутствовать данные с 4-х листов.

Для удобства в строке 9 на листе, где будет итоговая таблица, пронумеруем столбцы С, D, E, F как 1, 2, 3, 4 в соответствии с номером квартала и пронумеруем строки таблицы (см. столбец А).

Чтобы вывести данные с других листов используем формулу =ДВССЫЛ(«Лист»&C$9&»!B»&$A10+3)

Такая запись возможна, т.к. все листы имеют однотипные названия: Лист1 , Лист2 , Лист3 и Лист4 , и все таблицы на этих листах имеют одинаковую структуру (одинаковое количество строк и столбцов, наименования товаров, также должны совпадать).

Вышеуказанная формула в ячейке С12 эквивалентна формуле =ДВССЫЛ(«Лист1!B4») , формула в ячейке D12 эквивалентна =ДВССЫЛ(«Лист2!B4») , т.е. ссылается на другой лист! Весь смысл использования функции ДВССЫЛ() состоит в том, чтобы написать формулу в ячейке С12 и затем ее скопировать в другие ячейки (вправо и вниз), например с помощью Маркера заполнения . Теперь данные с 4-х различных листов сведены в 1 таблицу!

Примечание : Обратите внимание на использование в формуле смешанных ссылок ( C$9 и $A12).

Задача2 — ссылки на четные/ нечетные строки

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

Записав формулу =ДВССЫЛ(СИМВОЛ(65+H$26)&$A12*2+11) и скопировав ее в нужное количество ячеек, получим только четные записи из исходной таблицы. Формула в ячейке H 12 эквивалентна =ДВССЫЛ(«B13»)

Примечание : С помощью функции СИМВОЛ() можно вывести любой символ, зная его код. =СИМВОЛ(65) выведет букву А (английскую), =СИМВОЛ(66) выведет В , =СИМВОЛ(68) выведет D .

C помощью формулы =ДВССЫЛ(СИМВОЛ(65+N$26)&$A12*2+10) можно вывести только нечетные строки, а с помощью формулы =ДВССЫЛ(СИМВОЛ(65+B$26)&$A28+11) вообще произвольные строки, номера которых заданы в столбце А .

Задача3 — транспонирование таблиц/ векторов

С помощью нижеуказанной формулы можно транспонировать исходную таблицу ( подробнее читайте здесь ):

О транспонировании таблиц можно прочитать в этом разделе .

Примечание : О других применениях функции ДВССЫЛ() можно прочитать в статьях, список которых расположен ниже.

Задача 4 — использование с именами

Имена Имя1 и Имя4 — это именованные диапазоны , т.е. эти имена возвращают ссылки.

Имя И мя2 — это константа массива , т.е. массив чисел, а не ссылка.

Также массив значений будет возвращать функция СМЕЩ() . см. Имя5 .

Имя Имя3 — это именованная формула , которая возвращает число, а не ссылку.

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

Как видим, работают только те формулы, которые ссылаются на ячейки содержащие Имя1 и Имя4 . Только эти имена ссылаются на диапазоны ячеек. Если вспомним синтаксис функции ДВССЫЛ() , то в качестве первого аргумента можно использовать » текстовую строку в формате ссылки», а не числовые массивы.

Формула =СУММ(ДВССЫЛ(A2)) эквивалентна =СУММ(ДВССЫЛ(«имя1»)) Вместо «имя1» подставляется ссылка =Имена!$A$14:$A$17 ( текстовая строка в формате ссылки ), которая успешно разрешается функцией ДВССЫЛ() . В итоге функция ДВССЫЛ() возвращает массив из диапазона $A$14:$A$17 , который затем суммируется.

В случае с Имя2 все по-другому. Формула =СУММ(ДВССЫЛ(A3)) эквивалентна =СУММ(ДВССЫЛ(«имя2»)) Вместо «имя2» подставляется массив , который не является текстовой строкой и не может быть обработан функцией ДВССЫЛ() . Поэтому она возвращает ошибку. Аналогичный результат получим для имен: Имя3 и Имя5 .

В чем разница между =СУММ(ДВССЫЛ(имя5)) и =СУММ(ДВССЫЛ(«имя5»)) ? Когда мы записываем =СУММ(ДВССЫЛ(«имя5»)) мы говорим функции ДВССЫЛ() работать с имя5 как с адресом. Это сработает, если имя5 содержит » Имена!$A$14:$A$17″ или что-то в этом роде. Но, имя5 указывает на формулу, которая возвращает значения из диапазона Имена!$A$14:$A$17. Т.к. это не ссылка, то функция вернет ошибку.

Как работает функция двссыл в excel примеры

Вопросы по покупке sales@onlyoffice.com

Запросы на партнерство partners@onlyoffice.com

Запросы от прессы press@onlyoffice.com

Следите за нашими новостями:

© Ascensio System SIA 2024. Все права защищены

© Ascensio System SIA 2024. Все права защищены

Не пропустите обновление!

Получайте последние новости ONLYOFFICE на ваш email

Имя не указано.

Email не указан.

На ваш адрес электронной почты отправлено сообщение с подтверждением.

В Справочном центре ONLYOFFICE используются файлы cookie для обеспечения максимального удобства работы пользователей. Продолжая использовать этот сайт, вы соглашаетесь с тем, что мы можем сохранять файлы cookie в вашем браузере.

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

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

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

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

1. Ссылка на текст. Обязательный аргумент, содержащий ссылку на ячейку в формате текстовой строки. Например, =ДВССЫЛ («А1») или =ДВССЫЛ («Лист 2!А3»).
2. А1 – логическое значение для определения типа ссылки. Необязательный аргумент. Если имеет значение ИСТИНА или опущен, то ссылка на текст воспринимается как ссылка типа А1 (адрес ячейки указан явно). Значение ЛОЖЬ – как ссылка в стиле R1C1 (ссылка на ячейку с формулой, отсылающей к другой ячейке).

1. Значение аргумента «ссылка на ячейку» является недопустимой ссылкой – ошибка #ССЫЛКА!.
2. Обязательный аргумент ссылается на другую книгу (является внешней ссылкой), которая недоступна (закрыта) – ошибка #ССЫЛКА!. При создании подобных формул внешний источник данных должен быть открыт.
3. Значение обязательного аргумента ссылается на превышающий предел диапазон – ошибка #ССЫЛКА!. Функция может обработать до 1048576 строк или 16384 столбцов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как работает функция двссыл в excel примеры

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071