Comment automatiser le solveur Excel

Dans Comment tirer le meilleur parti d'Excel Solver, j'ai abordé les techniques permettant d'améliorer votre modèle et de renforcer votre analyse. L'un des sujets sur lesquels j'ai reçu le plus de questions est l'automatisation du solveur pour résoudre plusieurs valeurs.

Il y avait une brève explication dans l'article précédent, mais rien de dédié à la façon de le mettre en œuvre du début à la fin. Dans cet article, nous irons en profondeur du début à la fin en couvrant les concepts et les méthodes étape par étape. À la fin, vous serez en mesure d'automatiser n'importe lequel de vos modèles personnels et d'adapter votre analyse.

Comment allons-nous le faire

Nous tirerons parti de l'enregistreur de macros d'Excel pour gérer la majeure partie du code réel. L'outil de macro d'enregistrement permettra à Excel de convertir nos étapes manuelles en code VBA.

Nous identifierons ensuite les éléments clés du code à modifier. Ensuite, nous allons créer une boucle pour sélectionner et remplacer dynamiquement ces valeurs et exécuter à nouveau le solveur.

Ce sera semi-technique, mais grâce à ce style de développement, nous pouvons nous concentrer sur le processus et les concepts au lieu de nous concentrer sur la façon d'écrire du code.

Configuration de l'enregistreur de macros

La fonctionnalité d'enregistrement de macro se trouve dans l'onglet développeur du ruban. Ceci n'est pas disponible par défaut mais est facile à mettre en œuvre. Si vous accédez à vos options de ruban, vous verrez une case à cocher pour l'onglet développeur.

La capture d'écran ci-dessous est pour Mac mais Excel basé sur PC est similaire.

Si vous n'avez pas installé le module complémentaire du solveur, vous pouvez l'installer via le menu Add-ins. Le site de support Microsoft contient des instructions pour toutes les plates-formes, chargez le complément Solver dans Excel.

Enregistrement de la macro

Maintenant que la configuration est terminée, nous sommes prêts à enregistrer ! Aménagez votre espace de travail de manière appropriée avec toutes vos formules et références. Vous voudrez tout mettre en place avant de créer le modèle de solveur.

Revenez à l'onglet développeur sur le ruban. Vous verrez un morceau de papier avec un point rouge. Cliquez sur ce bouton pour ouvrir l'enregistreur.

Donnez un nom à votre macro et remplissez la description si vous le souhaitez.

Une fois que vous avez appuyé sur OK, l'enregistreur commencera.

Suivez les étapes de configuration de votre solveur et résolvez-le. Une fois cette opération terminée, revenez à l'onglet développeur et arrêtez l'enregistrement. Ce sera au même endroit que le bouton que vous avez utilisé pour démarrer l'enregistrement.

Maintenant que nous avons le code de base, nous pouvons le nettoyer et le préparer pour les prochaines étapes.

Nettoyer le code

Nous pouvons maintenant modifier le code, supprimer toutes les parties inutiles et nous assurer que nous n'utilisons que les éléments essentiels de l'enregistrement de la macro.

Vous pouvez modifier le code via l'éditeur VBA d'Excel. Pour accéder à votre code enregistré, cliquez sur le bouton Macros.

Accédez à votre macro dans le nouveau menu et cliquez sur modifier.

Maintenant que notre éditeur de code est ouvert, nous pouvons commencer à faire des ajustements.

Supprimer le code inutile

S'il vous arrivait de cliquer sur un tas d'autres choses pendant l'enregistrement, vous pouvez les effacer maintenant et vous retrouver avec quelque chose de similaire à ci-dessous. S'il y a beaucoup de code supplémentaire et que vous craignez de le casser, vous pouvez toujours réenregistrer.

Configuration des références du solveur

Une dernière étape avant de continuer, allez dans Outils > Références et sélectionnez Solveur. Si vous ne le faites pas, le package ne se chargera pas dans la macro et vous obtiendrez une erreur.

Vous pouvez maintenant modifier vos paramètres, ouvrir le menu des macros, sélectionner votre macro, appuyer sur Exécuter et les résultats seront mis à jour.

Alignons le code sur notre modèle de solveur. Vous verrez comment il s'aligne. La langue est différente, mais vous pouvez reconstituer ce que tout cela signifie.

Ensuite, nous allons isoler les pièces que nous devons modifier et terminer notre automatisation.

