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