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

Test

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



28 août 2016

Les graphes combinés

Dans l’article précédent, j’expiquais tout ce que nous avions beaucoup perdu dans Excel, ces 20 dernières années, quant à la facilité de créer des graphes avec deux axes Y.

Heureusement, ils sont revenus à l’ordre du jour, sous le nom de graphes combinés, avec Excel 2016 !

Nous voyons ci-dessous ce que cela donne, avec un exemple où nous avons trois trimestres de chiffres d’affaires, ainsi que le total de ces trois trimestres.


Les graphes combinés sont accessibles directement dans la barre d’outils de la commande Insérer – bloc Graphiques : c’est l’avant-dernière icône de ce bloc. On eut aussi y accéder – cf. la copie d’écran – via la commande Graphiques recommandés, onglet « Tous les graphiques », en bas de la liste.

Dans le bas à droite de la fenêtre, on a la liste des colonnes, ce qui nous permet, pour chacune d’entre elles, de sélectionner à la fois le type de graphique et – si nécessaire – d’indiquer que l’on utilisera l’axe Y à droite.


En prime, le graphe illustre en temps réel l’effet des choix effectués par l’utilisateur.

23 août 2016

Bienvenue dans Excel 2016 (e)

Suite à l’article précédent, nous avons accepté la seconde proposition d’Excel en graphique recommandé, le graphe en cascade. Nous l’appelerons « waterfall », vu que c’est sous ce nom qu’il est connu dans le monde entier. 

C’est un graphique très parlant : les recettes apparaissent en bleu et les dépenses en orange. En reportant le haut de chaque barre bleue, ou le bas de chaque barre orange sur l’axe des Y, on voit combien il nous reste d’argent…


Quand Excel est passé de la version 2003 à la version 2007, le monde des graphes d’Excel a subi de très nombreux changements. C’est ainsi que nous avons perdu trois possibilités originales d’Excel :

  • Jusqu’à la version 2003 incluse, on pouvait sélectionner un point d’une série, puis le monter ou le baisser à volonté. Ce n’est plus possible aujourd’hui sauf à utiliser un add-in à cet effet…
  • Jusqu’à la version 2003 incluse, on pouvait sélectionner un bloc de cellules puis le tirer dans un graphe, ce qui ajoutait automatiquement la série au graphe. Aujourd’hui, ce n’est plus possible, il faut sélectionner le bloc, le copier, sélectionner le graphe, puis coller… C’est tellement plus long !
  • Jusqu’à la version 2003 incluse, Excel proposait en standard des graphes avec deux axes Y. Ce n’était hélas plus le cas depuis, avant la version 2016...
Remarque 1 – C’est bien là une des pertes les plus regrettables ! Imaginons en effet que vous ayez 8 séries  dont vous souhaitez mettre 4 sur l’axe gauche en histogramme et 4 sur l’axe droit, en courbe. Vous créez au départ un histogramme avec le 8 séries, et vous devez alors réaliser 8 opérations supplémentaires : pour chacune des séries à associer à l’axe droit, modifier le type de la série, puis l’axe.

Remarque 2 – Et vous avez perdu plus encore, par rapport aux toutes premières versions d’Excel. En effet, d’une part on y disposait d’un graphe standard à deux axes Y et, d’autre part, il y avait une case dans le dialogue de la création de ce graphe où l’on entrait le nombre de séries liées à l’axe gauche ! Ce qui évitait de devoir modifier l’axe et le type de toutes les séries mal placées.

Heureusement pour nous, Excel 2016 corrige tout cela avec le graphique combiné dont nous parlerons dans le prochain article.

Bienvenue dans Excel 2016 (e)

Suite à l’article précédent, nous avons accepté la seconde proposition d’Excel en graphique recommandé, le graphe en cascade. Nous l’appelerons « waterfall », vu que c’est sous ce nom qu’il est connu dans le monde entier. 

C’est un graphique très parlant : les recettes apparaissent en bleu et les dépenses en orange. En reportant le haut de chaque barre bleue, ou le bas de chaque barre orange sur l’axe des Y, on voit combien il nous reste d’argent…


