Hvordan finne sirkulære referanser i Excel på 4 måter

Så du sirkulærreferansefeilen på et Excel-ark og lurte på om regnearket er ødelagt? Stol på meg! Du er ikke den første som møter denne Excel-feilen. Hundre og tusenvis av Excel-brukere opplever sirkulære referansefeil hver dag. Ikke vær redd! Slik finner du sirkulære referanser i Excel og løser dem.

Som en ivrig Excel-bruker skriver du sjelden de samme dataene i mange celler. I stedet importerer du et datasett fra kilden og bruker cellereferanser til å lage formler. Dermed lager du et dynamisk regneark.

Når noen endrer celleverdiene til cellereferansene, oppdaterer regnearket seg selv automatisk. Dette kalles dataanalyseautomatisering og dataanalytikere elsker å leke med cellereferanser.

Problemet kommer imidlertid når du med vilje eller ved et uhell skriver inn celleadressen der du for øyeblikket utfører en beregning. Eller, når du lager mange formler gjennom regnearket, vises en celle som inneholder en formel indirekte i en annen formel.

I slike og mange andre lignende situasjoner får du den sirkulære referansefeilen i Excel. Les videre for å lære hva sirkulær referanse er, dens grunner, hvorfor du bør unngå det, og viktigst av alt hvordan du finner sirkulære referanser i Excel for å bli kvitt disse anomaliene for godt.

Excel støter på en sirkulær referansefeil når den ser at en eller flere funksjoner i regnearket refererer tilbake til celleadressen der funksjonene deres ble opprettet.

Det kan høres forvirrende ut for deg, så tenk på hvor alvorlig det kan være for Excel-appen. Kort sagt, du vil aldri legge celleadressen i en formel der du oppretter den. Se på følgende eksempel for en visuell avklaring:

Du prøver å formere deg 10 fra celle B1 og legg resultatet i cellen B1 seg selv.

Nå, i scenariet ovenfor, hvis du bare trykker OK, vil Excel lukke feilen og vise null som det beregnede resultatet. Dette er bare et enkelt eksempel.

Tenk deg nå at det er hundrevis av formler i et regneark for bokføring, regneark for økonomiplanlegging, ansattlønn, regneark for leverandørbetaling og så videre.

Du vil stole på de feilaktige dataene beregnet av Excel på grunn av den sirkulære referansefeilen, og det vil igjen påvirke din bedrift eller profesjonelle beslutningstaking.

Videre kan sirkulær referanse redusere hastigheten på beregninger i Excel-appen betydelig. I alvorlige tilfeller kan selve feilen føre til ekstremt treg ytelse på PC-en også hvis den ikke håndteres umiddelbart.

Men hvis du jobber med avanserte Excel-formler der datamodellen din krever mellomliggende input fra den sirkulære referansen for å fortsette med en lang funksjon, må du kanskje bruke den. Faktisk lar Excel deg bruke denne beregningsmetoden når det er nødvendig.

For eksempel, i energiproduksjonsenheter, som vindmøller, må du beregne resirkulert utgangsenergi fra en brøkdel av tilført energi. Alternativt, i et kjemisk anlegg, bruker ingeniører utgangsstammen som input for samme eller en annen kjele eller prosessbeholder. Slike tekniske beregninger på høyt nivå krever faktisk sirkulære referanser for iterative beregninger.

Det er tydelig at popup-vinduet med sirkulær referanse ikke er en feilmelding. Det er en måte for Excel å informere deg om at den fant sirkulær referanse i en eller flere formler, og som kan påvirke beregningsalgoritmen og ytelsen.

For å unngå skader forårsaket av sirkulære referanser i Excel-formler, må du vite når de vises og når de ikke vises. Finn forekomstene nedenfor:

• I et hvilket som helst åpent regneark eller arbeidsbok oppretter du en formel og skriver inn den sirkulære referansen for første gang.
• Du eller noen fjernet alle sirkulære referanser fra Excel-filen og lagret den. Nå skriver du eller en annen bruker inn en sirkulær referanse i samme fil.
• Du lukket alle åpne arbeidsbøker. Nå åpnet en annen arbeidsbok og skriv deretter inn en formel som inneholder en sirkulær referanse.

• Du har nettopp åpnet en arbeidsbok som tidligere inneholdt en eller flere sirkulære referanser. Du vil se advarselen.
• Når Excel har vist deg den sirkulære referanseadvarselsmeldingen én gang og du klikket OK, vil den ikke vise det samme popup-vinduet lenger i samme økt selv om du fortsetter å lage flere sirkulære refererte formler.

Derfor må du nøye se etter tilfellene ovenfor for å fange sirkulære referanseadvarsler og løse dem hvis du ikke leter etter iterative beregninger.

Hvis du vil lære hvordan du finner sirkulære referanser i Excel, må du forstå typene deres. Fordi å finne og eliminere sirkulære referanser i Excel ikke er en automatisert oppgave. Finn de vanlige typene av denne feilen nedenfor:

• Tiltenkte sirkulære referanser oppstår når du bruker dem med vilje til iterative beregningsformål. For eksempel må du legge inn et tidsstempel på en annen celle når noen skriver inn en verdi i en referert celle ved å bruke flyktige og logiske funksjoner som HVIS, ELLER, SUMIF, osv.

• Utilsiktet sirkulær referanse når du prøver å jobbe med en Excel-formel raskere og refererer til den formelinneholdende cellen feilaktig. For eksempel må du bruke SUM funksjon fra celle A1 til A9 og hent resultatet i cellen A10. Men feilaktig skriver du formelen slik på celle A10:

=SUM(A2:A10)

• Skjulte sirkulære referanser er de som ikke viser noen advarselsmeldinger når du oppretter en formel eller åpner regnearket for første gang. Det skjer fordi noen tillot iterativ beregning på Excel-filen.

• Indirekte sirkulære referanser er også populære, og de oppstår når du oppretter en ny formel i en hvilken som helst celle, for eksempel celle A1, og denne nye formelen vil bruke en annen funksjon i en annen celle, som celle B1, som allerede bruker celle A1 som referanse.

Nå som du forsto det grunnleggende om sirkulære referanser i Excel, la oss finne ut hvordan du finner sirkulære referanser i Excel nedenfor:

Forutsatt at den iterative beregningsfunksjonen er deaktivert, kan du se følgende varsler om sirkulære referanser på Excel-statuslinjen:

• Sirkulære referanser: C3 (eller en hvilken som helst annen cellereferanse) når du er på et regneark som inneholder sirkulær formel i arbeidsboken.

• Du vil bare se Sirkulære referanser hvis du er på et regneark som ikke inneholder noen, men arbeidsboken har noen av disse sirkulære formlene.

Det er den beste måten å se etter sirkulære referanser i en arbeidsbok eller et regneark. Her er trinnene du må vite:

• Åpne et regneark som du mistenker for å inneholde sirkulære formler.
• Klikk på Formler menyen på Excel-båndet.

• I Formelrevisjon kommandogruppe, klikk på Feilkontrollnedtrekksliste.
• Hold markøren over Sirkulære referanser alternativet for å se alle tilgjengelige forekomster i alle regneark.
• Velg hver forekomst og Excel vil lokalisere disse cellene automatisk.

Hvis du ikke finner feilkontrollmetoden effektiv nok til å oppdage et stort antall formler med sirkulære referanser, kan du prøve disse trinnene for å bruke formelsporingsmetoden:

• Velg en hvilken som helst referert celle eller cellen som inneholder formelen.
• Gå til Formler på Excel-båndmenyen og klikk deretter Spor presedenser.
• Excel vil markere cellene som påvirker formelresultatet på gjeldende celle med piler.

• Du kan også klikke Spor avhengige for å oppdage celler påvirket av verdien til den valgte cellereferansen.
• Hvis Excel ikke viser noen feil etter at du har klikket på kommandoene ovenfor, er det ingen sirkulære referanser i regnearket.
• Gjenta trinnene på alle regnearkene for å sjekke hele arbeidsboken.

Denne metoden er spesielt viktig når de iterative beregningsfunksjonene er aktive på Excel-regnearket.

• Klikk på Fil fanen på Excel-båndet og åpne deretter Excel-alternativer fra sidefeltet som vises.
• På Excel-alternativer dialogboksen, velg Formler menyen fra sidefeltet og fjern deretter merket for Aktiver iterativ beregning.

• Klikk OK for å lagre endringene.
• Nå vil arbeidsboken eller regnearket automatisk vise deg den sirkulære referanseadvarselen.
• Hvis den ikke vises umiddelbart, lagre arbeidsboken, lukk den og åpne den på nytt for å se advarselen.
• Når du ser advarselen, sjekk Excel-statuslinje å vite hvilken celle som er i den sirkulære referansen.

Den sikre måten å korrigere en eller flere sirkulære referansefeil på Excel er ved å manuelt redigere hver formel som inneholder den. På denne måten kan du løse både tilfeldige og indirekte sirkulære referanser i Excel.

En annen enkel måte er å fjerne formelen fra gjeldende celle og plassere den et annet sted i det samme regnearket.

I likhet med Excel, tillater ikke Google Sheets noen sirkulære referanser i én eller flere formler hvis du ikke har aktivert den iterative beregningsfunksjonen. Derfor vil Google Regneark umiddelbart vise #REF! feil på cellen som inneholder en sirkulær formel.

Derfor kan du ganske enkelt velge alle cellene som inneholder #REF! advarsel om å finne og løse sirkulære referanser på Google Sheets.

Så nå vet du alle hemmelighetene for hvordan du finner sirkulære referanser i Excel. Bruk metoden du liker og slipp alle sirkulære referanser for å sikre at du ikke blir forvirret av upålitelige data fra en Excel-rapport eller matematisk dashbord.

Neste opp, den beste Excel-kalendermaler for ethvert år og beste Excel-budsjettmaler.