Како подесити опсег вредности у Екцелу

Када користите Мицрософт Екцел, врло често корисник не зна унапред колико ће информација бити у табели као резултат. Због тога не разумемо у свим ситуацијама који опсег треба покрити. На крају крајева, скуп ћелија је променљив концепт. Да бисте се решили овог проблема, потребно је генерисање опсега учинити аутоматским тако да се заснива искључиво на количини података које је корисник унео.

Аутоматско мењање опсега ћелија у Екцел-у

Предност аутоматских опсега у Екцел-у је у томе што знатно олакшавају коришћење формула. Поред тога, они омогућавају значајно поједностављење анализе сложених података који садрже велики број формула, које укључују многе функције. Овом опсегу можете дати име, а затим ће се аутоматски ажурирати у зависности од тога које податке садржи.

Како направити аутоматску промену опсега у Екцелу

Претпоставимо да сте инвеститор који треба да инвестира у неки објекат. Као резултат тога, желимо да добијемо информацију о томе колико укупно можете зарадити за све време док ће новац радити за овај пројекат. Ипак, да бисмо добили ове информације, потребно је редовно пратити колику нам укупну добит доноси овај објекат. Направите исти извештај као на овом снимку екрана.

На први поглед решење је очигледно: само треба да саберете целу колону. Ако се у њему појаве уноси, износ ће се ажурирати независно. Али ова метода има много недостатака:

1. Ако се проблем реши на овај начин, ћелије укључене у колону Б неће бити могуће користити у друге сврхе.
2. Таква табела ће потрошити много РАМ-а, што ће онемогућити коришћење документа на слабим рачунарима.

Дакле, неопходно је да се овај проблем реши кроз динамичка имена. Да бисте их креирали, морате извршити следећи низ радњи:

1. Идите на картицу „Формуле“, која се налази у главном менију. Појавиће се одељак „Дефинисана имена“, где се налази дугме „Додели име“ на које треба да кликнемо.
2. Затим ће се појавити оквир за дијалог у којем треба да попуните поља као што је приказано на снимку екрана. Важно је напоменути да морамо да применимо функцију = ДИСПЛАЦЕМЕНТ заједно са функцијом ПРОВЕРАда бисте креирали опсег за аутоматско ажурирање. 
3. Након тога треба да користимо функцију СУМ, који користи наш динамички опсег као аргумент. 

Након што завршимо ове кораке, можемо видети како се покривеност ћелија које припадају опсегу „прихода“ ажурира док тамо додајемо нове елементе.

Функција ОФФСЕТ у Екцел-у

Погледајмо функције које смо раније снимили у пољу „опсег“. Коришћење функције ОДЛАГАЊЕ можемо одредити количину опсега с обзиром на то колико ћелија у колони Б је попуњено. Аргументи функције су следећи:

1. Старт ћелија. Овим аргументом корисник може да назначи која ће се ћелија у опсегу сматрати у горњем левом углу. Извешће доле и десно.
2. Опсег је померен по редовима. Користећи овај опсег, постављамо број ћелија за који треба да се деси помак од горње леве ћелије опсега. Можете користити не само позитивне вредности, већ и нулу и минус. У овом случају, померање се можда уопште неће догодити или ће се извршити у супротном смеру.
3. Опсег је померен колонама. Овај параметар је сличан претходном, само што вам омогућава да подесите степен хоризонталног померања опсега. Овде можете користити и нулте и негативне вредности.
4. Количина распона у висини. У ствари, наслов овог аргумента нам јасно даје до знања шта он значи. Ово је број ћелија за који опсег треба да се повећа.
5. Вредност опсега у ширини. Аргумент је сличан претходном, само што се тиче колона.

Не морате да наведете последња два аргумента ако не морате. У овом случају, вредност опсега ће бити само једна ћелија. На пример, ако наведете формулу =ОФФСЕТ(А1;0;0), ова формула ће се односити на исту ћелију као и она у првом аргументу. Ако је вертикални помак постављен на 2 јединице, онда ће се у овом случају ћелија односити на ћелију А3. Хајде да сада детаљно опишемо шта значи функција ПРОВЕРА.

ЦОУНТ функција у Екцел-у

Коришћење функције ПРОВЕРА одређујемо колико смо ћелија у колони Б укупно попунили. То јест, користећи две функције, одређујемо колико ћелија у опсегу је попуњено и на основу примљених информација одређујемо величину опсега. Дакле, коначна формула ће бити следећа: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Хајде да погледамо како правилно разумети принцип ове формуле. Први аргумент указује на то где почиње наш динамички опсег. У нашем случају, ово је ћелија Б2. Даљи параметри имају нулте координате. Ово сугерише да нам не требају помаци у односу на горњу леву ћелију. Све што попуњавамо је вертикална величина опсега, као што смо користили функцију ПРОВЕРА, који одређује број ћелија које садрже неке податке. Четврти параметар који смо попунили је јединица. Тако показујемо да укупна ширина опсега треба да буде једна колона.

Дакле, користећи функцију ПРОВЕРА корисник може што ефикасније да користи меморију учитавањем само оних ћелија које садрже неке вредности. Сходно томе, неће бити додатних грешака у раду повезаних са лошим перформансама рачунара на којем ће табела радити.

