Садржај
Да ли имате табеле са подацима у Екцел-у којима се може мењати величина, односно број редова (колона) може да се повећава или смањује у току рада? Ако величине табеле "плутају", мораћете стално да пратите овај тренутак и исправите га:
- везе у формулама извештаја које се односе на нашу табелу
- почетни распони стожерних табела који су изграђени према нашој табели
- почетни распони графикона изграђених према нашој табели
- опсези за падајуће меније који користе нашу табелу као извор података
Све ово укупно вам неће дозволити да вам досади 😉
Биће много згодније и исправније креирати динамички „гумени“ опсег, који ће се аутоматски прилагођавати величини стварном броју редова и колона података. Да бисте ово спровели, постоји неколико начина.
Метод 1. Паметни сто
Означите опсег ћелија и изаберите са картице Почетна – Формат као табела (Почетна – Формат као табела):
Ако вам није потребан пругасти дизајн који је додат у табелу као споредни ефекат, можете га искључити на картици која се појави Конструктор (дизајн). Свака табела креирана на овај начин добија име које се може заменити погоднијим на истом месту на картици Конструктор (дизајн) на терену Назив табеле (Назив табеле).
Сада можемо да користимо динамичке везе до нашег „паметног стола“:
- Табела КСНУМКС – веза до целе табеле осим реда заглавља (А2:Д5)
- Табела 1[#Све] – линк до целе табеле (А1:Д5)
- Табела 1[Петар] – референца на колону опсега без првог заглавља ћелије (Ц2:Ц5)
- Табела 1[#Хеадерс] – веза до „заглавља“ са називима колона (А1:Д1)
Такве референце одлично функционишу у формулама, на пример:
= СУМ (Табела 1[Москва]) – обрачун суме за колону „Москва“
or
=ВПР(Ф5;Табела КСНУМКС;3;0) – претражите у табели месец из ћелије Ф5 и издајте за њега суму из Санкт Петербурга (шта је ВЛООКУП?)
Такве везе се могу успешно користити приликом креирања пивот табела избором на картици Инсерт – Пивот Табле (Инсерт – Пивот Табле) и уношење имена паметне табеле као извора података:
Ако изаберете фрагмент такве табеле (на пример, прве две колоне) и креирате дијаграм било ког типа, онда када додајете нове линије, они ће аутоматски бити додати у дијаграм.
Приликом креирања падајућих листа, не могу се користити директне везе до елемената паметне табеле, али можете лако да заобиђете ово ограничење користећи тактички трик – користите функцију ИНДИРЕКТАН (ИНДИРЕКТАН), што претвара текст у везу:
Оне. веза до паметне табеле у облику текстуалног низа (под наводницима!) претвара се у пуноправну везу, а падајућа листа то нормално перципира.
Метод 2: Динамички именовани опсег
Ако је претварање ваших података у паметну табелу из неког разлога непожељно, онда можете користити мало компликованији, али много суптилнији и свестранији метод – креирајте динамички именовани опсег у Екцел-у који се односи на нашу табелу. Затим, као иу случају паметне табеле, можете слободно користити назив креираног опсега у било којој формули, извештају, графикону итд. Почнимо са једноставним примером:
Задатак: направите динамички именовани опсег који ће се односити на листу градова и аутоматски се растеже и смањује у величини када додајете нове градове или их бришете.
Биће нам потребне две уграђене Екцел функције доступне у било којој верзији − ПОИЦПОЗ (УТАКМИЦА) да одреди последњу ћелију опсега, и ИНДЕКС (ИНДЕКС) да бисте направили динамичку везу.
Проналажење последње ћелије помоћу МАТЦХ
МАТЦХ(вредност_претраживања, опсег, тип_подударања) – функција која тражи дату вредност у опсегу (реду или колони) и враћа редни број ћелије у којој је пронађена. На пример, формула МАТЦХ(“Март”;А1:А5;0) ће као резултат вратити број 4, јер се реч “Март” налази у четвртој ћелији у колони А1:А5. Последњи аргумент функције Матцх_Типе = 0 значи да тражимо тачно подударање. Ако овај аргумент није наведен, функција ће се пребацити у режим претраге за најближу најмању вредност – то је управо оно што се може успешно користити за проналажење последње заузете ћелије у нашем низу.
Суштина трика је једноставна. МАТЦХ тражи ћелије у опсегу од врха до дна и, теоретски, требало би да се заустави када пронађе најмању вредност најближу датој. Ако наведете вредност која је очигледно већа од било које доступне у табели као жељену вредност, онда ће МАТЦХ доћи до самог краја табеле, пронаћи ништа и дати редни број последње попуњене ћелије. И треба нам!
Ако у нашем низу постоје само бројеви, онда можемо одредити број као жељену вредност, која је очигледно већа од било ког од оних у табели:
За гаранцију можете користити број 9Е + 307 (9 пута 10 на степен од 307, тј. 9 са 307 нула) – максимални број са којим Екцел може да ради у принципу.
Ако у нашој колони постоје текстуалне вредности, онда као еквивалент највећем могућем броју можете уметнути конструкцију РЕПЕАТ(“и”, 255) – текстуални низ који се састоји од 255 слова “и” – последње слово азбука. Пошто Екцел заправо упоређује кодове знакова приликом претраживања, било који текст у нашој табели ће технички бити „мањи“ од тако дугачког реда „ииииииииииииииииииииииииииииииииииииииииииии“:
Генеришите везу користећи ИНДЕКС
Сада када знамо позицију последњег непразног елемента у табели, остаје да формирамо везу са читавим нашим опсегом. За ово користимо функцију:
ИНДЕКС(опсег; број_редова; број_колоне)
Даје садржај ћелије из опсега по броју реда и колоне, односно, на пример, функција =ИНДЕКС(А1:Д5;3;4) у нашој табели са градовима и месецима из претходног метода ће дати 1240 – садржај из 3. реда и 4. колоне, односно ћелије Д3. Ако постоји само једна колона, онда се њен број може изоставити, односно формула ИНДЕКС(А2:А6;3) ће дати „Самара“ на последњем снимку екрана.
И постоји једна не сасвим очигледна нијанса: ако се ИНДЕКС не унесе само у ћелију после знака =, као и обично, већ се користи као завршни део референце на опсег после двотачка, онда се више не даје садржај ћелије, али њена адреса! Дакле, формула попут $А$2:ИНДЕКС($А$2:$А$100;3) ће дати референцу на опсег А2:А4 на излазу.
И ту долази функција МАТЦХ, коју убацујемо у ИНДЕКС да бисмо динамички одредили крај листе:
=$А$2:ИНДЕКС($А$2:$А$100; МЕЧ(РЕП(“И”;255);А2:А100))
Направите именовани опсег
Остаје да се све то спакује у једну целину. Отворите картицу формула (Формуле) И кликните на Наме Манагер (Менаџер имена). У прозору који се отвори кликните на дугме Створити (ново), унесите име нашег опсега и формулу у поље Домет (Референца):
Остаје да кликнете на OK а спреман опсег се може користити у било којој формулацији, падајућим листама или графиконима.
- Коришћење функције ВЛООКУП за повезивање табела и вредности за тражење
- Како да направите падајућу листу која се аутоматски попуњава
- Како направити стожерну табелу за анализу велике количине података