Функция КОСВЕННО в Excel (объяснение с примерами + видео)

Функция КОСВЕННО в Excel - Обзор

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

Короче говоря, вы можете использовать косвенную формулу, чтобы вернуть ссылку, указанную текстовой строкой.

В этом руководстве по Excel я покажу вам, как использовать косвенную функцию в Excel, используя несколько практических примеров.

Но прежде чем я перейду к примерам, давайте сначала посмотрим на его синтаксис.

НЕПРЯМАЯ ФУНКЦИЯ Синтаксис

= ДВССЫЛ (ref_text; [a1])

Входные аргументы

• ref_text - Текстовая строка, содержащая ссылку на ячейку или именованный диапазон. Это должна быть действительная ссылка на ячейку, иначе функция вернет # ССЫЛКУ! ошибка
• [a1] - Логическое значение, указывающее, какой тип ссылки использовать для текст ссылки. Это может быть ИСТИНА (указывает на ссылку стиля A1) или ЛОЖЬ (указывает ссылку в стиле R1C1). Если опущено, по умолчанию это ИСТИНА.

Дополнительные замечания

• КОСВЕННЫЙ - непостоянная функция. Это означает, что он пересчитывается всякий раз, когда открыта книга Excel или когда на листе запускается вычисление. Это увеличивает время обработки и замедляет вашу книгу. Хотя вы можете использовать косвенную формулу с небольшими наборами данных с незначительным влиянием или без влияния на скорость, вы можете увидеть, что это замедляет работу вашей книги при использовании ее с большими наборами данных.
• Справочный текст (ref_text) может быть:
  • Ссылка на ячейку, которая, в свою очередь, содержит ссылку в формате ссылок в стиле A1 или R1C1.
  • Ссылка на ячейку в двойных кавычках.
  • Именованный диапазон, возвращающий ссылку

Примеры использования косвенной функции в Excel

Теперь давайте углубимся и рассмотрим несколько примеров использования функции КОСВЕННО в Excel.

Пример 1. Использование ссылки на ячейку для получения значения

Он принимает ссылку на ячейку в виде текстовой строки в качестве входных и возвращает значение в этой ссылке (как показано в примере ниже):

Формула в ячейке C1:

= КОСВЕННО ("A1")

Приведенная выше формула принимает ссылку на ячейку A1 в качестве входного аргумента (в двойных кавычках как текстовую строку) и возвращает значение в этой ячейке, равное 123.

Теперь, если вы думаете, почему бы мне просто не использовать = A1 вместо функции КОСВЕННО, у вас есть правильный вопрос.

Вот почему …

Когда вы используете = A1 или = $ A $ 1, это дает тот же результат. Но когда вы вставляете строку над первой строкой, вы заметите, что ссылки на ячейки автоматически изменятся, чтобы учесть новую строку.

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

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

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

В приведенном выше примере ячейка A1 имеет значение 123.

Ячейка C1 имеет ссылку на ячейку A1 (в виде текстовой строки).

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

Это, в свою очередь, означает, что функция будет обращаться к ячейке A1 и возвращать значение в ней.

Обратите внимание, что здесь не нужно использовать двойные кавычки, поскольку в C1 ссылка на ячейку хранится только в формате текстовой строки.

Кроме того, если текстовая строка в ячейке C1 не является действительной ссылкой на ячейку, функция Indirect вернет #REF! ошибка.

Пример 3: Создание ссылки с использованием значения в ячейке

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

Например, если ячейка C1 содержит число 2, и вы используете формулу = КОСВЕННО («A» и C1) тогда он будет относиться к ячейке A2.

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