Quand Excel est passé de la version 2003 à la version 2007, le monde des graphes d’Excel a subi de très nombreux changements. C’est ainsi que nous avons perdu trois possibilités originales d’Excel :

  • Jusqu’à la version 2003 incluse, on pouvait sélectionner un point d’une série, puis le monter ou le baisser à volonté. Ce n’est plus possible aujourd’hui sauf à utiliser un add-in à cet effet…
  • Jusqu’à la version 2003 incluse, on pouvait sélectionner un bloc de cellules puis le tirer dans un graphe, ce qui ajoutait automatiquement la série au graphe. Aujourd’hui, ce n’est plus possible, il faut sélectionner le bloc, le copier, sélectionner le graphe, puis coller… C’est tellement plus long !
  • Jusqu’à la version 2003 incluse, Excel proposait en standard des graphes avec deux axes Y. Ce n’est hélas plus le cas : (

Remarque 1 – C’est bien là une des pertes les plus regrettables ! Imaginons en effet que vous ayez 8 séries  dont vous souhaitez mettre 4 sur l’axe gauche en histogramme et 4 sur l’axe droit, en courbe. Vous créez au départ un histogramme avec le 8 séries, et vous devez alors réaliser 8 opérations supplémentaires : pour chacune des séries à associer à l’axe droit, modifier le type de la série, puis l’axe.

Remarque 2 – Et vous avez perdu plus encore, par rapport aux toutes premières versions d’Excel. En effet, d’une part on y disposait d’un graphe standard à deux axes Y et, d’autre part, il y avait une case dans le dialogue de la création de ce graphe où l’on entrait le nombre de séries liées à l’axe gauche ! Ce qui évitait de devoir modifier l’axe et le type de toutes les séries mal placées.

Bienvenue dans Excel 2016 (e)

Suite à l’article précédent, nous avons accepté la seconde proposition d’Excel en graphique recommandé, le graphe en cascade. Nous l’appelerons « waterfall », vu que c’est sous ce nom qu’il est connu dans le monde entier. 

C’est un graphique très parlant : les recettes apparaissent en bleu et les dépenses en orange. En reportant le haut de chaque barre bleue, ou le bas de chaque barre orange sur l’axe des Y, on voit combien il nous reste d’argent…


Quand Excel est passé de la version 2003 à la version 2007, le monde des graphes d’Excel a subi de très nombreux changements. C’est ainsi que nous avons perdu trois possibilités originales d’Excel :

  • Jusqu’à la version 2003 incluse, on pouvait sélectionner un point d’une série, puis le monter ou le baisser à volonté. Ce n’est plus possible aujourd’hui sauf à utiliser un add-in à cet effet…
  • Jusqu’à la version 2003 incluse, on pouvait sélectionner un bloc de cellules puis le tirer dans un graphe, ce qui ajoutait automatiquement la série au graphe. Aujourd’hui, ce n’est plus possible, il faut sélectionner le bloc, le copier, sélectionner le graphe, puis coller… C’est tellement plus long !
  • Jusqu’à la version 2003 incluse, Excel proposait en standard des graphes avec deux axes Y. Ce n’est hélas plus le cas : (

Remarque 1 – C’est bien là une des pertes les plus regrettables ! Imaginons en effet que vous ayez 8 séries  dont vous souhaitez mettre 4 sur l’axe gauche en histogramme et 4 sur l’axe droit, en courbe. Vous créez au départ un histogramme avec le 8 séries, et vous devez alors réaliser 8 opérations supplémentaires : pour chacune des séries à associer à l’axe droit, modifier le type de la série, puis l’axe.


Remarque 2 – Et vous avez perdu plus encore, par rapport aux toutes premières versions d’Excel. En effet, d’une part on y disposait d’un graphe standard à deux axes Y et, d’autre part, il y avait une case dans le dialogue de la création de ce graphe où l’on entrait le nombre de séries liées à l’axe gauche ! Ce qui évitait de devoir modifier l’axe et le type de toutes les séries mal placées.

17 août 2016

Bienvenue dans Excel 2016 (d)

Après avoir présenté les onglets Remplissage (articles a et b) et Analyse (article c), nous nous attaquons aujourd’hui à l’onglet Graphique.

Comme le conseille le texte de cet oonglet, c’est maintenant le moment de sélectionner (cf. la copie d’écran) le tableau entier puis d’utiliser la commande InsertionGraphiques recommandés. Nous voyons alors une première proposition, « Barres groupées », qui n’est d’aucun intérêt pour nous mais aussi, en seconde position, le Graphique en cascade, qui est particulièrement bien adapté à ce type de données.


Ceux d’entre vous qui ont l’habitude de la modélisation financière n’ont probablement jamais entendu parler de « graphiques en cascade », mais ils ont eu très probablement les oreilles rebattues avec les « waterfall charts » ou les « bridges ». 

C’est en effet sous ces deux noms que ces graphes sont connus en finance. Ils sont très pratiques pour analyser la façon dont une séquence de dépenses et de recettes fait évoluer un capital.

Remarque – Excel aurait été plus astucieux encore en présentant ce type de graphe comme étant le plus recommandé pour les données de notre exemple, mais c’est déjà pas mal que ce graphe recommandé soit sa seconde suggestion.