Sådan finder du cirkulære referencer i Excel på 4 måder

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.

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 formere dig 10 fra celle B1 og sæt resultatet i cellen B1 sig selv.

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

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.

Det er klart sagt, 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.

• 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 den cirkulære referenceadvarsel én gang, og du klikkede Okay, vil den ikke vise den samme pop-up længere i den samme session, selvom du bliver ved med at oprette mere cirkulære refererede formler.

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.

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:

• 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æ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 funktion fra celle A1 til A9 og hent 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.

• Indirekte cirkulære referencer er også populære, og de opstår, når du opretter en ny formel i en celle, f.eks. A1, og denne nye formel ville bruge en anden funktion i en anden celle, f.eks. celle B1, som 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:

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.

• 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.

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å Formler menuen på Excel-båndet.

• I den Formelrevision kommandogruppe, klik på Fejlkontroldrop-down liste.
• Hold markøren over Cirkulære referencer mulighed for at se alle tilgængelige forekomster i alle regneark.
• Vælg hver forekomst, og Excel vil lokalisere disse celler automatisk.

Hvis du ikke finder fejlkontrolmetoden effektiv nok til at detektere 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 Formler fanen på Excel-båndmenuen, og klik derefter på Spor fortilfælde.
• Excel vil fremhæve de celler, der påvirker formelresultatet på den aktuelle celle med pile.

• Du kan også klikke 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.

• Klik på Fil fanen på Excel-båndet, og åbn derefter Excel-indstillinger fra sidebjælken, der dukker op.
• På den Excel-indstillinger dialogboksen skal du vælge Formler menuen fra sidebjælken, og fjern derefter markeringen i afkrydsningsfeltet for Aktiver iterativ beregning.

• Klik Okay 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 statuslinje at vide, hvilken celle der er i den cirkulære reference.

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.

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

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.

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.

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 op, den bedste Excel-kalenderskabeloner for ethvert år og bedste Excel budget skabeloner.