Monsieur Excel
Pour tout savoir faire sur Excel !

27 août 2015

Lancement de dés (c)

Enfin des dés qui fonctionnent proprement !

Dans le dernier article, nous avons constaté avec surprise – et tristesse ! – que le total des tirages ne faisait pas 100% et qu’il y avait parfois 9 dés  dans les résultats et parfois 10…

Pour comprendre cela, il faut comprendre un autre élément très important dans le fonctionnement d’Excel. Nous avons déjà constaté, dans le dernier article, qu’Excel n’effectue que les calculs qui lui semblent nécessaires : c’est pour cela que l’on avait dix fois le même dé dans le modèle du premier article.

L’autre élément très important, c’est qu’Excel calcule selon un ordre précis : du haut vers le bas puis, dans chaque ligne, de la gauche vers la droite.

Ainsi, quand Excel calcule la ligne 1 pour l’itération n°10, les lignes suivantes en sont encore à l’itération n°9, et le compteur en B3 est encore à 9. Les pourcentages en F1 et F2 sont donc divisés par 9, ce qui explique le 11% que nous avions obtenu en F2.

Quand la ligne 3 est calculée, le compteur passe à 10 et les pourcentages de F3 à F6 sont donc justes !

Quand nous avons tiré le 3 en B4 lors de l’itération n°10, ce dé a été ignoré du compte car c’est en ligne 3 (donc une ligne plus haut) que l’on vérifiait si le 3 avait été tiré mais – au moment de ce calcul – c’est le neuvième dé qui était encore affiché en B4 !

La solution est d’une simplicité biblique : il suffit, pour résoudre notre problème, de déplacer le bloc D1:F7 de 3 lignes vers le bas.


Comme vous le voyez ci-dessus, les pourcentages sont à présent tous bons et nous avons bien 10 dés répertoriés. Tous les problèmes ont disparu !

22 août 2015

Lancement de dés (b)

Avec le modèle précédent, on obtient hélas 10 fois le même dé !

Si nous mettons le drapeau à 0 puis lançons le calcul avec [F9], nous rétablissons les valeurs initiales. Si nous remettons le drapeau à 1 et lançons la simulation avec [F9], nous obtenons encore dix fois le même dé, avec 1 chance sur 6 bien entendu que ce soit le même que dans la simulation précédente.

Nous jouons donc aux dés avec un dé pipé, puisque c’est toujours la même face qui sort dix fois de suite.

Pour comprendre et donc pouvoir résoudre le problème, il faut savoir comment Excel réalise ses calculs.

Excel opère en effet de manière très intelligente, en essayant – pour calculer vite – de ne faire que les calculs qu’il estime indispensables.

La formule de calcul du dé, en B4 est =si(drapeau=0;"";ent(6*alea()+1)).

Quand Excel calcule la seconde itération, il constate que le si() dépend du drapeau et raisonne de la façon suivante : « Le drapeau n’a pas changé depuis l’itération précédente, je n’ai donc pas besoin de recalculer le résultat ! ». Et il fait de même pour les 8 itérations restantes.

La solution est toute simple ! Pour forcer Excel à recalculer le dé, il suffit donc de faire dépendre le si() d’une cellule qui change à chaque itération…

La bonne formule de B4 est donc : =si(B3=0;"";ent(6*alea()+1)).
Voici le résultat que nous obtenons après avoir effectué cette modification.


C’est bien meilleur, n’est-ce pas !

Et pourtant, il reste encore deux problèmes. Le premier est que nous avons tantôt 9, tantôt 10 itérations au compteur. Le second est que la somme des pourcentages ne fait que très rarement 100% !

Nous verrons dans le prochain article la raison de ces problèmes et comment les résoudre. 

17 août 2015

Lancement de dés (a)

Pour continuer dans la série des simulations probabilistes, nous nous attaquons aujourd’hui à la simulation du lancement de dés : nous souhaitons mettre en place un modèle qui nous permette de lancer un dé dix fois de suite et de voir quelle est la distribution des résultats, avec le pourcentage de 1, de 2, … et ainsi jusqu’à 6.

Nous appliquons la méthode décrite dans l’article du 1er août dernier. Un drapeau est défini à 1 quand nous voulons activer le calcul itératif, et à 0 quand nous voulons réinitialiser les cellules en vue d’une nouvelle simulation.

