Hvordan finne sirkulære referanser i Excel

Når du arbeider med , kan du av og til støte på det som kalles en sirkulær referanse. Dette er når en formel i en celle refererer tilbake til seg selv, og forårsaker en endeløs løkke i beregningsprosessen.

For å finne sirkulære referanser i Excel, bruk "Feilkontroll"-verktøyet under "Formler"-fanen for å identifisere celler med en sirkulær avhengighet.

Denne artikkelen viser deg hvordan du finner sirkulære referanser i Excel og fikser dem. Du vil også lære hvordan du kan forhindre dem når du arbeider med store eller komplekse regneark.

Innholdsfortegnelse

• Hva er sirkulære referanser
• Sirkulærreferanse-feilmeldingsvinduet
• Hvordan finne sirkulære referanser i Excel
• Hvordan finne sirkulære referanser med Excel-statuslinjen
• Hvordan finne skjulte sirkulære referanser i Excel
  • Hvordan bruke det sporavhengige verktøyet
  • Verktøy for sporing av presedenser
• 2 måter å fjerne sirkulære referanser i Excel
  • 2. Bruk alternative formler
• 5 tips for å forhindre sirkulære referanser
  • 1. Vær forsiktig når du velger områder
  • 2. Planlegg formlene nøye
  • 3. Bruk alternative formler
  • 4. Del opp komplekse formler i mindre deler
  • 5. Løs problemer mens du går
• Hvordan aktivere iterativ beregning og hvorfor bry seg?

Hva er sirkulære referanser

Sirkulære referanser forekommer i Excel når en formelberegning i en celle avhenger av cellens egen verdi. Dette skaper en loop og fører til uriktige eller uforutsigbare resultater.

Dette er lettere å forstå med et enkelt eksempel.

Anta at du har to tall i cellene A1 og B1. Du vil bruke en formel i celle A3 som legger de to tallene sammen. Formelen i celle A3 skal se slik ut:

=A1 + B1

Men hvis du gjør en feil og inkluderer C1 i beregningene, har du nå en sirkulær referanse:

=A1 + B1 + C1

Dette kan være et åpenbart problem å unngå når du har å gjøre med to celler. Men når du jobber med store celleområder, blir feil mer sannsynlig.

Heldigvis er Excel designet for å håndtere slike situasjoner ved å vise en advarsel når en sirkulær referanse oppdages.

I noen tilfeller kan du imidlertid støte på en sirkulær referanse uten å motta noe varsel. Derfor er det viktig å vite hvordan du finner og løser disse problemene manuelt.

Sirkulærreferanse-feilmeldingsvinduet

Som standard overvåker Excel for sirkulære referanser når du legger inn formler i celler. Når du trykker enter i en celle med et problem, vil et popup-vindu vise den sirkulære referansefeilmeldingen.

Dette er den forklarende teksten:

Det er en eller flere sirkulære referanser der en formel refererer til sin egen celle enten direkte eller indirekte. Dette kan føre til at de beregner feil. Prøv å fjerne eller endre disse referansene eller flytte formlene til forskjellige celler.

Når du ser denne dialogboksen, bør du klikke på "OK" og bruke metodene i denne artikkelen for å fikse problemet.

Du trenger imidlertid ikke å fikse feilen umiddelbart. Du kan fortsette å jobbe med regnearket med den hensikt å løse problemet senere.

I så fall må du vite hvordan du finner sirkulære referanser i Excel etter at du har avvist advarselsboksen.

Hvordan finne sirkulære referanser i Excel

Excel har et innebygd feilkontrollverktøy som finner flere typer feil, inkludert sirkulære referanser. Du får tilgang til verktøyet på Formler-fanen.

Dette bildet viser plasseringen i Formula Auditing-gruppen.

Nå vet du hvordan du finner verktøyet, her er de detaljerte trinnene for å bruke det:

1. Gå til Formler-fanen.

1. Klikk på rullegardinmenyen Error Checking i Formel Auditing-gruppen.

2. Velg "Sirkulære referanser" fra rullegardinlisten.

3. Klikk på koblingen for å hoppe markøren til den første cellen med et problem.

Bildet under viser at verktøyet har funnet en sirkulær feil i celle G1. Cellen som vises er en hyperkobling.

Når du klikker på koblingen, flyttes markøren til cellen. Dette er veldig nyttig med store datamengder.

