Ligazóns cíclicas en Microsoft Excel

Xeralmente acéptase que as ligazóns cíclicas en Excel sexan unha expresión errónea. De feito, a miúdo isto é certo, pero aínda así non sempre. Ás veces aplícanse bastante deliberadamente. Descubrimos que son os enlaces cíclicos, como crealos, como atopar os existentes nun documento, como traballar con eles ou como eliminalos se é necesario.

Usando referencias circulares

En primeiro lugar, descubrimos que é un enlace circular. De feito, esta é unha expresión que, a través de fórmulas noutras células, se refire a si mesma. Tamén pode ser unha ligazón situada no elemento da folla ao que el mesmo se refire.

Cómpre sinalar que por defecto, as versións modernas de Excel bloquean automaticamente o proceso de realización dunha operación cíclica. Isto débese a que tales expresións son excesivamente erróneas e o looping produce un proceso constante de conta e cálculo, o que crea unha carga adicional no sistema.

Crear unha ligazón circular

Agora vexamos como crear unha expresión cíclica sinxela. Este será o enlace situado na mesma cela á que se refire.

1. Seleccione un elemento de folla A1 e escribe nela a seguinte expresión:

   = A1

   A continuación, fai clic no botón Introduza no teclado.



2. Despois aparece o cadro de diálogo de advertencia de expresión cíclica. Fai clic no botón. "Está ben".



3. Así, recibimos unha operación cíclica nunha folla na que a célula se refire a si mesma.

Complicamos un pouco a tarefa e creamos unha expresión cíclica a partir de varias celas.

1. En calquera elemento da folla, escriba un número. Que sexa unha cela A1, e o número 5.



2. A outra cela (B1) escriba a expresión:

   = C1



3. No seguinte elemento (C1) escribimos tal fórmula:

   = A1



4. Despois volvemos á cela A1no que se establece o número 5. Referímonos ao elemento que hai. B1:

   = B1

   Fai clic no botón Introduza.



5. Así, pechou o bucle, e obtivemos unha referencia circular clásica. Unha vez que se pechou a xanela de aviso, vemos que o programa marcaba a ligazón cíclica con frechas azuis na folla, que se chaman frechas de rastro.

Agora imos a crear unha expresión cíclica usando unha táboa de exemplos. Temos unha táboa de vendas de alimentos. Consta de catro columnas nas que se indica o nome da mercadoría, o número de produtos vendidos, o prezo e a cantidade de ingresos procedentes da venda de todo o volume. A táboa da última columna xa ten fórmulas. Calculan ingresos multiplicando a cantidade polo prezo.

1. Para enlazar a fórmula na primeira liña, seleccione o elemento da folla coa cantidade do primeiro elemento da conta (B2) En lugar dun valor estático (6) introducimos a fórmula que considerará a cantidade de mercadorías dividindo o importe total (D2) ao prezo (C2):

   = D2 / C2

   Fai clic no botón Introduza.



2. Obtemos o primeiro enlace circular, a relación na que adoita estar indicada pola frecha de rastro. Pero como podes ver, o resultado é erróneo e igual a cero, como xa se mencionou anteriormente, Excel bloquea a execución de operacións cíclicas.



3. Copia a expresión a todas as outras celas da columna co número de produtos. Para iso, coloca o cursor na esquina inferior dereita do elemento que xa contén a fórmula. O cursor convértese nunha cruz, que normalmente se denomina marcador de recheo. Manteña o botón esquerdo do rato e arrastre esta cruz ata o final da táboa.



4. Como podes ver, a expresión copiouse en todos os elementos da columna. Pero, só unha relación está marcada cunha frecha de rastro. Teña en conta isto para o futuro.

Busca ligazóns circulares

Como vimos anteriormente, non en todos os casos o programa marca a relación da referencia circular cos obxectos, aínda que estea na folla. Dado que a gran maioría das operacións cíclicas son prexudiciais, deberían eliminarse. Pero para iso deben atoparse primeiro. Como facelo se as expresións non están marcadas cunha liña con frechas? Tratemos este problema.

