En av de vanligste feiladvarslene brukere møter i Excel er "Sirkulærreferansen". Tusenvis av brukere har det samme problemet, og det oppstår når en formel refererer tilbake til sin egen celle direkte eller indirekte, og forårsaker en endeløs løkke av beregninger.
For eksempel har du to verdier i cellene B1 og B2. Når formelen =B1+B2 legges inn i B2, lager den en sirkulær referanse; formelen i B2 omregnerer seg selv gjentatte ganger fordi hver gang den beregner, har B2-verdien endret seg.
De fleste sirkulære referanser er utilsiktede feil; Excel vil advare deg om disse. Imidlertid er det også tiltenkte sirkulære referanser, som brukes til å gjøre iterative beregninger. Utilsiktede sirkulære referanser i regnearket kan føre til at formelen din beregnes feil.
Derfor vil vi i denne artikkelen forklare alt du trenger å vite om sirkulære referanser, og hvordan du finner, fikser, fjerner og bruker sirkulære referanser i Excel.
Hvordan finne og håndtere sirkulær referanse i Excel
Når du arbeider med Excel, støter vi noen ganger på sirkulære referansefeil som oppstår når du skriver inn en formel som inkluderer cellen der formelen din ligger. I utgangspunktet skjer det når formelen din prøver å beregne seg selv.
For eksempel har du en kolonne med tall i celle A1:A4 og du bruker SUM-funksjonen (=SUM(A1:A5)) i celle A5. Cellen A5 refererer direkte til sin egen celle, noe som er feil. Derfor vil du få følgende sirkulære referanseadvarsel:
Når du har fått advarselsmeldingen ovenfor, kan du klikke på "Hjelp"-knappen for å vite mer om feilen, eller lukke feilmeldingsvinduet ved å klikke enten "OK" eller "X"-knappen og få "0" som resultat.
Noen ganger kan sirkulære referanseløkker føre til at beregningen krasjer eller reduserer ytelsen til regnearket. Sirkulær referanse kan også føre til en rekke andre problemer, som ikke vil være tydelige umiddelbart. Så det er best å unngå disse.
Direkte og indirekte sirkulære referanser
Sirkulære referanser kan kategoriseres i to typer: direkte sirkulære referanser og indirekte sirkulære referanser.
Direkte referanse
En direkte sirkulær referanse er ganske enkel. Den direkte sirkulære referanseadvarselsmeldingen dukker opp når formelen refererer tilbake til sin egen celle direkte.
I eksemplet nedenfor refererer formelen i celle A2 direkte til sin egen celle (A2).
Når advarselsmeldingen dukker opp, kan du klikke på "OK", men det vil bare resultere i "0".
Indirekte rundskriv
En indirekte sirkulær referanse i Excel oppstår når en verdi i en formel refererer tilbake til sin egen celle, men ikke direkte. Med andre ord kan sirkulær referanse dannes av to celler som refererer til hverandre.
La oss forklare med dette enkle eksemplet.
Nå starter verdien fra A1 som har verdien 20.
Deretter refererer celle C3 til celle A1.
Deretter refererer celle A5 til celle C3.
Erstatt nå verdien 20 i celle A1 med formelen som vist nedenfor. Annenhver celle er avhengig av celle A1. Når du bruker en referanse til en annen tidligere formelcelle i A1, vil det forårsake en sirkulær referanseadvarsel. Fordi formelen i A1 refererer til celle A5, som refererer til C3, og celle C3 refererer tilbake til A1, derav den sirkulære referansen.
Når du klikker "OK", resulterer det i en verdi på 0 i celle A1, og Excel oppretter en koblet linje som viser sporingspresedenser og sporingsavhengige som vist nedenfor. Vi kan bruke denne funksjonen til å enkelt finne og fikse/fjerne sirkulære referanser.
Slik aktiverer / deaktiverer du sirkulære referanser i Excel
Som standard er iterative beregninger slått av (deaktivert) i Excel. Iterative beregninger er repeterende beregninger til den oppfyller en bestemt betingelse. Når den er deaktivert, viser Excel en sirkulær referansemelding og returnerer en 0 som resultat.
Noen ganger er det imidlertid nødvendig med sirkulære referanser for å beregne en sløyfe. For å bruke sirkulær referanse, må du aktivere iterative beregninger i Excel, og det vil tillate deg å utføre beregningene dine. La oss nå vise deg hvordan du kan aktivere eller deaktivere iterative beregninger.
I Excel 2010, Excel 2013, Excel 2016, Excel 2019 og Microsoft 365, gå til "Fil"-fanen i øvre venstre hjørne av Excel, og klikk deretter "Alternativer" i venstre rute.
I vinduet for Excel-alternativer, gå til "Formel"-fanen og merk av for "Aktiver iterativ beregning" under delen "Beregningsalternativer". Klikk deretter "OK" for å lagre endringene.
Dette vil muliggjøre iterativ beregning og dermed tillate sirkulær referanse.
Følg disse trinnene for å oppnå dette i tidligere versjoner av Excel:
- I Excel 2007 klikker du på Office-knappen > Excel-alternativer > Formler > Iterasjonsområde.
- I Excel 2003 og tidligere versjoner må du gå til Meny > Verktøy > Alternativer > kategorien Beregning.
Maksimale iterasjoner og maksimale endringsparametere
Når du har aktivert iterative beregninger, kan du kontrollere de iterative beregningene ved å spesifisere to tilgjengelige alternativer under Aktiver iterativ beregning-delen som vist i skjermbildet nedenfor.
- Maksimal gjentakelse – Dette tallet angir hvor mange ganger formelen skal beregnes på nytt før du får det endelige resultatet. Standardverdien er 100. Hvis du endrer den til '50', vil Excel gjenta beregningene 50 ganger før du får det endelige resultatet. Husk at jo høyere antall iterasjoner, jo mer ressurser og tid tar det å beregne.
- Maksimal endring – Den bestemmer den maksimale endringen mellom beregningsresultatene. Denne verdien bestemmer nøyaktigheten til resultatet. Jo mindre tall, desto mer nøyaktig resultat vil resultatet bli, og jo lengre tid tar det å beregne regnearket.
Hvis alternativet for iterative beregninger er aktivert, vil du ikke få noen advarsel når det er en sirkulær referanse i regnearket ditt. Aktiver kun interaktiv beregning når det er absolutt nødvendig.
Finn sirkulær referanse i Excel
Anta at du har et stort datasett og du har den sirkulære referanseadvarselen, må du fortsatt finne ut hvor (i hvilken celle) feilen har oppstått for å fikse den. Følg disse trinnene for å finne sirkulære referanser i Excel:
Bruker feilkontrollverktøyet
Først åpner du regnearket der den sirkulære referansen har skjedd. Gå til «Formel»-fanen, klikk på pilen ved siden av «Feilkontroll»-verktøyet. Hold deretter markøren over alternativet "Sirkulære referanser", Excel vil vise deg listen over alle celler som er involvert i den sirkulære referansen som vist nedenfor.
Klikk på hvilken celleadresse du vil ha i listen, og den tar deg til den celleadressen for å løse problemet.
Bruker statuslinjen
Du kan også finne sirkulærreferansen på statuslinjen. På Excels statuslinje vil den vise deg den siste celleadressen med en sirkulær referanse, for eksempel 'Sirkulære referanser: B6' (se skjermbilde nedenfor).
Det er visse ting du bør vite når du håndterer rundskriv:
- Statuslinjen vil ikke vise den sirkulære referansecelleadressen når alternativet Iterativ beregning er aktivert, så du må deaktivere det før du begynner å se på arbeidsboken for sirkulære referanser.
- I tilfelle sirkulær referanse ikke finnes i det aktive arket, viser statuslinjen bare 'Sirkulære referanser' uten celleadresse.
- Du vil bare få en sirkulær referansemelding én gang, og etter at du har klikket "OK", vil den ikke vise forespørselen igjen neste gang.
- Hvis arbeidsboken har sirkulære referanser, vil den vise deg ledeteksten hver gang du åpner den til du løser den sirkulære referansen eller til du slår på iterativ beregning.
Fjern en sirkulær referanse i Excel
Det er enkelt å finne sirkulære referanser, men det er ikke så enkelt å fikse det. Dessverre er det ikke noe alternativ i Excel som lar deg fjerne alle sirkulære referanser på en gang.
For å fikse sirkulære referanser, må du finne hver sirkulær referanse individuelt og prøve å endre den, fjerne den sirkulære formelen helt eller erstatte den med en annen.
Noen ganger, i enkle formler, er alt du trenger å gjøre å justere parametrene til formelen slik at den ikke refererer tilbake til seg selv. Endre for eksempel formelen i B6 til =SUM(B1:B5)*A5 (endre B6 til B5).
Det vil returnere resultatet av beregningen som '756'.
I tilfeller der en Excel-sirkulærreferanse er vanskelig å finne, kan du bruke funksjonene Trace Precedents og Trace Dependents for å spore den tilbake til kilden og løse den én etter én. Pilen viser hvilke celler som påvirkes av den aktive cellen.
Det er to sporingsmetoder som kan hjelpe deg å slette sirkulære referanser ved å vise relasjonene mellom formler og celler.
For å få tilgang til sporingsmetodene, gå til "Formler"-fanen, og klikk deretter enten "Sporprecedenter" eller "Sporingsavhengige" i Formelrevisjon-gruppen.
Spor presedenser
Når du velger dette alternativet, sporer det tilbake cellene som påvirker den aktive cellens verdi. Den tegner en blå linje som indikerer hvilke celler som påvirker gjeldende celle. Hurtigtasten for å bruke sporpresedenser er Alt + T U T.
I eksemplet nedenfor viser den blå pilen cellene som påvirker B6-verdien er B1:B6 og A5. Som du kan se nedenfor, er celle B6 også en del av formelen, noe som gjør den til en sirkulær referanse og får formelen til å returnere '0' som resultat.
Dette kan enkelt fikses ved å erstatte B6 med B5 i SUMs argument: =SUM(B1:B5).
Spor avhengige
Sporingsavhengige-funksjonen sporer cellene som er avhengige av den valgte cellen. Denne funksjonen tegner en blå linje som indikerer hvilke celler som påvirkes av den valgte cellen. Det vil si at den viser hvilke celler som inneholder formler som refererer til den aktive cellen. Hurtigtasten for å bruke pårørende er Alt + T U D.
I følgende eksempel er celle D3 påvirket av B4. Den er avhengig av B4 for å gi resultater. Derfor trekker sporavhengig en blå linje fra B4 til D3, noe som indikerer at D3 er avhengig av B4.
Bevisst bruk av sirkulære referanser i Excel
Det anbefales ikke å bruke sirkulære referanser bevisst, men det kan være noen sjeldne tilfeller der du trenger en sirkulær referanse slik at du kan få utdataene du ønsker.
La oss forklare det ved å bruke et eksempel.
Til å begynne med, aktiver 'Iterativ beregning' i Excel-arbeidsboken. Når du har aktivert Iterativ beregning, kan du begynne å bruke sirkulære referanser til din fordel.
La oss anta at du kjøper et hus og du vil gi en 2% provisjon på den totale kostnaden for huset til agenten din. Totalkostnaden vil bli beregnet i celle B6 og provisjonsprosenten (agenthonorar) beregnes i B4. Provisjonen beregnes av totalkostnaden og totalkostnaden inkluderer provisjonen. Siden cellene B4 og B6 er avhengige av hverandre, skaper det en sirkulær referanse.
Skriv inn formelen for å beregne totalkostnaden i celle B6:
=SUM(B1:B4)
Siden totalkostnaden inkluderer agenthonoraret, inkluderte vi B4 i formelen ovenfor.
For å beregne agenthonorar på 2 %, sett inn denne formelen i B4:
=B6*2 %Nå avhenger formelen i celle B4 av verdien av B6 for å beregne 2% av det totale gebyret, og formelen i B6 avhenger av B4 for å beregne den totale kostnaden (inkludert agentavgift), derav den sirkulære referansen.
Hvis den iterative beregningen er aktivert, vil ikke Excel gi deg en advarsel eller en 0 i resultatet. I stedet vil resultatet av celle B6 og B4 bli beregnet som vist ovenfor.
Alternativet for iterative beregninger er vanligvis deaktivert som standard. Hvis du ikke har slått den på og når du skriver inn formelen i B4 som vil lage en sirkulær referanse. Excel vil gi advarselen, og når du klikker "OK", vil sporingspilen vises.
Det er det. Dette var alt du trenger å vite om sirkulære referanser i Excel.