Voici donc les formules des différentes cellules, à part les sommes en ligne 7 :

B3 : =si(drapeau=0;0;B3+1)
B4 : =si(drapeau=0;"";ent(6*alea()+1))
E1 : =si(drapeau=0;0;E1+si($B$4=D1;1;0))
F1 : =E1/$B$3

Les réglages du mode de calcul sont comme on le voit dans la copie d’écran encadrée de bleu.


Remarque – Notons la formule ent(6*alea()+1) pour le calcul du dé, ce qui est plus direct – mais un peu moins facile à trouver – que alea.entre.bornes(1;6).

Si vous mettez le drapeau à 1 et lancez la simulation avec [F9], vous allez obtenir un résultat surprenant. Essayez de comprendre pourquoi et, si possible, trouvez la parade.

Je vous expliquerai tout cela dans le prochain article…

12 août 2015

Test de "PDF Converter Elite 4"

Cette semaine, j’ai eu l’occasion de tester pour vous PDF Converter Elite 4.0, un logiciel de conversion depuis des documents Acrobat (*.PDF) vers différents logiciels tels que Word, Excel, Powerpoint ou d’autres encore. Bien entendu, nous nous intéresserons ici exclusivement à la conversion vers Excel.

Nous voyons ci-dessous le résutat obtenu en convertissant le haut de la partie « Passif » d’un bilan avec, à droite, une copie d’écran du logiciel.

Comme vous pouvez le voir dans cette copie d’écran, on peut au choix convertir la partie sélectionnée (ce que nous avons fait ici), la totalité du document, ou enfin un ensemble de pages choisies.

Remarque – Un seul petit problème de fonctionnement : quand la dernière option est sélectionnée et que l’on a saisi les pages à imprimer, le bouton « Convert » reste inactif, il ne s’éclaire pas en jaune. Il faut savoir qu’il faut alors appuyer sur la touche « Entrée » pour qu’il devienne actif .

  
Comme on le voit dans l’image ci-dessus, le résultat dans Excel est bon puisque les textes et les montants sont bien tous récupérés.

Il reste cependant la mise en page à refaire. Ainsi, les deux colonnes à droite ne sont pas bien alignées car, dans la ligne du capital social, il y a un montant présent dans une colonne où il n’y a rien pour les autres lignes.

En conclusion, ce produit est extrêmement utile mais il vous faut quand même exécuter un travail de finition et tout le travail de formatage et d’encadrement nécessaire pour obtenir un résultat très proche du document original, en fond comme en forme.

Plus, bien logiquement, toutes les formules qui sont à recréer si vous voulez récupérer un docupent Excel réellement opérationnel…

Ceci dit, en guise de conclusion, ce produit remplit sa mission et nous semble bien utile !

07 août 2015

Suivi de conso carburant

Le modèle que je vous présente aujourd’hui est lié à une demande qui m’a été faite de la part de la FAO à Madagascar. Vous avez en colonne A l’identification d’un camion, en colonne B le kilométrage qu’il affichait quand il a fait le plein, et en colonne C le nombre de litres pris à cette occasion.

La particularité de ce modèle est que le nombre de litres n’est pas toujours retranscrit… L’indolence des îles ?

Dans le tableau en E2:H10, on souhaite récupérer, pour chaque camion, la dernière quantité retranscrite pour son plein, l’avant-dernière (s’il y en a une), et enfin le nombre de kms parcourus entre les deux derniers pleins avec le litrage renseigné.


Pour trouver la formule à utiliser en F3, vous devez déjà être un expert Excel !
Pour trouver les formule à utiliser en G3 et H3, vous devez déjà être un grand expert Excel !

Je vous laisse chercher un peu. Ne lisez la suite que si vous ne trouvez pas la solution…
Formule de F3, reproduite ensuite vers le bas :
=index(C:C;max(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0)))

Formule de G3, reproduite ensuite vers le bas :
=index(C:C;grande.valeur(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0);2))

Formule de H3, reproduite ensuite vers le bas :
=si(estnum(G3);index(B:B;max(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0)))
-index(B:B;grande.valeur(ligne($A$2:$A$30)*($A$2:$A$30=E3)*($C$2:$C$30>0);2));"")

