Calcul du nombre d’itérations

Pour en terminer avec le thème abordé dans les deux précédents messages, nous allons voir aujourd’hui comment calculer le nombre d’itérations requis, dans notre exemple de calcul de marge, pour obtenir le résultat final au centime près. Pour cela, nous devons utiliser les réglages suivants :

 

  

Notez que nous avons demandé un calcul sur ordre avec 100 itérations au maximum (ce sera plus que nécessaire pour obtenir la précision demandée) et un écart maximal de 0,01 qui correspond au centime requis comme précision.

Il y a encore deux problèmes à résoudre :

• comment faire pour réinitialiser les valeurs après un premier calcul de marge ?

• comment obtenir une précision de 0,01 si le compteur d’itérations est incrémenté de 1 – donc 100 fois trop ! – à chaque itération ?

Réinitialisation de la simulation                         

Pour réinitialiser les valeurs après un calcul itératif, la solution la plus efficace est de créer un drapeau que l’on met à 0 pour réinitialiser puis à 1 quand on souhaite que le calcul itératif s’effectue. C’est ce que nous avons fait avec la cellule E2 :

Gestion du compteur d’itérations

Pour que le compteur d’itérations ne casse pas la convergence en ajoutant 1 à chaque itération – auquel cas l’écart maximal de 0,01 ne serait jamais obtenu – l’astuce consiste simplement à compter les itérations en millièmes, donc en ajoutant à chaque itération un incrément inférieur à 0,01 !

Convergence vers la solution finale

Il ne nous reste plus qu’à saisir 1 dans le drapeau en E2 et à lancer le calcul par [F9]. Le résultat apparaît instantanément, avec 142,85 € en B4 et 0,008 en B6. Il a donc suffi de huit itérations pour converger vers la solution au centime près !

Réglage de la référence circulaire

Cet article fait la suite de notre article « Création d’une référence circulaire » présenté le 14 février.

Pour avoir accès aux réglages du calcul itératif, il nous a fallu utiliser la commande « Options » d’Excel, puis l’onglet « Formules » :

Dès que nous avons coché l’option « Activer le calcul itératif », Excel a fait très rapidement quelques itérations et s’est aussitôt arrêté sur la solution finale de 142,86 €.

Quand on active le calcul itératif, il faut immédiatement activer l’option de calcul manuel, car il est alors important de contrôler le moment où l’on lance le calcul itératif. Celui-ci appliquera par défaut les deux réglages proposés, à savoir le nombre maximal d’itérations à 100 et l’écart maximal à 0,001.

Cela signifie qu’Excel va réaliser au maximum 100 itérations mais qu’il peut aussi s’arrêter bien avant, en fait dès que – d’une itération à l’itération suivante – aucune cellule du classeur actif n’aura vu sa valeur modifiée de plus que 0,001 (l’écart maximal) en valeur absolue.

Comment mesurer la vitesse de convergence ?

Il n’est pas facile, avec notre petit exemple en six cellules, de savoir combien d’itérations Excel a réellement opérées avant de s’arrêter sur le prix de vente définitif.

Pour le découvrir, il nous suffit de régler à 1 le nombre maximal d’itérations, d’effacer à présent la formule de la cellule B4, de lancer un calcul avec [F9] pour que la cellule B3 retrouve sa valeur initiale de 0, puis de ré-entrer la formule de sommation en B4.

Le prix de vente est maintenant de 100 € puisque seule la cellule B4 a pu être calculée, au moment où sa formule a été validée :

Utilisons la touche [F9] pour lancer le calcul à chaque itération : B4 prend alors successivement les valeurs 130 – 139 – 141,70 – 142,51 – 142,75 – 142,83 – 142,85 – 142,85 – 142,86.

Il a donc fallu moins de 10 itérations pour converger vers la solution définitive.

Création d’une référence circulaire

Désolé, mais j’étais chargé ces temps-ci, entre les trois développements Excel que j’assure en ce moment pour des entreprises et ma présence ces deux derniers jours aux TechDays de Microsoft, dans lequel j’ai répondu aux questions du public dans le thème  « Ask The Expert » sur le stand Office ; je vous retrouve donc après un sevrage de six jours...

Réalisons un petit modèle dans lequel nous entrons un prix d’achat en B2, nous déclarons en B3 que nous allons faire une marge de 30% du prix de vente et, en B4, nous demandons la somme grâce à l’outil de sommation.

Pour plus de clarté, j’ai reproduit en colonne C ce qui avait été saisi dans la colonne B…

