Sådan finder du cirkulære referencer i Excel for at undgå fejlagtige data

Så du den cirkulære referencefejl på et Excel-ark og spekulerede på, om regnearket er ødelagt? Stol på mig! Du er ikke den første, der står over for denne Excel-fejl. Hundredvis og tusinder af Excel-brugere oplever cirkulære referencefejl hver dag. Ikke at bekymre sig! Sådan finder du cirkulære referencer i Excel og løser dem.

Som en ivrig Excel-bruger skriver du sjældent de samme data i mange celler. I stedet importerer du et datasæt fra kilden og bruger cellehenvisning til at oprette formler. Dermed opretter du et dynamisk regneark.

Når nogen ændrer celleværdierne for cellereferencerne, opdaterer regnearket sig selv automatisk. Dette kaldes dataanalyseautomatisering, og dataanalytikere elsker at lege med cellereferencer.

Men problemet opstår, når du med vilje eller ved et uheld indtaster den celleadresse, hvor du i øjeblikket udfører en beregning. Eller, når du opretter mange formler i hele regnearket, vises en celle, der indeholder en formel, indirekte i en anden formel.

I sådanne og mange andre lignende situationer får du den cirkulære referencefejl i Excel. Læs videre for at lære, hvad cirkulær reference er, dens grunde, hvorfor du skal undgå det, og vigtigst af alt, hvordan du finder cirkulære referencer i Excel for at slippe af med disse uregelmæssigheder for altid.

Hvad er en cirkulær reference i Excel?

Excel støder på en cirkulær referencefejl, når den ser, at en eller mange funktioner i regnearket refererer tilbage til den celleadresse, hvor deres funktioner blev oprettet.

Det lyder måske forvirrende for dig, så tænk på, hvor alvorligt det kan være for Excel-appen. Kort sagt, du ønsker aldrig at sætte celleadressen i en formel, hvor du opretter den. Se på følgende eksempel for en visuel afklaring:

Du prøver at gange 10 fra celle B1 og indsætte resultatet i selve celle B1 .

Nu, i ovenstående scenarie, hvis du bare trykker på OK , vil Excel lukke fejlen og vise nul som det beregnede resultat. Dette er blot et ligetil eksempel.

Hvad er cirkulær reference i Excel

Forestil dig nu, at der er hundredvis af formler i et bogføringsregneark, økonomiplanlægningsark, medarbejderløn, leverandørbetalingsregneark og så videre.

Du vil stole på de fejlbehæftede data beregnet af Excel på grund af den cirkulære referencefejl, og det vil igen påvirke din virksomheds eller professionelle beslutningstagning.

Desuden kan cirkulær henvisning kraftigt reducere hastigheden af ​​beregninger i Excel-appen. I alvorlige tilfælde kan selve fejlen også forårsage ekstrem langsom ydeevne på pc'en, hvis den ikke håndteres med det samme.

Men hvis du arbejder på avancerede Excel-formler, hvor din datamodel kræver mellemliggende input fra den cirkulære reference for at fortsætte med en lang funktion, skal du muligvis bruge den. Faktisk lader Excel dig bruge denne beregningsmetode, når det er nødvendigt.

For eksempel skal du i energiproduktionsenheder, som vindmøller, beregne genanvendt udgangsenergi fra en brøkdel af inputenergien. Alternativt, i et kemisk anlæg, bruger ingeniører udgangsstammen som input til den samme eller en anden kedel eller procesbeholder. Sådanne tekniske beregninger på højt niveau kræver faktisk cirkulære referencer til iterative beregninger.

Hvornår ser du den cirkulære referencefejl i Excel?

Det er klart, at den cirkulære reference pop-up ikke er en fejlmeddelelse. Det er en måde for Excel at informere dig om, at den stødte på cirkulær reference i en eller flere formler, og det kan påvirke dens beregningsalgoritme og ydeevne.

For at undgå skader forårsaget af cirkulære referencer i Excel-formler, skal du vide, hvornår de vises, og hvornår de ikke gør det. Find forekomsterne nedenfor:

• I ethvert åbent regneark eller projektmappe opretter du en formel og indtaster den cirkulære reference for første gang.
• Du eller nogen fjernede alle cirkulære referencer fra Excel-filen og gemte den. Nu skal du eller en anden bruger igen indtaste en cirkulær reference i den samme fil.
• Du lukkede alle åbne projektmapper. Åbnede nu en anden projektmappe, og skriv derefter en formel, der indeholder en cirkulær reference.

Hvornår ser du den cirkulære referencefejl i Excel

• Du har lige åbnet en projektmappe, der tidligere indeholdt en eller flere cirkulære referencer. Du vil se advarslen.
• Når Excel har vist dig advarselsmeddelelsen om cirkulær reference én gang, og du har klikket på OK , vil den ikke vise den samme pop-up længere i samme session, selvom du bliver ved med at oprette flere cirkulære referencer.

Derfor skal du omhyggeligt lede efter ovenstående tilfælde for at fange cirkulære referenceadvarsler og løse dem, hvis du ikke leder efter iterative beregninger.

Typer af cirkulære referencer i Excel

Hvis du vil lære at finde cirkulære referencer i Excel, skal du forstå deres typer. Fordi det ikke er en automatiseret opgave at finde og fjerne cirkulære referencer i Excel. Find de almindelige typer af denne fejl nedenfor:

Et eksempel på brug af cirkulær reference

• Tilsigtede cirkulære referencer opstår, når du bruger dem bevidst til iterative beregningsformål. For eksempel skal du indtaste et tidsstempel på en anden celle, når nogen indtaster en værdi i en refereret celle ved hjælp af flygtige og logiske funktioner som HVIS, ELLER, SUMHVIS osv.

