Tâche de transport dans Excel. Trouver le meilleur moyen de transport du vendeur à l'acheteur

Excel est un programme très fonctionnel. Il peut être utilisé pour résoudre une énorme couche de problèmes auxquels on doit faire face dans les affaires. L'un des plus courants est le transport. Imaginez que nous ayons besoin de comprendre quelle méthode de transport du fabricant à l'acheteur final est la plus optimale en termes de temps, d'argent et d'autres ressources. Ce problème est assez populaire, quel que soit le secteur d'activité de l'entreprise. Par conséquent, examinons de plus près comment l'implémenter à l'aide d'Excel.

Description de la tâche de transport

Nous avons donc deux contreparties qui interagissent constamment l'une avec l'autre. Dans notre cas, il s'agit d'un acheteur et d'un vendeur. Nous devons comprendre comment transporter les marchandises de manière à ce que les coûts soient minimes. Pour ce faire, vous devez présenter toutes les données sous une forme schématique ou matricielle. Dans Excel, nous utilisons cette dernière option. En général, il existe deux types de tâches de transport :

1. Fermé. Dans ce cas, l'offre et la demande sont en équilibre.
2. Ouvert. Il n'y a pas d'égalité entre l'offre et la demande ici. Pour obtenir une solution à ce problème, vous devez d'abord l'amener au premier type, en égalisant l'offre et la demande. Pour ce faire, vous devez introduire un indicateur supplémentaire - la présence d'un acheteur ou d'un vendeur conditionnel. De plus, vous devez apporter certaines modifications au tableau des coûts.

Comment activer la fonctionnalité Rechercher une solution dans Excel

Pour résoudre les problèmes de transport dans Excel, il existe une fonction spéciale appelée "Rechercher une solution". Il n'est pas activé par défaut, vous devez donc suivre les étapes suivantes :

1. Ouvrez le menu "Fichier", qui se trouve dans le coin supérieur gauche de la fenêtre du programme.
2. Après cela, cliquez sur le bouton avec les paramètres.
3. Ensuite, nous trouvons la sous-section "Paramètres" et allons dans le menu de gestion des modules complémentaires. Ce sont de petits programmes qui s'exécutent dans l'environnement Microsoft Excel. Nous voyons qu'au début, nous avons cliqué sur le menu "Compléments", puis dans la partie inférieure droite, nous avons défini l'élément "Compléments Excel" et cliqué sur le bouton "Aller". Toutes les actions nécessaires sont mises en évidence par des rectangles rouges et des flèches.
4. Ensuite, activez le complément «Rechercher une solution», après quoi nous confirmons nos actions en appuyant sur le bouton OK. Sur la base de la description du cadre, nous pouvons voir qu'il est conçu pour analyser des données complexes, telles que scientifiques et financières.
5. Après cela, allez dans l'onglet "Données", où nous voyons un nouveau bouton, qui s'appelle de la même manière que le complément. Il se trouve dans le groupe d'outils d'analyse.

Il ne reste plus qu'à cliquer sur ce bouton, et nous procédons à la solution du problème de transport. Mais avant cela, nous devrions parler un peu plus de l'outil Solveur dans Excel. Il s'agit d'un module complémentaire Excel spécial qui permet de trouver la solution la plus rapide à un problème. Une caractéristique est la prise en compte des restrictions que l'utilisateur définit au stade de la préparation. En termes simples, il s'agit d'un sous-programme qui permet de déterminer la meilleure façon d'accomplir une certaine tâche. Ces tâches peuvent inclure les éléments suivants :

1. Investir, charger un entrepôt ou toute autre activité similaire. Y compris la livraison des marchandises.
2. Le meilleur moyen. Cela inclut des objectifs tels que la réalisation d'un profit maximum à un coût minimum, la manière d'obtenir la meilleure qualité avec les ressources disponibles, etc.

Outre les tâches de transport, cet add-on est également utilisé aux fins suivantes :

1. Élaboration d'un plan de fabrication. C'est-à-dire combien d'unités d'un produit doivent être produites pour atteindre un revenu maximum.
2. Trouvez la répartition du travail pour différents types de travail afin que le coût total de production d'un produit ou d'un service soit le plus petit.
3. Définissez le temps minimum qu'il faudra pour terminer tout le travail.

Comme vous pouvez le voir, les tâches sont très différentes. La règle universelle pour appliquer ce complément est qu'avant de résoudre le problème, il est nécessaire de créer un modèle qui correspondrait aux caractéristiques clés du problème posé. Un modèle est une collection de fonctions qui utilisent des variables comme arguments. C'est-à-dire des valeurs qui peuvent changer.

