SOLVEUR EXCEL: Maîtrisez-le en UNE HEURE!

Le Solveur Excel, souvent éclipsé par les Tableaux Croisés Dynamiques, est le trésor caché pour les énigmes numériques. Cet outil robuste vous permet de poser à Excel des défis de calcul pour trouver le meilleur chemin vers vos objectifs de données. Si vous avez déjà maîtrisé les "TCD" et cherchez à conquérir de nouveaux sommets analytiques, le Solveur est votre prochaine conquête.

Dans ce tutoriel, nous irons droit au but, pour vous permettre de maîtriser le Solveur au bout d'une seule heure. Je vous guiderai à travers les étapes pour activer, configurer et utiliser le Solveur. Vous apprendrez à l'appliquer à des cas concrets, simplifiant des problèmes qui semblent complexes au premier abord. Que vous soyez novice ou utilisateur avancé, préparez-vous à débloquer le potentiel de résolution de problèmes d'Excel avec aisance et confiance.

1) Comment activer le Solveur dans Excel

Avant de plonger dans les mystères et les merveilles du Solveur, vous devez d'abord l'inviter à la fête. Comme un bon vin caché dans la cave, le Solveur attend dans les add-ins d'Excel. Il s'agit d'un Add-in développé par Frontline Systems. Voici comment le réveiller:

1. Ouvrez Excel et créez un nouveau document ou ouvrez-en un existant où vous souhaitez utiliser le Solveur.
2. Cliquez sur l'onglet "Fichier" dans le coin supérieur gauche de l'écran.
3. Dans le menu latéral, sélectionnez "Options". Une fenêtre s'ouvrira, révélant une multitude de paramètres pour personnaliser votre expérience Excel.
4. Dans la fenêtre des Options, allez à "Compléments".
5. En bas de la fenêtre des Compléments, vous verrez un menu déroulant. Sélectionnez "Compléments Excel" et cliquez sur "Atteindre…".
6. Une liste de compléments disponibles apparaîtra. Cochez la case à côté de "Solveur" et cliquez sur "OK".
7. Excel va peut-être prendre un moment pour réfléchir – après tout, vous venez de lui demander de charger un outil puissant. Une fois chargé, le Solveur apparaîtra dans l'onglet "Données" sous la forme d'un nouveau groupe de commandes.

Félicitations, vous avez débloqué le Solveur!

Maintenant que le Solveur est activé, vous êtes prêt à résoudre des problèmes complexes qui nécessitent plus que de simples formules. Dans la prochaine section, nous allons explorer comment utiliser le Solveur avec un exemple concret.

2) Utilisation de base du Solveur Excel

Nous allons maintenant examiner l'interface du Solveur et je vais vous montrer sur en Exemple comment cela fonctionne. Quand vous aurez compris les principes de l'exemple qui suit, le plus dur sera fait!

Découverte de l'interface du Solveur

Avec le Solveur activé, il est temps de se familiariser avec son interface. Voici comment procéder pour une première utilisation :

1. Lancez le Solveur en cliquant sur son icône dans l'onglet "Données". Une fenêtre s'ouvre, vous présentant les champs à remplir pour configurer votre problème.
2. Définissez l'objectif : Choisissez la cellule qui contient la valeur que vous souhaitez optimiser, que ce soit pour maximiser un profit, minimiser un coût ou atteindre une certaine valeur.
3. Sélectionnez 'Max', 'Min' ou 'Valeur' pour spécifier si vous souhaitez maximiser, minimiser ou atteindre une valeur spécifique dans la cellule d'objectif.
4. Déterminez les cellules variables : Indiquez les cellules qui peuvent être modifiées pour atteindre l'objectif. Ce sont vos variables de décision.
5. Ajoutez des contraintes : Cliquez sur "Ajouter" pour définir les limites dans lesquelles les cellules variables peuvent varier. Par exemple, vous pouvez spécifier que certaines cellules doivent être supérieures ou inférieures à une valeur donnée, ou égales à un certain nombre.

Comment utiliser le Solveur Excel – Explication sur un exemple

Imaginons un exemple: Une petite entreprise de meubles fabrique des tables, des chaises et des étagères. L'entreprise souhaite maximiser son profit mensuel, en tenant compte du temps de production disponible et du coût des matériaux pour chaque article. Nous allons donc utiliser le Solveur Excel pour déterminer combien d'unités produites de chaque produit mèneront au profit maximum.

Cet exemple est disponible pour téléchargement ci-dessous.

Voici les données de départ:

