Monsieur Excel
Pour tout savoir faire sur Excel !

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() !

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…



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…



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 :



27 avril 2018

Simulation de trafic : méthode

Dans l’article précédent, vous avez pu voir l’écran que l’on obtient avec mon modèle de simulation de trafic routier. Dans ce modèle, on a décomposé le réseau en un certain nombre de segments, chacun d’entre eux étant coloré en vert, orange ou rouge selon son degré de saturation à l’heure affichée. Le film permettait donc de suivre l’évolution du trafic dans le temps.

Le modèle permettait aussi de lancer des simulations pour calculer la distribution, suite à des simulations stochastiques réalisées avec l’aide de l’add-in Crystal Ball, du temps de trajet entre un point A et un point B.

Je vais en profiter pour vous expliquer un peu comment ce modèle original a été développé.

Genèse du modèle

Des ingénieurs ont analysé le réseau à modéliser et ont produit le schéma ci-dessous. Ils ont décidé de mettre à chaque point un capteur permettant de mesurer le passage des voitures et leur vitesse, et ce minute par minute. Chaque fichier Excel ainsi généré par jour comptait 1.440 lignes (24*60) et deux colonnes par capteur. Les ingénieurs ont ainsi créé un grand nombre de modèles Excel : jour normal, début de semaine, fin de semaine, jour de week-end, jour férié, jour de vacance, jour de grand départ, jour de grand retour, …


C’est cet ensemble de fichiers qui m’a permis de mettre au point le modèle de simulation de trafic et qui a permis ensuite à mon client de valider le modèle que j’avais développé. C’était la première fois que je simulais du trafic routier et je suis donc parti de zéro, si ce n’est pour ma maîtrise d’Excel…

J’ai commencé par prendre un jour normal. J’utilisais la vitesse et le débit aux points d’entrée dans le système et, à l’aide de mes formules, essayais d’en déduire la vitesse et le débit dans chaque segment. Je pouvais alors comparer les résultats que j’obtenais à ceux qui avaient été enregistrés. Cela me permettait d’affiner mes formules pour réduire l’écart entre les observations et mes prévisions.

Quand mes formules se sont avérées bonnes pour un jour normal, j’ao commencé à analyser un autre type de jour, puis à modifier mes formules pour que, restant efficaces pour le jour normal, elles deviennent meilleures pour ce second type de jour.

J’ai ensuite fait de même avec un troisième type de jour, puis un quatrième, puis un cinquième…

Validation du modèle

Comment peut-on valider la création d’un tel modèle ? Cette question est intéressante !

La solution que mon client et moi avons retenue était la suivante. Mon client m’a confié, pour la création du modèle, la moitié des fichiers de chaque type : la moitié des fichiers de jours normaux, de vacance, de grand départ,…

J’ai donc développé ce modèle avec la moitié des fichiers originaux.

Quand j’ai terminé mon modèle, mon client a testé ma solution sur les fichiers qui ne m’avaient pas été confiés et a pu ainsi comparer, pour chaque segment, le trafic que je prévoyais et celui qui avait été observé. Il était convenu que mon modèle serait validé si les écarts entre les deux restaient faibles. Ce qui fut le cas dès la première livraison de mon modèle : )

Amortissement du modèle

Ce modèle qui a coûté à mon client environ 50 K€ HT a été de fait largement amorti en moins de 6 mois.

En effet, vous avez pu constater dans le graphe qu’il y avait des ronds-points. Pour chacune des positions où l’on en trouvait un, il était possible de remplacer le rond-point par un stop ou par un feu rouge.

Le modèle ayant identifié un point du réseau où il y avait des problèmes de façon chronique, le client a testé les deux autres solutions, ses consultants transport lui conseillant de remplacer le rond-point par l’une des deux autres solutions.

Le modèle a démontré que l’une des deux solutions améliorait en effet le trafic au rond-point concerné. Mais il a aussi montré que, du coup, c’est un autre point du réseau, à quelques centaines de mètres qui, lui, avait à présent des problèmes. Ainsi, mon client a pu éviter ces travaux qui n’auraient pas résolu le problème de fond.

