in

Le Solveur Excel: Découvrez La Fonction La Plus Spectaculaire D’Excel

solveur Excel

Le solveur Excel un outil d’optimisation très intéressant dans la mesure où il nous permet de gagner énormément de temps et de résoudre des problèmes plus ou moins complexes.

C'est la définition du solveur Excel mais quand je la lis je n'y comprends rien !

C'est pourquoi dans cet article , je vais essayer de décortiquer cet outil et à la fin de ce guide vous serez totalement capable de l'utiliser.

Qu’Est-Ce Que Le Solveur Excel Et à Quoi Sert-Il ?

Pour prendre des décisions fondées et mettre une place des stratégies plus ou moins efficaces, il faut partir de certaines données pour faire des prévisions et des estimations pertinentes.

Il s'agit surtout d'être capable de se projeter à court, moyen ou à long terme pour affiner sa stratégie et d’optimiser ses performances.

C’est exactement le rôle du Solveur Excel.

Il s’agit d’un outil qui assure la résolution mathématique de problèmes ou bien d'équations définis par l’utilisateur.

Par exemple, si vous voulez prévoir le nombre de ventes pour tripler votre chiffre d'affaires tout en optimisant la production, la qualité et autres critères: C'est le solveur qui s'en charge.

Et il y a d'innombrables exemples et applications à cette fonctionnalité miracle que nous propose Excel.

Préparation du solveur 

Configurer le solveur

Dans la plupart des cas, le solveur Excel ne figure pas sur votre barre d’outils.

Il faut donc penser à l’ajouter.

Pour cela :

  • Dans le menu principal, cliquez sur "Options" :
solveur excel
  • Cliquez ensuite sur l'option "Compléments" dans le volet de votre gauche.
  • Choisissez « Complément solveur » puis « Atteindre ». 
  • Validez pour la suite.
solveur excel
  • Sur la nouvelle fenêtre on coche « Analysis Toolpack » puis « Complément Solveur » et on valide :
solveur excel

Le solveur apparaîtra sous l’onglet « Données » dans la partie « Analyse » :

solveur excel

Utiliser Le Solveur

Si l’utilité du solveur Excel, reste un peu flou pour vous, nous allons dans ce qui suit détailler quelques exemples pratiques pour mieux vous éclairer.

Avant de commencer, voici le classeur complet qui contient tous les exemples de ce guide afin de vous faciliter le suivi :

Fichier Pratique pour les exemples du Solveur Excel - Wikiclic

Nous avons choisi de travailler sur un exemple de tableau de bord pour le suivi des ventes.

Cet exemple constitue la feuille "Situation n°1" du classeur que vous avez téléchargé.

solveur excel

Exemple Pratique N°1

Pour ce premier exemple nous avons choisi de travailler sur les prévisions de vente pour l’année 2021.

Je dis bien "Prévisions", puisque l'objectif fixé étant d'atteindre un total de vente de 110000 Euros.

Le solveur nous aidera à déterminer le total de vente à atteindre pour chaque type de vente ainsi que les contraintes à prendre en compte rapport à l’année 2020. 

Pour cela, voici comment procéder :

  • Pour commencer, on insère une colonne dédiée aux prévisions 2021.
  • Ensuite, nous devons paramétrer notre formule permettant de calculer le total des différents types de ventes.
solveur excel
  • Sélectionnez par la suite, la cellule contenant le total calculé. Soit la cellule H12 pour notre exemple.
  • Pour utiliser le solveur Excel, activez l’onglet « Données » puis l'option «Solveur ».
solveur excel

Pour paramétrer le solveur Excel :

  • Sur la nouvelle fenêtre « Solveur », on commence par renseigner la référence de la cellule contenant notre «Objectif à définir » qui correspond à la cellule «H12 » (Total des ventes 2021). Soit la cellule H12. Le signe $ permet de figer la valeur pour qu'elle reste fixe. 
  • Ensuite, nous devons saisir la valeur qu’on voudrait atteindre. Soit la valeur «110000 ». Il ne faut pas oublier de cocher l'option "Valeur" pour que ce soit la valeur numérique correspondante qui soit estimée. 
  • L'étape suivante consiste à indiquer les cellules variables. Ces cellules correspondent aux références des cellules dans lesquelles seront affichées les prévisions pour chaque vente (de H7 à H11).
  • Après tout cela, il ne reste plus qu'à fixer les contraintes à respecter. Il faut juste cliquer sur l'option "Ajouter".
