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