Преузмите пример радне свеске
Овај водич показује како се користи Екцел ИНДИРЕКТНА функција у Екцелу да бисте креирали референцу ћелије од текста.
ИНДИРЕКТАН Преглед функција
Функција ИНДИРЕЦТ Креира референцу ћелије из текстуалног низа.
(Обратите пажњу на то како се појављују уноси формуле)
ИНДИРЕЦТ функција Синтакса и улази:
| 1 | = ИНДИРЕКТНО (реф_тект, Ц1) |
реф_тект - Низ који представља референцу ћелије или референцу опсега. Низ може бити у Р1Ц1 или А1 формату, или може бити именовани опсег.
а1 - ОПЦИОНАЛНО: Означава да ли је референца у Р1Ц1 или А1 формату. ФАЛСЕ за Р1Ц1 или ТРУЕ / Изостављено за А1.
Шта је ИНДИРЕКТНА функција?
ИНДИРЕЦТ функција вам омогућава да дате текстуални низ и да рачунар разуме тај низ као стварну референцу. Ово се може користити за референцирање распона на истом листу, другом листу или чак другој радној свесци.
ОПРЕЗ: ИНДИРЕЦТ функција је једна од испарљивих функција. Већину времена док радите у табели, рачунар ће поново израчунати формулу само ако су уноси променили своје вредности. Променљива функција се, међутим, прерачунава сваки време када промените било коју ћелију. Треба бити опрезан како не би дошло до великог времена поновног израчунавања због прекомерне употребе испарљиве функције или због тога што многе ћелије зависе од резултата испарљиве функције.
Креирајте референцу ћелије
Реците да желите да преузмете вредност из А2, али желите да се уверите да је ваша формула остаје на А2 без обзира на то да ли се нови редови убацују/уклањају. Могли бисте написати формулу
| 1 | = ИНДИРЕКТНО ("А2") |
Имајте на уму да је аргумент унутар наше функције текстуални низ „А2“, а не референца ћелије. Такође, пошто је ово текстуални низ, нема потребе да наводите апсолутну референцу попут $ А $ 2. Текст се никада неће променити, па ће ова формула увек показивати на А2, без обзира на то где се премешта.
ИНДИРЕКТАН број реда
Можете да повежете текстуалне низове и вредности из ћелија заједно. Уместо да напишемо „А2“ као што смо раније радили, можемо узети нумеричку вредност из ћелије Б2 и користити је у нашој формули. Формулу бисмо записали као
| 1 | = ИНДИРЕКТНО ("А" & Б2) |
Симбол „&“ се овде користи за спајање текстуалног низа „А“ са вредношћу из ћелије Б2. Дакле, ако је вредност Б2 тренутно 10, онда би наша формула гласила овако
| 123 | = ИНДИРЕКТНО ("А" & 10)= ИНДИРЕКТНО ("А10")= А10 |
ИНДИРЕКТНА вредност колоне
Такође можете спојити у референци колоне. Овај пут рецимо да знамо да желимо да преузмемо вредност из реда 10, али желимо да можемо да променимо из које колоне да се повучемо. Слово колоне које желимо ћемо ставити у ћелију Б2. Наша формула би могла да изгледа овако
| 1 | = ИНДИРЕКТНО (Б2 и "10") |
Ако је вредност Б2 „Г“, онда се наша формула тако процењује
| 123 | = ИНДИРЕКТНО ("Г" & 10)= ИНДИРЕКТНО ("Г10")= Г10 |
ИНДИРЕКТАН стил р1ц1
У нашем претходном примеру, морали смо да употребимо слово да наведемо референцу колоне. То је зато што смо користили оно што је познато као референцирање у стилу А1. У стилу А1, колоне су дате словом, а редови бројевима. Апсолутне референце означене су знаком „$“ пре ставке за коју желимо да остане апсолутна.
У р1ц1, и редови и колоне се покрећу помоћу броја. Апсолутна референца на а1 би се написала као
| 1 | = Р1Ц1 |
Ово можете прочитати као „Ред 1, колона 1“. Релативне референце су дате у заградама, али број означава позицију у односу на ћелију са формулом. Дакле, ако смо писали формулу у ћелију А10 и морамо се позвати на А1, написали бисмо формулу
| 1 | = Р [-9] Ц |
Ово можете прочитати као „Ћелија 9 редова нагоре, али у истој колони.
Разлог зашто би ово могло бити од помоћи је то што ИНДИРЕЦТ може подржати употребу записа р1ц1. Размотримо претходни пример где смо преузимали вредност из реда 10, али смо желели да можемо да променимо колону. Уместо да дамо слово, рецимо да смо ставили број у ћелију Б2. Наша формула би тада могла изгледати
| 1 | = ИНДИРЕКТНО ("Р10Ц" & Б2, ФАЛСЕ) |
Изоставили смо 2нд свађа до сада. Ако је овај аргумент изостављен или Тачан, функција ће проценити коришћењем стила А1. Пошто је лажно, процениће се у р1ц1. Претпоставимо да је вредност Б2 5. Наша формула ће то овако проценити
| 12 | = ИНДИРЕКТНО ("Р10Ц5", ФАЛСЕ)= $ Е $ 10 |
Индиректне разлике са А1 вс р1ц1
Сећате се да смо претходно показали да се садржај ове формуле никада није променио пошто је био текстуални низ?
| 1 | = ИНДИРЕКТНО ("А2") |
Ова формула ће увек гледати ћелију А2, без обзира на то где формулу преместите. У р1ц1, пошто можете да наведете релативни положај помоћу заграда, ово правило не остаје доследно. Ако ову формулу поставите у ћелију Б2
| 1 | = ИНДИРЕКТНО ("РЦ [-1]") |
Гледаће ћелију А2 (пошто је колона А једна лево од колоне Б). Ако копирате ову формулу у ћелију Б3, унутрашњи текст ће остати исти, али ИНДИРЕЦТ ће сада гледати ћелију А3.
ИНДИРЕКТНО са називом листа
Такође можете комбиновати назив листа у ИНДИРЕКТНЕ референце. Важно правило које треба запамтити је да око имена поставите појединачне наводнике, а назив листа морате одвојити од референце ћелије узвичником.
Рецимо да смо имали ово подешавање, где наводимо назив листа, ред и колону.
Наша формула за комбиновање свега овога у референцу изгледала би овако:
| 1 | = ИНДИРЕКТНО ("'" & А2 & "'!" & Б2 & Ц2) |
Тада ће се наша формула оценити на следећи начин:
| 123 | = ИНДИРЕЦТ ("'" & "Схеет2" & "'!" & "Б" & "5")= ИНДИРЕЦТ ("'" Схеет2'! Б5 ")= 'Лист 2'! Б5 |
Технички, пошто реч „Лист 2“ нема размака у њој, ми немамо потреба једноструки наводници. Потпуно је ваљано написати нешто попут
| 1 | = Лист2! А2 |
Међутим, не шкоди стављање наводника када вам не требају. Најбоља је пракса да их укључите како би ваша формула могла да обрађује инстанце тамо где би могле бити потребне.
ИНДИРЕКТНО у другу радну свеску
Такође ћемо напоменути да ИНДИРЕЦТ може створити референцу на другу радну свеску. Ограничење је да ИНДИРЕЦТ неће дохватити вредности из затворене радне свеске, па је ова употреба ограничене практичности. Ако се радна свеска на коју указује ИНДИРЕЦТ не отвара, функција ће приказати „#РЕФ!“ грешка.
Синтакса при писању назива радне свеске је да мора бити у угластим заградама. Користимо ово подешавање и покушајмо да дохватимо вредност из ћелије Ц7.
Наша формула би била
| 1 | = ИНДИРЕКТНО ("'[" & А2 & "]" & Б2 & "'! Ц7") |
Опет, обратите пажњу на постављање појединачних наводника, заграда и ускличника. Тада ће се наша формула оценити на следећи начин:
| 123 | = ИНДИРЕЦТ ("'[" & "Сампле.клск" & "]" & "Резиме" & "'! Ц7")= ИНДИРЕЦТ ("'[Сампле.кслк] Резиме'! Ц7")= '[Сампле.клск] Резиме'! Ц7 |
ИНДИРЕКТНО за изградњу динамичког опсега
Када имате велики скуп података, важно је покушати оптимизирати формуле тако да не раде више посла него што је потребно. На пример, уместо да се позивамо на целу колону А, можда бисмо желели само да наведемо тачан број ћелија на нашој листи. Размотрите следећи распоред:
У ћелију Б2 сместили смо формулу
| 1 | = ЦОУНТА (А: А) |
Рачунару је функција ЦОУНТА врло једноставна за израчунавање, јер једноставно проверава колико ћелија у колони А има неку вредност, уместо да мора да врши било какве логичке провере или математичке операције.
Сада, изградимо нашу формулу која ће сумирати вредности у колони А, али желимо да будемо сигурни да гледа само тачан опсег са вредностима (А2: А5). Написаћемо формулу као
| 1 | = СУМ (ИНДИРЕКТНО ("А2: А" & Б2)) |
Наш ИНДИРЕЦТ ће зграбити број 5 из ћелије Б2 и креираће референцу на опсег А2: А5. СУМ тада може користити овај опсег за своје израчунавање. Ако у ћелију А6 додамо још једну вредност, тада ће се број у Б2 ажурирати, а наша формула СУМ ће се такође аутоматски ажурирати како би укључила ову нову вредност.
ОПРЕЗ: Увођењем Табела у Оффице 2007, много је ефикасније складиштење података у табелу и употреба структурне референце, а не израда формуле коју смо користили у овом примеру због променљиве природе ИНДИРЕЦТ. Међутим, то могу бити случајеви у којима морате да направите листу ставки и не можете да користите табелу.
Динамичко графиконирање са ИНДИРЕЦТ
Узмимо претходни пример и учинимо још један корак. Уместо да напишемо формулу која ће нам дати збир вредности, ми ћемо створити Намед Ранге. Овај опсег бисмо могли назвати „МиДата“ и упутити на њега
| 1 | = ИНДИРЕКТНО ("А2: А" & ЦОУНТА ($ А: $ А)) |
Имајте на уму да смо ово ставили у Намед Ранге, заменили смо референцу на Б2 и уместо тога директно ставили функцију ЦОУНТА.
Сада када имамо овај именовани распон, могли бисмо га користити у графикону. Направићемо празан графикон, а затим додати низ података. За вредности серије можете написати нешто попут
| 1 | = Лист1! МиДата |
Графикон ће сада користити ову референцу за вредности графикона. Како се у колону А додају више вредности, ИНДИРЕЦТ ће се односити на све већи опсег, а наш графикон ће наставити да се ажурира са свим ново додатим вредностима.
Динамичка валидација података са ИНДИРЕЦТ
При прикупљању уноса од корисника, понекад постоји потреба да опције једног избора зависе од претходног избора. Размислите о овом распореду, где наша прва колона омогућава кориснику да бира између воћа, поврћа и меса.
У 2нд у колони, не желимо да имамо велику листу која приказује сваки могући избор, јер смо већ мало сузили ствари. Дакле, креирали смо још 3 листе које изгледају овако:
Затим ћемо доделити сваки од њих ове листе у Намед Ранге. Тј., Све воће ће бити у асортиману званом „Воће“, а поврће у „Поврћу“ итд.
Назад у нашој табели, спремни смо за подешавање валидације података у 2нд колона. Направићемо валидацију типа листе са уносом:
| 1 | = ИНДИРЕКТНО (А2) |
ИНДИРЕЦТ ће прочитати избор направљен у колони А и видети назив категорије. Дефинисали смо опсеге са овим именима, па ће ИНДИРЕЦТ тада узети то име и створити референцу на жељени опсег.
Додатне напомене
Користите ИНДИРЕЦТ функцију да креирате референцу ћелије из текста.
Прво креирајте низ текста који представља референцу ћелије. Низ мора бити у уобичајеном слову и броју колоне у стилу А1 (М37) или у стилу Р1Ц1 (Р37Ц13). Референцу можете уписати директно, али обично ћете референцирати ћелије које дефинишу редове и колоне. На крају, унесите који референтни формат ћелије изаберете. ТРУЕ или Изостављено за референцу у А1 стилу или ФАЛСЕ за Р1Ц1 стил.
Док радите са ИНДИРЕЦТ формулама, можда ћете желети да користите РОВ функција да бисте добили број реда референце или ЦОЛУМН функција да бисте добили број колоне (не слово) референце.
Повратак на листу свих функција у програму Екцел
ИНДИРЕКТАН у Гоогле табелама
ИНДИРЕКТНА функција ради потпуно исто у Гоогле таблицама као и у Екцелу:
