Trabajar con referencias cíclicas en Excel.

Se cree que las referencias cíclicas en Excele son una expresión errónea. De hecho, bastante a menudo este es exactamente el caso, pero aún no siempre. A veces se aplican bastante conscientemente. Averigüemos qué los enlaces cíclicos son cómo crearlos cómo encontrar ya existentes en el documento cómo trabajar con ellos o cómo eliminarlo.

Uso de referencias cíclicas.

En primer lugar, averigüe qué es un enlace cíclico. En esencia, esta expresión, que, a través de las fórmulas en otras células, se refiere a sí misma. Además, puede ser un enlace ubicado en el elemento de hoja al que se refiere.

Cabe señalar que de forma predeterminada, las versiones modernas de Excel bloquean automáticamente el proceso de realizar una operación cíclica. Esto se debe al hecho de que tales expresiones en la mayoría abrumadora son erróneas, y el bucle produce un proceso constante de recálculo y calculación, lo que crea una carga adicional en el sistema.

Creando un enlace cíclico

Ahora veamos cómo crear la expresión cíclica más simple. Este será un enlace ubicado en la misma celda en la que se refiere.

1. Resaltamos el elemento de la hoja A1 y escribimos la siguiente expresión:

   = A1.

   A continuación, haga clic en el botón ENTER en el teclado.



2. Después de eso, aparece un cuadro de diálogo Advertencia de expresión cíclica. Haga clic en el botón "OK".



3. Por lo tanto, recibimos una operación cíclica en una hoja en la que la celda se refiere a sí misma.

Un poco de complicación de tarea y crea una expresión cíclica de varias células.

1. En cualquier elemento de la hoja, escriba un número. Deje que sea una celda A1, y el número 5.



2. En otra celda (B1) escribe la expresión:

   = C1.



3. En el siguiente elemento (C1) registraremos una fórmula de este tipo:

   = A1.



4. Después de eso, regresamos a la celda A1, en la que se establece el número 5. Consultelo al elemento B1:

   = B1.

   Haga clic en el botón ENTER.



5. Así, el ciclo cerró, y recibimos un enlace cíclico clásico. Después de que la ventana de advertencia está cerrada, vemos que el programa marcó el enlace cíclico con flechas azules en una hoja, que se denominan flechas de rastreo.

Ahora nos dirigimos a crear una expresión cíclica en el ejemplo de la tabla. Tenemos una tabla de implementación de mesa. Consta de cuatro columnas, que indican el nombre de las mercancías, el número de productos vendidos, el precio y la cantidad de ingresos de la venta de todo el volumen. La tabla en la última columna ya tiene fórmulas. Calculan los ingresos multiplicando la cantidad del precio.

1. Para aflojar la fórmula en la primera línea, resaltamos el elemento de la hoja con el número del primer producto (B2). En lugar del valor estático (6), ingrese la fórmula allí, que considerará la cantidad de bienes dividiendo la cantidad total (D2) para el precio (C2):

   = D2 / C2

   Haga clic en el botón ENTER.



2. Hemos resultado el primer enlace cíclico, la relación en la que está familiarizada con la flecha de rastreo. Pero como vemos, el resultado es erróneo e igual a cero, ya que ya se ha dicho antes, Excel bloquea la ejecución de las operaciones cíclicas.



3. Copie la expresión en todas las demás celdas de la columna con la cantidad de productos. Para hacer esto, coloque el cursor en el ángulo recto inferior de ese elemento que ya contiene la fórmula. El cursor se convierte en una cruz, que se llama para llamar a un marcador de llenado. Borre el botón izquierdo del ratón y tire de esta cruz hasta el final de la tabla hacia abajo.



4. Como puede ver, la expresión se copió a todos los elementos de la columna. Pero, solo una relación está marcada con la flecha traza. Note para el futuro.

Búsqueda de enlaces cíclicos

Como ya hemos visto más alto, no en todos los casos, el programa marca la relación de la referencia cíclica con objetos, incluso si está en la hoja. Dado el hecho de que en la mayoría de las operaciones cíclicas de abrumadoras son dañinas, deben eliminarse. Pero para esto primero deben encontrar. ¿Cómo hacer esto si las expresiones no están etiquetadas con la línea de flecha? Vamos a lidiar con esta tarea.

