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

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

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

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

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

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

Как можно использовать функцию INDIRECT (ДВССЫЛ)?

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

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

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

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

INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)

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

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

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

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

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

=SUM(C2:C7)
=СУММ(C2:C7)

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

=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)

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

=SUM(C3:C8)
=СУММ(C3:C8)

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

=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)

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

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

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

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

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

=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))

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

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

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

=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)

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

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

=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))

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

=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))

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

=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))

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

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

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