Объяснение функции ИНДИРЕКТ в Excel

При работе с Excel мы часто используем ссылки на ячейки в формулах. Именно это делает Excel таким полезным в первую очередь: Вместо того чтобы выполнять вычисления вручную — как в школьной математике — вводя значения непосредственно в формулы, в Excel мы просто указываем ячейки, из которых нужно взять значения, по всем столбцам и строкам. Таким образом, программа электронных таблиц Microsoft является динамичной.

Функция ИНДИРЕКТ в Excel позволяет сделать еще один шаг вперед. Вместо того чтобы записывать ссылку на ячейку в формулу, можно просто получить ее из другой ячейки или скомпилировать на основе нескольких параметров. Звучит сложно, но это может быть очень полезно!

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

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

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

INDIRECT состоит из обязательного и необязательного параметра.

• Ссылка: Здесь вы вводите ссылку на ячейку или ссылки на ячейки. Вы также можете записать часть ссылки на ячейку непосредственно в формулу (используя кавычки). Два компонента ссылки объединяются с помощью символа амперсанда (&).
• A1: Этот необязательный параметр задает формат, в котором нужно создать ссылку на ячейку. Если параметр не указан или в формуле введено значение TRUE, применяется формат A1. В случае FALSE формула использует формат R1C1.

Excel обычно используется в формате A1. Здесь строки нумеруются (A1, A2, A3, …), а столбцы отображаются бегущими буквами (A1, B1, C1, …). Но вы также можете настроить использование формата R1C1 в настройках программы; в этом случае нумеруются и строки, и столбцы. Соответственно, R5C10 будет обозначать ячейку, где пятая строка встречается с десятым столбцом.

Например, если ячейка A1 содержит B1, а ячейка B1 содержит значение 5, то следующая формула выдаст значение 5:

Excel распознает текстовый ввод в ячейке A1 как сигнал для ссылки на ячейку и интерпретирует его соответствующим образом.

Функция становится более сложной, если ссылка на ячейку разделена на части.

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

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

ИНДИРЕКТ также предлагает интересное применение, когда формула используется для ссылки на значения в других рабочих листах. Это может быть полезно, например, если похожие таблицы и вычисления содержатся в нескольких листах, и вы хотите отобразить выбранные значения других листов на обзорном листе. Здесь имя рабочего листа получается из ячейки.

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

Теперь вы можете либо изменить значение в ячейке A1 — например, с помощью выпадающего меню — по мере необходимости, либо расположить значения разных листов одно под другим. Здесь вы можете просто перетащить формулу вниз (используя курсор), и Excel автоматически подгонит ссылки к новому положению функции.

Функция ИНДИРЕКТ Excel часто используется в сочетании с другими. Например, сочетание INDIRECT с функцией SUM полезно, если вы хотите добавить значения из другого рабочего листа. Вместо того чтобы генерировать итог на соответствующем листе, а затем воспроизводить его в обзоре, как в предыдущем примере, значения можно добавить непосредственно в обзоре.

Эта формула позволяет добавить ячейки с B1 по B5 в рабочем листе, который вы указали в ячейке A3.

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

Многие другие математические или статистические функции также могут быть расширены в Excel с помощью этой техники (например, MEAN).