30 функций Excel за 30 дней: ДВССЫЛ (INDIRECT)

Поздравляю! Вы добрались до финишного денька марафона 30 функций Excel за 30 дней. Это было длинноватое увлекательное путешествие, в течение которого Вы узнали много нужных вещей о функциях Excel.

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

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

Функция 30: INDIRECT (ДВССЫЛ)

Функция INDIRECT (ДВССЫЛ) возвращает ссылку, заданную текстовой строчкой.

Как можно применять функцию INDIRECT (ДВССЫЛ)?

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

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

Синтаксис INDIRECT (ДВССЫЛ)

Функция INDIRECT (ДВССЫЛ) имеет вот таковой синтаксис:

ref_text (ссылка_на_ячейку) – это текст ссылки.
a1 – если равен TRUE (ИСТИНА) либо не указан, то будет применен стиль ссылки A1; а если FALSE (ЛОЖЬ), то стиль R1C1.

Ловушки INDIRECT (ДВССЫЛ)

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

Пример 1: Создаем не сдвигающуюся исходную ссылку

В первом примере в столбцах C и E находятся однообразные числа, их суммы, посчитанные с помощью функции SUM (СУММ), тоже схожи. Тем не наименее, формулы незначительно различаются. В ячейке C8 формула вот таковая:

В ячейке E8 функция INDIRECT (ДВССЫЛ) создаёт ссылку на исходную ячейку E2:

Если вверху листа вставить строчку и добавить значение для января (Jan), то сумма в столбце C не поменяется. Поменяется формула, отреагировав на прибавление строчки:

Но, функция INDIRECT (ДВССЫЛ) фиксирует E2 как исходную ячейку, потому значение января автоматом врубается в подсчёт суммы по столбцу E. Конечная ячейка поменялась, но на исходную это не воздействовало.

Пример 2: Создаем ссылку на статичный именованный спектр

Функция INDIRECT (ДВССЫЛ) может сделать ссылку на именованный спектр. В этом примере голубые ячейки составляют спектр NumList. Не считая этого, из значений в столбце B сотворен к тому же динамический спектр NumListDyn, зависящий от количества чисел в этом столбце.

Сумма для обоих диапазонов быть может вычислена, просто задав его имя в качестве аргумента для функции SUM (СУММ), как это можно узреть в ячейках E3 и E4.

=SUM(NumList) либо =СУММ(NumList)
=SUM(NumListDyn) либо =СУММ(NumListDyn)

Заместо того, чтоб вводить с клавиатуры имя спектра в функцию SUM (СУММ), Вы сможете сослаться на имя, записанное в одной из ячеек листа. К примеру, если имя NumList записано в ячейке D7, то формула в ячейке E7 будет вот таковая:

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

Пример 3: Создаём ссылку используя информацию о листе, строке и столбце

Вы просто сможете сделать ссылку, делая упор на номера строк и столбцов, также используя значение FALSE (ЛОЖЬ) для второго аргумента функции INDIRECT (ДВССЫЛ). Так создается ссылка стиля R1C1. В этом примере мы добавочно добавили к ссылке имя листа – ‘MyLinks’!R2C2

Интересно почитать: Exp функция в excel

Пример 4: Создаём не сдвигающийся массив чисел

Время от времени в формулах Excel нужно применять массив чисел. В последующем примере мы желаем получить среднее из 3-х больших чисел в столбце B. Числа могут быть вписаны в формулу, как это изготовлено в ячейке D4:

Если Для вас будет нужно массив побольше, то Вы навряд ли возжелаете вчеркивать в формулу все числа. 2-ой вариант – это применять функцию ROW (СТРОКА), как это изготовлено в формуле массива, введенной в ячейку D5:

3-ий вариант – это применять функцию ROW (СТРОКА) вкупе с INDIRECT (ДВССЫЛ), как это изготовлено при помощи формулы массива в ячейке D6:

Итог для всех 3-х формул будет схожим:

Но, если сверху листа вставить строчки, 2-ая формула вернет не верный итог из-за того, что ссылки в формуле поменяются вкупе со сдвигом строк. Сейчас, заместо среднего значения трёх наибольших чисел, формула возвращает среднее 3-го, 4-го и 5-го по величине чисел.

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

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

Функция ДВССЫЛ в программе Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Функция транспонирования в excel

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

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

Сейчас давайте разглядим определенные примеры внедрения оператора.

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

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

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

«Вставить функцию»

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

Пример 2: внедрение оператора в всеохватывающей формуле

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

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

«Март»

«Май»

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

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

Мы рады, что смогли посодействовать Для вас в решении задачи.

Кроме данной статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

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

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

Интересно почитать: Функция деления в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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