Monsieur Excel
Pour tout savoir faire sur Excel !

24 septembre 2016

L’énigme des pélerins (b)

Voyons à présent comment est construit le modèle Excel que j’ai présenté à la fin du précédent article.

Vous pouvez vous demander pourquoi je n’ai pas mis de formule de somme pour la colonne du milieu et la ligne du milieu, dans chaque étage. Ce n’est de fait pas indispensable mais, comme nous le verrons dans l’article suivant, cela nous simplifiera le travail par la suite.


Le bloc D21:D24 sert à vérifier que la contrainte de 11 pèlerins par axe (N, E, S et O) est bien satisfaite.

En F21, on doit avoir un résultat de 0 pour que la contrainte spécifiant qu’il y a deux fois plus de pèlerins en haut qu’en bas soit bien respectée.

On pourrait aussi donner aux 16 chambres un format conditionnel mettant le fond en rouge si le nombre saisi n’est pas un entier de 1 à 3. Ou bien leur attribuer une zone de validation avec un entier de 1 à 3…

Nous avons du dans l’article précédent que nous pouvions nous limiter à 5 cas possibles : 24, 27, 30, 33, 36. Commençons par 24. Pour cela, il faut exactement 1 personne dans chaque chambre du bas. On constate alors, en faisant des essais, que ce n’est pas possible si l’on veut en même temps satisfaire les contraintes exigeant un total de 11 personnes dans chaque axe principal et la contrainte d’avoir 16 personnes en haut.

En fait, la première solution possible correspond à 27 personnes. Voici une solution pour 27 pèlerins, et ce n’est pas la seule que j’aie trouvée :


18 septembre 2016

L’énigme des pèlerins (a)

L‘énigme qui suit est une ancienne énigme : elle a été publiée en 1907, créée par Henry Ernest Dudeney et inspirée par les célèbres « Canterbury Tales » de l’écrivain anglais Geoffrey Chaucer (1340-1400) .

Un monastère doit recevoir des pèlerins. Son dortoir contient deux étages avec 8 chambres chacun, l’escalier se trouvant au centre de l’ensemble. Le prieur a exigé que chaque chambre soit occupée par au moins 1 personne, et au maximum 3 personnes. Chaque côté (Nord, Est, Sud, Ouest) de chaque étage doit avoir exactement 11 pélerins (en comptant les deux étages). En outre, il doit y avoir exactement 2 fois autant de pèlerins au premier étage qu’au rez-de-chaussée.

Les moines étant très astucieux, ils parviennent à planifier le logement pour tous les pèlerins annoncés. Mais, quand les pèlerins arrivent, on découvre qu’il y en a 3 de plus que prévu. Heureusement, les moines trouvent une nouvelle solution qui, elle aussi, satisfait toutes les contraintes.

Quel était le planning initial d’occupation des chambres ?
Quel est le planning final ?

Des premiers éléments de réflexion…

Pour vous mettre un peu sur la voie, on sait que le nombre de pèlerins est compris entre 24 (1 par chambre en bas et 2 par chambre en haut) et 36 (3 par chambre en haut et en moyenne 1,5 par chambre en bas). On sait aussi que c’est un multiple de 3, vu qu’il y en a 2 fois plus en haut qu’en bas. Vous voyez, avec un peu de bon sens, on se limite déjà à 5 cas possibles : 24, 27, 30, 33, 36.

Dans l’exemple ci-dessous, nous avons mis en place une solution qui aboutit au total de 27 pèlerins mais qui ne satisfait pas les contraintes en D21:D24 où l’on devrait avoir un total de 11 à chaque fois. En revanche, toutes les autres contraintes sont satisfaites…



13 septembre 2016

Le chameau et les bananes

Plusieurs lecteurs m’ont fait savoir qu’ils avaient apprécié l’utilisation d’Excel pour résoudre des énigmes. En voici donc une autre, celle du chameau, du désert et des bananes !

Un chameau mangeur de bananes doit traverser un désert de 1.500 kms. Il dispose pour cela au point de départ d’un stock de 3.000 bananes qui, vu la sécheresse ambiante, se conservent toutes seules.

Il consomme de façon continue une banane au km. et peut en porter 1.000 au maximum. Il peut constituer des stocks de bananes. Ainsi, il pourrait faire le trajet suivant : partir à plein, faire 250 kms, déposer 500 bananes, et enfin revenir à son point de départ.

