När du arbetar med Excel -formler kan du ibland se följande varningsmeddelande.
Denna prompt berättar att det finns en cirkulär referens i ditt kalkylblad och detta kan leda till eller en felaktig beräkning av formlerna. Det ber dig också att ta itu med denna cirkulära referensfråga och sortera den.
I denna handledning kommer jag att täcka allt du behöver veta om cirkulär referens, samt hur man hittar och tar bort cirkulära referenser i Excel.
Så låt oss komma igång!
Vad är cirkulärreferens i Excel?
I enkla ord sker en cirkulär referens när du har en formel i en cell - som i sig använder cellen (där den har angetts) för beräkningen.
Låt mig försöka förklara detta med ett enkelt exempel.
Antag att du har datauppsättningen i cell A1: A5 och att du använder nedanstående formel i cell A6:
= SUMMA (A1: A6)
Detta ger dig en cirkulär referensvarning.
Detta beror på att du vill summera värdena i cell A1: A6, och resultatet ska vara i cell A6.
Detta skapar en loop eftersom Excel bara fortsätter att lägga till det nya värdet i cell A6, vilket fortsätter att förändras (därmed en cirkulär referensslinga).
Hur hittar jag cirkulära referenser i Excel?
Även om varningsmeddelandet för cirkulär referens är vänligt nog att berätta att det finns i ditt kalkylblad, berättar det inte var det händer och vilka cellreferenser som orsakar det.
Så om du försöker hitta och hantera cirkulära referenser i kalkylbladet måste du veta ett sätt att på något sätt hitta dessa.
Nedan följer stegen för att hitta en cirkulär referens i Excel:
- Aktivera kalkylbladet som har cirkelreferensen
- Klicka på fliken Formler
- I gruppen Formelredigering klickar du på listrutan Felkontroll (liten nedåtpekande pil till höger)
- Håll muspekaren över alternativet Cirkulära referenser. Det visar dig cellen som har en cirkulär referens i kalkylbladet
- Klicka på celladressen (som visas) och den tar dig till cellen i kalkylbladet.
När du har åtgärdat problemet kan du igen följa samma steg ovan och det kommer att visa fler cellreferenser som har cirkulärreferensen. Om det inte finns någon ser du ingen cellreferens,
Ett annat snabbt och enkelt sätt att hitta cirkulärreferensen är genom att titta på statusfältet. På den vänstra delen av den visar den texten Cirkulär referens tillsammans med celladressen.
Det finns några saker du behöver veta när du arbetar med cirkulära referenser:
- Om den iterativa beräkningen är aktiverad (täcks senare i den här självstudien) visar statusfältet inte den cirkulära referenscelladressen
- Om cirkulärreferensen inte finns i det aktiva arket (men i andra blad i samma arbetsbok) kommer den bara att visa cirkulärreferens och inte celladressen
- Om du får en cirkulär varningsmeddelande en gång och du avvisar den, kommer den inte att visas igen nästa gång.
- Om du öppnar en arbetsbok som har cirkulärreferensen kommer du att få uppmaningen så snart arbetsboken öppnas.
Hur tar jag bort en cirkulär referens i Excel?
När du har identifierat att det finns cirkulära referenser i ditt blad är det dags att ta bort dessa (om du inte vill att de ska finnas där av en anledning).
Tyvärr är det inte så enkelt som att trycka på raderingsknappen. Eftersom dessa är beroende av formler och varje formel är olika måste du analysera detta från fall till fall.
Om det bara handlar om att cellreferensen orsakar problemet av misstag kan du helt enkelt korrigera genom att justera referensen.
Men ibland är det inte så enkelt.
Cirkulär referens kan också orsakas baserat på flera celler som matas in i varandra på många nivåer.
Låt mig visa dig ett exempel.
Nedan finns en cirkulär referens i cell C6, men det är inte bara ett enkelt fall av självreferens. Det är flernivå där cellerna som det använder i beräkningarna också refererar till varandra.
- Formlerna i cell A6 är = SUMMA (A1: A5)+C6
- Formeln är cell C1 = A6*0,1
- Formeln i cell C6 är = A6+C1
I exemplet ovan är resultatet i cell C6 beroende av värdena i cell A6 och C1, som i sin tur är beroende av cell C6 (vilket orsakar cirkulärt referensfel)
Och återigen, jag har valt ett riktigt enkelt exempel bara för demoändamål. I verkligheten kan dessa vara ganska svåra att räkna ut och kanske långt borta i samma kalkylblad eller till och med spridda över flera kalkylblad.
I ett sådant fall finns det ett sätt att identifiera cellerna som orsakar cirkulär referens och sedan behandla dessa.
Det är genom att använda alternativet Trace Precedents.
Nedan följer stegen för att använda spårpreferenser för att hitta celler som matar till cellen som har cirkulär referens:
- Markera cellen som har cirkelreferensen
- Klicka på fliken Formler
- Klicka på Trace Precedents
Stegen ovan visar dig blå pilar som berättar vilka celler som matas in i formeln i den markerade cellen. På så sätt kan du inspektera formlerna och cellerna och bli av med cirkelreferensen.
Om du arbetar med komplexa finansiella modeller kan det vara möjligt att dessa prejudikat också går flera nivåer djupt.
Detta fungerar bra om du har alla formler som refererar till celler i samma kalkylblad. Om den finns i flera kalkylblad är den här metoden inte effektiv.
Så här aktiverar/inaktiverar du Iterativa beräkningar i Excel
När du har en cirkulär referens i en cell får du först varningsmeddelandet enligt nedan, och om du stänger den här dialogrutan kommer det att ge dig 0 som resultatet i cellen.
Detta beror på att när det finns en cirkulär referens är det en oändlig slinga och Excel vill inte fastna i den. Så det ger en 0.
Men i vissa fall kanske du faktiskt vill att cirkulärreferensen ska vara aktiv och göra ett par iterationer. I ett sådant fall, istället för en oändlig slinga, och du kan bestämma hur många gånger slingan ska köras.
Det här kallas iterativ beräkning i Excel.
Nedan följer stegen för att aktivera och konfigurera iterativa beräkningar i Excel:
- Klicka på fliken Arkiv
- Klicka på Alternativ. Detta öppnar dialogrutan Excel -alternativ
- Välj Formel i den vänstra rutan
- Markera kryssrutan ‘Aktivera iterativ beräkning’ i avsnittet Beräkningsalternativ. Här kan du ange de maximala iterationerna och det maximala ändringsvärdet
Det är allt! Stegen ovan skulle möjliggöra iterativ beräkning i Excel.
Låt mig också snabbt förklara de två alternativen i den iterativa beräkningen:
- Maximal iterationer: Detta är det maximala antalet gånger du vill att Excel ska beräkna innan du får det slutliga resultatet. Så om du anger detta som 100, kör Excel loop 100 gånger innan du får det slutliga resultatet.
- Maximal förändring: Detta är den maximala ändringen, som om beräkningen inte skulle uppnås mellan iterationerna skulle den stoppas. Som standard är värdet .001. Ju lägre detta värde desto mer exakt blir resultatet.
Kom ihåg att ju fler gånger iterationerna körs, desto mer tid och resurser tar det för Excel att göra det. Om du håller de maximala iterationerna höga kan det leda till att din Excel saktar ner eller kraschar.
Obs! När iterativa beräkningar är aktiverade visar Excel dig inte varningsmeddelandet för cirkulär referens och visar det nu också i statusfältet.Medvetet använda cirkulära referenser
I de flesta fall skulle förekomsten av cirkulär referens i ditt kalkylblad vara ett fel. Och det är därför Excel visar dig en prompt som säger: "Försök ta bort eller ändra dessa referenser eller flytta formlerna till olika celler."
Men det kan finnas vissa specifika fall där du behöver en cirkulär referens så att du kan få önskat resultat.
Ett sådant specifikt fall har jag redan skrivit om att det får tidsstämpeln i en cell i en cell i Excel.
Anta till exempel att du vill skapa en formel så att varje post görs i en cell i kolumn A, tidsstämpeln visas i kolumn B (som visas nedan):
Medan du enkelt kan infoga en tidsstämpel med följande formel:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Problemet med ovanstående formel är att det skulle uppdatera alla tidsstämplar så snart någon ändring görs i kalkylbladet eller om kalkylbladet öppnas igen (eftersom NU -formeln är flyktig)
För att komma runt problemet kan du använda en cirkulär referensmetod. Använd samma formel, men aktivera iterativ beräkning.
Det finns också några andra fall där man vill ha möjlighet att använda cirkulär referens (du kan hitta ett exempel här).
Obs! Även om det kan finnas vissa fall där du kan använda cirkulär referens, tycker jag det är bäst att undvika att använda den. Cirkulära referenser kan också ta en vägtull på arbetsbokens prestanda och kan göra det långsamt. I sällsynta fall där du behöver det, föredrar jag alltid att använda VBA -koder för att få jobbet gjort.Jag hoppas att du tyckte att denna handledning var användbar!
Andra Excel -självstudier kan vara användbara:
- #REF! Fel i Excel; Så här åtgärdar du referensfelet!
- Excel VBA -felhantering
- Använd IFERROR med VLOOKUP för att bli av med #N/A -fel
- Hur man hänvisar till ett annat blad eller en arbetsbok i Excel (med exempel)
- Absoluta, relativa och blandade cellreferenser i Excel
