Monsieur Excel
Pour tout savoir faire sur Excel !

16 octobre 2017

Une étude de cas – Conclusion

Nous avons démarré le 22 août dernier une série d’articles sur le « Cas n°1 ». En fait, il s’agit d’une séquence relative à un petit problème d’optimisation de prix de vente qui nous a servi de véhicule pour vous présenter différentes façons de résoudre un problème d’optimisation.

Le 8 septembre, nous avons vu comment résoudre le problème par une recherche dichotomique, une méthode qui permet de converger rapidement vers la solution optimale.

Le 14 septembre, nous avons vu comment résoudre le problème en utilisant le solveur, un add-in d’optimisation fourni avec Excel mais qui n’est pas installé par défaut. Il faut donc l’installer en passant par la séquence FichierOptionsCompléments. Avec le solveur, on trouve bien sûr immédiatement le prix de vente optimal.

Ensuite, nous avons résolu le problème en utilisant des tables à 1 dimension (20 septembre) puis à 2 dimensions (27 septembre et 3 octobre).

Enfin,  le 9 octobre, nous avons procédé par simulation en tirant simplement au hasard une série de prix de départ pour voir lequel donnait la meilleure marge bénéficiaire. Ce qui est surprenant, c’est que cette approche qui n’exerce aucune intelligence, dans la mesure ou chaque nouvelle itération ne tient aucun cas des résultats des itérations antérieures, donne contre toute attente d’excellents résultats.

Cette série d’articles a été l’occasion pour nous de voir (ou revoir) un certain nombre d’outils de modélisation auquel Excel vous donne accès et – par la même occasion – de constater qu’il y a souvent dans Excel une grande variété d’approches possibles pour résoudre un problème donné.

Pour télécharger le fichier de cette série d’articles :

09 octobre 2017

Cas n°1 – Simulation

Nous avons vu dans les articles précédents plusieurs façons d’identifier le prix de vente qui maximisera la marge bénéficiaire. Il reste une approche tout à faire originale pour résoudre ce type de problème, et qui donne des résultats d’une qualité surprenante.

J’ai donc écit une petite macro qui tire 20 fois au hasard un prix de vente entre 100 € et 150  € et copie en colonnes E et F le prix de vente ainsi tiré au hasard et la marge bénéficiaire correspondante.

En F21, on identifie avec =max(F1:F20) le meilleur bénéfice des 20 tirages et, en E21, le prix de vente correspondant avec =index(E:E;equiv(F21;F:F;0)).

J’ai reporté en I21 et J21 le prix optimal et le bénéfice maximal. En H21, la formule =abs(J21-F21)/J21 calcule l’écart entre le meilleur bénéfice des 20 tirages et le bénéfice optimal.


Ce qui est vraiment surprenant, c’est qu’avec seulement 20 tirages totalement aléatoires, on parvienne à d’aussi bons résultats.

Ainsi, en faisant 10 exécutions consécutives de la simulation, le pire résultat avait un écart de seulement 0,30%, le second le plus mauvais de 0,12%, le troisième de 0,09% et les sept autres de 0,06% ou moins !

Un résultat impressionnant pour seulement 20 tirages au hasard, n’est-ce pas !

Certes, d'autres séries de 10 donnent des résultats un peu moins brillants, mais ils sont toujours excellents considérant qu'il n'y a que 20 itérations...

03 octobre 2017

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

Nous avons remarqué dans le dernier article que la table de données à 2 dimensions permettait de faire varier deux paramètres, mais – en revanche – ne pouvait être utilisée que pour analyser les valeurs d’un résultat.

Il y a une façon très esthétique de contourner cette limitation en utilisant une fonction assez peu connue des utilisateurs lambda, la fonction choisir().

La fonction choisir()

La syntaxe de cette fonction est =choisir(index;formule_1;formule_2 ;…).

index est un nombre allant de 1 à 254 ou une formule aboutissant à un tel nombre.

Quand index est égal à 1, le résultat est celui de formule_1 ; quand il vaut 2, c’est celui de formule_2, et ainsi de suite.

Je n’ai encore jamais testé cette fonction avec 254 arguments, et je ne suis pas sûr qu’Excel vous laisse saisir une formule de cette longueur. Si vous l’avez déjà tenté, n’hésitez pas à ajouter un commentaire en nous disant ce qui vous est arrivé…

Mise en place de la solution

Insérons une ligne au-dessus de la ligne 20, pour aérer un peu et créons en A20 une zone de validaton par liste à partir de $A$14:$A$18. Cela nous permet donc de sélectionner Ventes prévues, Coût variable total, Chiffre d'affaires, Coût produits vendus ou Marge bénéficiaire.

Il suffit alors d’entrer en A21 la formule =recherchev(A20;A14:B18;2;faux).

Nous avons donc à présent une table capable de calculer les résultats de cinq formules différentes en fonction de deux paramètres !


Remarque – Reste à résoudre un problème esthétique. Quand nous sélectionnons « Ventes prévues »,  les quantités vendues sont formatées en € !

Pour résoudre ce problème, il suffit d’utiliser le format personnalisé suivant pour l’intérieur de la table de données : [>10000]# ##0 €;# ##0. En effet, toutes les valeurs en € sont supérieures à 10.000 !

27 septembre 2017

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

Dans l’article précédent, nous avons appris à créer une table à 1 dimension, ce qui permet d’analyser la façon dont plusieurs résultats varient quand on modifie un paramètre.

Aujourd’hui, nous créons une table à 2 dimensions, ce qui ne permet d’observer que les variations d’un résultat unique, mais autorise – à titre de compensation ! – les modifications en fonction d’un second paramètre.

Nous avons donc créé la table A20:F31, avec différentes valeurs d’élasticité en B20:F20 et différents prix de vente en A21:A31. En A20, nous avons entré la formule =A18 pour faire référence à la marge bénéficiaire. Dans une table à 2 dimensions, la formule à analyser se trouve toujours dans le coin supérieur gauche de la table.

Remarque 1 – Afin de vous permettre de voir le maximum de choses dans la copie d’écran, j’ai masqué les lignes 5:6 et 11:17.


Notre table comporte 55 cellules, ce qui signifie qu’Excel calcule 55 fois le classeur chaque fois que la table se recalcule. Si notre modèle était bien plus conséquent et avait besoin d’une minute pour se recalculer sans la table,  il lui faudrait 55 minutes pour se recalculer avec la table !

C’est pour cela que les options de calcul d’Excel vous offrent la modalité de calcul « Automatique sauf les tables ». Je vous conseille de l’utiliser quand vos modèles sont gourmands en temps de calcul.

Pire encore, si vous avez une première table affichant 10 résultats et une seconde table affichant 55 résultats, Excel calculera 550 fois votre classeur !

Une autre solution, que je mets souvent en place chez mes clients, consiste à créer une macro qui crée la table, la calcule, puis copie l’intérieur pour en coller ensuite les valeurs. Je crée alors par-dessus la cellule du coin supérieur gauche de la table un bouton associé à cette macro.

Remarque 2 – Il suffit de changer la formule en A20 pour que la table calcule un autre résultat. Si par exemple nous y entrons la formule =B14, ce sont les quantités vendues que la table affichera. Il ne restera alors plus qu’à ôter le format monétaire des résultats pour que le tableau soit impeccable.

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 !