Monsieur Excel
Pour tout savoir faire sur Excel !

26 mai 2018

Un bug de table (a)

Un lecteur m’a fait remarquer que la table qu’il avait réalisée à partir de l’article Cas n°1 – Formules de base du 28 août 2017 ne lui donnait pas le résultat escompté. En creusant le problème, j’ai découvert qu’il y avait en effet un effet pervers possible dans la création des tables.

Pour le mettre en relief de la façon la plus claire possible, j’ai construit une table qui ne sert à rien d’autre qu’à mettre en relief cet effet pervers. Nous avons en ligne 8 une série de valeurs partant de B3, en ôtant B4 à chaque nouvelle colonne. En ligne 9, nous calculons la racine carrée de ces valeurs.

Nous construisons, comme nous le voyons dans la copie d’écran, une table en K1:L6 pour afficher les racines carrées des valeurs en colonne K. Pour faciliter la comparaison entre les valeurs obtenues dans la table par rapport aux valeurs attendues, nous avons calculé directement en colonne N les racines carrées des valeurs en colonne K.


Erreurs dans la table

Nous constatons que le résultat en L2 et L3 sont bons. Le résultat en L4 est décalé d’une valeur, celui en L5 de deux valeurs et celui en L6 de trois valeurs !

Si nous entrons 130 en K6, la valeur de la table en L6 n’est pas la même que pour l’autre 130 en L4 !

rédigé par Hervé Thiriez | 0 commentaire(s)

20 mai 2018

Légumes et fruits (b)

Dans le dernier article, nous avions terminé en laissant un problème irrésolu. Comment faire pour que le modèle que nous avons construit continue à fonctionner de façon parfaite même après l’insertion d’une ligne à l’intérieur du bloc ?

La solution consiste à remplacer la formule de la cellule E2 par =somme(decaler(E2;-1;0);D2).

La fonction decaler() est très utile pour résoudre des problèmes tels que celui-là et rendre les modèles bien plus stables.

Transformation en table

Une autre solution consiste à transformer le bloc A1:B6 en table ; à condition bien entendu d’avoir au préalable donné un titre, par exemple Produit à la cellule A1. En effet, dans une table, chaque colonne doit posséder un titre.

Une fois la table construire, redéfinissons la colonne E en entrant une nouvelle formule en E2. Mettons-nous en E2. Nous souhaitons par exemple y entrer une formule effectuant la somme de D$2 à D2. Le problème est que, dès que l’on clique en D2 pour créer cette formule, c’est [@Total] qui apparaît. Il s’agit de la référence structurée propre aux tables introduites avec Excel 2007 !

Comment faire pour obtenir l’équivalent de D$2 ? La solution n’est en fait pas si compliquée que cela : il suffit d’utiliser à cet effet la fonction index(). La bonne formule pour E2 est donc :
=somme(index([Total];1):[@Total])

Dès que nous avons entré cette formule en E2, elle est automatiquement recopiée dans toute la colonne. C’est là une des propriétés les plus intéressantes des tables.


Nous constatons que, grâce à la table, nous résolvons les deux problèmes que nous avions soulevés dans l’article précédent : d’une part, tout fonctionne bien après l’insertion d’une ligne à l’intérieur ; d’autre part, dès que l’on entre quoi que ce soit en A8, la table est étendue d’une ligne de façon automatique, les deux formules étant créées aussitôt par Excel.

Il ne reste qu’un seul petit problème, c’est que cette solution peut avoir un impact sur le temps de calcul si la table comporte un grand nombre de lignes. En effet, la formule de la colonne E provoque pour chaque cellule une sommation depuis la première ligne de données, ce qui est plus lourd, en temps de calcul, que la solution proposée plus haut avec la fonction decaler() !

rédigé par Hervé Thiriez | 0 commentaire(s)

14 mai 2018

Légumes et fruits (a)

Nous allons utiliser un « panier de la ménagère » comportant des légumes et des fruits comme outil de référence pour introduire plusieurs concepts de « bonne modélisation ».

Nous souhaitons construire un modèle « propre ». Pour cela, l’idéal est que la première ligne de données, la ligne 2, comporte des formules originales que l’on pourra simplement copier vers le bas.

Un premier problème apparaît avec la formule de E2. On ne peut pas écrire =E1+D2 car cela nous donnerait une erreur. La solution consiste à utiliser la formule =somme(E1;D2) qui ne pose aucun problème car la somme compte les textes pour une valeur de 0. Les formules de D2 et E2 peuvent alors être copiées vers le bas sans problème.


Entrons à présent les valeurs de A7 et B7 comme dans la copie d’écran ci-dessous. Nous constatons que, dès la saisie en B7, la formule de C7 apparaît automatiquement. Mais pas celle de D7 ! C’est parce que sa formule ne fait pas référence qu’à des éléments antérieurs dans la même ligne. Il nous faut donc recopier E6 vers le bas pour compléter la ligne…

