Solver est un complément gratuit depuis l'époque de Lotus 1-2-3
Excel n'était pas le premier programme de tableur. Lotus 1-2-3 n'était pas le premier programme de tableur. Le premier programme de tableur a été VisiCalc en 1979. Développé par Dan Bricklin et Bob Frankston, VisiCalc a été publié par Dan Fylstra. Aujourd'hui, Dan dirige Frontline Systems. Son entreprise a écrit le Solver utilisé dans Excel. Il a également développé toute une suite de logiciels d'analyse fonctionnant avec Excel.
Si vous avez Excel, vous avez Solver. Il n'est peut-être pas activé, mais vous l'avez. Pour activer Solver dans Excel, appuyez sur alt = "" + T suivi de I. Ajoutez une coche à côté de Solver.
Pour utiliser avec succès Solver, vous devez créer un modèle de feuille de calcul qui comporte trois éléments:
- Il doit y avoir une seule cellule Objectif. Il s'agit d'une cellule que vous souhaitez soit réduire, maximiser ou définir une valeur particulière.
- Il peut y avoir de nombreuses cellules d'entrée. C'est une amélioration fondamentale par rapport à Goal Seek, qui ne peut traiter qu'une seule cellule d'entrée.
- Il peut y avoir des contraintes.
Votre objectif est de définir les exigences de planification pour un parc d'attractions. Chaque employé travaillera cinq jours consécutifs, puis disposera de deux jours de congé. Il existe sept façons différentes de programmer quelqu'un pour cinq jours consécutifs et deux jours de congé. Ceux-ci sont affichés sous forme de texte au format A4: A10. Les cellules bleues dans B4: B10 sont les cellules d'entrée. C'est ici que vous spécifiez le nombre de personnes qui travaillent pour chaque horaire.
La cellule Objectif correspond à la masse salariale totale par semaine, indiquée en B17. Ce sont des mathématiques simples: le nombre total de personnes de B11 fois le salaire de 68 $ par personne et par jour. Vous demanderez à Solver de trouver un moyen de minimiser la masse salariale hebdomadaire.
La boîte rouge montre des valeurs qui ne changeront pas. C'est le nombre de personnes dont vous avez besoin pour travailler dans le parc chaque jour de la semaine. Vous avez besoin d'au moins 30 personnes les jours de fin de semaine chargés, mais aussi peu que 12 le lundi et le mardi. Les cellules orange utilisent SUMPRODUCT pour calculer le nombre de personnes qui seront programmées chaque jour en fonction des entrées dans les cellules bleues.
Les icônes de la ligne 15 indiquent si vous avez besoin de plus de personnes ou de moins de personnes ou si vous avez exactement le bon nombre de personnes.
Tout d'abord, j'ai essayé de résoudre ce problème sans Solver. J'y suis allé avec 4 employés chaque jour. C'était génial, mais je n'avais pas assez de monde dimanche. Alors, j'ai commencé à augmenter les horaires qui me donneraient plus d'employés du dimanche. J'ai fini avec quelque chose qui fonctionne: 38 employés et 2584 $ de masse salariale hebdomadaire.
Cliquez sur l'icône Solveur dans l'onglet Données. Dites au Solveur que vous essayez de régler la paie dans B17 au minimum. Les cellules d'entrée sont B4: B10.
Les contraintes entrent dans des catégories évidentes et pas si évidentes.
La première contrainte évidente est que D12: J12 doit être> = D14: J14.
Mais, si vous essayez d'exécuter Solver maintenant, vous obtiendrez des résultats bizarres où vous avez un nombre fractionnaire de personnes et peut-être un nombre négatif de personnes travaillant certains horaires.
Bien qu'il vous semble évident que vous ne pouvez pas embaucher 0,39 personne, vous devez ajouter des contraintes pour indiquer au Solveur que B4: B10 sont> = 0 et que B4: B10 sont des entiers.
Choisissez Simplex LP comme méthode de résolution et choisissez Résoudre. Dans quelques instants, Solver présente une solution optimale.
Solver a trouvé un moyen de couvrir le personnel du parc d'attractions en utilisant 30 employés au lieu de 38. Les économies par semaine sont de 544 $ - soit plus de 7 000 $ au cours de l'été.
Notez les cinq étoiles ci-dessous Employés nécessaires. Le programme proposé par Solver répond exactement à vos besoins pendant cinq des sept jours. Le sous-produit est que vous aurez plus d'employés mercredi et jeudi que vous n'en avez vraiment besoin.
Je peux comprendre comment Solver a proposé cette solution. Vous avez besoin de beaucoup de monde samedi, dimanche et vendredi. Une façon d’amener les gens là-bas ce jour-là est de leur donner congé le lundi et le mardi. C'est pourquoi Solver a mis 18 personnes en congé le lundi et le mardi.
Mais ce n'est pas parce que Solver a proposé une solution optimale qu'il n'existe pas d'autres solutions également optimales.
Lorsque je devinais simplement la dotation en personnel, je n'avais pas vraiment une bonne stratégie.
Maintenant que Solver m'a donné l'une des solutions optimales, je peux mettre mon chapeau logique. Avoir 28 employés en âge de fréquenter l'université le mercredi et le jeudi alors que vous n'avez besoin que de 15 ou 18 employés va créer des problèmes. Il n'y aura pas assez à faire. De plus, avec exactement le bon effectif sur cinq jours, vous devrez faire appel à quelqu'un pour des heures supplémentaires si quelqu'un d'autre appelle malade.
Je suis convaincu que Solver a besoin de 30 personnes pour que cela fonctionne. Mais je parie que je peux réorganiser ces personnes pour égaliser le calendrier et fournir un petit tampon les autres jours.
Par exemple, donner à quelqu'un le mercredi et le jeudi de congé garantit également que la personne est au travail les vendredi, samedi et dimanche. Ainsi, j'ai déplacé manuellement certains travailleurs de la ligne du lundi, mardi à la ligne du mercredi jeudi. J'ai continué à brancher manuellement différentes combinaisons et j'ai trouvé cette solution qui a les mêmes charges salariales que Solver mais de meilleurs actifs incorporels. La situation de sureffectif existe maintenant sur quatre jours au lieu de deux. Cela signifie que vous pouvez gérer les appels du lundi au jeudi sans avoir à appeler quelqu'un de son week-end.
Est-ce dommage que j'ai pu trouver une meilleure solution que Solver? Non. Le fait est que je n'aurais pas pu accéder à cette solution sans utiliser Solver. Une fois que Solver m'a donné un modèle qui minimisait les coûts, j'ai pu utiliser la logique des actifs incorporels pour conserver la même masse salariale.
Si vous avez besoin de résoudre des problèmes plus complexes que ce que Solver peut gérer, consultez les solveurs Excel premium disponibles sur Frontline Systems: http://mrx.cl/solver77.
Merci à Dan Fylstra et Frontline Systems pour cet exemple. Walter Moore a illustré les montagnes russes XL.
Regarder la vidéo
- Solver est un complément gratuit depuis l'époque de Lotus 1-2-3
- Solver est un produit du fondateur de Visicorp, Dan Fylstra
- Le solveur dans votre Excel est une version plus petite des solveurs lourds
- En savoir plus sur les solveurs professionnels: http://mrx.cl/solver77
- Pour installer Solver, tapez alt = "" + T puis I. Check Solver.
- Le solveur se trouve sur le côté droit de l'onglet Données
- Vous voulez avoir une cellule objective que vous essayez de minimiser ou de maximiser.
- Vous pouvez spécifier plusieurs cellules d'entrée.
- Vous pouvez spécifier des contraintes, y compris certaines auxquelles vous ne vous attendez pas:
- Pas de demi-personnes: utilisez INT pour Integer
- Le solveur trouvera une solution optimale, mais il pourrait y en avoir d'autres qui soient des liens
- Une fois que vous avez obtenu la solution Solver, vous pourrez peut-être la modifier.
Transcription vidéo
Apprenez Excel à partir d'un podcast, épisode 2036 - Introduction au solveur!
Très bien, je baladore tout ce livre, cliquez sur le "i" dans le coin supérieur droit pour accéder à la playlist, où vous pouvez lire toutes les vidéos!
Bienvenue à nouveau sur netcast, je suis Bill Jelen. Nous avons récemment parlé d'une analyse de simulation, comme la recherche d'objectifs, vous savez, avec une cellule d'entrée que vous modifiez, mais que faire si vous avez quelque chose de plus complexe? Il y a un excellent outil appelé Solver, Solver existe depuis longtemps, je vous garantis que si vous avez Excel et que vous utilisez Windows, vous avez Solver, il n'est probablement pas activé. Donc, pour l'activer, vous devez aller à alt = "" T puis I, donc T pour Tom, I pour glace, et cochez cette case pour Solver, cliquez sur OK, et après quelques secondes, vous aurez un onglet Solveur ici sur le côté droit. D'accord, et nous allons mettre en place un modèle ici que le solveur pourrait être en mesure de résoudre, nous avons un parc d'attractions, nous essayons de déterminer le nombre d'employés à planifier. Tout le monde travaille cinq jours consécutifs, donc là 's vraiment sept horaires possibles où vous êtes en congé, dimanche lundi, lundi mardi, mardi mercredi. Nous devons déterminer le nombre d'employés à inscrire à chacun de ces horaires.
Et donc juste de simples petits calculs ici, en faisant des SOMMAIRES, le nombre d'employés fois le dimanche pour savoir combien de personnes étaient là le dimanche, lundi, mardi, mercredi. Et ce que nous avons appris en exploitant ce parc d'attractions, c'est que nous avons besoin de beaucoup de monde samedi et dimanche. 30 personnes le samedi et dimanche, en semaine lundi, mardi, un peu lent, 12 collaborateurs pourront le faire. Très bien, juste en venant ici et en trainant juste de visser, vous savez, en essayant de trouver les bons chiffres, vous pouvez simplement continuer à brancher les choses, mais avec sept choix différents, cela prendrait une éternité, d'accord.
Maintenant, dans Solver, nous avons une série de cellules d'entrée, et dans la version gratuite de Solver, je pense que vous pouvez en avoir, est-ce une centaine? Je ne sais pas, il y a un certain nombre, et si vous devez aller plus loin, il y a un solveur Premium que vous pouvez obtenir de Frontline Systems. D'accord, nous avons donc des cellules d'entrée, nous avons des cellules de contrainte, puis vous devez tout ramener à un nombre final. Donc, dans mon cas, j'essaie de minimiser la masse salariale par semaine, de sorte que ce nombre vert est ce que je veux essayer et optimiser, d'accord, alors voici ce que nous allons faire!
Solveur, voici la cellule objectif, c'est la cellule verte, et je veux définir cela à une valeur minimale, déterminer la dotation en personnel qui me donne la valeur minimale, en modifiant ces cellules bleues. Et puis voici les contraintes, d'accord, donc la première contrainte est que le total de la planification doit être> = la section rouge, et nous pouvons faire tout cela comme une seule contrainte. Regardez à quel point c'est cool, toutes ces cellules doivent être> = ces cellules correspondantes ici, génial, cliquez sur Ajouter, d'accord, mais il y a d'autres choses auxquelles vous ne pensez pas. Par exemple, Solver à ce stade pourrait décider qu'il est préférable d'avoir 17 personnes sur ce calendrier, 43 personnes sur le calendrier et -7 personnes sur ce calendrier. Très bien, nous devons donc dire au Solveur que ces cellules d'entrée doivent être un nombre entier, cliquez sur Ajouter. Et aussi, nous ne pouvons pas faire en sorte que quelqu'un ne se présente pas,et ils nous rendront leur salaire, non? Nous allons donc dire que ces cellules doivent être> = 0, cliquez sur Ajouter, nous revenons maintenant, nous avons nos trois contraintes là-bas.
Il y a trois façons différentes de résoudre, et celle-ci suit les mathématiques linéaires, nous pouvons donc simplement utiliser Simplex LP. Si celui-ci ne fonctionne pas, alors essayez certainement les deux autres, j'ai eu des cas où le Simplex dit qu'il ne peut pas trouver de solution, et l'un des deux autres fonctionne. Frontline Systems a d'excellents didacticiels sur Solver, j'essaie juste de vous faire découvrir votre premier ici aujourd'hui, je ne prétends pas être un expert en Solver. Une fois que j'ai eu un Solver qui ne fonctionnait pas, et j'ai envoyé une note à Frontline Systems, et wow, j'ai reçu cette superbe lettre de 5 pages, à droite, de Dan Fylstra lui-même, le président de Solver! Et cela a commencé: "Cher Bill, ravi de vous entendre!" Et puis a continué pendant 4,9 pages, tout cela était à peu près complètement au-dessus de ma tête, d'accord. Mais tu sais, j'en sais assez sur Solver pour m'en sortir, d'accord,nous allons donc cliquer ici sur Résoudre, il a trouvé une solution, "Toutes les contraintes et conditions d'optimalité sont satisfaites." Je vais garder ça, je peux créer des rapports, je n'ai pas besoin de le faire maintenant. Oh, je peux en fait enregistrer un scénario, je me suis moqué des scénarios hier, peut-être que Solver pourrait créer un nouveau scénario pour moi, alors nous allons cliquer sur OK.
D'accord, et bien sûr, cela nous a permis d'économiser de l'argent, nous avons écrit 2584 avant, et maintenant cela nous a ramenés à 2040. Nous avons donc besoin de beaucoup de monde le lundi et le mardi, d'accord, des personnes, 2 personnes le mercredi jeudi, et puis vendredi samedi. Eh bien, c'est génial, je n'aurais jamais trouvé cette série de réponses au hasard, d'accord, mais cela signifie-t-il que c'est la meilleure réponse? Eh bien, cela signifie que c'est la masse salariale minimum, mais je peux probablement proposer un ensemble de réponses différent qui aurait toujours cette masse salariale minimum. Il y a d'autres façons de le faire, ce pourrait être un calendrier légèrement meilleur. Par exemple, en ce moment, nous avons 28 personnes mercredi et jeudi, alors que nous n'avons besoin que de 15 et 18, c'est beaucoup de monde. Pensez à qui travaille dans les parcs d'attractions, ce sont des collégiens à la maison pour une pause,cela va être un problème si nous avons autant de personnes supplémentaires. Et lundi mardi, nous sommes morts même, exactement là où nous voulons être. Donc, cela signifie que si quelqu'un que je vais appeler malade, maintenant nous allons devoir, vous savez, appeler quelqu'un et lui payer une heure et demie, car il a déjà travaillé cinq autres jours.
Très bien, donc juste avec quelques simples petits calculs ici, si je devais enlever 8 à lundi mardi, et faire 10, et prendre ces 8 et les ajouter à mercredi jeudi, d'accord. Maintenant, j'ai une solution Solver avec exactement la même réponse, 2040, ils ont le bon nombre de personnes. Je viens d'équilibrer le calendrier, et maintenant nous avons 8 supplémentaires, 8 supplémentaires, 3 supplémentaires et 2 supplémentaires, et exactement ce dont nous avons besoin le week-end qui sont, vous savez, le scénario complet du personnel. Pour moi, c'est légèrement mieux que ce que Solver a proposé, cela signifie-t-il que ce solveur a échoué? Non, absolument pas, car je n'aurais jamais été aussi proche sans Solver. Une fois que Solver m'a donné la réponse, oui, j'ai pu l'ajuster un peu et y arriver, d'accord. Astuce n ° 37, «40 meilleurs conseils Excel de tous les temps», approchant de la fin de ces 40 premiers, une excellente petite introduction à Solver.Le guide de tous les podcasts de cette série est ici, «MrExcel XL - 40 meilleurs conseils Excel de tous les temps», vous pouvez avoir le livre électronique pour seulement 10 $, imprimer le livre pour 25 $, cliquez sur le «i» en haut -coin droit!
Bon, récapitulatif: Solveur, si vous êtes dans les versions Windows d'Excel, Lotus 1-2-3, il est là, il est créé par le fondateur de Visicorp, Dan Fylstra. C'est une version gratuite des solveurs lourds, voici un lien pour aller voir les solveurs lourds, qui figurera dans les commentaires YouTube. Il est probable qu'ils ne soient tout simplement pas installés, alt = "" TI, cochez Solver, regardez sur le côté droit de l'onglet Données pour trouver Solver. D'accord, vous devez avoir une cellule objective que vous essayez de minimiser ou de maximiser ou de définir une valeur, une plage de cellules d'entrée. Précisez les contraintes, y compris quelque chose auquel je ne m'attendais pas, comme je devais dire «Pas de demi-personne» et «Pas de personne négative». Le solveur trouvera la solution optimale, mais il peut y en avoir d'autres qui sont des liens et vous pourrez peut-être la modifier pour obtenir une meilleure solution.
D'accord, là vous l'avez, je veux vous remercier d'être passé, nous vous reverrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2036.xlsx
