Monsieur Excel
Pour tout savoir faire sur Excel !

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.