Докато работите с формули на Excel, може някой път да видите следното предупреждение.
Тази подкана ви казва, че във вашия работен лист има кръгова справка и това може да доведе до или неправилно изчисление по формулите. Той също така ви моли да решите този кръгов справочен проблем и да го сортирате.
В този урок ще разгледам всичко, което трябва да знаете за кръговата справка, както и как да намерите и премахнете кръгови препратки в Excel.
Така че нека започнем!
Какво е кръгова справка в Excel?
С прости думи, кръгова справка се случва, когато в крайна сметка имате формула в клетка - която сама по себе си използва клетката (в която е въведена) за изчислението.
Нека се опитам да обясня това с прост пример.
Да предположим, че имате набор от данни в клетка A1: A5 и използвате следната формула в клетка A6:
= SUM (A1: A6)
Това ще ви даде кръгово предупреждение.
Това е така, защото искате да сумирате стойностите в клетка A1: A6 и резултатът трябва да бъде в клетка A6.
Това създава цикъл, тъй като Excel просто продължава да добавя новата стойност в клетка A6, която продължава да се променя (следователно, кръгов референтен цикъл).
Как да намерите кръгови справки в Excel?
Докато подканата за предупреждение за кръгова справка е достатъчно любезна, за да ви каже, че съществува във вашия работен лист, тя не ви казва къде се случва и какви препратки към клетките го причиняват.
Така че, ако се опитвате да намерите и боравите с кръгови препратки в работния лист, трябва да знаете начин по някакъв начин да ги намерите.
По -долу са стъпките за намиране на кръгова справка в Excel:
- Активирайте работния лист, който има кръгова препратка
- Щракнете върху раздела Формули
- В групата за редактиране на формули щракнете върху падащата икона Проверка на грешки (малка стрелка надолу вдясно)
- Задръжте курсора на мишката върху опцията Кръгови справки. Той ще ви покаже клетката, която има кръгова справка в работния лист
- Кликнете върху адреса на клетката (който се показва) и той ще ви отведе до тази клетка в работния лист.
След като сте решили проблема, можете отново да следвате същите стъпки по -горе и той ще покаже повече препратки към клетки, които имат кръгова препратка. Ако няма такава, няма да видите препратка към клетка,
Друг бърз и лесен начин да намерите кръговата справка е като погледнете лентата на състоянието. В лявата му част той ще ви покаже текста Circular Reference заедно с адреса на клетката.
Когато работите с кръгови препратки, трябва да знаете няколко неща:
- В случай, че итеративното изчисление е активирано (обхванато по -късно в този урок), лентата на състоянието няма да показва адреса на кръговата референтна клетка
- В случай, че кръговата справка не е в активния лист (но в други листове в същата работна книга), тя ще показва само кръгова справка, но не и адреса на клетката
- В случай, че веднъж получите подкана за кръгова справка и го отхвърлите, той няма да покаже подканата отново следващия път.
- Ако отворите работна книга, която има кръгова справка, тя ще ви покаже подкана веднага щом работната книга се отвори.
Как да премахнете кръгова справка в Excel?
След като установите, че във вашия лист има кръгови препратки, е време да ги премахнете (освен ако не искате те да са там по някаква причина).
За съжаление, това не е толкова просто, колкото натискането на клавиша за изтриване. Тъй като те зависят от формулите и всяка формула е различна, трябва да анализирате това за всеки отделен случай.
В случай, че става въпрос само за това, че препратката към клетката причинява проблема по погрешка, можете просто да коригирате, като коригирате препратката.
Но понякога не е толкова просто.
Кръговите препратки също могат да бъдат причинени въз основа на множество клетки, които се подават една в друга на много нива.
Нека ви покажа пример.
По-долу има кръгова справка в клетка C6, но това не е просто прост случай на самореференция. Това е на много нива, където клетките, които използва в изчисленията, също се препращат помежду си.
- Формулите в клетка A6 е = SUM (A1: A5)+C6
- Формулата е клетка C1 е = A6*0.1
- Формулата в клетка C6 е = A6+C1
В горния пример резултатът в клетка С6 зависи от стойностите в клетка А6 и С1, които от своя страна зависят от клетка С6 (като по този начин причинява кръгова грешка при справка)
И отново, избрах наистина прост пример само за демонстрационни цели. В действителност те могат да бъдат доста трудни за разбиране и може би далеч в един и същ работен лист или дори разпръснати в множество работни листове.
В такъв случай има един начин да се идентифицират клетките, които причиняват кръгова справка, и след това да се третират тези.
Това е чрез опцията Trace Precedents.
По -долу са стъпките за използване на прецеденти за проследяване за намиране на клетки, които се подават към клетката, която има кръгова референция:
- Изберете клетката, която има кръгова препратка
- Щракнете върху раздела Формули
- Кликнете върху Trace Precedents
Горните стъпки ще ви покажат сини стрелки, които ще ви кажат кои клетки се подават във формулата в избраната клетка. По този начин можете да проверите формулите и клетките и да се отървете от кръговата справка.
В случай, че работите със сложни финансови модели, може да е възможно тези прецеденти да са дълбоки на няколко нива.
Това работи добре, ако имате всички формули, отнасящи се до клетки в един и същ работен лист. Ако е в няколко работни листа, този метод не е ефективен.
Как да активирате/деактивирате итеративни изчисления в Excel
Когато имате кръгова справка в клетка, първо получавате подканата за предупреждение, както е показано по -долу, и ако затворите този диалогов прозорец, той ще ви даде 0 като резултат в клетката.
Това е така, защото когато има кръгова справка, това е безкраен цикъл и Excel не иска да се захване с него. Така че връща 0.
Но в някои случаи всъщност може да искате кръговата препратка да бъде активна и да направите няколко повторения. В такъв случай вместо безкраен цикъл и можете да решите колко пъти цикълът да се изпълнява.
Това се казва итеративно изчисление в Excel.
По -долу са стъпките за активиране и конфигуриране на итеративни изчисления в Excel:
- Щракнете върху раздела Файл
- Кликнете върху Опции. Това ще отвори диалоговия прозорец Опции на Excel
- Изберете Формула в левия прозорец
- В секцията Опции за изчисление поставете отметка в квадратчето „Активиране на итеративно изчисление“. Тук можете да зададете максималните итерации и максималната стойност на промяната
Това е! Горните стъпки биха позволили итеративно изчисление в Excel.
Позволете ми също така бързо да обясня двата варианта в итеративното изчисление:
- Максимални повторения: Това е максималният брой пъти, които искате Excel да изчисли, преди да ви даде крайния резултат. Така че, ако зададете това като 100, Excel ще изпълни цикъла 100 пъти, преди да ви даде крайния резултат.
- Максимална промяна: Това е максималната промяна, която ако не бъде постигната между итерациите, изчислението ще бъде спряно. По подразбиране стойността е .001. Колкото по -ниска е тази стойност, толкова по -точен ще бъде резултатът.
Не забравяйте, че колкото повече пъти се изпълняват итерациите, толкова повече време и ресурси са необходими на Excel да го направи. В случай, че поддържате максималните итерации високи, това може да доведе до забавяне или срив на вашия Excel.
Забележка: Когато итеративните изчисления са активирани, Excel няма да ви покаже подканата за предупреждение за кръгова справка и сега също ще го покаже в лентата на състоянието.Умишлено използване на циркулярни справки
В повечето случаи наличието на кръгова справка във вашия работен лист би било грешка. И затова Excel ви показва подкана, която гласи - „Опитайте да премахнете или промените тези препратки или да преместите формулите в различни клетки.“
Но може да има някои специфични случаи, когато имате нужда от кръгова справка, за да получите желания резултат.
Един такъв конкретен случай, за който вече писах, е получаването на отпечатъка за време в клетка в клетка в Excel.
Да предположим например, че искате да създадете формула, така че всеки запис да бъде направен в клетка в колона А, времевата марка да се появи в колона В (както е показано по -долу):
Въпреки че можете лесно да вмъкнете времева отметка, като използвате формулата по -долу:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Проблемът с горната формула е, че тя ще актуализира всички часови отметки веднага щом се направи някаква промяна в работния лист или ако работният лист се отвори отново (тъй като формулата NOW е променлива)
За да заобиколите този проблем, можете да използвате метод на кръгова справка. Използвайте същата формула, но активирайте итеративно изчисление.
Има и някои други случаи, при които е желателно да се използва кръгова справка (можете да намерите един пример тук).
Забележка: Въпреки че може да има някои случаи, в които можете да използвате кръгова справка, намирам за най -добре да избягвам използването й. Кръговите препратки също могат да повлияят на работата на вашата работна книга и да я забавят. В редки случаи, когато имате нужда от него, винаги предпочитам да използвам VBA кодове, за да свърша работата.Надявам се, че сте намерили този урок за полезен!
Други уроци по Excel, които може да ви бъдат полезни:
- #REF! Грешка в Excel; Как да поправя грешката при справка!
- Обработка на грешки в Excel VBA
- Използвайте IFERROR с VLOOKUP, за да се отървете от #N/A грешки
- Как да препратите към друг лист или работна книга в Excel (с примери)
- Абсолютни, относителни и смесени клетъчни препратки в Excel
