Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Непряма функція Excel в Excel для створення посилання на клітинку з тексту.
Огляд непрямих функцій
Функція INDIRECT Створює посилання на клітинку з текстового рядка.
(Зверніть увагу, як виглядають вхідні дані формул)
Функція INDIRECT Синтаксис та входи:
| 1 | = НЕПРЯМИЙ (посилання_текст, C1) |
ref_text - Рядок, що представляє посилання на клітинку або посилання на діапазон. Рядок може мати формат R1C1 або A1, або бути іменованим діапазоном.
a1 - НЕОБОВ'ЯЗКОВО: Вказує, чи посилання у форматі R1C1 або A1. FALSE для R1C1 або TRUE / Опущено для A1.
Що таке непряма функція?
Функція INDIRECT дозволяє вводити текстовий рядок і комп’ютер інтерпретувати цей рядок як фактичну посилання. Це може бути використано для посилання на діапазон на тому ж аркуші, на іншому аркуші або навіть на іншій книзі.
УВАГА: Функція INDIRECT - одна з летючих функцій. У більшості випадків, коли ви працюєте над своєю електронною таблицею, комп’ютер перераховує формулу лише тоді, коли вхідні дані змінили свої значення. Однак мінлива функція перераховується кожен час змінити будь -яку клітинку. Слід бути обережним, щоб уникнути великого часу перерахунку через надмірне використання летких функцій або наявність багатьох клітин залежно від результату летючої функції.
Створіть посилання на клітинку
Скажіть, що ви хочете отримати значення з A2, але ви хочете переконатися, що ваша формула залишається на A2 незалежно від того, вставляються/видаляються нові рядки. Ви можете написати формулу
| 1 | = Непрямий ("A2") |
Зауважте, що аргументом всередині нашої функції є текстовий рядок “A2”, а не посилання на клітинку. Крім того, оскільки це текстовий рядок, немає необхідності вказувати абсолютну посилання, наприклад $ A $ 2. Текст ніколи не зміниться, і тому ця формула завжди буде вказувати на A2, незалежно від того, куди він буде переміщений.
Непрямий номер рядка
Ви можете об'єднати текстові рядки та значення з клітинок разом. Замість того, щоб писати «А2», як ми це робили раніше, ми можемо витягнути числове значення з комірки В2 і використати це у нашій формулі. Ми б виписали формулу так
| 1 | = НЕПРЯМО ("A" & B2) |
Символ “&” використовується тут для об’єднання текстового рядка “A” зі значенням із комірки B2. Отже, якби значення В2 наразі було 10, то наша формула буде читати це так
| 123 | = Непрямий ("A" & 10)= Непрямий ("A10")= А10 |
Непряме значення стовпця
Ви також можете об'єднати посилання на стовпець. Цього разу, скажімо, ми знаємо, що хочемо отримати значення з рядка 10, але ми хочемо мати можливість змінити, з якого стовпця витягувати. Ми помістимо потрібну літеру стовпця в клітинку В2. Наша формула може виглядати так
| 1 | = НЕПРЯМО (B2 & "10") |
Якщо значення B2 дорівнює "G", то наша формула оцінює так
| 123 | = Непрямий ("G" & 10)= Непрямий ("G10")= G10 |
Непрямий стиль r1c1
У нашому попередньому прикладі нам доводилося використовувати букву для позначення посилання на стовпець. Це тому, що ми використовували посилання у стилі А1. У стилі А1 стовпці задаються буквою, а рядки - цифрами. Абсолютні посилання вказуються за допомогою "$" перед елементом, який ми хочемо залишити абсолютним.
У r1c1 і рядки, і стовпці запускаються за допомогою числа. Абсолютне посилання на a1 записується як
| 1 | = R1C1 |
Ви можете прочитати це як “Ряд 1, колонка 1”. Відносні посилання подаються за допомогою дужок, але число вказує на позицію відносно клітинки з формулою. Отже, якби ми писали формулу в комірці A10 і нам потрібно було посилатися на A1, ми б написали формулу
| 1 | = R [-9] C |
Ви можете прочитати це як “Осередок на 9 рядків вгору, але в одному стовпці.
Причина, чому це може бути корисним, полягає в тому, що INDIRECT може підтримувати використання позначення r1c1. Розглянемо попередній приклад, коли ми отримували значення з рядка 10, але хотіли мати можливість змінити стовпець. Замість того, щоб давати букву, скажімо, ми поставили номер у клітинку В2. Тоді наша формула може виглядати так
| 1 | = НЕПРЯМО ("R10C" & B2, НЕВІРНО) |
Ми пропустили 2nd аргументи досі. Якщо цей аргумент опущено або має значення True, функція оцінюватиметься за допомогою стилю A1. Оскільки це неправда, це буде оцінено в r1c1. Припустимо, що значення В2 дорівнює 5. Наша формула буде оцінювати це так
| 12 | = НЕПРЯМО ("R10C5", НЕВІРНО)= $ E $ 10 |
Непрямі відмінності з A1 проти r1c1
Пам'ятайте, що ми попередньо показали, що оскільки вміст цієї формули був текстовим рядком, він ніколи не змінювався?
| 1 | = Непрямий ("A2") |
Ця формула завжди буде дивитися на клітинку A2, незалежно від того, куди ви перемістите формулу. У r1c1, оскільки ви можете вказати відносне положення за допомогою дужок, це правило не залишається послідовним. Якщо ви помістите цю формулу в клітинку В2
| 1 | = НЕПРЯМО ("RC [-1]") |
Він буде дивитися на клітинку A2 (оскільки стовпець A - один зліва від стовпця B). Якщо скопіювати цю формулу в клітинку В3, текст всередині залишиться незмінним, але зараз НЕПРЯМЕ буде дивитися на комірку А3.
INDIRECT з назвою аркуша
Ви також можете об'єднати назву аркуша у свої непрямі посилання. Важливе правило, яке слід пам’ятати, полягає в тому, що навколо імен слід розміщувати одинарні лапки, а відокремлювати назву аркуша від посилання на клітинку за допомогою знака оклику.
Скажімо, у нас було таке налаштування, де ми вказуємо назву свого аркуша, рядок і стовпець.
Наша формула об’єднати все це в посилання виглядатиме так:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Тоді наша формула буде оцінена так:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" Sheet2'! B5 ")= 'Аркуш2'! B5 |
Технічно, оскільки слово «аркуш 2» не містить пробілів, ми цього не робимо потреба одинарні лапки. Цілком справедливо написати щось на кшталт
| 1 | = Аркуш2! A2 |
Однак не завадить розмістити лапки, коли вони вам не потрібні. Найкраще їх включити, щоб ваша формула могла обробляти екземпляр там, де він може знадобитися.
НЕПРЯМО до іншої робочої книги
Ми також зазначимо, що INDIRECT може створити посилання на іншу книгу. Обмеження полягає в тому, що INDIRECT не буде отримувати значення з закритої книги, тому це конкретне використання має обмежену практичність. Якщо робоча книга, на яку вказує непряма, буде відкрита, функція видасть "#REF!" помилка.
Синтаксис під час написання назви книги полягає в тому, що вона має бути у квадратних дужках. Давайте скористаємось цим налаштуванням і спробуємо отримати значення з комірки C7.
Наша формула буде такою
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Знову зверніть увагу на розміщення одинарних лапок, дужок та знаку оклику. Тоді наша формула буде оцінена так:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Summary" & "'! C7")= INDIRECT ("'[Sample.xslx] Підсумок'! C7")= '[Зразок.xlsx] Підсумок'! C7 |
INDIRECT для побудови динамічного діапазону
Якщо у вас великий набір даних, важливо спробувати оптимізувати формули, щоб вони не виконували більше роботи, ніж потрібно. Наприклад, замість того, щоб посилатися на весь стовпець А, ми можемо просто вказати точну кількість клітинок у нашому списку. Розглянемо такий макет:
У комірку В2 ми помістили формулу
| 1 | = COUNTA (A: A) |
Функція COUNTA дуже легко обчислюється комп'ютером, оскільки вона просто перевіряє, скільки клітинок у стовпці A має певне значення, на відміну від необхідності виконувати будь -які логічні перевірки або математичні операції.
Тепер давайте побудуємо нашу формулу, яка підсумовуватиме значення у стовпці А, але ми хочемо переконатися, що вона дивиться лише на точний діапазон зі значеннями (A2: A5). Ми запишемо нашу формулу так
| 1 | = SUM (непрямий ("A2: A" & B2)) |
Наш INDIRECT збирається захопити номер 5 з комірки B2 і створить посилання на діапазон A2: A5. Сума може потім використовувати цей діапазон для свого розрахунку. Якщо ми додамо ще одне значення у клітинку A6, тоді номер у B2 оновиться, і наша формула SUM також автоматично оновиться, щоб включити це нове значення.
УВАГА: З введенням таблиць в Office 2007 набагато ефективніше зберігати дані в таблиці та використовувати структурні посилання, а не будувати формулу, яку ми використовували у цьому прикладі через мінливу природу INDIRECT. Однак це можуть бути випадки, коли вам потрібно створити список елементів, і ви не можете використовувати таблицю.
Динамічні діаграми з INDIRECT
Візьмемо попередній приклад і зробимо ще один крок. Замість того, щоб писати формулу, щоб дати нам суму значень, ми створимо Іменований діапазон. Ми могли б назвати цей діапазон “MyData” і посилатися на нього
| 1 | = НЕПРЯМО ("A2: A" & COUNTA ($ A: $ A)) |
Зауважте, що оскільки ми розміщуємо це в іменованому діапазоні, ми поміняли посилання на B2 і замість цього помістили туди безпосередньо функцію COUNTA.
Тепер, коли у нас є цей іменований діапазон, ми могли б використовувати його в діаграмі. Ми створимо порожню лінійну діаграму, а потім додамо ряд даних. Для значень ряду можна написати щось на зразок
| 1 | = Аркуш1! MyData |
Тепер діаграма буде використовувати це посилання для значень графіків. Оскільки до стовпця А буде додано більше значень, INDIRECT буде посилатися на все більший і більший діапазон, і наша діаграма буде продовжувати оновлюватися з усіма нещодавно доданими значеннями.
Динамічна перевірка даних за допомогою INDIRECT
Збираючи вхідні дані користувачів, іноді виникає потреба зробити вибір одного варіанту вибору залежним від попереднього вибору. Розглянемо цей макет, де наш перший стовпець дозволяє користувачеві вибирати між фруктами, овочами та м’ясом.
У 2nd стовпця, ми не хочемо мати великий список із усіма можливими варіантами, оскільки ми вже трохи звузили речі. Отже, ми створили ще 3 списки, які виглядають так:
Далі ми призначимо кожен із ці списки до іменованого діапазону. Тобто всі фрукти будуть в асортименті під назвою «Фрукти», а овочі в «Овочі» тощо.
Повернувшись до нашої таблиці, ми готові налаштувати перевірку даних у 2nd стовпчик. Ми створимо перевірку типу списку із введенням:
| 1 | = НЕПРЯМО (A2) |
INDIRECT збирається прочитати вибір, зроблений у стовпці A, і побачить назву категорії. Ми визначили діапазони з цими назвами, тому INDIRECT візьме це ім'я і створить посилання на потрібний діапазон.
додаткові нотатки
Використовуйте функцію INDIRECT для створення посилання на клітинку з тексту.
Спочатку створіть рядок тексту, що представляє посилання на клітинку. Рядок повинен бути або у звичайній літері стовпця у стилі А1, і в рядку (M37), або у стилі R1C1 (R37C13). Ви можете ввести посилання безпосередньо, але зазвичай ви будете посилатися на клітинки, які визначають рядки та стовпці. Нарешті, введіть обраний формат посилання на клітинку. TRUE або Опущено для посилання у стилі A1 або FALSE для стилю R1C1.
Під час роботи з непрямими формулами ви можете скористатися Функція ROW щоб отримати номер рядка посилання або Функція COLUMN щоб отримати номер стовпця (а не букву) посилання.
Повернення до списку всіх функцій Excel
НЕПРЯМО у Google Таблицях
Функція INDIRECT працює точно так само в Google Таблицях, як і в Excel:
