Как использовать косвенную функцию в Excel

Как только вы научитесь использовать косвенную функцию в Excel, вы можете брать информацию с других листов, ссылаться на именованные диапазоны и комбинировать ее с другие функции чтобы создать действительно универсальный инструмент. Чтобы разобраться в этом, может потребоваться небольшая практика, но с косвенной функцией вы можете сделать больше, чем вы думаете.

Что такое косвенная функция?

Косвенная функция - это способ преобразования текстовой строки в ссылку. То есть он извлекает информацию из ссылки на другую ячейку или диапазон. Он создает ссылку из текста и не изменяется при изменении, добавлении или удалении ячеек, строк или столбцов из цитируемого диапазона. Создаваемые ссылки оцениваются в режиме реального времени, поэтому ссылка всегда соответствует данным, из которых она извлекается.

Если это немного сбивает с толку, не волнуйтесь. Косвенную формулу легче понять на реальных примерах и на практике. Если сомневаетесь, следуйте инструкциям ниже, и вы скоро научитесь.

Использование косвенной функции с именованными диапазонами

Именованные диапазоны в Excel - отличный способ собрать данные по одной ссылке, а косвенная функция значительно упрощает получение этой информации из них. Вот как это сделать:

1. Откройте документ Excel с помощью именованные диапазоны уже применены. В нашем примере у нас есть информация о продажах различных продуктов и напитков, а деньги, заработанные за каждый день недели, собираются в названных диапазонах, названных после продуктов.

   

2. Выберите ячейку для именованного диапазона и введите в нее одну из них. В нашем примере мы использовали Бургеры. При желании добавьте другие обозначения и цвета.

   

3. Выберите другую ячейку, в которую вы хотите направить косвенный вывод. Поскольку мы хотим сложить весь объем продаж за неделю для определенного продукта питания, в данном случае бургеров, мы введем в ячейку следующее:

   = СУММ (КОСВЕННАЯ (G5)

4. Это обозначает функцию СУММ, которая будет использовать косвенную функцию для извлечения информации из именованного диапазона в ячейке G5, в данном случае Бургеры. Выпущено 3781, общий объем продаж бургеров за неделю.

   В нашем примере мы можем заменить гамбургеры в ячейке G5 на «Лимонад» или «Десерты», два других именованных диапазона, и вместо этого результат изменится на их сумму SUM.

   

Использование косвенной функции на нескольких листах

Косвенная формула становится еще более действенной, когда вы используете ее для извлечения информации с других листов. Для этого вам также не нужно использовать именованные диапазоны.

1. Откройте документ Excel с несколькими листами или создайте их со всей необходимой информацией.

2. На листе, на который вы хотите направить косвенный вывод, создайте ячейку с именем листа, с которого вы хотите получить информацию. В нашем примере это Продовольствие.

3. Поскольку мы хотим получать информацию из наших Продовольствие лист для общего количества Бургеры sold, мы ввели следующее (замените диапазон ячеек и имена листов своими):

   = СУММ (КОСВЕННО (B4 & "! B4: B10"))

   

4. Это обозначает его как функцию СУММ, поскольку мы пытаемся найти сумму. Затем он обозначает ячейку B4 как текст ссылки для косвенной функции. В & объединяет элементы этой функции, за которыми следуют цитата и восклицательный знак, а затем диапазон ячеек, из которых мы хотим извлечь данные. B4 через B10.

5. Результат - это общее количество продаж бургеров за эту неделю. Когда мы создаем новый лист FoodSales2 для новой недели с другими числами, нам нужно только настроить ячейку B4, чтобы сказать ПродовольствиеПродажи2 чтобы получить данные о продажах бургеров за эту неделю.

Использование косвенной функции со справочником по стилю R1C1

Для листов, которые постоянно расширяются, где ссылка, которую вы хотите использовать, не всегда будет в В той же ячейке ссылки на стиль R1C1 можно использовать с косвенной формулой, чтобы предоставить вам информацию, которую вы необходимость. Мы продолжим использовать здесь наши примеры продаж продуктов питания, но представьте, что это для таблицы более высокого уровня, которая рассматривает итоги продаж за неделю в целом.

1. Откройте документ Excel со всеми данными, которые вы хотите использовать, и выберите ячейку для вывода косвенной функции. В нашем примере мы смотрим на ежемесячные итоги продаж продуктов питания и хотим знать самый последний общий объем продаж за месяц.

2. В нашем примере формула выглядит так:

   = КОСВЕННО ("R12C" & COUNTA (12:12); FALSE)

   

3. Косвенная функция использует R12 (строка 12), за которым следует C для обозначения столбца, заключенного в кавычки. В & объединяет две части функции вместе. Мы используем функцию COUNTA для подсчета всех непустых ячеек в строке 12 (выделение строки или ввод 12:12), за которыми следует запятая. FALSE обозначает это как ссылку R1C1.

4. Результатом будет последняя запись в нашей таблице, в данном случае 8102 или 8 102 доллара. Когда мы в конечном итоге добавим данные о продажах за апрель, последний номер продаж будет обновляться автоматически в режиме реального времени.