Sådan finder du cirkulære referencer i Excel

Når du arbejder med , kan du af og til støde på det, der er kendt som en cirkulær reference. Dette er, når en formel i en celle refererer tilbage til sig selv, hvilket forårsager en endeløs løkke i beregningsprocessen.

For at finde cirkulære referencer i Excel skal du bruge værktøjet "Fejlkontrol" under fanen "Formler" for at identificere celler med en cirkulær afhængighed.

Denne artikel viser dig, hvordan du finder cirkulære referencer i Excel og retter dem. Du vil også lære, hvordan du forebygger dem, når du arbejder med store eller komplekse regneark.

Indholdsfortegnelse

• Hvad er cirkulære referencer
• Vinduet med cirkulær referencefejlmeddelelse
• Sådan finder du cirkulære referencer i Excel
• Sådan finder du cirkulære referencer med Excel-statuslinjen
• Sådan finder du skjulte cirkulære referencer i Excel
  • Sådan bruges det sporafhængige værktøj
  • Spor præcedensværktøj
• 2 måder, hvordan du fjerner cirkulære referencer i Excel
  • 2. Brug alternative formler
• 5 tips til at forhindre cirkulære referencer
  • 1. Vær forsigtig, når du vælger områder
  • 2. Planlæg dine formler omhyggeligt
  • 3. Brug alternative formler
  • 4. Bryd komplekse formler op i mindre dele
  • 5. Løs problemer, mens du går
• Hvordan aktiverer man iterativ beregning og hvorfor gider?

Hvad er cirkulære referencer

Cirkulære referencer forekommer i Excel, når en formelberegning i en celle afhænger af cellens egen værdi. Dette skaber en loop og fører til forkerte eller uforudsigelige resultater.

Dette er lettere at forstå med et simpelt eksempel.

Antag, at du har to tal i cellerne A1 og B1. Du vil bruge en formel i celle A3, der lægger de to tal sammen. Formlen i celle A3 skulle se sådan ud:

=A1 + B1

Men hvis du laver en fejl og inkluderer C1 i beregningerne, har du nu en cirkulær reference:

=A1 + B1 + C1

Dette kan være et åbenlyst problem at undgå, når man har at gøre med to celler. Men når du arbejder med store celleområder, bliver fejl mere sandsynlige.

Heldigvis er Excel designet til at håndtere sådanne situationer ved at vise en advarselsmeddelelse, når der registreres en cirkulær reference.

Men i nogle tilfælde kan du støde på en cirkulær reference uden at modtage nogen advarsel. Derfor er det vigtigt at vide, hvordan man finder og løser disse problemer manuelt.

Vinduet med cirkulær referencefejlmeddelelse

Som standard overvåger Excel for cirkulære referencer, når du indtaster formler i celler. Når du trykker på enter i en celle med et problem, vil et pop op-vindue vise den cirkulære referencefejlmeddelelse.

Dette er den forklarende tekst:

Der er en eller flere cirkulære referencer, hvor en formel refererer til sin egen celle enten direkte eller indirekte. Dette kan få dem til at beregne forkert. Prøv at fjerne eller ændre disse referencer eller flytte formlerne til forskellige celler.

Når du ser denne dialogboks, skal du klikke på "OK" og bruge metoderne i denne artikel til at løse problemet.

Du behøver dog ikke rette fejlen med det samme. Du kan fortsætte med at arbejde med regnearket med den hensigt at løse problemet senere.

I så fald skal du vide, hvordan du finder cirkulære referencer i Excel, efter du har afvist advarselsboksen.

Sådan finder du cirkulære referencer i Excel

Excel har et indbygget fejlkontrolværktøj, der finder flere slags fejl, herunder cirkulære referencer. Du får adgang til værktøjet på fanen Formler.

Dette billede viser placeringen i Formula Auditing-gruppen.

Nu ved du, hvordan du finder værktøjet, her er de detaljerede trin til at bruge det:

1. Gå til fanen Formler.

1. Klik på rullemenuen Fejlkontrol i gruppen Formelrevision.

2. Vælg "Cirkulære referencer" fra rullelisten.

3. Klik på linket for at springe markøren til den første celle med et problem.

Billedet nedenfor viser, at værktøjet har fundet en cirkulær fejl i celle G1. Den viste celle er et hyperlink.

Når du klikker på linket, flyttes markøren til cellen. Dette er meget nyttigt med en stor mængde data.

