Monsieur Excel
Pour tout savoir faire sur Excel !

28 août 2017

Cas n°1 – Formules de base

Peut-être avez-vous déjà trouvé toutes les formules du cas que je vous ai présenté dans mon dernier article ? Si c’est le cas, entrez 140 en B3 et 5 en B4. Si vos formules sont bonnes, vous devez trouver les mêmes résultats que ci-dessous. Si ce n’est pas le cas, essayez de corriger votre modèle avant de lire la suite.


Voici donc les formules originales :

B13 : =B3
C13 : =B13-$B$4
B14 : =$B$6+$B$7*($B$5-B13)
B15 : =B14*$B$9
B16 : =B13*B14
B17 : =B15+$B$8
B18 : =B16-B17

Remarque 1 – Toutes les formules, à part celle de B13, sont recopiées vers la droite.

Remarque 2 – Pourquoi avoir utilisé des « $B$ » là ou des « $B » auraient suffi ? C’est en fait le résultat d’un souci d’efficacité lors de la frappe des formules : je n’ai eu à chaque fois qu’à utiliser une seule fois [F4] pour tout bloquer, au lieu de devoir utiliser trois fois la touche pour ne bloquer que la colonne !

Sur le plan méthodologique, il est un peu malheureux que la ligne 13 soit la seule pour laquelle les formules à droite ne sont pas toutes copiées depuis la colonne B. Nous résolvons ce problème ci-dessous…

Comment avoir toutes les formules originales en colonne B ?

Essayez de trouver la réponse avant de lire la suite…

En fait, ce n’est pas très compliqué…
Il suffit pour cela d’entrer en B13 la formule :
=$B$3-$B$4*(colonne()-2)

On pourrait encore pinailler et dire que cela ne marchera plus si l’on insère une ou plusieurs colonnes à gauche de la colonne A. Là encore, je peux vous proposer une solution :
=$B$3-$B$4*(colonne()-colonne($B$3))

Nous avons certes augmenté la flexibilité de notre modèle,  mais au prix d’une formule bien plus lourde. C’est là où il faut se poser la question suivante : « Le risque que des colonnes soient insérées à gauche est-il suffisant pour que l’on alourdisse ainsi les formules ? »

Toute bonne modélisation implique de savoir se poser de telles questions et d’être capable d’y répondre de la façon la plus efficace possible.

22 août 2017

Cas n°1 – Enoncé du problème

Voici un petit cas que j’utilise fréquemment dans mes formations à l’utilisation avancée d’Excel. Ce cas sera pour nous l’occasion – à travers plusieurs articles consécutifs – de découvrir (ou de réviser) un certain nombre d’astuces de modélisation avec Excel.


Vous commercialisez un produit vendu normalement 150 € et qui, à ce prix-là, se vend à 5.000 exemplaires (cf B5:B6). L’élasticité de 80 en B7 signifie que, si l’on vend le produit 1 € moins cher, il s’en vendra 80 unités de plus, et inversement. Compte tenu de ces données et des coûts (B8:B9), le problème à résoudre consiste à trouver le prix de vente pour lequel la marge bénéficiaire sera la plus grande.

Le but du jeu est de créer le modèle suivant, avec cinq colonnes dans lesquelles le prix de vente est à chaque fois baissé de l’écart en B4, ce qui permet de visualiser l’évolution du bénéfice sur le graphe. On fera alors une recherche dichotomique en faisant varier B3 et B4 pour identifier pas à pas le prix de vente optimal.

Remarque – Certes, cela pourrait aussi se faire directement avec le solveur, et nous le verrons d’aileurs nous-mêmes par la suite. Mais notre approche pas à pas nous permettra de mettre en relief un certain nombre d’astuces de modélisation dans Excel.


Vous pouvez, ce n’est pas très compliqué, créer ce modèle par vous-même. Cela vous permettra ensuite, en lisant mes articles, de voir s’il n’y avait pas, ici ou là, une meilleure façon de procéder que celle que vous avez suivie.

Je peux vous garantir que – quel que soit votre niveau actuel en Excel –  il y a au moins une chose que je vous présenterai dans cette série d’articles que vous ne connaissiez pas déjà !  

16 août 2017

Et la touche « Windows » !

La touche Windows, dont quelques exemples d’icônes sont reproduits ci-dessous, vous donne un accès direct à une flopée de raccourcis. Ces raccourcis sont certes utilisables pour les autres applications Windows, mais ils sont quand même bien utiles quand on travaille avec Excel.


C’est probablement dans Excel la source de raccourcis la plus méconnue. Mais il est vrai que la plupart de ces raccourcis ne concernent pas directement Excel.

Win + D (Desktop) : réduit toutes les applications ouvertes.

Win + E (Explorer) : ouvre l’explorateur de fichiers

Dans de nombreuses situations, c’est la façon la plus rapide d’accéder dans Excel à l’ouverture de fichiers sur lesquels on a travaillé récemment. On voit d’un coup d’œil la liste des derniers fichiers ouverts, toutes applications confondues, avec leur chemin complet.

