Monsieur Excel
Pour tout savoir faire sur Excel !

15 janvier 2018

Réglage de la référence circulaire

Cet article fait la suite de notre première présentation des références circulaires, le 10 janvier dernier.

Quand on active, en cochant « Activer le calcul itératif » dans la commande Fichier – Options – Formules, les références circulaires, on a accès à deux réglages, le nombre maximal d’itérations et l’écart maximal.

Le nombre maximal d’itérations est le nombre d’itérations qui vont être effectuées lors d’un calcul de la feuille. Fixons-le à « 1 » pour l’instant. Cette commande n’a donc de sens que si l’on a sélectionné « Manuel » dans le bloc « Calcul du classeur », comme dans l’encart dela copie d’écran ci-dessous :


Effaçons à présent la formule de la cellule B4, lançons un calcul avec [F9] pour que la cellule B3 retrouve sa valeur de 0, puis ré-entrons la formule de sommation en B4.

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

Lançons un calcul avec [F9] et nous constatons que B3 prend à présent une valeur de 30,00 € et B4 une valeur de 130,00 €.

Lançons un nouveau calcul avec [F9], et nous obtenons 39,00 € en B3 et 139,00 € en B4.
Lançons encore un calcul avec [F9], et nous obtenons 41,70 € en B3 et 141,70 € en B4.

Avec six calculs de plus, le résultat final avec 42,86 € en B3 et 142,86 € en B4 est enfin obtenu... Mais on était déjà à la bonne réponse à 1 centime près avec le quatrième calcul.

Quand on ne limite pas, comme nous l'avons fait ici, à 1 le nombre d'itérations, Excel arrête le calcul dès que soit le nombre maximal d’itérations a été atteint, soit l’écart maximal entre le calcul de n° (n-1) et le énième calcul – pour les valeurs de toutes les cellules du classeur – est inférieur à l’écart maximal défini.

10 janvier 2018

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

Nous allons aujourd’hui nous attaquer à un problème qui vous a sûrement intrigué un jour ou l’autre : l’apparition d’une référence circulaire !

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

Et patatras ! Excel nous annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :


Remarque – Le message d’Excel est inexact et, de plus, moins clair qu’il ne l’était dans les versions antérieures du logiciel : en effet, Excel ne semble plus envisager que vous ayez pu construire cette référence circulaire volontairement !

Il est vrai que, bien souvent, les références circulaires sont tout à fait involontaires. Il s’agit en général d’erreurs de conception qu’Excel signale avec raison.

Mais il se peut cependant – c’est 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’utiliser la commande Fichier – Options – Formules, et de cocher « Activer le calcul itératif », comme nous le voyons dans la copie d’écran 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 dans le prochain article comment tirer profit des deux réglages suivants, le nombre maximal d’itérations et l’écart maximal.

30 décembre 2017

Une nouvelle énigme (b)

Comme nous l’avons vu à la fin de l’article précédent, l’analyse de toutes les permutations possibles de 1 à 10 nous emmènerait trop loin, avec 3.628.800 permutations possibles. Mais nous pouvons facilement réduire le problème avec un peu de bon sens. Voici deux façons différentes d’y parvenir…

Réduction par analyse globale

Le nombre moyen par sommet est de 5,5 soit de 16,5 par groupe de trois lettres. Pour obtenir un total de 20 pour chacun des 5 trinômes importants, il faudra donc taper dans les nombres élevés. Et ce en priorité pour A et B, qui interviennent chacun 3 fois dans un trinôme de 20. Puis pour C et/ou D qui interviennent 2 fois dans un trinôme de 20 et une fois dans un trinôme de 17.

Réduction par analyse détaillée

Prenons le cas des sommets A et B. Comme chacun doit mener à 3 trinômes de total 20 avec à chaque fois 2 partenaires nouveaux, on peut éliminer pour eux les valeurs de 1 à 6. Si par exemple A était égal à 5, on pourrait avoir 5-9-6 ou 5-8-7 mais on n’aurait pas de troisième possibilité.

Solution possible

En tenant compte de deux remarques précédentes, on voit que l’on pourrait déjà bien simplifier le problème en mettant 10 en A et 9 en B. Il ne resterait alors plus que 7! combinaisons possibles, soit 5.040 cas à analyser. Si l’on trouve une solution, ce sera forcément la bonne. Sinon, il suffira de relâcher cette contrainte…

L’écran suivant montre comment j’ai résolu le problème. On tire les valeurs dans le bloc D3 :D12 à partir des aléas en colonne C. Les cellules à fond bleu sont celles définies par notre choix ci-dessus. La cellule « Ecart » en E20 calcule le nombre d’écarts par rapport aux objectifs.

On découvre en fait qu’il y a deux solutions possibles, que j’ai reproduites en I3:K12.


Remarque 1 – Nous comprenons pourquoi la question posée était d’obtenir le produit C*J*D car celui-ci reste le même avec ces deux solutions.

Sur 10 résolutions, il m’a fallu en moyenne 2.334 itérations ce qui correspond comme par hasard à 5.040/2 puisqu’il y a deux solutions.

Remarque 2 – On pourrait peut-être trouver d’autres solutions en mettant d’autres valeurs de départ en A et B. Mais cela va compliquer les formules en D5:D12…

23 décembre 2017

Enigme du tétraèdre (a)

Pour clore en beauté cette fin d‘année dans laquelle nous avons utilisé Excel pour résoudre plusieurs énigmes, je ne résiste pas à vous en proposer une dernière en guise de feu d’artifice. Cette dernière énigme de l’année a été publiée dans son n°41 de septembre par la revue Tangente, une excellente revue à laquelle je viens de m’abonner. 