Når du har løst den første cirkulære reference, kan du gå tilbage til fejlkontrolværktøjet for at finde den næste på arket.

Brug trinene i de senere afsnit i denne artikel til at løse problemet og brug værktøjet til at gå til næste problem.

Hvis der ikke er flere cirkulære spørgsmål, vil menupunktet "Cirkulære referencer" i rullemenuen være nedtonet. Du har ikke mere at gøre.

Sådan finder du cirkulære referencer med Excel-statuslinjen

En anden måde at få øje på en cirkulær reference på er at se på statuslinjen i bunden af ​​Excel-regnearket.

Når der er et eller flere problemer i regnearket, viser statuslinjen placeringen af ​​én problemcellereference.

For eksempel, hvis der er en cirkulær reference i celle C1, og der ikke er andre problemer med regnearket, vil statuslinjen læse: "Cirkulære referencer: C1".

Dette billede viser dig, hvor du kan se displayet.

Sådan finder du skjulte cirkulære referencer i Excel

Skjulte cirkulære referencer i er cirkulære referencer, der ikke umiddelbart opdages af de indbyggede fejlfunktioner. Med andre ord viser Excel ikke standardadvarslen.

De sker, når en celle refererer til en anden celle indirekte gennem en række mellemceller og til sidst refererer tilbage til den oprindelige celle.

Excel har to værktøjer, der hjælper dig med at spore relationer og undersøge disse problemer:

1. Trace Dependents værktøj

2. Spor præcedensværktøj

Sådan bruges det sporafhængige værktøj

Erfarne Excel-brugere drager fordel af Trace Dependents-værktøjet til at undersøge disse problemer og rette cirkulære referencer. Ved at vælge en eller flere celler og bruge dette værktøj, vil Excel vise dig de afhængige celler.

Værktøjet er tilgængeligt i sektionen Formelrevision på fanen Formler.

Følg disse trin for at bruge værktøjet:

1. Klik på den eller de celler, du vil spore afhængige for.

2. Gå til fanen 'Formler' i Excel-båndet.

3. Klik på knappen 'Spor afhængige' i gruppen 'Formelrevision'.

4. Gennemgå sporingspilen eller pilene, der viser afhængigheder.

Excel viser pile fra den aktive celleværdi til alle celler, der afhænger af den. Den blå pil på billedet nedenfor viser, at celle D6 har en afhængighed af celle B2.

Spor præcedensværktøj

Præcedens er det modsatte af pårørende. Med værktøjet Spor præcedens kan du vælge en celle med en formel og se alle de celler, som den afhænger af, dvs. præcedenserne.

Værktøjet er tilgængeligt i sektionen Formelrevision på fanen Formler.

Følg disse trin for at bruge værktøjet:

1. Klik på cellen, der indeholder den formel, du vil undersøge.

2. Gå til fanen Formel i Excel-båndet.

3. Klik på knappen 'Spor præcedenser' i gruppen 'Formelrevision'.

4. Gennemgå sporingspilene, som Excel viser.

De blå pile på billedet nedenfor viser dig, at celle D6 er afhængig af celleområdet A2:B5.

Brug af begge værktøjer hjælper dig med at finde Excel-cirkulære referencer, der ikke umiddelbart er synlige. Det næste trin er at løse problemet i den valgte celle. Du kan derefter fortsætte med at søge efter det samme problem i andre celler.

2 måder, hvordan du fjerner cirkulære referencer i Excel

Der er to hovedmetoder til at løse cirkulære referencer:

• bryde den cirkulære reference

• ved hjælp af alternative formler

Lad os forklare begge veje.

1. At bryde cirkulærehenvisningen

At bryde den cirkulære reference i Excel er en effektiv måde at løse problemet på. Dette kræver, at du identificerer de celler, der er involveret i den cirkulære reference og ændrer deres formler i overensstemmelse hermed.

For at gøre dette skal du følge trinene beskrevet tidligere for at finde celleadressen.

Du bør derefter undersøge formlen i formellinjen. Se efter en reference til selve cellen.

I vores første eksempel på en cirkulær reference inkluderede additionsformlen i celle C1 en reference til selve cellen:

=A1 + B1 + C1

Dette kan løses ved at fjerne den sidste tilføjelse. Når dette problem er løst, går du videre for at finde andre cirkulære referencer og adressere dem.

2. Brug alternative formler

Nogle af de mest almindelige årsager til cirkulære referencer er ved summering eller gennemsnit af celler. Fejlen er at inkludere resultatcellen i formlen.