Win + L (Lock) : verrouillage immédiat de l’écran.

Win + M (Minimize) : réduit toutes les applications ouvertes (comme Win+D). Je ne vois pas bien l’intérêt de ce double emploi…

Win + R (Run) : il faut entrer le nom d’un programme, dossier, document ou ressource Internet que l’on demande à Windows d’ouvrir.

Win + P (Project) : pour modifier le lieu de projection de l’écran.


Win + n° : ouvre la tâche placée dans cette position dans la barre des tâches en bas de votre écran. Ainsi, si l’icône de Powerpoint est la troisième à droite de l’icône de Windows dans la barre des tâches, Win + 3 ouvre Powerpoint. Cela joue en quelque sorte le rôle d’un [Alt]-[Tab] intelligent.

Win + flèche de déplacement : redimensionne et/ou déplace la fenêtre Excel.

Win + PgUp/PgDn : déplace la zone affichée dans Excel d’une page vers le haut/vers le bas.

10 août 2017

Commentaires sur l’énigme

L’énigme que nous avons étudiée dans les articles précédents avait été proposée par M. Laurent Poli à la société Eurodécision qui publie plusieurs fois par an des énigmes sur son blog. Lui-même a d’ailleurs trouvé une autre solution presque entièrement par VBA, et qui aboutit au même résultat. Sa solution est donc plus compacte que celle que je vous ai proposée, mais ne permet pas de suivre aussi facilement la façon dont les sièges de l’avion se remplissent.

D’autres personnes ont trouvé la solution par récurrence. Il est clair que, dans le cas de 2 passagers, il y a une chance sur deux que le second passager puisse occuper la place qui lui était réservée.On vérifie aisément que c’est encore vrai avec 3 passagers. Reste alors à construire le raisonnement par récurrence qui prouve qu’il en sera de même avec n+1 passagers si c’est vrai avec n passagers…

Quel est l’avantage de passer par un modèle de simulation tel que celui que je vous ai proposé, par rapport par exemple à une solution analytique ? En fait, il y en a deux.

Le premier est que mon modèle de simulation permet de suivre pas à pas, de façon visuelle, le remplissage de l’avion et de vérifier à tout moment le bon fonctionnement de l’algorithme. C’est donc à la fois un outil pédagogique et un contrôle de sécurité permanent.

Le second est que, s’agissant d’une simulation, on peut intégrer sans limite des règles ou conditions supplémentaires, ce qui ne serait pas possible avec une approche analytique. On peut par exemple décider que toute personne ne pouvant s’asseoir à sa place réservée s’assiera sur la place vide la plus proche de celle-ci. Ou, dans une autre simulation, sur une place de même nature (fenêtre, couloir).

Si vous voulez en lire plus sur cette énigme, voici quelques références qui m’ont été proposées par Laurent Poli :

Ou alors, aussi par simulation mais sans Excel, avec l’aide du logiciel gratuit d’analyse statistique « R » :

Si vous désirez recevoir à titre gracieux mon fichier Excel, envoyez-moi un mail à cet effet à :

04 août 2017

Passagers d’un avion (e)

Maintenant que nous avons un modèle qui simule parfaitement le remplissage d’un avion, il ne reste plus qu’à simuler un grand nombre de remplissages pour voir quelle est – en moyenne – la probabilité pour que le 100ème passager puisse occuper la place qui lui était réservée.

Voici à quoi ressemble le modèle final. La macro présentée dans l’article précédent se lance avec le bouton « 1 scénario ». La macro de simulation complète se lance avec le bouton « Simulation ». Elle fait 10 boucles de 100 itérations dont les résultats sont affichés en Y2:Y101, puis stocke le résultat en colonne Z. Enfin, Z13 affiche la moyenne ainsi obtenue sur 1.000 remplissages de l’avion.


Voici le code de la macro :

Sub Simulation()
'
' Auteur : Hervé Thiriez
'
Dim I As Integer
Dim K As Integer
Dim L As Integer

Range("V2:V11").ClearContents
For L = 1 To 10
    Range("Y2:Y101").ClearContents
    Application.ScreenUpdating = False
    For K = 1 To 100
        Range("A1") = 1
        Calculate
        For I = 2 To 19
            Range("A1") = I
            Calculate
        Next I
        Cells(K + 1, 25) = Range("Rés")
    Next K
    Cells(L + 1, 26) = Range("Pct").Value
    Application.StatusBar = "Fin du traitement n°" & L
    Application.ScreenUpdating = True
Next L

Application.ScreenUpdating = True
Application.StatusBar = ""
Calculate
End Sub

On obtient donc le résultat moyen de 50%. Notez qu’il est important de reproduire les colonnes F:G jusqu’à V:W. En effet, au début, je m’étais arrêté 4 colonnes plus tôt et j’obtenais alors un résultat moyen de plus de 60% !

Et voilà ! Nous avons trouvé la solution de l'énigme :)