Produit	Profit/unité	Temps/unité (h)	Coût mat./unité (€)	Demande	Temps de production (h)	Budget mat. (€)	Unités à produire
Table	25 €	3	10 €	100	0,00	0 €	???
Chaise	15 €	2	5 €	200	0,00	0 €	???
Étagère	30 €	4	15 €	50	0,00	0 €	???

Le budget de matériel total: maximum 2.000 €

Le temps de travail total: maximum 600 heures

Explication des différentes colonnes:

• Nom de produit (A)
• Bénéfice par unité vendue (B)
• Le temps de travail nécessaire à fabriquer une unité (C)
• Coût des matières premières pour produire une unité (D)
• Demande – le nombre d'unités qu'il est possible d'écouler (E)
• Temps de production (total) – le temps total de travail pour le produit. Formule = C1*H1 . (F)
• Budget pour les matières premières pour la production du produit. Formule = D1*H1 (G)
• Unités à produire pour chaque produit. Valeurs à remplir par Solveur. (H)

Il faut également prévoir:

• Une cellule "Budget maximum" et "Temps de travail maximum" avec les valeurs de 2.000€ et 600 h car nous en aurons besoin dans le Solveur.
• Une cellule "Budget utilisé" qui contient la formule =SOMME(G2:G4) (résultat de départ = 0 €)
• Cellule "Temps utilisé" avec la formule =SOMME(F2:F4) (résultat de départ = 0)
• Cellule avec le "Profit Total". Formule utilisée: =SOMMEPROD(B2:B4;H2:H4). C'est cette cellule que nous allons tenter de maximiser.

Voici donc le tableau dans une Feuille Excel.

Remplissage des champs du Solveur Excel

Voyons maintenant comment nous allons remplir le Solveur pour obtenir le bénéfice maximal.

Voici les explications des différentes valeurs:

• Objectif à définir: la valeur $B$5 désigne la cellule à optimiser. Dans notre cas la cellule avec le "Profit Total"
• À: nous allons choisir Max car notre but est de maximiser la valeur du bénéfice
• Cellules variables: ce sot les cellules que le Solveur peut modifier afin d'obtenir le meilleur résultat. Dans notre exemple, il s'agit des cellules de la colonne H / "Unités à produire" ($H$2:$H$4)
• Contraintes: il s'agit des différentes contraintes dont Solveur doit tenir compte dans son calcul. Dans notre cas, il s'agit des contraintes suivantes:
  • Temps total du travail doit être plus petit ou égal au "Temps de travail maximal" (ici 600 heures). Contrainte dans Solveur: $B$10<=$B$7
  • Le Coût maximal des matières premières ne peut pas dépasser le Budget total (ici 2.000 €). Contrainte $B$11<=$B$8
  • Pour chaque produit, le nombre des unités produites (proposition du Solveur) ne peut pas dépasser la Demande (on ne veut pas fabriquer plus que ce que l'on peut écouler). La Contrainte, comme elle est la même pour les trois produits, est en une seule ligne pour les 3 produits: $H$2:$H$4 <= $E$2:$E$4
• Type de résolution: Nous allons choisir la méthode "Simplex PL" (voir la section n°5 pour les différentes méthodes)
• Résolution: cliquez sur le bouton "Résoudre"

Résultat proposé

Tout d'abord, le Solveur Excel nous informe qu'il a bien trouvé une solution qui remplit les conditions (il est possible d'avoir une série des conditions qui s'excluent les une des autres et il est donc impossible d'obtenir un résultat).

Choisissez de "Conserver la solution du solveur" et confirmez en cliquant sur "OK". Solveur va alors remplir les valeurs dans votre Feuille.

Et maintenant, nous pouvons observer le résultat trouvé par le Solveur. N'oubliez pas: Solveur a rempli les valeurs H2, H3 et H4 – comme nous lui avons demandé (les "Cellules variables" dans l'interface du Solveur). Il propose donc de fabriquer 100 tables, 50 chaises et 50 étagères. Ce qui nous donnera le bénéfice total de 4.750€ (cellule B5).

Comme vous pouvez constater, toutes les conditions (contraintes) sont remplies.

Et le MEILLEUR POUR LA FIN

Et vous savez en quoi cet outil est particulièrement utile? Pour "refaire" des simulations. Vous pouvez tester autant de scénarios que vous souhaitez.

Imaginez que, dans notre exemple, vous vous posez les questions suivantes?

• Et quel serait le bénéfice maximal si l'on pouvait travailler 700 heures et pas seulement 600?
• Et que se passerait-il si on arrivait à fabriquer une table en 3h30 plutôt qu'en 4 heures?
• Que ferait le bénéfice maximal si on arrivait à produire une étagère avec 9 € de matières premières plutôt qu'avec 15 €?

Toutes ces questions, ainsi que toutes les autres, vous pouvez les tester! Tout comme leur combinaisons!

Et vous ne devez même pas modifier les données dans le Solveur. Il retient votre "scénario" dans sa mémoire tant que vous ne le modifiez pas. Il vous suffit donc de modifier les chiffres dans votre tableau et relancer la Résolution dans solveur. En quelques minutes, vous aurez une image parfaite des possibilités qui s'offrent à vous pour maximiser le profit!

Exemple: Si on arrive à gagner 20 € sur une chaise plutôt que 15 € et si en même temps, le temps maximum de travail peut passer à 700 heures, le résultat sera le suivant…

3) Exemples pratiques d'utilisation du Solveur Excel

