Hur man hittar cirkulära referenser i Excel

När du arbetar med kan du ibland stöta på vad som kallas en cirkulär referens. Det är när en formel i en cell hänvisar tillbaka till sig själv, vilket orsakar en oändlig loop i beräkningsprocessen.

För att hitta cirkulära referenser i Excel, använd verktyget "Felkontroll" under fliken "Formler" för att identifiera celler med ett cirkulärt beroende.

Den här artikeln visar hur du hittar cirkulära referenser i Excel och fixar dem. Du kommer också att lära dig hur du förhindrar dem när du arbetar med stora eller komplexa kalkylblad.

Innehållsförteckning

• Vad är cirkulära referenser
• Fönstret Circular Reference Felmeddelande
• Hur man hittar cirkulära referenser i Excel
• Hur man hittar cirkulära referenser med Excel-statusfältet
• Hur man hittar dolda cirkulära referenser i Excel
  • Hur man använder det spårberoende verktyget
  • Verktyget Spåra prejudikat
• 2 sätt hur man tar bort cirkulära referenser i Excel
  • 2. Använd alternativa formler
• 5 tips för att förhindra cirkulära referenser
  • 1. Var försiktig när du väljer intervall
  • 2. Planera dina formler noggrant
  • 3. Använd alternativa formler
  • 4. Dela upp komplexa formler i mindre delar
  • 5. Åtgärda problem allt eftersom
• Hur aktiverar man iterativ beräkning och varför bry sig?

Vad är cirkulära referenser

Cirkulära referenser förekommer i Excel när en formelberäkning i en cell beror på cellens eget värde. Detta skapar en loop och leder till felaktiga eller oförutsägbara resultat.

Detta är lättare att förstå med ett enkelt exempel.

Anta att du har två tal i cellerna A1 och B1. Du vill använda en formel i cell A3 som adderar de två talen tillsammans. Formeln i cell A3 ska se ut så här:

=A1 + Bl

Men om du gör ett misstag och tar med C1 i beräkningarna har du nu en cirkulär referens:

=A1 + B1 + Cl

Detta kan vara ett uppenbart problem att undvika när man har att göra med två celler. Men när du arbetar med stora cellområden, blir misstag mer sannolikt.

Tack och lov är Excel designat för att hantera sådana situationer genom att visa ett varningsmeddelande när en cirkulär referens upptäcks.

I vissa fall kan du dock stöta på en cirkulär referens utan att få någon varning. Därför är det viktigt att veta hur man hittar och löser dessa problem manuellt.

Fönstret Circular Reference Felmeddelande

Som standard övervakar Excel efter cirkulära referenser när du anger formler i celler. När du trycker på enter i en cell med ett problem kommer ett popup-fönster att visa felmeddelandet med cirkulär referens.

Detta är den förklarande texten:

Det finns en eller flera cirkulära referenser där en formel refererar till sin egen cell antingen direkt eller indirekt. Detta kan få dem att beräkna felaktigt. Försök att ta bort eller ändra dessa referenser eller flytta formlerna till olika celler.

När du ser den här dialogrutan bör du klicka på "OK" och använda metoderna i den här artikeln för att åtgärda problemet.

Du behöver dock inte åtgärda felet omedelbart. Du kan fortsätta arbeta med kalkylarket med avsikten att lösa problemet senare.

I så fall måste du veta hur du hittar cirkulära referenser i Excel efter att du har stängt varningsrutan.

Hur man hittar cirkulära referenser i Excel

Excel har ett inbyggt felkontrollverktyg som hittar flera typer av fel, inklusive cirkulära referenser. Du kommer åt verktyget på fliken Formler.

Den här bilden visar platsen inom Formula Auditing-gruppen.

Nu vet du hur du hittar verktyget, här är de detaljerade stegen för att använda det:

1. Gå till fliken Formler.

1. Klicka på rullgardinsmenyn Felkontroll i gruppen Formelrevision.

2. Välj "Circular References" från rullgardinsmenyn.

3. Klicka på länken för att flytta markören till den första cellen med ett problem.

Bilden nedan visar att verktyget har hittat ett cirkulärt fel i cell G1. Den visade cellen är en hyperlänk.

När du klickar på länken flyttas markören till cellen. Detta är mycket användbart med en stor mängd data.

