Използване на Excel за решаване на всеки сложен модел
Лев е комисар на състезателна лига по плуване. Той пише: "Аз съм комисар на плувна лига. Тази година има осем отбора. Всеки отбор е домакин на една среща и е домакин. Срещата ще има 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 е True, Excel ще пребоядиса екрана. Реших да го редувам между клетката Counter и най-добрите резултати досега.
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 До затваряне, Познайте до правилно, Решаване на груба сила, Мярка за близост
Имайте предвид, че аз се опитах да използвам Solver за решаване на проблема. Но Солвър не можа да се доближи. Никога не стана по-добре от 26 отбора, когато целта беше 28.
Също така имайте предвид, че всяко решение, което получавам в това видео, е „тъп късмет“. В метода за решаване няма нищо интелигентно. Например макросът не казва: „Трябва да започнем от най-доброто решение до момента и да направим някои микрокоригирания.“ Дори ако имате гета решение, което е само на едно число, той отново натиска сляпо F9. Вероятно има по-интелигентен начин за атака на проблема. Но … точно сега … за нашия комисар по плуване този подход проработи.
Изтеглете работната книга
Гледам видео
Свали файл
Изтеглете примерния файл тук: Podcast2180.zip
