Екцел формула: Динамички именовани опсег са ОФФСЕТ -

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

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Резиме

Један од начина за стварање динамичког именованог опсега са формулом је употреба функције ОФФСЕТ заједно са функцијом ЦОУНТА. Динамички опсези су познати и као опсези који се шире - аутоматски се проширују и скупљају како би се прилагодили новим или избрисаним подацима.

Напомена: ОФФСЕТ је испарљива функција, што значи да се прерачунава при свакој промени радног листа. Са модерном машином и мањим скупом података, ово не би требало да прави проблем, али можда ћете видети спорије перформансе на великим скуповима података. У том случају размислите о томе да направите динамички именовани опсег са функцијом ИНДЕКС.

У приказаном примеру, формула која се користи за динамички опсег је:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Објашњење

Ова формула користи функцију ОФФСЕТ за генерисање опсега који се шири и скупља подешавањем висине и ширине на основу броја непразних ћелија.

Први аргумент у ОФФСЕТ представља прву ћелију у подацима (исходиште), што је у овом случају ћелија Б5. Следећа два аргумента су одступања за редове и колоне и испоручују се као нула.

Последња два аргумента представљају висину и ширину. Висина и ширина се генеришу у ходу коришћењем ЦОУНТА-е, што резултујућу референцу чини динамичном.

За висину користимо функцију ЦОУНТА за бројање празних вредности у опсегу Б5: Б100. Ово не подразумева празне вредности у подацима и вредности изван Б100. ЦОУНТА се враћа 6.

За ширину користимо функцију ЦОУНТА за бројање празних вредности у опсегу Б5: З5. Ово претпоставља да нема ћелија заглавља и заглавља изван З5. ЦОУНТА се враћа 6.

У овом тренутку формула изгледа овако:

=OFFSET(B5,0,0,6,6)

Са овим информацијама, ОФФСЕТ враћа референцу на Б5: Г10, што одговара опсегу висине 6 редова са 6 колона попречно.

Напомена: Распоне који се користе за висину и ширину треба прилагодити тако да одговарају распореду радног листа.

Варијација са пуним референцама колона / редова

Такође можете користити пуне референце колона и редова за висину и ширину на следећи начин:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Имајте на уму да се висина подешава са -2 како би се узеле у обзир вредности заглавља и наслова у ћелијама Б4 и Б2. Предност овог приступа је једноставност распона унутар ЦОУНТА-е. Недостатак долази због велике величине пуних колона и редова - мора се водити рачуна да се спрече погрешне вредности изван опсега, јер оне лако могу да одброје.

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

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