Функція Excel INDIRECT - Огляд
Функцію INDIRECT в Excel можна використовувати, якщо у вас є посилання на клітинку або діапазон як текстовий рядок, і ви хочете отримати значення з цих посилань.
Коротше кажучи - ви можете використовувати непряму формулу для повернути посилання, зазначене текстовим рядком.
У цьому підручнику Excel я покажу вам, як використовувати непряму функцію в Excel, використовуючи деякі практичні приклади.
Але перш ніж перейти до прикладів, давайте спочатку подивимося на його синтаксис.
Непряма функція Синтаксис
= НЕПРЯМИЙ (ref_text, [a1])
Аргументи введення
- ref_text - Текстовий рядок, що містить посилання на клітинку або іменований діапазон. Це повинно бути дійсним посиланням на клітинку, інакше функція поверне #REF! помилка
- [a1] - Логічне значення, яке вказує для якого типу посилання використовувати реф. текст. Це може бути або ІСТИНА (вказує посилання на стиль A1), або FALSE (вказує посилання у стилі R1C1). Якщо пропущено, воно за замовчуванням - ІСТИНА.
додаткові нотатки
- INDIRECT - мінлива функція. Це означає, що він перераховує кожного разу, коли відкрита книга Excel або коли на робочому аркуші запускається обчислення. Це збільшує час обробки та уповільнює вашу книгу. Хоча ви можете використовувати непряму формулу з невеликими наборами даних, які мало чи зовсім не впливають на швидкість, ви можете побачити, як вона уповільнює вашу книгу під час використання з великими наборами даних
- Довідковий текст (ref_text) може бути таким:
- Посилання на клітинку, яка, у свою чергу, містить посилання у форматі посилання у стилі A1 або R1C1.
- Посилання на клітинку в подвійних лапках.
- Іменований діапазон, який повертає посилання
Приклади використання непрямої функції в Excel
Тепер давайте зануримось і подивимося на деякі приклади того, як використовувати функцію INDIRECT в Excel.
Приклад 1: Використовуйте посилання на комірку, щоб отримати значення
Він приймає посилання на клітинку як текстовий рядок як вхід і повертає значення в цьому посиланні (як показано в прикладі нижче):
Формула у клітинці С1 така:
= Непрямий ("A1")
Наведена вище формула приймає посилання на клітинку A1 як вхідний аргумент (у подвійних лапках як текстовий рядок) і повертає значення в цій клітинці, що становить 123.
Тепер, якщо ви думаєте, чому б мені просто не використовувати = A1 замість використання функції INDIRECT, у вас є правильне запитання.
Ось чому…
Коли ви використовуєте = A1 або = $ A $ 1, це дає вам той самий результат. Але коли ви вставляєте рядок над першим рядком, ви помітите, що посилання на клітинки автоматично змінюватимуться, враховуючи новий рядок.
Ви також можете використовувати функцію INDIRECT, коли хочете заблокувати посилання на клітинки таким чином, щоб вона не змінювалася під час вставлення рядків/стовпців у аркуш.
Приклад 2: Використовуйте посилання на комірку в клітинку, щоб отримати значення
Ви також можете використовувати цю функцію для отримання значення з комірки, посилання на яку зберігається у самій клітині.
У наведеному вище прикладі осередок A1 має значення 123.
Комірка C1 має посилання на клітинку A1 (у вигляді текстового рядка).
Тепер, коли ви використовуєте функцію INDIRECT і використовуєте C1 як аргумент (який, у свою чергу, містить адресу комірки як текстовий рядок у ній), це перетворює значення у комірці A1 у дійсне посилання на клітинку.
Це, в свою чергу, означає, що функція посилатиметься на комірку A1 і повертатиме значення в ній.
Зауважте, що тут не потрібно використовувати подвійні лапки, оскільки у C1 посилання на клітинку зберігається лише у форматі текстового рядка.
Крім того, у разі, якщо текстовий рядок у комірці C1 не є дійсним посиланням на клітинку, функція Indirect поверне #REF! помилка.
Приклад 3: Створення посилання за допомогою значення в клітинці
Ви також можете створити посилання на клітинку, використовуючи комбінацію алфавіту стовпця та номера рядка.
Наприклад, якщо клітинка С1 містить число 2, і ви використовуєте формулу = НЕПРЯМО ("A" & C1) тоді це буде посилатися на комірку А2.
Практичне застосування цього може бути, коли ви хочете створити динамічне посилання на клітинки на основі значення в іншій комірці.
Якщо текстовий рядок, який ви використовуєте у формулі, містить посилання, яке Excel не розуміє, він поверне помилку ref (#REF!).
Приклад 4: Обчислення суми діапазону клітинок
Ви також можете посилатися на діапазон клітинок так само, як і на одну клітинку, використовуючи функцію INDIRECT у Excel.
Наприклад, = НЕПРЯМО ("A1: A5") відноситься до діапазону A1: A5.
Потім ви можете скористатися функцією SUM для пошуку підсумків або функцією LARGE/SMALL/MIN/MAX для виконання інших обчислень.
Так само, як і функція SUM, ви також можете використовувати такі функції, як LARGE, MAX/MIN, COUNT тощо.
Приклад 5: Створення посилання на аркуш за допомогою функції INDIRECT
У наведених вище прикладах було розглянуто, як посилатись на клітинку на одному робочому аркуші. Ви також можете використовувати формулу INDIRECT для посилання на клітинку на іншому аркуші чи іншій книзі.
Ось що вам потрібно знати про посилання на інші аркуші:
- Припустимо, у вас є робочий аркуш з назвою Sheet1, і всередині аркуша в комірці A1 у вас є значення 123. Якщо ви перейдете на інший аркуш (скажімо, Sheet2) і звернетесь до комірки A1 у Sheet1, формула буде такою: = Аркуш1! A1
Але…
- Якщо у вас є робочий аркуш, який містить два або більше двох слів (із символом пробілу між ними), і ви посилаєтесь на клітинку A1 на цьому аркуші з іншого аркуша, формула буде такою: = 'Набір даних'! A1
У разі кількох слів Excel автоматично вставляє одинарні лапки на початку та в кінці назви аркуша.
Тепер давайте подивимося, як створити функцію INDIRECT для посилання на клітинку на іншому аркуші.
Припустимо, у вас є аркуш з набором даних і клітинка A1 у ньому має значення 123.
Тепер, щоб звернутися до цієї комірки з іншого аркуша, використовуйте таку формулу:
= INDIRECT ("'Набір даних'! A1")
Як бачите, посилання на комірку також має містити назву аркуша.
Якщо у вас є назва робочого аркуша в комірці (скажімо, A1), то ви можете скористатися такою формулою:
= INDIRECT ("'" & A1 & "'! A1")
Якщо у вас є ім’я робочого аркуша у комірці А1 та адреса комірки у комірці А2, то формула буде такою:
= INDIRECT ("'" & A1 & "'!" & A2)
Аналогічно, ви також можете змінити формулу для посилання на клітинку в іншій книзі.
Це може бути корисним, коли ви намагаєтесь створити зведений аркуш, який витягуватиме дані з кількох різних аркушів.
Пам’ятайте також, що при використанні цієї формули для посилання на іншу книгу ця книга має бути відкритою.
Приклад 6: Звернення до іменованого діапазону за допомогою формули INDIRECT
Якщо ви створили іменований діапазон у Excel, ви можете звернутися до цього іменованого діапазону за допомогою функції INDIRECT.
Наприклад, припустимо, що у вас є оцінки за 5 учнів з трьох предметів, як показано нижче:
У цьому прикладі назвемо клітинки:
- В2: В6: Математика
- C2: C6: Фізика
- D2: D6: Хімія
Щоб назвати діапазон клітинок, просто виділіть клітинки та перейдіть до вікна імені, введіть ім’я та натисніть клавішу Enter.
Тепер ви можете звернутися до цих іменованих діапазонів за формулою:
= INDIRECT ("Іменований діапазон")
Наприклад, якщо ви хочете дізнатися середнє значення балів у математиці, скористайтеся формулою:
= СЕРЕДНЯ (НЕПРЯМА ("Математика"))
Якщо у клітинці є назва імені діапазону (F2 у прикладі нижче має ім’я Math), ви можете використовувати це безпосередньо у формулі.
У наведеному нижче прикладі показано, як обчислити середнє значення за допомогою названих діапазонів.
Приклад 7: Створення залежного випадаючого списку за допомогою функції Excel INDIRECT
Це одне чудове використання цієї функції. Ви можете легко створити залежний випадаючий список, використовуючи його (також званий умовний випадаючий список).
Наприклад, припустимо, що у вас є список країн поспіль і назва міст для кожної країни, як показано нижче:
Тепер для створення залежного випадаючого списку вам потрібно створити два іменовані діапазони, A2: A5 з назвою US та B2: B5 з назвою India.
Тепер виберіть клітинку D2 та створіть розкривний список для Індії та США. Це буде перший розкривний список, де користувач отримує можливість вибрати країну.
Тепер для створення залежного випадаючого списку:
- Виберіть клітинку E2 (клітинку, в яку потрібно отримати залежний випадаючий список).
- Перейдіть на вкладку Дані
- Натисніть на Перевірка даних.
- Виберіть Список як критерії перевірки та скористайтеся такою формулою у полі джерела: = ІНДІРЕКТИВНО ($ D $ 2)
- Натисніть OK.
Тепер, коли ви входите до США у комірці D2, у спадному меню у комірці E2 відображатимуться штати у США.
І коли ви входите до Індії у клітині D2, у спадному меню у комірці E2 відображатимуться штати в Індії.
Отже, це деякі приклади використання функції INDIRECT у Excel. Ці приклади працюватимуть у всіх версіях Excel (Office 365, Excel2021-2022/2016/2013/2013)
Сподіваюся, цей підручник був вам корисним.
- Функція Excel VLOOKUP.
- Функція Excel HLOOKUP.
- Функція Excel INDEX.
- Функція Excel MATCH.
- Функція Excel OFFSET.
