Treballa amb referències cícliques a Excel

Es creu que les referències cícliques a Excel són una expressió errònia. De fet, sovint aquest és exactament el cas, però encara no sempre. De vegades s'apliquen conscientment. Anem a esbrinar quins enllaços cíclics són com crear-los com trobar ja existents al document Com treballar amb ells o com eliminar-lo.

Ús de referències cícliques

Primer de tot, descobriu què és un enllaç cíclic. En essència, aquesta expressió, que, a través de les fórmules d'altres cèl·lules, es refereix a si mateix. A més, pot ser un enllaç situat a l'element de fulla a què es refereix.

Cal assenyalar que per defecte, les versions modernes d'Excel bloquegen automàticament el procés de realització d'una operació cíclica. Això es deu al fet que aquestes expressions en la majoria aclaparadora són errònies, i el bucle produeix un procés constant de recalculació i calculant, que crea una càrrega addicional al sistema.

Creació d'un enllaç cíclic

Ara anem a veure com crear l'expressió cíclica més senzilla. Aquest serà un enllaç situat a la mateixa cel·la sobre la qual es refereix.

1. Destaquem l'element del full A1 i escrivim la següent expressió:

   = A1.

   A continuació, feu clic al botó Intro del teclat.



2. Després d'això, apareix un quadre de diàleg d'avís d'expressió cíclic. Feu clic al botó "D'acord".



3. Per tant, vam rebre una operació cíclica en un full en què la cèl·lula es refereix a si mateixa.

Una mica de complicació de tasques i crear una expressió cíclica de diverses cèl·lules.

1. En qualsevol element del full, escriviu un número. Deixa que sigui una cel·la A1 i el número 5.



2. En una altra cel·la (B1) escriviu l'expressió:

   = C1.



3. En el següent element (C1) gravarem aquesta fórmula:

   = A1.



4. Després d'això, tornem a la cel·la A1, en la qual s'estableix el número 5. Consulteu-lo a l'element B1:

   = B1.

   Feu clic al botó Intro.



5. Així, el cicle es va tancar i vam rebre un enllaç cíclic clàssic. Després de tancar la finestra d'avís, veiem que el programa va marcar el vincle cíclic amb fletxes blaves en un full, que s'anomenen fletxes de traça.

Ara girem a la creació d'una expressió cíclica a l'exemple de la taula. Tenim una taula d'implementació de taula. Consta de quatre columnes, que indiquen el nom de la mercaderia, el nombre de productes venuts, el preu i la quantitat d'ingressos de la venda de tot el volum. La taula en l'última columna ja té fórmules. Calculen els ingressos multiplicant la quantitat del preu.

1. Per afluixar la fórmula a la primera línia, destaquem l'element de full amb el número del primer producte (B2). En lloc de valor estàtic (6), introduïu la fórmula allà, que considerarà la quantitat de mercaderies dividint l'import total (D2) per al preu (C2):

   = D2 / C2

   Feu clic al botó Intro.



2. Hem sortit el primer enllaç cíclic, la relació en què està familiaritzat amb la fletxa de traça. Però a mesura que veiem, el resultat és erroni i igual a zero, ja que ja s'ha dit abans, Excel bloqueja l'execució de les operacions cícliques.



3. Copieu l'expressió a totes les altres cel·les de la columna amb la quantitat de productes. Per fer-ho, establiu el cursor a l'angle inferior dret d'aquest element que ja conté la fórmula. El cursor es converteix en una creu, que es diu per trucar a un marcador de farciment. Esborreu el botó esquerre del ratolí i traieu aquesta creu fins al final de la taula cap avall.



4. Com es pot veure, l'expressió es va copiar a tots els elements de la columna. Però, només una relació està marcada amb la fletxa de traça. Tingueu en compte que per al futur.

Cerqueu enllaços cíclics

Com ja hem vist més alt, no en tots els casos el programa marca la relació de la referència cíclica amb els objectes, fins i tot si es troba al full. Tenint en compte el fet que en les operacions de la majoria cícliques de la majoria són perjudicials, haurien de ser eliminats. Però per això han de trobar primer. Com fer-ho si les expressions no estan etiquetades amb la línia de fletxa? Tractem aquesta tasca.

