Վիճակախաղի մոդելավորում Excel-ում

Վիճակախաղը բախտի որս չէ,

դա պարտվողների որս է:

Նախանձելի օրինաչափությամբ (իսկ վերջին շրջանում ավելի հաճախ) մարդիկ ինձ գրում են՝ խնդրելով օգնություն խնդրել վիճակախաղերի հետ կապված տարբեր հաշվարկներում։ Ինչ-որ մեկը ցանկանում է իրականացնել Excel-ում շահող թվեր ընտրելու իր գաղտնի ալգորիթմը, ինչ-որ մեկը ցանկանում է գտնել օրինաչափություններ անցյալ խաղարկություններից դուրս մնացած թվերում, ինչ-որ մեկը ցանկանում է բռնել վիճակախաղի կազմակերպիչներին անազնիվ խաղի մեջ:

В этой статье мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие из которых, кстати, могут пригодиться и в более прозаических рабочих ситуациях.

Առաջադրանք 1. Հաղթելու հավանականությունը

Որպես օրինակ վերցնենք դասական Stoloto 6-ը 45 վիճակախաղից: Կանոնների համաձայն՝ գերմրցանակ ստանում են միայն նրանք, ովքեր գուշակել են 10-ից բոլոր 6 թվերը (45 միլիոն ռուբլի կամ ավելի, եթե մրցանակային ֆոնդի մնացորդը կուտակվել է նախորդ խաղարկություններից): Եթե ​​գուշակեցիք 5-ը, կստանաք 150 հազար ռուբլի, եթե 4 – 1500 ռուբլի: , եթե 3-ից 6 համար, ապա 150 ռուբլի, եթե 2 համար՝ կվերադարձնեք տոմսի վրա ծախսած 50 ռուբլի։ Գուշակիր միայն մեկը կամ ոչ մեկը. ստացիր միայն էնդորֆիններ խաղի գործընթացից:

Հաղթելու մաթեմատիկական հավանականությունը հեշտությամբ կարելի է հաշվարկել ստանդարտ ֆունկցիայի միջոցով NUMBERCOMB (ՄԻԱՎՈՐԵԼ), որը հասանելի է Microsoft Excel-ում նման դեպքի համար։ Այս ֆունկցիան հաշվարկում է M-ից N թվերի համակցությունների քանակը: Այսպիսով, մեր «6-ը 45-ից» վիճակախաղի համար կլինի.

=ЧИСЛКОМБ(45;6)

… որը հավասար է 8-ի՝ այս վիճակախաղի բոլոր հնարավոր համակցությունների ընդհանուր թիվը:

Եթե ​​ցանկանում եք հաշվարկել մասնակի հաղթանակի հավանականությունը (2-5 թվեր 6-ից), ապա նախ պետք է հաշվարկեք նման տարբերակների քանակը, որը հավասար է գուշակված թվերի համակցությունների քանակի արտադրյալին։ 6 չգուշակված թվերի քանակով մնացած (45-6) = 39 թվեր։ Այնուհետև մենք բաժանում ենք բոլոր հնարավոր կոմբինացիաների ընդհանուր թիվը (8) յուրաքանչյուր տարբերակի համար ստացված շահումների քանակի վրա և ստանում ենք շահելու հավանականությունները յուրաքանչյուր դեպքի համար.

Ի դեպ, մեր երկրում, օրինակ, ավիավթարից զոհվելու հավանականությունը գնահատվում է մոտ 1 միլիոնից։ Իսկ կազինոյում ռուլետկաում հաղթելու հավանականությունը՝ ամեն ինչ մեկ թվի վրա խաղադրույք կատարելով, 1-ից 37 է:

Եթե ​​վերը նշված բոլորը ձեզ չխանգարեցին, և դուք դեռ պատրաստ եք խաղալ հետագա, շարունակեք:

Առաջադրանք 2. Յուրաքանչյուր թվի առաջացման հաճախականությունը

Սկսենք, որոշենք, թե ինչ հաճախականությամբ են ընկնում որոշակի թվեր։ Իդեալական վիճակախաղում, հաշվի առնելով վերլուծության համար բավական մեծ ժամանակային ընդմիջում, բոլոր գնդակները պետք է ունենան նույն հավանականությունը, որ գտնվեն հաղթող նմուշում: Իրականում վիճակախաղի թմբուկի դիզայնի առանձնահատկությունները և գնդակների քաշի ձևը կարող են խեղաթյուրել այս պատկերը, և որոշ գնդակների դեպքում դուրս ընկնելու հավանականությունը կարող է ավելի բարձր/ավելի ցածր լինել, քան մյուսների համար: Փորձենք այս վարկածը գործնականում։

