Актуальность данной темы заключается в том, что азартные игры и наблюдение за спортивными состязаниями - популярное времяпрепровождение. Я считаю, что они так волнуют, поскольку никогда не знаешь, что случится в следующую минуту. Моделирование методом Монте-Карло представляет собой мощное средство, позволяющее определять вероятность событий в азартных играх и спорте. По сути, мы оцениваем вероятность, многократно воспроизводя азартную или спортивную игру. Если, например, мы с помощью Excel 10 000 раз смоделируем бросание кости и 4900 раз выиграем, то получим вероятность выигрыша, равную 4900/10000 или 49%. Если мы 1000 раз воспроизведем мужской полуфинал НССА, и команда Сиракуз выиграет 300 раз, то мы сможем оценить вероятность победы команды города Сиракуз на чемпионате как равную 300/1000 или 30%.

Целью данной работы является определение вероятности выигрыша при игре в кости, в покер и в баскетбол.

Для решения поставленной цели, необходимо сделать следующее:

1. Изучить правила рассматриваемых игр.

2. Познакомиться с их особенностями.

3. Рассчитать в Excel вероятности выигрышей.

Данный отчет был реализован в компьютерной программе Microsoft Excel, который помимо своих стандартных функций и возможностей позволяет моделировать вероятности событий в азартных играх и спорте.

1. Вероятность победы при игре в кости

Какова вероятность победы при игре в кости?

Чтобы ответить на этот вопрос, необходимо знать правила игры. При игре в кости участники бросают два кубика. Если в сумме выпадает 2, 3 или 12, участник проигрывает. Если - 7 или 11, то он выигрывает. Если выпадает другое число, участник продолжает бросать кости до тех пор, пока не выпадет число, равное числу, выпавшему при первом броске (называемому очком), или семерка. Если очко выпадет раньше семерки, игрок побеждает. Если семерка выпадет раньше очка, игрок проигрывает. Путем сложных вычислений мы можем доказать, что вероятность выигрыша в кости равна 0,493. Для подтверждения мы с помощью Excel многократно смоделируем игру в кости (я это сделал 2000 раз).

В данном примере важно помнить, что мы не знаем, сколько раз придется бросить кости, чтобы закончить игру. Можно доказать, что вероятность того, что игра потребует более 50 бросков, крайне низка, и поэтому мы будем воспроизводить именно 50 бросков костей. После каждого броска мы отслеживаем состояние игры:

· 0 - игра проиграна;

· 1 - игра выиграна;

· 2 - игра продолжается.

В ячейке вывода мы будем отслеживать состояние игры после пятидесятого броска. Проделанная мной работа показана на рис.1.

В ячейке В2 я, воспользовавшись функцией СЛУЧМЕЖДУ (RANDBETWEEN), сгенерировал число на первой кости при первом броске с помощью формулы СЛУЧМЕЖДУ(1;6). Функция СЛУЧМЕЖДУ (RANDBETWEEN) генерирует число, которое с одинаковой вероятностью принимает все значения из диапазона заданных аргументов, и поэтому на каждой кости может с одинаковой (1/6) вероятностью выпасть 1, 2, 3, 4, 5, или 6. Скопировав эту формулу в диапазон В2:AY3, мы сгенерируем 50 бросков кости (рис.1).

Рис.1. Моделирование игры в кости

В диапазоне ячеек В4:AY4 я вычисляю общую сумму цифр на костях для каждого из 50 бросков, копируя из ячейки В4 в С4:AY4 формулу СУММ (В2:В3). В ячейке В5 я определяю состояние игры после первого броска по формуле ЕСЛИ(ИЛИ(В4=2;В4=3;В4=12);0;ЕСЛИ(ИЛИ(В4=7; В4=11);1;2)). Помните: результат, равный 2, 3 или 12, означает проигрыш (в ячейку вводится 0); а результат, равный 7 или 11, означает выигрыш (в ячейку вводится 1); любые другие результаты означают продолжение игры (в ячейку вводится 2).

В ячейке С5 я вычисляю состояние игры после второго броска по формуле ЕСЛИ(ИЛИ(В5=0;В5=1);В5;ЕСЛИ(С4=$В4;1;ЕСЛИ(С4=7;0;2))). Если игра закончилась после первого броска, мы сохраняем состояние игры. Если мы выбросили очко, мы фиксируем победу, вводя 1 в ячейку. Если мы выбросили 7, мы фиксируем проигрыш. В противном случае игра продолжается. Обратите внимание: в этой формуле я добавил знак доллара в ссылке на столбец В ($В4), чтобы гарантировать, что при каждом броске мы проверяем результат на равенство сумме, выпавшей после первого броска. Скопировав эту формулу из ячейки С5 в диапазон D5:AY5, мы определим состояние игры со 2-го по 50-й бросок.

