Når du arbeider med Excel -formler, kan du en gang se følgende advarsel.
Denne ledeteksten forteller deg at det er en sirkulær referanse i regnearket, og dette kan føre til eller feil beregning av formlene. Den ber deg også om å ta opp dette sirkulære referansespørsmålet og sortere det.
I denne opplæringen vil jeg dekke alt du trenger å vite om sirkulær referanse, samt hvordan finne og fjerne sirkulære referanser i Excel.
Så la oss komme i gang!
Hva er sirkulær referanse i Excel?
I enkle ord skjer en sirkulær referanse når du ender opp med å ha en formel i en celle - som i seg selv bruker cellen (der den er lagt inn) for beregningen.
La meg prøve å forklare dette med et enkelt eksempel.
Anta at du har datasettet i celle A1: A5, og at du bruker formelen nedenfor i celle A6:
= SUMMER (A1: A6)
Dette vil gi deg en sirkulær referanse advarsel.
Dette er fordi du vil summere verdiene i celle A1: A6, og resultatet skal være i celle A6.
Dette skaper en sløyfe da Excel bare fortsetter å legge til den nye verdien i celle A6, som fortsetter å endre seg (derav en sirkulær referansesløyfe).
Hvordan finne sirkulære referanser i Excel?
Selv om advarselen om sirkulær referanse er snill nok til å fortelle deg at den finnes i regnearket, forteller den deg ikke hvor det skjer og hvilke cellereferanser som forårsaker det.
Så hvis du prøver å finne og håndtere sirkulære referanser i regnearket, må du vite en måte å finne disse på en eller annen måte.
Nedenfor er trinnene for å finne en sirkulær referanse i Excel:
- Aktiver regnearket som har sirkulær referanse
- Klikk kategorien Formler
- I formelredigeringsgruppen klikker du på rullegardinikonet Feilkontroll (liten pil nedover til høyre)
- Hold markøren over alternativet Sirkulære referanser. Den viser deg cellen som har en sirkulær referanse i regnearket
- Klikk på celleadressen (som vises), og den tar deg til cellen i regnearket.
Når du har løst problemet, kan du igjen følge de samme trinnene ovenfor, og det vil vise flere cellereferanser som har sirkulær referanse. Hvis det ikke er noen, vil du ikke se noen cellereferanse,
En annen rask og enkel måte å finne den sirkulære referansen på er ved å se på statuslinjen. På venstre del av den vil den vise deg teksten Sirkulær referanse sammen med celleadressen.
Det er noen ting du trenger å vite når du arbeider med sirkulære referanser:
- Hvis den iterative beregningen er aktivert (dekket senere i denne opplæringen), vil ikke statuslinjen vise den sirkulære referansecelleadressen
- Hvis sirkulærreferansen ikke er i det aktive arket (men i andre ark i den samme arbeidsboken), vil den bare vise sirkulærreferanse og ikke celleadressen
- Hvis du får en sirkulær advarselsvarsel en gang og du avviser den, vil den ikke vise meldingen igjen neste gang.
- Hvis du åpner en arbeidsbok som har sirkulær referanse, vil den vise deg ledeteksten så snart arbeidsboken åpnes.
Hvordan fjerner jeg en sirkulær referanse i Excel?
Når du har identifisert at det er sirkulære referanser i arket ditt, er det på tide å fjerne disse (med mindre du vil at de skal være der av en grunn).
Dessverre er det ikke så enkelt som å trykke på slette -tasten. Siden disse er avhengige av formler og hver formel er forskjellig, må du analysere dette fra sak til sak.
Hvis det bare er et spørsmål om at cellereferansen forårsaker problemet ved en feiltakelse, kan du bare rette ved å justere referansen.
Men noen ganger er det ikke så enkelt.
Sirkulær referanse kan også forårsakes basert på flere celler som mates inn i hverandre på mange nivåer.
La meg vise deg et eksempel.
Nedenfor er det en sirkulær referanse i celle C6, men det er ikke bare et enkelt tilfelle av selvreferanse. Det er flernivå der cellene det bruker i beregningene også refererer til hverandre.
- Formlene i celle A6 er = SUM (A1: A5)+C6
- Formelen er celle C1 er = A6*0,1
- Formelen i celle C6 er = A6+C1
I eksemplet ovenfor er resultatet i celle C6 avhengig av verdiene i celle A6 og C1, som igjen er avhengige av celle C6 (og dermed forårsake sirkulær referansefeil)
Og igjen, jeg har valgt et veldig enkelt eksempel bare for demoformål. I virkeligheten kan disse være ganske vanskelige å finne ut og kanskje langt unna i det samme regnearket eller til og med spredt over flere regneark.
I et slikt tilfelle er det en måte å identifisere cellene som forårsaker sirkulær referanse og deretter behandle disse.
Det er ved å bruke alternativet Trace Precedents.
Nedenfor er trinnene for å bruke sporforløp for å finne celler som mates til cellen som har sirkulær referanse:
- Velg cellen som har sirkulær referanse
- Klikk kategorien Formler
- Klikk på Trace Precedents
Trinnene ovenfor viser deg blå piler som forteller deg hvilke celler som mates inn i formelen i den valgte cellen. På denne måten kan du inspisere formlene og cellene og bli kvitt den sirkulære referansen.
Hvis du jobber med komplekse økonomiske modeller, kan det være mulig at disse presedensene også går dypere på flere nivåer.
Dette fungerer bra hvis du har alle formlene som refererer til celler i det samme regnearket. Hvis den er i flere regneark, er denne metoden ikke effektiv.
Slik aktiverer/deaktiverer du iterative beregninger i Excel
Når du har en sirkulær referanse i en celle, får du først advarselen som vist nedenfor, og hvis du lukker denne dialogboksen, vil den gi deg 0 som resultatet i cellen.
Dette er fordi når det er en sirkulær referanse, er det en endeløs sløyfe og Excel vil ikke bli fanget opp av det. Så det gir en 0.
Men i noen tilfeller vil du kanskje at sirkulærreferansen skal være aktiv og gjøre et par iterasjoner. I et slikt tilfelle, i stedet for en uendelig sløyfe, og du kan bestemme hvor mange ganger løkken skal kjøres.
Dette kalles iterativ beregning i Excel.
Nedenfor er trinnene for å aktivere og konfigurere iterative beregninger i Excel:
- Klikk kategorien Fil
- Klikk på Alternativer. Dette åpner dialogboksen Alternativer for Excel
- Velg Formel i den venstre ruten
- I delen Beregningsalternativer merker du av i boksen ‘Aktiver iterativ beregning’. Her kan du angi maksimal iterasjoner og maksimal endringsverdi
Det er det! Trinnene ovenfor vil muliggjøre iterativ beregning i Excel.
La meg også raskt forklare de to alternativene i den iterative beregningen:
- Maksimal gjentakelse: Dette er det maksimale antallet ganger du vil at Excel skal beregne før du gir deg det endelige resultatet. Så hvis du angir dette som 100, vil Excel kjøre sløyfen 100 ganger før du gir deg det endelige resultatet.
- Maksimal endring: Dette er den maksimale endringen, som hvis ikke oppnås mellom iterasjoner, vil beregningen bli stoppet. Som standard er verdien .001. Jo lavere denne verdien er, desto mer nøyaktig blir resultatet.
Husk at jo flere ganger iterasjonene kjøres, jo mer tid og ressurser tar det for Excel å gjøre det. Hvis du holder de maksimale gjentakelsene høye, kan det føre til at Excel bremser eller krasjer.
Merk: Når iterative beregninger er aktivert, vil Excel ikke vise deg advarselen om sirkulær referanse, og den vil nå også vises på statuslinjen.Bevisst bruk av sirkulære referanser
I de fleste tilfeller vil tilstedeværelsen av sirkulær referanse i regnearket være en feil. Og det er derfor Excel viser deg en melding som sier: "Prøv å fjerne eller endre disse referansene, eller flytt formlene til forskjellige celler."
Men det kan være noen spesifikke tilfeller der du trenger en sirkulær referanse slik at du kan få ønsket resultat.
En slik spesifikk sak har jeg allerede skrevet om at den får tidsstempelet i en celle i en celle i Excel.
Anta for eksempel at du vil lage en formel slik at hver oppføring blir gjort i en celle i kolonne A, tidsstempelet vises i kolonne B (som vist nedenfor):
Mens du enkelt kan sette inn et tidsstempel ved å bruke formelen nedenfor:
= HVIS (A2 "", HVIS (B2 "", B2, NÅ ()), "")
Problemet med formelen ovenfor er at den vil oppdatere alle tidsstemplene så snart endringer er gjort i regnearket eller hvis regnearket åpnes igjen (ettersom NÅ -formelen er flyktig)
For å omgå dette problemet kan du bruke en sirkulær referansemetode. Bruk samme formel, men aktiver iterativ beregning.
Det er også noen andre tilfeller der det er ønskelig å ha muligheten til å bruke sirkulær referanse (du kan finne et eksempel her).
Merk: Selv om det kan være noen tilfeller der du kan bruke sirkulær referanse, synes jeg det er best å unngå å bruke den. Sirkulære referanser kan også påvirke arbeidsbokens ytelse og kan gjøre den treg. I sjeldne tilfeller der du trenger det, foretrekker jeg alltid å bruke VBA -koder for å få jobben gjort.Jeg håper du synes denne opplæringen var nyttig!
Andre opplæringsprogrammer i Excel kan være nyttige:
- #REF! Feil i Excel; Slik løser du referansefeilen!
- Excel VBA -feilbehandling
- Bruk IFERROR med VLOOKUP for å bli kvitt #N/A -feil
- Hvordan referere til et annet ark eller en arbeidsbok i Excel (med eksempler)
- Absolutte, relative og blandede cellereferanser i Excel