Når du har løst den første sirkulære referansen, kan du gå tilbake til feilkontrollverktøyet for å finne den neste på arket.

Bruk trinnene i de senere delene av denne artikkelen for å fikse problemet og bruk verktøyet til å gå til neste problem.

Hvis det ikke er flere sirkulære problemer, vil menypunktet "Sirkulære referanser" i rullegardinmenyen være nedtonet. Du har ikke noe mer arbeid å gjøre.

Hvordan finne sirkulære referanser med Excel-statuslinjen

En annen måte å oppdage en sirkulær referanse på er å se på statuslinjen nederst i Excel-regnearket.

Når det er ett eller flere problemer i regnearket, viser statuslinjen plasseringen til én problemcellereferanse.

For eksempel, hvis det er en sirkulær referanse i celle C1 og det ikke er andre problemer med regnearket, vil statuslinjen lese: "Sirkulære referanser: C1".

Dette bildet viser deg hvor du kan se skjermen.

Hvordan finne skjulte sirkulære referanser i Excel

Skjulte sirkulære referanser i er sirkulære referanser som ikke umiddelbart oppdages av de innebygde feilfunksjonene. Excel viser med andre ord ikke standardadvarselen.

De skjer når en celle refererer til en annen celle indirekte, gjennom en serie mellomceller, og til slutt refererer tilbake til den opprinnelige cellen.

Excel har to verktøy som hjelper deg med å spore relasjoner og undersøke disse problemene:

1. Trace Dependents-verktøy

2. Verktøy for sporing av presedenser

Hvordan bruke det sporavhengige verktøyet

Erfarne Excel-brukere drar nytte av Trace Dependents-verktøyet for å undersøke disse problemene og fikse sirkulære referanser. Ved å velge én eller flere celler og bruke dette verktøyet, vil Excel vise deg de avhengige cellene.

Verktøyet er tilgjengelig i Formelrevisjon-delen på Formler-fanen.

Følg disse trinnene for å bruke verktøyet:

1. Klikk på cellen eller cellene du vil spore avhengige for.

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

3. Klikk på "Spor avhengige"-knappen i "Formelrevisjon"-gruppen.

4. Se gjennom sporingspilen eller pilene som viser avhengigheter.

Excel viser piler fra den aktive celleverdien til alle celler som er avhengige av den. Den blå pilen på bildet nedenfor viser at celle D6 er avhengig av celle B2.

Verktøy for sporing av presedenser

Presedenser er det motsatte av pårørende. Verktøyet Trace Precedents lar deg velge en celle med en formel og se alle cellene som den er avhengig av, dvs. presedensene.

Verktøyet er tilgjengelig i Formelrevisjon-delen på Formler-fanen.

Følg disse trinnene for å bruke verktøyet:

1. Klikk på cellen som inneholder formelen du vil undersøke.

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

3. Klikk på "Spor presedenser"-knappen i "Formelrevisjon"-gruppen.

4. Se gjennom sporingspilene som Excel viser.

De blå pilene på bildet nedenfor viser deg at celle D6 er avhengig av celleområdet A2:B5.

Ved å bruke begge verktøyene kan du finne sirkulære Excel-referanser som ikke er umiddelbart synlige. Det neste trinnet er å fikse problemet i den valgte cellen. Du kan deretter fortsette å søke etter det samme problemet i andre celler.

2 måter å fjerne sirkulære referanser i Excel

Det er to hovedmetoder for å løse sirkulære referanser:

• bryte den sirkulære referansen

• bruke alternative formler

La oss forklare begge veier.

1. Bryte sirkulærreferansen

Å bryte den sirkulære referansen i Excel er en effektiv måte å løse problemet på. Dette krever at du identifiserer cellene som er involvert i den sirkulære referansen og endrer deres formler tilsvarende.

For å gjøre dette, følg trinnene beskrevet tidligere for å finne celleadressen.

Du bør deretter undersøke formelen i formellinjen. Se etter en referanse til selve cellen.

I vårt første eksempel på en sirkulær referanse inkluderte addisjonsformelen i celle C1 en referanse til selve cellen:

=A1 + B1 + C1

Dette kan fikses ved å fjerne det endelige tillegget. Når dette problemet er løst, går du videre for å finne andre sirkulære referanser og adressere dem.

2. Bruk alternative formler

Noen av de vanligste årsakene til sirkulære referanser er ved summering eller gjennomsnitt av celler. Feilen er å inkludere resultatcellen i formelen.