I vores tidligere eksempel brugte formlen additionsoperatorer til at beregne summen af ​​et celleområde. Den alternative metode er at bruge SUM Excel-funktionen.

For at rette eksemplet erstatter du formlen med:

=SUM(A1:B1)

På samme måde kan du tage et gennemsnit af et interval med en formel som =(A1+B1)/2. Det er dog mindre sandsynligt, at funktionen AVERAGE fører til en fejl.

=MIDDEL(A1:B1)

5 tips til at forhindre cirkulære referencer

For at forhindre cirkulære referencer i Microsoft Excel er det vigtigt at have en klar forståelse af, hvordan dine formler interagerer med hinanden i dit Excel-ark.

Ved at identificere relationerne mellem celler og være opmærksom på, hvordan disse forbindelser fungerer sammen, kan du undgå at skabe cirkulære formler.

Her er vores fem bedste tips til at hjælpe dig med at forhindre cirkulære referencer.

1. Vær forsigtig, når du vælger områder

Efter min erfaring er en af ​​de mest almindelige årsager til cirkulære referencer, når brugere hurtigt vælger intervaller, der skal inkluderes i deres formler.

Når du stryger lodret og/eller vandret for at gruppere celler, er det nemt at vælge en celle mere, der støder op til det korrekte område. Hvis den celle også er resultatcellen, har du ved et uheld oprettet en cirkulær reference.

Denne fejl begås typisk, når man arbejder med store datasæt, såsom at køre chi-square-tests. Løsningen er at være forsigtig med dit udvalg.

2. Planlæg dine formler omhyggeligt

Inden du indtaster formler, skal du tænke over logikken bag dine beregninger, og hvordan de skal flyde på tværs af celler.

Du kan kortlægge beregningerne på forhånd ved hjælp af diagramværktøjer eller en tavle.

Dette kan hjælpe dig med at identificere eventuelle sløjfer, der kan føre til cirkulære referencer.

3. Brug alternative formler

Visse funktioner, såsom INDIREKTE eller OFFSET , kan utilsigtet skabe cirkulære referencer, når de bruges forkert.

Overvej at bruge alternative funktioner, der giver lignende funktionalitet uden risiko for cirkulære referencer.

4. Bryd komplekse formler op i mindre dele

I stedet for at oprette en enkelt kompleks formel med flere indlejrede funktioner, kan du overveje at opdele dine beregninger i mindre, mere håndterbare dele.

Dette gør ikke kun din projektmappe nemmere at forstå og vedligeholde, men reducerer også chancerne for at introducere en cirkulær reference.

5. Løs problemer, mens du går

Når du arbejder med store eller komplicerede projektmapper, er det også vigtigt at overvåge for cirkulære referencer. I stedet for at ignorere problemer, indtil et stort sæt data og formler er blevet samlet, er det bedre at rette hver fejl, efterhånden som den opstår.

Du kan vælge at ignorere en cirkulær referenceadvarselsmeddelelse i et kort stykke tid. Du bør dog bruge funktionen Fejltjek til at indhente de eksisterende cirkulære referencer inden alt for længe.

Dette gør dem nemmere at rette og forhindrer deres virkninger på din projektmappe.

Hvordan aktiverer man iterativ beregning og hvorfor gider?

Du ønsker normalt at undgå cirkulære referencer i dit arbejdsark. Der er dog nogle scenarier, hvor de er nødvendige.

For eksempel skal nogle tekniske eller videnskabelige beregninger bruge resultatet af en tidligere beregning. Dette er kendt som en iterativ løsning.

Hver sløjfe forfiner resultaterne af en beregning, indtil en specifik tærskel eller mål er nået.

I disse tilfælde er det vigtigt at aktivere iterative beregninger i Excel-indstillingerne for at tillade tilsigtede cirkulære referencer. Dette gør det muligt for softwaren at gentage beregningerne et bestemt antal gange, konvergerende til en løsning.

Følg disse trin for at lave et cirkulært opslagsværk i din Excel-fil:

1. Klik på fanen Filer.

2. Vælg Indstillinger.

3. Vælg kategorien Formler.

4. Under sektionen 'Beregningsmuligheder' skal du markere afkrydsningsfeltet for 'Aktiver iterativ beregning'.

5. Skift tallet i boksen med maksimalt gentagelser, eller lad være som standard på 100.

6. Brug den maksimale ændringsboks til at reducere gentagelser i henhold til dit krav.