Analyse de données - Utilisation du solveur dans Excel

Ceci est le cinquième et dernier article de la série en cinq parties sur l'analyse des données dans Excel. Dans cette section, je vais vous montrer comment utiliser Solver dans Excel.

Autres articles de cette série :

• Un tableau de données variables dans Excel.
• Tableau de données à deux variables dans Excel.
• Gestionnaire de scénarios dans Excel.
• Recherche d'objectif dans Excel.

Regarder la vidéo - Utilisation du solveur dans Excel

Solver dans Excel est un complément qui vous permet d'obtenir une solution optimale lorsqu'il existe de nombreuses variables et contraintes. Vous pouvez le considérer comme une version avancée de Goal Seek.

Comment trouver le complément de solveur dans Excel

Le complément Solveur est désactivé dans Excel par défaut. Voici les étapes pour l'activer :

Voici les étapes pour l'activer :

• Allez dans Fichier -> Options.
• Dans la boîte de dialogue Options Excel, sélectionnez Complément dans le volet gauche.
• Dans le volet de droite, en bas, sélectionnez Compléments Excel dans la liste déroulante et cliquez sur Aller…
• Dans la boîte de dialogue Compléments, vous verrez une liste des compléments disponibles. Sélectionnez Complément Solveur et cliquez sur OK.
• Cela activera le complément Solveur. Il sera désormais disponible dans l'onglet Données sous le groupe Analyse.

Utilisation du solveur dans Excel - Exemple

Solver vous donne le résultat souhaité lorsque vous mentionnez les variables dépendantes et les conditions/contraintes.

Par exemple, supposons que j'ai un ensemble de données comme indiqué ci-dessous.

Cet exemple contient des données de fabrication pour 3 widgets : quantité, prix par widget et bénéfice global.

Objectif: Pour obtenir le maximum de profit.

Si vous avez une idée sur la fabrication, vous savez que vous devez optimiser la production pour obtenir le meilleur rendement. Alors qu'en théorie, vous pouvez fabriquer des quantités illimitées du widget le plus rentable, il existe toujours de nombreuses contraintes sous lesquelles vous devez optimiser la production.

Contraintes:

Voici quelques contraintes que vous devez prendre en compte tout en essayant de maximiser le profit.

• Au moins 100 Quantité de Widget A doit être faite.
• Au moins 20 Quantité de Widget B doit être faite.
• Au moins 50 Quantité de Widget C doit être faite.
• Un total de 350 widgets doit être réalisé.

Il s'agit d'un problème d'optimisation de fabrication typique et vous pouvez facilement y répondre à l'aide de Solver dans Excel.

Étapes pour utiliser le solveur dans Excel

• Une fois que vous avez activé le complément du solveur (comme expliqué ci-dessus dans cet article), accédez à Données -> Analyse -> Solveur.
• Dans la boîte de dialogue Paramètres du solveur, utilisez les éléments suivants :
  1. Définir l'objectif : $D$5 (c'est la cellule qui a la valeur désirée - dans ce cas, c'est le profit global).
  2. À : Max (puisque nous voulons le maximum de profit).
  3. En changeant les cellules variables : $B$2:$B$4 (variables que l'on veut optimiser - dans ce cas, c'est la quantité).
  4. Soumis aux Contraintes :
     • Ici, vous devez spécifier les contraintes. Pour ajouter une contrainte, cliquez sur Ajouter. Dans la boîte de dialogue Ajouter une contrainte, spécifiez la référence de cellule, la condition et la valeur de contrainte (comme indiqué ci-dessous) :
     • Répétez ce processus pour toutes les contraintes.
  5. Sélectionnez une méthode de résolution : sélectionnez Simplex LP.
  6. Cliquez sur Résoudre
     • Si le solveur trouve une solution, cela ouvrira la boîte de dialogue Solver Result. Vous pouvez choisir de conserver la solution du solveur (que vous pouvez voir dans votre ensemble de données) ou choisir de revenir aux valeurs d'origine.
       • Vous pouvez également enregistrer ceci comme l'un des scénarios, qui peut être utilisé dans le gestionnaire de scénarios.
       • Parallèlement à cela, vous pouvez également choisir de créer des rapports : Réponse, Sensibilité et Limites. Il suffit de le sélectionner et de cliquer sur OK. Cela créera différents onglets avec des détails chacun pour Réponse, Sensibilité et Limites (si vous n'en sélectionnez qu'un ou deux, alors autant d'onglets sont créés).

Avec cet article, j'ai essayé de vous présenter Solver. Il y a beaucoup plus à faire, et si vous aimez les statistiques, je vous recommande d'aller en savoir plus à ce sujet. Voici quelques bons articles que j'ai pu trouver en ligne :

• Utilisation du solveur dans Excel - Aide MS.
• Un manuel sur l'utilisation de Solver dans Excel (avec des exemples)).

Essayez-le vous-même… Téléchargez le fichier