I vårt forrige eksempel brukte formelen addisjonsoperatorer for å beregne summen av et celleområde. Den alternative metoden er å bruke SUM Excel-funksjonen.

For å fikse eksempelet, erstatter du formelen med:

=SUM(A1:B1)

På samme måte kan du gjennomsnitt et område med en formel som =(A1+B1)/2. Det er imidlertid mindre sannsynlig at AVERAGE-funksjonen fører til en feil.

=GJENNOMSNITT(A1:B1)

5 tips for å forhindre sirkulære referanser

For å forhindre sirkulære referanser i Microsoft Excel, er det viktig å ha en klar forståelse av hvordan formlene samhandler med hverandre i Excel-arket.

Ved å identifisere relasjonene mellom celler og være klar over hvordan disse forbindelsene fungerer sammen, kan du unngå å lage sirkulære formler.

Her er våre fem beste tips for å hjelpe deg med å forhindre sirkulære referanser.

1. Vær forsiktig når du velger områder

Etter min erfaring er en av de vanligste årsakene til sirkulære referanser når brukere raskt velger områder som skal inkluderes i formlene.

Når du sveiper vertikalt og/eller horisontalt for å gruppere celler, er det enkelt å velge en celle til som er ved siden av det riktige området. Hvis den cellen også er resultatcellen, har du ved et uhell opprettet en sirkulær referanse.

Denne feilen gjøres vanligvis når du arbeider med store sett med data, for eksempel å kjøre kjikvadrat-tester. Løsningen er å være forsiktig med utvalget ditt.

2. Planlegg formlene nøye

Før du skriver inn formler, tenk på logikken bak beregningene dine og hvordan de skal flyte på tvers av celler.

Du kan kartlegge beregningene på forhånd ved hjelp av diagramverktøy eller en tavle.

Dette kan hjelpe deg med å identifisere mulige løkker som kan føre til sirkulære referanser.

3. Bruk alternative formler

Enkelte funksjoner, for eksempel INDIREKTE eller FORSKJØVELSE , kan utilsiktet lage sirkulære referanser når de brukes feil.

Vurder å bruke alternative funksjoner som gir lignende funksjonalitet uten risiko for sirkulære referanser.

4. Del opp komplekse formler i mindre deler

I stedet for å lage en enkelt kompleks formel med flere nestede funksjoner, bør du vurdere å dele opp beregningene dine i mindre, mer håndterbare deler.

Dette gjør ikke bare arbeidsboken enklere å forstå og vedlikeholde, men reduserer også sjansene for å introdusere en sirkulær referanse.

5. Løs problemer mens du går

Når du arbeider med store eller kompliserte arbeidsbøker, er det også viktig å overvåke for sirkulære referanser. I stedet for å ignorere problemer før et stort sett med data og formler er satt sammen, er det bedre å fikse hver feil etter hvert som den oppstår.

Du kan velge å ignorere en sirkulær referanseadvarsel for en kort stund. Du bør imidlertid bruke funksjonen Feilkontroll for å få med deg de eksisterende sirkulære referansene før alt for lenge.

Dette gjør dem lettere å fikse og forhindrer virkningene på arbeidsboken din.

Hvordan aktivere iterativ beregning og hvorfor bry seg?

Du vil vanligvis unngå sirkulære referanser i regnearket ditt. Imidlertid er det noen scenarier der de er nødvendige.

For eksempel må noen tekniske eller vitenskapelige beregninger bruke resultatet av en tidligere beregning. Dette er kjent som en iterativ løsning.

Hver sløyfe avgrenser resultatene av en beregning til en bestemt terskel eller mål er nådd.

I disse tilfellene er det viktig å aktivere Iterative Calculations i Excel-innstillingene for å tillate tiltenkte sirkulære referanser. Dette gjør at programvaren kan gjenta beregningene et spesifisert antall ganger, og konvergere til en løsning.

Følg disse trinnene for å lage et sirkulært referanseverk i Excel-filen din:

1. Klikk på Fil-fanen.

2. Velg Alternativer.

3. Velg kategorien Formler.

4. Under delen "Beregningsalternativer" merker du av for "Aktiver iterativ beregning".

5. Endre tallet i boksen for maksimale iterasjoner eller la være 100 som standard.

6. Bruk den maksimale endringsboksen for å redusere iterasjoner i henhold til ditt krav.