Solver : l’optimiseur mathématique intégré à Excel
Le complément Solver d’Excel résout des problèmes d’optimisation : minimiser un coût ou maximiser un profit, sous contraintes multiples. Pour un logisticien ivoirien, il permet de planifier les tournées, dimensionner le stock, allouer les équipes, avec une précision mathématique inaccessible à Excel seul.
Activer Solver
Fichier > Options > Compléments > Compléments Excel > Atteindre > cocher Complément Solver. Un bouton Solver apparaît sous Données > Analyse.
Cas pratique : 5 points de livraison à Abidjan
Un livreur part du dépôt Yopougon et doit desservir 5 clients : Cocody, Plateau, Marcory, Port-Bouët, Koumassi. Objectif : minimiser le kilométrage total.
Étape 1 : matrice des distances
Construisez un tableau 6×6 avec distances routières entre tous les points (utilisez Google Maps pour les valeurs réelles).
| Yopo | Coco | Plat | Marc | PB | Kou | |
|---|---|---|---|---|---|---|
| Yopo | 0 | 18 | 14 | 22 | 28 | 24 |
| Coco | 18 | 0 | 8 | 12 | 20 | 16 |
Étape 2 : variables de décision
Créez une matrice X de 6×6 où X(i,j) = 1 si le livreur va de i à j, 0 sinon. Ce sont les variables que Solver ajustera.
Étape 3 : fonction objectif
=SOMMEPROD(MatriceDistances; MatriceX)C’est la somme des distances pour tous les segments empruntés. À minimiser.
Étape 4 : contraintes
- Chaque ville est visitée exactement une fois : somme par ligne = 1 et somme par colonne = 1
- Les X(i,j) sont binaires (0 ou 1)
- Pas de sous-tours (MTZ ou approche manuelle via contraintes auxiliaires)
Étape 5 : lancer Solver
Données > Solver. Cellule cible : fonction objectif, Minimiser. Cellules variables : la matrice X. Ajoutez les contraintes. Méthode : Evolutionary (GRG ne supporte pas les contraintes binaires efficacement). Cliquez Résoudre.
Après 30-60 secondes, Solver propose un itinéraire optimal : par exemple Yopo > Plateau > Cocody > Marcory > PB > Koumassi > Yopo, pour 82 km au total contre 104 km pour une tournée intuitive. Gain : 21 pour cent sur le carburant et le temps.
Cas pratique bonus : dimensionnement stock
Gérant d’un supermarché, vous voulez déterminer les quantités à commander pour 50 références en respectant un budget mensuel et des contraintes de linéaire.
- Variables : quantité commandée par référence
- Objectif : maximiser la marge anticipée
- Contraintes : budget total, place disponible, quantité minimale par catégorie stratégique
Solver GRG (non linéaire) est adapté pour ce type de problème continu.
Solver multi-objectif
Excel ne gère pas nativement plusieurs objectifs simultanés. Astuce : pondérer les objectifs dans une fonction unique. Par exemple optimiser 0,7 × profit – 0,3 × risque pour équilibrer rentabilité et prudence.
Limites et alternatives
Solver est limité à 200 variables et 100 contraintes en version standard. Pour des problèmes plus grands : Solver Premium (payant), ou passage à Python avec PuLP ou Pyomo. OR-Tools de Google gère les problèmes de tournée (VRP) à grande échelle gratuitement.
Conclusion
Solver transforme Excel en outil d’aide à la décision quantitative. Pour un logisticien, un acheteur ou un financier, c’est un investissement d’apprentissage de quelques heures pour un bénéfice opérationnel durable.