Как использовать функцию КОСВЕННО (INDIRECT) в Google Таблицах

Содержание:

В Google Таблицах существует множество функций, большинство из которых довольно просты для понимания. Однако функция КОСВЕННО (INDIRECT) — одна из тех функций Google Таблиц, которые поначалу может быть не так просто «понять».

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

Что делает функция КОСВЕННО?

Проще говоря, функция INDIRECT (рус. Косвенно) возвращает ссылку на заданный диапазон.

Поясню на примере:

Предположим, у вас есть значение 5 в ячейке A1 и вы установите его для использования формулы = INDIRECT(«A1»), тогда в результате вы получите 5.

Теперь вы можете подумать, зачем вам использовать формулу КОСВЕННО, если вы можете просто сослаться на ячейку. Поэтому вместо использования формулы вы можете просто использовать = A1 в ячейке A2, и это даст вам тот же результат.

Ты прав!

В этом случае использование формулы КОСВЕННО не имеет смысла.

Но в некоторых случаях ссылка на ячейку или диапазон недоступна. В таком сценарии будет очень полезна косвенная функция.

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

Синтаксис функции INDIRECT

Синтаксис косвенной функции следующий:

INDIRECT (ref_text; [ref_style])

Функция принимает два параметра:

ref_text : это текстовая строка, содержащая ссылку на ячейку или диапазон ячеек.
ref_style : этот параметр необязательный. Он задается значением ИСТИНА / ЛОЖЬ. Значение TRUE указывает, что ref_text задан в нотации «A1», а значение FALSE указывает, что он задан в нотации «R1C1». Если это значение не указано, предполагается, что значение по умолчанию — ИСТИНА.

Обратите внимание, что параметр ref_text также может содержать ссылку на ячейку, которая, в свою очередь, содержит строку со ссылкой на другую ячейку.

Если все это звучит сбивающе с толку, это станет яснее с помощью нескольких примеров.

Понимание КОСВЕННОЙ функции

В следующем наборе данных мы видим 4 различных способа ссылки на значение в ячейке A2, из которых один из них неверен.

В первой строке мы использовали обычный метод обращения к ссылке на ячейку (с использованием записи A1): «= A2». Это вернуло значение в ячейке A2, равное 25 .
Во второй строке мы использовали функцию INDIRECT для ссылки на ячейку A2. Здесь мы указали текстовую строку «A2» в качестве параметра ref_text: = INDIRECT («A2»). Обратите внимание, что мы заключили ссылку на ячейку в двойные кавычки (поскольку это текстовая строка). Это вернуло значение в ячейке A2, равное 25 .
В третьей строке мы использовали функцию INDIRECT для ссылки на ячейку A2. Здесь мы снова указали A2 как ref_text, но не заключили его в двойные кавычки: = INDIRECT (A2). Это означает, что мы не передаем строку «A2». Таким образом, это вернуло ошибку #REF .
В четвертой строке мы использовали функцию INDIRECT для ссылки на ячейку A5. Здесь мы указали A5 в качестве параметров ref_text без двойных кавычек: = INDIRECT (A5). Это означает, что мы не передаем строку «A5», а передаем ссылку на ячейку A5. Ячейка A5 в наборе данных содержит строку «A2», которую мы используем в качестве ссылки для нашей функции INDIRECT. Таким образом, это вернуло значение в ячейке A2 (не A5), равное 25 .

НЕПРЯМАЯ функция. Примеры

Мы надеемся, что теперь вы понимаете, как работает функция КОСВЕННО. Однако ваше понимание этого укрепится только после того, как вы увидите несколько примеров того, когда использовать эту функцию.

Итак, вот несколько вариантов использования, в которых функция INDIRECT может оказаться весьма полезной.

Использование функции INDIRECT для блокировки ссылки на ячейку

Функция INDIRECT предоставляет отличный способ «заблокировать» определенные ячейки или диапазон ячеек в формуле. Чтобы продемонстрировать это, давайте посмотрим на следующий пример:

Предположим, что приведенный выше список имен является списком победителей чемпионата, и этот список постоянно обновляется, и имя ведущего чемпиона всегда указывается наверху. Это означает, что имя, указанное в ячейке A2, является текущим ведущим чемпионом.

Создадим ссылку на эту ячейку как с косвенной функцией, так и без нее. На изображении ниже мы создали ссылку на ячейку A2, используя обычную ссылку на ячейку «= A2» в ячейке B9.

Однако в ячейке B10 мы использовали функцию INDIRECT для ссылки на ячейку A2: «= INDIRECT (« A2 »)»

Поскольку в настоящий момент Джон Смит занимает ячейку A2, оба метода отображают одного и того же ведущего чемпиона, «Джона Смита».

