Використання 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
