Excel Solver : optimiser les tournées de livraison à Abidjan

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.

Vue d’ensemble 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).

Vue d’ensemble 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.

Vue d’ensemble 3 : fonction objectif

=SOMMEPROD(MatriceDistances; MatriceX)

C’est la somme des distances pour tous les segments empruntés. À minimiser.

Vue d’ensemble 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)

Vue d’ensemble 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.

📚 Voir le guide complet : Maîtriser Excel pour PME — guide 2026 (Sénégal, Côte d’Ivoire, Mali, Burkina Faso, Niger, Togo, Bénin, Guinée, Mauritanie).

Voir aussi

• Excel : créer un tableau de bord financier dynamique
• Excel : cartographier les ventes Afrique avec cartes 3D
• Excel : simulation Monte Carlo pour prévisions commerciales

Pourquoi le Solveur Excel pour optimiser une tournée à Abidjan

Une PME de livraison de Cocody qui dessert 18 clients par jour entre Marcory, Treichville, Plateau et Yopougon perd souvent une heure d’embouteillage évitable parce que l’ordre de passage a été décidé à la main. Le Solveur Excel résout exactement ce problème : il classe les arrêts dans l’ordre qui minimise le kilométrage total, en respectant des contraintes de fenêtres horaires et de capacité de véhicule.

Ce tutoriel couvre le problème du voyageur de commerce (TSP) appliqué à 18 clients abidjanais, avec l’algorithme Évolutionnaire du Solveur. La méthode reste valable jusqu’à environ 50 points par tournée. Au-delà, il faut basculer vers OR-Tools de Google, mais 90 % des PME ouest-africaines tiennent largement sous ce seuil.

Étape 1 : activer le complément Solveur dans Excel

Le Solveur n’est pas activé par défaut. Sur Excel 2021 ou Microsoft 365, allez dans Fichier puis Options puis Compléments. En bas de la fenêtre, sélectionnez Compléments Excel dans la liste déroulante Gérer puis cliquez sur Atteindre. Cochez Complément Solver et validez.

Une fois activé, le bouton Solveur apparaît dans l’onglet Données, à droite de la barre. Si l’onglet ne s’actualise pas, fermez et rouvrez Excel. Sur Mac, le chemin est identique mais le menu s’appelle Outils puis Compléments Excel.

Étape 2 : préparer la matrice de distances entre points

Le Solveur a besoin d’une matrice 18×18 contenant la distance routière entre chaque paire de clients. Pour Abidjan, l’API Distance Matrix de Google donne des distances réalistes incluant les sens uniques et les ponts. Comptez environ 0,005 USD par paire, soit 1,53 USD pour la matrice complète, soit environ 920 FCFA.

=GOOGLEDISTANCE(B2;B3;"driving")

Pour ceux qui n’utilisent pas Google Sheets, on peut peupler la matrice via un script Python qui appelle l’API et écrit les valeurs dans matrice.xlsx. La distance retournée est en mètres ; on divise par 1000 pour obtenir des kilomètres lisibles dans le Solveur.

Étape 3 : construire la feuille de calcul du TSP

Sur la feuille, créez trois zones : la matrice de distances en B3:S20, l’ordre de passage en U3:U20 (cellules variables que le Solveur va modifier), et la formule de distance totale en U22. Le client 0 est l’entrepôt de départ et d’arrivée.

U22 : =SOMMEPROD(INDEX(B3:S20;U3:U20+1;DECALER(U3:U20;1;0;17;1)+1))

Cette formule calcule la somme des distances entre arrêts consécutifs. La fonction INDEX combinée à DECALER lit la matrice en suivant l’ordre proposé en colonne U. La cellule U22 est l’objectif que le Solveur va minimiser.

Étape 4 : paramétrer le Solveur en mode Évolutionnaire

Cliquez sur Données puis Solveur. Dans Objectif à définir, mettez U22 et cochez Min. Dans Cellules variables, sélectionnez U3:U20. Ajoutez une contrainte unique : U3:U20 = AllDifferent. Cette contrainte empêche le Solveur de visiter deux fois le même client.

Dans Sélectionner une méthode de résolution, choisissez Évolutionnaire. Le Simplex et le GRG nonlinéaire échouent sur ce problème combinatoire car la fonction objectif n’est ni linéaire ni dérivable. L’algorithme Évolutionnaire utilise une métaheuristique génétique adaptée aux permutations.

Étape 5 : régler les options évolutionnaires pour 18 points

Cliquez sur Options puis onglet Évolutionnaire. Réglez Taille de population sur 100, Taux de mutation sur 0,15, et Durée maximale sans amélioration sur 60 secondes. Pour 18 clients, le solveur trouve généralement la solution optimale en 30 à 90 secondes sur un Intel i5 récent.

Si vous avez un i3 ou un Atom, augmentez la durée maximale à 180 secondes. Le mode Évolutionnaire est CPU-intensif mais reste mono-thread, donc inutile d’investir dans un Ryzen 16 cœurs pour ce cas d’usage. Un laptop bureautique standard suffit largement.

Étape 6 : lancer la résolution et lire le résultat

Cliquer Résoudre.
Attendre 30 à 90 secondes.
Dans la boîte de dialogue, choisir Conserver la solution du solveur.

La colonne U contient désormais l’ordre optimal. Pour notre exemple type Cocody-Marcory-Treichville-Plateau-Yopougon, le Solveur réduit la distance totale de 87 km à 61 km, soit une économie de 30 % de carburant. À 760 FCFA le litre de gasoil et 8 L/100 km, cela représente 158 FCFA par tournée et environ 41 000 FCFA par an pour 5 tournées par semaine.

Étape 7 : ajouter des fenêtres horaires