Les trois formules ci-dessus sont matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée].

En fait, en colonne G, le résultat est un texte quand on ne trouve pas au moins deux litrages dans la colonne C. Nous avons donc utilisé le format personnalisé « 0;;0; » pour masquer ces textes. Et c’est ce qui explique le estnum(G3) dans la formule de H3.

Remarque 1 – Cela faisait quelque temps que je vous avais pas prouvé que, avec des formules matricielles bien conçues, on pouvait faire pratiquement n’importe quoi dans Excel !

Remarque 2 – Notons dans la formule de G3 la fonction grande.valeur() qui est très utile et pourtant assez peu connue.

En E12, nous avons mis une validation de cellule par liste avec la référence =$E$3:$E$10.

Enfin, dans le bloc de gauche, nous avons mis un format conditionnel dont la définition est reproduite dans le cadre bleu. Cela met en relief les lignes relatives au camion sélectionné.

03 août 2015

Thalys rançonne ses clients...

Chères lectrices, Chers lecteurs,

Pour une fois, je ne parlerai pas d'Excel mais de ce qui m'est arrivé quand j'ai été faire une conférence sur Excel en Belgique. Ceci dit, c'est une lecture que je vous conseille quand même, ne serait-ce que pour son aspect distractif !

Le 2 juillet dernier, j’ai passé la journée à Bruxelles où je donnais une conférence.

Mon train de retour était prévu à 18H13. Je suis arrivé à la gare juste à temps pour pouvoir prendre le Bruxelles-Paris précédent, ce qui me permettait de gagner une grosse demi-heure.

J’ai demandé à l’employée Thalys à l’entrée du wagon si, avec mon ticket, je pouvais prendre ce train. Elle m’a assuré qu’il n’y avait aucun problème et m’a fait monter.

Pendant le voyage, deux contrôleurs sont passés et la contrôleuse m’a dit que mon billet n’était pas valable et que je devais payer 112 € en plus (soit un supplément de 118% !). Je lui ai expliqué la situation en lui disant que, si l’employée à l’entrée du wagon m’avait dit qu’il faudrait payer cela en plus, j’aurais bien évidemment attendu une demi-heure à Bruxelles le départ du train prévu sur mon ticket.

La contrôleuse, pour se justifier, m’a dit que le personnel à l’entrée du wagon n’était pas tenu à m’informer du problème, que son seul rôle était de vérifier que chaque client avait un ticket. Dans la mesure où elle portait un uniforme Thalys, je pense pour ma part que l’hôtesse devrait forcément avoir le devoir d’avertir les clients de tels problèmes.

La contrôleuse n’a absolument rien voulu savoir et m’a fait payer 112 € de plus. Du coup, mon trajet Bruxelles-Paris m’est revenu plus cher qu’un aller-retour en avion Paris-Varsovie ou Paris-Budapest !

La contrôleuse, de petite taille et bien nourrie, me dominait (elle était debout, moi assis) et affichait un sourire sardonique. Il ne lui manquait plus que la guêpière en cuir noir et la cravache pour compléter le tableau…

Le contrôleur qui l’accompagnait, plus jeune, m’a bien fait comprendre – avec un mime que n’aurait pas renié Charlie Chaplin – qu’il était vraiment navré de la situation mais qu’il ne pouvait que se taire face à l’Obersturmführer, sa chef.

Quand j’ai dit à la contrôleuse que je souhaitais réclamer, elle m’a menacé en répondant que, dans ce cas, cela pouvait me coûter bien plus cher encore. Ce comportement de la contrôleuse est proprement inadmissible. Par crainte de pire encore, j’ai donc dû me résoudre à payer, bien malgré moi. Est-ce une façon de traiter un client fidèle qui, en 15 jours, avait fait trois fois l’aller-retour Paris-Bruxelles en déboursant pour cela environ 600 € ?

De retour à Paris, j’ai envoyé une lettre de réclamation à Thalys, en expliquant la situation (sans les détails sado-maso). Réponse de M. Dothoy, du Service (faudrait-il dire « Sévice » ?) Clientèle de Thalys, qui soutient totalement sa contrôleuse, refuse donc de me rembourser, et commente « Je tiens à vous préciser que le personnel d'accueil n'a pas pour rôle de vérifier la validité des billets, ceci se faisant à bord, mais bien d'accueillir et orienter vers les bonnes voitures ».

