Les pénalités et l’évolution Solveur dans Microsoft Excel

■■ Quelles sont les clés pour utiliser avec succès le solveur évolutionnaire?

■■ Comment puis-je utiliser le solveur évolutionnaire pour affecter 80 employés de Microsoft Finance à un travail dans l’un des quatre groupes de travail?

Réponses aux questions de ce cette article

Cette section fournit les réponses aux questions répertoriées au début de cette article.

Quelles sont les clés pour utiliser le solveur évolutionnaire avec succès?

Au moins, ce livre a indiqué que le solveur évolutionnaire devait être utilisé pour trouver des solutions aux problèmes d’optimisation dans les cellules cibles, les cellules changeantes ou les deux impliquent des fonctions non lisses telles que SI, ABS, MAX, MIN, NB.SI, NB.SI.ENS, SOMME.SI, SOMME.SI.ENS, MOYENNE.SI et MOYENNE.SI.ENS. Avant de résoudre un problème avec le solveur évolutionnaire, vous devez effectuer les opérations suivantes dans la boîte de dialogue Paramètres du solveur:

■■ Cliquez sur Options, sélectionnez l’onglet Évolutionnaire et augmentez le taux de mutation à 0,50. En augmentant

le taux de mutation permet au solveur de sauter dans l’ensemble des solutions possibles et d’être coïncidé dans une partie de l’ensemble des solutions possibles qui ne contient pas une bonne solution au modèle du solveur.

■■ Modifiez la durée maximale sans amélioration à 3 600 secondes. L’augmentation du temps maximum sans amélioration assurée que vous quittez votre PC, Microsoft Excel recherché une solution pendant 3 600 secondes. Vous pouvez arrêter le solveur à tout moment en appuyant sur la touche Échap.

■■ Placez des limites respectives et supérieures raisonnables sur vos cellules changeantes. Le fait de placer des limites sur les cellules change la taille de la région dans laquelle Solver recherche une solution optimale. Cela peut accélérer la progression du Solveur vers une solution optimale. L’utilisateur peut modifier de nombreux autres paramètres du solveur évolutionnaire, mais le taux de mutation et la durée maximale sans amélioration se sont avérés être les seuls paramètres qui ont un impact significatif sur le processus de solution.

Tout dans la vie a un avantage et un inconvénient, et le solveur évolutionnaire ne fait pas exception. L’avantage du solveur évolutionnaire est qu’il gère bien les fonctions non lisses. L’inconvénient est que les contraintes qui ne sont pas des fonctions linéaires des cellules changées ne sont pas très bien gérées. Pour gérer la plupart des contraintes avec le solveur évolutionnaire, vous devez pénaliser la cellule cible pour faire de la violation d’une contrainte une mauvaise choix. Ensuite, la survie des plus aptes supprimera toute violation des contraintes. La question suivante du chapitre montre comment utiliser les pénalités avec le solveur évolutionnaire.

Comment puis-je utiliser le solveur évolutionnaire pour affecter 80 employés de Microsoft Finance à un travail dans l’un des quatre groupes de travail?

Vous devez affecter 80 employés à quatre groupes de travail. Le chef de chaque groupe de travail a évalué la compétence de chaque employé sur une échelle de 0 à 10 (10 est le plus compétent). Chaque employé a évalué sa satisfaction à l’égard de chaque affectation (encore une fois sur une échelle de 0 à 10). Par exemple, le travailleur 1 a reçu une note de 9 de la partie du chef du groupe de travail 1, et le travailleur 1 donne au groupe de travail 4 une note de 7.

Le travail pour cette question se trouve dans le fichier (Voir figure ci-dessous.) Vous souhaitez attribuer entre 18 et 22 personnes par groupe de travail. Vous considérez que la compétence professionnelle est deux fois plus importante que la satisfaction des employés. Comment pouvez-vous affecter des employés à des groupes de travail pour maximiser la satisfaction totale et assurer que chaque division à un nombre d’employés requis?

 FIGURE 2 : Il s’agit des données du problème d’affectation des tâches.

Dans les cellules A3: A82, entrez les affectations d’essai des travailleurs aux groupes de travail. L’affectation de chaque travailleur au groupe de travail 1, par exemple, est une solution de départ acceptable. La copie de la formule RECHERCHEH (A3, Qual, B3 + 1) de K3 à K3: K82 vous permet de rechercher les qualifications de chaque employé pour le travail qui lui est affecté. Notez que Qual fait référence à la gamme C2: F82. Ensuite, copie de la formule RECHERCHEH (A3, Satis, B3 + 1) de L3 à L3: L82 vous permet de rechercher la satisfaction de l’employé avec son travail assigné. Satis est le nom de gamme pour G2: J82.

