Да бисте направили тачне прорачуне у Екцелу, неопходно је разумети како функционишу различите врсте референци ћелија.
А1 вс. Р1Ц1 Референце
Екцел радни листови садрже много ћелија и (подразумевано) свака ћелија је идентификована својом колонско писмо након чега следи његов редни број. Ово је познато као референцирање у стилу А1. Примери: А1, Б4, Ц6
Референтни стил А1
Опционално, можете прећи на Референтни режим Р1Ц1 да се односи на ред ћелије и број колоне. Уместо да се позивате на ћелију А1, позвали бисте се на Р1Ц1 (ред 1, колона 1). Ћелија Ц4 би се односила на Р4Ц3.
Референтни стил Р1Ц1
Референцирање у стилу Р1Ц1 изузетно је неуобичајено у Екцелу. Осим ако немате добар разлог, вероватно бисте се требали држати подразумеваног референтног режима у стилу А1. Међутим, ако користите ВБА, вероватно ћете наићи на овај референтни стил.
Пребаците се на Р1Ц1 референтни стил
Да бисте променили референтни стил, идите на Датотека> Опција> Формула. Означите поље поред Р1Ц1 референтни стил.
Намед Рангес
Једна од функција које се Екцел најмање користи је функција Намед Рангес. Уместо да се на ћелију (или групу ћелија) позивате према локацији ћелије (нпр. Б3 или Р3Ц2), можете да дате назив том опсегу и једноставно наведете назив опсега у својим формулама.
Да бисте именовали опсег:
- Изаберите ћелију или ћелије којима желите да дате име
- Кликните Унутар оквира за назив опсега
- Унесите жељено име
- Притисните Ентер
Сада можете референцирати ћелију А1 тако што ћете уместо тога унети = ранге_наме1. Ово је веома корисно при раду са великим радним свескама са више радних листова.
Опсег ћелија
Када користите Екцел -ове уграђене функције, можда ћете морати да наведете опсег ћелија. Опсези ћелија изгледају овако „А1: Б3“. Ова референца се односи на све ћелије између А1 и Б3: ћелије А1, А2, А3, Б1, Б2, Б3.
Да бисте изабрали опсег ћелија приликом уноса формуле:
- Укуцајте опсег (почетни и завршни опсег одвојите тачком -зарезом)
- Кликните мишем на прву референцу ћелије, држите притиснут тастер миша и превуците жељени опсег.
- Држите притиснут тастер схифт и крећите се помоћу тастера са стрелицама да бисте изабрали свој опсег
Апсолутне (замрзнуте) и релативне референце
Приликом уноса референци ћелије у формуле, можете користити релативне или апсолутне (замрзнуте) референце. Релативне референце ће се пропорционално померати када копирате формулу у нову ћелију. Апсолутне референце ће остати непромењене. Погледајмо неке примере:
Релативна референца
Релативна референца у Екцелу изгледа овако
= А1
Када копирате и залепите формулу са релативним референцама, релативне референце ће се померати пропорционално. Осим ако не наведете другачије, референце ћелија ће према заданим поставкама бити релативне (одмрзнуте).
Пример: Ако копирате „= А1“ низ један ред, референца се мења у „= А2“.
Апсолутне (замрзнуте) ћелијске референце
Ако не желите да се референце ћелија померају приликом копирања формуле, можете да „замрзнете“ референце ћелија додавањем знакова долара ($ с) испред референце коју желите да замрзнете. Сада када копирате и залепите формулу, референца ћелије ће остати непромењена. Можете да замрзнете референцу ретка, референцу колоне или обоје.
О1: Ништа није замрзнуто
$ А1: Колона је замрзнута, али ред није замрзнут
А $ 1: Ред је замрзнут, али колона није замрзнута
$ А $ 1: Ред и колона су замрзнути
Апсолутна референтна пречица
Ручно додавање знакова долара ($ с) у формуле није баш практично. Уместо тога, док креирате формулу, користите тастер Ф4 за пребацивање између апсолутних/релативних референци ћелија.
Пример апсолутне ћелијске референце
Када бисте заправо морали да замрзнете ћелијску референцу? Један уобичајен пример је када имате улазне ћелије на које се често упућује. У доњем примеру желимо да израчунамо порез на промет за сваку количину ставки менија. Порез на промет је константан за све ставке, па ћемо се више пута позивати на ћелију за порез на промет.
Да бисте пронашли укупан порез на промет, унесите формулу „= (Б3*Ц3)*$ Ц $ 1“ у колону Д и копирајте формулу надоле.
Мешовита референца
Можда сте чули за мешовите референце ћелија. Мешовита референца је када је референца ретка или колоне закључана (али не обоје).
Мешовита референца
Запамтите, помоћу тастера „Ф4“ можете да се крећете кроз релативне, апсолутне референце ћелија.
Референце ћелија - Уметање и брисање редова/колона
Можда се питате шта се дешава са референцама ћелија када убацујете или бришете редове/колоне?
Референца ћелије ће се аутоматски ажурирати како би се односила на оригиналну ћелију. То је случај без обзира на то да ли је референца ћелије замрзнута.
3Д референце
Понекад ћете можда морати да радите са неколико радних листова са истим обрасцима података. Екцел вам омогућава да се позивате на више листова одједном без потребе за ручним уносом сваког радног листа. Можете се позвати на низ листова слично ономе на шта би се односило на распон ћелија. Пример „Лист 1: Лист 5! А1“ упућивао би на ћелије А1 на свим листовима од листа 1 до листа 5.
Прођимо кроз пример:
Желите да саберете укупне продате јединице за сваки производ у свим продавницама. Свака продавница има свој радни лист и сви радни листови имају идентичан формат. Можете направити формулу сличну овој:
Ово није превише тешко са само четири радна листа, али шта ако имате 40 радних листова? Да ли бисте заиста желели ручно да додате сваку референцу ћелије?
Уместо тога, можете користити 3Д референцу за референцирање више листова одједном с лакоћом (слично као што можете референцирати низ ћелија).
Будите опрезни! Редослед ваших радних листова је важан. Ако премјестите други лист између референцираних листова (СтореА и СтореД), тај ће лист бити укључен. Насупрот томе, ако лист преместите изван опсега листова (пре СтореА или после СтореД), он више неће бити укључен.
Референца кружних ћелија
Кружна референца ћелије је када се ћелија позива на себе. На пример, ако се резултат ћелије Б1 користи као улаз за ћелију Б1, тада се ствара кружна референца. Ћелија не мора да се директно односи на себе. Могу постојати посредни кораци.
Пример:
У овом случају, формула за ћелију Б2 је „А2+А3+Б2“. Пошто сте у ћелији Б2, не можете користити Б2 у једначини. Ово би покренуло кружну референцу и вредност у ћелији „Б2“ ће се аутоматски поставити на „0“.
Обично су кружне референце резултат грешке корисника, али у неким случајевима можете користити кружну референцу. Примарни пример коришћења кружне референце је израчунавање вредности итеративно. Да бисте то урадили, морате да одете на Датотека> Опције> Формуле и Омогућен итеративни прорачун:
Спољашње референце
Понекад ћете приликом израчунавања података морати да се позивате на податке изван радне свеске. Ово се назива спољна референца (веза).
Да бисте изабрали спољашњу референцу током креирања формуле, идите до спољне радне свеске и изаберите референцу као што бисте иначе.
За навигацију до друге радне свеске можете користити пречицу ЦТРЛ + ТАБ или идите на Поглед> Промени Виндовс.
Када изаберете ћелију, видећете да спољна референца изгледа овако:
Обратите пажњу на то да је назив радне свеске затворен заградама [].
Када затворите референцирану радну свеску, референца ће приказати локацију датотеке:
Када поново отворите радну свеску која садржи спољну везу, од вас ће бити затражено да омогућите аутоматско ажурирање веза. Ако то учините, Екцел ће отворити референтну вредност са тренутном вредношћу радне свеске. Чак и ако је затворено! Будите опрезни! Можда ово желите или не желите.
Именовани распони и спољне референце
Шта се дешава са вашом спољашњом референцом ћелије када се редови / колоне додају или избришу из референтне радне свеске? Ако су обе радне свеске отворене, референце ћелија се аутоматски ажурирају. Међутим, ако обе радне свеске нису отворене, референце ћелија неће се ажурирати и више неће бити важеће. Ово представља велику забринутост при повезивању са спољним радним свескама. Многе грешке су узроковане овим.
Ако се повезујете са спољним радним свескама, референцу ћелије треба да дате именованом опсегу (за више информација погледајте претходни одељак). Сада ће се ваша формула односити на именовани опсег без обзира на промене у спољној радној свесци.