Il est important de noter que l'optimisation d'un ensemble de valeurs s'effectue exclusivement sur un indicateur, appelé fonction objectif.

Le complément Solver énumère les différentes valeurs des variables qui sont passées à la fonction objectif de telle sorte qu'elle soit maximale, minimale ou égale à une certaine valeur (c'est précisément la restriction). Il existe une autre fonction quelque peu similaire dans son principe de fonctionnement, et qui est souvent confondue avec la « Recherche de solution ». C'est ce qu'on appelle la "sélection d'options". Mais si vous creusez plus profondément, la différence entre eux est énorme :

1. La fonction Goal Seek ne fonctionne pas avec plus d'une variable.
2. Il ne prévoit pas la possibilité de fixer des limites sur les variables.
3. Il est capable de déterminer uniquement l'égalité de la fonction objectif à une certaine valeur, mais ne permet pas de trouver le maximum et le minimum. Par conséquent, il n'est pas adapté à notre tâche.
4. Capable de calculer efficacement uniquement si le modèle est de type linéaire. Si le modèle est non linéaire, il trouve la valeur la plus proche de la valeur d'origine.

La tâche de transport est beaucoup plus compliquée dans sa structure, donc le module complémentaire "Sélection de paramètres" n'est pas suffisant pour cela. Regardons de plus près comment mettre en œuvre la fonction "Rechercher une solution" en pratique en utilisant l'exemple d'un problème de transport.

Un exemple de résolution d'un problème de transport dans Excel

Afin de montrer clairement comment résoudre les problèmes de transport en pratique dans Excel, donnons un exemple.

Tâches liées aux conditions

Supposons que nous ayons 6 vendeurs et 7 acheteurs. La demande et l'offre entre eux se répartissent respectivement de la manière suivante : 36, 51, 32, 44, 35 et 38 unités sont vendeurs et 33, 48, 30, 36, 33, 24 et 32 ​​unités sont acheteurs. Si vous additionnez toutes ces valeurs, vous constaterez que l'offre et la demande sont en équilibre. Ce problème est donc de type fermé, qui se résout très simplement.

De plus, nous avons des informations sur le montant que vous devez dépenser pour le transport d'un point A à un point B (ils sont mis en évidence dans les cellules jaunes dans l'exemple).

Solution - algorithme étape par étape

Maintenant, après nous être familiarisés avec les tables avec les données initiales, nous pouvons utiliser l'algorithme suivant pour résoudre ce problème :

1. Tout d'abord, nous créons un tableau composé de 6 lignes et 7 colonnes.
2. Après cela, nous allons dans n'importe quelle cellule qui ne contient aucune valeur et se trouve en même temps en dehors du tableau nouvellement créé et insérons la fonction. Pour ce faire, cliquez sur le bouton fx, situé à gauche de la ligne de saisie de la fonction.
3. Nous avons une fenêtre dans laquelle nous devons sélectionner la catégorie "Math". Quelle fonction nous intéresse ? Celui mis en évidence dans cette capture d'écran. Fonction SUMPRODUCT multiplie les plages ou les tableaux entre eux et les additionne. Juste ce dont nous avons besoin. Après cela, appuyez sur la touche OK.
4. Ensuite, une fenêtre apparaîtra sur l'écran dans laquelle vous devez spécifier les paramètres de la fonction. Ce sont les suivants :
   1. Tableau 1. C'est le premier argument dans lequel nous écrivons la plage qui est surlignée en jaune. Vous pouvez définir les paramètres de la fonction soit à l'aide du clavier, soit en sélectionnant la zone appropriée avec le bouton gauche de la souris.
   2. Tableau 2. Il s'agit du deuxième argument, qui est la table nouvellement créée. Les actions sont exécutées de la même manière.

Confirmez votre action en appuyant sur le bouton OK.

