Vous vous rappelez des problèmes mathématiques à l’école ? Avec Excel, plus besoin de se creuser les méninges, le tableur les résout pour vous.
Utilité et fonctionnement
Objectif
Le solveur d’Excel est un outil d’analyse de scénarios. Il fonctionne de manière analogue à la valeur cible, sauf qu’il va plus loin.
Il calcule des objectifs à atteindre en fonction de plusieurs variables, et non d’une seule pour la valeur cible. On y ajoute aussi la notion de contraintes.
Composantes
Afin d’effectuer vos simulations par le biais du solveur, vous avez besoin de définir les paramètres suivants :
L’objectif à définir représente la cellule qui doit atteindre une certaine valeur ou être optimisée. Elle contient généralement une formule et dépend d’une ou plusieurs cellules variables.
Les cellules variables (aussi appelées variables de décision) représentent les cellules dont la valeur sera modifiée par le solveur jusqu’à ce que la « cellule à définir » atteigne l’objectif défini et tant que les contraintes respectées.
Les contraintes sont des valeurs imposées afin de fixer certaines limites.
Pour résoudre un problème, le solveur simule plusieurs scénarios pour lesquels il utilise une diversité de valeurs pour les cellules variables en respectant les contraintes fixées dans le seul but d’atteindre d’objectif défini.
Le Solveur affiche alors la valeur des cellule variables la plus proche de l’objectif recherché. Vous pouvez également créer un rapport de synthèse contenant les caractéristiques du problème, ses valeurs initiales et finales.
Modes de résolution
Une dernière chose à appréhender et pas des moindres : le mode de résolution. Il s’agit de la manière à laquelle le solveur va effectuer ses simulations. Il en existe 3 :
- GRG non linéaire : problèmes non linéaires simples
- Simplex PL : problèmes linaires
- Évolutionnaire : problèmes complexes
Je le conçois, c’est pas simple à comprendre comme ça. Moi-même, j’ai eu du mal au début. Mais, vous pensez bien que je vais vous faciliter les choses.
| Linéaire | Non linéaire |
|---|---|
Formules de calcul pour l’objectif à définir et les contraintes de forme Ax + By Quelque soit le nombre x ou y, les variables A et B ne changent pas, elles sont constantes Exemple : si X représente une quantité, qu’il soit de 1 ou 500, la variable A qui représente le coût unitaire est toujours le même. | Formules de calcul pour l’objectif à définir et les contraintes :
|
Solveur vs valeur cible
Le plus simple est de faire une comparaison des caractéristiques avec la valeur cible :
| Caractéristiques | Valeur cible | Solveur |
|---|---|---|
Nombre de variables inconnues |
1 |
Plusieurs |
Type de résultat attendu |
valeur exacte |
valeur exacte / valeur minimum / valeur maximum |
Cellules variables de décision |
1 |
jusqu’à 200 |
Contraintes |
N/A |
jusqu’à 200 |
Installer le solveur
Le solveur n’est pas installé par défaut sur Excel.
Toutefois, vous avez de la chance : il s’agit d’un complément gratuit facile à installer.
Dans le ruban d’Excel, allez sur l’onglet Fichier puis cliquez sur Options (en bas de la fenêtre).
Sur la nouvelle fenêtre, Cliquez sur la rubrique Compléments puis sur Atteindre… en bas de la fenêtre.
Parmi les compléments activables, sélectionnez Complément Solveur puis cliquez sur OK
Accès au solveur
- Allez sur l’onglet Données du ruban Excel
- Tout à droite, cliquez sur Solveur
Exemples
Optimisation de marge : cas simple
Commençons par un exemple simple : recherche d’optimisation de marge sur une production.
- Objectif à définir : maximiser la marge
- Cellules variables : Quantités vendues, Prix de vente unitaire, coût d’achat unitaire
- Contraintes :
- quantités vendues doit être un nombre entier
- limite de production : 500
- coût achat unitaire : 2.50 € minimum
- prix vente unitaire : entre 3 et 4 €
Lancez le solveur Excel et remplissez les champs comme suit :
- Objectif à définir : $C$9
- Cellules variables : $C$2:$C$3;$C$6
Maintenant, passons aux contraintes. Pour en créer une, cliquez sur Ajouter.
1ère contrainte : quantités vendues doit être un nombre entier
se traduit par Référence de cellule : $C$2 et ent sur la 2e liste déroulante.
Cliquez sur Ajouter
2ème contrainte : limite de production = 500
Référence de cellule : $C$2
2e liste déroulante : <=
contrainte : 500
Cliquez sur Ajouter
3ème contrainte : coût achat unitaire > 2.50 €
Référence de cellule : $C$6
2e liste déroulante : >=
contrainte : 2.5
Cliquez sur Ajouter
4ème contrainte : prix vente unitaire = entre 3 et 4 €
Référence de cellule : $C$3
2e liste déroulante : >=
contrainte : 3
Cliquez sur Ajouter
Référence de cellule : $C$3
2e liste déroulante : <=
contrainte : 4
Cliquez sur OK (nous avons saisi toutes les contraintes)
Maintenant, il faut choisir le mode de résolution.
Question : y a-t-il une constante dans le calcul de l’objectif (marge) ?
Réponse : non, même si les quantités peuvent différer, le prix change aussi car c’est une autre variable. Il n’y a donc aucune linéarité dans ce problème.
De plus, ce dernier est simple. Donc, le mode de résolution adapté est GRG non linéaire.
Cliquez sur Résoudre
Une nouvelle fenêtre apparaît indiquant :
- si le solveur a trouvé une solution satisfaisante.
- Conserver la solution du solveur (il a rempli les cellules variables) ou Rétablir les valeurs d’origine (avant l’utilisation du solveur)
- Retourner dans la boîte de dialogue Paramètres du solveur. Si décoché, la fenêtre du solveur se fermera.
- Générer un rapport de plan. Si vous en voulez un, cochez la case et surtout sélectionnez le rapport parmi la liste proposée.
- Cliquez sur OK pour valider la simulation su solveur
- Annuler ferme la fenêtre et rétablit les valeurs des cellules avant utilisation du solveur
- Enregistre le scénario
Pour notre problème, la marge maximale atteignable est de 750 €. En tenant compte des contraintes, nous avons besoin de vendre 500 produits à 4 € que nous avons acheté 2.50 €.
Optimisation de production
Passons à un exemple plus développé. On le trouve souvent sur Internet ou dans des cours de comptabilité.
- Objectif à définir : maximiser la marge
- Cellules variables : Quantité produit A et Quantité produit B
- Contraintes :
- les quantités doivent être des nombres entiers
- L’entreprise doit vendre au moins 50 produits A et 100 produits B suite à un contrat de distribution avec une grande enseigne
- Produit A nécessite 3kg de matières premières
- Produit B nécessite 6kg de matières premières
- L’entreprise possède 1.5T de matières premières. Le prix au kg est de 20€.
- Produit A nécessite 1h d’atelier
- Produit B nécessite 1h30 d’atelier
- L’atelier fonctionne 15h par jour, 28 jours par mois soit 420h. Chaque heure coûte 50€.
Lancez le solveur Excel et remplissez les champs comme suit :
- Objectif à définir : $D$17
- Cellules variables : $B$9:$C$9;
1ère contrainte : les quantités doivent être des nombres entiers
Référence de cellule : $B$9
2e liste déroulante : ent
Cliquez sur Ajouter
Référence de cellule : $C$9
2e liste déroulante : ent
Cliquez sur Ajouter
2ème contrainte : L’entreprise doit vendre au moins 50 produits A et 100 produits B
Référence de cellule : $B$9
2e liste déroulante : >=
contrainte : 50
Cliquez sur Ajouter
Référence de cellule : $C$9
2e liste déroulante : >=
contrainte : 100
Cliquez sur Ajouter
3ème contrainte : L’entreprise possède 1.5T de matières premières
Référence de cellule : $D$14
2e liste déroulante : <=
contrainte : $E$14
Cliquez sur Ajouter
4ème contrainte : L’atelier fonctionne 15h par jour, 28 jours par mois soit 420h
Référence de cellule : $D$15
2e liste déroulante : <=
contrainte : $E$15
Cliquez sur OK (nous avons saisi toutes les contraintes)
Maintenant, le mode de résolution.
Question : y a-t-il une constante dans le calcul de l’objectif (marge) ?
Réponse : oui, même si les quantités peuvent différer, tous les prix unitaires sont fixes (prix matières, de l’atelier, prix de vente). Il y a donc une linéarité dans ce problème.
Donc, le mode de résolution adapté est Simplex PL.
Cliquez sur Résoudre
Afin de tirer un maximum de marge, soit 16300 €, il faut vendre 270 produits A et 100 produits B.
Les contraintes sont bien respectées :
- 50 produits A vendus : 270
- 100 produits B vendus : 100
- 1500 kg de matières : 1410 kg consommés
- 420h atelier : 420 h utilisées
Optimisation d'un budget cadeaux
Les anniversaires de mes enfants approchent. Férus de nouvelles technologies, je prévois de leur offrir pour chacun un cadeau. Pour cela, je leur ai demandé les prix des 4 types de cadeaux qu’ils voudraient.
Les temps étant durs pour les français, je dois satisfaire mes enfants tout en économisant un maximum.
Pour résoudre mon problème, je vais utiliser le solveur d’Excel mais d’une manière différentes des précédents exemples.
Pour cela, je duplique mon tableau en effaçant les prix. De plus, j’ajoute une colonne à droite et une ligne en bas. Enfin, j’ajoute une cellule séparée qui va me calculer le coût total de mes achats.
À quoi va bien servir ce nouveau tableau ?
Il va me servir à indiquer qui recevra tel cadeau. Les zones en jaune vont vérifier que chaque enfant aura un seul cadeau / chaque cadeau aura un seul bénéficiaire. Pour cela j’utilise la fonction SOMME pour chaque cellule en jaune.
Enfin, la cellule coût va calculer le coût. Pour cela, j’utilise la fonction SOMMEPROD pour faire la somme de tous les produits sélectionnés (2e tableau) multipliés par leur prix (1er tableau).
Allez, je vous aide avec la formule :
=SOMMEPROD($C$3:$F$6;$C$10:$F$13)
Maintenant, passons au solveur.
- Objectif à définir : c’est le coût soit $C$16. On veut qu’il soit le plus petit possible, donc choisir Min.
- Cellules variables : celles qui seront complétées par le solveur, il s’agit donc des cellules en orange soient $C$10:$F$13
Voyons maintenant les contraintes :
- chaque objet a un seul bénéficiaire. Donc chaque cellule de la dernière colonne doit être égal à 1
On obtient :
Référence de cellule : $G$10:$G$13
2ème liste : =
Contrainte : 1 - chaque bénéficiaire aura un seul cadeau. Donc chaque cellule de la dernière ligne soit être égal à 1
On obtient :
Référence de cellule : $C$14:$F$14
2ème liste : =
Contrainte : 1 - pour chaque cellule dans la zone orange, la réponse est soit VRAI soit FAUX (Exemple : Delphine a ou n’a pas de console comme cadeau).
Sur Excel, ça se traduit aussi par les valeurs 0 et 1. On appelle ça, le langage binaire.
ça se traduit par :
Référence de cellule : $C$10:$F$13
2ème liste : bin
Pour finir, le mode de résolution. Il n’y a aucune constante dans ce problème. Bien que peu habituel, il ne s’agit pas d’un problème réellement complexe. le mode adapté est GRG non linéaire.
Lancez le solveur.
Il trouve une solution :
- la console pour Delphine
- le smartphone pour Pierre
- le PC/tablette pour Dylan
- l’appareil photo pour Jessica
Tout ça pour un budget minimal de 1020 €.
