Содержание:
На первый взгляд (особенно при чтении справки) функция КОСВЕННЫЕ (КОСВЕННО) выглядит просто и даже ненужно. Его суть в том, чтобы превратить текст, похожий на ссылку, в полноценную ссылку. Те. если нам нужно сослаться на ячейку А1, то мы можем либо по привычке сделать прямую ссылку (вводим знак равенства в D1, кликаем на А1 и нажимаем Enter), либо можем использовать КОСВЕННЫЕ с той же целью:
Обратите внимание, что аргумент функции — ссылка на A1 — вводится в кавычках, так что, по сути, здесь это текст.
«Ну окей», — скажете вы. «А в чем выгода?»
Но не судите по первому впечатлению – оно обманчиво. Эта функция может помочь вам во многих ситуациях.
Пример 1. Транспонирование
Классика жанра: нужно крутить вертикальный диаметр
канавку в горизонтальную (транспонировать). Конечно, вы можете использовать специальную вставку или функцию ТРАНСПОРТ (ТРАНСПОН) в формуле массива, но можно обойтись и нашим КОСВЕННЫЕ:
Логика проста: чтобы получить адрес следующей ячейки, мы склеиваем букву «А» со спецсимволом «&» и номер столбца текущей ячейки, который нам дает функция КОЛОНКА (СТОЛБЕЦ).
Обратную процедуру лучше сделать немного по-другому. Поскольку на этот раз нам необходимо сформировать ссылку на ячейки B2, C2, D2 и т.д., вместо классического «морского боя» удобнее использовать режим связи R1C1. В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C2, С2=R1C3, Д2=R1C4 и так далее
Здесь на помощь приходит второй необязательный аргумент функции. КОСВЕННЫЕ. Если оно равно ЛЕЖА (ЛОЖНЫЙ), то вы можете установить адрес ссылки в режиме R1C1. Таким образом, мы можем легко перенести горизонтальный диапазон обратно в вертикальный:
Пример 2. Сумма по интервалу
Мы уже разбирали один из способов суммирования по окну (диапазону) заданного размера на листе с помощью функции УДАЛЕНИЕ (КОМПЕНСИРОВАТЬ). Аналогичную задачу можно решить и с помощью КОСВЕННЫЕ. Если нам нужно суммировать данные только за определенный диапазон-период, то мы можем склеить их из кусочков и затем превратить в полноценную ссылку, которую можно вставить внутрь функции SUM (СУММ):
Пример 3. Раскрывающийся список смарт-таблицы
Иногда Microsoft Excel не рассматривает имена и столбцы интеллектуальных таблиц как полные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные – Проверка данных) на основе столбца Сотрудники из умного стола Люди мы получим ошибку:
Если мы «обернем» ссылку нашей функцией КОСВЕННЫЕ, то Excel легко его примет и наш раскрывающийся список будет динамически обновляться при добавлении новых сотрудников в конец смарт-таблицы:
Пример 4. Неразрывные ссылки
Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на листе. В большинстве случаев это правильно и удобно, но не всегда. Допустим, нам нужно перенести имена из справочника сотрудников в отчет:
Если поставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то при удалении, например, Даши, мы получим #ССЫЛКА! ошибка в соответствующей ей зеленой ячейке. (#ССЫЛКА!). В случае использования функции создания ссылок КОСВЕННЫЕ такой проблемы не будет.
Пример 5. Сбор данных с нескольких листов
Предположим, у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаила, Елены, Ивана, Сергея, Дмитрия):
Предположим, что форма, размер, положение и последовательность товаров и месяцев во всех таблицах одинаковы – различаются только цифры.
Собрать данные со всех листов (не суммировать, а сложить друг под другом «кучей») можно всего по одной формуле:
Как видите, идея та же: приклеиваем ссылку на нужную ячейку данного листа, и КОСВЕННЫЕ превращает его в «живое». Для удобства над таблицей я добавил буквы столбцов (Б,В,Г), а справа – номера строк, которые нужно взять с каждого листа.
Ловушки
Если вы используете КОСВЕННЫЕ (КОСВЕННО) нужно помнить о его слабых сторонах:
- Если сделать ссылку на другой файл (склеив имя файла в квадратных скобках, имя листа и адрес ячейки), то это работает только пока открыт исходный файл. Если мы закроем его, то получим ошибку #LINK!
- INDIRECT не может ссылаться на динамический именованный диапазон. В статике - без проблем.
- ДВССЫЛ — это изменчивая или «летучая» функция, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только при воздействии на ячейки, как в обычных функциях. Это плохо влияет на производительность и большими таблицами INDIRECT лучше не увлекаться.
- Как создать динамический диапазон с автоматическим изменением размера
- Суммирование по окну диапазона на листе с помощью функции СМЕЩ.