När du har löst den första cirkulära referensen kan du gå tillbaka till felkontrollverktyget för att hitta nästa på bladet.

Använd stegen i de senare avsnitten i den här artikeln för att åtgärda problemet och använd verktyget för att gå till nästa problem.

Om det inte finns fler cirkulära problem kommer menyalternativet "Cirkulära referenser" i rullgardinsmenyn att vara nedtonat. Du har inget mer arbete att göra.

Hur man hittar cirkulära referenser med Excel-statusfältet

Ett annat sätt att upptäcka en cirkulär referens är att titta på statusfältet längst ned i Excel-kalkylbladet.

När det finns ett eller flera problem i kalkylbladet visar statusfältet platsen för en problemcellreferens.

Till exempel, om det finns en cirkulär referens i cell C1 och det inte finns några andra problem med kalkylarket, kommer statusfältet att läsa: "Cirkulära referenser: C1".

Den här bilden visar var du kan se displayen.

Hur man hittar dolda cirkulära referenser i Excel

Dolda cirkulära referenser i är cirkulära referenser som inte omedelbart upptäcks av de inbyggda felfunktionerna. Med andra ord visar Excel inte standardvarningen.

De inträffar när en cell refererar till en annan cell indirekt, genom en serie mellanceller, och så småningom refererar tillbaka till den ursprungliga cellen.

Excel har två verktyg som hjälper dig att spåra relationer och undersöka dessa problem:

1. Verktyg för spårberoende

2. Verktyget Spåra prejudikat

Hur man använder det spårberoende verktyget

Erfarna Excel-användare drar fördel av verktyget Trace Dependents för att undersöka dessa problem och fixa cirkulära referenser. Genom att välja en eller flera celler och använda det här verktyget kommer Excel att visa de beroende cellerna.

Verktyget är tillgängligt i avsnittet Formelrevision på fliken Formler.

Följ dessa steg för att använda verktyget:

1. Klicka på den eller de celler som du vill spåra anhöriga för.

2. Gå till fliken "Formler" i Excel-bandet.

3. Klicka på knappen "Spåra beroende" i gruppen "Formelrevision".

4. Granska spårningspilen eller pilarna som visar beroenden.

Excel visar pilar från det aktiva cellvärdet till alla celler som är beroende av det. Den blå pilen i bilden nedan visar att cell D6 har ett beroende av cell B2.

Verktyget Spåra prejudikat

Prejudikat är motsatsen till anhöriga. Verktyget Spåra prejudikat låter dig välja en cell med en formel och se alla celler som den beror på, dvs prejudikat.

Verktyget är tillgängligt i avsnittet Formelrevision på fliken Formler.

Följ dessa steg för att använda verktyget:

1. Klicka på cellen som innehåller formeln du vill undersöka.

2. Gå till fliken Formel i Excel-bandet.

3. Klicka på knappen "Spåra prejudikat" i gruppen "Formelrevision".

4. Granska spårningspilarna som Excel visar.

De blå pilarna på bilden nedan visar att cell D6 är beroende av cellområdet A2:B5.

Att använda båda verktygen hjälper dig att hitta Excel-cirkulära referenser som inte är direkt uppenbara. Nästa steg är att åtgärda problemet i den valda cellen. Du kan sedan fortsätta att söka efter samma problem i andra celler.

2 sätt hur man tar bort cirkulära referenser i Excel

Det finns två huvudsakliga metoder för att lösa cirkulära referenser:

• bryta den cirkulära referensen

• använda alternativa formler

Låt oss förklara åt båda hållen.

1. Bryta cirkulärreferensen

Att bryta cirkulärreferensen i Excel är ett effektivt sätt att lösa problemet. Detta kräver att du identifierar cellerna som är involverade i den cirkulära referensen och ändrar deras formler därefter.

För att göra detta, följ stegen som beskrivits tidigare för att hitta celladressen.

Du bör sedan undersöka formeln i formelfältet. Leta efter en referens till själva cellen.

I vårt första exempel på en cirkulär referens inkluderade additionsformeln i cell C1 en referens till själva cellen:

=A1 + B1 + Cl

Detta kan åtgärdas genom att ta bort det sista tillägget. När det här problemet är löst går du vidare för att hitta andra cirkulära referenser och ta itu med dem.

2. Använd alternativa formler

Några av de vanligaste orsakerna till cirkulära referenser är vid summering eller medelvärdesberäkning av celler. Felet är att inkludera resultatcellen i formeln.

