Как да намерите кръгови препратки в Excel по 4 начина

Видяхте ли грешката с кръгова препратка на лист в Excel и се чудите дали работният лист е повреден? Вярвай ми! Не сте първият, който се сблъсква с тази грешка в Excel. Стотици и хиляди потребители на Excel изпитват грешки в кръгови препратки всеки ден. Не се притеснявайте! Ето как да намерите кръгови препратки в Excel и да ги разрешите.

Тъй като сте запален потребител на Excel, рядко въвеждате едни и същи данни в много клетки. Вместо това импортирате набор от данни от източника и използвате препратка към клетка, за да създавате формули. Така създавате динамична електронна таблица.

Когато някой промени стойностите на клетките на препратките към клетките, работният лист се актуализира автоматично. Това се нарича автоматизация на анализа на данни и анализаторите на данни обичат да си играят с препратки към клетки.

Проблемът обаче възниква, когато умишлено или случайно въведете адреса на клетката, където в момента извършвате изчисление. Или, когато създавате много формули в целия работен лист, клетка, съдържаща формула, се появява индиректно в друга формула.

В такива и много други подобни ситуации получавате грешката на кръговата препратка в Excel. Прочетете, за да научите какво е кръгова препратка, причините за нея, защо да я избягвате и най-важното как да намерите кръгови препратки в Excel, за да се отървете от тези аномалии завинаги.

Excel открива грешка с кръгова препратка, когато види, че една или много функции на работния лист препращат обратно към адреса на клетката, където са създадени техните функции.

Може да ви звучи объркващо, така че помислете колко тежко може да е за приложението Excel. Накратко, никога не искате да поставите адреса на клетката във формула, където я създавате. Вижте следния пример за визуално пояснение:

Опитвате се да умножите 10 от клетката B1 и поставете резултата в клетка B1 себе си.

Сега, в горния сценарий, ако просто натиснете Добре, Excel ще затвори грешката и ще покаже нула като изчислен резултат. Това е просто директен пример.

Сега си представете, че има стотици формули в счетоводен работен лист, работен лист за финансово планиране, ведомост за заплати на служители, работен лист за плащания на доставчик и т.н.

Ще се доверите на грешните данни, изчислени от Excel поради кръговата референтна грешка и това от своя страна ще повлияе на вземането на бизнес или професионални решения.

Освен това, кръговото препращане може сериозно да намали скоростта на изчисленията в приложението Excel. В тежки случаи самата грешка може да причини изключително ниска производителност и на компютъра, ако не се отстрани незабавно.

Ако обаче работите върху усъвършенствани формули на Excel, където вашият модел на данни изисква междинни входове от кръговата препратка, за да продължите с дълга функция, може да се наложи да я използвате. Всъщност Excel ви позволява да използвате този метод на изчисление, когато е необходимо.

Например, в единици за производство на енергия, като вятърни мелници, трябва да изчислите рециклирана изходна енергия от част от входящата енергия. Като алтернатива, в химически завод инженерите използват изходния ствол като вход за същия или друг котел или съд за процес. Такива инженерни изчисления на високо ниво наистина изискват кръгови препратки за итеративни изчисления.

Ясно казано, изскачащият прозорец с кръгова препратка не е съобщение за грешка. Това е начин Excel да ви информира, че е срещнал кръгова препратка в една или много формули и това може да повлияе на неговия алгоритъм за изчисление и производителност.

За да избегнете щети, причинени от кръгови препратки във формулите на Excel, трябва да знаете кога се появяват и кога не. Намерете по-долу случаите:

• Във всеки отворен работен лист или работна книга създавате формула и въвеждате кръговата препратка за първи път.
• Вие или някой друг премахна всички кръгови препратки от файла на Excel и го запази. Сега отново вие или друг потребител въвеждате кръгова препратка в същия файл.
• Затворихте всички отворени работни книги. Сега отворете друга работна книга и след това въведете формула, съдържаща кръгова препратка.

• Току-що сте отворили работна книга, която преди това е съдържала една или много кръгови препратки. Ще видите предупреждението.
• След като Excel ви е показал веднъж предупредителното съобщение за кръгова препратка и сте щракнали Добре, вече няма да показва същия изскачащ прозорец в същата сесия, дори ако продължавате да създавате повече кръгови референтни формули.

Следователно трябва внимателно да потърсите горните случаи, за да уловите предупреждения за кръгови препратки и да ги разрешите, ако не търсите итеративни изчисления.

Ако искате да научите как да намирате кръгови препратки в Excel, трябва да разберете техните видове. Тъй като намирането и елиминирането на кръгови препратки в Excel не е автоматизирана задача. Намерете по-долу често срещаните типове на тази грешка:

