Le Solveur dans Excel
La valeur cible modifie une seule cellule pour atteindre un résultat. Le Solveur va plus loin : il fait varier plusieurs cellules à la fois pour maximiser ou minimiser un résultat, tout en respectant des contraintes (budget, capacité, nombres entiers…). C’est l’outil d’optimisation d’Excel : planning, mix de production, affectation de ressources.
ActiverFichier › Options › Compléments
LancerDonnées › Analyse › Solveur
Cellules variables200 maximum
Contraintes≤ · = · ≥ · ent · bin · dif
1
Activer le complément Solveur
Le Solveur est un complément livré avec Excel mais désactivé par défaut.
- Fichier › Options › Compléments.
- En bas, Gérer : Compléments Excel, puis Atteindre…
- Cochez Complément Solver et validez par OK.
- Le bouton Solveur apparaît à droite de l’onglet Données › Analyse.
Une seule activationLe complément reste actif pour tous les classeurs du poste. Sur un autre ordinateur, il faudra peut-être l’activer à nouveau.
Préparer le modèleAvant d’ouvrir le Solveur, la feuille doit contenir les variables (cellules de saisie), l’objectif (une formule) et le calcul de chaque contrainte (une formule par ressource).
Simulateur : planifier les sessions du trimestre
Combien de sessions Excel (2 jours, 900 € de marge) et Word (1 jour, 600 € de marge) programmer pour maximiser la marge, avec 20 jours de formateur, 14 créneaux de salle et une demande de 8 sessions Excel au plus ?
Paramètres du solveur
Objectif à définir$B$5 (Marge totale)
À
Cellules variables$B$2:$B$3
Contraintes
Résultats du solveur
2
Objectif, variables et contraintes
| Paramètre | Rôle | Dans l’exemple |
|---|---|---|
| Objectif à définir | Cellule contenant une formule, à rendre Max, Min ou égale à une Valeur | =B2*C2+B3*C3 (marges unitaires en C2:C3) à maximiser |
| Cellules variables | Les données que le Solveur a le droit de modifier (200 au maximum) | Sessions Excel (B2) et Word (B3) |
| Contraintes | Limites à respecter : <=, =, >=, ent (entier), bin (0 ou 1), dif (tous différents) | Jours formateur ≤ 20, salles ≤ 14, Excel ≤ 8 |
| Non négatives | Interdit les valeurs négatives pour les variables sans contrainte | On ne programme pas −3 sessions |
- Données › Analyse › Solveur. Dans Objectif à définir, cliquez sur la cellule résultat et choisissez Max.
- Dans Cellules variables, sélectionnez les cellules à faire varier (Ctrl + clic pour des plages séparées).
- Ajouter une contrainte : référence de cellule, opérateur, contrainte (valeur ou cellule). Recommencez pour chaque limite.
- Choisissez la méthode Simplex PL pour un modèle linéaire, puis Résoudre.
Contraintes dans des cellulesÉcrivez les limites dans la feuille (20 jours, 14 salles) et faites-y référence dans le Solveur : il suffit de modifier la cellule pour relancer avec une nouvelle hypothèse.
Nombres entiersSans contrainte
ent, le Solveur peut proposer 6,5 sessions. Ajoutez-la dès que la variable est un nombre de personnes, de sessions ou de produits.3
Résultats et rapports
À la fin du calcul, la boîte Résultats du solveur indique si une solution a été trouvée et propose de la garder ou de rétablir les valeurs d’origine.
| Élément | Utilité |
|---|---|
| Rapport des réponses | Valeurs initiales et finales de l’objectif et des variables ; pour chaque contrainte, Lié (ressource épuisée) ou Non lié et la marge restante. |
| Rapport de sensibilité | (Simplex PL) Coûts réduits et multiplicateurs de Lagrange : ce que rapporte une unité de ressource supplémentaire. |
| Rapport des limites | Valeurs minimales et maximales de chaque variable, les autres restant fixes. |
| Enregistrer le scénario | Mémorise la solution dans le gestionnaire de scénarios pour la comparer à d’autres hypothèses. |
Lire « Lié »Dans l’exemple, les jours formateur et les salles sont liés : ce sont eux qui bloquent. La demande Excel ne l’est pas (6 sessions sur 8 possibles) : l’augmenter ne changerait rien.
Enregistrer le modèleLes paramètres du Solveur sont mémorisés avec la feuille. Le bouton Charger/Enregistrer permet d’en stocker plusieurs dans des plages de cellules.
4
Méthodes de résolution et messages d’erreur
| Méthode | Quand l’utiliser |
|---|---|
| Simplex PL | Modèle linéaire (sommes et produits par des constantes) : rapide et solution optimale garantie. |
| GRG non linéaire | Formules non linéaires et régulières (produits de variables, puissances, VPM…) : solution optimale locale. |
| Évolutionnaire | Formules non régulières (SI, RECHERCHEV, ARRONDI, ABS…) : recherche par essais, plus lente, sans garantie. |
| Message | Cause probable |
|---|---|
| « Le Solveur n’a pas trouvé de solution réalisable. » | Contraintes contradictoires (au moins 16 sessions Word avec 14 salles). |
| « Les valeurs de la cellule objectif ne convergent pas. » | Rien ne limite l’objectif : une contrainte a été oubliée. |
| « Les conditions de linéarité requises par ce solveur PL ne sont pas satisfaites. » | Méthode Simplex PL sur un modèle non linéaire : passez en GRG non linéaire. |
Dans le simulateurEssayez les exemples « Contrainte impossible » et « Contrainte oubliée » pour voir les deux premiers messages, puis passez la marge Excel à 1 300 € : l’optimum change de sommet.
Vérifier le résultatLe Solveur ne connaît que les contraintes saisies. Relisez toujours la solution avec le bon sens métier avant de l’appliquer.
Exercice de synthèse : planning trimestriel
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Activez le complément Solveur puis ouvrez la feuille Planning.
- Maximisez la marge (B5) en modifiant B2:B3, avec les contraintes B8 ≤ C8, B9 ≤ C9, B10 ≤ C10, B2:B3 = entier. Réponse : 6 sessions Excel et 8 Word, marge 10 200 €.
- Générez le rapport des réponses : quelles ressources sont liées ?
- Passez la marge Excel à 1 300 € et relancez : 8 Excel et 4 Word, 12 800 €. Ajoutez ensuite « Word ≥ 16 » et lisez le message.