1. Après cela, nous faisons un clic gauche de la souris sur la cellule qui sert de haut à gauche dans le tableau nouvellement créé. Cliquez à nouveau sur le bouton de fonction d'insertion.
2. Nous sélectionnons la même catégorie que dans le cas précédent. Mais cette fois nous nous intéressons à la fonction SUM.
3. Vient maintenant l'étape de remplir les arguments. Comme premier argument, nous écrivons la ligne supérieure du tableau que nous avons créé au début. De la même manière que précédemment, cela peut se faire en sélectionnant ces cellules sur la feuille, ou manuellement. Nous confirmons nos actions en appuyant sur le bouton OK.
4. Nous verrons les résultats dans la cellule avec la fonction. Dans ce cas, c'est zéro. Ensuite, déplacez le curseur vers le coin inférieur droit, après quoi un marqueur de saisie semi-automatique apparaîtra. Il ressemble à une petite peluche noire. S'il apparaît, maintenez le bouton gauche de la souris enfoncé et déplacez le curseur sur la dernière cellule de notre tableau.
5. Cela nous donne la possibilité de transférer la formule dans toutes les autres cellules et d'obtenir les résultats corrects sans avoir à effectuer de calculs supplémentaires.
6. L'étape suivante consiste à sélectionner la cellule en haut à gauche et à coller la fonction SUM en elle. Après cela, nous entrons les arguments et utilisons le marqueur de saisie semi-automatique pour remplir toutes les cellules restantes.
7. Après cela, nous procédons directement à la résolution du problème. Pour ce faire, nous utiliserons le module complémentaire que nous avons inclus précédemment. Allez dans l'onglet "Données", et nous y trouvons l'outil "Rechercher une solution". Nous cliquons sur ce bouton.
8. Maintenant, une fenêtre est apparue devant nos yeux, à travers laquelle vous pouvez configurer les paramètres de notre add-on. Examinons chacune de ces options :
   1. Optimiser la fonction objectif. Ici, nous devons sélectionner la cellule contenant la fonction SUMPRODUCT. On voit que cette option permet de sélectionner une fonction pour laquelle une solution sera recherchée.
   2. Avant de. Ici, nous définissons l'option "Minimum".
   3. En changeant les cellules des variables. Nous indiquons ici la plage correspondant au tableau que nous avons créé au tout début (à l'exception de la ligne et de la colonne récapitulatives).
   4. Sous réserve de restrictions. Ici, nous devons ajouter des contraintes en cliquant sur le bouton Ajouter.
   5. Nous nous souvenons du type de contrainte que nous devons créer - la somme des valeurs des demandes des acheteurs et des offres des vendeurs doit être la même.
9. La tâche des restrictions est effectuée comme suit:
   1. Lien vers les cellules. Ici, nous entrons dans la plage de la table pour les calculs.
   2. Conditions. Il s'agit d'une opération mathématique par rapport à laquelle la plage spécifiée dans le premier champ d'entrée est vérifiée.
   3. La valeur de la condition ou de la contrainte. Ici, nous entrons dans la colonne appropriée dans la table source.
   4. Une fois toutes les étapes terminées, cliquez sur le bouton OK, confirmant ainsi nos actions.

Nous effectuons exactement les mêmes opérations pour les lignes du haut, en fixant la condition suivante : elles doivent être égales.

La prochaine étape consiste à définir les conditions. Nous devons définir les critères suivants pour la somme des cellules du tableau - supérieur ou égal à zéro, un entier. En conséquence, nous avons une telle liste de conditions dans lesquelles le problème est résolu. Ici, vous devez vous assurer que la case à côté de l'option "Rendre les variables sans limites non négatives" est cochée. De plus, dans notre situation, il est nécessaire que la méthode de résolution du problème soit choisie - "Recherche d'une solution aux problèmes non linéaires des méthodes OPG". Maintenant, nous pouvons dire en toute sécurité que le réglage est terminé. Par conséquent, il ne reste plus qu'à effectuer les calculs. Pour cela, cliquez sur le bouton « Trouver une solution ».

Après cela, toutes les données seront calculées automatiquement, puis Excel affichera une fenêtre avec les résultats. Il est nécessaire de revérifier le fonctionnement de l'ordinateur, car des erreurs sont possibles si les conditions ont été précédemment définies de manière incorrecte. Si tout est correct, cliquez sur le bouton "OK" et voyez le tableau terminé.

S'il s'avère que notre tâche est devenue un type ouvert, c'est mauvais, car vous devez modifier la table source pour que la tâche devienne une tâche fermée. Cependant, lorsque cela est fait, l'algorithme restant sera le même.

Conclusion

Comme vous pouvez le voir, Excel peut également être utilisé pour des calculs très complexes, qui à première vue ne sont pas disponibles pour un simple programme informatique installé dans presque tout le monde. Cependant, il est. Aujourd'hui, nous avons déjà couvert le niveau d'utilisation avancé. Ce sujet n'est pas si simple, mais comme on dit, la route sera maîtrisée par celui qui marche. L'essentiel est de suivre le plan d'action et d'effectuer avec précision toutes les actions indiquées ci-dessus. Ensuite, il n'y aura pas d'erreurs et le programme effectuera indépendamment tous les calculs nécessaires. Il n'y aura pas besoin de réfléchir à la fonction à utiliser et ainsi de suite.