1. Por lo tanto, si inicia un archivo de Excel, tiene una ventana de información que contiene un enlace cíclico, es deseable encontrarlo. Para hacer esto, muévase a la pestaña "Fórmulas". Haga clic en la cinta en el triángulo, que se encuentra a la derecha del botón "Comprobación de errores", ubicado en el bloque de herramientas "Dependencia de dependencia". Se abre un menú en el que se debe alojar el cursor a "Enlaces cíclicos". Después de eso, el siguiente menú abre una lista de direcciones de elementos de hoja en los que el programa ha descubierto expresiones cíclicas.



2. Al hacer clic en una dirección específica, la celda correspondiente se selecciona en la hoja.

Hay otra forma de averiguar dónde se encuentra el enlace cíclico. El mensaje sobre este problema y la dirección del elemento que contiene una expresión similar se encuentra en el lado izquierdo de la cadena de estado, que está en la parte inferior de la ventana de Excel. Es cierto, en contraste con la versión anterior, las direcciones de no todos los elementos que contienen referencias cíclicas se mostrarán en la barra de estado, si hay muchas de ellas, pero solo una de ellas, que apareció ante otros.

Además, si está en un libro que contiene una expresión cíclica, no en la hoja donde se encuentra, y en el otro, en este caso, solo en este caso, solo se mostrará un mensaje sobre la presencia de un error en la barra de estado.

LECCIÓN: Cómo encontrar enlaces cíclicos a Excel

Corrección de referencias cíclicas.

Como se mencionó anteriormente, en la abrumadora mayoría de los casos, las operaciones cíclicas son malas, de las cuales se debe eliminar fácilmente. Por lo tanto, es natural que después de detectar la conexión cíclica, es necesario corregirlo para llevar la fórmula a la forma normal.

Para corregir la dependencia cíclica, debe rastrear toda la interconexión de las celdas. Incluso si la verificación indicó una celda específica, entonces el error puede no estar cubierto en sí mismo, sino en otro elemento de la cadena de dependencia.

1. En nuestro caso, a pesar del hecho de que el programa señaló correctamente a una de las células del ciclo (D6), el error real radica en otra célula. Seleccione el elemento D6 para averiguar qué celdas detiene el valor. Miramos la expresión en la cadena de fórmulas. Como vemos, el valor en este elemento de la hoja está formado multiplicando el contenido de las células B6 y C6.



2. Ir a la celda C6. Lo resaltamos y miramos la cadena de fórmulas. Como vemos, es el valor estático habitual (1000), que no es un producto que calcula la fórmula. Por lo tanto, es seguro decir que el elemento especificado no contiene errores que causan operaciones cíclicas.



3. Ir a la siguiente celda (B6). Después de la selección en la fila de fórmulas, vemos que contiene una expresión calculada (= D6 / C6), que extrae los datos de otros elementos de tabla, en particular, desde la celda D6. Por lo tanto, la celda D6 se refiere a los datos del elemento B6 y viceversa, que causa el bucle de bucle.

   

   Aquí, la relación calculamos bastante rápido, pero en realidad hay casos en que muchas células están involucradas en el proceso de cálculo, y no tres elementos que tenemos. Luego, la búsqueda puede tardar durante mucho tiempo, ya que tendrá que estudiar cada elemento cíclico.

4. Ahora debemos entender qué celda (B6 o D6) contiene un error. Aunque, formalmente, ni siquiera es un error, sino simplemente el uso excesivo de referencias que conduce al bucle. Durante el proceso de resolución de lo que se debe editar la célula, debe aplicar la lógica. No hay algoritmo claro para la acción. En cada caso, esta lógica será propia.

   Por ejemplo, si nuestra tabla compartió el monto total, se debe calcular multiplicando el número de productos vendidos en realidad a su precio, entonces podemos decir que el enlace que cuenta contando la cantidad de la cantidad total de venta es claramente superflua. Por lo tanto, lo eliminamos y lo reemplazamos con importancia estática.



