Генеричка формула
=$A$1:INDEX($A:$A,lastrow)
Резиме
Један од начина за стварање динамичког именованог опсега у програму Екцел је употреба функције ИНДЕКС. У приказаном примеру, именовани опсег „подаци“ дефинисан је следећом формулом:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
који се решава у опсегу $ А $ 2: $ А $ 10.
Напомена: ова формула треба да дефинише именовани опсег који се може користити у другим формулама.
Објашњење
Ова страница приказује пример динамичког именованог опсега створеног помоћу функције ИНДЕКС заједно са функцијом ЦОУНТА. Динамички именовани опсези се аутоматски проширују и скупљају када се подаци додају или уклоне. Они су алтернатива коришћењу Екцел табеле, која се такође мења када се подаци додају или уклањају.
Функција ИНДЕКС враћа вредност на датој позицији у опсегу или низу. ИНДЕКС можете користити за преузимање појединачних вредности или целих редова и колона у опсегу. Оно што ИНДЕКС чини посебно корисним за динамичке именоване опсеге је што заправо враћа референцу. То значи да помоћу ИНДЕКС-а можете створити мешовите референце попут $ А $ 1: А100.
У приказаном примеру, именовани опсег „подаци“ дефинисан је следећом формулом:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
који се решава у опсегу $ А $ 2: $ А $ 10.
Како функционишу ове формуле
Прво приметите да је ова формула састављена из два дела која се налазе са обе стране оператора опсега (:). Са леве стране имамо почетну референцу за опсег, тврдо кодирану као:
$A$2
Десно је крајња референца за опсег, креирана са ИНДЕКС-ом овако:
INDEX($A:$A,COUNTA($A:$A))
Овде доводимо ИНДЕКС у целу колону А за низ, а затим користимо функцију ЦОУНТА да бисмо пронашли „последњи ред“ у опсегу. ЦОУНТА овде добро функционише јер у колони А постоји 10 вредности, укључујући ред заглавља. ЦОУНТА стога враћа 10, који иде директно у ИНДЕКС као број реда. Затим ИНДЕКС враћа референцу на $ А $ 10, последњи коришћени ред у опсегу:
INDEX($A:$A,10) // resolves to $A$10
Дакле, коначни резултат формуле је овај опсег:
$A$2:$A$10
Дводимензионални опсег
Горњи пример делује за једнодимензионални опсег. Да бисте креирали дводимензионални динамички опсег где је и број колона динамичан, можете користити исти приступ, проширен овако:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Као и раније, ЦОУНТА се користи за откривање „ластров-а“, а ми поново користимо ЦОУНТА-у да бисмо добили „ластцолумн“. Они се достављају у индекс као ред_нум, односно број_колоне.
Међутим, за низ испоручујемо цео радни лист, унет као свих 1048576 редова, што омогућава ИНДЕКС-у да врати референцу у 2Д простору.
Напомена: Екцел 2003 подржава само 65535 редова.
Утврђивање последњег реда
Постоји неколико начина за одређивање последњег реда (последњег релативног положаја) у скупу података, у зависности од структуре и садржаја података на радном листу:
- Последњи ред у мешовитим подацима са празним местима
- Последњи ред у мешовитим подацима без празних места
- Последњи ред у текстуалним подацима
- Последњи ред у нумеричким подацима
