Table des matières
"Rechercher une solution" est un complément Excel, grâce auquel il est possible de choisir la meilleure solution aux problèmes en fonction des restrictions spécifiées. La fonction permet de planifier les employés, répartir les coûts ou les investissements. Savoir comment cette fonctionnalité fonctionne vous fera gagner du temps et des efforts.
Qu'est-ce que la recherche de solutions ?
En combinaison avec diverses autres options d'Excel, il existe une fonction moins populaire, mais extrêmement nécessaire "Rechercher une solution". Malgré le fait que le trouver n'est pas facile, le connaître et l'utiliser aide à résoudre de nombreux problèmes. L'option traite les données et donne la solution optimale parmi celles autorisées. L'article décrit le fonctionnement direct de la recherche de solution.
Comment activer la fonctionnalité "Rechercher une solution"
Malgré son efficacité, l'option en question n'occupe pas une place prépondérante dans la barre d'outils ou le menu contextuel. La plupart des utilisateurs qui travaillent dans Excel ne sont pas conscients de sa présence. Par défaut, cette fonction est désactivée, pour l'afficher, effectuez les actions suivantes :
- Ouvrez le "Fichier" en cliquant sur le nom approprié.
- Cliquez sur la section "Paramètres".
- Sélectionnez ensuite la sous-section "Modules complémentaires". Tous les modules complémentaires du programme seront affichés ici, l'inscription "Gestion" apparaîtra ci-dessous. Sur le côté droit de celui-ci, il y aura un menu contextuel dans lequel vous devrez sélectionner "Excel Add-ins". Cliquez ensuite sur "Aller".
1 - Une fenêtre supplémentaire "Compléments" s'affichera sur le moniteur. Cochez la case à côté de la fonction souhaitée et cliquez sur OK.
- La fonction souhaitée apparaîtra sur le ruban à droite de la section "Données".
À propos des modèles
Ces informations seront extrêmement utiles pour ceux qui viennent de se familiariser avec le concept de "modèle d'optimisation". Avant d'utiliser la « Recherche de solution », il est recommandé d'étudier les matériaux sur les méthodes de construction des modèles :
- l'option envisagée permettra d'identifier la meilleure méthode afin d'allouer des fonds pour des investissements, le chargement des locaux, la fourniture de biens ou d'autres actions où il est nécessaire de trouver la meilleure solution.
- La « méthode optimale » dans une telle situation signifierait : augmenter les revenus, réduire les coûts, améliorer la qualité, etc.
Tâches d'optimisation typiques :
- Détermination d'un plan de production, au cours duquel le bénéfice de la vente des marchandises libérées sera maximal.
- Détermination des cartes de transport, au cours desquelles les coûts de transport sont minimisés.
- Recherche de la distribution de plusieurs machines pour différents types de travaux, afin de réduire les coûts de production.
- Détermination du délai le plus court pour la réalisation des travaux.
Important! Pour formaliser la tâche, il est nécessaire de créer un modèle qui reflète les principaux paramètres du domaine. Dans Excel, un modèle est un ensemble de formules qui utilisent des variables. L'option considérée recherche des indicateurs tels que la fonction objectif est supérieure (inférieure) ou égale à la valeur spécifiée.
Phase préparatoire
Avant de placer une fonction sur le ruban, vous devez comprendre le fonctionnement de l'option. Par exemple, des informations sur la vente de biens sont indiquées dans le tableau. La tâche consiste à attribuer une remise pour chaque article, qui serait de 4.5 millions de roubles. Le paramètre est affiché dans une cellule appelée cible. Sur cette base, d'autres paramètres sont calculés.
Notre tâche sera de calculer la remise par laquelle les sommes pour la vente de divers produits sont multipliées. Ces 2 éléments sont reliés par une formule écrite comme ceci : =D13*$G$2. Où dans D13 la quantité totale pour l'implémentation est écrite, et $G$2 est l'adresse de l'élément désiré.
Utiliser la fonction et la configurer
Lorsque la formule est prête, vous devez utiliser directement la fonction elle-même :
- Vous devez passer à la section "Données" et cliquer sur "Rechercher une solution".
- Les "Options" s'ouvriront, où les paramètres requis sont définis. Dans la ligne « Optimiser la fonction objectif : », vous devez spécifier la cellule où la somme des remises est affichée. Il est possible de prescrire vous-même les coordonnées ou de choisir dans le document.
- Ensuite, vous devez accéder aux paramètres des autres paramètres. Dans la section "À :", il est possible de définir les limites maximales et minimales ou un nombre exact.
- Puis le champ « Modification des valeurs des variables : » est renseigné. Ici, les données de la cellule souhaitée sont entrées, qui contiennent une valeur spécifique. Les coordonnées sont enregistrées indépendamment ou la cellule correspondante dans le document est cliquée.
- Ensuite, l'onglet "Selon les restrictions :" est édité, où les restrictions sur les données appliquées sont définies. Par exemple, les fractions décimales ou les nombres négatifs sont exclus.
- Après cela, une fenêtre s'ouvre qui vous permet d'ajouter des restrictions dans les calculs. La ligne initiale contient les coordonnées d'une cellule ou d'une plage entière. Suivant les conditions de la tâche, les données de la cellule souhaitée sont indiquées, où l'indicateur de remise est affiché. Ensuite, le signe de comparaison est déterminé. Il est défini sur "supérieur ou égal à" afin que la valeur finale ne soit pas accompagnée d'un signe moins. La « Limite » définie à la ligne 3 est de 0 dans cette situation. Il est également possible de définir une limite avec "Ajouter". Les prochaines étapes sont les mêmes.
- Lorsque les étapes ci-dessus sont terminées, la limite définie apparaît sur la plus grande ligne. La liste peut être longue et dépendra de la complexité des calculs, cependant, dans une situation particulière, 1 condition suffit.
- De plus, il est possible de sélectionner d'autres paramètres avancés. En bas à droite, il y a une option "Options" qui vous permet de le faire.
- Dans les paramètres, vous pouvez définir la "Précision limite" et les "Limites de la solution". Dans notre situation, il n'est pas nécessaire d'utiliser ces options.
- Lorsque les réglages sont terminés, la fonction elle-même démarre - cliquez sur "Trouver une solution".
- Une fois que le programme a effectué les calculs requis et émis les calculs finaux dans les cellules requises. Ensuite, une fenêtre avec les résultats s'ouvre, où les résultats sont enregistrés / annulés, ou les paramètres de recherche sont configurés en fonction d'un nouveau. Lorsque les données répondent aux exigences, la solution trouvée est enregistrée. Si vous cochez au préalable la case "Retour à la boîte de dialogue des options de recherche de solution", une fenêtre avec les paramètres de fonction s'ouvrira.
- Il est possible que les calculs se soient avérés erronés ou qu'il soit nécessaire de modifier les données initiales afin d'obtenir d'autres indicateurs. Dans une telle situation, vous devez rouvrir la fenêtre des paramètres et revérifier les informations.
- Lorsque les données sont exactes, une autre méthode peut être utilisée. À ces fins, vous devez cliquer sur l'option actuelle et sélectionner la méthode la plus appropriée dans la liste qui apparaît :
- Trouver une solution à l'aide d'un gradient généralisé pour les problèmes non linéaires. Par défaut, cette option est utilisée, mais il est possible d'en utiliser d'autres.
- Trouver des solutions à des problèmes linéaires basés sur la méthode du simplexe.
- Utiliser la recherche évolutive pour accomplir une tâche.
Attention! Lorsque les options ci-dessus n'ont pas réussi à faire face à la tâche, vous devez vérifier à nouveau les données dans les paramètres, car c'est souvent la principale erreur dans de telles tâches.
- Lorsque la remise souhaitée est reçue, il reste à l'appliquer pour calculer le montant des remises pour chaque article. A cet effet, l'élément initial de la colonne "Montant de la remise" est mis en surbrillance, la formule s'écrit «=D2*$G$2» et appuyez sur "Entrée". Les signes dollar sont posés de sorte que lorsque la formule est étendue aux lignes adjacentes, G2 ne change pas.
- Le montant de la remise pour l'article initial sera maintenant obtenu. Ensuite, vous devez déplacer le curseur sur le coin de la cellule, lorsqu'il devient un "plus", le LMB est enfoncé et la formule est étirée jusqu'aux lignes requises.
- Après cela, la table sera enfin prête.
Charger/enregistrer les options de recherche
Cette option est utile lors de l'application de diverses options de contrainte.
- Dans le menu Options de l'outil de recherche de solutions, cliquez sur Charger/Enregistrer.
- Entrez la plage de la zone de modèle et cliquez sur Enregistrer ou Charger.
Lors de l'enregistrement du modèle, une référence est saisie à 1 cellule d'une colonne vide où sera placé le modèle d'optimisation. Lors du chargement du modèle, une référence est saisie pour toute la plage contenant le modèle d'optimisation.
Important! Pour enregistrer les derniers paramètres dans le menu Options de la solution, un classeur est enregistré. Chaque feuille qu'elle contient possède ses propres options de complément Solveur. De plus, il est possible de définir plus d'une tâche pour une feuille en cliquant sur le bouton "Charger ou Enregistrer" afin d'enregistrer des tâches individuelles.
Un exemple simple d'utilisation du Solveur
Il est nécessaire de charger le conteneur avec des conteneurs pour que sa masse soit maximale. Le réservoir a un volume de 32 mètres cubes. M. Une boîte remplie a un poids de 20 kg, son volume est de 0,15 mètre cube. M. Carton – 80 kg et 0,5 cu. M. Il est nécessaire que le nombre total de conteneurs soit d'au moins 110 pièces. Les données sont organisées comme ceci :
Les variables du modèle sont marquées en vert. La fonction objectif est surlignée en rouge. Restrictions : par le plus petit nombre de contenants (supérieur ou égal à 110) et par poids (=SOMMEPROD(B8:C8,B6:C6) – tare totale dans le conteneur.
Par analogie, on considère le volume total : =SOMMEPROD(B7:C7,B8:C8). Une telle formule est nécessaire pour fixer une limite au volume total des conteneurs. Ensuite, via la « Recherche d'une solution », des liens sont entrés vers des éléments avec des variables, des formules et les indicateurs eux-mêmes (ou des liens vers des cellules spécifiques). Bien entendu, le nombre de conteneurs est un entier (c'est aussi une limitation). Nous appuyons sur "Trouver une solution", à la suite de quoi nous trouvons un tel nombre de conteneurs lorsque la masse totale est maximale et que toutes les restrictions sont prises en compte.
La recherche de solution n'a pas permis de trouver des solutions
Une telle notification apparaît lorsque la fonction en question n'a pas trouvé de combinaisons de scores variables qui satisfont chaque contrainte. Lors de l'utilisation de la méthode Simplex, il est tout à fait possible qu'il n'y ait pas de solution.
Lorsqu'une méthode de résolution de problèmes non linéaires est utilisée, dans tous les cas à partir des indicateurs initiaux des variables, cela indique que la solution possible est éloignée de tels paramètres. Si vous exécutez la fonction avec d'autres indicateurs initiaux des variables, il existe probablement une solution.
Par exemple, lors de l'utilisation de la méthode non linéaire, les éléments du tableau avec des variables n'étaient pas remplis et la fonction n'a pas trouvé de solutions. Cela ne veut pas dire qu'il n'y a pas de solution. Maintenant, compte tenu des résultats d'une certaine évaluation, d'autres données sont entrées dans les éléments avec des variables proches de celles reçues.
Dans toutes les situations, vous devez d'abord examiner le modèle pour l'absence de conflit de contraintes. Souvent, cela est lié à une mauvaise sélection du ratio ou de l'indicateur de limitation.
Dans l'exemple ci-dessus, l'indicateur de volume maximum est de 16 mètres cubes. m au lieu de 32, car une telle restriction contredit les indicateurs du nombre minimum de sièges, puisqu'elle correspondra au nombre de 16,5 mètres cubes. M.
Conclusion
Sur cette base, l'option "Rechercher une solution" dans Excel aidera à résoudre des problèmes spécifiques qui sont plutôt difficiles ou impossibles à résoudre de la manière habituelle. La difficulté d'appliquer cette méthode est qu'au départ cette option est masquée, c'est pourquoi la plupart des utilisateurs ne sont pas conscients de sa présence. De plus, la fonction est assez difficile à apprendre et à utiliser, mais avec une recherche appropriée, elle apportera de grands avantages et facilitera les calculs.