Monsieur Excel
Pour tout savoir faire sur Excel !

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…

07 avril 2018

Zoom sur une partie d’un graphe

On dispose parfois de données numériques sur de longues périodes et l’on souhaite afficher un graphe à partir d’un certain point dans cette chronique.

Cela est utile par exemple pour la comparaison de cours d’actions quand on veut comparer deux séries, par exemple le CAC 40 et le cours d’une action, en choisissant le point de départ à une date donnée.

Dans l’exemple ci-dessous, nous avons entré des dates consécutives en colonne A. En B1, histoire d’avoir une jolie courbe, nous avons entré la formule =sin(pi()*(ligne()-1)/22,5)*(1-cos(pi()*(ligne()-1)/17)) que nous avons tirée vers le bas jusqu’en A500 (mars 2018).

En C1, nous avons entré une validation par liste alimentée par A1:A500.

En C2, avec la formule =equiv(C1;A:A;0), nous calculons le numéro de la ligne où se trouve la date sélectionnée en C1.

En D1, nous avons mis la formule =index(B:B;$C$2+LIGNE()-1), reproduite ensuite jusqu’en D100. Le graphe représente les valeurs D1:D100.

En F1, cellule cachée par le graphe, nous avons entré la formule ="Début en "&texte(C1;"mmmm aaaa"). Pour le titre du graphe, nous avons entré la formule =F1.


Remarque – On n’a pas le droit d’entrer la formule de F1 directement dans le titre du graphe, mais on a le droit de faire ainsi référence à une cellule contenant la formule du titre. Cela permet, comme nous le faisons ici, d’avoir un titre dynamique.