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ées – Analyse 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
Aucun commentaire:
Enregistrer un commentaire