Le Solveur d'Excel est un outil polyvalent qui peut être utilisé dans une variété de scénarios pour aider à la prise de décision basée sur des données. Voici quelques exemples pratiques qui illustrent la diversité de ses applications.

Exemple Solveur n°1: Planification de repas équilibrés

Supposons que vous soyez un nutritionniste cherchant à planifier un menu hebdomadaire pour un client, en respectant un apport calorique et nutritionnel spécifique.

• Objectif: Minimiser le coût total du menu tout en atteignant les objectifs nutritionnels.
• Variables: Quantités de chaque aliment à inclure dans le menu.
• Contraintes: Apports caloriques et nutritionnels (protéines, glucides, lipides, vitamines, etc.).

Exemple Solveur n°2: Gestion de portefeuille d'investissement

En tant qu'investisseur, vous souhaitez répartir votre capital entre différentes actions pour maximiser votre retour sur investissement tout en minimisant le risque.

• Objectif: Maximiser le retour sur investissement attendu pour un niveau de risque donné.
• Variables: Pourcentage du capital investi dans chaque action.
• Contraintes: Limites de risque, exigences de diversification, et liquidité minimale.

Exemple Solveur n°3: Optimisation de la production

Un fabricant veut déterminer la quantité optimale de produits à fabriquer pour maximiser les profits en tenant compte des contraintes de coût et de capacité de production.

• Objectif: Maximiser les profits.
• Variables: Nombre d'unités de chaque produit à produire.
• Contraintes: Capacité de production, coûts de main-d'œuvre et de matériaux, demande du marché.

Exemple Solveur n°4: Organisation d'événements

Organiser un événement avec un budget fixe, en cherchant à maximiser la satisfaction des participants.

• Objectif: Maximiser la satisfaction des participants.
• Variables: Budget alloué à chaque aspect de l'événement (nourriture, divertissement, location de salle).
• Contraintes: Budget total, nombre minimum de participants, exigences de sécurité.

Exemple Solveur n°5: Gestion du temps

Un freelance cherche à optimiser son emploi du temps pour équilibrer le travail, l'apprentissage et la vie personnelle.

• Objectif: Maximiser le revenu tout en allouant du temps pour l'apprentissage et la détente.
• Variables: Heures consacrées à chaque activité par semaine.
• Contraintes: Heures de travail disponibles, objectifs d'apprentissage, engagement social et familial.

Chacun de ces exemples montre comment le Solveur peut être adapté à des situations variées, allant de la vie quotidienne à des décisions d'affaires complexes. En utilisant le Solveur, vous pouvez explorer différentes combinaisons et configurations pour trouver la solution optimale à votre problème spécifique.

Astuce Pro : Lorsque vous configurez vos problèmes dans le Solveur, pensez à toutes les variables et contraintes possibles pour obtenir les résultats les plus précis.

4) Solveur Excel: 10 Conseils pour le Résultat Optimal

L'utilisation du Solveur peut parfois être entravée par des problèmes techniques ou des erreurs de modélisation. Cette section vise à fournir des conseils pratiques pour résoudre les problèmes courants et optimiser l'utilisation du Solveur.

Conseil 1: Vérifier les Données d'Entrée

Avant de lancer le Solveur, assurez-vous que toutes vos données d'entrée sont correctes. Les erreurs de saisie ou les références de cellules incorrectes peuvent conduire à des résultats inattendus ou à l'impossibilité de trouver une solution.

Conseil 2: Simplifier le Modèle

Si le Solveur ne parvient pas à trouver une solution, envisagez de simplifier votre modèle. Réduisez le nombre de variables ou de contraintes pour voir si une solution plus simple peut être trouvée, puis ajoutez progressivement de la complexité.

Conseil 3: Utiliser des Valeurs de Départ Réalistes