solveur excel

La première contrainte consiste à ce que les prévisions soient des valeurs entières exactes puisque nous parlons de quantité à vendre.

La principe étant de paramétrer les cellules de façon à avoir des quantités exactes et supérieures aux anciennes valeurs.

Pour cela :

La 1ère contrainte : Valeur entière exacte

  • En ayant déjà cliquer sur le bouton "Ajouter" de l'interface du solveur, vous verrez cette fenêtre :
solveur excel

Les paramètres sont simples à définir :

  • Référence de la cellule (la première cellule de la colonne, soit H7).
  • Le type de la valeur : il faut choisir "Ent" pour entier.
solveur excel

Cliquez sur le bouton "Ajouter" pour introduire la 2ème contrainte.

La 2ème contrainte : Valeur supérieure à la précédente

  • La référence de la cellule étant la même, soit H7, spécifiez "Supérieur ou égal" dans le deuxième paramètre.
  • La référence de l'ancienne valeur est la valeur de 2020, soit G7 ayant la valeur 6150.
solveur excel

Pour prendre en compte les contraintes, cliquez sur le bouton "Ok".

Répétez cette procédure pour toutes vos cellules variables et à la fin, les contraintes s'afficheront dans le solveur.

solveur excel

Pour lancer le solveur et afficher les prévisions, cliquez sur le bouton « Résoudre »  puis sur cochez « Conserver la solution du solveur » :

Il ne reste plus qu'à valider :

Les prévisions sont les ventes à effecteur pour atteindre la somme voulue.

C'est le début de l'élaboration d'un plan de travail.

Exemple Pratique N°2

Pour le deuxième exemple, notre objectif consistera à ce que le chiffre d'affaire en 2021 soit multipliée par 4 par rapport à 2015.

Nous souhaitons estimer le nombre de ventes nécessaires  qu’on devrait réaliser en se basant sur ceux effectuées 2015.

Pour cela :

  • Commencez par calculer le montant à atteindre en 2021 (cellule H12), soit le montant de 2015 (cellule B12) * 4. La formule sera : 4*B12. 
  • Dans le fenêtre du solveur, on commence par saisir notre  «Objectif à définir».  Il est représenté par le but à atteindre, soit la cellule H12. 
  • Ensuite, nous devons spécifier la valeur à atteindre, soit 332000 en cochant l'option "Valeur".
  • Il ne reste plus qu'a spécifier les données variables qui correspond aux cellules C12, D12, E12, F12 et G12.

Les paramètres étant fixés, l'étape suivante consiste à spécifier les contraintes.

La 1ère contrainte : Valeur entière exacte

Notre première contrainte oblige le solveur à calculer des valeurs entières exactes.

Toutes les valeurs des cellules variables (les quantités de vente) doivent êtres des valeurs exactes. 

Pour cela :

  • En appuyant sur le bouton "Ajouter" du solveur, vous verrez la fenêtre ci-dessous.
  • Les paramètres de la contrainte sont la référence de la cellule concernée, soit la première cellule variable qui correspond au nombre de vente de l'année 2015 donc la cellule C12 et le type "Ent" pour entier. 

La 2ème contrainte : Valeur supérieure à la précédente

La deuxième condition stipule que le nombre de ventes doit dépasser celui des années précedentes.

Pour fixer cette contrainte :

  • De la même façon, appuyez sur le bouton "Ajouter" de l'interface principale du solveur et dans la fenêtre qui s'affiche renseigner la cellule variable, la contrainte et la valeur comparative. Soit la cellule C12 qui doit être supérieure ou égal à la valeur 83000 (celle de 2015).