Если текстовая строка, которую вы используете в формуле, дает ссылку, которую Excel не понимает, она вернет ошибку ссылки (#REF!).

Пример 4: вычислить сумму диапазона ячеек

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

Например, = КОСВЕННО («A1: A5») будет относиться к диапазону A1: A5.

Затем вы можете использовать функцию СУММ, чтобы найти сумму, или функцию НАИБОЛЬШИЙ / МАЛЫЙ / МИН / МАКС, чтобы выполнить другие вычисления.

Как и функция СУММ, вы также можете использовать такие функции, как НАИБОЛЬШИЙ, МАКС / МИН, СЧЁТ и т. Д.

Пример 5: Создание ссылки на лист с помощью функции КОСВЕННО

В приведенных выше примерах показано, как указать ячейку на том же листе. Вы также можете использовать формулу КОСВЕННО для ссылки на ячейку на другом листе или в другой книге.

Вот что вам нужно знать о ссылках на другие листы:

• Допустим, у вас есть рабочий лист с именем Sheet1, а внутри листа в ячейке A1 у вас есть значение 123. Если вы перейдете на другой лист (скажем, Sheet2) и обратитесь к ячейке A1 в Sheet1, формула будет выглядеть так: = Лист1! A1

Но…

• Если у вас есть рабочий лист, содержащий два или более двух слов (с пробелом между ними), и вы ссылаетесь на ячейку A1 на этом листе с другого листа, формула будет выглядеть следующим образом: = ’Data Set’! A1

В случае нескольких слов Excel автоматически вставляет одинарные кавычки в начало и конец имени листа.

Теперь давайте посмотрим, как создать КОСВЕННУЮ функцию для ссылки на ячейку на другом листе.

Предположим, у вас есть лист с именем Dataset и ячейка A1 в нем имеет значение 123.

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

= КОСВЕННО ("'Набор данных'! A1")

Как видите, ссылка на ячейку также должна содержать имя рабочего листа.

Если у вас есть имя рабочего листа в ячейке (скажем, A1), вы можете использовать следующую формулу:

= КОСВЕННО ("'" & A1 & "'! A1")

Если у вас есть имя рабочего листа в ячейке A1 и адрес ячейки в ячейке A2, то формула будет выглядеть следующим образом:

= КОСВЕННО ("'" & A1 & "'!" & A2)

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

Это может быть полезно, когда вы пытаетесь создать сводный лист, который извлекает данные из нескольких разных листов.

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

Пример 6: Обращение к именованному диапазону с использованием косвенной формулы

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

Например, предположим, что у вас есть оценки для 5 студентов по трем предметам, как показано ниже:

В этом примере назовем ячейки:

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

Чтобы назвать диапазон ячеек, просто выберите ячейки и перейдите в поле имени, введите имя и нажмите Enter.

Теперь вы можете ссылаться на эти именованные диапазоны, используя формулу:

= КОСВЕННЫЙ («Именованный диапазон»)

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

= СРЕДНИЙ (КОСВЕННЫЙ («Математический»))

Если у вас есть имя именованного диапазона в ячейке (F2 в приведенном ниже примере имеет имя Math), вы можете использовать его непосредственно в формуле.

В приведенном ниже примере показано, как рассчитать среднее значение с использованием названных диапазонов.

Пример 7: Создание зависимого раскрывающегося списка с помощью функции НЕПРЯМОЙ Excel

Это одно из отличных применений этой функции. Вы можете легко создать зависимый раскрывающийся список с его помощью (также называемый условным раскрывающимся списком).

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

Теперь, чтобы создать зависимый раскрывающийся список, вам нужно создать два именованных диапазона: A2: A5 с именем US и B2: B5 с именем India.

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

Теперь, чтобы создать зависимый выпадающий список:

• Выберите ячейку E2 (ячейку, в которой вы хотите получить зависимый раскрывающийся список).
• Перейдите на вкладку "Данные".
• Щелкните Проверка данных.
• Выберите Список в качестве критерия проверки и используйте следующую формулу в исходном поле: = КОСВЕННО ($ D $ 2)
• Щелкните ОК.

Теперь, когда вы вводите США в ячейку D2, раскрывающийся список в ячейке E2 покажет штаты в США.

И когда вы вводите Индию в ячейку D2, раскрывающийся список в ячейке E2 покажет штаты Индии.

Итак, это несколько примеров использования функции ДВССЫЛ в Excel. Эти примеры будут работать во всех версиях Excel (Office 365, Excel2021-2022 / 2016/2013/2013).

Надеюсь, вы нашли этот урок полезным.

• Функция ВПР в Excel.
• Функция Excel HLOOKUP.
• Функция ИНДЕКС Excel.
• Функция ПОИСКПОЗ в Excel.
• Функция смещения Excel.