Comment utiliser le solveur d’Excel ?

Guide « 83 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchageant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Aujourd’hui, je vous propose de découvrir une fonctionnalité avancée d’Excel : le solveur qui permet, un peu à la manière de l’outil de détermination de la valeur cible, d’obtenir automatiquement la valeur à affecter à une cellule pour obtenir un résultat donné sur une autre cellule. Nous verrons également que le solveur va permettre de définir des contraintes qui vont afin d’ajuster très précisément le résultat retourné par celui-ci.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

1. Le solveur

Dans ce tutoriel, nous allons découvrir le fonctionnement du solveur d’Excel.

Il s’agit d’un outil de simulation extrêmement pratique et puissant proposé par Excel qui permet, à l’instar du calcul de la valeur cible que nous avons découvert récemment, d’effectuer une série de simulations pour déterminer la valeur à donner à une ou plusieurs cellules pour arriver à un résultat.

En outre, la force du solveur par rapport à la valeur cible est que nous allons pouvoir définir des contraintes à appliquer sur l’analyse, ce qui permet de mettre en place des simulations bien plus complexes.

Avant même de pouvoir utiliser le solveur, il convient de l’activer dans Excel.

En effet, il s’agit d’une fonctionnalité bien présente dans Excel, mais celle-ci est cachée, et disponible sous la forme d’un complément Excel.

Pour l’activer, nous devons nous rendre dans le menu Fichier > Options :

Excel formation - solveur excel - 01

Puis dans la fenêtre des options, choisir la rubrique « Compléments », puis tout en bas dans le menu Gérer, choisir l’options « Compléments Excel » et cliquer sur le bouton [Atteindre] :

Excel formation - solveur excel - 02

Dans la fenêtre suivante, nous pouvons activer l’option « Complément Solveur » et valider en appuyant sur le bouton [OK].

Excel formation - solveur excel - 03

Le solveur est maintenant activé, et nous le retrouverons dans menu « Données », tout à droite :

Excel formation - solveur excel - 04

 

2. Cas d’étude

Pour illustrer le fonctionnement du Solveur, nous allons utiliser l’exemple suivant :

Excel formation - solveur excel - 05

Nous retrouvons ici l’analyse de la rentabilité de la production d’une entreprise qui vend deux types de produits : un produit A vendu 167€ et un produit B vendu 268€.

Chacun de ces produits nécessite de passer en atelier et évidemment une matière première :

  • Le produit A nécessite 14 minutes d’atelier et 7kg de matière
  • Le produit B nécessite 32 minutes d’atelier et 4kg de matière

En outre, l’atelier peut fonctionner 6h par jours, pour un total de 7200 minutes par mois pour un coût de fonctionnement global de 40€ de l’heure.

Quant à la matière, le stock auquel l’entreprise peut prétendre est de 1500kg, dont le coût d’achat est estimé à 14€/kg.

Pour finir, l’entreprise est déjà engagée à livrer 100 unités de produits A.

L’objectif va donc être de déterminer le nombre de produits A et B à produire pour maximiser sa marge, en supposant que tous les produits sortis de l’atelier seront immédiatement vendus.

Il est évidemment possible de déterminer les nombres de produits A et B manuellement, mais nous préfèrerons laisser Excel réaliser ce calcul pour nous en utilisant le solveur.

Dans le tableau d’analyse, nous retrouvons pour chaque produit une cellule avec la quantité à produire :

Excel formation - solveur excel - 06

Ces quantités sont ici définies à une unique unité, mais nous allons souhaiter définir les valeurs qui permettent d’optimiser la marge.

À partir du prix de vente unitaire, nous allons retrouver le CA TTC correspondant, ainsi que le CA HT compte tenu d’un taux de 20% :

Excel formation - solveur excel - 07

Les coûts des matières et de l’atelier sont ensuite calculés à partir des temps d’occupations et de matières consommées :

Excel formation - solveur excel - 08

Ce qui permet de déterminer la marge, laquelle est égale au CA HT duquel les coûts sont retranchés :

Excel formation - solveur excel - 09

 

3. Utiliser le solveur

Nous pouvons maintenant lancer le solveur :

Excel formation - solveur excel - 10

La première chose à faire va être de sélectionner la cellule dans laquelle se trouve se trouve l’élément que nous allons souhaiter optimiser en l’occurrence, la marge :

