Едно от най-често срещаните предупреждения за грешки, които потребителите срещат в Excel, е „Кръговата препратка“. Хиляди потребители имат същия проблем и той се появява, когато формула препраща обратно към собствената си клетка директно или индиректно, причинявайки безкраен цикъл от изчисления.
Например, имате две стойности в клетки B1 и B2. Когато формулата =B1+B2 се въведе в B2, тя създава кръгова препратка; формулата в B2 многократно се преизчислява, защото всеки път, когато изчислява, стойността на B2 се променя.
Повечето кръгови препратки са неволни грешки; Excel ще ви предупреди за тях. Има обаче и предвидени кръгови препратки, които се използват за извършване на итеративни изчисления. Непредвидените кръгови препратки във вашия работен лист може да доведат до неправилно изчисляване на формулата ви.
Ето защо в тази статия ще обясним всичко, което трябва да знаете за кръговите препратки, както и как да намерите, коригирате, премахнете и използвате кръгови препратки в Excel.
Как да намерите и боравите с кръгова препратка в Excel
Когато работим с Excel, понякога се сблъскваме с грешки с кръгови препратки, които се случват, когато въведете формула, която включва клетката, в която се намира формулата ви. По принцип това се случва, когато вашата формула се опитва да се изчисли сама.
Например, имате колона с числа в клетка A1:A4 и използвате функцията SUM (=SUM(A1:A5)) в клетка A5. Клетката A5 директно се отнася до собствената си клетка, което е погрешно. Следователно ще получите следното кръгово предупреждение:
След като получите горното предупредително съобщение, можете да щракнете върху бутона „Помощ“, за да научите повече за грешката, или да затворите прозореца със съобщение за грешка, като щракнете върху бутона „OK“ или „X“ и получите „0“ като резултат.
Понякога кръговите референтни цикли могат да доведат до срив на вашето изчисление или да забавят производителността на работния ви лист. Циркулярното позоваване може също да доведе до редица други проблеми, които няма да бъдат очевидни веднага. Така че, най-добре е да избягвате тези.
Преки и косвени кръгови препратки
Циркулярните препратки могат да бъдат категоризирани в два типа: директни кръгови препратки и косвени кръгови препратки.
Директна справка
Директната кръгова препратка е доста проста. Съобщението за директна кръгова препратка се появява, когато формулата препраща директно към собствената си клетка.
В примера по-долу формулата в клетка A2 директно се отнася до нейната собствена клетка (A2).
След като се появи предупредителното съобщение, можете да щракнете върху „OK“, но това ще доведе само до „0“.
Непряка циркулярна справка
Непряка кръгова препратка в Excel възниква, когато стойност във формула се отнася обратно към собствената си клетка, но не директно. С други думи, кръговата препратка може да бъде образувана от две клетки, които се препращат една към друга.
Нека да обясним с този прост пример.
Сега стойността започва от A1, което има стойност 20.
След това клетка C3 се отнася до клетка A1.
Тогава клетка A5 се отнася до клетка C3.
Сега заменете стойността 20 в клетка A1 с формулата, както е показано по-долу. Всяка друга клетка зависи от клетка А1. Когато използвате препратка към която и да е друга предишна клетка с формула в A1, това ще предизвика предупреждение за кръгова препратка. Защото формулата в A1 се отнася до клетка A5, която се отнася до C3, а клетка C3 се отнася обратно до A1, оттук и кръговата препратка.
Когато щракнете върху „OK“, това води до стойност 0 в клетка A1 и Excel създава свързан ред, показващ прецедентите на проследяване и зависимите от проследяването, както е показано по-долу. Можем да използваме тази функция за лесно намиране и коригиране/премахване на кръгови препратки.
Как да активирате / деактивирате кръговите препратки в Excel
По подразбиране итеративните изчисления са изключени (деактивирани) в Excel. Итеративните изчисления са повтарящи се изчисления, докато не изпълни определено условие. Когато е деактивиран, Excel показва съобщение за кръгова справка и връща 0 като резултат.
Въпреки това, понякога са необходими кръгови препратки за изчисляване на цикъл. За да използвате кръгова справка, трябва да активирате итеративните изчисления във вашия Excel и това ще ви позволи да извършвате изчисленията си. Сега нека ви покажем как можете да активирате или деактивирате итеративните изчисления.
В Excel 2010, Excel 2013, Excel 2016, Excel 2019 и Microsoft 365 отидете на раздела „Файл“ в горния ляв ъгъл на Excel, след което щракнете върху „Опции“ в левия прозорец.
В прозореца Опции на Excel отидете на раздела „Формула“ и поставете отметка в квадратчето „Активиране на итеративно изчисление“ под секцията „Опции за изчисление“. След това щракнете върху „OK“, за да запазите промените.
Това ще позволи итеративно изчисление и по този начин ще позволи кръгова справка.
За да постигнете това в предишни версии на Excel, следвайте тези стъпки:
- В Excel 2007 щракнете върху бутона Office > Опции на Excel > Формули > Област за итерации.
- В Excel 2003 и по-стари версии трябва да отидете в Меню > Инструменти > Опции > Раздел Изчисление.
Максимални итерации и параметри за максимална промяна
След като активирате итеративните изчисления, можете да контролирате итеративните изчисления, като посочите две налични опции в секцията Активиране на итеративно изчисление, както е показано на екранната снимка по-долу.
- Максимални итерации – Това число указва колко пъти формулата трябва да бъде преизчислена, преди да ви даде крайния резултат. Стойността по подразбиране е 100. Ако я промените на „50“, Excel ще повтори изчисленията 50 пъти, преди да ви даде крайния резултат. Не забравяйте, че колкото по-голям е броят на повторенията, толкова повече ресурси и време са необходими за изчисляване.
- Максимална промяна – Определя максималната промяна между резултатите от изчисленията. Тази стойност определя точността на резултата. Колкото по-малко е числото, толкова по-точен резултат ще бъде резултатът и толкова повече време отнема изчисляването на работния лист.
Ако опцията за итеративни изчисления е активирана, няма да получавате никакво предупреждение, когато във вашия работен лист има кръгова препратка. Активирайте интерактивното изчисление само когато е абсолютно необходимо.
Намерете кръгова препратка в Excel
Да предположим, че имате голям набор от данни и сте получили предупреждение за кръгова справка, все пак ще трябва да разберете къде (в коя клетка) е възникнала грешката, за да я поправите. За да намерите кръгови препратки в Excel, следвайте тези стъпки:
Използване на инструмента за проверка на грешки
Първо отворете работния лист, където се е случила кръговата препратка. Отидете в раздела „Формула“, щракнете върху стрелката до инструмента „Проверка на грешки“. След това просто задръжте курсора на мишката върху опцията „Кръгови препратки“, Excel ще ви покаже списъка с всички клетки, които участват в кръговата препратка, както е показано по-долу.
Щракнете върху който и да е клетъчен адрес, който искате в списъка, и той ще ви отведе до този клетъчен адрес, за да разрешите проблема.
Използване на лентата на състоянието
Можете също да намерите кръговата препратка в лентата на състоянието. В лентата на състоянието на Excel той ще ви покаже последния адрес на клетка с кръгова препратка, като например „Кръгови препратки: B6“ (вижте екранната снимка по-долу).
Има някои неща, които трябва да знаете, когато работите с кръгова препратка:
- Лентата на състоянието няма да показва адреса на кръгова референтна клетка, когато опцията за итеративно изчисление е активирана, така че трябва да я деактивирате, преди да започнете да разглеждате работната книга за кръгови препратки.
- В случай, че кръговата препратка не е намерена в активния лист, лентата на състоянието показва само „Кръгови препратки“ без адрес на клетка.
- Ще получите подкана за кръгова справка само веднъж и след като щракнете върху „OK“, тя няма да покаже подканата отново следващия път.
- Ако работната ви книга има кръгови препратки, тя ще ви показва подканата всеки път, когато я отворите, докато не разрешите кръговата препратка или докато не включите итеративното изчисление.
Премахнете кръгова препратка в Excel
Намирането на циркулярни препратки е лесно, но коригирането им не е толкова лесно. За съжаление, в Excel няма опция, която да ви позволи да премахнете всички кръгови препратки наведнъж.
За да коригирате кръговите препратки, трябва да намерите всяка кръгова препратка поотделно и да се опитате да я модифицирате, да премахнете изцяло кръговата формула или да я замените с друга.
Понякога, в простите формули, всичко, което трябва да направите, е да коригирате отново параметрите на формулата, така че да не се отнася обратно към себе си. Например променете формулата в B6 на =SUM(B1:B5)*A5 (променете B6 на B5).
Той ще върне резултата от изчислението като „756“.
В случаите, когато е трудно да се намери кръгова препратка в Excel, можете да използвате функциите за прецеденти на проследяване и зависими от проследяване, за да я проследите обратно до източника и да я разрешите един по един. Стрелката показва кои клетки са засегнати от активната клетка.
Има два метода за проследяване, които могат да ви помогнат да изтриете кръгови препратки, като показват връзките между формули и клетки.
За да получите достъп до методите за проследяване, отидете на раздела „Формули“, след което щракнете върху „Прецеденти за проследяване“ или „Зависими от проследяване“ в групата „Одит на формули“.
Прецеденти за проследяване
Когато изберете тази опция, тя проследява обратно клетките, които влияят върху стойността на активната клетка. Начертава синя линия, показваща кои клетки засягат текущата клетка. Клавишът за бърз достъп за използване на прецеденти за проследяване е Alt + T U T.
В примера по-долу синята стрелка показва клетките, които влияят на стойността B6, са B1:B6 и A5. Както можете да видите по-долу, клетка B6 също е част от формулата, което я прави кръгова препратка и кара формулата да връща „0“ като резултат.
Това може лесно да се поправи чрез замяна на B6 с B5 в аргумента на SUM: =SUM(B1:B5).
Проследяващи зависими
Функцията зависими от проследяването проследява клетките, които са зависими от избраната клетка. Тази функция чертае синя линия, показваща кои клетки са засегнати от избраната клетка. Тоест, показва кои клетки съдържат формули, които препращат към активната клетка. Прекият клавиш за използване на зависими е Alt + T U D.
В следващия пример клетка D3 е засегната от B4. Стойността му зависи от B4, за да даде резултати. Следователно, зависимостта от трасето чертае синя линия от B4 до D3, което показва, че D3 зависи от B4.
Умишлено използване на кръгови препратки в Excel
Умишленото използване на кръгови препратки не се препоръчва, но може да има някои редки случаи, когато имате нужда от кръгова препратка, за да можете да получите желания резултат.
Нека обясним това с помощта на пример.
За начало активирайте „Итеративно изчисление“ във вашата работна книга на Excel. След като активирате итеративното изчисляване, можете да започнете да използвате кръгови препратки в своя полза.
Да предположим, че купувате къща и искате да дадете 2% комисионна от общата цена на къщата на вашия агент. Общата цена ще бъде изчислена в клетка B6, а процентът на комисионата (таксата на агента) се изчислява в B4. Комисионната се изчислява от общите разходи и общата цена включва комисионната. Тъй като клетките B4 и B6 зависят една от друга, това създава кръгова препратка.
Въведете формулата за изчисляване на общата цена в клетка B6:
=SUM(B1:B4)
Тъй като общите разходи включват таксата на агента, ние включихме B4 в горната формула.
За да изчислите таксата на агента от 2%, поставете тази формула в B4:
=B6*2%Сега формулата в клетка B4 зависи от стойността на B6 за изчисляване на 2% от общата такса, а формулата в B6 зависи от B4 за изчисляване на общите разходи (включително таксата на агента), оттук и кръговата препратка.
Ако итеративното изчисление е активирано, Excel няма да ви даде предупреждение или 0 в резултата. Вместо това, резултатът от клетки B6 и B4 ще бъде изчислен, както е показано по-горе.
Опцията за итеративни изчисления обикновено е деактивирана по подразбиране. Ако не сте го включили и когато въведете формулата в B4, което ще създаде кръгова препратка. Excel ще издаде предупреждението и когато щракнете върху „OK“, ще се покаже стрелката за проследяване.
Това е. Това беше всичко, което трябва да знаете за кръговите препратки в Excel.