Hur man hittar cirkulära referenser i Excel på fyra sätt

Såg du det cirkulära referensfelet på ett Excel-ark och undrar om arbetsbladet är trasigt? Lita på mig! Du är inte den första som möter detta Excel-fel. Hundra och tusentals Excel-användare upplever cirkulära referensfel varje dag. Oroa dig inte! Så här hittar du cirkulära referenser i Excel och löser dem.

Som en ivrig Excel-användare skriver du sällan samma data i många celler. Istället importerar du en datamängd från källan och använder cellreferenser för att skapa formler. Således skapar du ett dynamiskt kalkylblad.

När någon ändrar cellvärdena för cellreferenserna uppdateras kalkylbladet automatiskt. Detta kallas dataanalysautomation och dataanalytiker älskar att leka med cellreferenser.

Men problemet uppstår när du avsiktligt eller av misstag skriver in den celladress där du för närvarande utför en beräkning. Eller, när du skapar många formler i kalkylbladet, visas en cell som innehåller en formel indirekt i en annan formel.

I sådana och många andra liknande situationer får du det cirkulära referensfelet i Excel. Läs vidare för att lära dig vad cirkulär referens är, dess skäl, varför du bör undvika det, och viktigast av allt hur du hittar cirkulära referenser i Excel för att bli av med dessa anomalier för gott.

Excel stöter på ett cirkulärt referensfel när det ser att en eller flera funktioner i kalkylbladet refererar tillbaka till celladressen där deras funktioner skapades.

Det kan låta förvirrande för dig, så tänk på hur allvarligt det kan vara för Excel-appen. Kort sagt, du vill aldrig sätta celladressen i en formel där du skapar den. Titta på följande exempel för ett visuellt förtydligande:

Du försöker föröka dig 10 från cellen B1 och lägg resultatet i cellen B1 sig.

Nu, i ovanstående scenario, om du bara trycker på OK, stänger Excel felet och visar noll som det beräknade resultatet. Detta är bara ett enkelt exempel.

Föreställ dig nu att det finns hundratals formler i ett kalkylblad för bokföring, kalkylblad för ekonomiplanering, lönelista för anställda, kalkylblad för leverantörsbetalning och så vidare.

Du kommer att lita på de felaktiga data som beräknas av Excel på grund av det cirkulära referensfelet och det kommer i sin tur att påverka ditt företags eller professionella beslutsfattande.

Dessutom kan cirkulär hänvisning kraftigt minska hastigheten för beräkningar i Excel-appen. I allvarliga fall kan själva felet orsaka extremt långsam prestanda även på datorn om det inte åtgärdas omedelbart.

Men om du arbetar med avancerade Excel-formler där din datamodell kräver intermediära indata från den cirkulära referensen för att fortsätta med en lång funktion, kan du behöva använda den. Faktum är att Excel låter dig använda den här beräkningsmetoden när det behövs.

Till exempel, i energiproduktionsenheter, som väderkvarnar, måste du beräkna återvunnen utgående energi från en bråkdel av inmatad energi. Alternativt, i en kemisk fabrik, använder ingenjörer utmatningsstammen som insats för samma eller en annan panna eller processkärl. Sådana tekniska beräkningar på hög nivå kräver verkligen cirkulära referenser för iterativa beräkningar.

Uppenbarligen är popup-fönstret med cirkulär referens inte ett felmeddelande. Det är ett sätt för Excel att informera dig om att den stötte på cirkulär referens i en eller flera formler och som kan påverka dess beräkningsalgoritm och prestanda.

För att undvika skador som orsakas av cirkulära referenser i Excel-formler måste du veta när de visas och när de inte gör det. Nedan hittar du instanserna:

• I alla öppna kalkylblad eller arbetsböcker skapar du en formel och anger den cirkulära referensen för första gången.
• Du eller någon tog bort alla cirkulära referenser från Excel-filen och sparade den. Nu anger du eller en annan användare en cirkulär referens i samma fil.
• Du stängde alla öppna arbetsböcker. Nu öppnade en annan arbetsbok och skriv sedan en formel som innehåller en cirkulär referens.

• Du har precis öppnat en arbetsbok som tidigare innehöll en eller flera cirkulära referenser. Du kommer att se varningen.
• När Excel har visat dig varningsmeddelandet med cirkulär referens en gång och du klickade OK, kommer den inte att visa samma popup längre i samma session även om du fortsätter att skapa mer cirkulära refererade formler.

Därför måste du noggrant leta efter ovanstående instanser för att fånga cirkulära referensvarningar och lösa dem om du inte letar efter iterativa beräkningar.

Om du vill lära dig hur du hittar cirkulära referenser i Excel måste du förstå deras typer. Eftersom att hitta och eliminera cirkulära referenser i Excel inte är en automatiserad uppgift. Nedan hittar du de vanligaste typerna av detta fel:

• Avsedda cirkulära referenser uppstår när du använder dem avsiktligt för iterativa beräkningsändamål. Till exempel måste du mata in en tidsstämpel på en annan cell när någon anger ett värde i en refererad cell med hjälp av flyktiga och logiska funktioner som OM, ELLER, SUMOM, etc.

• Oavsiktlig cirkulär referens när du försöker arbeta med en Excel-formel snabbare och refererar till den formelinnehållande cellen av misstag. Till exempel måste du tillämpa BELOPP funktion från cell A1 till A9 och hämta resultatet i cellen A10. Men av misstag skriver du formeln så här i cell A10:

=SUMMA(A2:A10)

• Dolda cirkulära referenser är de som inte visar några varningsmeddelanden när du skapar en formel eller öppnar kalkylbladet för första gången. Det händer för att någon tillåtit iterativ beräkning på Excel-filen.

• Indirekta cirkulära referenser är också populära och de uppstår när du skapar en ny formel i valfri cell, till exempel cell A1, och den här nya formeln skulle använda en annan funktion i en annan cell, som cell B1, som redan använder cell A1 som referens.

Nu när du förstod grunderna i cirkulära referenser i Excel, låt oss ta reda på hur du hittar cirkulära referenser i Excel nedan:

Förutsatt att den iterativa beräkningsfunktionen är avaktiverad kan du se följande meddelanden om cirkulära referenser i Excel-statusfältet:

• Cirkulär referenser: C3 (eller någon annan cellreferens) när du är på ett kalkylblad som innehåller cirkulär formel i arbetsboken.

• Du kommer bara se Cirkulära referenser om du är på ett kalkylblad som inte innehåller några men arbetsboken har några av dessa cirkulära formler.

Det är det bästa sättet att söka efter cirkulära referenser i en arbetsbok eller ett kalkylblad. Här är stegen du måste känna till:

• Öppna ett kalkylblad som du misstänker innehåller cirkulära formler.
• Klicka på Formler menyn på Excel-bandet.

• I den Formelrevision kommandogrupp, klicka på Fel vid kontrollrullgardinsmenyn.
• Håll muspekaren över Cirkulära referenser alternativet för att se alla tillgängliga instanser i alla kalkylblad.
• Välj varje instans och Excel kommer att lokalisera dessa celler automatiskt.

Om du inte tycker att metoden för felkontroll är tillräckligt effektiv för att upptäcka ett stort antal formler med cirkulära referenser, kan du prova dessa steg för att använda formelspårningsmetoden:

• Välj valfri cell som refereras till eller den cell som innehåller formeln.
• Gå till Formler fliken på Excel-bandmenyn och klicka sedan Spåra prejudikat.
• Excel kommer att markera de celler som påverkar formelresultatet på den aktuella cellen med pilar.

• Du kan också klicka Spårberoende för att upptäcka celler som påverkas av värdet på den valda cellreferensen.
• Om Excel inte visar några fel efter att du klickat på ovanstående kommandon, finns det inga cirkulära referenser i ditt kalkylblad.
• Upprepa stegen på alla kalkylblad för att kontrollera hela arbetsboken.

Denna metod är särskilt viktig när de iterativa beräkningsfunktionerna är aktiva i Excel-kalkylbladet.

• Klicka på Fil fliken på Excel-bandet och öppna sedan Excel-alternativ från sidofältet som dyker upp.
• På Excel-alternativ dialogrutan, välj Formler menyn från sidofältet och avmarkera sedan kryssrutan för Aktivera iterativ beräkning.

• Klick OK för att spara ändringarna.
• Nu kommer arbetsboken eller arbetsbladet automatiskt att visa dig den cirkulära referensvarningen.
• Om den inte visas direkt, spara arbetsboken, stäng den och öppna den igen för att se varningen.
• När du ser varningen kontrollera Excel statusfält för att veta vilken cell som finns i den cirkulära referensen.

Det säkra sättet att korrigera ett eller flera cirkulära referensfel i Excel är att manuellt redigera varje formel som innehåller den. På så sätt kan du lösa både tillfälliga och indirekta cirkulära referenser i Excel.

Ett annat enkelt sätt är att ta bort formeln från den aktuella cellen och placera den någon annanstans i samma kalkylblad.

Liksom Excel tillåter inte Google Sheets några cirkulära referenser i en eller flera formler om du inte har aktiverat den iterativa beräkningsfunktionen. Därför kommer Google Kalkylark omedelbart att visa #REF! fel på cellen som innehåller en cirkulär formel.

Därför kan du helt enkelt välja alla celler som innehåller #REF! varning för att hitta och lösa cirkulära referenser på Google Kalkylark.

Så nu vet du alla hemligheter för hur man hittar cirkulära referenser i Excel. Använd den metod du gillar och släpp alla cirkulära referenser för att säkerställa att du inte blir förvirrad av opålitliga data från en Excel-rapport eller matematisk instrumentpanel.

Nästa upp, den bästa Excel-kalendermallarna för alla år och den bästa Excel-budgetmallar.