Arrivera-t-il vivant au bout du désert et, dans l’affirmative, combien lui restera-t-il de bananes en arrivant à sa destination ?

Bon, là je vous laisse travailler. Vous pourrez reprendre la lecture plus tard pour vérifier si vous avez trouvé la bonne solution…

Solution de l’énigme

Comme de nombreuses énigmes, celle-ci se résout très simplement si l’on démarre de la fin. Le plus long trajet que peut faire le chameau est un trajet de 1.000 kms, à condition de partir chargé à bloc.

A part ce trajet unique, le chameau doit effectuer des trajets en 3 parties : aller – dépôt de banaces et retour – re-aller pour revenir au même point.

Ensuite, il est obligé de couvrir le terrain 5 fois : 2 fois (aller puis dépôt puis retour) – en enfin le dernier aller.


Le modèle ci-dessus répond à toutes nos questions. Nous constatons que, avec les données de l’énoncé, le chameau arrive à la fin du désert avec un solde de 33,33 bananes !

08 septembre 2016

Une énigme résolue ...

Voici le tableau que j’ai construit pour résoudre l’énigme présentée dans l’article précédent.

J’ai pris comme point de départ en A3 que Calixte avait actuellement une chance sur deux (50%) de se réveiller la nuit. J’en ai déduit – cf. les formules reproduites dans le bloc encadré de bleu – les probabilités de réveil chaque année de Samuel (colonne B) et de Calixte (colonne C) et, par voie de conséquence, la probabilité en colonne D de ne pas être réveillé la nuit. L’objectif à atteindre (6 nuits sur 7) étant affiché en F3.


Etape 1 – Calcul de la probabilité de réveil de Calixte

La première étape consiste à remplacer A3 par la probabilité de réveil qui aboutit à la sutuation actuelle, c’est-à-dire 1 chance sur 3 de non-réveil. Utilisons donc la valeur cible (via DonnéesAnalyse scénarios) pour cela. Nous obtenons un résultat de 33,41%, ce qui n’est pas terrible. Recommençaons avec le solveur et, là, nous obtenons le résultat exact.

Remarque 1 – La valeur cible du solveur est de bien meilleure qualité que la valeur cible standard. N’hésitez pas à passer par le solveur pour calculer une valeur cible !

Nous constatons à présent que la probabilité de non-réveil des parents est à 80,57% dans 2 ans et à 90,06% dans 3 ans. Notre objectif de 85,71% sera donc atteint dans un peu plus de 2 ans.

Etape 2 – Création de la représentation graphique

Sélectionnons à présent – avec la touche [Ctrl] enfoncée – les blocs A4:A6 et D4:D6 puis insérons un graphe en nuage de points (soit en X/Y). Puis modifions le type de graphe en ligne et ajoutons-lui une courbe de tendance logarithmique, en demandant l’affichage de l’équation.

Remarque 2 – Nous avons choisi une croissance logarithmique car cela correspond bien à la division par 2 chaque année.

Nous obtenons alors le graphe de la copie d’écran ci-dessous. Il ne reste plus qu’à entrer 2 en B9 – une première estimation (grossière) du nombre d’années à attendre – et la formule =0,2472*ln(B9)+0,6311 en C9.

Etape 3 – Calcul de la solution exacte

Il ne nous reste plus qu’à trouver la valeur de x (en B9) qui nous donne le résultat attendu (85,71%) de y (en C9). Encore un boulot pour la valeur cible du solveur d’Excel qui trouve le résultat final : 2,495 années.

C’est donc quand Calixte aura 3,5 ans que les parents pourront dormir tranquilles 6 jours sur 7 !


En guise de conclusion

Bon, j’ai bien pris mon temps pour tout vous expliquer en détail, mais voici le déroulé des opérations une fois que l’on a créé le tableau et les formules de départ (cf. la première copie d’écran) :
  • Calcul par la valeur cible de la valeur en B3 qui nous donne un résultat de 1/3 en D3
  • Sélection de A4:A6 et D4:D6, graphe en X/Y, puis en ligne
  • Ajout d’une courbe d’ajustement logarithmique avec affichage de l’équation
  • Entrée de cette formule et C9 et calcul par valeur cible du B9 qui donnera 85,71% en C9