Références circulaires, cible et solveur - Modèles de décision

Caractéristiques de calcul répétitives et compléments.

Certaines fonctionnalités Excel et compléments Excel à la cause recalculent à plusieurs reprises:

• Les tableaux Excel.
• Scénarios.
• Références circulaires avec Iteration permis.
• Solveur.
• Valeur cible.
• Analyse des risques compléments.

Assurez-vous que lorsque vous utilisez ces fonctions que vous avez vraiment besoin d'eux, et que vous avez optimisé entièrement les blocs de formules qui seront recalculés à plusieurs reprises.

Objectif rechercher déclenche un nouveau calcul de tous les classeurs ouverts à chaque itération: essayez donc d'avoir seulement asingle petit classeur rapide ouvert lors de l'utilisation GoalSeek.

Références circulaires

la capacité d'Excel pour résoudre les problèmes itérativement de référence circulaire peut être utile, mais il est facile de surutilisation.

Le mode de calcul par défaut pour Excel est de désactiver l'itération, de sorte que si vous créez une référence circulaire et calculer, Excel détecte et vous avertit que vous avez une référence circulaire. Pour résoudre itérativement la référence circulaire, vous devez activer itération (Outils -> Options -> Calcul). Une fois que vous avez activé l'itération sur vous n'aurez pas plus de messages sur les références circulaires et Excel recalcule à plusieurs reprises jusqu'à ce qu'il atteigne la limite du nombre d'itérations, ou la plus grande variation de valeur au cours d'une itération est inférieure à la valeur de variation maximale spécifiée .

Il y a un problème majeur à l'utilisation des références circulaires: une fois que vous avez une référence circulaire, il est très difficile de faire la distinction entre les erreurs qui ont créé par inadvertance des références circulaires et la référence circulaire intentionnelle. Dans la plupart des cas (en particulier avec les calculs financiers), il est possible (et souhaitable) de «Déroulez la référence circulaire en utilisant une étape supplémentaire. Par exemple, si vous voulez calculer un solde de trésorerie qui comprend les intérêts sur le solde, vous pouvez utiliser une référence circulaire où l'intérêt dépend de l'équilibre et l'équilibre dépend de l'intérêt. Cela calcule les intérêts composés. Vous pouvez « déroulez » le calcul en calculant le solde avant intérêts, puis à calculer l'intérêt (simple ou composé), puis le solde final.

Références circulaires Intentional

Si pour une raison quelconque, vous ne pouvez pas « déroulez » le calcul alors je conseillerais en utilisant la technique de Stephen Bullen d'inclure une instruction IF dans vos références circulaires intentionnelles qui agit comme un interrupteur pour éteindre les références circulaires, et met en place la condition initiale de l'itération Solution.

Si A1 est égal à zéro et l'itération est désactivée, Excel ne considérera pas ce calcul comme une référence circulaire, de sorte que toute référence circulaire détectée est probablement intentionnelle. Réglez A1 à 1 et permettre à l'itération de demander Excel pour résoudre en utilisant itération.

Notez que tous les calculs circulaires convergent vers une solution stable. Une autre pièce utile de conseils de Stephen Bullen est de tester le calcul dans le calcul manuel avec le nombre d'itérations mis à 1. F9 sera une seule étape le calcul de sorte que vous pouvez regarder le comportement et voir si vous avez vraiment convergé à la solution correcte .

Références circulaires et vitesse de calcul.

Parce que Excel calcule la feuille par feuille références circulaires sans tenir compte des dépendances vous aurez tendance à obtenir calcul très lent si vos références circulaires couvrent plus d'une feuille de calcul. Essayez de déplacer les calculs circulaires sur une seule feuille de calcul ou d'optimiser la séquence de calcul de la feuille de calcul afin d'éviter les calculs inutiles.

Avant les calculs itératifs commencent Excel doit recalculer le classeur pour identifier toutes les références circulaires et leurs personnes à charge. Ce procédé est équivalent à 2 ou 3 itérations du calcul.

Après les références circulaires et leurs personnes à charge ont été identifiés chaque itération nécessite Excel pour calculer non seulement toutes les cellules dans la référence circulaire, mais aussi toutes les cellules qui dépendent des cellules dans la chaîne de référence circulaire. Donc, si vous avez un calcul lourd qui dépend de cellules dans la référence circulaire, il peut être plus rapide d'isoler ce dans un classeur fermé séparé et ouvrez-le pour recalcul après le calcul circulaire a convergé.

Et bien sûr, il est important de réduire à la fois le nombre de cellules dans le calcul circulaire et le temps de calcul pris par ces cellules.

Références circulaires et les FDU.

Si vous écrivez une UDF qui est soit dans ou en fonction de la chaîne circulaire, il sera exécuté à plusieurs reprises. Vous pouvez détecter si l'UDF est calculé au cours de la première ou de la deuxième étape du processus de calcul en vérifiant Application.Iteration. Ce sera faux au cours de la première étape et vrai au cours de la deuxième étape.