Message au personnel d’accueil de Thalys

Chers amis, votre employeur souhaite que vous sachiez seulement lire un billet, mais pas le comprendre, ni être capable d’assister efficacement un client qui vous pose une question précise.

Je suppose que cela lui permet de vous rémunérer pas trop cher, vu que vous n’êtes en quelque sorte – et apparemment selon sa volonté – que des potiches. Je vous conseille de lui demander une formation permettant l’enrichissement de votre tâche et un accueil efficace de vos clients.

L’autre intérêt de Thalys est que votre incompétence lui permet d’engranger de belles recettes supplémentaires en piégeant de pauvres pigeons de clients comme moi.

Thalys est en fait un acronyme

La société étant un consortium international, j’ai réalisé que son nom était en fait un acronyme. Cet acronyme est bilingue, ce qui est normal pour un opérateur international. Le voici…

Tarifs
   Honteusement
      Abusifs 
         Lets
            You
               Scream

Un nouveau logo pour Thalys

Il m’est venu à l’idée de proposer un nouveau logo pour Thalys, un logo porte-bonheur, un talisman en quelque sorte. Le voici :


On peut bien parler de mensonge puisque l’hôtesse d’accueil m’a assuré qu’avec mon ticket je pouvais monter sans problème dans le wagon et que, plus tard, la contrôleuse m’a fait payer une amende de 118% de la valeur de mon billet.

Un billet « semi-flex »

Le billet en ma possession était un billet « semi-flex », dont je suppose que cela signifiait qu’il était à moitié flexible. S’il n’avait pas été flexible du tout, mon amende aurait-elle alors été de 200% de la valeur du billet, ou même de 300% ?

Je suppose que la flexibilité en question est celle que Thalys s’octroie pour arnaquer ses clients.

En guise de conclusion

N’hésitez pas à partager cet article, que je vous permets de reproduire en toute liberté, à tous vos contacts afin de les avertir des risques qu’ils prennent à voyager avec Thalys. Voilà une société qui fait une bonne affaire : pour les 112 € quelle m’a volés, elle bénéficie d’une publicité de 829 mots. Moins de 15 centimes par mot, quelle véritable aubaine !

01 août 2015

Le nombre d’itérations requis

Pour compléter les messages du 21 et du 26 juillet, nous allons voir aujourd’hui comment calculer le nombre d’itérations requis dans notre exemple de calcul de marge pour obtenir le résultat final au centime près. Pour cela, nous devons utiliser les réglages suivants :

Notez que nous avons demandé un calcul sur ordre avec 100 itérations au maximum (ce sera plus que nécessaire pour obtenir la précision demandée) et un écart de 0,01 qui correspond au centime requis comme précision.

Il y a deux problèmes à résoudre, qui seront présents chaque fois que l’on voudra ainsi effectuer du calcul itératif :
  • Comment faire pour réinitialiser les valeurs après avoir terminé un calcul de marge ?
  • Comment obtenir une précision de 0,01 si le compteur d’itérations est incrémenté de 1 à chaque itération ?
Réinitialisation de la simulation

Pour réinitialiser les valeurs après un calcul itératif, la solution la plus efficace est de créer un drapeau que l’on met à 0 pour réinitialiser puis à 1 quand on souhaite que le calcul itératif s’effectue :


Gestion du compteur d’itérations

Pour que le compteur d’itérations ne casse pas la convergence en ajoutant 1 à chaque itération – auquel cas l’écart maximal de 0,01 ne serait jamais obtenu – l’astuce consiste simplement à compter les itérations en millièmes, donc en ajoutant à chaque itération un incrément de 0,001, ce qui est inférieur au seuil limite de 0,01 !

Convergence vers la solution finale

Il ne nous reste plus qu’à saisir 1 dans le drapeau en E2 et à lancer le calcul par [F9]. Le résultat apparaît instantanément, avec 142,85 € en B4 et 0,008 en B6. Il a donc suffi de huit itérations pour converger vers la solution au centime près !