Одна з вбудованих функцій 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.
- Виділіть першу порожню клітинку стовпця, куди вставлятимете формулу, і натисніть значок «Вставити функцію» (Insert Function).
- Відкриється майстер функцій. Перейдіть до категорії «Посилання та масиви», виберіть у списку INDIRECT і натисніть «ОК».
- Відкриється вікно аргументів. У полі «посилання на клітинку» вкажіть адресу клітинки, вміст якої треба показати. Її можна ввести вручну, але зручніше поставити курсор у поле й клацнути потрібну клітинку на аркуші — її адреса одразу з'явиться в полі. Потім візьміть посилання в лапки з обох боків: як ми пам'ятаємо, це особливість аргументу цієї функції.
У полі «a1», оскільки ми працюємо зі звичайним стилем посилань, можна поставити TRUE або залишити поле порожнім — це рівнозначно. Залишимо порожнім.
Натисніть «ОК».
- Тепер вміст першої клітинки першого стовпця таблиці виводиться в клітинці з формулою INDIRECT.
- Якщо застосувати цю функцію в клітинках нижче, формулу доведеться вводити в кожну окремо. Якщо скопіювати її маркером заповнення чи іншим способом, у всіх клітинках стовпця буде одне й те саме значення: посилання передається як текст (у лапках), тож не може бути відносним.
Приклад 2: INDIRECT у складній формулі
Тепер приклад значно частішого застосування INDIRECT — у складі складної формули.
Є помісячна таблиця доходів підприємства. Потрібно порахувати суму доходу за певний період, наприклад березень — травень або червень — листопад. Можна, звісно, скористатися простим підсумовуванням, але тоді для кожного нового періоду доведеться змінювати формулу. А з INDIRECT діапазон підсумовування змінюється, якщо просто вказати потрібні місяці в окремих клітинках. Спробуймо порахувати суму за березень — травень формулою з комбінацією функцій SUM та INDIRECT.
- Спершу в окремих клітинках аркуша впишіть назви місяців початку й кінця періоду — «Березень» і «Травень».
- Тепер дайте кожній клітинці стовпця «Дохід» ім'я, що збігається з назвою відповідного місяця: першу клітинку стовпця з сумою доходу назвіть «Січень», другу — «Лютий» тощо.
Щоб дати ім'я першій клітинці стовпця, виділіть її, клацніть правою кнопкою миші й виберіть «Визначити ім'я…» (Define Name).
- Відкриється вікно створення імені. У полі «Ім'я» (Name) введіть «Січень». Більше нічого змінювати не потрібно, хоча можна перевірити, що в полі «Діапазон» (Refers to) указано адресу клітинки з доходом за січень. Натисніть «ОК».
- Тепер, якщо виділити цю клітинку, у полі імені буде видно не її адресу, а дане вами ім'я. Так само назвіть решту клітинок стовпця «Дохід»: «Лютий», «Березень», «Квітень» і так далі до грудня включно.
- Виділіть клітинку, у якій виводитиметься сума за період, і натисніть значок «Вставити функцію» ліворуч від рядка формул.
- У майстрі функцій перейдіть до категорії «Математичні» (Math & Trig), виберіть SUM і натисніть «ОК».
- Відкриється вікно аргументів функції SUM, яка просто додає вказані значення. Її синтаксис дуже простий:
=SUM(число1; число2; …)Аргументів може бути до 255, і всі вони однорідні: число, адреса клітинки з числом або вкладена формула, яка обчислює число чи вказує на клітинку з ним. Саме як вкладену функцію ми й використаємо INDIRECT.
Поставте курсор у поле «Число1» (Number1). Потім натисніть значок у вигляді перевернутого трикутника праворуч від поля імені — відкриється список нещодавно використаних функцій. Якщо INDIRECT є в ньому, клацніть її, щоб перейти до вікна аргументів. Якщо немає — виберіть «Інші функції…» (More Functions) унизу списку.
- Відкриється знайомий майстер функцій. Перейдіть до розділу «Посилання та масиви», виберіть INDIRECT і натисніть «ОК».
- Відкриється вікно аргументів INDIRECT. У полі «посилання на клітинку» вкажіть адресу клітинки з назвою початкового місяця періоду. Зверніть увагу: цього разу лапки не потрібні, бо адресою слугують не координати клітинки, а її вміст, який уже є текстом (слово «Березень»). Поле «a1» залиште порожнім, бо ми використовуємо стандартний стиль посилань.
Не поспішайте натискати «ОК»: це вкладена функція, і працювати з нею треба інакше. Клацніть назву SUM у рядку формул.
- Ви повернетеся до вікна аргументів SUM. У полі «Число1» уже є функція INDIRECT зі своїм вмістом. Поставте курсор у це поле одразу після останнього символу й введіть двокрапку (:) — це знак діапазону клітинок. Потім, не виходячи з поля, знову натисніть трикутник для вибору функцій. Цього разу INDIRECT точно буде в списку нещодавно використаних — клацніть її.
- Знову відкриється вікно аргументів INDIRECT. У поле «посилання на клітинку» введіть адресу клітинки з назвою місяця, яким закінчується період, — знову без лапок. Поле «a1» залиште порожнім і натисніть «ОК».
- Програма обчислить і виведе в клітинку з формулою суму доходу підприємства за вказаний період (березень — травень). Готова формула має приблизно такий вигляд:
=SUM(INDIRECT(F2):INDIRECT(F3)), де F2 і F3 — клітинки з назвами місяців. - Якщо в клітинках з назвами місяців указати інші, наприклад «Червень» і «Листопад», результат зміниться відповідно — буде пораховано суму доходу за новий період.
Як бачимо, хоча функцію INDIRECT не назвеш дуже популярною, вона допомагає розв'язувати в Excel завдання різної складності значно простіше, ніж інші інструменти. Найкорисніша вона у складі складних формул. Утім, усі її можливості зрозуміти непросто — це й пояснює її невелику популярність.