Chacune des cellules variables doit avoir ces deux contraintes.

Pour ajouter une contrainte vous pouvez soit appuyez sur le bouton "Ajouter" de l'interface principale du solveur :

solveur

Ou appuyez sur le bouton "Ajouter" de la fenêtre des contraintes :

Une fois que votre problème soit bien défini, il ne reste plus qu'à cliquer sur le bouton « Résoudre » :

Pour afficher nos solutions, il ne faut pas oublier de cocher l'option « Conserver la solution du solveur » puis valider :

Ceci a permis au solveur de créer un schéma prévisionnel apte à estimer le nombre de ventes à atteindre pour l'année 2021 afin de multiplier le chiffre d'affaire par 4.

Exemple Pratique N°3

Dans cet exemple, nous souhaitons annuler les conventions tout en gardant au minimum le même total de ventes qu’en 2020. 

Nous allons utiliser le solveur pour estimer le nombre des autres types de vente pour avoir le même nombre total de ventes. 

On commence donc par paramétrer notre formule qui égale à la somme des différents types de ventes sans les conventions.

Soit =H7+H8+H9+H11 (sans la colonne H11 qui est celle des conventions) :

Comme pour les deux exemples précédents, en accédant au solveur, vous devez :

  • Remplir la case « Objectif à définir » qui représente le résultat souhaité et qui correspond à la cellule « H12 » (Total des ventes 2021).
  • Saisissez ensuite la valeur que nous voulons atteindre soit « 103500 ».
  • Indiquez quelles sont les cellules variables et qui correspondent aux références des cellules dont on veut déterminer les valeurs (de H7 à H10).

A présent, nous devons fixer les contraintes en appuyant sur le bouton "Ajouter" de l'interface principale du solveur d'Excel. 

Les contraintes sont les mêmes utilisées pour les exemples précédents.

Soit des valeurs entières et des valeurs supérieures aux valeurs antérieures.

Les valeurs doivent aussi être supérieures ou égales à celles de 2020.

Pour cela, on définit notre deuxième contrainte comme suit :

En appliquant les contraintes pour toutes les cellules variables, le solveur apparaîtra comme ceci :

il ne reste plus qu'à appuyer sur le bouton "Résoudre" et à cocher « Conserver la solution du solveur » :

On obtient ainsi les valeurs des ventes à atteindre, si on ne compte plus sur les conventions:

Contraintes et Limites Du Solveur

Si le Solveur Excel a démontré sa performance et son efficacité pour résoudre un grand nombre de problèmes, il reste toutefois limité dans certains cas en termes de fiabilité des résultats fournis si vous utilisez des équations non linéaires et un peu complexes.

Il est donc généralement conseillé de supposer que le modèle est linéaire (cocher la case Modèle supposé linéaire) ou bien de procéder à plusieurs résolutions avec différentes valeurs initiales pour obtenir des résultats plus justes.

En plus, si les valeurs utilisées ne sont pas du même type, les résultats fournis par le solveur seront moins fiables. Comme si par exemple, vous utilisiez dans le solveur des chiffres et d'autres valeurs en pourcentage.

Conclusion

Le Solveur Excel vous offre la possibilité d’optimiser à la fois vos bénéfices et vos ressources, il vous fera gagner du temps et vous permettra ainsi de rester concentrés sur vos objectifs et focalisés sur les solutions plutôt que de s’éterniser sur la résolution de vos équations. 

Donc, si vous avez besoin d’optimiser une ou plusieurs valeurs, le Solveur Excel vous épargnera des calculs manuels assez longs à réaliser sans prendre en considération le risque d’erreur encouru.

Par ailleurs, il faut être conscient que pour bien fonctionner et aboutir à des prévisions plus ou moins exactes, fiables et exploitables, le solveur Excel doit disposer d'un problème précis.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

sierreur

SiErreur : La Fonction Miracle Pour Gérer Toutes Les Erreurs Excel

nb si excel

La Fonction NB SI Excel Avec NB, NB.VAL, NB.VIDE [Guide Pratique]