Excel надає різноманітні інструменти для ефективного аналізу даних. Однією з таких потужних можливостей є функція INDIRECT, яка дозволяє створювати посилання на комірки, використовуючи змінні критерії. Це робить ваші електронні таблиці більш адаптованими до різних завдань.
У цьому матеріалі ми розглянемо функцію INDIRECT, вивчимо її призначення та навчимося застосовувати її на практиці.
Що являє собою функція INDIRECT в Excel?
Функція INDIRECT в Excel дозволяє звертатися до клітинок, діапазонів чи іменованих діапазонів не напряму, а через текстовий рядок, що містить посилання. На відміну від звичайних функцій, що вимагають прямих посилань, INDIRECT дає можливість створювати посилання, опираючись на вміст інших клітинок.
Синтаксис функції INDIRECT досить простий:
=INDIRECT(посилання_текст; [A1])
Де:
- посилання_текст: обов’язковий аргумент, який є текстовим рядком з коректним посиланням на комірку. Це може бути посилання у форматі A1, іменований діапазон (наприклад, “Ціни”) або текстовий рядок у лапках (“Аркуш1!B2”).
- [A1]: необов’язковий аргумент, що вказує, який стиль посилань використовувати: A1 (TRUE) або R1C1 (FALSE). За замовчуванням використовується стиль A1 (TRUE).
Як ефективно використовувати функцію INDIRECT в Excel
Розгляньмо кілька прикладів застосування функції INDIRECT в Excel:
1. Отримання значень за допомогою посилання на комірку
Ви можете використовувати INDIRECT у комбінації з функцією MATCH для пошуку значень у діапазоні, опираючись на посилання з іншої клітинки. Наприклад:
Припустимо, у вас є перелік товарів у стовпці A (A2:A8) та їхні ціни у стовпці B (B2:B8). У клітинці D2 вказана назва товару, ціну якого ви хочете знайти, скажімо, “Яблука”.
Щоб знайти ціну товару “Яблука”, використовуючи INDIRECT разом із MATCH, введіть таку формулу:
=INDIRECT("B" & MATCH(D2; A2:A8; 0))
У цій формулі:
- MATCH(D2; A2:A8; 0) визначає номер рядка, де розташовано значення “Яблука” у списку.
- “B” & MATCH(D2; A2:A8; 0) з’єднує “B” з номером рядка, створюючи посилання на кшталт “B5” (якщо “Яблука” знаходиться в клітинці A5).
- INDIRECT(“B5”) повертає значення з клітинки B5, яке є ціною на яблука.
2. Обчислення суми діапазону комірок
Припустимо, у вас є дані в клітинках від A1 до A7. Щоб обчислити їх суму, ви можете використовувати функцію INDIRECT. Ось як це зробити:
=SUM(INDIRECT("A1:A7"))
Ця формула використовує INDIRECT для створення посилання на діапазон (A1:A7), а потім функція SUM підсумовує значення в цьому діапазоні.
3. Посилання на іменовані діапазони
Якщо ви створили іменований діапазон, ви можете звертатися до нього за допомогою функції INDIRECT для виконання обчислень.
Уявіть, що у вас є набір даних з показниками продажів за різні місяці. За допомогою INDIRECT ми можемо обчислити середні продажі за кожний тиждень місяця.
=AVERAGE(INDIRECT(F2))
Ця формула звертається до іменованого діапазону, вказаного в клітинці F2 (наприклад, “Тиждень1”, який відповідає діапазону B2:B6) та розраховує середнє значення в цьому діапазоні.
4. Посилання на комірки з іншого аркуша
Припустимо, у вас є книга з двома аркушами: “ДаніМіст” та “Звіт”. Аркуш “ДаніМіст” містить інформацію про різні міста. На аркуші “Звіт” ви хочете створити звіт, що посилається на дані з аркуша “ДаніМіст”, наприклад, на населення Кіншаси.
Введіть наступну формулу в будь-яку комірку на аркуші “Звіт”:
=INDIRECT("ДаніМіст!C6")
Ця формула відобразить населення Кіншаси з комірки C6 на аркуші “ДаніМіст”. Ви також можете змінювати посилання всередині INDIRECT для отримання даних з аркуша “ДаніМіст” для інших міст або типів інформації.
Гнучке посилання на дані з функцією INDIRECT в Excel
Функція INDIRECT в Excel – це багатофункціональний інструмент, що дозволяє створювати динамічні посилання в електронних таблицях. Зрозумівши її синтаксис і способи застосування, ви зможете максимально ефективно використовувати її можливості для покращення своїх навичок обробки та аналізу даних. Незалежно від того, чи працюєте ви зі складними звітами, чи консолідуєте дані, функція INDIRECT стане незамінним помічником у вашому арсеналі Excel.