Теперь предположим, что новый победитель по имени «Сэмюэл Джонсон» найден, и в ячейке A2 создается новая строка для его имени. Обратите внимание, что в результате имя Джона Смита переместится в следующий ряд (A3).

Давайте посмотрим, что происходит с ценностями ведущих чемпионов.

Используя традиционный метод привязки ячеек, мы все равно получаем победителя в лице Джона Смита. Это связано с тем, что ссылка на ячейку A2 теперь изменилась на A3 при добавлении новой строки.

Однако при использовании функции INDIRECT значение чемпиона обновляется до Samuel Johnson, поскольку ссылочная строка в функции не изменяется даже после добавления новой строки.

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

Использование функции INDIRECT для ссылки на ячейку на другом листе

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

Возьмем другой пример.

Предположим, у вас есть три листа с названиями «Красный», «Оранжевый» и «Синий» (как показано ниже):

На четвертом листе мы хотим отобразить значение в ячейке B2 из каждого из первых трех листов:

Обычно вам приходилось вводить имя каждого листа вместе со ссылкой на ячейку B2, как показано ниже:

= SheetName! B2

Представьте, что вы делаете это, когда у вас есть 30 листов!

Более простой способ был бы следующим:

В ячейке B2 нового листа введите формулу: = INDIRECT(A2 & «! B2»)
Нажмите клавишу возврата
Дважды щелкните маркер заполнения ячейки B2.
Формула копируется во все ячейки столбца B

Теперь у вас есть значение ячейки B2 из каждого листа, отображаемого в столбце B!

Это значительно упрощает работу по сравнению с обычным методом.

Объяснение формулы

Когда мы щелкали маркер заполнения, ссылка на ячейку перед знаком ‘&’ в каждой ячейке обновлялась до следующего имени листа.

So,

в ячейке B2 функция была = INDIRECT (A2 &! «! B2»), что относится к ячейке Red! B2
в ячейке B3 функция была = INDIRECT (A3 &! «! B2»), что относится к ячейке Оранжевый! B2
в ячейке B4 функция была = INDIRECT (A4 &! «! B2»), что относится к ячейке Blue! B2

Таким образом, всего двойным щелчком мы обновили все ячейки строки B необходимыми данными.

Примечание . Если вам нужно извлечь данные из разных ячеек на разных листах, вы можете создать отдельный столбец, содержащий ссылки на ячейки для каждого листа, а затем использовать формулу КОСВЕННО, как показано ниже:

Использование функции INDIRECT для динамического обращения к именованному диапазону

Это отличное применение функции КОСВЕННО (INDIRECT).

Допустим, у вас есть три столбца для трех цветов, как показано ниже.

Вы хотите отобразить общую сумму для цвета в зависимости от имени столбца, выбранного из раскрывающегося списка в C6.

Это означает, что сумма, отображаемая в ячейке C11 в приведенном выше наборе данных, будет варьироваться в зависимости от выбранного цвета.

Учтите, что заранее узнать, какой цвет будет выбран, невозможно. Итак, чтобы каждый раз получать правильную сумму, вам необходимо сделать следующее:

Создайте именованный диапазон для каждого цвета. Начнем с красного.
- Чтобы создать именованный диапазон для значений красного цвета, выберите ячейки в разделе «Красный».
- Выберите Данные-> Именованный диапазон.
- В правой части окна откроется боковая панель «Именованные диапазоны».
- Введите «Красный» в поле ввода над диапазоном ячеек.
- Нажмите Готово
- Это создаст именованный диапазон для группы ячеек под «красным».
Таким же образом создайте именованные диапазоны для каждого цвета.
Выберите ячейку C11
Введите формулу: = SUM (INDIRECT (C6))
Нажмите клавишу возврата.
Теперь попробуйте выбрать цвет из раскрывающегося списка. Вы увидите, что сумма обновляется соответствующим образом в ячейке C11.

Объяснение формулы

Разберем использованную нами формулу:

= SUM (INDIRECT(C6))

Здесь функция INDIRECT принимает в качестве параметра строку, которая находится в ячейке C6. Таким образом, если выбран текст «Красный», функция КОСВЕННО вернет ссылку на именованuный диапазон «Красный».
Функция СУММ принимает числовые значения, которые находятся в выбранном именованном диапазоне, и возвращает сумму.

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

Существует ряд других способов применения функции КОСВЕННО. В этом руководстве мы объяснили синтаксис функции INDIRECT, а также несколько примеров формул.

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

Мы рекомендуем вам найти более творческие способы применения функции КОСВЕННО в Google Таблицах и облегчить вашу работу.

Надеюсь, вы нашли этот урок полезным.

Вас могут заинтересовать следующие статьи: