Monsieur Excel
Pour tout savoir faire sur Excel !

20 septembre 2017

Cas n°1 – Table de données (a)

Nous avons déjà traité notre cas de deux façons : la recherche dichotomique pour converger rapidement vers le prix de vente optimal, et le solveur d’Excel. Il reste un troisième mode d’analyse, qui est hélas inconnu de la plupart des utilisateurs d’Excel : la table de données.

Il faut reconnaître que Microsoft a sa part de responsabilité dans l'ignorance de la table de données : cet outil, si puissant soit-il, a été hélas mal conçu et mal documenté, et ce depuis ses débuts dans Excel !

Il existe deux sortes de tables de données, aussi appelées tables d’hypothèses, des tables à 1 dimension et des tables à 2 dimensions. Dans une table à 1 dimension, il y a un paramètre et l’on peut calculer le résultat de plusieurs formules selon les valeurs de ce paramètre. Dans une table à 2 dimensions, il y a deux paramètres, mais on ne peut alors analyser les résultats que d’une seule formule.

Aujourd’hui, nous construisons une table à 1 dimension dans laquelle nous allons voir comment le prix de vente impacte les quantités vendues et la marge bénéficiaire.

Nous entrons en H8:H18 des prix de vente dégressifs de 5 € par ligne, en I7 la formule =B14 et en J7 la formule =B18. Les autres cases du tableau sont pour le moment vides. Nous sélectionnons alors le bloc H7:J18 et utilisons la séquence Outils de données – Analyse de scénarios – Table de données.

Nous remplissons alors le dialogue comme dans la copie d’écran et les résultats apparaissent aussitôt dans la table de données. Si ce n’est pas le cas, un petit calcul avec [F9] résout le problème.


Remarque 1 – C’est là où Excel n’est pas convivial ! Il faut reconnaître que « Cellule d’entrée en colonne » ne veut absolument rien dire…

Notez qu’une table de données est une structure particulièrement solide. Essayez donc de sélectionner les lignes 13 à 15 puis de les supprimer… Ou alors les colonnes H et I… On ne peut détruire que tout l’intérieur de la table : ainsi, quand vous sélectionnez I8:K19, vous avez le droit de supprimer. Mais pas avec I8:J17.

Remarque 2 – Avec la table ci-dessus, on peut converger très rapidement vers la solution optimale. Nous constatons dans la table que l’optimum se situe entre les prix de vente de 135 € et 125 €. Il suffit donc d’entrer 135 en H8 et 134 en H9 ; on tire ensuite H8:H9 vers le bas, on recalcule la table et on trouve le prix optimal à 1 € près…

14 septembre 2017

Cas n°1 – Solution optimale

Nous l’avons vu dans l’article précédent, la recherche dichotomique nous permet, en modifiant successivement les cellules B3 et B4, de rapidement converger vers le prix de vente qui nous permettra de maximiser la marge bénéficiaire.

En fait, avec Excel, il n’est pas du tout nécessaire de passer par la recherche dichotomique pour trouver le prix optimal. Ce problème se résout parfaitement bien avec l’aide du solveur d’Excel.

La commande « Solveur » se trouve à droite dans l’onglet « Données ». Si elle ne s’y trouve pas, vous devez passer par FichierOptionsCompléments pour installer le solveur.

Nous sélectionnons B18, la première marge bénéficiaire, puis nous appelons le solveur. Nous sélectionnons le bouton « Max », nous cliquons en B3 pour identifier la cellule variable, puis nous cliquons dans le bouton « Résoudre » (en bas du dialogue, non visible dans la copie d’écran).


Excel nous informe qu’il a trouvé la solution optimale et nous décidons de la garder. Nous acceptons l’affichage de la solution et nous voyons alors en B3 (sous réserve de lui ajouter deux décimales) que le prix optimal est de 131,25 € pour une contribution de 228.125 €.

Remarque – Il y a trois réponses possibles pour le solveur : Solution optimale (le cas le plus sympa), Pas de solution (vous avez entré des contraintes telles qu’il n’y a aucune solution possible), Solution infinie (vous avez oublié au moins une contrainte).

08 septembre 2017

Cas n°1 – Recherche dichotomique

Il est temps de parler un peu plus de la recherche dichotomique. Supposons que j’aie choisi sans vous le dire un nombre entre 1 et 1.000 et que vous deviez le deviner. Vous avez le droit de ne me poser que des questions du genre « Ton nombre est-il supérieur à 500 ? ». Combien  de questions de ce type devrez-vous me poser pour trouver avec certitude le nombre auquel j’ai pensé ?

La réponse est 10. En effet, supposons par exemple que j’aie choisi 683. Je réponds « Oui ». Vous demandez alors « Est-il supérieur à 750 ? ». Je réponds « Non ». Supérieur à 625 ? Oui. Supérieur à 687 ? Je vous laisse deviner la suite…

En fait, à chaque question, on divise l’intervalle par 2. Avec 10 questions, on couvre donc 2^10 possibilités, soit 1.024 valeurs. On peut par conséquent trouver, avec 10 questions, un nombre allant de 1 à 1.024 !

Notre recherche dichotomique

Le tableau que nous avons construit sert justement à effectuer une recherche dichotomotique, et c’est la raison pour laquelle il comporte cinq colonnes.

Dans le premier tableau ci-dessous, nous sommes au point de départ. Il est clair que, si la courbe est régulière, l’optimum sera obtenu pour un prix de vente entre 140 € et 120 €.

On prend alors 140 € comme point de départ et 5 € comme écart de prix de vente, ce qui nous donne le second tableau.

Là, on voit que dans l’étape suivante, il faut passer à 130 € au départ et 2,5 € d’écart de prix, ce qui donne le troisième tableau.


Dans ce troisième tableau, deux résultats sont égaux : il suffit alors de baisser un peu l’écart de prix de vente pour les départager, par exemple avec 2,4 €.

On converge ainsi très rapidement vers le prix de vente optimal. Vous comprenez maintenant pourquoi il nous fallait cinq colonnes pour effectuer cette recherche dichotomique !

03 septembre 2017

Cas n°1 – Créer le graphique

Pour créer le graphique, l’approche varie selon la version d’Excel utilisée.

Excel 2013 et Excel 2016

Depuis Excel 2013, le mieux est de sélectionner B13:F13 (l’axe des X) puis, en  maintenant la touche [Ctrl] enfoncée, B18:F18 (l’axe des Y) et de faire InsertionGraphiques recommandés. Le premier graphique proposé est alors justement celui qui nous convient :


Excel 2010 ou antérieur

Avec un Excel 2010 ou antérieur, il fallait un peu plus de créativité pour obtenir le résultat désiré… Ceci dit, même si vous avez un Excel récent, vous pouvez lire avec intérêt ce qui suit…

Quand on sélectionne comme ci-dessus B13:F13 et B18:F18 et que l’on demande à Excel un histogramme, Excel représente les deux séries comme deux séries indépendantes, ce qui n’est pas le but recherché. Comme ce n’est pas votre objectif, il vous reste deux solutions efficaces…

Histogramme à partir de B18:F18. Vous sélectionnez uniquement B18:F18 et vous créez l’histogramme. Vous cliquez dans une des barres de l’histogramme. Puis, dans la barre de formule, vous cliquez dans la formule =serie(…) juste après le premier « ; », c’est-à-dire là où l’on identifie l’axe des X, puis vous sélectionnez B13:F13, et le tour est joué !

Remarque 1 – Il est nettement plus rapide de passer ainsi par la formule =serie(…) que de passer par les menus graphiques d’Excel !

Création à partir des deux séries. Ma seconde solution est très originale… Sélectionnez B13:F13 et B18:F18 en  maintenant la touche [Ctrl] enfoncée, puis insérez un graphique X/Y (en nuage de points). Vous obtenez alors le résultat suivant :


Ciel ! Quelle est cette horreur ?

Rassurez-vous, vous êtes à quelques secondes seulement du résultat désiré… Il ne vous reste plus qu’à faire un clic droit sur l’un des points, puis « Modifier le type de graphique » et choisir l’histogramme.

Remarque 2 – Pour ceux qui n’ont pas eu comme moi la chance de connaître Excel depuis ses débuts, sachez que, dans les versions très anciennes, quand on créait un histogramme comme ici à partir de deux séries, il y avait une case à cocher dans le dialogue de création qui permettait de spécifier si la première série était l’axe des X !

Cette fonctionnalité astucieuse a hélas disparu lors des « améliorations » ultérieures d’Excel. Comme d’autres encore, telle que la fonctionnalité où l’on pouvait monter ou descendre une barre d’un histogramme !  Quand on faisait cela, Excel adaptait la cellule concernée en conséquence : si c’était une constante, elle était modifiée ; si c’était une formule la valeur cible s’activait automatiquement pour que l’on puisse modifier une cellule en amont de cette formule.

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.