Crese que as referencias cíclicas en Excel son unha expresión errónea. De feito, moitas veces este é o caso, pero non sempre. Ás veces aplícanse moi deliberadamente. Descubrimos cales son os enlaces cíclicos, como crealos, como atopar os existentes nun documento, como traballar con eles ou como borralos se é necesario.
Usar referencias circulares
Primeiro de todo, descubra o que constitúe unha referencia circular. De feito, é unha expresión que, por medio de fórmulas noutras células, refírese a si mesma. Tamén pode ser un enlace situado no elemento de folla ao que se refire.
Debe terse en conta que, por defecto, as versións modernas de Excel bloquean automaticamente o proceso de execución dunha operación cíclica. Isto é debido ao feito de que tales expresións son esencialmente erróneas e o looping produce un proceso constante de recálculo e cálculo, o que crea unha carga adicional no sistema.
Crear unha referencia circular
Agora imos ver como crear a expresión de loop máis sinxela. Esta será unha ligazón situada na mesma cela á que se refire.
- Seleccione o elemento da folla A1 e escriba na seguinte expresión:
= A1A continuación, fai clic no botón Intro no teclado.
- Despois aparece un cadro de diálogo de aviso de expresión cíclica. Facemos clic sobre el no botón. "OK".
- Así, recibimos unha operación cíclica nunha folla na que a cela fai referencia a si mesma.
Imos complicar un pouco a tarefa e crear unha expresión cíclica de varias células.
- Escribe un número en calquera elemento da folla. Que sexa unha cela A1eo número 5.
- A outra celaB1) escriba a expresión:
= C1 - No seguinte elemento (C1) escriba a seguinte fórmula:
= A1 - Despois diso volveremos á cela. A1no que está definido o número 5. Referímonos ao seu elemento B1:
= B1Pulsamos o botón Intro.
- Así, o lazo está pechado e obtemos un enlace cíclico clásico. Despois de pechar a ventá de advertencia, vemos que o programa marcou unha conexión cíclica con frechas azuis na folla, que se chaman frechas de traza.
Agora volvemos á creación dunha expresión cíclica no exemplo dunha táboa. Temos unha táboa de vendas de alimentos. Consta de catro columnas nas que se indica o nome do produto, o número de produtos vendidos, o prezo e o importe dos ingresos procedentes da venda de todo o volume. Xa hai fórmulas na táboa da última columna. Calculan ingresos multiplicando a cantidade polo prezo.
- Para encadrar a fórmula na primeira liña, seleccione o elemento da folla coa cantidade do primeiro produto (B2). En vez de un valor estático (6) ingresamos alí a fórmula que contará a cantidade de bens dividindo o importe total (D2) sobre o prezo (C2):
= D2 / C2Fai clic no botón Intro.
- Conseguimos o primeiro enlace cíclico, a relación na que se adoita indicar unha frecha de trazado. Pero como pode ver, o resultado é erróneo e igual a cero, xa que xa se dixo antes, Excel bloquea a execución das operacións cíclicas.
- Copie a expresión a todas as outras celas da columna co número de produtos. Para facelo, configure o cursor no ángulo inferior dereito do elemento que xa contén a fórmula. O cursor convértese nunha cruz, que se denomina marcador de enchemento. Manteña premido o botón esquerdo do rato e arrastre esta cruz ata o final da táboa.
- Como podes ver, a expresión foi copiada en todos os elementos da columna. Pero só unha relación está marcada cunha frecha de traza. Note isto para o futuro.
Busca referencias circulares
Como xa vimos anteriormente, non en todos os casos o programa marca a interrelación dunha referencia circular cos obxectos, aínda que estea na folla. Dado o feito de que as operacións cíclicas son esencialmente perjudiciales, deberían eliminarse. Pero para iso deben atoparse primeiro. Como se pode facer se as expresións non están marcadas cunha liña con frechas? Imos tratar con esta tarefa.
- Polo tanto, se executa un ficheiro de Excel cando abre unha ventá de información dicindo que contén unha ligazón circular, é aconsellable atopalo. Para facelo, desprácese á pestana "Fórmulas". Fai clic na cinta do triángulo, que se atopa á dereita do botón "Verificar se hai erros"situado nun bloque de ferramentas "Dependencias da fórmula". Ábrese un menú no que debería mover o cursor ao elemento "Ligazóns cíclicas". Despois diso, o seguinte menú abre unha lista de enderezos dos elementos da folla na que o programa detectou expresións cíclicas.
- Cando fas clic nun enderezo específico, selecciona a cela correspondente na folla.
Hai outro xeito de saber onde está a ligazón circular. A mensaxe sobre este problema ea dirección do elemento que contén unha expresión semellante atópase no lado esquerdo da barra de estado, que se atopa na parte inferior da xanela de Excel. Non obstante, a diferenza da versión anterior, as direccións da barra de estado mostrarán as direccións de non todos os elementos que conteñan referencias circulares, se hai moitos deles, pero só un deles, que apareceu diante dos demais.
Ademais, se estás nun libro que contén unha expresión en bucle, non na folla onde está situada, pero noutro, entón neste caso só aparecerá unha mensaxe sobre a presenza dun erro sen enderezo na barra de estado.
Lección: como atopar ligazóns circulares en Excel
Corrixir enlaces cíclicos
Como se mencionou anteriormente, na esmagadora maioría dos casos, as operacións cíclicas son malas que deben ser eliminadas. Polo tanto, é natural que despois da conexión cíclica sexa descuberto, cómpre corrixilo para levar a fórmula a unha forma normal.
Para corrixir a dependencia cíclica, é necesario rastrexar toda a interconexión das células. Aínda que a verificación indicase unha cela específica, o erro pode non estar en si mesmo, senón noutro elemento da cadea de dependencia.
- No noso caso, a pesar de que o programa apuntou correctamente a unha das células do ciclo (D6), o erro real atópase noutra cela. Seleccione o elemento D6para saber de que celas tira o valor. Miramos a expresión na barra de fórmulas. Como podes ver, o valor neste elemento da folla está formado multiplicando o contido das celas B6 e C6.
- Ir á cela C6. Selecciona e mira a barra de fórmulas. Como podes ver, este é un valor estático regular (1000), que non é un produto da fórmula. Polo tanto, é seguro dicir que o elemento especificado non contén un erro que provoca a creación de operacións cíclicas.
- Ir á seguinte celaB6). Despois de seleccionar a fórmula na liña, vemos que contén unha expresión calculada (= D6 / C6), que tira datos de outros elementos da táboa, en particular, dunha cela D6. Así que a cela D6 refírese aos datos dos ítems B6 e viceversa, que causa obsesión.
Aquí calculamos a relación con bastante rapidez, pero en realidade hai casos nos que o proceso de cálculo implica moitas celas e non tres elementos como o noso. Entón a busca pode levar moito tempo, porque terá que estudar cada elemento do ciclo.
- Agora necesitamos comprender exactamente que cela (B6 ou D6) contén un erro. Aínda que, formalmente, isto non é nin sequera un erro, senón simplemente un uso excesivo de enlaces, o que leva a un looping. Durante o proceso de decidir que cela debe editar, ten que aplicar unha lóxica. Non hai ningún algoritmo claro para a acción. En cada caso, esta lóxica será diferente.
Por exemplo, se na nosa táboa o importe total debe calcularse multiplicando a cantidade de mercadorías vendidas efectivamente polo seu prezo, entón podemos dicir que a ligazón que calcula o importe do importe total da venda é claramente superfluo. Polo tanto, eliminámolo e substituímoslo por un valor estático.
- Realizamos unha operación similar en todas as outras expresións cíclicas, se están na folla. Despois de eliminar todos os enlaces 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 descubrir a utilización da ferramenta de verificación de erros. Vaia á pestana "Fórmulas" e fai clic no triángulo xa coñecido á dereita do botón "Verificar se hai erros" nun grupo de ferramentas "Dependencias da fórmula". Se está no elemento do menú Inicio "Ligazóns cíclicas" non estará activo, significa que eliminamos todos estes obxectos do documento. No caso contrario, será necesario aplicar o procedemento de eliminación aos elementos que están na lista do mesmo xeito que se considerou anteriormente.
Permiso para realizar operacións cíclicas
Na parte anterior da lección, describimos principalmente como tratar con referencias circulares ou como atopalas. Pero antes a conversa tratábase tamén do feito de que nalgúns casos, pola contra, poden ser útiles e usados conscientemente polo usuario. Por exemplo, moitas veces este método emprégase para cálculos iterativos ao construír modelos económicos. Pero o problema é que, independentemente de que use conscientemente ou sen saber unha expresión cíclica, Excel por defecto aínda bloqueará a operación neles, para non levar a unha sobrecarga de sistema excesiva. Neste caso, a cuestión da desactivación de tal bloqueo resulta relevante. Vexamos como facelo.
- Primeiro de todo, vaia á pestana "Ficheiro" Aplicacións de Excel.
- A continuación, faga clic no elemento "Opcións"situado no lado esquerdo da xanela que se abre.
- Iníciase a xanela Parámetros de Excel. Necesitamos ir á pestana "Fórmulas".
- É na ventá aberta que será posible producir permiso para realizar operacións cíclicas. Vaia ao bloque dereito desta ventá, onde se atopan a configuración de Excel. Traballaremos co bloque de configuración. "Parámetros de cálculo"que se atopa na parte superior.
Para permitir o uso de expresións cíclicas, cómpre marcar a caixa ao lado do parámetro "Habilitar cálculos iterativos". Ademais, no mesmo bloque, pode configurar o número límite de iteracións eo erro relativo. Por defecto, os seus valores son 100 e 0,001, respectivamente. Na maioría dos casos, non é necesario cambiar estes parámetros, aínda que se é necesario ou se o desexa, pode facer cambios nos campos indicados. Pero aquí hai que ter en conta que moitas iteracións poden provocar unha carga grave no programa e no sistema no seu conxunto, especialmente se traballas cun ficheiro que contén moitas expresións cíclicas.
Entón, establecer unha marca preto do parámetro "Habilitar cálculos iterativos"e logo para que a nova configuración teña efecto, fai clic no botón "OK"situado na parte inferior da ventá de opcións de Excel.
- Despois pasamos automaticamente á folla do libro actual. Como podes ver, nas celas nas que se atopan as fórmulas cíclicas, os valores calcúlanse correctamente. O programa non bloquea os cálculos neles.
Pero aínda cabe destacar que a inclusión de operacións cíclicas non debe ser abusada. Esta característica só se debería empregar cando o usuario está completamente seguro da súa necesidade. A inclusión non razoable de operacións cíclicas pode non só provocar unha carga excesiva no sistema e retardar os cálculos ao traballar co documento, senón que o usuario pode inadvertidamente introducir unha expresión cíclica errónea que o programa bloquearía de xeito predeterminado.
Como vemos, na esmagadora maioría dos casos, as referencias circulares son un fenómeno que hai que tratar. Para facelo, en primeiro lugar, debería atopar a propia relación cíclica e calcular a cela que contén o erro e, finalmente, eliminar a corrección. Pero nalgúns casos, as operacións cíclicas poden ser útiles nos cálculos e son realizadas polo usuario de forma consciente. Pero aínda así, paga a pena abordar o seu uso con cautela, configurar correctamente Excel e coñecer a medida engadindo tales ligazóns que, cando se usan en grandes cantidades, poden retardar o sistema.
