Использование 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 перерисовывает экран. Я решил, что он будет чередоваться между ячейкой счетчика и лучшими результатами на данный момент.
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
