Екцел формула: Динамички именовани опсег са ИНДЕКС -

Генеричка формула

=$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 редова.

Утврђивање последњег реда

Постоји неколико начина за одређивање последњег реда (последњег релативног положаја) у скупу података, у зависности од структуре и садржаја података на радном листу: