Једно од најчешћих упозорења о грешци са којима се корисници сусрећу у Екцел-у је „Кружна референца“. Хиљаде корисника има исти проблем, а јавља се када се формула директно или индиректно позива на сопствену ћелију, изазивајући бесконачну петљу израчунавања.
На пример, имате две вредности у ћелијама Б1 и Б2. Када се формула =Б1+Б2 унесе у Б2, она ствара кружну референцу; формула у Б2 се више пута прерачунава јер се сваки пут када израчунава вредност Б2 променила.
Већина кружних референци су ненамерне грешке; Екцел ће вас упозорити на ово. Међутим, постоје и предвиђене кружне референце, које се користе за итеративне прорачуне. Ненамерне кружне референце у вашем радном листу могу довести до погрешног израчунавања формуле.
Стога ћемо у овом чланку објаснити све што треба да знате о кружним референцама, као и како да пронађете, поправите, уклоните и користите кружне референце у Екцел-у.
Како пронаћи и руковати кружним референцама у Екцел-у
Када радимо са Екцел-ом, понекад наилазимо на грешке кружне референце које се дешавају када унесете формулу која укључује ћелију у којој се налази ваша формула. У суштини, то се дешава када ваша формула покушава сама да израчуна.
На пример, имате колону бројева у ћелији А1:А4 и користите функцију СУМ (=СУМ(А1:А5)) у ћелији А5. Ћелија А5 директно се односи на сопствену ћелију, што је погрешно. Дакле, добићете следеће кружно упозорење:
Када добијете горњу поруку упозорења, можете да кликнете на дугме „Помоћ“ да бисте сазнали више о грешци или да затворите прозор поруке о грешци тако што ћете кликнути на дугме „ОК“ или „Кс“ и добити „0“ као резултат.
Понекад кружне референтне петље могу довести до пада вашег прорачуна или успоравања перформанси радног листа. Циркуларна референца такође може довести до бројних других проблема, који неће бити евидентни одмах. Дакле, најбоље је избегавати ове.
Директне и индиректне циркуларне референце
Кружне референце могу се категорисати у два типа: директне кружне референце и индиректне кружне референце.
Директна референца
Директна кружна референца је прилично једноставна. Директна кружна референца са упозорењем се појављује када се формула директно позива на сопствену ћелију.
У следећем примеру, формула у ћелији А2 директно се односи на сопствену ћелију (А2).
Када се појави порука упозорења, можете кликнути на „ОК“, али то ће резултирати само са „0“.
Индиректна кружна референца
Индиректна кружна референца у Екцел-у се јавља када се вредност у формули односи на сопствену ћелију, али не директно. Другим речима, кружна референца се може формирати тако што две ћелије упућују једна на другу.
Хајде да објаснимо овим једноставним примером.
Сада вредност почиње од А1 који има вредност 20.
Затим, ћелија Ц3 се односи на ћелију А1.
Затим, ћелија А5 се односи на ћелију Ц3.
Сада замените вредност 20 у ћелији А1 формулом као што је приказано испод. Свака друга ћелија зависи од ћелије А1. Када користите референцу било које друге претходне ћелије формуле у А1, то ће изазвати кружно упозорење о референци. Јер, формула у А1 се односи на ћелију А5, која се односи на Ц3, а ћелија Ц3 се односи на А1, отуда и кружна референца.
Када кликнете на „ОК“, то резултира вредношћу од 0 у ћелији А1 и Екцел креира повезану линију која приказује претходнике и зависне трагове као што је приказано у наставку. Ову функцију можемо користити да лако пронађемо и поправимо/уклонимо кружне референце.
Како омогућити / онемогућити кружне референце у Екцелу
Подразумевано, итеративни прорачуни су искључени (онемогућени) у Екцел-у. Итеративни прорачуни су прорачуни који се понављају све док не испуни одређени услов. Када је онемогућен, Екцел приказује поруку кружне референце и враћа 0 као резултат.
Међутим, понекад су потребне кружне референце за израчунавање петље. Да бисте користили кружну референцу, морате омогућити итеративне прорачуне у вашем Екцел-у и то ће вам омогућити да извршите своје прорачуне. Сада ћемо вам показати како можете да омогућите или онемогућите итеративне прорачуне.
У Екцел 2010, Екцел 2013, Екцел 2016, Екцел 2019 и Мицрософт 365, идите на картицу „Датотека“ у горњем левом углу програма Екцел, а затим кликните на „Опције“ у левом окну.
У прозору Екцел опције идите на картицу „Формула“ и означите поље за потврду „Омогући итеративно израчунавање“ у одељку „Опције израчунавања“. Затим кликните на „ОК“ да бисте сачували промене.
Ово ће омогућити итеративно израчунавање и тиме омогућити кружну референцу.
Да бисте то постигли у претходним верзијама Екцел-а, следите ове кораке:
- У програму Екцел 2007 кликните на дугме Оффице > Екцел опције > Формуле > област итерације.
- У програму Екцел 2003 и старијим верзијама, потребно је да одете на Мени > Алатке > Опције > картица Израчунавање.
Максималне итерације и максимални параметри промене
Када омогућите итеративне прорачуне, можете контролисати итеративне прорачуне тако што ћете навести две доступне опције у одељку Омогући итеративно израчунавање као што је приказано на слици испод.
- Максимум итерација – Овај број одређује колико пута формула треба да се поново израчуна пре него што добије коначни резултат. Подразумевана вредност је 100. Ако је промените у „50“, Екцел ће поновити прорачуне 50 пута пре него што вам да коначни резултат. Запамтите да што је већи број итерација, то је више ресурса и времена потребно за израчунавање.
- Максимална промена – Одређује максималну промену између резултата прорачуна. Ова вредност одређује тачност резултата. Што је број мањи, то би резултат био тачнији и дуже је потребно да се израчуна радни лист.
Ако је опција итеративних прорачуна омогућена, нећете добити никакво упозорење кад год постоји кружна референца на вашем радном листу. Омогућите интерактивни прорачун само када је то апсолутно неопходно.
Пронађите кружну референцу у Екцел-у
Претпоставимо да имате велики скуп података и да сте добили упозорење о кружној референци, и даље ћете морати да сазнате где (у којој ћелији) је дошло до грешке да бисте је поправили. Да бисте пронашли кружне референце у Екцел-у, следите ове кораке:
Коришћење алатке за проверу грешака
Прво отворите радни лист на коме се десила кружна референца. Идите на картицу „Формула“, кликните на стрелицу поред алатке „Провера грешака“. Затим само пређите курсором преко опције „Кружне референце“, Екцел ће вам показати листу свих ћелија које су укључене у кружну референцу као што је приказано испод.
Кликните на коју год адресу ћелије желите на листи и она ће вас одвести до те адресе ћелије да решите проблем.
Коришћење статусне траке
Такође можете пронаћи кружну референцу на статусној траци. На статусној траци Екцел-а ће вам показати најновију адресу ћелије са кружном референцом, као што је „Кружне референце: Б6“ (погледајте снимак екрана испод).
Постоје одређене ствари које треба да знате када рукујете кружним референцама:
- Статусна трака неће приказати адресу ћелије са кружним референцама када је омогућена опција Итеративно израчунавање, тако да морате да је онемогућите пре него што почнете да тражите кружне референце у радној свесци.
- У случају да кружна референца није пронађена у активном листу, статусна трака приказује само „Кружне референце“ без адресе ћелије.
- Добићете само једном кружну референцу и након што кликнете на „ОК“, неће се поново приказати упит следећи пут.
- Ако ваша радна свеска има кружне референце, показаће вам упит сваки пут када је отворите док не решите кружну референцу или док не укључите итеративно израчунавање.
Уклоните кружну референцу у Екцел-у
Проналажење кружних референци је лако, али поправљање није тако једноставно. Нажалост, у Екцелу не постоји опција која ће вам омогућити да уклоните све кружне референце одједном.
Да бисте поправили кружне референце, морате да пронађете сваку кружну референцу појединачно и покушате да је модификујете, потпуно уклоните кружну формулу или је замените другом.
Понекад, у једноставним формулама, све што треба да урадите је да поново подесите параметре формуле тако да се не односи на себе. На пример, промените формулу у Б6 у =СУМ(Б1:Б5)*А5 (мењајући Б6 у Б5).
Вратиће резултат прорачуна као „756“.
У случајевима када је тешко пронаћи кружну референцу у Екцел-у, можете користити функције Траце Прецедентс и Траце Депендентс да бисте је пратили до извора и решили је једно по једно. Стрелица показује на које ћелије утиче активна ћелија.
Постоје две методе праћења које вам могу помоћи да избришете кружне референце показујући односе између формула и ћелија.
Да бисте приступили методама праћења, идите на картицу „Формуле“, а затим кликните на „Прецеденти праћења“ или „Зависни од праћења“ у групи Ревизија формуле.
Траце Прецедентс
Када изаберете ову опцију, она прати ћелије које утичу на вредност активне ћелије. Црта плаву линију која показује које ћелије утичу на тренутну ћелију. Пречица за коришћење преседана праћења је Алт + Т У Т.
У примеру испод, плава стрелица показује ћелије које утичу на вредност Б6 су Б1:Б6 и А5. Као што можете видети у наставку, ћелија Б6 је такође део формуле, што је чини кружном референцом и доводи до тога да формула враћа „0“ као резултат.
Ово се лако може поправити заменом Б6 са Б5 у аргументу СУМ: =СУМ(Б1:Б5).
Траце Депендентс
Функција зависних трагова прати ћелије које зависе од изабране ћелије. Ова карактеристика црта плаву линију која означава на које ћелије утиче изабрана ћелија. То значи да приказује које ћелије садрже формуле које упућују на активну ћелију. Пречица за коришћење зависних је Алт + Т У Д.
У следећем примеру, на ћелију Д3 утиче Б4. Његова вредност зависи од Б4 да би произвела резултате. Дакле, зависна од трага повлачи плаву линију од Б4 до Д3, што указује да је Д3 зависан од Б4.
Намерно коришћење кружних референци у Екцел-у
Намерно коришћење кружних референци се не препоручује, али могу постојати неки ретки случајеви у којима вам је потребна кружна референца да бисте добили жељени резултат.
Хајде да то објаснимо користећи пример.
За почетак, омогућите „Итеративно израчунавање“ у радној свесци Екцел. Када омогућите итеративно израчунавање, можете почети да користите кружне референце у своју корист.
Претпоставимо да купујете кућу и желите да свом агенту дате провизију од 2% на укупну цену куће. Укупан трошак ће бити израчунат у ћелији Б6, а проценат провизије (провизија агента) се израчунава у ћелији Б4. Провизија се обрачунава од укупног трошка и укупан трошак укључује провизију. Пошто ћелије Б4 и Б6 зависе једна од друге, то ствара кружну референцу.
Унесите формулу за израчунавање укупне цене у ћелију Б6:
=СУМ(Б1:Б4)
Пошто укупни трошак укључује агентску накнаду, укључили смо Б4 у горњу формулу.
Да бисте израчунали агенцијску накнаду од 2%, убаците ову формулу у Б4:
=Б6*2%Сада формула у ћелији Б4 зависи од вредности Б6 за израчунавање 2% укупне накнаде, а формула у ћелији Б6 зависи од Б4 за израчунавање укупног трошка (укључујући накнаду за агенте), отуда и кружна референца.
Ако је итеративно израчунавање омогућено, Екцел вам неће дати упозорење или 0 у резултату. Уместо тога, резултат ћелија Б6 и Б4 ће се израчунати као што је приказано изнад.
Опција итеративних прорачуна је обично подразумевано онемогућена. Ако га нисте укључили и када унесете формулу у Б4 која ће створити кружну референцу. Екцел ће издати упозорење и када кликнете на „ОК“, приказаће се стрелица за праћење.
То је то. Ово је било све што треба да знате о кружним референцама у Екцел-у.