Si vous voulez en savoir plus sur cette revue :   http://tangente-mag.com/

Vous devez attribuer aux cases de A à J, sans répétition, des nombres de 1 à 10, en respectant les contraintes suivantes :

1)    Les totaux des trinômes AEC, AGD, AFB, CHB et BID sont tous égaux à 20

2)    CJD = 17

Sachant tout cela, quel est le produit des trois valeurs de CJD ?

La meilleure solution n’est certes pas d’énumérer toutes les affectations possibles des nombres de 1 à 10 : il y en a 10!, soit 3.628.800, ce qui est quand même beaucoup, même pour Excel !


Joyeux Noël !

16 décembre 2017

De l’art avec Excel

Remarque – Je demande pardon à mes lecteurs fidèles pour le délai entre le message précédent et celui-ci. J’ai été en effet assez chargé avec d'une part un modèle Excel assez complexe que j’ai développé pour les sociétés d’autoroutes en France et d'autre part les modèles sur lesquels je travaille pour la start-up Alzohis que je vous avais présentée le 16 mars 2016.

J’ai découvert récemment le travail de Tatsuo Horiuchi. Juste avant sa retraite (il est né en 1940), il s’est dit qu’il s’est dit qu’il lui fallait un nouveau challenge. Il a alors acheté un ordinateur et s’est mis à tester les possibilités d’Excel.

Il a remarqué que les logiciels de dessin coûtent cher alors qu’Excel est disponible sur quasiment tous les ordinateurs. Puis qu’Excel est plus facile à utiliser que Microsoft Paint et possède aussi plus de fonctionnalités. Il s’est alors mis à créer des œuvres d’art exclusivement avec Excel. Comme vous pouvez le voir ci-dessous sur une de ses œuvres, des cerisiers en fleur, le résultat est tout à fait impressionnant.

A titre indicatif, le dessin ci-dessous comporte un peu plus de 200 lignes et 400 colonnes.

Si vous voulez en savoir plus et télécharger des œuvres :



06 décembre 2017

Deux énigmes résolues avec Excel

Dans les derniers mois, nous avons utilisé Excel pour résoudre deux énigmes, la première sur le remplissage d’un avion où le premier passager à entrer dans la carlingue s’est installé à la place n°1 en ignorant le siège qui lui était réservé, la seconde sur des piles de 16 cubes sur lesquels étaient inscrits des nombres tout différents les uns des autres.

L’énigme sur le remplissage d’avion a été présentée et analysée dans mes articles du 11 juillet au 10 août dernier. Nous avons présenté dans ces divers articles un certain nombre d’éléments intéressants aboutissant finalement à la résolution du problème.

L’énigme sur les cubes numérotés nous a occupés entre le 5 novembre et le 30 novembre dernier. Là, nous avons résolu le problème original et, dans le dernier article, un problème complémentaire.

Ce qui est intéressant, c’est que – pour les deux énigmes – il était possible, au moins en partie, de résoudre le problème par d’autres approches, en particulier par une approche analytique. Donc à partir de formules purement mathématiques.

On peut se demander pourquoi utiliser Excel pour résoudre – par simulation et donc en quelque sorte par tâtonnements – un problème que l’on pourrait parfois résoudre avec une approche purement mathématique. Il y a en fait deux réponses à cela.

La première est que l’approche purement mathématique exige une certaine compétence dans ce domaine et n’est donc pas – et de loin, souvent ! – compréhensible par le commun des mortels.

La seconde est que la simulation, outre le fait qu’elle soit bien plus naturelle et facile à comprendre, présente un autre avantage, considérable lui aussi. C’est que, dans une simulation, il est très facile d’intégrer de nouvelles contraintes qui, pour la résolution purement mathématique, soit rendraient le problème insoluble, soit en augmenteraient la difficulté de résolution de façon notable.

C’est pour ces deux raisons que, dans mon activité de consultant, j’ai souvent mis au point – pour aider mes clients à résoudre leurs problèmes – des modèles de simulation. Ce n’est pas par hasard que j’ai aidé à traduire en français  plusieurs versions de l’add-in Crystal Ball de simulation probabiliste et que j’ai écrit le livre « La modélisation du risque »…

30 novembre 2017

Enigme 2 – Aucune valeur dupliquée

Nous nous attaquons à présent à la seconde et dernière question de notre énigme : « Quelle est la plus petite valeur possible du cube final si les valeurs affichées sur les cubes des quatre niveaux sont différentes les unes des autres ? »

En fait, en analysant bien les résultats obtenus avec diverses solutions optimales pour la formulation initiale de l’énigme, nous constatons que la valeur « 10 » est toujours la seule qui se retrouve dupliquée. La solution s’impose alors : il suffit de modifier les formules de telle façon que, si l’on obtient 10, on met alors 17 à la place…

Vous pouvez voir les formules dans l’insert de la copie d’écran.


La simulation, même avec seulement 500 itérations, trouve toujours le résultat optimal de 365.

Remarque – Formule universelle

Dans la solution proposée, j’ai affiché les formules des 12 cellules non centrales du premier bloc. Elles sont toutes différentes les unes des autres avec =si(equiv(n ;…))n prend les valeurs de 1 à 12.

Certes, nous aurions pu créer – à grand renfort de ligne() et de colonne() – une formule unique qui aurait marché dans les 12 cellules, mais cette formule aurait été beaucoup plus lourde et complexe.