Arbeid med sykliske referanser i Excel

Det antas at sykliske referanser i excele er et feilaktig uttrykk. Faktisk, ganske ofte er dette akkurat saken, men fortsatt ikke alltid. Noen ganger gjelder de ganske bevisst. La oss finne ut hva sykliske koblinger er hvordan du lager dem hvordan du finner allerede eksisterende i dokumentet hvordan du kan jobbe med dem eller hvordan du fjerner det.

Bruk av cykliske referanser

Først av alt, finn ut hva som er en syklisk lenke. I hovedsak refererer dette uttrykket, som gjennom formlene i andre celler refererer til seg selv. Det kan også være en lenke som ligger i bladet som det selv refererer til.

Det bør bemerkes at den moderne versjonen som standard blokkerer automatisk prosessen med å utføre en syklisk drift automatisk som standard. Dette skyldes at slike uttrykk i det overveldende flertallet er feil, og looping gir en konstant prosess med omberegning og beregning, noe som skaper en ekstra belastning på systemet.

Opprette en syklisk lenke

La oss nå se hvordan du lager det enkleste sykliske uttrykket. Dette vil være en lenke som ligger i samme celle som den refererer til.

1. Vi markerer elementet i A1-arket og skriver følgende uttrykk i det:

   = A1.

   Deretter klikker du på Enter-knappen på tastaturet.



2. Deretter vises en cyklisk ekspresjonsadvarsel dialogboks. Klikk på det på "OK" -knappen.



3. Dermed fikk vi en konjunkturoperasjon på et ark der cellen refererer til seg selv.

En liten kompliserende oppgave og skape et syklisk uttrykk fra flere celler.

1. I et hvilket som helst element i arket, skriv et nummer. La det være en celle A1, og nummeret 5.



2. I en annen celle (B1) skriv uttrykket:

   = C1.



3. I det neste elementet (C1) vil vi registrere en slik formel:

   = A1.



4. Etter det kommer vi tilbake til cellen A1, hvor tallet er satt 5. Se det til elementet B1:

   = B1.

   Klikk på Enter-knappen.



5. Således er syklusen lukket, og vi fikk en klassisk syklisk lenke. Etter at advarselsvinduet er stengt, ser vi at programmet markerte cyklisk binding med blå piler på et ark, som kalles Trace-piler.

Vi vender nå til å skape et syklisk uttrykk på bordets eksempel. Vi har et bordimplementeringsbord. Den består av fire kolonner, som angir navnet på varene, antall solgte produkter, prisen og mengden av inntekter fra salg av hele volumet. Tabellen i den siste kolonnen har allerede formler. De beregner inntekter ved å multiplisere mengden av prisen.

1. For å løsne formelen i den første linjen markerer vi arkelementet med nummeret til det første produktet (B2). I stedet for statisk verdi (6), skriv inn formelen der, som vil vurdere mengden av varer ved å dele det totale beløpet (D2) for prisen (C2):

   = D2 / C2

   Klikk på Enter-knappen.



2. Vi har slått ut den første cykliske linken, forholdet i som er kjent med Trace-pilen. Men som vi ser, er resultatet feil og lik null, da det allerede er sagt før, blokkerer Excel utførelsen av sykliske operasjoner.



3. Kopier uttrykket til alle andre celler i kolonnen med mengden produkter. For å gjøre dette, sett markøren til den nedre høyre vinkelen på det elementet som allerede inneholder formelen. Markøren konverteres til et kryss, som kalles for å ringe en fyllingsmarkør. Fjern venstre museknapp og trekk dette krysset til slutten av bordet ned.



4. Som du kan se, ble uttrykket kopiert til alle elementene i kolonnen. Men bare ett forhold er merket med Trace-pilen. Legg merke til det for fremtiden.

Søk etter sykliske linker

Som vi allerede har sett høyere, ikke i alle tilfeller markerer programmet forholdet mellom den sykliske referansen med objekter, selv om det er på arket. Gitt det faktum at i den overveldende flertallet sykliske operasjoner er skadelige, bør de fjernes. Men for dette må de først finne. Hvordan gjør du dette hvis uttrykkene ikke er merket med pilen? La oss håndtere denne oppgaven.