Si certains clients n’acceptent les livraisons que sur créneaux (par exemple un restaurant qui n’ouvre qu’à 11h), il faut enrichir le modèle. Créez une colonne V avec l’heure d’arrivée prévue, calculée à partir de la vitesse moyenne d’Abidjan en heure pleine, qui tourne autour de 18 km/h selon les relevés Waze.

V3 : 7:00
V4 : =V3 + (INDEX(B3:S20;U3+1;U4+1)/18)/24 + 0:10
[recopier vers le bas]

Ajoutez ensuite des contraintes du type V8 >= 11:00 pour le client 5 qui n’ouvre qu’à 11h. Le Solveur va alors arbitrer entre minimisation du kilométrage et respect des fenêtres. Le résultat n’est plus optimal en distance pure mais devient réalisable opérationnellement.

Étape 8 : exporter la tournée pour Google Maps

Pour transmettre l’itinéraire au chauffeur, on génère une URL Google Maps avec les 18 waypoints dans l’ordre optimisé. Une formule de concaténation produit l’URL en une cellule.

="https://www.google.com/maps/dir/"&TEXTJOIN("/";VRAI;DECALER(C3;U3;0;1;1);...)

Le chauffeur reçoit l’URL par WhatsApp Business, ouvre Maps directement et lance la navigation. Le gain de temps réel sur Abidjan en heure de pointe est de 35 à 50 minutes par tournée selon notre suivi sur 4 PME accompagnées entre 2024 et 2026. Pour pousser plus loin l’analyse opérationnelle, voir notre analyse de rentabilité par produit et notre modèle Power Pivot pour PME.

Contraintes Dakar Almadies Sicap : modeliser la realite du terrain

Une livraison Dakar n’est pas un probleme TSP academique. La Corniche Ouest entre les Almadies et le Plateau peut prendre 12 minutes a 6h du matin et 75 minutes a 18h en semaine. Le boulevard du Centenaire entre Sicap et Yoff oscille entre 25 et 90 minutes selon la saison des pluies. Le solveur tournees doit donc integrer une matrice de temps de trajet variable par tranche horaire, pas une distance euclidienne.

La construction de la matrice se fait via l’API Distance Matrix de Google ou un OSRM auto-heberge. Pour 30 points de livraison, soit 30 par 30 = 900 paires, l’appel API coute environ 4,5 USD avec Google (forfait 1000 elements) ou zero avec OSRM auto-heberge. Le solveur OR-Tools de Google, libre et gratuit, accepte directement cette matrice et optimise une tournee qui visite tous les points, retourne au depot, et respecte les fenetres horaires de chaque client (par exemple « boutique fermee entre 13h et 15h »).

Les contraintes operationnelles s’ajoutent au TSP de base. Capacite du vehicule (35 colis max pour un Kangoo), volume cumule (les bonbonnes d’eau prennent de la place), poids cumule (la cargaison ne doit pas depasser le PTAC), pauses obligatoires (deux pauses de 15 minutes par tournee de 8h pour le chauffeur). OR-Tools modelise tout cela via les concepts Capacity Dimension, Time Dimension, Time Windows. La serie tournees Excel et OR-Tools donne le code Python complet pour 50 points avec contraintes capacite et fenetres.

Validation graphique : verifier que la solution a du sens metier

Un solveur peut produire une tournee mathematiquement optimale mais operationnellement absurde : zigzag entre Yoff et Almadies parce qu’une fenetre horaire l’impose, retour au depot au milieu de la journee parce que la capacite est atteinte. La validation graphique systematique evite ces pieges. La methode standard : exporter la tournee en GeoJSON, l’afficher sur Folium (Python) ou Mapbox, et la faire valider par le chef de tournee experimente avant deploiement.

Trois criteres de validation. Le premier est la coherence visuelle : la tournee doit former une boucle propre, sans aller-retours suspects. Le deuxieme est la duree totale : un solveur qui propose 9 heures pour 25 livraisons habituellement faites en 7 heures a probablement mal estime les temps de trajet. Le troisieme est la repartition de charge entre les vehicules : si vehicule 1 fait 12 livraisons et vehicule 2 en fait 4, la matrice de couts est mal ponderee.

L’iteration sur les parametres se fait en mesurant 5 jours reels et en comparant aux predictions du solveur. Sur une PME de Dakar qui livre 80 commandes par jour avec 3 vehicules, l’ecart initial entre prediction et realite etait de 28 % (le solveur sous-estimait les temps de stationnement). Apres calibration de la variable « temps moyen par livraison » a 8 minutes au lieu des 4 minutes par defaut, l’ecart est tombe sous 6 %.

Cas pratique : reduire de 22 % les kilometres parcourus en 3 mois

Une societe de distribution de produits frais qui livre 60 restaurants entre Dakar et la petite cote (Saly, Mbour, Toubab Dialaw) a deploye OR-Tools en remplacement d’une tournee historique optimisee a la main par le chef logistique depuis 8 ans. La tournee historique faisait 412 km par jour en moyenne pour 3 vehicules. La tournee optimisee par OR-Tools sur 3 mois de donnees calibrees fait 322 km par jour, soit 22 % d’economie de carburant et environ 145 000 FCFA par mois sur le poste essence.

Le gain operationnel additionnel est moins visible mais significatif : les chauffeurs rentrent en moyenne 45 minutes plus tot, ce qui reduit les heures supplementaires et ameliore la satisfaction. Le projet a ete amorti en 11 jours sur l’economie carburant pure. La cle du succes : ne pas remplacer le chef logistique, mais lui donner un outil qui calcule les options et le laisser arbitrer les exceptions (client VIP qui doit etre livre en premier malgre la geometrie defavorable, fournisseur qui exige une fenetre etroite).