Et patatras ! Quand je cherche à valider la formule de sommation en B4, Excel m’annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :

Il s’agit d’une sécurité normale car, le plus souvent, les références circulaires sont tout à fait involontaires, il s’agit d’erreurs de conception qu’Excel signale avec raison.

Il se peut cependant que – c’est d’ailleurs le cas ici – qu’il ne s’agisse pas d’une erreur mais que nous souhaitions effectivement calculer un résultat par convergence. C’est par exemple indispensable si vous construisez un modèle de calcul d’ISF (Impôt Sur la Fortune) : en effet, dans ce cas, on peut déduire, pour calculer la base imposable, le montant de l’ISF à payer qui dépend de cette même base imposable ! Encore une invention d’énarque...

Pour valider le calcul en références circulaires, il vous suffit d’appeler la commande Options d’Excel, onglet « Formules », puis de cocher la case « Activer le calcul itératif », comme nous l’avons fait ci-dessous.

Le résultat final de 142,86 € apparaît alors aussitôt dans la cellule B4 !

Et voilà, ce n’est pas plus compliqué que cela...

Nous verrons bientôt comment tirer profit des deux réglages suivants, le nombre maximal d’itérations et l’écart maximal.

Les Techdays de Microsoft

La semaine prochaine, nous avons les Techdays de Microsoft, du 12 au 14 février, au Palais des Congrès de la Porte Maillot.

J’y serai  présent mardi 12 et mercredi 13, le matin dans les deux cas, au stand « Ask The Expert » de Microsoft. Ceux qui veulent  en profiter pour discuter avec moi peuvent venir me rencontrer à cette occasion.

Si vous voulez être sûr que je ne sois pas trop pris au moment où vous viendrez, envoyez-moi un mail à thiriez@hec.fr pour indiquer le jour et l’heure de votre passage. Je pourrai ainsi – si nécessaire – vous proposer un créneau voisin ou alternatif pour que vous n’ayez pas trop à attendre…

Formation « Modélisation avec Excel »

Dans cette formation, je transforme en deux jours, avec remboursement garanti en cas de non-satisfaction (ce qui ne m’est encore jamais arrivé en 15 ans) un utilisateur moyen d’Excel en « power user », c’est-à-dire un utilisateur se plaçant dans le top 5% des utilisateurs.

Mon dernier séminaire sur ce sujet – cf. article « Mes formations en janvier 2013 » du 17 décembre 2012 – ayant été saturé, j’ai décidé d’en animer une nouvelle session. 

Cette session se tiendra le mercredi 13 mars et le lundi 18 mars dans les locaux de Finance 3.1, au 9 avenue de l’Opéra. Il reste encore deux places disponibles.

Cette formation, comme toutes mes formations dont vous trouverez la description dans l’article ci-dessus, peut aussi être personnalisée et animée en intra dans votre entreprise.

Réflexions sur la volatilité

Dans l’article précédent, nous avons vu que, par défaut, les fonctions personnalisées n’étaient pas volatiles, mais qu’on pouvait les rendre volatiles en mettant l’instruction Application.Volatile au début de leur code.

Une autre façon de résoudre le problème est l’astuce utilisée par la formule utilisée aujourd’hui en C3 : en ajoutant puis soustrayant une fonction volatile – telle que aujourdhui() ou maintenant() – à somme_2(), on rend cette fonction volatile même quand la commande Application.Volatile est désactivée.

Le concept de volatilité n’est pas simple à maîtriser… Dans notre exemple, quand on lance le recalcul des nombres aléatoires par [F9], la cellule C2 ne se recalcule pas. En revanche, si l’on modifie à la main la valeur de l’une quelconque des cinq cellules de A1 à A5, C2 se recalcule…

En fait, il y a même de nombreux désaccords entre Microsoft et les experts d’Excel sur ce qui est ou n’est pas volatile dans Excel.

Pire encore, la volatilité d’une fonction peut très bien changer d’une version d’Excel à l’autre !!!

Et, plus étrange même, cette volatilité peut aussi dépendre des arguments que vous avez utilisés…

Pour en savoir plus sur ce sujet, je vous conseille vivement la lecture de la passionnante analyse publiée à l’adresse suivante :

http://www.decisionmodels.com/calcsecretsi.htm

Dans cet article, l’auteur spécifie clairement qu’il est préférable de ne pas avoir recours à l’instruction Application.Volatile, car cela ralentit les calculs en forçant Excel à effectuer des recalculs trop fréquents.