Travailler avec des références cycliques dans Excel

On pense que les références cycliques dans Excele sont une expression erronée. En effet, assez souvent, c'est exactement le cas, mais toujours pas toujours. Parfois, ils s'appliquent assez consciemment. Découvrons ce que les liens cycliques sont de savoir comment les créer de manière déjà existante dans le document comment travailler avec eux ou comment le supprimer.

Utilisation de références cycliques

Tout d'abord, découvrez ce qui est un lien cyclique. En substance, cette expression, qui, à travers les formules d'autres cellules, se réfère à elle-même. En outre, il peut s'agir d'une liaison située dans l'élément feuille à laquelle elle se réfère elle-même.

Il convient de noter que par défaut, les versions modernes d'Excel bloquent automatiquement le processus d'exécution d'une opération cyclique. Cela est dû au fait que de telles expressions dans la majorité écrasante sont erronées et que la boucle produit un processus constant de recalcul et de calcul, ce qui crée une charge supplémentaire sur le système.

Créer une liaison cyclique

Voyons maintenant comment créer l'expression cyclique la plus simple. Ce sera un lien situé dans la même cellule sur laquelle il se réfère.

1. Nous mettons en évidence l'élément de la feuille A1 et écris l'expression suivante:

   = A1.

   Ensuite, cliquez sur le bouton ENTER du clavier.



2. Après cela, une boîte de dialogue d'avertissement d'expression cyclique apparaît. Cliquez dessus sur le bouton "OK".



3. Ainsi, nous avons reçu une opération cyclique sur une feuille dans laquelle la cellule se réfère à elle-même.

Une petite tâche compliquant et crée une expression cyclique de plusieurs cellules.

1. Dans n'importe quel élément de la feuille, écrivez un numéro. Que ce soit une cellule A1 et le numéro 5.



2. Dans une autre cellule (B1), écrivez l'expression:

   = C1.



3. Dans l'élément suivant (C1), nous enregistrerons une telle formule:

   = A1.



4. Après cela, nous retournons à la cellule A1, dans laquelle le nombre est défini. 5. Reportez-vous à l'élément B1:

   = B1.

   Cliquez sur le bouton ENTER.



5. Ainsi, le cycle fermé et nous avons reçu une liaison cyclique classique. Une fois la fenêtre d'avertissement terminée, nous voyons que le programme a marqué la liaison cyclique avec des flèches bleues sur une feuille, appelée Trace Flèches.

Nous nous tournons maintenant vers la création d'une expression cyclique sur l'exemple de la table. Nous avons une table de mise en œuvre de la table. Il se compose de quatre colonnes, qui indiquent le nom des marchandises, le nombre de produits vendus, le prix et le montant des revenus de la vente de l'ensemble du volume. La table dans la dernière colonne a déjà des formules. Ils calculent les revenus en multipliant le montant du prix.

1. Pour desserrer la formule dans la première ligne, nous mettons en surbrillance l'élément de feuille avec le numéro du premier produit (B2). Au lieu de la valeur statique (6), entrez la formule là-bas, qui examinera la quantité de marchandises en divisant le montant total (D2) pour le prix (C2):

   = D2 / C2

   Cliquez sur le bouton ENTER.



2. Nous avons éteint le premier lien cyclique, la relation dans laquelle est familière avec la flèche de trace. Mais comme on le voit, le résultat est erroné et égal à zéro, car il a déjà été dit avant, Excel bloque l'exécution des opérations cycliques.



3. Copiez l'expression dans toutes les autres cellules de la colonne avec la quantité de produits. Pour ce faire, définissez le curseur sur l'angle inférieur droit de cet élément qui contient déjà la formule. Le curseur est converti en une croix, appelée pour appeler un marqueur de remplissage. Effacez le bouton gauche de la souris et tirez cette croix à la fin de la table.



4. Comme vous pouvez le constater, l'expression a été copiée à tous les éléments de la colonne. Mais une seule relation est marquée de la flèche de trace. Notez-le pour l'avenir.

Recherche de liens cycliques

Comme nous l'avons déjà vu plus haut, pas dans tous les cas, le programme marque la relation de la référence cyclique avec des objets, même si elle est sur la feuille. Compte tenu du fait que, dans les opérations cycliques majoritaires majoritaires, elles devraient être supprimées. Mais pour cela, ils doivent d'abord trouver. Comment faire cela si les expressions ne sont pas étiquetées avec la ligne de flèche? Traisons cette tâche.

