Функція INDIRECT в Excel: синтаксис і приклади

Як працює функція INDIRECT в Excel: посилання в текстовому вигляді, чим вона відрізняється від звичайного посилання, простий приклад і підсумовування доходу за змінний період разом із SUM та іменованими клітинками.

Одна з вбудованих функцій Excel — INDIRECT. Вона повертає вміст клітинки, посилання на яку передано їй як аргумент у текстовому вигляді.

Здавалося б, нічого особливого: показати вміст однієї клітинки в іншій можна й простіше. Але в цієї функції є нюанси, які роблять її унікальною: іноді вона розв'язує завдання, з якими іншими способами не впоратися або впоратися значно складніше. Розберімося, що таке INDIRECT і як застосовувати її на практиці.

Як працює INDIRECT

Назва INDIRECT означає «непряме посилання» — це і вказує на призначення функції: виводити дані з однієї клітинки в іншу за вказаним посиланням. Причому, на відміну від більшості функцій, що працюють з посиланнями, посилання тут задається в текстовому вигляді, тобто береться в лапки.

Функція належить до категорії «Посилання та масиви» (Lookup & Reference) і має такий синтаксис:

=INDIRECT(посилання_на_клітинку; [a1])

Отже, у формули лише два аргументи.

Аргумент «посилання на клітинку» — це посилання на клітинку, вміст якої потрібно показати. Посилання має бути в текстовому вигляді, тобто «загорнуте» в лапки.

Аргумент «a1» необов'язковий, і здебільшого його взагалі не вказують. Він може мати значення TRUE або FALSE. У першому випадку функція сприймає посилання в стилі A1 — саме цей стиль увімкнено в Excel за замовчуванням. Якщо аргумент не вказано, вважається, що він дорівнює TRUE. У другому випадку посилання сприймаються в стилі R1C1 — цей стиль потрібно окремо вмикати в параметрах Excel.

Простіше кажучи, INDIRECT — своєрідний еквівалент посилання на клітинку після знака «дорівнює». Наприклад, здебільшого вираз

=INDIRECT("A1")

рівнозначний виразу

=A1

Але, на відміну від виразу «=A1», функція INDIRECT прив'язується не до конкретної клітинки, а до її координат на аркуші.

Що це означає, покаже простий приклад. У клітинках B8 і B9 записано відповідно формулу через «=» і функцію INDIRECT. Обидві посилаються на клітинку B4 і виводять її вміст — природно, однаковий.

Додамо в таблицю ще один порожній рядок вище — рядки змістяться. У формулі зі знаком «дорівнює» значення залишиться попереднім: вона посилається на ту саму клітинку, хоча координати клітинки змінилися. А от значення, яке виводить INDIRECT, зміниться: функція посилається не на клітинку, а на координати. Після додавання рядка за адресою B4 опинилася інша клітинка — її вміст формула тепер і показує.

Функція може виводити не лише числа, а й текст, результат обчислення формул та будь-які інші значення з указаної клітинки. На практиці її рідко використовують окремо — значно частіше вона входить до складних формул.

Варто зазначити, що INDIRECT працює й з посиланнями на інші аркуші і навіть на інші книги Excel, але такі книги мають бути відкриті.

Розглянемо конкретні приклади.

Приклад 1: функція окремо

Спершу найпростіший приклад, у якому INDIRECT працює сама — щоб зрозуміти суть.

Є довільна таблиця. Завдання — показати дані першої клітинки першого стовпця в першій клітинці окремого стовпця за допомогою INDIRECT.

  1. Виділіть першу порожню клітинку стовпця, куди вставлятимете формулу, і натисніть значок «Вставити функцію» (Insert Function).
  2. Відкриється майстер функцій. Перейдіть до категорії «Посилання та масиви», виберіть у списку INDIRECT і натисніть «ОК».
  3. Відкриється вікно аргументів. У полі «посилання на клітинку» вкажіть адресу клітинки, вміст якої треба показати. Її можна ввести вручну, але зручніше поставити курсор у поле й клацнути потрібну клітинку на аркуші — її адреса одразу з'явиться в полі. Потім візьміть посилання в лапки з обох боків: як ми пам'ятаємо, це особливість аргументу цієї функції.

    У полі «a1», оскільки ми працюємо зі звичайним стилем посилань, можна поставити TRUE або залишити поле порожнім — це рівнозначно. Залишимо порожнім.

    Натисніть «ОК».

  4. Тепер вміст першої клітинки першого стовпця таблиці виводиться в клітинці з формулою INDIRECT.
  5. Якщо застосувати цю функцію в клітинках нижче, формулу доведеться вводити в кожну окремо. Якщо скопіювати її маркером заповнення чи іншим способом, у всіх клітинках стовпця буде одне й те саме значення: посилання передається як текст (у лапках), тож не може бути відносним.

