Міністерство освіти і науки Російської Федерації
МІНІСТЕРСТВО ОСВІТИ
Реферат
на тему: «Розваги та ігри: моделювання ймовірності подій в азартних іграх і спорт»
Губкін 2006
Зміст
Введення
1. Вірогідність перемоги при грі в кості
2. Вірогідність отримати три карти однакової гідності при грі в п'ятикартковий покер з обміном
3. Вірогідність перемоги у спортивних змаганнях
4. Визначення середнього розміру ставки
Висновок
Список використаної літератури
Введення
Даний звіт виконаний на тему: «Розваги та ігри: моделювання ймовірності подій в азартних іграх і спорт».
Актуальність даної теми полягає в тому, що азартні ігри і спостереження за спортивними змаганнями - популярне проведення часу. Я вважаю, що вони так хвилюють, оскільки ніколи не знаєш, що станеться в наступну хвилину. Моделювання методом Монте-Карло представляє собою потужний засіб, що дозволяє визначати ймовірність подій в азартних іграх та спорті. По суті, ми оцінюємо ймовірність, багаторазово відтворюючи азартну або спортивну гру. Якщо, наприклад, ми за допомогою 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 поверне наступні значення рангів:
Оскільки 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 долар?
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.
МІНІСТЕРСТВО ОСВІТИ
Реферат
на тему: «Розваги та ігри: моделювання ймовірності подій в азартних іграх і спорт»
Губкін 2006
Зміст
Введення
1. Вірогідність перемоги при грі в кості
2. Вірогідність отримати три карти однакової гідності при грі в п'ятикартковий покер з обміном
3. Вірогідність перемоги у спортивних змаганнях
4. Визначення середнього розміру ставки
Висновок
Список використаної літератури
Введення
Даний звіт виконаний на тему: «Розваги та ігри: моделювання ймовірності подій в азартних іграх і спорт».
Актуальність даної теми полягає в тому, що азартні ігри і спостереження за спортивними змаганнями - популярне проведення часу. Я вважаю, що вони так хвилюють, оскільки ніколи не знаєш, що станеться в наступну хвилину. Моделювання методом Монте-Карло представляє собою потужний засіб, що дозволяє визначати ймовірність подій в азартних іграх та спорті. По суті, ми оцінюємо ймовірність, багаторазово відтворюючи азартну або спортивну гру. Якщо, наприклад, ми за допомогою 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) |
| 1 3 3 4 | 1 2 2 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 на перемогу команди Канзасу, і при якому букмекер виплачує нам $ 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.