Monsieur Excel
Pour tout savoir faire sur Excel !

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 !

26 juillet 2015

Paramétrer une référence circulaire

Cet article fait la suite de notre présentation des références circulaires dans le dernier article.

Quand on active, en cochant « Activer le calcul itératif » dans la commande Fichier – Options – Formules, les références circulaires, on a accès à deux réglages, le nombre maximal d’itérations et l’écart maximal.

Le nombre maximal d’itérations est le nombre d’itérations qui pourront être effectuées lors d’un calcul de la feuille. Fixons-le à « 1 » pour l’instant. Cette commande n’a donc de sens que si l’on a sélectionné l'option « Activer le calcul itératif » comme dans la copie d’écran ci-dessous :


Remarque – Ce serait bien plus clair si les commandes « Nb maximal d’itérations » et « Ecart maximal » étaient grisées tant que l’on n’a pas coché la case « Activer le calcul itératif ».

Effaçons à présent la formule de la cellule B4, lançons un calcul avec [F9] pour que la cellule B3 retrouve sa valeur de 0, puis ré-entrons la formule de sommation en B4.

Le prix de vente est maintenant de 100 € puisque seule la cellule B4 a pu être calculée, au moment où sa formule a été validée :


Lançons un calcul avec [F9] et nous constatons que B3 prend à présent une valeur de 30,00 € et B4 une valeur de 130,00 €.

Lançons un nouveau calcul avec [F9], et nous obtenons 39,00 € en B3 et 139,00 € en B4.

Lançons encore un calcul avec [F9], et nous obtenons 41,70 € en B3 et 141,70 € en B4.

Avec cinq calculs de plus, le résultat final avec 42,86 € en B3 et 142,86 € en B4 est enfin obtenu...

En fait, Excel arrête le calcul dès que soit le nombre maximal d’itérations a été atteint, soit l’écart maximal entre le nème calcul et le (n+1)ème calcul – pour les valeurs de toutes les cellules du classeur – est inférieur à l’écart maximal défini.

21 juillet 2015

Création d’une référence circulaire

Réalisons un petit modèle dans lequel nous entrons un prix d’achat en B2, nous déclarons en B3 que nous allons faire une marge de 30% du prix de vente et, en B4, nous demandons la somme grâce à l’outil de sommation.

Et patatras ! Excel nous annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :


Il s’agit d’une sécurité normale car, le plus souvent, les références circulaires sont tout à fait involontaires, il s’agit alors d’erreurs de conception qu’Excel signale avec raison.

Il se peut cependant que – c’est d’ailleurs le cas ici – qu’il ne s’agisse pas d’une erreur mais que nous souhaitions effectivement calculer un résultat par convergence, à travers des itérations successives. C’est par exemple indispensable si vous construisez un modèle de calcul d’ISF (Impôt Sur la Fortune) : en effet, dans ce cas, on peut déduire, pour calculer la base imposable, le montant de l’ISF à payer qui dépend de cette même base imposable ! Encore une invention d’énarque...

Pour valider le calcul en références circulaires, il vous suffit d’appeler la commande Fichier – Options – Formules, et de cocher l'option « Activer le calcul itératif », comme nous le voyons ci-dessous.


Le résultat final de 142,86 € apparaît alors aussitôt dans la cellule B4 !

Et voilà, ce n’est pas plus compliqué que cela...

Nous verrons bientôt comment tirer profit des deux réglages suivants, le Nb  maximal d’itérations et l’Ecart maximal.

16 juillet 2015

Je veux réussir mon examen (d)

Dans les trois articles précédents, nous avons découvert trois façons totalement différentes de résoudre le problème d’examen présenté dans l’article du 30 juin : par la statistique, par l’analyse combinatoire, et enfin par simulation.

On peut se poser la question de savoir si l’une de ces trois approches est préférable.

Pour le temps de développement, quand on maîtrise bien ces trois approches – ainsi bien entendu qu’Excel lui-même – il n’y a pas une grande différence. Dans chacun des trois cas, il m’a fallu moins de dix minutes pour développer le modèle.

En terme de précision, les deux premières approches donnent un résultat parfaitement exact. Dans le troisième cas, avec 1.000 itérations, la probabilité de succès avec 6 chapitres révisés oscille le plus souvent entre 90% et 94,5%, mais il m’est arrivé de tomber entre 89% et 90%. Ceci dit, il suffirait de passer à 10.000 lignes pour réduire sensiblement les variations.

En terme de flexibilité, la simulation est de loin la meilleure solution.

Supposons que l’on introduise une nouvelle contrainte, par exemple que l’on ne puisse comprendre le chapitre 8 que si l’on a préalablement révisé le chapitre 3. Avec les deux premières approches, il serait très compliqué (ou impossible) d’intégrer une telle contrainte. Dans le cas de la simulation, la prise en compte d’une contrainte de ce genre est non seulement possible, mais aussi nettement plus simple.

Bon, j’en entends qui ricanent au fond de la salle en se demandant si cela est aussi simple que je le dis. Voilà donc une excellente occasion pour vous de voir si vous y parvenez. Ceux d’entre vous qui y parviendront sont clairement dans le top 5% – et même probablement le top 1% – des utilisateurs d’Excel. Ne lisez donc la suite de cet article qu’après avoir essayé de résoudre ce problème…

Pas de révision du chapitre 8 sans le chapitre 3…