Quels sont maintenant les deux objectifs à suivre pour une bonne modélisation ? S’assurer d’une part que l’adjonction d’une nouvelle ligne ne pose pas de problème, ce que nous venons de vérifier en ajoutant cette ligne 7. S’assurer d’autre part que le modèle continue à fonctionner si l’on insère une ligne à l’intérieur du tableau actuel.

Et c’est là que le bât blesse avec notre modèle actuel ! La formule de E6, la nouvelle ligne du Concombre, deviendra =SOMME(E4;D6) et le résultat ne sera pas le bon…



rédigé par Hervé Thiriez | 2 commentaire(s)

08 mai 2018

Simulation de gare de péage

Dans l’article du 20 avril, je présentais quatre des applications professionnelles dans lesquelles j’avais mis au point pour mes clients du cinéma dans Excel. L’une d’entre elles simulait le fonctionnement d’une gare de péage autoroutier, modèle qui a été acheté par plusieurs sociétés de péage.

Le client identifiait dans le modèle quelles portes de péage (40 au maximum) il ouvrait, et pour quels types de véhicules et quels modes de paiement. Il indiquait aussi, minute par minute, quel était le trafic (nombre de véhicules) il attendait et quelle répartition entre ces types (auto, moto, camion,…) ce jour-là et à ces heures-là.


Le modèle simulait alors le trafic avec entre autres le graphe précédent qui permettait de suivre l’évolution du trafic sur les portes de péage ouvertes ce jour-là. Dans l’exemple ci-dessus, on simulait le trafic de 8H du matin à 10H du matin, avec 8 portes de péage ouvertes. Pour chaque porte, le graphe en bleu représentait le nombre de véhicules en attente (nous avons choisi un jour de grand embouteillage : trop peu de portes ouvertes) et le graphe en rouge (en fait les valeurs sont positives, mais c’était plus pratique de les montrer en négatif) le nombre de véhicules par minute dans la porte de péage concernée.

Le bouton en haut et à droite permettait de lancer un film en continu illustrant l’évolution du système de 8H à 10H. Le curseur sous le graphe permettait de choisir l’heure à laquelle on voulait afficher le graphe.  Il y avait en dessous un autre curseur, non représenté ici, permettant de modifier la vitesse du film.

Remarque – On constate, entre l’image ci-dessus et celle représentée ci-dessous, que la porte 7 affiche un trafic important sans la moindre file d’attente. C’est une porte de télépéage rapide, dans laquelle on peut rouler à 30 km/h. La porte 8 est une porte de télépéage normale : toujours pas d’attente mais un débit plus faible…



rédigé par Hervé Thiriez | 0 commentaire(s)

03 mai 2018

Simulation de trafic : astuces

Pour développer un modèle tel que celui décrit dans l’article précédent, il faut de l’astuce pour résoudre certains problèmes. Quel est à votre avis le problème le plus délicat pour la réalisation d’un tel modèle ? Ne tombez pas dans la facilité : avant d’aller plus loin dans la lecture de cet article, arrêtez votre lecture et réfléchissez à cela : Quel est le problème le plus délicat ?

Si vous n’avez pas encore trouvé de réponse satisfaisante, regardez bien l’image de l’article du 20 avril, où je vous montrais une photo du modèle Excel en train de tourner… Si vous cliquez dedans, vous pourrez la voir à grande échelle…

Voici ce que donne le haut de la partie supérieure, tirée d'une autre simulation :


Eh oui ! En regardant bien, on constate que les segments croisant la ligne horizontale qui passe au milieu des ronds-points sont tous dédoublés. Pourquoi donc ?

Le premier rond-point

Prenons le premier rond-point. Il est formé par les blocs allant de 30 à 37, sans compter les deux segments en double dont le nom n’est pas visible.

Pourquoi cela ? Tout simplement parce que le trafic dans chaque segment du bloc dépend du trafic dans tous les autres segments du bloc. On se trouve donc dans la situation que l’on cherche toujours à éviter dans Excel : la présence de références circulaires.

Comment peut-on faire pour éviter cette situation toujours délicate, et souvent même dangereuse ? La solution que j’ai trouvée pour ce modèle consiste à dédoubler certains segments, comme le 33 et le 37. Ma simulation est conduite par une macro qui lance une itération à chaque top : le trafic du segment 32 arrive au segment 33 pendant que la macro fait passer celui du segment 33 au segment 33 bis qui, lui, au top suivant, basculera son contenu dans le segment 34.

En me mettant ainsi en calcul manuel, avec une macro qui lance le calcul pour un top donné en s’occupant de tous les transferts depuis et vers les segments dédoublés, je résous le problème des références circulaires qui – autrement – rendraient de tels modèles irréalisables.

Analyse d’un segment

Vous vous demandez peut-être aussi comment cette modélisation est réalisée pour chaque segment. Je ne vais quand même pas vous donner toutes mes formules, mais je peux au moins vous montrer comment la description et l’analyse des segments sont assurées.

Voici ce que cela donne pour les deux premiers segments :



rédigé par Hervé Thiriez | 0 commentaire(s)