Развлечения и игры - моделирование вероятности событий в азартных играх и спорте

Для начала перечислим в ячейках D3:D54 все карты колоды: четыре «первых», четыре «вторых» и так далее до четырех «двенадцатых» и четырех «тринадцатых». Затем скопируем из ячейки Е3 в диапазон Е4:Е54 функцию СЛЧИС( ) [RAND( )], чтобы сопоставить с каждой картой колоды случайное число. Скопировав из ячейки С3 в диапазон С4:С54 формулу РАНГ (Е3;$Е$3:$Е$54;1), мы получим упоряд

оченный по возрастанию ряд всех случайных чисел (назовем его рангом числа). Например, на рис.2 видно, что первая из «третьих» карт колоды (строка 11) сопоставлена с 24-м по величине случайным числом (в электронной таблице у вас будут другие результаты, поскольку при ее открытии случайные числа генерируются заново).

Синтаксис функции РАНГ (RANK) – РАНГ (число; ссылка; 1 или 0). Если последний аргумент функции РАНГ (RANK) равен 1, функция возвращает ранг числа в массиве, присваивая первому по величине наименьшему числу ранг 1, второму по величине наименьшему числу – ранг 2 и так далее. Если последний аргумент функции РАНГ (RANK) равен 0, функция возвращает ранг числа в массиве, присваивая первому по величине наибольшему числу ранг 1, второму по величине наибольшему числу – ранг 2 и так далее.

При ранжировании случайных чисел совпадения невозможны (потому что у случайных чисел должны совпасть шестнадцать знаков).

Предположим, например, что мы ранжируем числа 1, 3, 3 и 4 и последний аргумент функции РАНГ (RANK) равен 1. Excel вернет следующие значения рангов:

Поскольку 3 – второе по величине наименьшее число, ему должен быть присвоен ранг 2. Другому числу 3 также будет присвоен ранг 2. Поскольку 4 – четвертое по величине наименьшее число, ему будет присвоен ранг 4.

Скопировав из ячейки В3 в диапазон В4:В7 формулу ВПР (А3; поиск; 2; ЛОЖЬ), мы сдаем пять карт из колоды. Данная формула «сдает» пять карт, соответствующих пяти наименьшим по величине случайным числам (диапазону таблицы поиска C3:D54 присвоено имя поиск). Значение ЛОЖЬ используется в функции ВПР (VLOOKUP) потому, что нам на требуется сортировка рангов по возрастанию.

Назначив имя диапазона карты_на_руках нашим сданным картам (диапазон В3:В7) и скопировав из ячейки J3 в диапазон J4:J15 формулу СЧЕТЕСЛИ (карты_на_руках;I3), мы подсчитаем, сколько каких карт сдано. В ячейке J17 мы определяем, есть ли у нас три карты одного ранга по формуле ЕСЛИ(И(МАКС(J3:J15)=3;СЧЕТЕСЛИ(J3:J15;2)=0);1;0). Эта формула возвращает 1 тогда и только тогда, если в нашу комбинацию попало три карты одинакового достоинства и нет пар.

Далее при помощи таблицы подстановки с одним параметром моделируем 400 покерных комбинаций. В ячейке J19 мы копируем результат из ячейки J17 с помощью формулы =J17. После этого мы выделяем диапазон таблицы I19:J4019. Выбрав из меню Данные (DATA) команду Таблица подстановки (Table), мы создаем таблицу подстановки с одним параметром, указывая в поле Подставлять значения по строкам в (ColumnInputCell) любую пустую ячейку. Щелкнув ОК, мы смоделируем 4000 покерных комбинаций. В ячейке G21мы подсчитываем вероятность сдать три карты одного достоинства по формуле СРЗНАЧ(J20:J4019). Она равна 1,9% (используя основы теории вероятности, можно доказать, что вероятность получения трех карт одного достоинства равна 2,1%).

3. Вероятность победы в спортивных соревнованиях

До розыгрыша суперкубка 2003 г. у команды Окленда было преимущество в 3 очка. Какова была вероятность того, что команда Тампа Бэй победит команду Окланда?

Большое исследование, проведенное моим другом Джеффом Сэгэрином, показало, что число очков преимущества у победителя в университетском, профессиональном баскетбольном матче или матче по американскому футболу подчиняется нормальному распределению; при этом среднее значение равно прогнозу букмекеров, а стандартное отклонение равно 16 очкам для профессионального матча по американскому футболу, 14 очкам для университетского матча по американскому футболу, 12 очкам для профессионального баскетбольного матча и 10 очкам для университетского баскетбольного матча. Следовательно, преимущество, которым команда Окленда выиграла суперкубок, (отрицательное число очков преимущества означает, что команда Окленда проиграла) имеет нормальное распределение по средним, равным 3, и стандартным отклонением, равным 16 очкам. Опять же, чтобы команда Окленда проиграла, у нее должно быть 0 или меньше очков преимущества.

Данную задачу можно решить с помощью функции НОРМРАСП(0;3;16;ИСТИНА). Эта функция показывает, что вероятность проигрыша команды Окленда равна 42,6%. Как известно, команда Тампа Бэй выиграла матч, однако такой результат не был совершенно неожиданным.

Если рассматривать полуфинал чемпионата мужских команд Национальной студенческой спортивной ассоциации (НССА) по баскетболу 2003 г., какова вероятность того, что каждая из команд станет победителем чемпионата?

Используя методологию, где мы с помощью надстройки Поиск решения (Solver) Excel определяли рейтинги спортивных команд, мы можем на основе счета предыдущих игр определять рейтинги университетских баскетбольных команд. Накануне полуфинала чемпионата мужских команд по баскетболу 2003 г. рейтинги команд-участников были такими: команда Сиракьюз – 91,03; Канзаса – 92,76; Маркетта – 89,01; Техаса – 90,66. Зная эти данные, мы можем несколько тысяч раз «отыграть» полуфинал и оценить вероятность победы каждой команды.

Наше среднее значение прогнозируемого числа очков преимущества для принимающей команды равен рейтинг фаворита – рейтинг проигравшего. В полуфинале НССА ни одна команда не играет на своем поле, но если бы она была, нам следовало бы добавить 5 очков к ее рейтингу (в профессиональном баскетболе это 4 очка; в университетском и профессиональном американском футболе – 3 очка). Теперь можно с помощью функции НОРМОБР(NORMINV) смоделировать результаты каждой игры.

Мы вычислили вероятный результат полуфинала 2003 г. на рис. 3. В полуфиналах играли команда Канзаса против команды Маркетта и команда Сиракьюз против команды Техаса.

Рис. 3. Моделирование полуфинала НССА 2003 г.

Введем название и рейтинг каждой команды в диапазон С4:D9. В ячейке F4 мы с помощью функции СЛЧИС() [RAND()] определим случайное число для матча «Маркетт против Канзаса», а в ячейке F8 – случайное число для матча «Сиракьюз против Техаса». Наш смоделированный результат всегда взаимосвязан с командой, указанной вверху списка.

В ячейке Е4 мы определяем результат матча «Канзас против Маркетта» (с точки зрения команды Канзаса) по формуле НОРМОБР(F4;D4-D5;10. Обратите внимание: команда Канзаса имеет преимущество в D4-D5 очков. В ячейке Е8 мы определяем результат матча «Техас против Сиракьюз» (с точки зрения команды Сиракьюз) по формуле НОРМОБР(F8;D8-D9;10) (не забывайте, что стандартное отклонение для победного преимущества в университетских матчах по баскетболу составляет 10 очков).

 Скачать реферат