• Предвидени кръгови препратки възникват, когато ги използвате умишлено за целите на итеративно изчисление. Например, трябва да въведете времеви печат в друга клетка, когато някой въведе стойност в референтна клетка, използвайки променливи и логически функции като IF, OR, SUMIF и др.

• Нежелано кръгово препращане, когато се опитвате да работите по формула на Excel по-бързо и препращате към клетката, съдържаща формула, погрешно. Например, трябва да приложите SUM функция от клетката A1 да се A9 и извлечете резултата в клетка A10. Но по погрешка въвеждате формулата по следния начин в клетка A10:

=SUM(A2:A10)

• Скритите кръгови препратки са тези, които не показват предупредителни съобщения, когато създавате формула или отваряте работния лист за първи път. Това се случва, защото някой е позволил итеративно изчисление на файла на Excel.

• Индиректните кръгови препратки също са популярни и се появяват, когато създавате нова формула в която и да е клетка, например клетка A1и тази нова формула ще използва друга функция в друга клетка, като клетка B1, който вече използва клетка A1 като справка.

Сега, след като разбрахте основите на кръговите препратки в Excel, нека разберем как да намерите кръгови препратки в Excel по-долу:

При условие, че функцията за итеративно изчисление е деактивирана, можете да видите следните известия за кръгови препратки в лентата на състоянието на Excel:

• Циркулярни препратки: C3 (или всяка друга препратка към клетка), когато сте на работен лист, съдържащ кръгова формула в работната книга.

• Вие само ще видите Циркулярни препратки ако сте на работен лист, който не съдържа никакви, но работната книга има няколко от тези кръгови формули.

Това е най-добрият начин да проверите за кръгови препратки в работна книга или работен лист. Ето стъпките, които трябва да знаете:

• Отворете работен лист, за който подозирате, че съдържа кръгови формули.
• Щракнете върху Формули меню на лентата на Excel.

• В Формулен одит група команди, щракнете върху Проверка на грешкипадащ списък.
• Задръжте курсора върху Циркулярни препратки опция, за да видите всички налични екземпляри във всички работни листове.
• Изберете всеки екземпляр и Excel автоматично ще посочи тези клетки.

Ако не намирате метода за проверка на грешки за достатъчно ефективен, за да откриете голям брой формули с кръгови препратки, можете да опитате тези стъпки, за да използвате метода за проследяване на формули:

• Изберете която и да е референтна клетка или клетката, съдържаща формула.
• Отидете на Формули в менюто на лентата на Excel и след това щракнете върху Проследяване на прецеденти.
• Excel ще маркира клетките, които влияят на резултата от формулата в текущата клетка със стрелки.

• Можете също да щракнете Проследяване на зависими лица за откриване на клетки, засегнати от стойността на избраната препратка към клетка.
• Ако Excel не показва никакви грешки, след като щракнете върху горните команди, няма кръгови препратки във вашия работен лист.
• Повторете стъпките на всички работни листове, за да проверите цялата работна книга.

Този метод е особено важен, когато функциите за итеративно изчисление са активни в работния лист на Excel.

• Щракнете върху Файл раздел на лентата на Excel и след това отворете Опции на Excel от страничната лента, която се показва.
• На Опции на Excel диалогов прозорец, изберете Формули меню от страничната лента и след това премахнете отметката от квадратчето за Активиране на итеративно изчисление.

• Кликнете Добре за да запазите промените.
• Сега работната книга или работният лист автоматично ще ви покаже предупреждението за кръгова справка.
• Ако не се покаже незабавно, запазете работната книга, затворете я и я отворете отново, за да видите предупреждението.
• След като видите предупреждението, проверете Лента на състоянието на Excel за да знаете коя клетка е в кръговата препратка.

Сигурният начин за коригиране на една или много кръгови препратъчни грешки в Excel е чрез ръчно редактиране на всяка формула, която я съдържа. По този начин можете да разрешите както случайни, така и непреки кръгови препратки в Excel.

Друг лесен начин е да премахнете формулата от текущата клетка и да я поставите някъде другаде в рамките на същия работен лист.

Подобно на Excel, Google Sheets не позволява никакви кръгови препратки в една или повече формули, ако не сте активирали функцията за итеративно изчисление. Следователно Google Sheets веднага ще покаже #REF! грешка в клетката, която съдържа кръгова формула.

Следователно можете просто да изберете всички клетки, които съдържат #REF! предупреждение за намиране и разрешаване на кръгови препратки в Google Таблици.

И така, сега знаете всички тайни за това как да намерите кръгови препратки в Excel. Използвайте метода, който харесвате, и изхвърлете всички кръгови препратки, за да сте сигурни, че няма да се объркате от ненадеждни данни от отчет на Excel или математическо табло.

Следващата стъпка е най-добрите шаблони за календари на Excel за всяка година и на най-добрите бюджетни шаблони на Excel.