I vårt tidigare exempel använde formeln additionsoperatorer för att beräkna summan av ett cellintervall. Den alternativa metoden är att använda SUM Excel-funktionen.

För att fixa exemplet ersätter du formeln med:

=SUMMA(A1:B1)

På samma sätt kan du genomsnitta ett intervall med en formel som =(A1+B1)/2. Det är dock mindre sannolikt att funktionen AVERAGE leder till ett misstag.

=MEDEL(A1:B1)

5 tips för att förhindra cirkulära referenser

För att förhindra cirkulära referenser i Microsoft Excel är det viktigt att ha en tydlig förståelse för hur dina formler interagerar med varandra i ditt Excel-ark.

Genom att identifiera relationerna mellan celler och vara medveten om hur dessa kopplingar fungerar tillsammans kan du undvika att skapa cirkulära formler.

Här är våra fem bästa tips som hjälper dig att förhindra cirkulära referenser.

1. Var försiktig när du väljer intervall

Enligt min erfarenhet är en av de vanligaste orsakerna till cirkulära referenser när användare snabbt väljer intervall att inkludera i sina formler.

När du sveper vertikalt och/eller horisontellt för att gruppera celler är det lätt att välja en cell till som ligger intill rätt intervall. Om den cellen också är resultatcellen har du oavsiktligt skapat en cirkulär referens.

Detta misstag görs vanligtvis när man arbetar med stora uppsättningar data, som att köra chi-kvadrattest. Lösningen är att vara försiktig med ditt sortimentsval.

2. Planera dina formler noggrant

Innan du anger formler, tänk på logiken bakom dina beräkningar och hur de ska flöda över celler.

Du kan kartlägga beräkningarna i förväg med hjälp av diagramverktyg eller en whiteboard.

Detta kan hjälpa dig att identifiera eventuella loopar som kan leda till cirkulära referenser.

3. Använd alternativa formler

Vissa funktioner, som INDIREKTA eller OFFSET , kan oavsiktligt skapa cirkulära referenser när de används felaktigt.

Överväg att använda alternativa funktioner som ger liknande funktionalitet utan risk för cirkulära referenser.

4. Dela upp komplexa formler i mindre delar

Istället för att skapa en enda komplex formel med flera kapslade funktioner kan du överväga att dela upp dina beräkningar i mindre, mer hanterbara delar.

Detta gör inte bara din arbetsbok lättare att förstå och underhålla utan minskar också chanserna att införa en cirkulär referens.

5. Åtgärda problem allt eftersom

När du arbetar med stora eller komplicerade arbetsböcker är det också viktigt att övervaka för cirkulära referenser. Istället för att ignorera problem tills en stor uppsättning data och formler har sammanställts, är det bättre att åtgärda varje fel när det uppstår.

Du kan välja att ignorera ett cirkulärt referensvarningsmeddelande för en kort stund. Du bör dock använda funktionen Felkontroll för att komma ikapp med befintliga cirkulära referenser innan alltför lång tid.

Detta gör dem lättare att fixa och förhindrar deras effekter på din arbetsbok.

Hur aktiverar man iterativ beräkning och varför bry sig?

Du vill vanligtvis undvika cirkulära referenser i ditt arbetsblad. Det finns dock vissa scenarier där de är nödvändiga.

Till exempel måste vissa tekniska eller vetenskapliga beräkningar använda resultatet av en tidigare beräkning. Detta är känt som en iterativ lösning.

Varje slinga förfinar resultatet av en beräkning tills ett specifikt tröskelvärde eller mål uppnås.

I dessa fall är det viktigt att aktivera Iterativa beräkningar i Excel-inställningarna för att tillåta avsedda cirkulära referenser. Detta gör att programvaran kan upprepa beräkningarna ett visst antal gånger och konvergera till en lösning.

Följ dessa steg för att göra ett cirkulärt referensverk i din Excel-fil:

1. Klicka på fliken Arkiv.

2. Välj inställningar.

3. Välj kategorin Formler.

4. Under avsnittet "Beräkningsalternativ" markerar du rutan för "Aktivera iterativ beräkning".

5. Ändra siffran i rutan för maximal iteration eller lämna som standard på 100.

6. Använd den maximala ändringsrutan för att minska iterationerna enligt dina krav.