Функция Excel INDIRECT - Общ преглед
Функцията INDIRECT в Excel може да се използва, когато имате препратка към клетка или диапазон като текстов низ и искате да получите стойностите от тези препратки.
Накратко - можете да използвате непряката формула, за да връща препратката, посочена от текстовия низ.
В този урок по Excel ще ви покажа как да използвате непряката функция в Excel, използвайки някои практически примери.
Но преди да вляза в примерите, нека първо да разгледаме неговия синтаксис.
Непряка функция Синтаксис
= INDIRECT (ref_text, [a1])
Аргументи за въвеждане
- ref_text - Текстов низ, който съдържа препратка към клетка или именуван диапазон. Това трябва да е валидна препратка към клетка, в противен случай функцията ще върне #REF! грешка
- [a1] - Логическа стойност, която определя за какъв тип препратка да се използва препратен текст. Това може да бъде или ИСТИНСКО (указващо препратка към стил А1) или ФАЛШНО (указващо препратка в стил R1C1). Ако е пропуснато, по подразбиране е ИСТИНА.
допълнителни бележки
- INDIRECT е нестабилна функция. Това означава, че той преизчислява винаги, когато работната книга на Excel е отворена или когато се задейства изчисление в работния лист. Това увеличава времето за обработка и забавя работната ви книга. Въпреки че можете да използвате непряката формула с малки набори от данни с малко или никакво влияние върху скоростта, може да видите, че тя прави вашата работна книга по -бавна, когато я използвате с големи набори от данни
- Референтният текст (ref_text) може да бъде:
- Препратка към клетка, която от своя страна съдържа препратка в референтен формат в стил А1 или R1C1.
- Препратка към клетка в двойни кавички.
- Именован диапазон, който връща препратка
Примери за това как да използвате непряка функция в Excel
Сега нека се потопим и да разгледаме някои примери за това как да използваме функцията INDIRECT в Excel.
Пример 1: Използвайте препратка към клетка, за да извлечете стойността
Той приема препратката към клетката като текстов низ като вход и връща стойността в тази препратка (както е показано в примера по -долу):
Формулата в клетка C1 е:
= INDIRECT ("A1")
Горната формула приема препратката към клетката A1 като входен аргумент (в двойни кавички като текстов низ) и връща стойността в тази клетка, която е 123.
Сега, ако мислите, защо просто не използвам = A1 вместо да използвам функцията INDIRECT, имате валиден въпрос.
Ето защо…
Когато използвате = А1 или = $ A $ 1, тя ви дава същия резултат. Но когато вмъкнете ред над първия ред, ще забележите, че препратките към клетките автоматично ще се променят, за да отчетат новия ред.
Можете също да използвате функцията INDIRECT, когато искате да заключите препратките към клетки по такъв начин, че да не се променя, когато вмъкнете редове/колони в работния лист.
Пример 2: Използвайте препратка към клетка в клетка, за да извлечете стойността
Можете също да използвате тази функция, за да извлечете стойността от клетка, чиято препратка се съхранява в самата клетка.
В горния пример клетка A1 има стойност 123.
Клетката C1 има препратка към клетката A1 (като текстов низ).
Сега, когато използвате функцията INDIRECT и използвате C1 като аргумент (който от своя страна има адрес на клетка като текстов низ), тя ще преобразува стойността в клетка A1 в валидна препратка към клетка.
Това от своя страна означава, че функцията ще се позовава на клетка A1 и ще върне стойността в нея.
Обърнете внимание, че не е необходимо да използвате двойни кавички тук, тъй като C1 има препратка към клетка, съхранявана само във формат текстов низ.
Също така, в случай че текстовият низ в клетка C1 не е валидна препратка към клетка, функцията Indirect ще върне #REF! грешка.
Пример 3: Създаване на препратка с използване на стойност в клетка
Можете също да създадете препратка към клетка, като използвате комбинация от азбуката на колоната и номера на ред.
Например, ако клетка C1 съдържа числото 2 и използвате формулата = INDIRECT (“A” & C1) тогава ще се отнася за клетка А2.
Практическо приложение на това може да бъде, когато искате да създадете динамична препратка към клетки въз основа на стойността в друга клетка.
В случай, че текстовият низ, който използвате във формулата, дава препратка, която Excel не разбира, тя ще върне грешката ref (#REF!).
Пример 4: Изчислете SUM на диапазон от клетки
Можете също да се обърнете към диапазон от клетки по същия начин, по който се отнасяте към една клетка, като използвате функцията INDIRECT в Excel.
Например, = INDIRECT (“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 функция, която да се позовава на клетка в друг работен лист.
Да предположим, че имате лист с име Dataset и клетка A1 в него има стойността 123.
Сега, за да се обърнете към тази клетка от друг работен лист, използвайте следната формула:
= INDIRECT ("'Набор от данни'! A1")
Както можете да видите, препратката към клетката трябва да съдържа и името на работния лист.
Ако имате име на работния лист в клетка (да речем A1), тогава можете да използвате следната формула:
= INDIRECT ("'" & A1 & "'! A1")
Ако имате името на работния лист в клетка A1 и адреса на клетката в клетка A2, тогава формулата ще бъде:
= INDIRECT ("'" & A1 & "'!" & A2)
По подобен начин можете също да промените формулата, така че да се позовава на клетка в друга работна книга.
Това може да бъде полезно, когато се опитвате да създадете обобщен лист, който извлича данните от множество различни листове.
Също така не забравяйте, че когато използвате тази формула за препратка към друга работна книга, тази работна книга трябва да е отворена.
Пример 6: Позоваване на именован диапазон с помощта на формулата INDIRECT
Ако сте създали именован диапазон в Excel, можете да се обърнете към него с помощта на функцията INDIRECT.
Да предположим например, че имате оценки за 5 ученици по три предмета, както е показано по -долу:
В този пример нека назовем клетките:
- B2: B6: Математика
- C2: C6: Физика
- D2: D6: Химия
За да дадете име на диапазон от клетки, просто изберете клетките и отидете в полето за име, въведете името и натиснете enter.
Сега можете да се обърнете към тези именовани диапазони, като използвате формулата:
= INDIRECT ("Именован диапазон")
Например, ако искате да знаете средната стойност на оценките в математиката, използвайте формулата:
= СРЕДНО (ИНДИРЕКТНО ("Математика"))
Ако имате име на име на диапазон в клетка (F2 в примера по -долу има името Math), можете да го използвате директно във формулата.
Примерът по -долу показва как да се изчисли средната стойност с помощта на посочените диапазони.
Пример 7: Създаване на зависим падащ списък с помощта на Excel INDIRECT функция
Това е едно отлично използване на тази функция. Можете лесно да създадете зависим падащ списък, като го използвате (наричан още условен падащ списък).
Да предположим например, че имате списък с държави подред и името на градовете за всяка държава, както е показано по -долу:
Сега, за да създадете зависим падащ списък, трябва да създадете два именовани диапазона, A2: A5 с името US и B2: B5 с името Индия.
Сега изберете клетка D2 и създайте падащ списък за Индия и САЩ. Това ще бъде първият падащ списък, в който потребителят получава възможност да избере държава.
Сега, за да създадете зависим падащ списък:
- Изберете клетка E2 (клетката, в която искате да получите зависимия падащ списък).
- Щракнете върху раздела Данни
- Кликнете върху Проверка на данни.
- Изберете Списък като критерии за валидиране и използвайте следната формула в полето източник: = INDIRECT ($ 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.