1. Så, hvis du starter en Excel-fil, har du et informasjonsvindu som det inneholder en syklisk lenke, det er ønskelig å finne det. For å gjøre dette, flytt til "Formulas" -fanen. Klikk på båndet på trekanten, som ligger til høyre for "Kontrollerer feil" -knappen, som ligger i verktøyblokken "Dependencyavhengighet". En meny åpnes der markøren skal være vert for "cykliske lenker". Etter det åpner følgende meny en liste over adresser til arkelementer der programmet har oppdaget sykliske uttrykk.



2. Når du klikker på en bestemt adresse, er den tilsvarende cellen valgt på arket.

Det er en annen måte å finne ut hvor den sykliske linken er plassert. Meldingen om dette problemet og adressen til elementet som inneholder et lignende uttrykk, er plassert på venstre side av statusstrengen, som ligger på bunnen av Excel-vinduet. True, i motsetning til den forrige versjonen, vil adressene til ikke alle elementene som inneholder sykliske referanser vises på statuslinjen, hvis det er mange av dem, men bare en av dem, som dukket opp for andre.

I tillegg, hvis du er i en bok som inneholder et syklisk uttrykk, ikke på arket der den er plassert, og på den andre, så i dette tilfellet vil bare en melding om tilstedeværelsen av en feil bli vist i statuslinjen.

Leksjon: Hvordan finne cykliske lenker til Excel

Korreksjon av sykliske referanser

Som nevnt ovenfor, i det overveldende flertallet av tilfeller, er sykliske operasjoner onde, hvorfra det enkelt skal elimineres. Derfor er det naturlig at etter at den sykliske tilkoblingen detekteres, er det nødvendig å rette det for å bringe formelen til normal form.

For å korrigere den konjunkturavhengigheten må du spore hele sammenkoblingen av cellene. Selv om sjekken indikerte en bestemt celle, så kan feilen ikke være dekket i selve den, men i et annet element av avhengigheten.

1. I vårt tilfelle, til tross for at programmet korrekt pekte på en av sykkelcellene (D6), ligger den faktiske feilen i en annen celle. Velg D6-elementet for å finne ut hvilke celler det trekker opp verdien. Vi ser på uttrykket i formelstrengen. Som vi ser, dannes verdien i dette arkelementet ved å multiplisere innholdet i B6 og C6-cellene.



2. Gå til C6-cellen. Vi fremhever det og ser på formelstrengen. Som vi ser, er det den vanlige statiske verdien (1000), som ikke er et produkt som beregner formelen. Derfor er det trygt å si at det angitte elementet ikke inneholder feil som forårsaker sykliske operasjoner.



3. Gå til neste celle (B6). Etter valg I Formula-raden ser vi at den inneholder et beregnet uttrykk (= D6 / C6), som trekker dataene fra andre tabellelementer, spesielt fra D6-cellen. Dermed refererer D6-cellen til dataene til elementet B6 og omvendt, noe som forårsaker loopedness.

   

   Her er forholdet vi beregnet ganske raskt, men i virkeligheten er det tilfeller når mange celler er involvert i beregningsprosessen, og ikke tre elementer som vi har. Da kan søket ta ganske lang tid, fordi det må studere hvert syklisk element.

4. Nå må vi forstå hvilken celle (B6 eller D6) inneholder en feil. Selv om det formelt er det ikke engang en feil, men bare overdreven bruk av referanser som fører til looping. Under prosessen med å løse hvilken celle skal redigeres, må du bruke logikk. Det er ingen klar algoritme for handling. I hvert tilfelle vil denne logikken være sin egen.

   For eksempel, hvis vårt bord delte det totale beløpet skulle beregnes ved å multiplisere antallet faktisk solgte varer til sin pris, så kan vi si at linken teller mengden av den totale mengden salg er tydelig overflødig. Derfor fjerner vi det og erstatter det med statisk betydning.