20 avril 2018

Du cinéma avec Excel 2016

Dans le dernier article, nous avons soulevé un problème : la macro « Movie » ne fonctionne que pour les versions d’Excel antérieures à 2016.

Remarque – En fait, il manquait dans la rédaction originale de l’article précédent une commande essentielle pour que le film fonctionne, même avec les versions d’Excel antérieures à 20126, la commande Application.ScreenUpdating=True. Cela a été corrigé a posteriori dans cet article.

Mon collègue MVP Jon Peltier a trouvé une solution pour ce type de situation :  il faut utiliser la commande DoEvents qui, si vous misez sur Internet les articles la concernant, n’est citée nulle part comme ayant cet effet bénéfique dans l’animation cinématographique…

Le piège, c’est que cette commande seule ne suffit pas : il faut exécuter au moins deux fois la commande pour que le cinéma fonctionne. Si un lecteur trouve une explication cohérente à ce phénomène, je suis preneur de son explication…

Voici donc la macro définitive, que nous associons au bouton Cinéma :

Sub Movie()
For i = 1 To 400
    Range("C2") = i
    For j = 1 To 50
        x = x + 12 ^ 2
    Next j
    DoEvents : DoEvents
Next i
End Sub

Exemples de films en Excel

J’ai réalisé de nombreux modèles Excel dans lequel je faisais du cinéma. En voici trois exemples pris dans les 20 dernières années, dans l’ordre chronologique…

1. Simulation des tapis roulants portant les bagages pour Aéroports de Paris. A titre indicatif, il s’agissait alors d’un appel d’offres lancé par AdP, que j’ai gagné et qui m’a valu ensuite des commandes de modèles de la part d’AdP durant plus de 10 ans.  Pour info, mon devis était de 300 KF (c’était la dernière année avant les euros) et le second concurrent le moins cher demandait 1,5 MF, soit cinq fois plus… J’étais le seul à proposer une solution avec Excel…

2. Simulation du fonctionnement d’une gare de péage en fonction des portes ouvertes et du trafic attendu. Ce modèle a été vendu à plusieurs sociétés de péage. Le film montrait dans le temps, pour les portes de péage ouvertes, le nombre de véhicules en attente et le débit en une minute.

3. Simulation du trafic routier dans une zone limitée. A titre indicatif, ce modèle de 50 K€ a été amorti par le client en moins de 6 mois.

4. Simulation du déplacement des piétons dans une aérogare.

Voici ce que donne l'exemple n°3 :




14 avril 2018

Faites votre cinéma avec Excel

Le modèle que nous avons mis au point dans le dernier article permet de modifier facilement le point de départ d’une série graphique.

Pour montrer cette évolution en continu, en un mot pour faire du cinéma avec Excel, il suffit d’écrire une macro qui modifiera la valeur de C1 entre ses limites actuelles 0 et 350.

Pour commencer, nous modifions la définition des cellules C1 et C2. En C1, nous entrons la formule =index(A:A;C2), et nous entrons la valeur 2 en C2.

Nous créons la macro suivante, que nous associons au bouton Cinéma en bas et à gauche du graphe.

Sub Movie()
For i = 1 To 400
    Range("C2") = i
    For j = 1 To 50
        x = x + 12 ^ 2
    Next j
    Application.ScreenUpdating=True
Next i
End Sub

Dès que l’on clique fans le bouton, on observe le film attendu, à une condition : il faut pour cela avoir utilisé une version d’Excel antérieure à Excel 2016.


La boucle For..Next sert à ralentir le film qui, autrement, tourne trop vite. S’il tourne trop vite pour vous ou trop lentement, modifiez le 50.

Si vous utilisez Excel 2016, voyez si vous trouvez le moyen de faire ce qu’il faut pour obtenir le film désiré. Je vous donnerai la réponse dans mon prochain article mais je vous préviens : ce n’est ni naturel ni simple à trouver…