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