Rincer et répéter - construire la boucle

Maintenant que nous avons un solveur fonctionnel. Nous devons répéter cela plusieurs fois. Mettons un exemple ensemble et cimentons le plan.

Nous avons commencé avec un budget de 1 000 $ dans le premier modèle, mais nous voulons faire la même chose pour des incréments de 100 $ jusqu'à 2 500 $.

Plongeons-nous ! Nous avons une liste de valeurs dans la colonne B et notre sortie dans la colonne C. Si nous écrivions le processus auquel il ressemblerait,

1. Définissez les dépenses totales sur la valeur de B10.
2. Résoudre le modèle.
3. Placez la sortie en C10.
4. Choisissez la valeur B suivante.
5. Définissez la sortie dans la ligne C.
6. Répétez les étapes 2 à 5 pour toutes les valeurs de B.

Commencer la boucle

Les boucles sont des constructions de codage qui répètent une action jusqu'à atteindre un point final logique. Une boucle sera utile ici pour répéter le même processus, en exécutant le solveur, sur chaque valeur jusqu'à ce que nous atteignions la fin de la valeur.

Pour cet exemple, nous allons définir le point final par le nombre de lignes. B10:B25 a 16 valeurs.

Nous allons commencer notre boucle et l'enrouler autour de notre code de solveur. Nous allons configurer une variable pour contenir le numéro de boucle (i) et parcourir chaque valeur, en résolvant la boucle à chaque fois.

Réinitialisation du solveur

Nous allons ajouter une autre condition au début de notre code. Puisque nous voulons réexécuter le solveur à chaque itération, nous le réinitialiserons à chaque boucle. Cela effacera les paramètres et démarrera un nouveau modèle.

Nous pouvons le faire via SolverReset .

Mise à jour des références de cellules

Nous résolvons maintenant seize fois mais devons mettre à jour nos références de cellules. Bien que nous ayons initialement fait référence à B10, nous devons descendre d'une cellule à chaque exécution pour mettre à jour les limites. Nous pouvons le faire via la fonction offset. Nous pouvons mettre à jour « $B$10 » pour référencer la cellule puis décaler de i lignes (0 à la première exécution, 1 ligne à la seconde, et ainsi de suite). Chaque fois que la boucle s'exécute, i augmente de un.

Collecter la sortie

La sortie changera à chaque fois, nous voudrons donc la sauvegarder. Nous pouvons suivre le même processus ci-dessus mais décalé de C10 et le définir égal à la valeur du modèle résolu. Puis la boucle recommence.

Nous appellerons C10 le décalage du nombre de lignes approprié, puis collerons la valeur de notre sortie.

Contourner les notifications du solveur

Evitons-nous un mal de tête et n'apprenons pas un instant par l'exemple. Vous vous souvenez lorsque vous avez exécuté le solveur et que ce menu est apparu pour vous demander si vous vouliez accepter la solution ? Cela apparaîtra à chaque fois si vous ne le désactivez pas. Imaginez si vous configurez une boucle avec des centaines de valeurs, vous devrez cliquer sur accepter à chaque fois !

Allez-y et ajoutez les lignes suivantes après votre code de solveur et ajoutez (TRUE) à SolverSolve. Cela vous imitera en acceptant les changements.

Nous avons maintenant le code fini !

Opérationnel

Nous pouvons maintenant exécuter le code en le sélectionnant dans la liste des macros et en l'exécutant. Si vous souhaitez utiliser votre classeur comme modèle, vous pouvez affecter la macro à un bouton pour en faciliter l'utilisation. Vous pouvez les trouver dans l'onglet développeur.

Vous pouvez également ajouter des graphiques de comparaison. Dans ce cas, nous voulons comparer le taux de croissance des dépenses par rapport à la croissance du volume de conversion. Cet exemple n'est pas intéressant mais des données réelles peuvent aider à révéler des points d'arrêt d'efficacité

Conclusion

Nous avons couvert un peu! Félicitations pour la mise en route de votre solveur automatisé. L'exemple que nous avons couvert était trop simpliste, mais vous pouvez prendre les mêmes concepts et les appliquer à n'importe quel modèle de solveur.

Cela peut faire gagner énormément de temps aux modèles que vous devez résoudre plusieurs fois et encourager les utilisateurs à expérimenter différents scénarios.

Date de publication originale : 30/07/2019