Χρήση του Excel για επίλυση οποιουδήποτε σύνθετου μοντέλου
Ο Lev είναι επίτροπος ενός ανταγωνιστικού κολυμβητικού πρωταθλήματος. Γράφει: "Είμαι ο επίτροπος ενός κολυμβητικού πρωταθλήματος. Υπάρχουν οκτώ ομάδες φέτος. Κάθε ομάδα φιλοξενεί μία συνάντηση και είναι η γηπεδούχος ομάδα. Μια συνάντηση θα έχει 4 ή 5 ομάδες. Πώς να οργανώσετε το πρόγραμμα ώστε κάθε ομάδα να κολυμπήσει κάθε άλλη ομάδα δύο φορές; Στο παρελθόν, όταν είχαμε 5, 6 ή 7 ομάδες, θα μπορούσα να το λύσω πατώντας το F9 μέχρι το κλείσιμο. Αλλά φέτος, με 8 ομάδες, δεν βγαίνει. "
Ένας από τους περιορισμούς είναι ότι ορισμένες πισίνες προσφέρουν μόνο 4 λωρίδες, επομένως μπορείτε να έχετε μόνο 4 ομάδες όταν αυτή η πισίνα φιλοξενεί το γκαλά. Για άλλες πισίνες, μπορεί να έχουν 5, 6 ή περισσότερες λωρίδες, αλλά η ιδανική συνάντηση θα έχει την γηπεδούχος ομάδα και τέσσερις άλλες.
Η πρότασή μου: Πατήστε F9 πιο γρήγορα! Για να το βοηθήσετε: αναπτύξτε ένα "μέτρο εγγύτητας" στο μοντέλο σας. Με αυτόν τον τρόπο, όταν πατάτε το F9, μπορείτε να παρακολουθείτε έναν αριθμό. Όταν βρείτε μια "καλύτερη" λύση από την καλύτερη που έχετε βρει, αποθηκεύστε την ως την ενδιάμεση καλύτερη λύση.
Βήματα ειδικά για το πρόβλημα κολύμβησης
- Λίστα των 8 γηπεδούχων ομάδων στην κορυφή.
- Πόσοι τρόποι για να γεμίσετε τις άλλες 4 λωρίδες;
- Λίστα όλων των τρόπων.
- Πόσοι τρόποι για να γεμίσετε τις άλλες 3 λωρίδες (για μικρούς χώρους;). Λίστα όλων των τρόπων.
- Χρησιμοποιήστε το
RANDBETWEEN(1,35)για να επιλέξετε ομάδες για κάθε αγώνα.
Σημειώστε ότι υπάρχουν 35 8 πιθανοί τρόποι για να οργανώσετε τη σεζόν (2,2 τρισεκατομμύρια). Θα ήταν "αδύνατο" να τα κάνουμε όλα με έναν οικιακό υπολογιστή. Εάν υπήρχαν μόνο 4000 δυνατότητες, θα μπορούσατε να τις κάνετε όλες, και αυτό είναι ένα βίντεο για μια άλλη μέρα. Αλλά με 2,2 τρισεκατομμύρια δυνατότητες, η τυχαία εικασία είναι πιο πιθανό να βρει λύσεις.
Αναπτύξτε ένα Μέτρο Εγγύτητας
Στο σενάριο κολύμβησης, το πιο σημαντικό πράγμα είναι ότι κάθε ομάδα κολυμπά δύο φορές έναντι κάθε άλλης ομάδας;
Πάρτε τους τρέχοντες 8 τυχαίους αριθμούς και χρησιμοποιήστε τύπους για να σχεδιάσετε όλους τους αγώνες. Καταγράψτε τις 28 πιθανές αντιστοιχίσεις. Χρησιμοποιήστε το COUNTIFγια να δείτε πόσες φορές συμβαίνει κάθε αντιστοιχία με τους τρέχοντες τυχαίους αριθμούς. Μετρήστε πόσα είναι 2 ή περισσότερα. Ο στόχος είναι να αυξηθεί αυτός ο αριθμός σε 28.
Δευτερεύων στόχος: Υπάρχουν 28 αγώνες. Κάθε πρέπει να συμβεί δύο φορές. Αυτό είναι 56 αγώνες που πρέπει να συμβούν. Με 8 πισίνες και 6 με πέντε λωρίδες, θα έχετε 68 αγώνες. Αυτό σημαίνει ότι μερικές ομάδες θα κολυμπήσουν εναντίον άλλων ομάδων 3 φορές και πιθανώς 4 φορές. Δευτερεύων στόχος: Βεβαιωθείτε ότι όσο το δυνατόν λιγότερες ομάδες έχουν 4 αγώνες. Τριτογενής στόχος: Ελαχιστοποίηση του Μέγ.
Αργός τρόπος επίλυσης αυτού
Πατήστε F9. Κοιτάξτε το αποτέλεσμα. Πατήστε το F9 μερικές φορές για να δείτε ποια αποτελέσματα λαμβάνετε. Όταν έχετε υψηλό αποτέλεσμα, αποθηκεύστε τις 8 εισόδους και τις τρεις μεταβλητές εξόδου. Συνεχίστε να πατάτε το F9 μέχρι να έχετε καλύτερο αποτέλεσμα. Αποθηκεύστε ένα καταγράφοντας τα 8 κελιά εισόδου και τα 3 κελιά αποτελεσμάτων.
Μακροεντολή για να αποθηκεύσετε το τρέχον αποτέλεσμα
Αυτή η μακροεντολή αποθηκεύει τα αποτελέσματα στην επόμενη σειρά.
Sub SaveThis() NR = Range("Z1048576").End(xlUp).Row + 1 Cells(NR, 26).Resize(1, 11).Value = Array(Range("c8").Value, _ Range("D8").Value, Range("E8").Value, Range("F8").Value, _ Range("G8").Value, Range("H8").Value, Range("I8").Value, _ Range("J8").Value, Range("O1").Value, Range("P1").Value, _ Range("Q1").Value) End SubΜακροεντολή για να πατήσετε F9 επανειλημμένα και ελέγξτε τα αποτελέσματα
Γράψτε μια μακροεντολή για να πατήσετε επανειλημμένα το F9, καταγράφοντας μόνο "καλύτερες" λύσεις. Σταματήστε τη μακροεντολή όταν φτάσετε στα επιθυμητά αποτελέσματα των 28 & 0.
Sub TrySome() NR = Range("Z1048576").End(xlUp).Row + 1 Ctr = Range("T1").Value Application.ScreenUpdating = Range("AH2").Value SolutionFound = False GoAgain: ActiveSheet.Calculate Ctr = Ctr + 1 UseIt = 0 If Range("O1").Value> Range("AK1").Value Then UseIt = 1 ElseIf Range("O1").Value = Range("AK1").Value Then If Range("P1").Value 300 Then Application.ScreenUpdating = True Exit Sub End If If SolutionFound = True Then Application.ScreenUpdating = True Exit Sub End If If Ctr Mod 1000 = 0 Then Range("T1").Value = Ctr Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").Value End If GoTo GoAgain End SubΠλευρική γραμμή σχετικά με την Ενημέρωση οθόνης
Πλευρική γραμμή: Στην αρχή, είναι "διασκεδαστικό" να παρακολουθείτε τις επαναλήψεις. Αλλά τελικά συνειδητοποιείτε ότι ίσως χρειαστεί να δοκιμάσετε εκατομμύρια δυνατότητες. Έχοντας ξαναγράψει το Excel η οθόνη επιβραδύνει τη μακροεντολή. Χρησιμοποιήστε το Application.ScreenUpdating = False για να μην βάψετε ξανά την οθόνη.
Κάθε φορά που λαμβάνετε μια νέα απάντηση ή κάθε 1000, αφήστε το Excel να σχεδιάσει ξανά την οθόνη. Πρόβλημα: Το Excel δεν επανασχεδιάζει την οθόνη, εκτός εάν μετακινηθεί ο δείκτης κελιού. Βρήκα ότι επιλέγοντας ένα νέο κελί ενώ το ScreenUpdating είναι αληθινό, το Excel θα ζωγραφίσει ξανά την οθόνη. Αποφάσισα να το εναλλάξ μεταξύ του Counter cell και των καλύτερων αποτελεσμάτων μέχρι στιγμής.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueΕναλλακτικές λύσεις επίλυσης
Σκέφτηκα πολλούς τίτλους για αυτό το βίντεο: Πατήστε F9 Μέχρι Κλείσιμο, Μαντέψτε μέχρι σωστού, Επίλυση Brute Force, Μέτρο εγγύτητας
Σημειώστε ότι προσπάθησα να χρησιμοποιήσω το Solver για να λύσω το πρόβλημα. Αλλά ο Solver δεν μπορούσε να πλησιάσει. Ποτέ δεν ήταν καλύτερο από 26 ομάδες όταν ο στόχος ήταν 28.
Σημειώστε επίσης ότι οποιαδήποτε λύση που έχω σε αυτό το βίντεο είναι "χαζή τύχη". Δεν υπάρχει τίποτα ευφυές για τη μέθοδο επίλυσης. Για παράδειγμα, η μακροεντολή δεν λέει, "Πρέπει να ξεκινήσουμε από την καλύτερη λύση μέχρι τώρα και να κάνουμε κάποιες μικρορυθμίσεις." Ακόμα κι αν λάβετε μια λύση που απέχει μόνο έναν αριθμό, πιέζει τυφλά το F9 ξανά. Υπάρχει πιθανώς ένας πιο έξυπνος τρόπος επίθεσης του προβλήματος. Αλλά … τώρα … για τον επίτροπο κολύμβησης, αυτή η προσέγγιση λειτούργησε.
Πραγματοποιήστε λήψη του βιβλίου εργασίας
Δες το βίντεο
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2180.zip