1. Per tant, si comenceu un fitxer d'Excel, teniu una finestra d'informació que conté un enllaç cíclic, és convenient trobar-lo. Per fer-ho, traslladeu-vos a la pestanya "Fórmules". Feu clic a la cinta del triangle, que es troba a la dreta del botó "Comprovació d'errors", situat al bloc d'eines "Dependency Depependència". S'obre un menú en el qual el cursor hauria d'estar allotjat a "enllaços cíclics". Després d'això, el menú següent obre una llista d'adreces dels elements de fulls en què el programa ha descobert expressions cícliques.



2. Quan feu clic a una adreça específica, la cel·la corresponent es selecciona al full.

Hi ha una altra manera de saber on es troba l'enllaç cíclic. El missatge sobre aquest problema i l'adreça de l'element que conté una expressió similar es troba a la banda esquerra de la cadena d'estat, que es troba a la part inferior de la finestra d'Excel. És cert que, a diferència de la versió anterior, les adreces de no tots els elements que contenen referències cícliques es mostraran a la barra d'estat, si hi ha molts d'ells, però només un d'ells, que va aparèixer davant d'altres.

A més, si vostè està en un llibre que conté una expressió cíclica, no al full on es troba, i de l'altra, en aquest cas, només es mostrarà un missatge sobre la presència d'un error a la barra d'estat.

Lliçó: Com trobar enllaços cíclics a Excel

Correcció de referències cícliques

Com es va esmentar anteriorment, en la immensa majoria dels casos, les operacions cícliques són malvades, de les quals s'hauria d'eliminar fàcilment. Per tant, és natural que es detecti la connexió cíclica, cal corregir-la per portar la fórmula a la forma normal.

Per corregir la dependència cíclica, heu de traçar tota la interconnexió de les cèl·lules. Fins i tot si el xec indica una cel·la específica, l'error no es pot cobrir en ell mateix, sinó en un altre element de la cadena de dependència.

1. En el nostre cas, tot i que el programa apunta correctament a una de les cèl·lules cicles (D6), l'error real es troba en una altra cel·la. Seleccioneu l'element D6 per esbrinar quines cel·les treu el valor. Mirem l'expressió de la cadena de fórmula. Com veiem, el valor d'aquest element de fulla està format per multiplicar els continguts de les cèl·lules B6 i C6.



2. Aneu a la cel·la C6. Ho destaquem i mirem la cadena de fórmula. Com veiem, és el valor estàtic habitual (1000), que no és un producte que calcula la fórmula. Per tant, és segur dir que l'element especificat no conté errors que causen operacions cícliques.



3. Aneu a la següent cel·la (B6). Després de la selecció a la fila de fórmula, veiem que conté una expressió calculada (= D6 / C6), que treu les dades d'altres elements de taula, en particular, des de la cel·la D6. Així, la cèl·lula D6 es refereix a les dades de l'element B6 i viceversa, que provoca la bucle.

   

   Aquí la relació que calculava molt ràpidament, però en realitat hi ha casos en què moltes cèl·lules estan involucrades en el procés de càlcul, i no tres elements que tenim. A continuació, la cerca pot trigar molt de temps, perquè haurà d'estudiar cada element cíclic.

4. Ara hem d'entendre quina cel·la (B6 o D6) conté un error. Tot i que, formalment, ni tan sols és un error, sinó simplement ús excessiu de referències que condueix al bucle. Durant el procés de resolució de quina cel·la s'ha d'editar, cal que apliqueu la lògica. No hi ha un algoritme clar per a l'acció. En cada cas, aquesta lògica serà pròpia.

   Per exemple, si la nostra taula compartida, l'import total s'ha de calcular multiplicant el nombre de béns realment venuts al seu preu, llavors podem dir que l'enllaç comptant l'import de la quantitat total de venda és clarament superflu. Per tant, l'eliminem i la substituirem per importància estàtica.



