Περιεχόμενα
Διατύπωση του προβλήματος
Ας υποθέσουμε ότι η εταιρεία όπου εργάζεστε έχει τρεις αποθήκες, από όπου τα εμπορεύματα πηγαίνουν σε πέντε από τα καταστήματά σας διάσπαρτα σε όλη τη Μόσχα.
Κάθε κατάστημα είναι σε θέση να πουλήσει μια συγκεκριμένη ποσότητα αγαθών που είναι γνωστά σε εμάς. Κάθε μία από τις αποθήκες έχει περιορισμένη χωρητικότητα. Το καθήκον είναι να επιλέξετε ορθολογικά από ποια αποθήκη σε ποια καταστήματα θα παραδώσετε τα αγαθά, ώστε να ελαχιστοποιήσετε το συνολικό κόστος μεταφοράς.
Πριν ξεκινήσετε τη βελτιστοποίηση, θα χρειαστεί να συντάξετε έναν απλό πίνακα σε ένα φύλλο Excel - το μαθηματικό μας μοντέλο που περιγράφει την κατάσταση:
Εννοείται ότι:
- Ο ανοιχτό κίτρινος πίνακας (C4:G6) περιγράφει το κόστος αποστολής ενός είδους από κάθε αποθήκη σε κάθε κατάστημα.
- Τα μωβ κελιά (C15:G14) περιγράφουν την ποσότητα των αγαθών που απαιτείται για κάθε κατάστημα για πώληση.
- Τα ερυθρά κελιά (J10:J13) εμφανίζουν τη χωρητικότητα κάθε αποθήκης – τη μέγιστη ποσότητα αγαθών που μπορεί να χωρέσει η αποθήκη.
- Τα κίτρινα (C13:G13) και τα μπλε (H10:H13) κελιά είναι τα άθροισμα γραμμών και στηλών για τα πράσινα κελιά, αντίστοιχα.
- Το συνολικό κόστος αποστολής (J18) υπολογίζεται ως το άθροισμα των προϊόντων του αριθμού των εμπορευμάτων και των αντίστοιχων εξόδων αποστολής – για τον υπολογισμό, η συνάρτηση χρησιμοποιείται εδώ ΑΝΤΙΠΡΟΣΩΠΟΣ (SUMPRODUCT).
Έτσι, το καθήκον μας περιορίζεται στην επιλογή των βέλτιστων τιμών των πράσινων κυττάρων. Και έτσι ώστε το συνολικό ποσό για τη γραμμή (μπλε κελιά) να μην υπερβαίνει τη χωρητικότητα της αποθήκης (κόκκινα κελιά), και ταυτόχρονα κάθε κατάστημα να λαμβάνει την ποσότητα των αγαθών που χρειάζεται να πουλήσει (το ποσό για κάθε κατάστημα στο τα κίτρινα κελιά πρέπει να είναι όσο το δυνατόν πιο κοντά στις απαιτήσεις – μωβ κελιά).
Λύση
Στα μαθηματικά, τέτοια προβλήματα επιλογής της βέλτιστης κατανομής των πόρων έχουν διατυπωθεί και περιγράφεται εδώ και πολύ καιρό. Και, φυσικά, οι τρόποι επίλυσής τους έχουν αναπτυχθεί εδώ και καιρό όχι με αμβλεία απαρίθμηση (η οποία είναι πολύ μεγάλη), αλλά σε πολύ μικρό αριθμό επαναλήψεων. Το Excel παρέχει στο χρήστη μια τέτοια λειτουργικότητα χρησιμοποιώντας ένα πρόσθετο. Αναζήτηση λύσεων (Διαλύτης) από την καρτέλα ημερομηνία (Ημερομηνία):
Εάν στην καρτέλα ημερομηνία Το Excel σας δεν έχει τέτοια εντολή – δεν πειράζει – σημαίνει ότι το πρόσθετο απλώς δεν έχει συνδεθεί ακόμα. Για να το ενεργοποιήσετε ανοίξτε Αρχεία, Στη συνέχεια, επιλέξτε παράμετροι - Πρόσθετα - Σχετικα (Επιλογές — Πρόσθετα — Μετάβαση σε). Στο παράθυρο που ανοίγει, τσεκάρουμε το πλαίσιο δίπλα στη γραμμή που χρειαζόμαστε Αναζήτηση λύσεων (Διαλύτης).
Ας τρέξουμε το πρόσθετο:
Σε αυτό το παράθυρο, πρέπει να ορίσετε τις ακόλουθες παραμέτρους:
- Βελτιστοποιήστε τη λειτουργία στόχου (Σετ tχρήματα κύτταρο) – εδώ είναι απαραίτητο να υποδείξουμε τον τελικό βασικό στόχο της βελτιστοποίησής μας, δηλαδή ροζ κουτί με το συνολικό κόστος αποστολής (J18). Το κελί-στόχος μπορεί να ελαχιστοποιηθεί (αν πρόκειται για έξοδα, όπως στην περίπτωσή μας), να μεγιστοποιηθεί (αν είναι, για παράδειγμα, κέρδος) ή να προσπαθήσουμε να το φέρουμε σε μια δεδομένη τιμή (για παράδειγμα, να ταιριάζει ακριβώς στον προϋπολογισμό που έχει διατεθεί).
- Αλλαγή μεταβλητών κελιών (By αλλαγή κύτταρα) – εδώ υποδεικνύουμε τα πράσινα κελιά (C10: G12), μεταβάλλοντας τις τιμές των οποίων θέλουμε να επιτύχουμε το αποτέλεσμά μας – το ελάχιστο κόστος παράδοσης.
- Συνεπής με περιορισμούς (Θέμα προς την ο Περιορισμοί) – μια λίστα περιορισμών που πρέπει να λαμβάνονται υπόψη κατά τη βελτιστοποίηση. Για να προσθέσετε περιορισμούς στη λίστα, κάντε κλικ στο κουμπί Πρόσθεση (Προσθήκη) και εισάγετε τη συνθήκη στο παράθυρο που εμφανίζεται. Στην περίπτωσή μας, αυτός θα είναι ο περιορισμός ζήτησης:
και όριο στον μέγιστο όγκο αποθηκών:
Εκτός από τους προφανείς περιορισμούς που σχετίζονται με φυσικούς παράγοντες (χωρητικότητα αποθηκών και μεταφορικών μέσων, περιορισμοί προϋπολογισμού και χρόνου, κ.λπ.), μερικές φορές είναι απαραίτητο να προστεθούν περιορισμοί «ειδικοί για το Excel». Έτσι, για παράδειγμα, το Excel μπορεί εύκολα να κανονίσει να «βελτιστοποιήσετε» το κόστος παράδοσης προσφέροντας τη μεταφορά αγαθών από τα καταστήματα πίσω στην αποθήκη – το κόστος θα γίνει αρνητικό, δηλαδή θα έχουμε κέρδος! 🙂
Για να μην συμβεί αυτό, είναι καλύτερο να αφήσετε το πλαίσιο ελέγχου ενεργοποιημένο. Κάντε τις απεριόριστες μεταβλητές μη αρνητικές ή ακόμη και μερικές φορές ρητά καταχωρίστε τέτοιες στιγμές στη λίστα των περιορισμών.
Αφού ρυθμίσετε όλες τις απαραίτητες παραμέτρους, το παράθυρο θα πρέπει να μοιάζει με αυτό:
Στην αναπτυσσόμενη λίστα Επιλογή μεθόδου επίλυσης, πρέπει επιπλέον να επιλέξετε την κατάλληλη μαθηματική μέθοδο για την επίλυση μιας επιλογής τριών επιλογών:
- Μέθοδος Simplex είναι μια απλή και γρήγορη μέθοδος επίλυσης γραμμικών προβλημάτων, δηλαδή προβλημάτων όπου η έξοδος εξαρτάται γραμμικά από την είσοδο.
- Γενική μέθοδος υποβαθμισμένης κλίσης (OGG) – για μη γραμμικά προβλήματα, όπου υπάρχουν πολύπλοκες μη γραμμικές εξαρτήσεις μεταξύ δεδομένων εισόδου και εξόδου (για παράδειγμα, η εξάρτηση των πωλήσεων από το κόστος διαφήμισης).
- Εξελικτική αναζήτηση λύσης – μια σχετικά νέα μέθοδος βελτιστοποίησης που βασίζεται στις αρχές της βιολογικής εξέλιξης (γεια σου Δαρβίνο). Αυτή η μέθοδος λειτουργεί πολλές φορές περισσότερο από τις δύο πρώτες, αλλά μπορεί να λύσει σχεδόν οποιοδήποτε πρόβλημα (μη γραμμικό, διακριτό).
Το καθήκον μας είναι σαφώς γραμμικό: παραδόθηκε 1 τεμάχιο – ξοδέψαμε 40 ρούβλια, παραδόθηκαν 2 τεμάχια – ξοδέψαμε 80 ρούβλια. κ.λπ., επομένως η μέθοδος simplex είναι η καλύτερη επιλογή.
Τώρα που εισάγονται τα δεδομένα για τον υπολογισμό, πατήστε το κουμπί Βρες μια λύση (Λύσει)για να ξεκινήσετε τη βελτιστοποίηση. Σε σοβαρές περιπτώσεις με πολλά μεταβαλλόμενα κελιά και περιορισμούς, η εύρεση λύσης μπορεί να διαρκέσει πολύ (ειδικά με την εξελικτική μέθοδο), αλλά η εργασία μας για το Excel δεν θα είναι πρόβλημα – σε λίγα λεπτά θα έχουμε τα ακόλουθα αποτελέσματα :
Προσέξτε πόσο ενδιαφέροντα κατανεμήθηκαν οι όγκοι προμήθειας μεταξύ των καταστημάτων, ενώ δεν ξεπερνούσαν τη χωρητικότητα των αποθηκών μας και ικανοποιώντας όλα τα αιτήματα για τον απαιτούμενο αριθμό εμπορευμάτων για κάθε κατάστημα.
Εάν η λύση που βρήκαμε μας ταιριάζει, τότε μπορούμε να την αποθηκεύσουμε ή να επιστρέψουμε στις αρχικές τιμέςκαι να προσπαθήσουμε ξανά με άλλες παραμέτρους. Μπορείτε επίσης να αποθηκεύσετε τον επιλεγμένο συνδυασμό παραμέτρων ως σενάριο. Κατόπιν αιτήματος του χρήστη, το Excel μπορεί να δημιουργήσει τρεις τύπους Εκθέσεις σχετικά με το πρόβλημα που επιλύεται σε ξεχωριστά φύλλα: μια αναφορά για τα αποτελέσματα, μια αναφορά για τη μαθηματική σταθερότητα της λύσης και μια αναφορά για τα όρια (περιορισμούς) της λύσης, ωστόσο, στις περισσότερες περιπτώσεις, ενδιαφέρουν μόνο ειδικούς .
Υπάρχουν, ωστόσο, περιπτώσεις όπου το Excel δεν μπορεί να βρει την κατάλληλη λύση. Είναι δυνατή η προσομοίωση μιας τέτοιας περίπτωσης αν υποδείξουμε στο παράδειγμά μας τις απαιτήσεις των καταστημάτων σε ποσότητα μεγαλύτερη από τη συνολική χωρητικότητα των αποθηκών. Στη συνέχεια, κατά την εκτέλεση μιας βελτιστοποίησης, το Excel θα προσπαθήσει να πλησιάσει όσο το δυνατόν περισσότερο τη λύση και, στη συνέχεια, θα εμφανίσει ένα μήνυμα ότι η λύση δεν μπορεί να βρεθεί. Ωστόσο, ακόμη και σε αυτήν την περίπτωση, έχουμε πολλές χρήσιμες πληροφορίες – συγκεκριμένα, μπορούμε να δούμε τους «αδύναμους κρίκους» των επιχειρηματικών διαδικασιών μας και να κατανοήσουμε τους τομείς προς βελτίωση.
Το εξεταζόμενο παράδειγμα, φυσικά, είναι σχετικά απλό, αλλά εύκολα κλιμακώνεται για να λύσει πολύ πιο σύνθετα προβλήματα. Για παράδειγμα:
- Βελτιστοποίηση της κατανομής των οικονομικών πόρων ανά θέση δαπάνης στο επιχειρηματικό σχέδιο ή τον προϋπολογισμό του έργου. Οι περιορισμοί, σε αυτή την περίπτωση, θα είναι το ύψος της χρηματοδότησης και ο χρόνος του έργου και ο στόχος της βελτιστοποίησης είναι η μεγιστοποίηση των κερδών και η ελαχιστοποίηση του κόστους του έργου.
- Βελτιστοποίηση προγραμματισμού εργαζομένων προκειμένου να ελαχιστοποιηθεί το μισθολογικό ταμείο της επιχείρησης. Περιορισμοί, σε αυτή την περίπτωση, θα είναι οι επιθυμίες κάθε εργαζόμενου σύμφωνα με το ωράριο απασχόλησης και τις απαιτήσεις του πίνακα στελέχωσης.
- Βελτιστοποίηση επενδυτικών επενδύσεων – την ανάγκη σωστής κατανομής κεφαλαίων μεταξύ πολλών τραπεζών, τίτλων ή μετοχών επιχειρήσεων προκειμένου, και πάλι, να μεγιστοποιηθούν τα κέρδη ή (αν είναι πιο σημαντικό) να ελαχιστοποιηθούν οι κίνδυνοι.
Σε κάθε περίπτωση, πρόσθετο Αναζήτηση λύσεων (Διαλύτης) είναι ένα πολύ ισχυρό και όμορφο εργαλείο του Excel και αξίζει της προσοχής σας, καθώς μπορεί να σας βοηθήσει σε πολλές δύσκολες καταστάσεις που έχετε να αντιμετωπίσετε στις σύγχρονες επιχειρήσεις.