Как только вы научитесь использовать косвенную функцию в Excel, вы сможете получать информацию из других листов, ссылаться на именованные диапазоны и комбинировать ее с другими функциями для создания действительно универсального инструмента. Чтобы освоиться, может потребоваться немного практики, но с косвенной функцией вы можете сделать больше, чем вы думаете.
Инструкции в этой статье относятся к Excel для Microsoft 365, Excel 2019 и Excel 2016.
Что такое косвенная функция?
Косвенная функция - это способ преобразования текстовой строки в ссылку. То есть извлекает информацию из ссылки на другую ячейку или диапазон. Он создает ссылку из текста и не изменяется при изменении, добавлении или удалении ячеек, строк или столбцов из цитируемого диапазона. Ссылки, которые он создает, оцениваются в режиме реального времени, поэтому эталон всегда точно соответствует данным, из которых он строится.
Если это кажется немного запутанным, не волнуйтесь. Косвенную формулу легче понять на действительных примерах и на практике. Если вы сомневаетесь, попробуйте выполнить следующие шаги, и вскоре вы освоитесь.
Использование косвенной функции с именованными диапазонами
Именованные диапазоны в Excel - отличный способ собрать данные по одной ссылке, а непрямая функция упрощает получение этой информации из них. Вот как это сделать:
-
Откройте документ Excel с уже примененными именованными диапазонами. В нашем примере у нас есть информация о продажах различных продуктов питания и напитков, а деньги, заработанные за каждый день недели, собираются в именованных диапазонах, названных в честь продуктов.
Image -
Выберите ячейку для именованного диапазона и введите в нее один из них. В нашем примере мы использовали Burgers. Если хотите, добавьте другие названия обозначений и раскраски.
Image -
Выберите другую ячейку, в которую вы хотите направить непрямой вывод. Поскольку мы хотим суммировать весь объем продаж за неделю для определенного продукта питания, в данном случае для бургеров, мы введем в ячейку следующее:
=СУММ(ДВССЫЛ(G5)
-
Обозначает функцию СУММ, которая будет использовать косвенную функцию для получения информации из именованного диапазона в ячейке G5, в данном случае Бургеры. Результат: 3781, сумма продаж бургеров за неделю.
В нашем примере мы можем заменить Бургеры в ячейке G5 на Лимонад или Десерты, два других именованных диапазона, и вместо этого выходные данные изменятся на их сумму СУММ.
Image
Использование косвенной функции на нескольких листах
Косвенная формула еще более эффективна, когда вы используете ее для извлечения информации из других листов. Для этого вам также не нужно использовать именованные диапазоны.
- Откройте документ Excel с несколькими листами или создайте их со всей необходимой информацией.
- На листе, куда вы хотите направить непрямой вывод, создайте ячейку с именем листа, из которого вы хотите извлечь информацию. В нашем примере это FoodSales..
-
Поскольку мы хотим извлечь информацию из нашего листа FoodSales, чтобы подсчитать количество проданных Бургеров, мы ввели следующее (замените диапазон ячеек и имена листов с вашими собственными):
=СУММ(ДВССЫЛ(B4&"!B4:B10"))
Image - Это обозначает функцию СУММ, так как мы пытаемся найти итог. Затем он назначает ячейку B4 справочным текстом для косвенной функции. & объединяет элементы этой функции, за которыми следуют кавычка и восклицательный знак, а затем диапазон ячеек, из которых мы хотим получить данные. B4 по B10
- Вывод – это сумма продаж бургеров за эту неделю. Когда мы создаем новый лист FoodSales2 для новой недели с другими числами, нам нужно только настроить ячейку B4, чтобы она говорила FoodSales2, чтобы получить данные о продажах бургеров за эту неделю.
Использование косвенной функции со ссылкой на стиль R1C1
Для листов, которые постоянно расширяются, где ссылка, которую вы хотите использовать, не всегда будет находиться в одной и той же ячейке, ссылки стиля R1C1 можно использовать с косвенной формулой, чтобы предоставить вам необходимую информацию. Мы продолжим использовать здесь наши примеры продаж продуктов питания, но представьте, что это таблица более высокого уровня, в которой просматриваются общие недельные продажи.
- Откройте документ Excel со всеми данными, которые вы хотите извлечь, и выберите ячейку для косвенного вывода функции. В нашем примере мы смотрим на месячные итоги продаж продуктов питания и хотим узнать самый последний общий объем продаж за месяц.
-
В нашем примере формула выглядит так:
=ДВССЫЛ("R12C"&СЧЁТ(12:12), ЛОЖЬ)
Image - Косвенная функция использует R12 (строка 12), за которой следует C для обозначения столбца, заключенного в кавычки. & соединяет две части функции вместе. Мы используем функцию COUNTA для подсчета всех непустых ячеек в строке 12 (выбор строки или ввод 12:12) с последующей запятой. FALSE обозначает ссылку R1C1.
-
Выводом будет последняя запись в нашей таблице, в данном случае 8102 или 8 102 долларов. Когда мы в конце концов добавим данные о продажах за апрель, последний номер продаж будет автоматически обновляться в режиме реального времени.
Image