1. Entón, se ao iniciar o ficheiro Excel, ábrese unha xanela de información indicando que contén unha ligazón circular, é recomendable atopala. Para iso, desprácese á lapela Fórmulas. Fai clic na cinta do triángulo, que se atopa á dereita do botón "Verificar erros"situado no bloque de ferramentas Dependencias de fórmula. Ábrese un menú no que debería pasar o elemento "Ligazóns circulares". Despois diso, no seguinte menú ábrese a lista de enderezos dos elementos da folla nos que o programa detectou expresións cíclicas.



2. Cando fai clic nun enderezo específico, seleccionarase a cela correspondente da folla.

Hai outro xeito de descubrir onde está o enlace circular. A mensaxe sobre este problema e o enderezo do elemento que contén esta expresión están situados no lado esquerdo da barra de estado, que se atopa na parte inferior da xanela de Excel. É certo, a diferenza da versión anterior, a barra de estado non amosará os enderezos de todos os elementos que conteñan enlaces circulares, se hai moitos, pero só un deles que apareceu antes dos outros.

Ademais, se está nun libro que contén unha expresión cíclica, non na folla onde se atopa, senón pola outra, entón neste caso só se mostrará na barra de estado a mensaxe sobre a presenza dun erro sen enderezo.

Lección: Como atopar enlaces circulares en Excel

Corrixir enlaces cíclicos

Como se mencionou anteriormente, na gran maioría dos casos, as operacións cíclicas son malas que deben eliminarse. Polo tanto, é lóxico que despois de que se atopa unha conexión cíclica, sexa necesario corrixila para levar a fórmula á forma normal.

Para arranxar a dependencia cíclica é necesario rastrexar toda a interconexión das células. Aínda que a comprobación indicase unha cela específica, o erro pode non estar nela mesmo, senón noutro elemento da cadea de dependencia.

1. No noso caso, a pesar de que o programa apuntou correctamente a unha das celas do bucle (D6), o verdadeiro erro reside noutra cela. Seleccione un elemento D6para saber de que celas saca o valor. Miremos a expresión na barra de fórmulas. Como podes ver, o valor desta folla elemento fórmase multiplicando o contido das celas B6 e C6.



2. Vai á cela C6. Selecciona e mira a liña de fórmulas. Como podes ver, este é o valor estático habitual (1000), que non é un produto do cálculo da fórmula. Polo tanto, podemos dicir con confianza que o elemento especificado non contén un erro que provoca a creación de operacións cíclicas.



3. Vai á seguinte cela (B6) Despois de resaltar na barra de fórmulas, vemos que contén unha expresión calculada (= D6 / C6), que extrae datos doutros elementos da táboa, en particular, da cela D6. Así, a cela D6 refírese aos datos do elemento B6 e viceversa, o que provoca bucles.

   

   Aquí calculamos a relación bastante rápido, pero en realidade hai casos en que moitas células están implicadas no proceso de cálculo e non tres elementos, como temos. Entón a busca pode levar moito tempo, porque terá que estudar cada elemento da ciclicidade.

4. Agora necesitamos entender en que cela (B6 ou D6) contén un erro. Aínda que, formalmente, isto nin sequera é un erro, senón simplemente un uso excesivo de ligazóns, o que leva a un bucle. Durante o proceso para decidir que cela debe editarse, debe aplicarse a lóxica. Non hai un algoritmo claro de accións. En cada caso, esta lóxica será diferente.

   Por exemplo, se na nosa táboa a cantidade total debería calcularse multiplicando a cantidade de mercadorías realmente vendidas polo seu prezo, entón podemos dicir que a ligazón que calcula a cantidade do importe total da venda é claramente superfluo. Polo tanto, eliminámolo e substituímolo por un valor estático.



