En av de vanligaste felvarningarna som användare stöter på i Excel är "Circular Reference". Tusentals användare har samma problem, och det uppstår när en formel refererar tillbaka till sin egen cell direkt eller indirekt, vilket orsakar en oändlig loop av beräkningar.
Till exempel har du två värden i cellerna B1 och B2. När formeln =B1+B2 skrivs in i B2, skapar den en cirkulär referens; formeln i B2 räknar om sig själv upprepade gånger eftersom varje gång den beräknar har B2-värdet ändrats.
De flesta cirkulära referenser är oavsiktliga misstag; Excel kommer att varna dig om dessa. Men det finns också avsedda cirkulära referenser, som används för att göra iterativa beräkningar. Oavsiktliga cirkulära referenser i ditt kalkylblad kan göra att din formel beräknas felaktigt.
Därför kommer vi i den här artikeln att förklara allt du behöver veta om cirkulära referenser och hur du hittar, fixar, tar bort och använder cirkulära referenser i Excel.
Hur man hittar och hanterar cirkulär referens i Excel
När vi arbetar med Excel stöter vi ibland på cirkulära referensfel som inträffar när du anger en formel som innehåller cellen där din formel finns. I grund och botten händer det när din formel försöker beräkna sig själv.
Du har till exempel en kolumn med tal i cell A1:A4 och du använder SUMMA-funktionen (=SUMMA(A1:A5)) i cell A5. Cellen A5 refererar direkt till sin egen cell, vilket är fel. Därför kommer du att få följande cirkulär referensvarning:
När du har fått varningsmeddelandet ovan kan du klicka på knappen "Hjälp" för att veta mer om felet, eller stänga felmeddelandefönstret genom att klicka på antingen "OK" eller "X" och få "0" som resultat.
Ibland kan cirkulära referensslingor få din beräkning att krascha eller sakta ner ditt arbetsblads prestanda. Cirkulärhänvisning kan också leda till ett antal andra problem, som inte kommer att bli uppenbara omedelbart. Så det är bäst att undvika dessa.
Direkta och indirekta cirkulära referenser
Cirkulärreferenserna kan kategoriseras i två typer: direkta cirkulära referenser och indirekta cirkulära referenser.
Direkt referens
En direkt cirkulär referens är ganska enkel. Varningsmeddelandet med direkt cirkulär referens dyker upp när formeln hänvisar tillbaka till sin egen cell direkt.
I exemplet nedan hänvisar formeln i cell A2 direkt till sin egen cell (A2).
När varningsmeddelandet dyker upp kan du klicka på "OK", men det kommer bara att resultera i "0".
Indirekt cirkulär referens
En indirekt cirkulär referens i Excel uppstår när ett värde i en formel refererar tillbaka till sin egen cell, men inte direkt. Med andra ord kan cirkulär referens bildas av två celler som refererar till varandra.
Låt oss förklara med detta enkla exempel.
Nu börjar värdet från A1 som har värdet 20.
Därefter hänvisar cell C3 till cell A1.
Sedan hänvisar cell A5 till cell C3.
Ersätt nu värdet 20 i cell A1 med formeln som visas nedan. Varannan cell är beroende av cell A1. När du använder en referens till någon annan tidigare formelcell i A1 kommer det att orsaka en cirkulär referensvarning. Eftersom formeln i A1 hänvisar till cell A5, som hänvisar till C3, och cell C3 hänvisar tillbaka till A1, därav den cirkulära hänvisningen.
När du klickar på "OK" resulterar det i ett värde på 0 i cell A1 och Excel skapar en länkad rad som visar spårningsprecedens och spårberoende som visas nedan. Vi kan använda den här funktionen för att enkelt hitta och fixa/ta bort cirkulära referenser.
Hur man aktiverar/avaktiverar cirkulära referenser i Excel
Som standard är iterativa beräkningar avstängda (inaktiverade) i Excel. Iterativa beräkningar är repetitiva beräkningar tills den uppfyller ett specifikt villkor. När det är inaktiverat visar Excel ett cirkulär referensmeddelande och returnerar en 0 som resultat.
Men ibland behövs cirkulära referenser för att beräkna en slinga. För att använda cirkulär referens måste du aktivera iterativa beräkningar i din Excel så att du kan utföra dina beräkningar. Låt oss nu visa dig hur du kan aktivera eller inaktivera iterativa beräkningar.
I Excel 2010, Excel 2013, Excel 2016, Excel 2019 och Microsoft 365, gå till fliken "Arkiv" i det övre vänstra hörnet av Excel och klicka sedan på "Alternativ" i den vänstra rutan.
I fönstret för Excel-alternativ, gå till fliken "Formel" och markera kryssrutan "Aktivera iterativ beräkning" under avsnittet "Beräkningsalternativ". Klicka sedan på "OK" för att spara ändringarna.
Detta kommer att möjliggöra iterativ beräkning och därmed möjliggöra cirkulär referens.
Följ dessa steg för att uppnå detta i tidigare versioner av Excel:
- I Excel 2007 klickar du på Office-knappen > Excel-alternativ > Formler > Iterationsområde.
- I Excel 2003 och tidigare versioner måste du gå till Meny > Verktyg > Alternativ > Fliken Beräkning.
Maximala iterationer och maximala ändringsparametrar
När du har aktiverat iterativa beräkningar kan du styra de iterativa beräkningarna genom att ange två tillgängliga alternativ under avsnittet Aktivera iterativ beräkning som visas i skärmdumpen nedan.
- Maximala iterationer – Den här siffran anger hur många gånger formeln ska räknas om innan du får det slutliga resultatet. Standardvärdet är 100. Om du ändrar det till '50' kommer Excel att upprepa beräkningarna 50 gånger innan du ger dig det slutliga resultatet. Kom ihåg att ju fler iterationer, desto mer resurser och tid tar det att beräkna.
- Maximal förändring – Den bestämmer den maximala förändringen mellan beräkningsresultaten. Detta värde avgör resultatets noggrannhet. Ju mindre antal, desto mer exakt resultat skulle resultatet bli och desto längre tid tar det att beräkna kalkylbladet.
Om alternativet iterativa beräkningar är aktiverat kommer du inte att få någon varning när det finns en cirkulär referens i ditt kalkylblad. Aktivera endast interaktiv beräkning när det är absolut nödvändigt.
Hitta Circular Reference i Excel
Anta att du har en stor datamängd och du fick den cirkulära referensvarningen, måste du fortfarande ta reda på var (i vilken cell) felet har inträffat för att åtgärda det. Följ dessa steg för att hitta cirkulära referenser i Excel:
Använder felkontrollverktyget
Öppna först kalkylbladet där den cirkulära referensen har skett. Gå till fliken "Formel", klicka på pilen bredvid verktyget "Felkontroll". Håll sedan muspekaren över alternativet "Cirkulära referenser", Excel visar dig listan över alla celler som är involverade i den cirkulära referensen som visas nedan.
Klicka på vilken celladress du vill ha i listan och den tar dig till den celladressen för att lösa problemet.
Använder statusfältet
Du kan också hitta den cirkulära referensen i statusfältet. På Excels statusfält kommer den att visa dig den senaste celladressen med en cirkulär referens, till exempel "Circular References: B6" (se skärmdump nedan).
Det finns vissa saker du bör veta när du hanterar cirkulär referens:
- Statusfältet visar inte adressen till den cirkulära referenscellen när alternativet Iterativ beräkning är aktiverat, så du måste inaktivera det innan du börjar titta i arbetsboken efter cirkulära referenser.
- Om cirkulär referens inte hittas i det aktiva bladet, visar statusfältet endast "Cirkulära referenser" utan celladress.
- Du kommer bara att få en cirkulär referensuppmaning en gång och efter att du klickat på "OK" kommer den inte att visa uppmaningen igen nästa gång.
- Om din arbetsbok har cirkulära referenser kommer den att visa dig uppmaningen varje gång du öppnar den tills du löser den cirkulära referensen eller tills du aktiverar iterativ beräkning.
Ta bort en cirkulär referens i Excel
Det är lätt att hitta cirkulära referenser men att fixa det är inte så enkelt. Tyvärr finns det inget alternativ i Excel som låter dig ta bort alla cirkulära referenser på en gång.
För att fixa cirkulära referenser måste du hitta varje cirkulär referens individuellt och försöka modifiera den, ta bort den cirkulära formeln helt eller ersätta den med en annan.
Ibland, i enkla formler, är allt du behöver göra att justera parametrarna för formeln så att den inte hänvisar tillbaka till sig själv. Ändra till exempel formeln i B6 till =SUMMA(B1:B5)*A5 (ändrar B6 till B5).
Det kommer att returnera resultatet av beräkningen som "756".
I de fall då en Excel-cirkulärreferens är svår att hitta kan du använda funktionerna Spåra prejudikat och Spårberoende för att spåra det tillbaka till källan och lösa det en efter en. Pilen visar vilka celler som påverkas av den aktiva cellen.
Det finns två spårningsmetoder som kan hjälpa dig att ta bort cirkulära referenser genom att visa sambanden mellan formler och celler.
För att komma åt spårningsmetoderna, gå till fliken "Formler" och klicka sedan på antingen "Spåra prejudikat" eller "Spåra beroende" i gruppen Formelrevision.
Spåra prejudikat
När du väljer det här alternativet spårar det tillbaka de celler som påverkar den aktiva cellens värde. Den ritar en blå linje som indikerar vilka celler som påverkar den aktuella cellen. Genvägstangenten för att använda spårprejudikat är Alt + T U T.
I exemplet nedan visar den blå pilen de celler som påverkar B6-värdet är B1:B6 och A5. Som du kan se nedan är cell B6 också en del av formeln, vilket gör den till en cirkulär referens och gör att formeln returnerar "0" som resultat.
Detta kan enkelt fixas genom att ersätta B6 med B5 i SUM:s argument: =SUM(B1:B5).
Spårberoende
Funktionen spårberoende spårar cellerna som är beroende av den valda cellen. Den här funktionen ritar en blå linje som anger vilka celler som påverkas av den valda cellen. Det vill säga, den visar vilka celler som innehåller formler som refererar till den aktiva cellen. Kortkommandot för att använda anhöriga är Alt + T U D.
I följande exempel påverkas cell D3 av B4. Det är beroende av B4 för dess värde för att ge resultat. Följaktligen drar spårberoende en blå linje från B4 till D3, vilket indikerar att D3 är beroende av B4.
Medvetet använda cirkulära referenser i Excel
Att använda cirkulära referenser medvetet rekommenderas inte, men det kan finnas några sällsynta fall där du behöver en cirkulär referens så att du kan få den utdata du vill ha.
Låt oss förklara det med ett exempel.
Till att börja med, aktivera "Iterativ beräkning" i din Excel-arbetsbok. När du har aktiverat Iterativ beräkning kan du börja använda cirkulära referenser till din fördel.
Låt oss anta att du köper ett hus och att du vill ge en 2% provision på den totala kostnaden för huset till din agent. Den totala kostnaden kommer att beräknas i cell B6 och provisionsprocenten (ombudsavgift) beräknas i B4. Provisionen beräknas från den totala kostnaden och den totala kostnaden inkluderar provisionen. Eftersom cellerna B4 och B6 är beroende av varandra skapar det en cirkulär referens.
Ange formeln för att beräkna den totala kostnaden i cell B6:
=SUMMA(B1:B4)
Eftersom den totala kostnaden inkluderar agentavgiften inkluderade vi B4 i formeln ovan.
För att beräkna agentavgiften på 2 %, infoga denna formel i B4:
=B6*2%Nu beror formeln i cell B4 på värdet av B6 för att beräkna 2% av den totala avgiften och formeln i B6 beror på B4 för att beräkna den totala kostnaden (inklusive agentavgift), därav cirkulärreferensen.
Om den iterativa beräkningen är aktiverad kommer Excel inte att ge dig en varning eller en nolla i resultatet. Istället kommer resultatet av cellerna B6 och B4 att beräknas enligt ovan.
Alternativet iterativa beräkningar är vanligtvis inaktiverat som standard. Om du inte slog på det och när du anger formeln i B4 som kommer att skapa en cirkulär referens. Excel kommer att utfärda varningen och när du klickar på "OK" kommer spårningspilen att visas.
Det är allt. Detta var allt du behöver veta om cirkulära referenser i Excel.