Всем привет! Сегодня начинаем разбирать функцию ДВССЫЛ (INDIRECT) в Excel.
Сама по себе, функция очень проста - она превращает текст в ссылку. Например, две нижеследующие формулы аналогичны:
=ДВССЫЛ (A1) (оно же в англоязычном интерфейсе INDIRECT (A1))
=A1
После такого описания может показаться, что функция ДВССЫЛ - это какой-то атавизм из древних версий Excel, ведь, если можно простым способом обращаться к ячейке, то зачем его усложнять? Но это типичное заблуждение, с которым я и сам когда-то столкнулся на начальном этапе изучения Excel. На самом деле, функция достаточно интересная - в некоторых случаях она может заметно облегчить жизнь, хоть многие и считают, что использовать ДВССЫЛ - это моветон:)
Начнем с простого. Синтаксис функции:
=ДВССЫЛ(ссылка_на_текст;[a1]). Здесь:
- ссылка_на_текст. Обязательный аргумент, в котором указывается ссылка на ячейку в формате текста. Это может быть ссылка, на ячейку с этим текстом, например, =ДВССЫЛ(A1). Или это может быть сразу текст, например, так: =ДВССЫЛ("A1") - помним, что текст в формулах прописывается в кавычках.
- [a1]. Необязательный аргумент для определения типа ссылки. Если имеет значение ИСТИНА (TRUE) или опущен, то ссылка на текст воспринимается, как ссылка типа A1. Если указано значение ЛОЖЬ (FALSE), то воспринимается, как ссылка в стиле R1C1.
Посмотрим, как это работает.
1. Классический способ применения функции ДВССЫЛ (INDIRECT)
Имеем таблицу с наименованием городов и численностью их населения. Задача: сослаться на ячейку с численностью - пусть это будет ячейка. B2. Наши действия:
- привычный способ - обратиться через знак ровно: =B2
- через ДВССЫЛ эта же формула будет выглядеть так: ДВССЫЛ("B2"). Помним, что в кавычках указывается текст, поэтому функция сразу воспринимает указанное, как адрес необходимой ячейки.
- если в файле в отдельной ячейке указана текстом ссылка на ячейку B2 (в нашем примере это значение прописано в H2), то функция будет выглядеть следующим образом: =ДВССЫЛ(H2). Здесь мы не указываем ковычки, поэтому функция понимает, что адрес необходимой ячейки прописан в ячейке H2. Иллюстрация ниже.
Теперь, когда мы поняли, как это работает, посмотрим, где это можно использовать.
2. Транспонирование данных с помощью функции ДВССЫЛ (INDIRECT)
Задача: имеем вертикальный диапазон с названиями, но хотим преобразовать его в горизонтальный.
ДВССЫЛ в данном случае не является классическим решением задачи, но тоже с ней справится. Для этого нам нужно прописать такую формулу, которая при горизонтальном протягивании будет каждый раз ссылаться на ячейку ниже. Можно было бы прописывать руками номер строки, но в примере ниже мы воспользуемся функцией СТОЛБЕЦ (COLUMN), которая вернет номер столбца текущей ячейки. В моем примере они совпадут - данные расположены удобно, чтобы не перегружать формулу лишними вычислениями для вашей наглядности (исходник начинается со 2й строки, транспонирую в диапазон, который начинается со 2го столбца). На практике может потребоваться скорректировать полученный результат фунции СТОЛБЕЦ, например, вычитанием разницы.
3. Зависимый выпадающий список
Задача: при заполнении отчета необходимо, чтобы в выпадающем списке были не все значения, а лишь те, которые соответствуют какому-то ранее указанному признаку. Например, имеем три филиала с названиями Московский, Тверской, Тульский - каждый филиал отвечает за определенные города, находящиеся поблизости >> хотим, чтобы в отчете при выборе московского филиала в выпадающем списке появлялись только его города.
Решаем задачу в несколько этапов:
- Создадим именованные диапазоны, в которых будут перечислены города (в нашем случае это диапазоны A2:A5, B2:B5 и C2:С5) >> каждому присвоим имя филиала (Московский, Тверской, Тульский)
- Создаем первый выпадающий список, где нам будет предложено выбрать филиал. Для этого встаем на необходимую ячейку (у меня это C7) и следуем по пути:
закладка "Данные">>Проверка данных>> в открывшемся окне выбираем типа данных "Список", а в качестве источника указываем диапазон, где у нас прописаны наименования филиалов (A1:C1)
- Теперь ниже создаем второй выпадающий список для городов. Здесь мы проходим тот же путь, но в качестве источника используем формулу ДВССЫЛ, которая будет ссылаться на выбранный филиал:
- Результат - список предлагаемых городов автоматически обновляется под выбранный филиал.
Пока всё. В следующей статье расскажу еще о нескольких приемах использования этой функции - подписывайтесь, будет интересно.
Как обычно, вопросы и комментарии - приветствуются!
PS. Подготовил продолжение статьи о сборе данных с разных листов с помощью этой функции: ссылка
