En af de mest almindelige fejladvarsler, som brugere støder på i Excel, er 'Cirkulær reference'. Tusindvis af brugere har det samme problem, og det opstår, når en formel refererer tilbage til sin egen celle direkte eller indirekte, hvilket forårsager en endeløs løkke af beregninger.
For eksempel har du to værdier i cellerne B1 og B2. Når formlen =B1+B2 indtastes i B2, opretter den en cirkulær reference; formlen i B2 genberegner sig selv gentagne gange, fordi hver gang den beregner, har B2-værdien ændret sig.
De fleste cirkulære referencer er utilsigtede fejl; Excel vil advare dig om disse. Der er dog også tiltænkte cirkulære referencer, som bruges til at lave iterative beregninger. Utilsigtede cirkulære referencer i dit regneark kan få din formel til at beregne forkert.
Derfor vil vi i denne artikel forklare alt, hvad du behøver at vide om cirkulære referencer, og hvordan du finder, reparerer, fjerner og bruger cirkulære referencer i Excel.
Sådan finder og håndterer du cirkulær reference i Excel
Når vi arbejder med Excel, støder vi nogle gange på cirkulære referencefejl, som opstår, når du indtaster en formel, der inkluderer cellen, hvor din formel er placeret. Dybest set sker det, når din formel forsøger at beregne sig selv.
For eksempel har du en kolonne med tal i celle A1:A4, og du bruger SUM-funktionen (=SUM(A1:A5)) i celle A5. Cellen A5 refererer direkte til sin helt egen celle, hvilket er forkert. Derfor vil du få følgende cirkulære referenceadvarsel:
Når du har fået ovenstående advarselsmeddelelse, kan du klikke på knappen 'Hjælp' for at få mere at vide om fejlen, eller lukke fejlmeddelelsesvinduet ved at klikke på enten 'OK' eller 'X' knappen og få '0' som resultat.
Nogle gange kan cirkulære referencesløjfer få din beregning til at gå ned eller sænke dit regnearks ydeevne. Cirkulær henvisning kan også føre til en række andre problemer, som ikke vil være tydelige med det samme. Så det er bedst at undgå disse.
Direkte og indirekte cirkulære referencer
Cirkulære referencer kan kategoriseres i to typer: Direkte cirkulære referencer og indirekte cirkulære referencer.
Direkte reference
En direkte cirkulær reference er ret enkel. Advarselsmeddelelsen om direkte cirkulær reference dukker op, når formlen refererer direkte tilbage til sin egen celle.
I eksemplet nedenfor refererer formlen i celle A2 direkte til sin egen celle (A2).
Når advarselsmeddelelsen dukker op, kan du klikke på 'OK', men det vil kun resultere i '0'.
Indirekte cirkulære reference
En indirekte cirkulær reference i Excel opstår, når en værdi i en formel refererer tilbage til sin egen celle, men ikke direkte. Med andre ord kan cirkulær reference dannes af to celler, der refererer til hinanden.
Lad os forklare med dette enkle eksempel.
Nu starter værdien fra A1, som har værdien 20.
Dernæst refererer celle C3 til celle A1.
Derefter refererer celle A5 til celle C3.
Erstat nu værdien 20 i celle A1 med formlen som vist nedenfor. Hver anden celle er afhængig af celle A1. Når du bruger en reference til en anden tidligere formelcelle i A1, vil det forårsage en cirkulær referenceadvarsel. Fordi formlen i A1 refererer til celle A5, som refererer til C3, og celle C3 refererer tilbage til A1, deraf den cirkulære reference.
Når du klikker på 'OK', resulterer det i en værdi på 0 i celle A1, og Excel opretter en sammenkædet linje, der viser sporingspræcedenser og sporingsafhængige som vist nedenfor. Vi kan bruge denne funktion til nemt at finde og rette/fjerne cirkulære referencer.
Sådan aktiveres/deaktiveres cirkulære referencer i Excel
Som standard er iterative beregninger slået fra (deaktiveret) i Excel. Iterative beregninger er gentagne beregninger, indtil den opfylder en bestemt betingelse. Når det er deaktiveret, viser Excel en cirkulær referencemeddelelse og returnerer et 0 som resultat.
Men nogle gange er cirkulære referencer nødvendige for at beregne en løkke. For at bruge cirkulær reference skal du aktivere iterative beregninger i din Excel, og det vil give dig mulighed for at udføre dine beregninger. Lad os nu vise dig, hvordan du kan aktivere eller deaktivere iterative beregninger.
I Excel 2010, Excel 2013, Excel 2016, Excel 2019 og Microsoft 365 skal du gå til fanen 'Filer' i øverste venstre hjørne af Excel, og derefter klikke på 'Indstillinger' i venstre rude.
I vinduet Excel-indstillinger skal du gå til fanen 'Formel' og markere afkrydsningsfeltet 'Aktiver iterativ beregning' under sektionen 'Beregningsindstillinger'. Klik derefter på 'OK' for at gemme ændringerne.
Dette vil muliggøre iterativ beregning og dermed tillade cirkulær reference.
Følg disse trin for at opnå dette i tidligere versioner af Excel:
- I Excel 2007 skal du klikke på Office-knappen > Excel-indstillinger > Formler > Iterationsområde.
- I Excel 2003 og tidligere versioner skal du gå til Menu > Værktøjer > Indstillinger > fanen Beregning.
Maksimale iterationer og maksimale ændringsparametre
Når du har aktiveret iterative beregninger, kan du kontrollere de iterative beregninger ved at angive to tilgængelige muligheder under sektionen Aktiver iterativ beregning som vist på skærmbilledet nedenfor.
- Maksimale iterationer – Dette tal angiver, hvor mange gange formlen skal genberegnes, før du får det endelige resultat. Standardværdien er 100. Hvis du ændrer den til '50', vil Excel gentage beregningerne 50 gange, før du giver dig det endelige resultat. Husk, at jo højere antal iterationer, jo flere ressourcer og tid tager det at beregne.
- Maksimal ændring – Den bestemmer den maksimale ændring mellem beregningsresultaterne. Denne værdi bestemmer nøjagtigheden af resultatet. Jo mindre tal, jo mere nøjagtigt resultat ville resultatet være, og jo længere tid tager det at beregne regnearket.
Hvis muligheden for iterative beregninger er aktiveret, vil du ikke få nogen advarsel, når der er en cirkulær reference i dit regneark. Aktiver kun interaktiv beregning, når det er absolut nødvendigt.
Find Circular Reference i Excel
Antag, at du har et stort datasæt, og du har fået den cirkulære referenceadvarsel, skal du stadig finde ud af, hvor (i hvilken celle) fejlen er opstået for at rette den. Følg disse trin for at finde cirkulære referencer i Excel:
Brug af fejlkontrolværktøj
Åbn først arbejdsarket, hvor den cirkulære reference er sket. Gå til fanen 'Formel', klik på pilen ved siden af værktøjet 'Fejlkontrol'. Hold derefter markøren over indstillingen 'Cirkulære referencer', Excel vil vise dig listen over alle celler, der er involveret i den cirkulære reference som vist nedenfor.
Klik på den celleadresse, du ønsker på listen, og den vil tage dig til den pågældende celleadresse for at løse problemet.
Brug af statuslinjen
Du kan også finde den cirkulære reference på statuslinjen. På Excels statuslinje vil den vise dig den seneste celleadresse med en cirkulær reference, såsom 'Cirkulære referencer: B6' (se skærmbilledet nedenfor).
Der er visse ting, du bør vide, når du håndterer cirkulær reference:
- Statuslinjen viser ikke den cirkulære referencecelleadresse, når indstillingen Iterativ beregning er aktiveret, så du skal deaktivere den, før du begynder at se på projektmappen for cirkulære referencer.
- Hvis cirkulær reference ikke findes i det aktive ark, viser statuslinjen kun 'Cirkulære referencer' uden celleadresse.
- Du vil kun få en cirkulær referenceprompt én gang, og efter du har klikket på 'OK', vil den ikke vise prompten igen næste gang.
- Hvis din projektmappe har cirkulære referencer, vil den vise dig prompten, hver gang du åbner den, indtil du løser den cirkulære reference, eller indtil du slår iterativ beregning til.
Fjern en cirkulær reference i Excel
Det er nemt at finde cirkulære referencer, men det er ikke så nemt at rette det. Desværre er der ingen mulighed i Excel, der giver dig mulighed for at fjerne alle cirkulære referencer på én gang.
For at rette cirkulære referencer skal du finde hver cirkulær reference individuelt og prøve at ændre den, fjerne den cirkulære formel helt eller erstatte den med en anden.
Nogle gange, i simple formler, er alt hvad du skal gøre at justere parametrene for formlen, så den ikke refererer tilbage til sig selv. For eksempel skal du ændre formlen i B6 til =SUM(B1:B5)*A5 (ændre B6 til B5).
Det vil returnere resultatet af beregningen som '756'.
I tilfælde, hvor en Excel-cirkulær reference er svær at finde, kan du bruge funktionerne Trace Precedents og Trace Dependents til at spore den tilbage til kilden og løse den én efter én. Pilen viser hvilke celler der er påvirket af den aktive celle.
Der er to sporingsmetoder, der kan hjælpe dig med at slette cirkulære referencer ved at vise forholdet mellem formler og celler.
For at få adgang til sporingsmetoderne skal du gå til fanen 'Formler' og derefter klikke på enten 'Sporing fortilfælde' eller 'Sporingsafhængige' i gruppen Formelrevision.
Spor fortilfælde
Når du vælger denne mulighed, spores de celler tilbage, der påvirker den aktive celles værdi. Den tegner en blå linje, der angiver, hvilke celler der påvirker den aktuelle celle. Genvejstasten til at bruge spor fortilfælde er Alt + T U T.
I eksemplet nedenfor viser den blå pil de celler, der påvirker B6-værdien, er B1:B6 og A5. Som du kan se nedenfor, er celle B6 også en del af formlen, hvilket gør den til en cirkulær reference og får formlen til at returnere '0' som resultat.
Dette kan nemt rettes ved at erstatte B6 med B5 i SUMs argument: =SUM(B1:B5).
Spor afhængige
Funktionen sporafhængige sporer de celler, der er afhængige af den valgte celle. Denne funktion tegner en blå linje, der angiver, hvilke celler der er påvirket af den valgte celle. Det vil sige, det viser, hvilke celler der indeholder formler, der refererer til den aktive celle. Genvejstasten til at bruge pårørende er Alt + T U D.
I det følgende eksempel er celle D3 påvirket af B4. Den er afhængig af B4 for dens værdi for at give resultater. Derfor tegner sporafhængig en blå linje fra B4 til D3, hvilket indikerer, at D3 er afhængig af B4.
Bevidst brug af cirkulære referencer i Excel
Det anbefales ikke at bruge cirkulære referencer bevidst, men der kan være nogle sjældne tilfælde, hvor du har brug for en cirkulær reference, så du kan få det output, du ønsker.
Lad os forklare det ved at bruge et eksempel.
Til at starte med skal du aktivere 'Iterativ beregning' i din Excel-projektmappe. Når du har aktiveret Iterativ beregning, kan du begynde at bruge cirkulære referencer til din fordel.
Lad os antage, at du køber et hus, og du vil give en 2% kommission på husets samlede omkostninger til din agent. De samlede omkostninger vil blive opgjort i celle B6, og kommissionsprocenten (agenthonorar) beregnes i B4. Provisionen beregnes ud fra de samlede omkostninger, og de samlede omkostninger inkluderer provisionen. Da cellerne B4 og B6 er afhængige af hinanden, skaber det en cirkulær reference.
Indtast formlen for at beregne de samlede omkostninger i celle B6:
=SUM(B1:B4)
Da de samlede omkostninger inkluderer agenthonoraret, inkluderede vi B4 i ovenstående formel.
For at beregne agentgebyr på 2 % skal du indsætte denne formel i B4:
=B6*2 %Nu afhænger formlen i celle B4 af værdien af B6 for at beregne 2% af det samlede gebyr, og formlen i B6 afhænger af B4 for at beregne de samlede omkostninger (inklusive agenthonorar), deraf den cirkulære reference.
Hvis den iterative beregning er aktiveret, vil Excel ikke give dig en advarsel eller et 0 i resultatet. I stedet vil resultatet af celler B6 og B4 blive beregnet som vist ovenfor.
Muligheden for iterative beregninger er normalt deaktiveret som standard. Hvis du ikke har slået det til, og når du indtaster formlen i B4, vil det skabe en cirkulær reference. Excel udsender advarslen, og når du klikker på 'OK', vises sporingspilen.
Det er det. Dette var alt, du behøver at vide om cirkulære referencer i Excel.