Hvordan finne sirkulære referanser i Excel for å unngå feilaktige data

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 oppstår 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.

Hva er en sirkulær referanse i Excel?

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 å multiplisere 10 fra celle B1 og sette resultatet i selve celle B1 .

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.

Hva er sirkulær referanse i Excel

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.

Når ser du sirkulærreferansefeilen i Excel?

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 én 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.

Når ser du sirkulærreferansefeilen i Excel

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

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

Typer sirkulære referanser i Excel

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 er ikke en automatisert oppgave. Finn de vanlige typene av denne feilen nedenfor:

Et eksempel på sirkulær referansebruk

• 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 rundskriv i Excel

• 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- funksjonen fra celle A1 til A9 og hente resultatet i celle 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.

Et eksempel på en indirekte sirkulær referanse

• 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:

Slik finner du sirkulære referanser i Excel: Fra statuslinjen

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 annen cellereferanse) når du er på et regneark som inneholder sirkulær formel i arbeidsboken.

Viser sirkulære referanser i Excel Status Bar

• 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.

Hvordan finne sirkulære referanser i Excel: Bruke feilkontroll

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.

Bruk feilsøking for Hvordan finne sirkulære referanser i Excel

• I kommandogruppen Formelrevisjon klikker du på rullegardinlisten Feilkontroll  .
• Hold markøren over alternativet Circular References for å se alle tilgjengelige forekomster i alle regneark.
• Velg hver forekomst og Excel vil lokalisere disse cellene automatisk.

Hvordan finne sirkulære referanser i Excel: Fra Formelsporing

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- fanen på Excel-båndmenyen og klikk deretter Trace Precedents .
• Excel vil markere cellene som påvirker formelresultatet på gjeldende celle med piler.

Bruk formelsporing for Hvordan finne sirkulære referanser i Excel

• Du kan også klikke på Spor avhengige for å oppdage celler som er 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.

Hvordan finne sirkulære referanser i Excel: Bruke Excel-alternativer

• Klikk på Fil- fanen på Excel-båndet og åpne deretter Excel-alternativer fra sidefeltet som vises.
• I dialogboksen for Excel-alternativer velger du Formler- menyen fra sidefeltet og fjerner merket for Aktiver iterativ beregning .

Slik deaktiverer du iterativ beregning i Excel

• 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-statuslinjen for å vite hvilken celle som er i den sirkulære referansen.

Hvordan løse sirkulære referanser i Excel

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.

Hvordan løse 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.

Hvordan finne sirkulære referanser i Google Sheets (bonusmetode)

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.

Google Sheets Slik finner du sirkulære referanser

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

Excel Circular References: Sluttord

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, de  beste Excel-kalendermalene for ethvert år  og de  beste Excel-budsjettmalene .