Приклад 2: INDIRECT у складній формулі

Тепер приклад значно частішого застосування INDIRECT — у складі складної формули.

Є помісячна таблиця доходів підприємства. Потрібно порахувати суму доходу за певний період, наприклад березень — травень або червень — листопад. Можна, звісно, скористатися простим підсумовуванням, але тоді для кожного нового періоду доведеться змінювати формулу. А з INDIRECT діапазон підсумовування змінюється, якщо просто вказати потрібні місяці в окремих клітинках. Спробуймо порахувати суму за березень — травень формулою з комбінацією функцій SUM та INDIRECT.

  1. Спершу в окремих клітинках аркуша впишіть назви місяців початку й кінця періоду — «Березень» і «Травень».
  2. Тепер дайте кожній клітинці стовпця «Дохід» ім'я, що збігається з назвою відповідного місяця: першу клітинку стовпця з сумою доходу назвіть «Січень», другу — «Лютий» тощо.

    Щоб дати ім'я першій клітинці стовпця, виділіть її, клацніть правою кнопкою миші й виберіть «Визначити ім'я…» (Define Name).

  3. Відкриється вікно створення імені. У полі «Ім'я» (Name) введіть «Січень». Більше нічого змінювати не потрібно, хоча можна перевірити, що в полі «Діапазон» (Refers to) указано адресу клітинки з доходом за січень. Натисніть «ОК».
  4. Тепер, якщо виділити цю клітинку, у полі імені буде видно не її адресу, а дане вами ім'я. Так само назвіть решту клітинок стовпця «Дохід»: «Лютий», «Березень», «Квітень» і так далі до грудня включно.
  5. Виділіть клітинку, у якій виводитиметься сума за період, і натисніть значок «Вставити функцію» ліворуч від рядка формул.
  6. У майстрі функцій перейдіть до категорії «Математичні» (Math & Trig), виберіть SUM і натисніть «ОК».
  7. Відкриється вікно аргументів функції SUM, яка просто додає вказані значення. Її синтаксис дуже простий:

    =SUM(число1; число2; …)

    Аргументів може бути до 255, і всі вони однорідні: число, адреса клітинки з числом або вкладена формула, яка обчислює число чи вказує на клітинку з ним. Саме як вкладену функцію ми й використаємо INDIRECT.

    Поставте курсор у поле «Число1» (Number1). Потім натисніть значок у вигляді перевернутого трикутника праворуч від поля імені — відкриється список нещодавно використаних функцій. Якщо INDIRECT є в ньому, клацніть її, щоб перейти до вікна аргументів. Якщо немає — виберіть «Інші функції…» (More Functions) унизу списку.

  8. Відкриється знайомий майстер функцій. Перейдіть до розділу «Посилання та масиви», виберіть INDIRECT і натисніть «ОК».
  9. Відкриється вікно аргументів INDIRECT. У полі «посилання на клітинку» вкажіть адресу клітинки з назвою початкового місяця періоду. Зверніть увагу: цього разу лапки не потрібні, бо адресою слугують не координати клітинки, а її вміст, який уже є текстом (слово «Березень»). Поле «a1» залиште порожнім, бо ми використовуємо стандартний стиль посилань.

    Не поспішайте натискати «ОК»: це вкладена функція, і працювати з нею треба інакше. Клацніть назву SUM у рядку формул.

  10. Ви повернетеся до вікна аргументів SUM. У полі «Число1» уже є функція INDIRECT зі своїм вмістом. Поставте курсор у це поле одразу після останнього символу й введіть двокрапку (:) — це знак діапазону клітинок. Потім, не виходячи з поля, знову натисніть трикутник для вибору функцій. Цього разу INDIRECT точно буде в списку нещодавно використаних — клацніть її.
  11. Знову відкриється вікно аргументів INDIRECT. У поле «посилання на клітинку» введіть адресу клітинки з назвою місяця, яким закінчується період, — знову без лапок. Поле «a1» залиште порожнім і натисніть «ОК».
  12. Програма обчислить і виведе в клітинку з формулою суму доходу підприємства за вказаний період (березень — травень). Готова формула має приблизно такий вигляд: =SUM(INDIRECT(F2):INDIRECT(F3)), де F2 і F3 — клітинки з назвами місяців.
  13. Якщо в клітинках з назвами місяців указати інші, наприклад «Червень» і «Листопад», результат зміниться відповідно — буде пораховано суму доходу за новий період.

Як бачимо, хоча функцію INDIRECT не назвеш дуже популярною, вона допомагає розв'язувати в Excel завдання різної складності значно простіше, ніж інші інструменти. Найкорисніша вона у складі складних формул. Утім, усі її можливості зрозуміти непросто — це й пояснює її невелику популярність.

Схожі інструкції