Monsieur Excel
Pour tout savoir faire sur Excel !

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.

24 novembre 2017

Enigme 2 – Solution manuelle

Dans le premier article, je vous disais que j’avais une assez bonne idée de ce que devait être une solution optimale. Il est temps à présent de vous expliquer quel était mon raisonnement.

Je me suis dit qu’il était préférable de remplir d’abord les quatre cubes du milieu, car c’étaient ceux qui allaient impacter le plus grand nombre d’autres cubes. Puis, dans une seconde phase, les huit cubes latéraux (coins exclus), et enfin les quatre coins.

J’ai donc rempli d’abord les quatre cubes centraux, comme on peut le voir en E1:H4 dans la copie d’écran ci-dessous, puis les huit cubes latéraux – en faisant des déplacements de cavaliers aux échecs pour bien les répartir. En enfin les quatre coins, ne les répartissant aussi de façon variée. Et – comme par hasard – je suis tombé sur un résultat de 352.


Vérification de l’impact des cellules initiales

Pour illustrer l’impact des trois types de cellules initiales, j’ai fait trois copies d’écran, la première en mettant =na() dans un bloc central, la seconde en le mettant dans une cellule latérale, et la troisième en le mettant dans un coin. On voit clairement que les impacts vont en décroissant.

En fait 352 = 9*(1+2+3+4)+3*(5+6+7+8+9+10+11+12)+1*(13+14+15+16) !

Ce qui comme par hasard a un rapport avec le contenu du message anonyme laissé sur le dernier article…

Un nouveau challenge

Maintenant, nous allons compliquer un peu le problème... Quelle est la valeur minimale affichée sur le dernier cube si l'on ajoute la contrainte suivante : tous les cubes doivent avoir une valeur différente.  Ce qui n'est pas respecté dans notre exemple où il y a deux 10...

19 novembre 2017

Enigme 2 – Simulation n°2

Comme toutes les meilleurs solutions que je trouvais avaient les valeurs de 1 à 4 dans le carré central, j’ai donc décidé de figer ces quatre valeurs et de ne plus tirer que les 12 numéros restants. C’est ce que vous pouvez observer dans la copie d’écran suivante. Du coup, j’ai dû modifier toutes en conséquence les formules du bloc E1:H4.

L’insert dans la copie d’écran vous montre les 12 formules restantes dans ce bloc. Certes, j’aurais pu ne faire qu’une seule formule, la même dans les 12 cellules, à grand renfort de fonctions ligne() et colonne(), mais ces formules auraient été assez lourdes…


Toutes les autres formules de la feuille restent inchangées. J’ai aussi modifié la macro car cela n’avait plus de sens d’effectuer autant de tirages. En effet, après quelques tâtonnements, j’ai découvert qu’il suffisait de 3.000 itérations – au lieu de 100.000 ! – et 2 à 3 secondes pour trouver à tous les coups la solution optimale de 352, comme on en voit un exemple dans le bloc S1:V4.

Remarque 1 – Mes félicitations aux deux lecteurs qui ont déclaré dans des commentaires sur le premier article de cette série avoir trouvé une solution de cette valeur.

Remarque 2 – C'est logique qu'il ait fallu beaucoup moins d'itérations : il y a en effet 43.680 combinaisons de moins avec 12! qu'avec 16! ...

11 novembre 2017

Enigme 2 – Simulation n°1

En analysant le problème posé dans l’article précédent, je voyais bien comment créer une très bonne solution,  mais je me suis dit qu’il serait intéressant de voir comment trouver – par étapes successives – la meilleure solution par simulation dans Excel.

Je me suis donc dit que l’on pouvait en toute logique se limiter au départ aux valeurs de 1 à 16 pour le bloc A. J’ai alors mis au point un modèle dans lequel je tirais ces nombres au hasard.

Voici la formule de B1, recopiée vers le bas : =alea().

En colonne C, j’ai calculé la position de chaque aléa avec en C1 la formule : =rang(B1;B:B).

En E1, recopiée dans le bloc E1:H4, la formule =equiv(4*ligne()+colonne()-8;$C:$C;0) tire au hasard un des 16 nombres.