5. Aquesta operació es realitza a través de totes les altres expressions cícliques si es troben al full. Després d'haver suprimit absolutament tots els enllaços cíclics del llibre, el missatge sobre la presència d'aquest problema hauria de desaparèixer de la cadena d'estat.

   A més, les expressions cícliques es van eliminar completament, es pot esbrinar mitjançant l'eina de comprovació d'errors. Aneu a la pestanya "Fórmules" i feu clic al triangle que ens coneix a la dreta del botó "Comprovació d'errors" del grup d'instruments "Depenent de fórmules" . Si l'element "Enllaços cíclics" al menú en execució no està actiu, llavors significa que hem eliminat tots aquests objectes del document. En el cas contrari, haureu d'aplicar el procediment de retirada als elements que figuren, de la mateixa manera.

Permís de l'execució d'operacions cícliques

A la part anterior de la lliçó, vam dir, principalment com tractar referències cícliques, o com trobar-les. Però, abans, la conversa també va ser sobre això, en alguns casos, per contra, poden ser útils i conscientment utilitzats per l'usuari. Per exemple, sovint aquest mètode s'utilitza per a càlculs iteratius a l'hora de construir models econòmics. Però el problema és que, independentment de si esteu conscientment o inconscientment, utilitzeu una expressió cíclica, Excel encara bloquejarà l'operació sobre ells per defecte, per no conduir a una sobrecàrrega excessiva del sistema. En aquest cas, la qüestió de la desactivació forçada d'aquest bloqueig esdevé rellevant. Vegem com fer-ho.

1. En primer lloc, ens movem a la pestanya "Fitxer" de l'aplicació Excel.



2. A continuació, feu clic a l'element "Paràmetres" situat a la part esquerra de la finestra que es va obrir.



3. La finestra del paràmetre de l'exili comença a funcionar. Hem d'entrar a la pestanya "Fórmules".



4. Es troba a la finestra que s'obre es permetrà realitzar l'execució de les operacions cícliques. Aneu al bloc dret d'aquesta finestra, on els paràmetres d'Excel són directament. Treballarem amb el bloc de configuració "Paràmetres informàtics", que es troba a la part superior.

   Per permetre l'ús d'expressions cícliques, haureu d'instal·lar un paràmetre "Habilita els càlculs iteratius". A més, en el mateix bloc, podeu configurar el nombre límit d'iteracions i l'error relatiu. Per defecte, els seus valors són 100 i 0.001, respectivament. En la majoria dels casos, aquests paràmetres no necessiten ser canviats, encara que si cal, és possible fer canvis en els camps especificats. Però aquí cal tenir en compte que massa iteracions poden conduir a una càrrega greu del programa i del conjunt del sistema, especialment si treballeu amb un fitxer en què es col·loquen moltes expressions cícliques.

   Per tant, instal·lem un paràmetre "Activa els càlculs iteratius", i després que la nova configuració introduïda en vigor, feu clic al botó "OK", situat a la part inferior de la finestra de paràmetres Excel.



5. Després d'això, anem automàticament al full del llibre actual. Com veiem, a les cèl·lules en què es troben les fórmules cícliques, ara es calculen correctament els valors. El programa no bloqueja els càlculs.

Però encara val la pena assenyalar que la inclusió de les operacions cícliques no s'hauria d'abusar. Aplicar aquesta funció només segueix quan l'usuari confia completament en la seva necessitat. La inclusió irracional de les operacions cícliques no només pot conduir a una càrrega excessiva del sistema i frenar els càlculs quan es treballa amb un document, però l'usuari pot fer una expressió cíclica errònia, que per defecte seria bloquejada pel programa.

Com veiem, en la immensa majoria dels casos, les referències cícliques són un fenomen amb el qual necessiteu lluitar. Per a això, en primer lloc, hauríeu de detectar la relació cíclica mateixa, calculeu la cel·la on es conté l'error i, finalment, elimineu-lo fent els ajustaments adequats. Però, en alguns casos, les operacions cícliques poden ser útils en calcular i utilitzar conscientment l'usuari. Però fins i tot llavors, val la pena utilitzar-lo amb precaució, configurar correctament Excel i conèixer la mesura en l'addició d'aquestes referències, que, quan s'utilitza en quantitats de massa, pot frenar el funcionament del sistema.