Функция ДВССЫЛ в Excel с примерами использования
Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.
Функция применяется для изменения ссылки на ячейку без изменения самой формулы, выведения значений лишь из четных/ нечетных строк, транспонирования таблиц и др.
Синтаксис функции с описанием
Аргументы:- Ссылка на текст. Обязательный аргумент, содержащий ссылку на ячейку в формате текстовой строки. Например, =ДВССЫЛ («А1») или =ДВССЫЛ («Лист 2!А3»).
- А1 – логическое значение для определения типа ссылки. Необязательный аргумент. Если имеет значение ИСТИНА или опущен, то ссылка на текст воспринимается как ссылка типа А1 (адрес ячейки указан явно). Значение ЛОЖЬ – как ссылка в стиле R1C1 (ссылка на ячейку с формулой, отсылающей к другой ячейке).
- Значение аргумента «ссылка на ячейку» является недопустимой ссылкой – ошибка #ССЫЛКА!.
- Обязательный аргумент ссылается на другую книгу (является внешней ссылкой), которая недоступна (закрыта) – ошибка #ССЫЛКА!. При создании подобных формул внешний источник данных должен быть открыт.
- Значение обязательного аргумента ссылается на превышающий предел диапазон – ошибка #ССЫЛКА!. Функция может обработать до 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 (&). В результате получается полное имя нужного диапазона.
Таким образом, эти две функции выполняют подобного рода задачи на отлично.