En J1, recopiée dans le bloc J1:L3, la formule =somme(E1:F2).

En N1, recopiée dans le bloc N1:O2, la formule =somme(J1:K2).

Enfin, en Q1, la formule =somme(N1:O2).

J’ai ensuite créé une macro qui faisait 100.000 itérations en copiant le bloc E1:H4 et en le collant en S1 chaque fois que la solution en Q1 était inférieure à celle de X1, que je mettais alors à jour avec la nouvelle solution.

Chaque simulation de 100.000 itérations durait 28 à 29 secondes et me donnait des résultats entre 352 et 360.


J’ai constaté que toutes les simulations que je faisais aboutissaient à un résultat dans lequel les quatres cubes du milieu portaient les numéros 1 à 4.

J’ai alors augmenté à 1.000.000 d’itérations et observé que, dans ce cas, je trouvais toujours 352.

Bravo donc aux deux lecteurs qui ont proposé cette solution en commentaire dans l’article précédent !

05 novembre 2017

Enigme 2 – Les cubes

Nous nous attaquons aujourd’hui à une seconde énigme qui sera une nouvelle occasion pour nous de voir comment – à l’aide d’Excel – résoudre simplement des problèmes complexes.

Nous avons en A une série de 16 cubes présentée sous la forme d’un carré. Chaque cube est marqué d’un nombre entier, ces nombres entiers étant tous différents les uns des autres. Nous avons illustré dans le graphique une possibilité de numérotation des 4 cubes de A en avant et à gauche.

Nous avons en B une série de 9 cubes présentée elle aussi sous la forme d’un carré.

Nous superposons alors les cubes de B, de façon centrée, sur les cubes de A ainsi que le montre l’illustration graphique.

On marque sur chaque cube de B la somme des nombres des cubes de A avec lesquels il est en contact. Dans notre exemple graphique, le cube en avant et à gauche de B portera la valeur 10, car c’est la somme des valeurs des 4 cubes de A avec lequel il est en contact.

On a ensuite une série C de 4 cubes que l’on place – de la même façon – centrée sur la série de cubes de B.

Chaque cube de C porte un nombre égal à la somme des nombres des cubes de B avec lesquels il est en contact.

Enfin, on place un cube unique D centré sur la série de cubes de C et on lui attribue un nombre égal à la somme des nombres des cubes de C.


Le problème à résoudre est le suivant : quel nombre doit-on mettre sur chaque cube de A de façon à minimiser la valeur affichée par le cube de D ?

Je vous laisse réfléchir jusqu’au prochain article…

23 octobre 2017

Mes formations en décembre

Vous trouverez ci-dessous la liste des formations que je propose pour décembre 2017. Chacune de ces formations est limitée à huit participants, chaque participant venant avec son ordinateur.

  • Modélisation avec Excel (2 jours) : les mardis 12 et 19 décembre 2017.
  • Création de tableau de bord sous Excel : le jeudi 14 décembre 2017.
  • Modélisation du risque : le jeudi 21 décembre 2017.
Vous trouverez la description détaillée de ces formations en cliquant dans le ruban droit, dans le gros bouton « Prochaines formations ».

Les autres séminaires habituels ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision.

Avec la formation « Modélisation avec Excel », je garantis de transformer en deux jours tout utilisateur moyen d'Excel en « power user », c'est-à-dire à l'amener au niveau des 5% des meilleurs utilisateurs d'Excel.

Avec la formation « Modélisation du risque », vous découvrirez comment utiliser Excel pour faire des simulations probabilistes (dites de Monte Carlo). Nous utiliserons à cet effet l’add-in Crystal Ball, le meilleur produit au monde dans cette catégorie.

La formation « Création de tableau de bord sous Excel » vous permettra, en une seule journée, de totalement maîtriser la création d’un tableau de bord personnalisé, comme vous pouvez le voir dans l’article
« Notre premier tableau de bord » du 22 mai 2013.

Chacune de mes formations peut être animée en intra dans votre entreprise et – le cas échéant – personnalisée grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne manquez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.