Voici la solution que je vous propose. Le plus simple est de modifier l’ordre des chapitres en mettant les chapitres 3 et 8 en haut de la liste.

J’ai ensuite modifié la formule de D2 :
=si(et(E1=1;C2>grande.valeur($C$1:$C$13;7));1;"")

Puis ajouté une formule en F2 :
=si(et(E1=0;C2>grande.valeur($C$1:$C$13;7));1;0)

Et enfin modifié la formule de D3 (et suivantes) :
=si(C3>grande.valeur($C$1:$C$13;7+$F$2);1;"")

 Et voici le résultat, dans lequel on constate qu’il faut à présent réviser 7 chapitres au lieu de 6 pour avoir au moins 90% de chances de réussite :


Remarque – On constate que le chapitre 8, qui devrait être tiré vu son grand aléa en C2, b'a pas été pris car le chapitre 3 n'était pas dans les élus...

Remarque pour les pros – Si j’ai placé les chapitres 3 et 8 en haut de la liste, c’est pour tenir compte du fait qu’Excel calcule ligne par ligne…

11 juillet 2015

Je veux réussir mon examen (c)

Dans les deux articles précédents, nous avons résolu le problème de l’examen que devait passer Eléonore, la première fois avec une solution purement statistique, la seconde fois en utilisant l’analyse combinatoire.

Solution par simulation

Aujourd’hui, nous utilisons une troisième solution, totalement différente, la simulation.

Dans le bloc A1:D13, nous identifions dans la colonne B les 6 chapitres (parmi les 13 chapitres du programme) qui ont été sélectionnés pour l’examen. Pour cela, nous avons utilisé les formules suivantes, reproduites vers le bas :

B1 : =alea()
C1 : =si(B1>grande.valeur($B$1:$B$13;7);1;"")
D1 : =somme($C$1:C1)

Les chapitres gagnants sont les 6 chapitres avec les plus grandes valeurs aléatoires.

En colonne D, dans la ligne n, nous avons le nombre de bonnes réponses quand on a révisé n chapitres.

En F2, recopiée vers la droite, la formule =INDEX($D$1:$D$13;COLONNE()-4).
Ce qui revient tout simplement à transposer les résultats de la colonne D.

Nous avons ensuite sélectionné le bloc E2:L1001 puis créé une table en mettant le paramètre de colonne en E1.


Il s’agit là d’un détournement très astucieux du principe des tables. On met le paramètre de table (un vecteur vertical vide) dans une cellule qui ne sert à rien, mais cela force Excel à calculer 1.000 fois les résultats de la colonne D. On amène ainsi Excel, sans la moindre ligne de VBA, à effectuer une boucle de calcul.

A chaque appui sur la touche [F9], nous lançons ainsi 1.000 itérations, et nous constatons – comme avec les deux méthodes précédentes – qu’il faut réviser 6 chapitres pour avoir au moins 90% de chances de réussir l’examen.

05 juillet 2015

Je veux réussir mon examen (b)

Dans l’article précédent, nous avons posé le problème de l’examen que devait passer Eléonore, et nous avons résolu ce problème en utilisant la loi hypergéométrique.

Aujourd’hui, nous voyons comment ce problème peut être résolu d’une autre façon, par analyse combinatoire.

Solution par analyse combinatoire

L’analyse combinatoire consiste à effectuer le décompte des cas possibles, puis celui des cas favorables. La probabilité de réussite est alors le rapport de  ces deux résultats.

Nous avons 13 chapitres, dont 6 sont tirés pour l’examen. Le nombre de combinaisons de « k parmi n » est :
n! / k!(n-k)!, soit dans notre cas 13! / (6! * 7!).

Il y a donc 1716 combinaisons différentes possibles à l’examen.

Nous pourrions entrer à cet effet la formule =fact(13)/fact(6)/fact(7), mais le résultat s’obtient plus simplement encore avec la fonction créée précisément dans Excel à cet effet : =combin(13;6).

Pour trouver le nombre de combinaisons avec au moins 2 chapitres révisés, nous allons passer par le complémentaire : toutes les combinaisons, moins celles avec 0 ou 1 chapitre révisé.

Appelons r le nombre de chapitres révisés.

Nombre de tirages avec aucun chapitre révisé : c’est le nombre de tirages de 6 chapitres parmi les 13-r non révisés : (13-r)! / 6!(13-r-6)! C’est la formule =COMBIN(13-C8;6) en B9.

Pour un chapitre donné révisé : nombre de tirages de 5 chapitres parmi les 13-r non révisés : (13-r)! / 5!(13-r-5)!

Ceci est valable pour les r chapitres révisés, donc les combinaisons se s’élèvent à : r * (13-r)! / 5!(13-r-5)!

Cela nous donne la formule =C8*COMBIN(13-C8;5) en B10 dans Excel.

On crée ensuite une table en E1:G10 avec la formule =1-loi.hypergeometrique.n(C1;C2;C3;C4;C5) en F1 et la formule =C11 en G1, en mettant l’argument en colonne en C2.


Quand on fait cela, on obtient des erreurs en G9 et G10, ce qui est normal. Pour les éliminer, il faut remplacer les formules de C9 et C10 par =sierreur(formule;0)

Ce qui est intéressant avec ce second article sur l’examen, c’est que nous trouvons exactement les mêmes résultats avec une approche totalement différente.