Сходно томе, да бисте одредили величину опсега у зависности од броја колона, потребно је да извршите сличан редослед радњи, само у овом случају морате навести јединицу у трећем параметру, а формулу у четвртом ПРОВЕРА.

Видимо да уз помоћ Екцел формула не можете само да аутоматизујете математичке прорачуне. Ово је само кап у мору, али у ствари вам омогућавају да аутоматизујете скоро сваку операцију која вам падне на памет.

Динамички графикони у Екцел-у

Дакле, у последњем кораку смо успели да креирамо динамички опсег, чија величина у потпуности зависи од тога колико попуњених ћелија садржи. Сада, на основу ових података, можете креирати динамичке графиконе који ће се аутоматски мењати чим корисник изврши било какве промене или дода додатну колону или ред. Редослед радњи у овом случају је следећи:

1. Одаберемо наш опсег, након чега убацујемо графикон типа „Хистограм са груписањем“. Ову ставку можете пронаћи у одељку „Инсерт“ у одељку „Графикони – Хистограм“.
2. Кликнемо левим мишем на случајну колону хистограма, након чега ће се у функцијској линији приказати функција =СЕРИЕС(). На снимку екрана можете видети детаљну формулу. 
3. Након тога, потребно је извршити неке промене у формули. Морате да замените опсег после „Схеет1!“ на име опсега. Ово ће резултирати следећом функцијом: =РОВ(Таб1!$Б$1;;Таста1!приход;1)
4. Сада остаје да додате нови запис у извештај како бисте проверили да ли се графикон аутоматски ажурира или не.

Хајде да погледамо наш дијаграм.

Хајде да сумирамо како смо то урадили. У претходном кораку креирали смо динамички опсег чија величина зависи од тога колико елемената садржи. Да бисмо то урадили, користили смо комбинацију функција ПРОВЕРА и ОДЛАГАЊЕ. Направили смо име овог опсега, а затим смо користили референцу на ово име као опсег нашег хистограма. Који одређени опсег изабрати као извор података у првој фази није толико важно. Главна ствар је да га касније замените именом опсега. На овај начин можете уштедети много РАМ-а.

Именовани опсези и њихова употреба

Хајде сада да разговарамо детаљније о томе како правилно креирати именоване опсеге и користити их за обављање задатака који су постављени за корисника Екцел-а.

Подразумевано користимо редовне адресе ћелија да бисмо уштедели време. Ово је корисно када треба да напишете опсег једном или више пута. Ако треба да се користи све време или мора да буде прилагодљив, онда треба користити именоване опсеге. Они знатно олакшавају креирање формула, а кориснику неће бити тако тешко да анализира сложене формуле које укључују велики број функција. Хајде да опишемо неке од корака који су укључени у креирање динамичких опсега.

Све почиње именовањем ћелије. Да бисте то урадили, једноставно га изаберите, а затим унесите име које нам је потребно у поље његовог имена. Важно је да се лако памти. Постоје нека ограничења која треба узети у обзир приликом именовања:

1. Максимална дужина је 255 знакова. Ово је сасвим довољно да доделите име које вам срце жели.
2. Име не сме да садржи размаке. Стога, ако садржи неколико речи, онда их је могуће раздвојити помоћу доње црте.

Ако касније на другим листовима ове датотеке будемо морали да прикажемо ову вредност или да је применимо да извршимо даље прорачуне, онда нема потребе да прелазимо на први лист. Можете само да запишете име ћелије овог опсега.

Следећи корак је креирање именованог опсега. Процедура је у основи иста. Прво морате да изаберете опсег, а затим наведете његово име. Након тога, ово име се може користити у свим другим операцијама са подацима у Екцел-у. На пример, именовани опсези се често користе за дефинисање збира вредности.

Поред тога, могуће је креирати именовани опсег помоћу картице Формуле помоћу алата Сет Наме. Након што га одаберемо, појавиће се прозор у коме треба да изаберемо име за наш опсег, као и да наведемо област на коју ће се он проширити ручно. Такође можете одредити где ће овај опсег деловати: унутар једног листа или у целој књизи.

Ако је опсег имена већ креиран, онда да бисте га користили, постоји посебна услуга која се зове менаџер имена. Омогућава не само уређивање или додавање нових имена, већ и њихово брисање ако више нису потребни.

Треба имати на уму да када користите именоване опсеге у формулама, онда након брисања, формуле неће бити аутоматски преписане исправним вредностима. Због тога може доћи до грешака. Стога, пре него што избришете именовани опсег, морате се уверити да се не користи ни у једној од формула.

Други начин да креирате именовани опсег је да га добијете из табеле. Да бисте то урадили, постоји посебан алат под називом „Креирај из селекције“. Како разумемо, да бисте га користили, прво морате да изаберете опсег који ћемо уређивати, а затим да поставите место на коме имамо наслове. Као резултат, на основу ових података, Екцел ће аутоматски обрадити све податке, а наслови ће бити аутоматски додељени.

Ако наслов садржи неколико речи, Екцел ће их аутоматски одвојити доњом цртом.

Тако смо схватили како да креирамо динамичке именоване опсеге и како вам омогућавају да аутоматизујете рад са великим количинама података. Као што видите, довољно је користити неколико функција и програмских алата уграђених у функционалност. Уопште нема ништа компликовано, иако почетнику на први поглед може изгледати тако.