Содержание:
Поздравляем! Вы дошли до финального дня марафона 30 функций Excel за 30 дней. Это был долгий и интересный путь, в ходе которого вы узнали много полезного о функциях Excel.
30-й день марафона мы посвятим изучению функции КОСВЕННЫЕ (ДВССЫЛ), который возвращает ссылку, указанную в текстовой строке. С помощью этой функции вы можете создавать зависимые выпадающие списки. Например, при выборе страны из раскрывающегося списка определяется, какие параметры появятся в раскрывающемся списке городов.
Итак, рассмотрим подробнее теоретическую часть функции КОСВЕННЫЕ (КОСВЕННЫЙ) и изучите практические примеры его применения. Если у вас есть дополнительная информация или примеры, поделитесь ими в комментариях.
Функция 30: КОСВЕННАЯ
Функция КОСВЕННЫЕ (ДВССЫЛ) возвращает ссылку, указанную в текстовой строке.
Как можно использовать функцию ДВССЫЛ?
Так как функция КОСВЕННЫЕ (ДВССЫЛ) возвращает ссылку, заданную текстовой строкой, вы можете использовать ее для:
- Создайте неизменяемую начальную ссылку.
- Создайте ссылку на статический именованный диапазон.
- Создайте ссылку, используя информацию о листе, строке и столбце.
- Создайте несмещающийся массив чисел.
Синтаксис ДВССЫЛ (ДВССЫЛ)
Функция КОСВЕННЫЕ (ДВССЫЛ) имеет следующий синтаксис:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) — текст ссылки.
- a1 – если равно TRUE (ИСТИНА) или не указано, то будет использован стиль ссылки A1; а если ЛОЖЬ (FALSE), то стиль R1C1.
Ловушки НЕПРЯМЫЕ (INDIRECT)
- Функция КОСВЕННЫЕ (ДВССЫЛ) пересчитывается всякий раз, когда изменяются значения на листе Excel. Это может значительно замедлить работу книги, если функция используется во многих формулах.
- Если функция КОСВЕННЫЕ (ДВССЫЛ) создает ссылку на другую книгу Excel; эта книга должна быть открыта, иначе формула сообщит об ошибке. #REF! (#СВЯЗЬ!).
- Если функция КОСВЕННЫЕ (ДВССЫЛ) ссылается на диапазон, превышающий ограничение по строкам и столбцам, формула сообщит об ошибке #REF! (#СВЯЗЬ!).
- Функция КОСВЕННЫЕ (ДВССЫЛ) не может ссылаться на динамический именованный диапазон.
Пример 1. Создайте неизменяемую исходную ссылку.
В первом примере столбцы C и E содержат одинаковые числа, их суммы рассчитываются с помощью функции SUM (СУММ) также одинаковы. Однако формулы немного отличаются. В ячейке C8 формула:
=SUM(C2:C7)
=СУММ(C2:C7)
В ячейке E8 функция КОСВЕННЫЕ (ДВССЫЛ) создает ссылку на начальную ячейку E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Если вставить строку вверху листа и добавить значение за январь (январь), то сумма в столбце С не изменится. Формула изменится, реагируя на добавление строки:
=SUM(C3:C8)
=СУММ(C3:C8)
Однако функция КОСВЕННЫЕ (ДВССЫЛ) фиксирует E2 в качестве начальной ячейки, поэтому январь автоматически включается в расчет итогов столбца E. Конечная ячейка изменилась, но стартовая ячейка не была затронута.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Пример 2. Ссылка на статический именованный диапазон.
Функция КОСВЕННЫЕ (ДВССЫЛ) может создать ссылку на именованный диапазон. В этом примере синие ячейки составляют диапазон Нумлист. Кроме того, из значений в столбце B также создается динамический диапазон. NumListDyn, в зависимости от количества чисел в этом столбце.
Сумму для обоих диапазонов можно вычислить, просто указав ее имя в качестве аргумента функции. SUM (СУММ), как вы можете видеть в ячейках E3 и E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Вместо ввода имени диапазона в функцию SUM (СУММА). Вы можете обратиться к имени, написанному в одной из ячеек рабочего листа. Например, если имя Нумлист записан в ячейке D7, то формула в ячейке E7 будет такой:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
К сожалению, функция КОСВЕННЫЕ (ДВССЫЛ) не может создать ссылку на динамический диапазон, поэтому при копировании этой формулы в ячейку E8 вы получите сообщение об ошибке. #REF! (#СВЯЗЬ!).
Пример 3. Создайте ссылку, используя информацию листа, строки и столбца.
Вы можете легко создать ссылку на основе номеров строк и столбцов, а также используя значение ЛОЖЬ (FALSE) для второго аргумента функции. КОСВЕННЫЕ (КОСВЕННО). Вот как создается ссылка на стиль 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}))
Если вам нужен массив большего размера, то вряд ли вы захотите вводить в формулу все числа. Второй вариант — использовать функцию РЯД (СТРОКА), как это сделано в формуле массива, введенной в ячейку D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Третий вариант — использовать функцию РЯД (STRING) вместе с КОСВЕННЫЕ (ДВССЫЛ), как это сделано с формулой массива в ячейке D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Результат для всех трех формул будет одинаковым:
Однако если строки вставлены вверху листа, вторая формула вернет неверный результат из-за того, что ссылки в формуле будут меняться вместе со сдвигом строки. Теперь вместо среднего значения трех самых больших чисел формула возвращает среднее значение 3-го, 4-го и 5-го самых больших чисел.
Использование функций КОСВЕННЫЕ (ДВССЫЛ), третья формула сохраняет правильные ссылки на строки и продолжает показывать правильный результат.