Վերցնենք, օրինակ, բոլոր 2020-ի տվյալները 21 վիճակախաղերից, որոնք տեղի են ունեցել 6-45-ը, իրենց կազմակերպիչ Stoloto-ի կայքից՝ ստեղծված վերլուծության համար հարմար «խելացի» աղյուսակի տեսքով՝ անվանմամբ. tabԱրխիվի շրջանառություն. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достатночная для начала выборка для վերլուծություն:

Յուրաքանչյուր թվի առաջացման հաճախականությունը հաշվարկելու համար օգտագործեք ֆունկցիան COUNTIF (COUNTIF) և դրան մի ֆունկցիա ավելացրեք TEXT (ՏԵՔՍՏ)միանիշ թվերին առաջ և հետո ավելացնել առաջնային զրոներ և աստղանիշներ, որպեսզի COUNTIF-ը փնտրի B սյունակի համակցության մեջ որևէ թվի հայտնվելը: Բացի այդ, ավելի հստակության համար մենք կկառուցենք գծապատկեր ըստ արդյունքների և կդասավորենք հաճախականությունները: նվազման կարգով.

Միջին հաշվով, ցանկացած գնդակ պետք է ընկնի 1459 ոչ-ոքի * 6 գնդակ / 45 թիվ = 194,53 անգամ (սա հենց այդպես է կոչվում վիճակագրության մեջ математическим ожиданем), но хорошо видно, что некоторые числа (27, 32, 11…) անորոշ է (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная маса. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.

Առաջադրանք 3. Ի՞նչ թվեր երկար ժամանակ չեն նկարվել:

Մեկ այլ ռազմավարություն հիմնված է այն գաղափարի վրա, որ բավականաչափ մեծ թվով խաղարկություններով, վաղ թե ուշ բոլոր հասանելիներից յուրաքանչյուր թիվ պետք է դուրս գա 1-ից մինչև 45: Այսպիսով, եթե որոշ թվեր վաղուց չեն հայտնվում հաղթողների մեջ («սառը գնդակներ»), ապա տրամաբանական է ապագայում փորձել խաղադրույք կատարել դրանց վրա: 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21 год по убыванию даты и использовать функцию. ԱՎԵԼԻ ԲԱՑՎԱԾ (ՄԱՏՉ). Այն կփնտրի վերևից ներքև (այսինքն՝ նորից մինչև հին վազք) յուրաքանչյուր համարը որոնելու համար և կտա վազքի սերիական համարը (հաշվելով տարեվերջից մինչև սկիզբ), որտեղ այս թիվը վերջին անգամ հանվել է.

Задача 4. Генератор случайных чисел

Մեկ այլ խաղի ռազմավարություն հիմնված է թվերը գուշակելիս հոգեբանական գործոնի վերացման վրա։ Երբ խաղացողն ընտրում է թվեր՝ կատարելով իր խաղադրույքը, նա ենթագիտակցորեն դա անում է ոչ ամբողջովին ռացիոնալ: Վիճակագրության համաձայն, օրինակ, 1-ից 31 թվերն ընտրվում են 70% ավելի հաճախ, քան մնացածը (սիրելի ամսաթվերը), 13-ն ընտրվում է ավելի քիչ (անիծյալ տասնյակ), ավելի հաճախ ընտրվում են «հաջողակ» յոթը պարունակող թվերը և այլն: Բայց մենք խաղում ենք մեքենայի (վիճակախաղի թմբուկի) դեմ, որի համար բոլոր թվերը նույնն են, ուստի իմաստ ունի ընտրել դրանք նույն մաթեմատիկական անկողմնակալությամբ՝ մեր հնարավորությունները հավասարեցնելու համար։ Դա անելու համար մենք պետք է ստեղծենք պատահական և, ամենակարևորը, չկրկնվող թվերի գեներատոր Excel-ում.

Անել դա:

1. Եկեք ստեղծենք «խելացի» աղյուսակ անունով սեղանի գեներատոր, որտեղ առաջին սյունակը կլինեն մեր թվերը 1-ից մինչև 45:
2. Երկրորդ սյունակում մուտքագրեք յուրաքանչյուր թվի կշիռը (մեզ այն պետք կգա մի փոքր ուշ): Եթե ​​բոլոր թվերը մեզ համար հավասարապես արժեքավոր են, և մենք ցանկանում ենք ընտրել դրանք հավասար հավանականությամբ, ապա ամենուր կշիռը կարող է հավասար լինել 1-ի։
3. Երրորդ սյունակում մենք օգտագործում ենք ֆունկցիան SLCHIS (ՌԱՆԴ), которая в Excel-ում գեներացնում է 0-ից 1-ի վրա, ստեղծվում է XNUMX-ից XNUMX-ից: Таким образом каждый раз при пересчёте ցուցակ (нажатии на клавишу F9) կստեղծվի 45 պատահական թվերի նոր հավաքածու՝ հաշվի առնելով դրանցից յուրաքանչյուրի կշիռը։
4. Ավելացնենք չորրորդ սյունակը, որտեղ օգտագործվում է ֆունկցիան ԱՍՏԻՃԱՆ (ԱՍՏԻՃԱՆ) вычислим ранг (позицию в топе) для каждого из чисел.

Այժմ մնում է կատարել առաջին վեց թվերի ընտրությունը ըստ 6-րդ հորիզոնականի՝ օգտագործելով ֆունկցիան ԱՎԵԼԻ ԲԱՑՎԱԾ (ՄԱՏՉ):

При нажатии на клавишу F9 Excel թերթիկի բանաձևերը կվերահաշվարկվեն և ամեն անգամ կանաչ բջիջներում կստանանք 6 թվերի նոր հավաքածու: Ավելին, այն թվերը, որոնց համար ավելի մեծ կշիռ է սահմանվել B սյունակում, կստանան համամասնորեն ավելի բարձր աստիճան և, հետևաբար, ավելի հաճախ կհայտնվեն մեր պատահական ընտրանքի արդյունքներում: Եթե ​​բոլոր թվերի կշիռը նույնն է, ապա բոլորը կընտրվեն նույն հավանականությամբ։ Այս կերպ մենք ստանում ենք 6-ից 45-ի արդար և անկողմնակալ պատահական թվերի գեներատոր, բայց անհրաժեշտության դեպքում բաշխման պատահականությանը ճշգրտումներ անելու ունակությամբ:

Եթե ​​յուրաքանչյուր խաղարկությունում որոշենք խաղալ ոչ թե մեկով, այլ, օրինակ, միանգամից երկու տոմսով, որոնցից յուրաքանչյուրում կընտրենք չկրկնվող թվեր, ապա կարող ենք պարզապես ներքևից կանաչ միջակայք ավելացնել լրացուցիչ տողեր, աստիճանին ավելացնելով 6, 12, 18 և այլն։ դ. համապատասխանաբար:

Առաջադրանք 5. Վիճակախաղի սիմուլյատոր Excel-ում

Որպես այս ամբողջ թեմայի ապոթեոզ, եկեք ստեղծենք վիճակախաղի լիարժեք սիմուլյատոր Excel-ում, որտեղ կարող եք փորձել ցանկացած ռազմավարություն և համեմատել արդյունքները (օպտիմալացման տեսության մեջ նման բան կոչվում է նաև Մոնտե Կառլոյի մեթոդ, բայց դա ավելի պարզ կլինի մեզ համար).

Որպեսզի ամեն ինչ հնարավորինս մոտ լինի իրականությանը, մի պահ պատկերացրեք, որ 1 թվականի հունվարի 2022-ն է, և մեզ սպասվում են այս տարվա վիճակահանությունները, որոնցում նախատեսում ենք խաղալ։ Աղյուսակում մուտքագրել եմ իրական բաց թողնված թվերը tablTiraži2022, լրացուցիչ գծված թվերը միմյանցից առանձնացնելով առանձին սյունակներում՝ հետագա հաշվարկների հարմարության համար.

Առանձին թերթիկի վրա խաղ ստեղծեք դատարկ մոդելավորման համար «խելացի» աղյուսակի տեսքով անունով tabIgra հետևյալ ձևը.

Այստեղ `

• Վերևի դեղին վանդակներում մենք մակրոյի համար կսահմանենք 2022 թվականի ոչ-ոքիների քանակը, որոնց ցանկանում ենք մասնակցել (1-82) և յուրաքանչյուր խաղարկության ժամանակ մեր խաղարկվող տոմսերի քանակը:
• Առաջին 11 սյունակների (AJ) տվյալները կպատճենվեն մակրոյով 2022 թվականի վիճակահանության թերթիկից:
• Հաջորդ վեց սյունակների (KP) տվյալները, որոնք մակրոները կվերցնեն թերթիկից Գեներատոր, որտեղ մենք իրականացրել ենք պատահական թվերի գեներատոր (տե՛ս վերը նշված խնդիրը 4):
• Q սյունակում մենք հաշվում ենք ընկած թվերի և ֆունկցիայի օգտագործմամբ գեներացված թվերի համընկնումների քանակը SUMPRODUCT- ը (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли, то приз — 50 р. за билет)
• Վերջին S սյունակում մենք դիտարկում ենք ամբողջ խաղի ընդհանուր արդյունքը որպես կուտակային տոտալ՝ գործընթացի դինամիկան տեսնելու համար:

Եվ այս ամբողջ կառույցը վերակենդանացնելու համար մեզ փոքրիկ մակրո է անհրաժեշտ։ Ներդիրի վրա երեվակիչ (մշակող) ընտրիր թիմ Visual Basic կամ օգտագործեք ստեղնաշարի դյուրանցում ալտ+F11. Այնուհետև ցանկի միջոցով ավելացրեք նոր դատարկ մոդուլ Ներդիր – մոդուլ և այնտեղ մուտքագրեք հետևյալ կոդը.

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Սահմանել wsGame = Worksheets("Игра") Սահմանել wsNumbers(")Герets = Worksheets" wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'перваблиа Rowska. ("6:1048576").Ջնջել 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тиражи 2022 и вставляем на лист Игра wsArchive.Rechive.Resells1(t). (1, 1).Պատճենել նպատակակետը՝=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор wsNumbers.Range("G1:L4").Պատճենել wssGame,C. .PasteSpecial Paste:=xlPasteValues ​​i = i + 4 Հաջորդ b Հաջորդ t Վերջ Ենթ.  

Մնում է մուտքագրել ցանկալի սկզբնական պարամետրերը դեղին բջիջներում և մակրո անցնել Մշակող – Մակրոներ (Մշակող — մակրո) կամ ստեղնաշարի դյուրանցում ալտ+F8.

Պարզության համար կարող եք նաև կառուցել վերջին սյունակի դիագրամ՝ կուտակային ընդհանուր գումարով, որն արտացոլում է խաղի ընթացքում դրամական մնացորդի փոփոխությունը.

Տարբեր ռազմավարությունների համեմատություն

Այժմ, օգտագործելով ստեղծված սիմուլյատորը, կարող եք 2022 թվականին իրական խաղարկությունների վրա փորձարկել ցանկացած խաղի ռազմավարություն և տեսնել այն արդյունքները, որոնք դա կբերի: Եթե ​​յուրաքանչյուր խաղարկությունում խաղում եք 1 տոմս, ապա «սալորի» ընդհանուր պատկերն այսպիսի տեսք ունի.

Այստեղ `

• Գեներատոր խաղ է, որտեղ յուրաքանչյուր խաղարկության ժամանակ մենք ընտրում ենք մեր գեներատորի կողմից ստեղծված պատահական թվեր (նույն քաշով):
• Էջանշան խաղ է, որտեղ յուրաքանչյուր խաղարկության ժամանակ մենք օգտագործում ենք նույն թվերը. նրանք, որոնք ամենից հաճախ դուրս են եկել ոչ-ոքիներից վերջին երկու տարվա ընթացքում (27, 32, 11, 14, 34, 40):
• դրսի – նույնը, բայց մենք օգտագործում ենք ամենահազվադեպ բացվող թվերը (12, 18, 26, 10, 21, 6):
• Ցուրտ – բոլոր խաղարկություններում մենք օգտագործում ենք երկար ժամանակ չընկնող թվեր (35, 5, 39, 11, 6, 29):

Ինչպես տեսնում եք, մեծ տարբերություն չկա, բայց պատահական թվերի գեներատորն իրեն մի փոքր ավելի լավ է պահում, քան մյուս «ռազմավարությունները»:

Можно также попробовать играть большим количеством билетов каждом тираже, чтобы перекрыть много количество вариантов (иногда для этого несколько игроков объединяются в группу).

Յուրաքանչյուր խաղարկությունում խաղում մեկ տոմսով պատահականորեն ստեղծված թվերով (նույն քաշով).

Յուրաքանչյուր խաղարկությունում 10 տոմս խաղալը պատահականորեն ստեղծված թվերով (նույն քաշով).

Յուրաքանչյուր խաղարկության 100 տոմս խաղալ պատահական թվերով (նույն քաշով).

Մեկնաբանությունները, ինչպես ասում են, ավելորդ են. ավանդի արտահոսքն անխուսափելի է բոլոր դեպքերում 🙂