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