1. Donc, si vous démarrez un fichier Excel, vous avez une fenêtre d'informations qu'il contient une liaison cyclique, elle est souhaitable de le trouver. Pour ce faire, passez à l'onglet "Formulas". Cliquez sur le ruban du triangle, situé à droite du bouton "Vérification des erreurs", situé dans le bloc d'outil "Dépendance de dépendance". Un menu s'ouvre dans lequel le curseur doit être hébergé sur "Liens cycliques". Après cela, le menu suivant ouvre une liste d'adresses d'éléments de feuille dans lesquels le programme a découvert des expressions cycliques.



2. Lorsque vous cliquez sur une adresse spécifique, la cellule correspondante est sélectionnée sur la feuille.

Il existe une autre façon de savoir où se trouve la liaison cyclique. Le message sur ce problème et l'adresse de l'élément contenant une expression similaire est située sur le côté gauche de la chaîne d'état, située au bas de la fenêtre Excel. VRAI, Contrairement à la version précédente, les adresses de tous les éléments contenant des références cycliques seront affichées sur la barre d'état, s'il y en a beaucoup d'entre eux, mais une seule d'entre elles apparaissait devant d'autres.

De plus, si vous êtes dans un livre contenant une expression cyclique, non sur la feuille où il se trouve, et de l'autre, alors dans ce cas, seul un message sur la présence d'une erreur sera affiché dans la barre d'état.

Leçon: Comment trouver des liens cycliques pour exceller

Correction des références cycliques

Comme mentionné ci-dessus, dans la majorité écrasante des cas, les opérations cycliques sont diaboliques, d'où elles devraient être facilement éliminées. Par conséquent, il est naturel qu'après la détection de la connexion cyclique, il est nécessaire de le corriger pour apporter la formule à la forme normale.

Afin de corriger la dépendance cyclique, vous devez suivre toute l'interconnexion des cellules. Même si la vérification indiquait une cellule spécifique, l'erreur peut ne pas être recouverte de celle-ci elle-même, mais dans un autre élément de la chaîne de dépendance.

1. Dans notre cas, malgré le fait que le programme indiquait correctement l'une des cellules cyclables (D6), l'erreur réelle se situe dans une autre cellule. Sélectionnez l'élément D6 pour savoir quelles cellules tirent la valeur. Nous examinons l'expression dans la chaîne de formule. Comme on le voit, la valeur de cet élément de feuille est formée en multipliant le contenu des cellules B6 et C6.



2. Aller à la cellule C6. Nous l'en soulignons et regardons la chaîne de formule. Comme on voit, c'est la valeur statique habituelle (1000), qui n'est pas un produit calculant la formule. Par conséquent, il est prudent de dire que l'élément spécifié ne contient pas d'erreurs causant des opérations cycliques.



3. Allez à la cellule suivante (B6). Après la sélection dans la ligne de formule, nous voyons qu'il contient une expression calculée (= D6 / C6), qui tire les données d'autres éléments de table, en particulier de la cellule D6. Ainsi, la cellule D6 fait référence aux données de l'élément B6 et inversement, ce qui provoque une boucle.

   

   Ici, la relation que nous avons calculée assez rapidement, mais dans la réalité, des cas où de nombreuses cellules sont impliquées dans le processus de calcul et non trois éléments que nous avons. Ensuite, la recherche peut prendre beaucoup de temps, car elle devra étudier tous les éléments cycliques.

4. Maintenant, nous devons comprendre quelle cellule (B6 ou D6) contient une erreur. Bien que, officiellement, ce n'est même pas une erreur, mais simplement une utilisation excessive de références qui conduit à une boucle. Pendant le processus de résolution de quelle cellule doit être modifiée, vous devez appliquer la logique. Il n'y a pas d'algorithme clair d'action. Dans chaque cas, cette logique sera sa propre.

   Par exemple, si notre table partageait le montant total doit être calculée en multipliant le nombre de biens réellement vendus à son prix, nous pouvons dire que le lien comptant le montant du montant total de la vente est clairement superflu. Par conséquent, nous le supprimons et remplacons-le par une importance statique.