Pour gérer le fait que chaque division a besoin de 18 à 22 employés, vous devez compter le nombre d’employés concernés par chaque groupe de travail. Vous pouvez faire dans les cellules N6: N9 en copiant la formule NB.SI($ A $ 3: $ A $ 82, M6) de N6 à N7: N9. Ensuite, dans les cellules O6: O9, détermine si un groupe de travail à un nombre incorrect d’employés en copiant la formule SI (OUI (N6 <18, N6> 22), 1,0) de O6 à O7: O9.

Vous allez maintenant voir comment travailler sur le calcul de la cellule cible. Dans K1: L1, vous calculez la compétence totale et la qualité totale du travail en copiant la formule SOMME (K3: K82) de K1 à K1: L1. Pour vous assurer que chaque groupe de travail compte entre 18 et 22 employés, vous pouvez pénaliser la cellule cible. Choisissez une pénalité de 1 000 pour chaque groupe de travail qui compte moins de 18 ou plus de 22 travailleurs. Il n’y a pas de règle stricte pour vous aider à déterminer une pénalité appropriée. Dans cette situation, la note moyenne est 5. Cela donne une cellule cible de 2 * 400 + 400 = 1 200. Par conséquent, il semble probable que mettre le mauvais nombre de personnes dans aucune division ne bénéficie de la cellule cible de plus de 1 000, de sorte que la survie du test d’ajustement tuera toute solution pour lequel un groupe de travail à trop ou trop peu de travailleurs. La pénalité appropriée ne doit pas être trop importante (100 000) car elle oblige parfois le solveur à ignorer le vrai problème. Si la pénalité est trop faible, le Solveur n’atteindra pas l’objectif que vous êtes fixé.

Dans la cellule O10, calculez le nombre total de divisions qui n’ont pas le nombre correct de travailleurs avec la formule SOMME (O6: O9). Maintenant, vous êtes enfin prêt à calculer la cellule cible dans la cellule O12 à deux fois la compétence totale à la satisfaction totale au travail et en soustrayant une pénalité de 1 000 pour chaque groupe qui n’a pas le bon nombre d’employés . Votre cellule cible finale est avec la formule 2 * K1 + L1–1000 * O10.

Vous pouvez maintenant créer le modèle de solveur pour ce problème. Vous devez utiliser le solveur évolutionnaire car les fonctions NB.SI et SI sont des fonctions non lisses des cellules changeantes. Le modèle est illustré à la figure  ci-dessous.

Maximisez la somme pondérée de la satisfaction du groupe de travail et des employés moins la pénalité pour un nombre incorrect de travailleurs dans un groupe de travail (cellule O12), puis contraignez l’affectation de chaque travailleur à 1, 2, 3 ou 4. La solution est illustrée à la figure 36-1. Chaque groupe à bon nombre de travailleurs; la compétence moyenne des employés est de 7,2 et la satisfaction moyenne des employés est de 6,3. Sur l’ensemble des 80 travailleurs, la moyenne de leurs notes de compétence est de 4,4 et la moyenne globale des notes de satisfaction est de 5, de sorte que les conditions sont beaucoup améliorées par rapport à une assignation aléatoire.

FIGURE 2 : Il s’agit du modèle de solveur pour le problème d’affectation des travailleurs.

Si vous aviez essayé le moteur non linéaire GRG (même avec Multistart), le Solveur n’a pas trouvé la solution optimale car le modèle inclut des fonctions non lisses. Une autre astuce sur l’utilisation du solveur évolutif consiste à utiliser le moins de cellules changeantes possible, et vous serez récompensés si le solveur prend moins de temps pour trouver une solution optimale.

Utilisation de la mise en forme conditionnelle pour mettre en surbrillance les notes de chaque employé

Vous pouvez utiliser la fonction de mise en forme conditionnelle pour mettre en surbrillance en jaune la compétence et la satisfaction réelle de chaque employé (en fonction de son affectation). Dans la cellule C3, connaît la plage des cellules C3: J82. Sélectionnez Nouvelle règle dans la mise en forme conditionnelle sous l’onglet Accueil, utilisez une formule pour déterminer les cellules à formateur, puis remplissez la boîte de dialogue comme illustré à la figure ci-dessous.

FIGURE 3: Utiliser la mise en forme conditionnelle pour mettre en évidence la qualité et la satisfaction des employés.

Cette formule prend un format jaune dans la cellule C3 si et seulement si le premier travailleur est affecté au groupe de travail 1. Excel copie cette formule dans tous les sens afin que les notes de qualité et de satisfaction de chaque travailleur soient mises en surveillance uniquement pour le groupe de travail consulté chaque employé est affecté.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x