Mens du arbejder med Excel -formler, kan du engang se følgende advarselsmeddelelse.
Denne prompt fortæller dig, at der er en cirkulær reference i dit regneark, og dette kan føre til eller en forkert beregning ved hjælp af formlerne. Det beder dig også om at behandle dette cirkulære referenceproblem og sortere det.
I denne vejledning dækker jeg alt det, du har brug for at vide om cirkulær reference, samt hvordan man finder og fjerner cirkulære referencer i Excel.
Så lad os komme i gang!
Hvad er cirkulær reference i Excel?
I enkle ord sker der en cirkulær reference, når du ender med at have en formel i en celle - som i sig selv bruger cellen (hvor den er indtastet) til beregningen.
Lad mig prøve at forklare dette ved et simpelt eksempel.
Antag, at du har datasættet i celle A1: A5, og du bruger nedenstående formel i celle A6:
= SUM (A1: A6)
Dette giver dig en cirkulær referenceadvarsel.
Dette er fordi du vil opsummere værdierne i celle A1: A6, og resultatet skal være i celle A6.
Dette skaber en loop, da Excel bare bliver ved med at tilføje den nye værdi i celle A6, som bliver ved med at ændre sig (derfor en cirkulær reference loop).
Sådan finder du cirkulære referencer i Excel?
Selvom den cirkulære advarselsadvarsel er venlig nok til at fortælle dig, at den findes i dit regneark, fortæller den dig ikke, hvor det sker, og hvilke cellereferencer der forårsager det.
Så hvis du forsøger at finde og håndtere cirkulære referencer i regnearket, skal du kende en måde at på en eller anden måde finde disse.
Nedenfor er trinene til at finde en cirkulær reference i Excel:
- Aktiver det regneark, der har den cirkulære reference
- Klik på fanen Formler
- I formelredigeringsgruppen skal du klikke på ikonet Fejlkontrol (lille pil nedad til højre)
- Hold markøren over indstillingen Cirkulære referencer. Det viser dig cellen, der har en cirkulær reference i regnearket
- Klik på celleadressen (der vises), og den fører dig til cellen i regnearket.
Når du har løst problemet, kan du igen følge de samme trin ovenfor, og det viser flere cellereferencer, der har cirkulær reference. Hvis der ikke er nogen, vil du ikke se nogen cellereference,
En anden hurtig og let måde at finde den cirkulære reference er ved at se på statuslinjen. I den venstre del af den viser den teksten Cirkulær reference sammen med celleadressen.
Der er et par ting, du skal vide, når du arbejder med cirkulære referencer:
- Hvis den iterative beregning er aktiveret (dækket senere i denne vejledning), viser statuslinjen ikke den cirkulære referencecelleadresse
- Hvis cirkulærreferencen ikke er i det aktive ark (men i andre ark i den samme projektmappe), viser den kun cirkulær reference og ikke celleadressen
- Hvis du får en cirkulær referenceadvarsel én gang, og du afviser den, vil den ikke vise prompten igen næste gang.
- Hvis du åbner en projektmappe med cirkulær reference, viser den dig prompten, så snart projektmappen åbnes.
Sådan fjernes en cirkulær reference i Excel?
Når du har identificeret, at der er cirkulære referencer i dit ark, er det tid til at fjerne disse (medmindre du vil have dem til at være der af en grund).
Desværre er det ikke så enkelt som at trykke på slette -tasten. Da disse er afhængige af formler, og hver formel er forskellig, skal du analysere dette fra sag til sag.
Hvis det bare er et spørgsmål om at få cellereferencen til at forårsage problemet ved en fejltagelse, kan du simpelthen rette ved at justere referencen.
Men nogle gange er det ikke så enkelt.
Cirkulær reference kan også forårsages baseret på flere celler, der føder ind i hinanden på mange niveauer.
Lad mig vise dig et eksempel.
Nedenfor er der en cirkulær reference i celle C6, men det er ikke kun et simpelt tilfælde af selvreference. Det er multi-level, hvor de celler, det bruger i beregningerne, også refererer til hinanden.
- Formlerne i celle A6 er = SUM (A1: A5)+C6
- Formlen er celle C1 er = A6*0,1
- Formlen i celle C6 er = A6+C1
I ovenstående eksempel er resultatet i celle C6 afhængigt af værdierne i celle A6 og C1, som igen er afhængige af celle C6 (hvilket forårsager den cirkulære referencefejl)
Og igen, jeg har valgt et virkelig simpelt eksempel kun til demo -formål. I virkeligheden kan disse være ret vanskelige at finde ud af og måske langt væk i det samme regneark eller endda spredt over flere regneark.
I et sådant tilfælde er der en måde at identificere de celler, der forårsager cirkulær reference, og derefter behandle disse.
Det er ved at bruge indstillingen Trace Precedents.
Nedenfor er trinene til at bruge spor -præcedenser til at finde celler, der føder til cellen, der har den cirkulære reference:
- Vælg den celle, der har den cirkulære reference
- Klik på fanen Formler
- Klik på Trace Precedents
Ovenstående trin viser dig blå pile, der fortæller dig, hvilke celler der føder formlen i den valgte celle. På denne måde kan du inspicere formlerne og cellerne og slippe af med den cirkulære reference.
Hvis du arbejder med komplekse finansielle modeller, kan det være muligt, at disse præcedenser også går dybt på flere niveauer.
Dette fungerer godt, hvis du har alle formlerne, der refererer til celler i det samme regneark. Hvis den findes i flere regneark, er denne metode ikke effektiv.
Sådan aktiveres/deaktiveres itterative beregninger i Excel
Når du har en cirkulær reference i en celle, får du først advarselsmeddelelsen som vist herunder, og hvis du lukker denne dialogboks, giver den dig 0 som resultatet i cellen.
Dette skyldes, at når der er en cirkulær reference, er det en endeløs sløjfe, og Excel vil ikke indhente det. Så det giver et 0.
Men i nogle tilfælde vil du faktisk gerne have, at den cirkulære reference er aktiv og foretager et par iterationer. I et sådant tilfælde i stedet for en uendelig loop, og du kan bestemme, hvor mange gange loopet skal køres.
Dette kaldes iterativ beregning i Excel.
Nedenfor er trinene til aktivering og konfiguration af iterative beregninger i Excel:
- Klik på fanen Filer
- Klik på Indstillinger. Dette åbner dialogboksen Excel -indstillinger
- Vælg formel i venstre rude
- I afsnittet Beregningsmuligheder skal du markere afkrydsningsfeltet ‘Aktiver iterativ beregning’. Her kan du angive de maksimale iterationer og den maksimale ændringsværdi
Det er det! Ovenstående trin ville muliggøre iterativ beregning i Excel.
Lad mig også hurtigt forklare de to muligheder i den iterative beregning:
- Maksimal iterationer: Dette er det maksimale antal gange, du vil have Excel til at beregne, før du giver dig det endelige resultat. Så hvis du angiver dette som 100, kører Excel løkken 100 gange, før du giver dig det endelige resultat.
- Maksimal ændring: Dette er den maksimale ændring, som hvis den ikke opnås mellem iterationer, vil beregningen blive stoppet. Som standard er værdien .001. Jo lavere denne værdi er, desto mere nøjagtig ville resultatet være.
Husk, at jo flere gange iterationerne kører, jo mere tid og ressourcer tager det for Excel at gøre det. Hvis du holder de maksimale iterationer høje, kan det føre til, at din Excel bremser eller går ned.
Bemærk: Når iterative beregninger er aktiveret, viser Excel dig ikke den cirkulære referenceadvarsel og viser den nu også på statuslinjen.Bevidst brug af cirkulære referencer
I de fleste tilfælde ville tilstedeværelsen af cirkulær reference i dit regneark være en fejl. Og derfor viser Excel dig en prompt, der siger: "Prøv at fjerne eller ændre disse referencer eller flytte formlerne til forskellige celler."
Men der kan være nogle specifikke tilfælde, hvor du har brug for en cirkulær reference, så du kan få det ønskede resultat.
En sådan specifik sag har jeg allerede skrevet om, at det får tidsstemplet i en celle i en celle i Excel.
Antag f.eks., At du vil oprette en formel, så hver indtastning foretages i en celle i kolonne A, tidsstemplet vises i kolonne B (som vist nedenfor):
Selvom du nemt kan indsætte et tidsstempel ved hjælp af nedenstående formel:
= HVIS (A2 "", HVIS (B2 "", B2, NU ()), "")
Problemet med ovenstående formel er, at det ville opdatere alle tidsstempler, så snart der foretages ændringer i regnearket, eller hvis regnearket åbnes igen (da NU -formlen er flygtig)
For at omgå dette problem kan du bruge en cirkulær referencemetode. Brug den samme formel, men aktiver iterativ beregning.
Der er også nogle andre tilfælde, hvor man ønsker evnen til at bruge cirkulær reference (du kan finde et eksempel her).
Bemærk: Selvom der kan være nogle tilfælde, hvor du kan bruge cirkulær reference, finder jeg det bedst at undgå at bruge det. Cirkulære referencer kan også tage en vejafgift på effektiviteten af din projektmappe og kan gøre den langsom. I sjældne tilfælde, hvor du har brug for det, foretrækker jeg altid at bruge VBA -koder for at få arbejdet udført.Jeg håber, at du fandt denne vejledning nyttig!
Andre Excel -øvelser, du kan finde nyttige:
- #REF! Fejl i Excel; Sådan rettes referencefejlen!
- Excel VBA fejlhåndtering
- Brug IFERROR med VLOOKUP for at slippe af med #N/A -fejl
- Sådan henvises til et andet ark eller en projektmappe i Excel (med eksempler)
- Absolutte, relative og blandede cellereferencer i Excel