Le Solveur fonctionne mieux avec des valeurs de départ qui sont proches de la solution optimale. Si vous avez une idée de ce que pourrait être une bonne solution, entrez ces valeurs avant de lancer le Solveur.

Conseil 4: Ajuster les Paramètres du Solveur

Dans les options du Solveur, vous pouvez ajuster les paramètres tels que la tolérance, le temps maximum de calcul, et les critères d'arrêt. Ces ajustements peuvent aider à trouver une solution plus rapidement ou à surmonter des problèmes où le Solveur est bloqué.

Conseil 5: Comprendre les Messages d'Erreur

Lorsque le Solveur ne trouve pas de solution, il fournit des messages d'erreur. Prenez le temps de comprendre ce que ces messages signifient – ils peuvent souvent indiquer où se trouve le problème dans votre modèle.

Conseil 6: Contrôler la Scalabilité

Pour les grands modèles avec de nombreuses variables et contraintes, le Solveur peut prendre beaucoup de temps pour trouver une solution ou échouer à cause de la complexité. Assurez-vous que votre ordinateur a suffisamment de ressources pour gérer la taille de votre modèle.

Conseil 7: Utiliser des Contraintes Non-linéaires avec Précaution

Les modèles non linéaires sont plus difficiles à résoudre que les modèles linéaires. Si vous utilisez des contraintes non linéaires, assurez-vous de bien comprendre leur impact sur le modèle et considérez l'utilisation de la méthode GRG Nonlinéaire pour de meilleurs résultats.

Conseil 8: Documenter le Modèle

Gardez une trace écrite de la logique de votre modèle, des variables utilisées, et des contraintes appliquées. Cela rendra le dépannage beaucoup plus facile et aidera à communiquer votre modèle à d'autres.

Conseil 9: Tester avec des Données Diverses

Testez votre modèle avec différentes séries de données pour vous assurer qu'il est robuste et qu'il fonctionne comme prévu dans divers scénarios.

Conseil 10: Se Former et Chercher de l'Aide

Si vous rencontrez des difficultés persistantes avec le Solveur, envisagez de suivre une formation pour approfondir votre compréhension de l'outil ou de demander de l'aide à la communauté Excel ou à un professionnel.

5) Niveau Expert: Techniques Avancées Décryptées

Si vous souhaitez approfondir votre maîtrise, voici quelques informations supplémentaires…

Conclusion de ce tutoriel

Le Solveur d'Excel est un outil puissant qui ouvre un monde de possibilités pour l'analyse de données et la prise de décision. Que vous cherchiez à optimiser un budget personnel, à planifier des repas nutritionnels, à gérer un portefeuille d'investissements, ou à résoudre des problèmes de production complexes, le Solveur peut vous aider à trouver la meilleure solution possible en fonction de vos contraintes spécifiques.

En maîtrisant le Solveur, vous ajoutez une compétence précieuse à votre arsenal Excel, vous permettant de prendre des décisions basées sur des analyses robustes et des modèles optimisés. Cependant, comme tout outil, le Solveur a ses limites et ses complexités. En suivant les conseils de dépannage et en approfondissant vos connaissances sur ses fonctionnalités, vous serez mieux équipé pour surmonter les défis et tirer le meilleur parti de cet outil.

J'espère que ce tutoriel vous a fourni une base solide pour commencer à utiliser le Solveur et a éveillé votre curiosité pour explorer ses applications plus avancées. N'oubliez pas que la pratique est essentielle pour maîtriser le Solveur, alors commencez par des problèmes simples et augmentez progressivement la complexité à mesure que vous gagnez en confiance et en compétence.

Enfin, gardez à l'esprit que l'apprentissage est un processus continu. Avec chaque nouveau problème que vous résolvez avec le Solveur, vous découvrirez de nouvelles façons d'améliorer vos modèles et d'affiner vos solutions. Alors, lancez-vous, expérimentez et profitez de la puissance du Solveur pour faire de meilleures analyses et prendre des décisions éclairées.

Bonne résolution de problèmes avec le Solveur d'Excel!

Pour aller plus loin en Excel…

Si vous avez apprécié ce tutoriel, je vous propose de découvrir d'autres articles intéressants sur Excel-Malin.com.

• Liste de toutes les fonctions disponibles dans Excel
• Excel Online Gratuit – pourquoi se priver!
• Les principales fonctions Excel en détail: SI, NB.SI, SOMME.SI, RECHERCHEV, RECHERCHEX,…
• Calculer la MOYENNE pondérée dans Excel
• Découvrez les Outils Excel Pratiques à télécharger gratuitement