Разбор функции ДВССЫЛ (INDIRECT) на примерах

Разбор функции ДВССЫЛ (INDIRECT) на примерах

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.

«Ну ОК», — скажете вы. «И что тут полезного?».

Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.

Пример 1. Транспонирование

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN) .

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE) , то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET) . Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM) :

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

Пример 4. Несбиваемые ссылки

Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:

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

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

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

Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

Примеры использования функции ДВССЫЛ в 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 студентам по трем предметам как показано ниже:

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

• B2:B6: Математика
• C2:C6: Физика
• D2:D6: Химия

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

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

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

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

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

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

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

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

Преимущества стиля ссылок R1C1

Можно выделить 2 основных преимущества, при которых использовать R1C1 предпочтительнее, чем A1: при проверке формул (поиске ошибок) и в макросах.

Рассмотрим пример: возьмем простую таблицу, в которой проверим на корректность расчета формулы в последнем столбце:

Видите ошибку?

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

Давайте теперь посмотрим эту же таблицу со стилем ячеек R1C1:

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

Аналогичный прием можно применить и в случае с работой с ячейками при создании макросов.
В случае, если нам нужно прописать для диапазона ячеек формулы произведения двух столбцов (как в примере выше), то для стиля R1C1 все формулы будут записываться абсолютно одинаково (=RC[-2]*RC[-1]), поэтому в коде макроса можно сразу прописать формулу для всех ячеек диапазона. Для стиля A1, к сожалению, такой способ не подойдет, так как записи всех формул отличаются друг от друга и каждую нужно будет прописывать отдельно, что несколько усложняет задачу.

Как вставить функцию

Для этого нужно сделать следующие шаги.

1. Выберите любую ячейку. Нажмите на иконку вызова окна «Вставка функции». Кликните на выпадающий список и выберите нужную категорию.

1. Затем выберите желаемую функцию. В качестве примера рассмотрим «СЧЁТЕСЛИ». Сразу после этого вы увидите короткую информацию о выбранном пункте. Для подробной справки нужно будет кликнуть на указанную функцию. Для продолжения необходимо нажать на «OK».

1. После этого вас попросят указать «Аргументы функции». Поля будут всегда меняться в зависимости от выбранной формулы. В данном случае нужно указать диапазон ячеек и критерий для подсчета.

1. Перейдите к первому полю. Выделите нужное количество клеток.

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

После этого нажмите на «OK».

1. Благодаря этому вы увидите какое-нибудь число. Этому значению будет соответствовать количество тех ячеек, которые удовлетворяют вашему критерию. В данном случае мы выделили 14 пустых ячеек.

1. Если внести какие-нибудь изменения, то результат функции изменится мгновенно.

Данную формулу можно редактировать вручную. Для этого необязательно использовать окно «Вставка функции». Достаточно кликнуть на нужную ячейку и перейти к строке формул.

Если данная строка вам кажется маленькой и неудобной, нужно нажать на горячие клавиши Ctrl+Shift+U. Благодаря этому её высота увеличится в несколько раз.

Для возврата к прежнему режиму нужно повторить комбинацию клавиш Ctrl+Shift+U.

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

Вы можете при желании указать любой нужный вам диапазон и критерий для отбора.

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

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

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

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

Результат для всех 3-х формул будет одинаковым:

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

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

Как в таблицах Excel использовать функцию ДВССЫЛ

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

Косвенное указание строк

На рис. 124.1 показан пример использования функции ДВССЫЛ. Формула Е5 будет следующей:
=СУММ(ДВССЫЛ(«B»&E2&»:B»&E3))

Рис. 124.1. Для суммирования предоставленных пользователем строк применяется функция

Обратите внимание, что в аргументе функции ДВССЫЛ указан оператор конкатенации. Он позволяет создать ссылку на диапазон, используя значения в ячейках Е2 и Е3. Таким образом, если Е2 содержит 2, а Е3 содержит 4, ссылка на диапазон получается идентичной этой строке: «В2:В4» .

Функция ДВССЫЛ преобразует строку в фактическую ссылку на диапазон, которая затем передается функции СУММ. В сущности, формула возвращает: =СУММ(В2:В4) . При изменении значений в Е2 и Е3 формула обновляется и отображает суммы указанных строк.

Косвенное указание названий листов

На рис. 124.2 продемонстрирован другой пример — на этот раз применятся ссылка на лист. Столбец А листа Итого содержит текст, который соответствует другим листам книги. Столбец В содержит формулы, которые ссылаются на эти пункты текста. Например, формула в ячейке В2 следующая:
=СУММ(ДВССЫЛ(A2&»!F1:F10″)) .

Рис. 124.2. Столбец А листа Итого содержит текст, который соответствует другим листам книги

Эта формула объединяет текст в А2 со ссылкой на диапазон. Функция ДВССЫЛ вычисляет результат и преобразует его в фактическую ссылку на диапазон. Результат эквивалентен следующей формуле:
=СУММ(Север!F1:F10) .

Формула копируется вниз по столбцу. Каждая формула возвращает сумму диапазона F1:F10 соответствующего листа.

Создание неизменной ссылки на ячейку

Другое применение функции ДВССЫЛ состоит в том, чтобы создать такую ссылку на ячейку, которая никогда не изменяется. Для примера рассмотрим формулу, которая суммирует значения в первых 12 строках столбца А: =СУММ(А1:А12) .

Если вставить новую строку 1, Excel изменит формулу на такую: =СУММ(А2:А13) . Другими словами, формула подстраивается так, чтобы по-прежнему ссылаться на исходные данные (и она уже не суммирует первые 12 строк столбца А).

Чтобы предотвратить Excel от изменения ссылок на ячейки, используйте функцию ДВССЫЛ: =СУММ(ДВССЫЛ(«А1:А12»)) . Эта формула всегда возвращает сумму первых 12 строк в столбце А.