Результат игры из ячейки AY5 скопируем в ячейку С6, чтобы его можно было легко увидеть. Затем при помощи таблицы подстановки с одним параметром воспроизведем игру в кости 2000 раз. В ячейку Е8 введем формулу =С6, чтобы отслеживать финальный итог игры (0 - проигрыш, 1 - выигрыш). Затем выделим диапазон таблицы (D9:E2009) и в меню Данные (DATA) выберем команду Таблица подстановки (Table). В поле Подставлять значение по строкам в (Column Input Cell) я указываю пустую ячейку. После нажатия F9 Excel смоделируем игру в кости 2000 раз.

В ячейке Е8 можно вычислить долю выигрыша во всех смоделированных играх по формуле СРЗНАЧ(Е10:Е2009). Из 2000 интеракций мы выигрывали в 49,5% случаев. Если бы мы провели больше испытаний (скажем 10 000 интеракций) мы бы гораздо точнее вычислили реальную вероятность выигрыша в кости.

2. Вероятность получить три карты одинакового достоинства при игре в пятикарточный покер с обменом

Обычная колода карт содержит 4 карты каждого достоинства - 4 туза, 4 двойки и так далее до четырех королей. Чтобы оценить вероятность получения определенной покерной комбинации, мы назначим тузу значение 1, двойке - 2 и далее по старшинству, так, чтобы валету соответствовало значение 11, даме - 12, королю - 13.

В пятикарточном покере с обменом вам сдают пять карт. Многие вероятности могут быть интересными, однако давайте оценим с помощью моделирования вероятность получения трех карт одинакового достоинства, т.е. получения трех карт одного ранга и отсутствие пар (пара и три карты одного ранга на руках образуют комбинацию «фулл хаус»). Чтобы смоделировать пять сданных карт, мы сделаем следующее (см. рис. 2):

· сопоставим случайное число с каждой картой колоды;

· пяти отобранным картам назначим наименьшие случайные числа. Это обеспечит каждой карте одинаковую вероятность быть отобранной;

· подсчитаем, сколько каких карт (начиная с туза и заканчивая королем) сдано.

Рис.2. Моделирование игры в покер для оценки вероятности сдачи трех карт одного достоинства

Для начала перечислим в ячейках 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 вернет следующие значения рангов:

Число

Ранг (наименьшему числу присваивается ранг 1)

Поскольку 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), мы создаем таблицу подстановки с одним параметром, указывая в поле Подставлять значения по строкам в (Column Input Cell) любую пустую ячейку. Щелкнув ОК, мы смоделируем 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 очков).

В ячейках G5 иG6 мы гарантируем, что победитель каждого полуфинального матча попадает в финал. Результат, превышающий 0, означает, что побеждает команда, указанная в списке первой; в противном случае побеждает команда, указанная последней. Таким образом, в ячейке G5 мы показываем победителя первого матча, воспользовавшись формулой Если(Е4>0;»Канзас»;»Маркетт»). В ячейке G6 мы выводим победителя второго матча с помощью формулы ЕСЛИ(Е8>0;»Сиракьюз»;»Техас»).

В ячейке Н5 мы вводим произвольное число, которое будет использоваться для моделирования результата матча на звание чемпиона. Скопировав из ячейки I5 в ячейку I6 формулу ВПР(G5;$C$4:$D$9;2;ЛОЖЬ), мы получим рейтинги для каждой команды, участвующей в матче на звание чемпиона.

Затем мы в ячейке J5 вычисляем результат матча на звание чемпиона (с очки зрения команды, которая указана первой - в ячейке G5) по формуле НОРМОБР(Н5;I5-I6;10). И наконец, в ячейке К5 мы определяем реального чемпиона по формуле ЕСЛИ(J5>0;G5;G6).

Теперь, как обычно, воспользуемся таблицей подстановки с одним параметром, чтобы пару тысяч раз воспроизвести полуфинал. Победители указаны в диапазоне ячеек М12:М2011. Скопировав из ячейки К12 в диапазон К13:К15 формулу СЧЕТЕСЛИ($M$12:$M$2011;J12)/2000, мы вычислим для каждой команды прогнозируемую вероятность победы: 38% для команды Канзаса, 24% для команды Сиракьюз, 24% для команды Техаса и 14% для команды Маркетта. Эти вероятности можно преобразовать в ставки по следующей формуле:

Ставки против победы команды=	Вероятность проигрыша команды
	Вероятность победы команды

Например, ставки против команды Канзаса - 1,63 к 1:

Это означает, что пари, при котором мы ставим $1 на победу команды Канзаса, и при котором букмекер выплачивает нам $1,63 в случае, если эта команда победит, - справедливое пари. Конечно, букмекеры слегка занижают ставки по таким пари, чтобы гарантировано заработать денег.

Кстати, нашу методологию можно легко расширить для моделирования всего чемпионата НССА. Воспользуйтесь операторами ЕСЛИ (IF), чтобы гарантировать переход победителя в следующий раунд, и добавьте функции ВПР(LOOKUP) для поиска рейтингов команд. В начале чемпионата мы определяем шансы команды Сиракьюз на победу равными 3%.

В этой электронной таблице я прокомментировал свои действия с помощью примечаний (рис. 4). Вот несколько рекомендаций по работе с примечаниями.

· Чтобы добавить примечание, выберите в меню Вставка (Insert) команду Примечание (Comment). В правом верхнем углу ячейки с примечанием появится небольшой красный значок.

· Чтобы изменить примечание, щелкните ячейку с примечанием правой кнопкой мыши и выберите в контекстном меню команду Изменить примечание (Edit Comment).

· Чтобы примечание всегда отображалось на экране, щелкните ячейку с примечанием правой кнопкой и выберите в контекстном меню команду Отобразить примечание (Show Comment). При выборе команды Скрыть примечание (Hide Comment) примечание будет отображаться только при наведении курсора на соответствующую ячейку.

· Чтобы вывести примечания на печать, выберите в меню Файл (File) команду Параметры страницы (Page Setup) и затем перейдите на вкладку Лист (Sheet). После этого укажите, где требуется печатать комментарии - на листе или в конце листа.

Рис.4. Пример примечания

4. Определение среднего размера ставки

1. Какие ставки должны принимать букмекеры на то, что команда Канзаса победит в полуфинале НССА, если они хотят получить в среднем по 10 центов с каждой ставки величиной в 1 доллар?

Решение:
1. Запускаем программу Microsoft Excel: Пуск > Программы > Microsoft Excel.

2. В ячейки А1:А4 вносим данные:

А1: Выплата при победе Канзаса;

А2: Вероятность победы Канзаса;

А3: Вероятность проигрыша Канзаса;

А4: Средняя прибыль букмекера.

3. В ячейки В2 и В3 вводим соответственно вероятности 0,38 и 0,62, которые мы нашли в разделе 3. В ячейку В4 вводим формулу нахождения средней прибыли букмекера, т.е. =В1*(-В2)+В3*1.

4. С помощью Подбора параметра находим выплату при победе Канзаса, устанавливая среднюю прибыль 0,1, которая при этом равна 1,3684. Следовательно, ставки 1,37 к 1 обеспечат букмекеру среднюю прибыль в 10 центов на каждый поставленный доллар.

Рис.5. Моделирование ставки, обеспечивающей среднюю прибыль

Заключение

Программу Excel трудно рассматривать как основной вычислительный инструмент. Однако ее удобно применять не только в тех случаях, когда требуется быстрая обработка больших объемов данных, построение диаграмм и графиков, но и для моделирования вероятностей, а именно в азартных играх и спорте. При достижении основной цели я изучил правила и особенности рассматриваемых мной игр, получил новые знания по совершенствованию компьютерных технологий, а именно:

-при формировании данного отчета возникла необходимость применения сканера, так как некоторую информацию приходилось сканировать;

-в процессе сканирования была постигнута программа Fine Reader, которая позволила наиболее четко разобрать тексты учебных пособий;

-для того, чтобы переместить рисунки из Microsoft Excel в Microsoft Word использовалась клавиша Print Screen. После нажатия данной клавиши изображение фотографируется; редактирование изображения происходило в программе Paint, которая позволяет создавать, просматривать рисунки или отсканированные фотографии.

Список используемой литературы

1. Акулич И.Л. Математическое программирование в примерах и задачах: Учеб. Пособие для студентов эконом. спец. Вузов. - М.: Высш. шк.,1986.

2. А.А. Давыдов. Курс лекций по предмету «ППОК», Губкин, МГОУ, 2005.

3. Уэйн Л. Винстон Microsoft: анализ данных и построение бизнес-моделей / Пер. с англ. - М.: Издательско-торговый дом «Русская редакция», 2005.