Hur man hittar cirkulära referenser i Excel för att undvika felaktiga data

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.

Vad är en cirkulär referens i Excel?

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 multiplicera 10 från cell B1 och sätta resultatet i själva cell B1 .

Nu, i scenariot ovan, om du bara trycker på OK , kommer Excel att stänga felet och visa noll som det beräknade resultatet. Detta är bara ett enkelt exempel.

Vad är cirkulär referens i Excel

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äknats 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.

När ser du cirkulär referensfel i Excel?

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 det har stött 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.

När ser du cirkulär referensfel i Excel

• 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 för cirkulär referens en gång och du klickade på OK , kommer det inte att visa samma popup längre i samma session även om du fortsätter att skapa fler cirkulära referensformler.

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.

Typer av cirkulära referenser i Excel

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

Ett exempel på användning av cirkulär referens

• 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 i Excel

• 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 använda SUM -funktionen från cell A1 till A9 och hämta resultatet i cell 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.

Ett exempel på en indirekt cirkulär hänvisning

• 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 denna nya formel 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:

Hur man hittar cirkulära referenser i Excel: Från statusfältet

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

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

Visar cirkulära referenser i Excel Status Bar

• Du kommer bara att 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.

Hur man hittar cirkulära referenser i Excel: Använda felkontroll

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.

Använd felkontroll för Hur man hittar cirkulära referenser i Excel

• I kommandogruppen Formula Auditing klickar du på rullgardinsmenyn Error Checking  .
• Håll muspekaren över alternativet Cirkulära referenser för att se alla tillgängliga instanser i alla kalkylblad.
• Välj varje instans och Excel kommer att lokalisera dessa celler automatiskt.

Hur man hittar cirkulära referenser i Excel: Från formelspårning

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 fliken Formler på Excel-bandmenyn och klicka sedan på Spåra prejudikat .
• Excel kommer att markera de celler som påverkar formelresultatet på den aktuella cellen med pilar.

Använd formelspårning för hur du hittar cirkulära referenser i Excel

• Du kan också klicka på Spåra beroende 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.

Hur man hittar cirkulära referenser i Excel: Använda Excel-alternativ

• Klicka på fliken Arkiv på Excel-bandet och öppna sedan Excel-alternativ från sidofältet som visas.
• I dialogrutan för Excel-alternativ väljer du menyn Formler i sidofältet och avmarkerar sedan kryssrutan för Aktivera iterativ beräkning .

Hur man inaktiverar iterativ beräkning i Excel

• Klicka på 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ältet för att veta vilken cell som finns i den cirkulära referensen.

Hur man löser cirkulära referenser i Excel

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.

Hur man löser 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.

Hur man hittar cirkulära referenser i Google Sheets (bonusmetod)

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.

Google Sheets Hur man hittar cirkulära referenser

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.

Excel Circular References: Slutord

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, de  bästa Excel-kalendermallarna för alla år  och de  bästa Excel-budgetmallarna .