5. Une telle opération est effectuée sur toutes les autres expressions cycliques si elles sont sur la feuille. Après absolument, tous les liens cycliques ont été supprimés du livre, le message de la présence de ce problème devrait disparaître de la chaîne d'état.

   De plus, les expressions cycliques ont été complètement supprimées, vous pouvez découvrir à l'aide de l'outil de vérification des erreurs. Allez dans l'onglet "Formulas" et cliquez sur le Triangle déjà familier pour nous à la droite du bouton "Erreurs de vérification" du groupe Instruments "Selon les formules" . Si l'élément "Liens cycliques" dans le menu exécutant n'est pas actif, cela signifie que nous avons supprimé tous ces objets du document. Dans le cas contraire, vous devrez appliquer la procédure de retrait aux éléments énumérés, de la même manière.

Autorisation de l'exécution des opérations cycliques

Dans la partie précédente de la leçon, nous avons dit, principalement comment traiter des références cycliques, ou comment les trouver. Mais, plus tôt, la conversation était également à ce sujet dans certains cas, ils peuvent, au contraire, peuvent être utiles et consciemment utilisées par l'utilisateur. Par exemple, très souvent, cette méthode est utilisée pour des calculs itératifs lors de la construction de modèles économiques. Mais le problème est que, quels que soient, que vous soyez consciemment ou inconsciemment, vous utilisez une expression cyclique, Excel bloquera toujours l'opération par défaut, afin de ne pas entraîner une surcharge excessive du système. Dans ce cas, la question de la désactivation forcée de ce blocage devient pertinente. Voyons comment le faire.

1. Tout d'abord, nous passons à l'onglet "Fichier" de l'application Excel.



2. Ensuite, cliquez sur l'élément "Paramètres" situé sur le côté gauche de la fenêtre ouverte.



3. La fenêtre de paramètre exil commence à courir. Nous devons entrer dans l'onglet "Formulas".



4. Il est dans la fenêtre qui s'ouvre sera autorisé à effectuer l'exécution des opérations cycliques. Allez dans le bloc droit de cette fenêtre, où les paramètres Excel sont directement. Nous travaillerons avec le bloc de paramètres «Paramètres de calcul», situé au sommet.

   Pour permettre l'utilisation d'expressions cycliques, vous devez installer une coche concernant le paramètre "Activer les calculs itératifs". De plus, dans le même bloc, vous pouvez configurer le nombre limite d'itérations et l'erreur relative. Par défaut, leurs valeurs sont respectivement de 100 et 0,001. Dans la plupart des cas, ces paramètres ne doivent pas nécessairement être modifiés, bien que si nécessaire, il est possible de modifier les champs spécifiés. Mais ici, il est nécessaire de considérer que trop d'itérations peuvent entraîner une charge grave sur le programme et le système dans son ensemble, surtout si vous travaillez avec un fichier dans lequel de nombreuses expressions cycliques sont placées.

   Nous installons donc une coche sur le paramètre "Activer les calculs itératifs", puis que les nouveaux paramètres entrés en vigueur, cliquent sur le bouton "OK", situé au bas de la fenêtre des paramètres Excel.



5. Après cela, nous allons automatiquement à la feuille du livre actuel. Comme nous le voyons, dans les cellules dans lesquelles sont situées des formules cycliques, les valeurs sont maintenant calculées correctement. Le programme ne bloque pas les calculs en eux.

Mais il convient toujours de noter que l'inclusion d'opérations cycliques ne doit pas être abusée. Appliquer cette fonctionnalité ne suit que lorsque l'utilisateur est totalement confiant dans son besoin. L'inclusion déraisonnable d'opérations cycliques peut non seulement conduire à une charge excessive sur le système et ralentir les calculs lorsque vous travaillez avec un document, mais l'utilisateur peut effectuer involontairement une expression cyclique erronée, qui serait par défaut, elle serait bloquée par le programme.

Comme nous le voyons, dans la majorité écrasante des cas, les références cycliques sont un phénomène avec lequel vous devez vous battre. Pour cela, tout d'abord, vous devez détecter la relation cyclique elle-même, puis calculer la cellule dans laquelle l'erreur est contenue et l'élimine enfin en faisant les ajustements appropriés. Mais dans certains cas, les opérations cycliques peuvent être utiles lors du calcul et utilisés par l'utilisateur consciemment. Mais même par là, il vaut la peine d'utiliser leur utilisation avec prudence, de configurer correctement Excel et de connaître la mesure dans l'ajout de telles références, qui, lorsqu'elles sont utilisées en quantités de masse, peuvent ralentir le fonctionnement du système.