Как да премахнете циркулярни препратки в Excel

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

Разбиране на референциите на Excel

Когато използвате Microsoft Excel, е обичайно формулата в една клетка да се позовава на друга клетка, за да се използват данните на тази клетка в изчисление. Обикновено към клетка се говори, като се използва редова буква и номер на колона, като A5 или B10.

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

Премахване на циркулярната справка в Excel

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

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

  1. Отворете електронната таблица

  2. Отворете електронната таблица на Microsoft Excel, която съдържа вашата кръгова справка.

  3. Използвайте менюто за формули

  4. Щракнете върху раздела "Формули" в менюто на лентата в горната част на прозореца на Excel. Щракнете върху малката стрелка до бутона "Проверка на грешки" в тази област. Преместете мишката върху „Циркулярни референции“ и ще се появи последната въведена циркулярна референция. Кликнете върху тази препратка, за да преминете към тази клетка в електронната таблица.

  5. Потърсете кръгова справка

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

  7. Проследяване на прецеденти с Excel

  8. Щракнете върху бутона "Проследяване на прецеденти" в областта "Одит на формула" в раздела "Формула" на менюто на лентата. Това ще създаде стрелка в електронната таблица от всяка клетка, спомената в посочената от вас клетка, до посочената клетка. Щракнете върху бутона „Проследяване на зависимите“, който ще създаде стрелки от посочената от вас клетка до всяка клетка, която се позовава на тази клетка.

  9. Следвайте референтната верига

  10. Щракнете двукратно върху всеки ред, който излиза от клетката с кръговата ви препратка, за да преминете към клетката в другия край на реда. Ако клетката, към която прескачате, има както началото на стрелка, която прилича на кръг, така и края на стрелка в нея, която прилича на глава на стрелка, вие сте намерили кръговата препратка. Променете или тази клетка, или препратката към тази клетка във вашата оригинална клетка, за да коригирате кръговата препратка.

  11. Намерете повече кръгове според нуждите

  12. Щракнете отново върху падащата стрелка до „Проверка на грешки“, за да видите дали се появява друга кръгова справка. Ако е така, повторете този процес, за да го изчистите и продължете, докато не се появят повече циркулярни препратки.

Итеративно изчисление в Excel

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

За да активирате итеративно изчисление в Excel, щракнете върху раздела „Файл“, след това върху „Опции“ и „Формули“. Под „Опции за изчисление“ поставете отметка в квадратчето „Активиране на итеративно изчисление“. Въведете максималния брой пъти, в които искате Excel да преизчисли формулата в полето „Максимални итерации“ и максималния размер на промяната, който ще позволите, преди Excel да трябва да преизчисли отново под „Максимална промяна“.

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