Excel formation - solveur excel - 11

Dans la zone suivante nous pouvons spécifier si nous souhaitons :

  • Maximiser le résultat de cette cellule,
  • Le minimiser,
  • Ou atteindre une valeur donnée (comme pour la valeur cible)

Excel formation - solveur excel - 12

Ensuite, nous allons sélectionner les cellules variables que le solveur va pouvoir modifier pour optimiser la marge (les cellules dans lesquelles se trouvent les quantités) :

Excel formation - solveur excel - 13

Nous allons maintenant saisir les contraintes qui vont permettre de guider le solveur dans sa recherche d’optimisation en cliquant sur le bouton [Ajouter] :

  • Tout d’abord, vouloir que les cellules à définir ne puissent avoir que des valeurs entières (nous ne pouvons produire que des produits entiers). Nous sélectionnons donc la cellule C21 et dans le menu déroulant, nous choisissons « ent », pour « entier » :

Excel formation - solveur excel - 14

  • Ensuite, nous appuyons sur le bouton [Ajouter] pour insérer une autre contrainte,
  • Nous répétons l’opération avec la cellule D21 :

Excel formation - solveur excel - 15

  • Puis, nous fixons le nombre de minutes maximum d’occupation de l’atelier à 7200 au maximum (« F31 <= 7200 ») :

Excel formation - solveur excel - 16

  • Et que le poids des matières utilisées ne peut dépasser 1500kg (« F32 <= 1500 ») :

Excel formation - solveur excel - 17

  • Et enfin, nous allons fixer le nombre minimal de produits A vendus à 100 unités (« C21 >= 100 »):

Excel formation - solveur excel - 18

Lorsque la dernière contrainte est enregistrée, nous pouvons appuyer sur le bouton [OK] pour la valider et revenir sur la fenêtre précédente, dans laquelle nous retrouverons chacune d’entre elles :

Excel formation - solveur excel - 19

Pour finir, nous choisissons le type de résolution à utiliser :

Excel formation - solveur excel - 20

Il s’agit ici d’une résolution linéaire, en utilisant l’algorithme de résolution simplexe (SIMPLEX PL).

Nous pouvons maintenant demander à Excel de résoudre notre problématique en appuyant sur le bouton [Résoudre] :

Excel formation - solveur excel - 21

Le solveur nous présente alors le résultat de son analyse : pour générer une marge maximale, définie à 46k€, l’entreprise doit produire et vendre 114 articles A et 175 articles B.

Nous pouvons ici :

  • Valider la modification proposée en sélectionnant l’option « Conserver la solution du solveur »,
  • Rétablir les données d’origine en sélectionnant l’option « Rétablir les valeurs d’origine »,
  • Choisir de revenir sur la fenêtre de paramétrage du solveur pour le modifier, en cochant l’option « Retourner dans la boite de dialogue Paramètres du solveur »,
  • Afficher un « Rapport du plan » après validation (dans ce cas il faut sélectionner le rapport dans la fenêtre au-dessus)
  • Enregistrer le scénario pour pouvoir l’appeler par la suite depuis la fenêtre

Ici, nous choisissons de conserver la solution, et d’afficher le rapport de plan, puis nous appuyons sur la touche [OK] :

Excel formation - solveur excel - 22

Nous retrouvons le détail des calculs dans un nouvel onglet :

Excel formation - solveur excel - 23

 

 

 



Articles qui pourraient vous intéresser

Comment utiliser le solveur d’Excel ?
Comment imprimer un document Excel ?
Comment utiliser la valeur cible d’Excel ?
Les bases du copier-coller sur Excel
Comment annuler des bêtises sur Excel
Utiliser les styles de cellules d’Excel pour gagner du temps
Comment masquer ou afficher des cellules dans Excel – Apprendre Excel pour les débutants
Comment mettre en forme un tableau Excel
Les 9 façons de sélectionner des cellules dans Excel – Apprendre Excel pour les débutants
L’analyse rapide des données – Apprendre Excel pour les débutants
Apprendre à saisir une formule dans Excel – Apprendre Excel pour les débutants
Les formats de fichiers gérés par Excel (xlsx, xlsm, xls, pdf, csv, …) – Apprendre Excel pour les débutants
Guide « 83 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchageant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2021 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.