Как использовать функцию ДВССЫЛ - Примеры формул
В этой статье объясняется синтаксис функции ДВССЫЛ, основные способы ее использования и приводится ряд примеров формул, демонстрирующих использование функции ДВССЫЛ в Excel.
В Microsoft Excel много функций, некоторые из них просты для понимания, другие требуют долгого изучения. Первый используется чаще, чем второй. И все же функция ДВССЫЛ в Excel уникальна. Эта функция Excel не выполняет никаких вычислений, не оценивает условия, не ищет значения.
Так что же такое функция ДВССЫЛ в Excel и для чего ее можно использовать? Это очень хороший вопрос, и, надеюсь, вы получите исчерпывающий ответ через несколько минут, когда закончите читать.
Функция ДВССЫЛ в Excel — синтаксис и основные способы использования
ДВССЫЛ используется для создания косвенных ссылок на ячейки, диапазоны, другие листы или книги. Другими словами, он получает нужный адрес и позволяет динамически ссылаться на ячейку или диапазон от нее с помощью формулы, а не указывать их напрямую. В результате вы можете изменить ссылку в формуле, не изменяя саму формулу. Кроме того, эти косвенные ссылки не изменятся при вставке в электронную таблицу новых строк или столбцов или при удалении существующих.
Все это может быть легче понять на примере. Но чтобы написать формулу, пусть даже самую простую, нужно знать аргументы функции, верно? Итак, давайте сначала кратко рассмотрим синтаксис Excel INDIRECT.
Функция ДВССЫЛ в Excel возвращает ссылку на ячейку, используя текстовую строку. У него два аргумента, первый обязательный, а второй необязательный:
НЕПРЯМОЙ(cellref; [a1])
ссылка на ячейку — это адрес нужной ячейки в виде текстовой строки или имя именованного диапазона.
a1 — логическое значение, указывающее тип ссылки, содержащейся в первом аргументе:
- Если значение TRUE или опущено, используется ссылка на ячейку в стиле A1.
- Если FALSE, ссылка возвращается как R1C1.
Таким образом, ДВССЫЛ возвращает либо ссылку на ячейку, либо ссылку на диапазон.
Хотя тип ссылки R1C1 может быть полезен в определенных ситуациях, вам, вероятно, будет удобнее использовать знакомые ссылки A1. В любом случае почти все формулы в этом руководстве будут использовать ссылки A1, поэтому мы просто опустим второй аргумент в функции.
Как работает функция ДВССЫЛ
Чтобы получить представление о том, как работает функция, давайте создадим простую формулу, показывающую, как ДВССЫЛ можно использовать в Excel.
Допустим, у вас есть число 5 в ячейке A1 и текст «A1» в ячейке C1. Теперь поместите формулу =ДВССЫЛ(C1) в любую другую ячейку и посмотрите, что произойдет:
- Функция ДВССЫЛ обращается к значению в ячейке C1. Адрес «А1» там записан в виде текстовой строки».
- Функция ДВССЫЛ идет по этому адресу в ячейке А1, из которой извлекает записанное в ней значение, то есть число 555.
Итак, в этом примере функция ДВССЫЛ преобразует текстовую строку в ссылку на ячейку.
Точно так же вы можете получить ссылку на область. Для этого вам нужно всего лишь указать два адреса для функции ДВССЫЛ - начальный и конечный адрес. Вы можете увидеть это на скриншоте ниже.
Формула ДВССЫЛ(C1&":"&C2) извлекает адреса из указанных ячеек и становится =ДВССЫЛ("A1:A5").
В итоге получаем ссылку =A1:A5
Если вы считаете, что это по-прежнему имеет очень мало практического смысла, читайте дальше, и я покажу вам еще несколько примеров, раскрывающих реальную мощь функции ДВССЫЛ в Excel, и более подробно покажу вам, как она работает.
Как использовать ДВССЫЛ в Excel - примеры формул
Как показано в приведенном выше примере, вы можете использовать функцию ДВССЫЛ, чтобы записать адрес ячейки в виде простой текстовой строки и в результате вернуть значение этой ячейки. Однако этот простой пример — не более чем намек на возможности INDIRECT.
При работе с реальными данными мы можем превратить любой текст в ссылку, включая очень сложные комбинации, которые вы создаете, используя другие значения ячеек и результаты, возвращаемые другими формулами Excel. Но не будем торопиться и пойдем от простого к сложному.
Создание косвенных ссылок из значений ячеек
Как вы помните, функция ДВССЫЛ в Excel позволяет использовать стили ссылок A1 и R1C1. Как правило, вы не можете применить оба стиля к одному и тому же листу одновременно. Вы можете переключаться между двумя типами ссылок только с помощью параметра «Файл» > «Параметры» > «Формулы» > «R1C1». По этой причине пользователи Excel редко рассматривают использование R1C1 в качестве альтернативного подхода к созданию ссылок.
В формуле ДВССЫЛ вы можете использовать любой тип ссылки на том же листе, если хотите. Прежде чем мы пойдем дальше, давайте подробнее рассмотрим разницу между стилями ссылок A1 и R1C1.
Стиль A1 — это распространенный и знакомый тип адресации в Excel, при котором сначала указывается столбец, а затем номер строки. Например, B2 указывает ячейку на пересечении столбца B и строки 2.
Стиль R1C1 - это обратные координаты ячейки - за строками следуют столбцы, и к этому нужно привыкнуть :) Например, R5C1 относится к ячейке A5, которая находится в строке 5, столбце 1 электронной таблицы. Если за буквой не следует цифра, вы имеете в виду ту же строку или столбец, в котором написана сама формула.
Теперь сравним на простом примере, как функция ДВССЫЛ обрабатывает адреса типа A1 и R1C1:
Как видно на скриншоте выше, две разные формулы дают одинаковый результат. Вы уже понимаете, почему?
- Формула в ячейке D1: =ДВССЫЛ(C1)
Это самый простой вариант. Формула обращается к ячейке C1, извлекает значение, текстовую строку «A2», преобразует ее в ссылку на ячейку, переходит к ячейке A2 и возвращает значение 456.
- Формула в ячейке D3: =ДВССЫЛ(C3,ЛОЖЬ)
FALSE во втором аргументе указывает, что указанное значение (C3) должно рассматриваться как ссылка на ячейку в формате R1C1, т е сначала идет номер строки, а затем номер столбца. Таким образом, наша формула ДВССЫЛ интерпретирует значение в ячейке C3 (R2C1) как ссылку на ячейку на пересечении строки 2 и столбца 1, которая является ячейкой A2.
Создание ссылок из значений ячеек и текста
Подобно тому, как мы создавали ссылки из значений ячеек, вы можете объединить текстовую строку и ссылку на ячейку с адресом непосредственно в формуле ДВССЫЛ, соединив их с помощью оператора объединения (&).
В следующем примере формула =ДВССЫЛ("A"&C1) возвращает значение из ячейки A1 на основе следующей логической цепочки:
Функция ДВССЫЛ объединяет элементы первого аргумента ссылки на ячейку, текст «A» и значение из ячейки C1. Значение в C1 — это число 1, что приводит к адресу A1. Формула переходит к ячейке A1 и возвращает значение — 555.
Использование функции ДВССЫЛ с именованными диапазонами
Помимо создания ссылок на ячейки из текстовых строк, вы можете использовать функцию ДВССЫЛ в Excel для создания ссылок на именованные диапазоны.
Допустим, в вашей электронной таблице есть следующие именованные диапазоны:
- Яблоки - C2:E2
- Лимоны — C3:E3
- Апельсины - C4:E4 и так далее для каждого продукта.
Чтобы создать динамическую ссылку Excel на любой из указанных выше диапазонов номеров продаж, введите имя, например, H1, и укажите ссылку на эту ячейку, используя формулу = ДВССЫЛ (H1).
И теперь вы можете сделать еще один шаг и добавить эту формулу в другие функции Excel. Например, попробуем посчитать сумму и среднее значений в заданном именованном диапазоне или найти в нем максимальную и минимальную сумму продаж, как это сделано на скриншоте ниже:
- =СУММ(ДВССЫЛ(H1))
- =СРЕДНЕЕ(КОСВЕННОЕ(H1))
- =МАКС(ДВССЫЛ(H1))
- =МИН(ДВССЫЛ(H1))
Теперь, когда у вас есть общее представление о том, как работает функция ДВССЫЛ в Excel, мы можем поэкспериментировать с более сложными формулами.
ДВССЫЛ для ссылки на другой рабочий лист
Полезность функции ДВССЫЛ в Excel не ограничивается созданием «динамических» ссылок на ячейки. Вы также можете использовать его для ссылки на другие листы.
Предположим, у вас есть важные данные на листе 1, и вы хотите, чтобы эти данные были на листе 2. На снимке экрана ниже показано, как вы можете выполнить эту задачу.
Нам поможет формула:
= ДВССЫЛ ("'"&A2&"'!"&B2&C2)
Давайте разберемся, как работает эта формула.
Как вы знаете, обычный способ сослаться на другой лист в Excel — ввести имя этого листа, восклицательный знак и ссылку на ячейку или диапазон, например Лист1!A1:C10. Так как имя листа часто содержит пробелы, его (имя, а не пробел:) следует заключать в одинарные кавычки, чтобы предотвратить возможную ошибку, например,
'Лист 1!'$A$1 или, для диапазона, 'Лист 1!'$A$1:$C$10 .
Наша задача — создать нужный текст и отправить его в функцию ДВССЫЛ. Все, что вам нужно сделать, это:
- написать название листа в одну ячейку,
- буква в столбик - другому,
- номер строки - в третьем,
- объединить все это в одну строку текста,
- передать этот адрес функции ДВССЫЛ.
Помните, что в текстовой строке вы должны заключить каждый элемент, кроме номера строки, в двойные кавычки, а затем объединить все элементы с помощью оператора конкатенации (&).
Учитывая вышеизложенное, мы получаем шаблон INDIRECT для создания ссылки на другой лист:
ДВССЫЛ ("'" и имя листа & "'!" и желаемое имя столбца ячейки и желаемый номер строки ячейки)
Возвращаясь к нашему примеру, поместите имя листа в ячейку A2 и введите адреса столбцов и строк в B2 и C2, как показано на снимке экрана выше. В результате вы получите следующую формулу:
ДВССЫЛ("'"&A2&"'!"&B2&C2)
Также обратите внимание, что если вы копируете формулу в несколько ячеек, вы должны исправить ссылку на имя листа, используя абсолютные ссылки на ячейки, например $A$2.
Комментарий.
- Если какая-либо из ячеек, содержащих имя листа и адреса ячеек (A2, B2 и c2 в приведенной выше формуле), пуста, формула вернет ошибку. Чтобы предотвратить это, вы можете обернуть функцию ДВССЫЛ в функцию ЕСЛИ :
ЕСЛИ(ИЛИ(A2="";B2="";C2-""); ""; ДВССЫЛ("'"&A2&"'!"&B2&C2)
- Чтобы формула ДВССЫЛ, ссылающаяся на другой лист, работала правильно, указанный лист должен быть открыт в Excel, иначе формула вернет ошибку #ССЫЛКА. Чтобы не видеть сообщение об ошибке, которое может испортить внешний вид вашей таблицы, вы можете использовать функцию ЕСЛИОШИБКА, которая при любой ошибке будет отображать пустую строку:
ЕСЛИОШИБКА(ДВССЫЛ("'"&A2&"'!"&B2&C2); "")
Формула ДВССЫЛ для ссылки на другую книгу Excel
Формула, создающая ссылку на другую книгу Excel, основана на том же подходе, что и обычная ссылка на другой рабочий лист. Вам нужно только указать имя книги в дополнение к имени листа и адресу ячейки.
Чтобы упростить задачу, начнем с создания ссылки на другую книгу обычным способом (апостроф добавляется, если название книги и/или листа содержит пробелы):
'[Book_name.xlsx]Sheet_name'!Cell_ares
Но чтобы формула была универсальной, лучше всегда добавлять апострофы - лишними они не будут .
Предполагая, что название книги находится в ячейке A2, имя листа — в B2, а адрес ячейки — в C2 и D2, мы получаем следующую формулу:
=ДВССЫЛ("'["&$A$2&".xlsx]"&$B$2&"'!"&C2&D2)
Поскольку вы не хотите, чтобы ячейки, содержащие имена книг и листов, менялись при копировании формулы в другие ячейки, вы можете исправить их, используя абсолютные ссылки на ячейки — $A$2 и $B$2 соответственно.
Если мы заменим адреса ячеек их значениями, то получим следующий промежуточный результат:
=ДВССЫЛ("'[ДВССЫЛ.xlsx]Продажи'!D3")
Что ж, окончательный результат вы можете увидеть на скриншоте ниже.
Hbc6
И теперь вы можете легко создать собственную динамическую ссылку на другую книгу Excel, используя следующий шаблон:
=ДВССЫЛ("'[" & название книги & "]" & имя листа & "'!" & адрес ячейки)
Примечание. Рабочая книга, на которую ссылается ваша формула, должна быть всегда открыта, иначе функция ДВССЫЛ выдаст ошибку #ССЫЛКА. Как обычно, функция ЕСЛИОШИБКА может помочь вам избежать этого:
=ОШИБКА(ДВССЫЛ("'["&$A$2&".xlsx]"&$B$2&"'!"&C2&D2); "")
Использование функции Excel ДВССЫЛ чтобы зафиксировать ссылку на ячейку
Как правило, Microsoft Excel автоматически изменяет ссылки на ячейки при вставке новых или удалении существующих строк или столбцов на листе. Чтобы этого не произошло, вы можете использовать функцию ДВССЫЛ для работы с конкретными адресами ячеек, которые в любом случае должны оставаться неизменными.
Чтобы проиллюстрировать разницу, сделайте следующее:
- Введите любое значение в ячейку, например число 555 в ячейку A1.
- Обратитесь к A1 из двух других ячеек тремя различными способами: =A1, =ДВССЫЛ("A1") и ДВССЫЛ(C1), где C1 имеет адрес "A1».
- Вставьте новую строку над строкой 1.
Вы видите, что происходит? Ячейка с логическим оператором =A1 по-прежнему возвращает 555, поскольку после вставки строки формула была автоматически изменена на =A2. Ячейки с формулой ДВССЫЛ теперь возвращают нули, потому что формулы в них не изменились при вставке новой строки, и они по-прежнему ссылаются на ячейку A1, которая в данный момент пуста:
После этой демонстрации у вас может сложиться впечатление, что функция ДВССЫЛ больше мешает, чем помогает. Ладно, попробуем что-нибудь еще.
Допустим, вы хотите просуммировать значения в ячейках A2:A5, и вы можете легко сделать это с помощью функции СУММ:
=СУММ(A2:A5)
Однако вы хотите, чтобы формула оставалась неизменной независимо от того, сколько строк было удалено или вставлено. Самое очевидное решение — использование абсолютных ссылок — не поможет. Чтобы быть уверенным, введите формулу =СУММ($A$2:$A$5) в ячейку, вставьте новую строку, скажем, в строку 3, и вы увидите, что формула преобразуется в =СУММ($A$2:$A $6).
Конечно, этот сервис от Microsoft Excel в большинстве случаев будет работать нормально. Однако могут быть сценарии, в которых вы не хотите, чтобы формула менялась автоматически. Например, нам нужна сумма только первых четырех значений из таблицы.
Решение состоит в использовании функции ДВССЫЛ, например:
=СУММ(ДВССЫЛ("A2:A5"))
Поскольку Excel обрабатывает «A1: A5» как простую текстовую строку, а не как ссылку на диапазон, он не будет вносить никаких изменений при вставке или удалении строк или при сортировке.
Использование ДВССЫЛ с другими функциями Excel
Помимо СУММ, ДВССЫЛ часто используется с другими функциями Excel, такими как СТРОКА, СТОЛБЦ, АДРЕС, ВПР, СУММЕСЛИ и т д
Пример 1. Функции ДВССЫЛ и СТРОКА
Довольно часто функция СТРОКА в Excel используется для возврата массива значений. Например, вы можете использовать следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы сделать это), чтобы получить среднее значение трех наименьших чисел в диапазоне B2:B13
{=СРЕДНИЙ(МАЛЕНЬКИЙ(B2:B13,СТРОКА(1:3)))}
Однако если вы вставите новую строку на лист где-нибудь между строками 1 и 3, диапазон в функции СТРОКА изменится на СТРОКА (1:4), и формула вернет среднее четырех наименьших чисел вместо трех.
Чтобы этого не произошло, вставьте ДВССЫЛ в функцию СТРОКА, и ваша формула массива всегда останется правильной, независимо от того, сколько строк вставлено или удалено:
={СРЕДНИЙ(МИНИМУМ(B2:B13,СТРОКА(ДВССЫЛ("1:3"))))}
Точно так же, если нам нужно найти сумму трех самых больших значений, мы можем использовать ДВССЫЛ вместе с функцией СУММПРОИЗВ.
Вот пример:
={СУММПРОИЗВ(БОЛЬШОЙ(B2:B13,СТРОКА(ДВССЫЛ("1:3"))))}
А чтобы указать переменное количество значений, которые мы хотим посчитать, мы можем поместить это число в отдельную ячейку. Например, в С1 пишем цифру 3. Тогда формулу можно изменить так:
={СУММПРОИЗВ(БОЛЬШОЙ(B2:B13,СТРОКА(ДВССЫЛ("1:"&C1))))}
Согласитесь, получается довольно гибкий расчет.
Пример 2. Функции ДВССЫЛ и АДРЕС
Вы можете использовать Excel ДВССЫЛ с функцией АДРЕС, чтобы получить значение в определенной ячейке на лету.
Как вы помните, функция АДРЕС в Excel используется для получения адреса ячейки по номеру строки и столбца. Например, формула =АДРЕС(1;3) возвращает текстовую строку «$C$1», так как C1 — это ячейка на пересечении строки 1 и столбца 3.
Чтобы создать ссылку на ячейку, просто вставьте функцию АДРЕС в формулу ДВССЫЛ, например:
= НЕПРЯМОЙ (АДРЕС (1,3))
Конечно, эта простая формула только демонстрирует технику. Дополнительные примеры использования функций ДВССЫЛ И АДРЕС в Excel см в разделе Преобразование строк в столбцы в Excel .
А вот еще несколько примеров формул с использованием функции ДВССЫЛ, которые могут оказаться полезными:
- VLOOKUP и INDIRECT — как динамически извлекать данные из разных таблиц (см пример 2).
- Excel ДВССЫЛ и СЧЁТЕСЛИ - как использовать функцию СЧЁТЕСЛИ в несмежном диапазоне или нескольких выбранных ячейках.
Использование ДВССЫЛ для создания выпадающих списков
Вы можете использовать функцию Excel ДВССЫЛ с инструментом проверки данных для создания раскрывающихся списков. Они показывают разные варианты в зависимости от того, какое значение пользователь ввел в предыдущем раскрывающемся списке.
Простой зависимый выпадающий список очень легко создать. Все, что требуется, — это несколько именованных областей для хранения выпадающего списка.
В ячейке A1 создайте простой раскрывающийся список с именами доступных именованных диапазонов. Для второго зависимого раскрывающегося списка в ячейке B2 используйте простую формулу = ДВССЫЛ (A1), где A1 — это ячейка, в которой выбрано имя желаемого именованного диапазона.
Например, если мы выбираем второй квартал в первом списке, мы видим месяцы для этого квартала во втором списке.
Рис. 9
Для создания более сложных трехуровневых меню или раскрывающихся списков с многоуровневыми записями вам потребуется немного более сложная формула ДВССЫЛ в сочетании с вложенной функцией ЗАМЕНИТЬ.
Подробное пошаговое руководство по использованию ДВССЫЛ с проверкой данных Excel см в этом руководстве: Как создать зависимый раскрывающийся список в Excel.
Функция ДВССЫЛ Excel - возможные ошибки и проблемы
Как показано в приведенных выше примерах, функция ДВССЫЛ очень полезна при работе со ссылками на ячейки и диапазоны. Однако не все пользователи Excel принимают этот подход, главным образом потому, что постоянное использование ДВССЫЛЫ приводит к отсутствию прозрачности в формулах Excel и затрудняет их понимание. Функцию ДВССЫЛ трудно увидеть и проанализировать, потому что ячейка, на которую она ссылается, не является конечным местоположением значения, используемого в формуле. Это действительно довольно запутанно, особенно когда вы имеете дело с большими сложными формулами.
В дополнение к вышесказанному, как и любая другая функция Excel, ДВССЫЛ может выдать ошибку, если вы злоупотребляете аргументами функции. Вот список наиболее распространенных ошибок и проблем:
Ошибка #ССЫЛКА!
Чаще всего функция ДВССЫЛ возвращает ошибку #ССЫЛКА! в следующих случаях:
- Аргумент ссылки на ячейку не является допустимой ссылкой Excel. Если вы попытаетесь передать в функцию текст, который не может ссылаться на ячейку (например, «A1B0»), формула выдаст ошибку #ССЫЛКА!. Проверьте аргументы функции ДВССЫЛ, чтобы избежать возможных проблем .
- Превышен лимит размера области. Если аргумент cell_reference в формуле ДВССЫЛ ссылается на диапазон ячеек за пределами строки 1048576 или столбца 16384, вы также получите ошибку #ССЫЛКА в Excel 2007 и более поздних версиях. Более ранние версии Excel игнорируют превышение этого ограничения и возвращают некоторое значение, но часто не то, что вы ожидаете.
- Лист или рабочая книга, используемые в формуле, закрываются. Если формула ДВССЫЛ ссылается на другую книгу или лист Excel, другая книга или лист должны быть открыты, иначе ДВССЫЛ возвращает ошибку #ССЫЛКА! . Однако это требование характерно для всех формул, ссылающихся на другие книги Excel.
Ошибка #ИМЯ?
Это наиболее очевидный случай, который предполагает наличие какой-то ошибки в имени функции.
Ошибка из-за несовпадения региональных настроек.
Кроме того, распространенная проблема связана не с именем функции ДВССЫЛ, а с разными локалями для разделителя списка.
В европейских странах запятая зарезервирована как десятичная точка, а точка с запятой используется как разделитель списка.
В конфигурации Windows по умолчанию для Северной Америки и некоторых других стран разделителем списка по умолчанию является запятая.
В результате, когда вы копируете формулу между двумя разными местоположениями Excel, вы можете получить сообщение об ошибке «Мы столкнулись с проблемой с этой формулой...», потому что разделитель списка, используемый в формуле, отличается от того, который указан на компьютере. Если вы столкнулись с этой ошибкой при копировании ДВССЫЛОЙ формулы из этого руководства в Excel, замените все запятые (,) точками с запятой (;) (или наоборот). В обычных формулах Excel, конечно, такой проблемы не возникнет. Там Excel автоматически изменит разделители в зависимости от ваших текущих региональных настроек.
Чтобы проверить, какой разделитель списка и десятичная точка установлены на вашем компьютере, откройте панель управления и выберите «Регион и язык» > «Дополнительные параметры».
Мы надеемся, что это руководство пролило на вас некоторый свет на использование ДВССЫЛ в Excel. Теперь, когда вы знаете ее сильные стороны и ограничения, пришло время попробовать ее и посмотреть, как функция ДВССЫЛ может упростить ваши задачи в Excel. Спасибо за чтение!