Utilsigtet cirkulære reference i Excel

• Utilsigtet cirkulær henvisning, når du forsøger at arbejde hurtigere med en Excel-formel og fejlagtigt refererer til den formelholdige celle. For eksempel skal du anvende SUM- funktionen fra celle A1 til A9 og hente resultatet i celle A10 . Men fejlagtigt skriver du formlen sådan her på celle A10:

=SUM(A2:A10)

• Skjulte cirkulære referencer er dem, der ikke viser nogen advarselsmeddelelser, når du opretter en formel eller åbner regnearket for første gang. Det sker, fordi nogen tillod iterativ beregning på Excel-filen.

Et eksempel på en indirekte cirkulær reference

• Indirekte cirkulære referencer er også populære, og de opstår, når du opretter en ny formel i en hvilken som helst celle, f.eks. celle A1 , og denne nye formel ville bruge en anden funktion i en anden celle, f.eks. celle B1 , der allerede bruger celle A1 som reference .

Nu hvor du har forstået det grundlæggende i cirkulære referencer i Excel, lad os finde ud af, hvordan du finder cirkulære referencer i Excel nedenfor:

Sådan finder du cirkulære referencer i Excel: Fra statuslinjen

Forudsat at den iterative beregningsfunktion er deaktiveret, kan du se følgende meddelelser om cirkulære referencer på Excel-statuslinjen:

• Cirkulære referencer: C3  (eller enhver anden cellereference), når du er på et regneark, der indeholder cirkulær formel i projektmappen.

Viser cirkulære referencer i Excel Status Bar

• Du vil kun se cirkulære referencer, hvis du er på et regneark, der ikke indeholder nogen, men projektmappen har et par af disse cirkulære formler.

Sådan finder du cirkulære referencer i Excel: Brug af fejlkontrol

Det er den bedste måde at søge efter cirkulære referencer i en projektmappe eller et regneark. Her er de trin, du skal kende:

• Åbn et regneark, som du mistænker for at indeholde cirkulære formler.
• Klik på menuen Formler på Excel-båndet.

Brug fejlkontrol for Sådan finder du cirkulære referencer i Excel

• Klik på rullelisten Fejlkontrol i kommandogruppen Formelrevision . 
• Hold markøren over indstillingen Cirkulære referencer for at se alle de tilgængelige forekomster i alle regneark.
• Vælg hver forekomst, og Excel vil lokalisere disse celler automatisk.

Sådan finder du cirkulære referencer i Excel: Fra Formelsporing

Hvis du ikke finder fejlkontrolmetoden effektiv nok til at registrere et stort antal formler med cirkulære referencer, kan du prøve disse trin for at bruge formelsporingsmetoden:

• Vælg en hvilken som helst refereret celle eller den celle, der indeholder formel.
• Gå til fanen Formler i Excel-båndmenuen, og klik derefter på Trace Precedents .
• Excel vil fremhæve de celler, der påvirker formelresultatet på den aktuelle celle med pile.

Brug formelsporing til Sådan finder du cirkulære referencer i Excel

• Du kan også klikke på Spor afhængige for at opdage celler, der er påvirket af værdien af ​​den valgte cellereference.
• Hvis Excel ikke viser nogen fejl, efter du har klikket på ovenstående kommandoer, er der ingen cirkulære referencer i dit regneark.
• Gentag trinene på alle regneark for at kontrollere hele projektmappen.

Denne metode er især vigtig, når de iterative beregningsfunktioner er aktive på Excel-regnearket.

Sådan finder du cirkulære referencer i Excel: Brug af Excel-indstillinger

• Klik på fanen Filer på Excel-båndet, og åbn derefter Excel-indstillinger fra sidebjælken, der vises.
• I dialogboksen Excel-indstillinger skal du vælge menuen Formler fra sidebjælken og derefter fjerne markeringen i afkrydsningsfeltet Aktiver iterativ beregning .

Sådan deaktiveres iterativ beregning i Excel

• Klik på OK for at gemme ændringerne.
• Nu vil projektmappen eller regnearket automatisk vise dig den cirkulære referenceadvarsel.
• Hvis den ikke vises med det samme, skal du gemme projektmappen, lukke den og åbne den igen for at se advarslen.
• Når du ser advarslen, skal du kontrollere Excel-statuslinjen for at vide, hvilken celle der er i den cirkulære reference.

Sådan løses cirkulære referencer i Excel

Den sikre måde at rette en eller mange cirkulære referencefejl på i Excel er ved manuelt at redigere hver formel, der indeholder den. På denne måde kan du løse både tilfældige og indirekte cirkulære referencer i Excel.

Sådan løses cirkulære referencer i Excel

En anden nem måde er at fjerne formlen fra den aktuelle celle og placere den et andet sted i det samme regneark.

Sådan finder du cirkulære referencer i Google Sheets (bonusmetode)

Ligesom Excel tillader Google Sheets ikke nogen cirkulære referencer i en eller flere formler, hvis du ikke har aktiveret den iterative beregningsfunktion. Derfor vil Google Sheets straks vise #REF! fejl på cellen, der indeholder en cirkulær formel.

Google Sheets Sådan finder du cirkulære referencer

Derfor kan du blot vælge alle de celler, der indeholder #REF! advarsel om at finde og løse cirkulære referencer på Google Sheets.

Excel Cirkulære Referencer: Slutord

Så nu kender du alle hemmelighederne for, hvordan du finder cirkulære referencer i Excel. Brug den metode, du kan lide, og forkast alle de cirkulære referencer for at sikre, at du ikke bliver forvirret af upålidelige data fra en Excel-rapport eller et matematisk dashboard.

Næste, de  bedste Excel-kalenderskabeloner for ethvert år  og de  bedste Excel-budgetskabeloner .