Как да намерите кръгови препратки в Excel, за да избегнете грешни данни

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

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

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

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

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

Какво е кръгова препратка в Excel?

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

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

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

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

Какво е кръгова препратка в Excel

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

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

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

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

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

Кога виждате грешката с кръгова препратка в Excel?

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

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

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

Кога виждате грешката с кръгова препратка в Excel

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

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

Видове кръгови препратки в Excel

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

Пример за използване на кръгова препратка

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

Неволна кръгова препратка в Excel

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

=SUM(A2:A10)

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

Пример за непряка кръгова препратка

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

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

Как да намерите кръгови препратки в Excel: От лентата на състоянието

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

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

Показване на кръгови препратки в лентата на състоянието на Excel

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

Как да намерите кръгови препратки в Excel: Използване на проверка за грешки

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

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

Използвайте проверка за грешки за Как да намерите кръгови препратки в Excel

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

Как да намерите кръгови препратки в Excel: От проследяване на формули

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

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

Използвайте проследяване на формула за Как да намерите кръгови препратки в Excel

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

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

Как да намерите кръгови препратки в Excel: Използване на опции на Excel

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

Как да деактивирате итеративно изчисление в Excel

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

Как да разрешите кръгови препратки в Excel

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

Как да разрешите кръгови препратки в Excel

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

Как да намерите кръгови препратки в Google Таблици (бонус метод)

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

Google Таблици Как да намерите кръгови препратки

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

Excel Circular References: Последни думи

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

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