5. Realizamos unha operación similar en todas as outras expresións cíclicas, se están na folla. Despois de que se eliminasen absolutamente todas as referencias circulares do libro, a mensaxe sobre a presenza deste problema debería desaparecer da barra de estado.

   Ademais, se se eliminaron completamente as expresións cíclicas, pode descubrilo usando a ferramenta de comprobación de erros. Vai á lapela Fórmulas e fai clic no triángulo que xa nos é coñecido á dereita do botón "Verificar erros" no grupo de ferramentas Dependencias de fórmula. Se no menú que se abre, "Ligazóns circulares" non estará activo, isto significa que eliminamos todos os obxectos deste documento. En caso contrario, será necesario aplicar o procedemento de eliminación aos elementos que figuran na lista do mesmo xeito que antes considerado.

Permiso Loopback

Na parte anterior da lección falamos principalmente sobre como tratar enlaces circulares ou como atopalos. Pero, anteriormente a conversa trataba tamén de que nalgúns casos, pola contra, poden ser útiles e conscientes polo usuario. Por exemplo, con bastante frecuencia este método úsase para cálculos iterativos na construción de modelos económicos. Pero o problema é que, independentemente de se use unha expresión circular consciente ou inconscientemente, Excel por defecto aínda bloqueará a operación nelas, para non levar a unha excesiva sobrecarga do sistema. Neste caso, a cuestión de deshabilitar forzosamente este bloqueo é relevante. Vexamos como facelo.

1. Primeiro de todo, vaia á lapela Arquivo Aplicacións Excel.



2. A continuación, fai clic sobre o elemento "Opcións"situado no lado esquerdo da xanela que se abre.



3. Comeza a xanela de opcións de Excel. Necesitamos ir á lapela Fórmulas.



4. Na xanela se abre que será posible permitir a execución de operacións cíclicas. Imos ao bloque dereito desta xanela, onde están situados os propios axustes de Excel. Imos traballar co bloque de configuración Parámetros de cálculoque está situado na parte superior.

   Para habilitar o uso de expresións cíclicas, marque a caixa xunto ao parámetro Activa a informática iterativa. Ademais, o número límite de iteracións e erro relativo pódense definir no mesmo bloque. Por defecto, os seus valores son 100 e 0,001, respectivamente. Na maioría dos casos, estes parámetros non necesitan ser cambiados, aínda que se é necesario ou se o desexa, pode facer cambios nestes campos. Pero aquí hai que ter en conta que demasiadas iteracións poden levar a unha carga grave no programa e no sistema no seu conxunto, especialmente se está a traballar cun ficheiro que contén moitas expresións cíclicas.

   Polo tanto, marca a caixa xunto ao parámetro Activa a informática iterativae despois para que a nova configuración teña efecto, prema no botón "Está ben"situado na parte inferior da xanela de opcións de Excel.



5. Despois, pasamos automaticamente á folla do libro actual. Como podes ver, nas celas nas que se atopan as fórmulas cíclicas, agora os valores calcúlanse correctamente. O programa non bloquea cálculos neles.

Non obstante, convén destacar que non se debe abusar da inclusión de operacións cíclicas. Use esta función só cando o usuario está completamente seguro da súa necesidade. A inclusión razoable de operacións cíclicas non só pode levar a unha carga excesiva no sistema e retardar os cálculos cando se traballa cun documento, senón que o usuario pode introducir inadvertidamente unha expresión cíclica errónea, que por defecto sería inmediatamente bloqueada polo programa.

Como vemos, na gran maioría dos casos, as referencias circulares son un fenómeno que cómpre abordar. Para isto, primeiro hai que detectar a propia relación cíclica, logo calcular a cela onde está o erro e, finalmente, eliminalo facendo axustes axeitados. Pero nalgúns casos, as operacións cíclicas poden ser útiles nos cálculos e realizar o usuario de forma consciente. Pero aínda así, paga a pena abordar o seu uso con precaución, configurando correctamente Excel e coñecendo a medida para engadir tales ligazóns, que cando se usan a granel poden retardar o sistema.