5. En slik operasjon utføres over alle andre sykliske uttrykk hvis de er på arket. Etter at alle sykliske koblinger har blitt slettet fra boken, bør meldingen om tilstedeværelsen av dette problemet forsvinne fra statusstrengen.

   I tillegg ble cykliske uttrykkene helt fjernet, du kan finne ut ved hjelp av feilkontrollverktøyet. Gå til "Formulas" -fanen og klikk på trekanten som allerede er kjent for oss til høyre for "Kontroller feil" -knappen i instrumentgruppen "Avhengige formler" . Hvis "Cyclic Links" -elementet i Running-menyen ikke er aktiv, betyr det at vi fjernet alle slike objekter fra dokumentet. I motsatt tilfelle må du bruke fjerningsprosedyren til elementene som er oppført, det samme på samme måte.

Tillatelse til utførelsen av sykliske operasjoner

I den forrige delen av leksjonen, fortalte vi, hovedsakelig hvordan man skal håndtere sykliske referanser, eller hvordan du finner dem. Men tidligere, samtalen var også om at de i noen tilfeller, tvert imot, kan være nyttig og bevisst brukt av brukeren. For eksempel, ganske ofte er denne metoden brukt til iterative beregninger når de bygger økonomiske modeller. Men trøbbel er at, uavhengig av om du er bevisst eller ubevisst du bruker et syklisk uttrykk, vil Excel fortsatt blokkere operasjonen på dem som standard, for ikke å føre til overdreven overbelastning av systemet. I dette tilfellet blir spørsmålet om tvungen som deaktiverer en slik blokkering, relevant. La oss se hvordan du gjør det.

1. Først og fremst flytter vi til "Fil" -fanen i Excel-applikasjonen.



2. Deretter klikker du på "Parametre" -elementet som er plassert på venstre side av vinduet som åpnes.



3. Det eksile parametervinduet begynner å løpe. Vi må gå inn i "formler" -fanen.



4. Det er i vinduet som åpnes, vil få lov til å utføre utførelsen av sykliske operasjoner. Gå til høyre blokk i dette vinduet, der Excel-innstillingene er direkte. Vi vil jobbe med innstillingsblokken "Computing parametere", som ligger øverst.

   For å tillate bruk av sykliske uttrykk, må du installere et kryss om parameteren "Aktiver iterative beregninger". I tillegg, i samme blokk kan du konfigurere grensenummeret av iterasjoner og den relative feilen. Som standard er deres verdier henholdsvis 100 og 0,001. I de fleste tilfeller trenger disse parametrene ikke å bli endret, men om nødvendig er det mulig å foreta endringer i de angitte feltene. Men her er det nødvendig å vurdere at for mye iterasjoner kan føre til en alvorlig belastning på programmet og systemet som helhet, spesielt hvis du jobber med en fil der mange sykliske uttrykk er plassert.

   Så, vi installerer et kryss om parameteren "Aktiver iterative beregninger", og da de nye innstillingene som er tatt i kraft, klikker du på "OK" -knappen, som ligger nederst på Excel-parametrene.



5. Etter det går vi automatisk til arket i den nåværende boken. Som vi ser, i cellene der cykliske formler er plassert, beregnes nå verdiene riktig. Programmet blokkerer ikke beregningene i dem.

Men det er fortsatt verdt å merke seg at inkluderingen av sykliske operasjoner ikke bør misbrukes. Bruk denne funksjonen følger bare når brukeren er helt sikker på behovet. Urimelig inkludering av sykliske operasjoner kan ikke bare føre til overdreven belastning på systemet og redusere beregningene når de arbeider med et dokument, men brukeren kan utilsiktet gjøre et feilaktig syklisk uttrykk, som som standard vil det bli blokkert av programmet.

Som vi ser, i det overveldende flertallet av tilfeller, er sykliske referanser et fenomen som du trenger å kjempe. For dette, først og fremst, bør du oppdage det konjunkturforholdet selv, og deretter beregne cellen der feilen er inneholdt, og til slutt eliminere den ved å gjøre de nødvendige justeringer. Men i noen tilfeller kan sykliske operasjoner være nyttige når man beregner og brukes av brukeren bevisst. Men selv da er det verdt å bruke deres bruk med forsiktighet, riktig konfigurering av Excel og å kjenne tiltaket i tillegg av slike referanser, som, når det brukes i massekvantiteter, kan redusere operasjonen av systemet.