5. Tal operación se realiza sobre todas las demás expresiones cíclicas si están en la hoja. Después de que todos los enlaces cíclicos se han eliminado del libro, el mensaje sobre la presencia de este problema debe desaparecer de la cadena de estado.

   Además, se eliminaron todas las expresiones cíclicas, puede descubrir la herramienta de verificación de errores. Vaya a la pestaña "Fórmulas" y haga clic en el triángulo que ya está familiarizado a la derecha del botón "Comprobación de errores" en el grupo de instrumentos "Dependiendo de fórmulas" . Si el elemento "Enlaces cíclicos" en el menú en ejecución no está activo, entonces significa que eliminamos todos estos objetos del documento. En el caso opuesto, deberá aplicar el procedimiento de eliminación a los elementos que se enumeran, lo mismo de la misma manera.

Permiso de la ejecución de las operaciones cíclicas.

En la parte anterior de la lección, le contamos, principalmente cómo lidiar con las referencias cíclicas, o cómo encontrarlas. Pero, anteriormente, la conversación también fue sobre eso, en algunos casos, por el contrario, pueden ser útiles y conscientemente utilizados por el usuario. Por ejemplo, con bastante frecuencia este método se utiliza para cálculos iterativos al crear modelos económicos. Pero el problema es que, independientemente de si usted está consciente o inconsciente, use una expresión cíclica, Excel todavía bloqueará la operación sobre ellos de forma predeterminada, para no llevar a una sobrecarga excesiva del sistema. En este caso, la cuestión de la desactivación forzada de este tipo de bloqueo se vuelve relevante. Veamos cómo hacerlo.

1. En primer lugar, nos dirigimos a la pestaña "Archivo" de la aplicación Excel.



2. A continuación, haga clic en el elemento "Parámetros" ubicado en el lado izquierdo de la ventana que se abre.



3. La ventana del parámetro del exilio comienza a funcionar. Necesitamos entrar en la pestaña "Fórmulas".



4. Está en la ventana que se abre se permitirá realizar la ejecución de las operaciones cíclicas. Vaya al bloque derecho de esta ventana, donde las configuraciones de Excel están directamente. Trabajaremos con el bloque de configuración de "Parámetros informáticos", que se encuentra en la parte superior.

   Para permitir el uso de expresiones cíclicas, debe instalar una marca en el parámetro "Habilitar cálculos iterativos". Además, en el mismo bloque, puede configurar el número de límite de iteraciones y el error relativo. De forma predeterminada, sus valores son 100 y 0.001, respectivamente. En la mayoría de los casos, estos parámetros no deben cambiarse, aunque si es necesario, es posible realizar cambios en los campos especificados. Pero aquí es necesario considerar que demasiadas iteraciones pueden llevar a una carga grave en el programa y el sistema en su conjunto, especialmente si trabaja con un archivo en el que se colocan muchas expresiones cíclicas.

   Por lo tanto, instalamos una garrapata sobre el parámetro "Habilitar cálculos iterativos", y luego en que la nueva configuración entró en vigor, haga clic en el botón "Aceptar", ubicado en la parte inferior de la ventana Parámetros de Excel.



5. Después de eso, automáticamente vamos a la hoja del libro actual. Como vemos, en las células en las que se encuentran las fórmulas cíclicas, ahora los valores se calculan correctamente. El programa no bloquea los cálculos en ellos.

Pero aún así vale la pena señalar que la inclusión de las operaciones cíclicas no debe ser abusada. Aplicar esta función sigue solo cuando el usuario está completamente seguro de su necesidad. La inclusión irrazonable de las operaciones cíclicas no solo puede conducir a una carga excesiva en el sistema y desacelerar los cálculos cuando se trabaja con un documento, pero el usuario puede hacer una expresión cíclica errónea, que de forma predeterminada estaría bloqueada por el programa.

Como vemos, en la abrumadora mayoría de los casos, las referencias cíclicas son un fenómeno con el que necesitas luchar. Para esto, en primer lugar, debe detectar la relación cíclica en sí, luego calcule la celda donde está contenida el error, y finalmente elimínelo haciendo los ajustes apropiados. Pero en algunos casos, las operaciones cíclicas pueden ser útiles al calcular y utilizar por el usuario conscientemente. Pero incluso entonces, vale la pena usar su uso con precaución, configurar correctamente Excel y conocer la medida en la adición de dichas referencias, que, cuando se usan en cantidades en masa, pueden ralentizar la operación del sistema.