Lottery simulation sa Excel

Ang lottery ay hindi paghahanap ng suwerte,

ito ay isang pangangaso para sa mga talunan.

Sa nakakainggit na regularidad (at mas madalas kamakailan), ang mga tao ay sumulat sa akin na humihingi ng tulong sa iba't ibang mga kalkulasyon na may kaugnayan sa mga loterya. May gustong ipatupad ang kanilang sikretong algorithm para sa pagpili ng mga nanalong numero sa Excel, may gustong maghanap ng mga pattern sa mga numerong nawala sa mga nakaraang draw, may gustong mahuli ang mga organizer ng lottery sa isang hindi tapat na laro.

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

Gawain 1. Probabilidad na manalo

Kunin natin ang klasikong Stoloto 6 sa 45 lottery bilang isang halimbawa. Ayon sa mga patakaran, tanging ang mga nahulaan ang lahat ng 10 numero sa 6 ay makakatanggap ng isang super premyo (45 milyong rubles o higit pa kung ang balanse ng pondo ng premyo ay naipon mula sa mga nakaraang draw). Kung nahulaan mo ang 5, makakatanggap ka ng 150 libong rubles, kung 4 - 1500 rubles. , kung 3 numero sa 6, pagkatapos ay 150 rubles, kung 2 numero - ibabalik mo ang 50 rubles na ginugol sa tiket. Hulaan lamang ang isa o wala - kumuha lamang ng mga endorphins mula sa proseso ng laro.

Ang mathematical na posibilidad na manalo ay madaling makalkula gamit ang standard function NUMBERCOMB (COMBINE), na available sa Microsoft Excel para sa ganoong kaso. Kinakalkula ng function na ito ang bilang ng mga kumbinasyon ng N na numero mula sa M. Kaya para sa aming “6 sa 45” na lottery ito ay magiging:

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

… na katumbas ng 8, ang kabuuang bilang ng lahat ng posibleng kumbinasyon sa lottery na ito.

Kung nais mong kalkulahin ang posibilidad para sa isang bahagyang panalo (2-5 na mga numero mula sa 6), pagkatapos ay kailangan mo munang kalkulahin ang bilang ng mga pagpipilian, na katumbas ng produkto ng bilang ng mga kumbinasyon ng mga nahulaan na numero mula sa 6 sa bilang ng mga hindi na-unses na numero mula sa natitirang (45-6) = 39 na numero. Pagkatapos ay hinahati namin ang kabuuang bilang ng lahat ng posibleng kumbinasyon (8) sa natanggap na bilang ng mga panalo para sa bawat opsyon – at nakukuha namin ang mga posibilidad na manalo para sa bawat kaso:

Sa pamamagitan ng paraan, ang posibilidad, halimbawa, na mamatay sa isang pag-crash ng eroplano sa Ating Bansa ay tinatantya sa humigit-kumulang 1 sa isang milyon. At ang posibilidad na manalo sa isang casino sa roulette, ang pagtaya ng lahat sa isang numero ay 1 hanggang 37.

Kung hindi ka napigilan ng lahat ng nasa itaas at handa ka pa ring maglaro, magpatuloy.

Gawain 2. Dalas ng paglitaw ng bawat bilang

Upang magsimula, alamin natin kung gaano kadalas nahuhulog ang ilang mga numero. Sa isang perpektong lottery, na binigyan ng sapat na malaking agwat ng oras para sa pagsusuri, ang lahat ng mga bola ay dapat magkaroon ng parehong posibilidad na mapabilang sa panalong sample. Sa katotohanan, ang mga tampok ng disenyo ng drum ng lottery at ang hugis ng timbang ng mga bola ay maaaring masira ang larawang ito, at para sa ilang mga bola ang posibilidad na mahulog ay maaaring mas mataas/mas mababa kaysa sa iba. Subukan natin ang hypothesis na ito sa pagsasanay.

Kunin natin, halimbawa, ang data sa lahat ng 2020 sa 21 lottery draw na naganap noong 6-45 mula sa website ng kanilang organizer na Stoloto, na idinisenyo sa anyo ng tulad ng isang "matalinong" talahanayan, na maginhawa para sa pagsusuri, na may pangalan tabArchive Circulation. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Upang kalkulahin ang dalas ng paglitaw ng bawat numero, gamitin ang function COUNTIF (COUNTIF) at magdagdag ng isang function dito TEXT (TEXT)upang magdagdag ng mga nangungunang zero at asterisk bago at pagkatapos sa mga single-digit na numero, upang hanapin ng COUNTIF ang paglitaw ng isang numero saanman sa kumbinasyon sa column B. Gayundin, para sa higit na kalinawan, bubuo kami ng tsart ayon sa mga resulta at pag-uuri-uriin ang mga frequency sa pababang pagkakasunud-sunod:

Sa karaniwan, ang anumang bola ay dapat mahulog 1459 draw * 6 na bola / 45 na numero = 194,53 beses (ito mismo ang tinatawag sa mga istatistika математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые числа (10, 21, 6…) выпадали заметно чаще (+15%), а некоторые числа (XNUMX, XNUMX, метро) Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шарто в начоборот .

Gawain 3. Anong mga numero ang matagal nang hindi nabubunot?

Ang isa pang diskarte ay batay sa ideya na may sapat na malaking bilang ng mga draw, maaga o huli ang bawat numero mula sa lahat ng magagamit mula 1 hanggang 45 ay dapat mahulog. Kaya't kung ang ilang mga numero ay hindi lumitaw sa mga nagwagi sa loob ng mahabang panahon ("malamig na mga bola"), lohikal na subukang tumaya sa kanila sa hinaharap. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей para sa 2020-21 год по по удица MAS EXPOSED (MATCH). Maghahanap ito mula sa itaas hanggang sa ibaba (ibig sabihin, mula sa bago hanggang sa lumang mga pagtakbo) upang hanapin ang bawat numero at ibigay ang serial number ng pagtakbo (nagbibilang mula sa katapusan ng taon hanggang sa simula) kung saan huling ibinaba ang numerong ito:

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

Ang isa pang diskarte sa laro ay batay sa pag-aalis ng sikolohikal na kadahilanan kapag nanghuhula ng mga numero. Kapag ang isang manlalaro ay pumipili ng mga numero sa pamamagitan ng pagtaya, hindi niya namamalayan na ginagawa ito nang hindi ganap na makatwiran. Ayon sa istatistika, halimbawa, ang mga numero mula 1 hanggang 31 ay pinili ng 70% na mas madalas kaysa sa iba (paboritong petsa), 13 ay pinili nang mas madalas (damn dosenang), mga numero na naglalaman ng "masuwerteng" pito ay mas madalas na napili, atbp. Ngunit kami ay naglalaro laban sa isang makina (lottery drum) kung saan ang lahat ng mga numero ay pareho, kaya makatuwirang piliin ang mga ito na may parehong mathematical impartiality upang mapantayan ang aming mga pagkakataon. Upang gawin ito, kailangan naming lumikha ng isang generator ng random at - pinaka-mahalaga - hindi umuulit na mga numero sa Excel:

Na gawin ito:

1. Gumawa tayo ng "matalinong" talahanayan na pinangalanan tableGenerator, kung saan ang unang column ay ang aming mga numero mula 1 hanggang 45.
2. Sa pangalawang hanay, ipasok ang timbang para sa bawat numero (kakailanganin namin ito sa ibang pagkakataon). Kung ang lahat ng mga numero ay pantay na mahalaga sa amin at gusto naming piliin ang mga ito na may pantay na posibilidad, kung gayon ang timbang ay maaaring itakda na katumbas ng 1 sa lahat ng dako.
3. Sa ikatlong hanay ginagamit namin ang function SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) isang bagong set ng 45 random na numero ang bubuo, na isinasaalang-alang ang timbang para sa bawat isa sa kanila.
4. Magdagdag tayo ng pang-apat na column, kung saan ginagamit ang function RANGGO (RANGGO) вычислим ранг (позицию в топе) для каждого из чисел.

Ngayon ay nananatiling pumili ng unang anim na numero sa pamamagitan ng ranggo 6 gamit ang function MAS EXPOSED (MATCH):

При нажатии на клавишу F9 ang mga formula sa Excel sheet ay muling kakalkulahin at sa bawat oras na makakakuha tayo ng bagong hanay ng 6 na numero sa mga berdeng selula. Bukod dito, ang mga numero kung saan ang isang mas malaking timbang ay itinakda sa column B ay makakatanggap ng proporsyonal na mas mataas na ranggo at, sa gayon, lilitaw nang mas madalas sa mga resulta ng aming random na sample. Kung ang timbang para sa lahat ng mga numero ay nakatakda sa pareho, ang lahat ng mga ito ay pipiliin na may parehong posibilidad. Sa ganitong paraan nakakakuha tayo ng patas at walang pinapanigan na random number generator na 6 sa 45, ngunit may kakayahang gumawa ng mga pagsasaayos sa randomness ng pamamahagi kung kinakailangan.

Kung magpasya kaming maglaro sa bawat draw hindi sa isa, ngunit, halimbawa, na may dalawang tiket nang sabay-sabay, sa bawat isa ay pipili kami ng mga hindi umuulit na numero, pagkatapos ay maaari lamang kaming magdagdag ng mga karagdagang linya mula sa ibaba hanggang sa berdeng hanay, pagdaragdag ng 6, 12, 18, atbp. sa ranggo. d. ayon sa pagkakabanggit:

Gawain 5. Lottery Simulator sa Excel

Bilang isang apotheosis ng buong paksang ito, lumikha tayo ng isang ganap na simulator ng lottery sa Excel, kung saan maaari mong subukan ang anumang mga diskarte at ihambing ang mga resulta (sa teorya ng pag-optimize, isang bagay na katulad ay tinatawag ding pamamaraan ng Monte Carlo, ngunit ito ay magiging mas simple. para sa atin).

Upang gawing mas malapit sa katotohanan ang lahat hangga't maaari, isipin sandali na ito ay Enero 1, 2022 at mayroon tayong mga draw ngayong taon, kung saan plano nating maglaro. Ipinasok ko ang mga totoong nahulog na numero sa mesa tablTiraži2022, na naghihiwalay sa mga karagdagang iginuhit na numero mula sa isa't isa sa magkakahiwalay na mga hanay para sa kaginhawahan ng mga kasunod na kalkulasyon:

Sa isang hiwalay na sheet Laro lumikha ng isang blangko para sa pagmomodelo sa anyo ng isang "matalinong" talahanayan na may pangalan tabIgra ang sumusunod na anyo:

Dito:

• Sa mga yellow cell sa itaas, itatakda namin para sa macro ang bilang ng mga draw sa 2022 kung saan gusto naming lumahok (1-82) at ang bilang ng mga tiket na aming nilalaro sa bawat draw.
• Ang data para sa unang 11 column (AJ) ay kokopyahin ng macro mula sa 2022 draw sheet.
• Data para sa susunod na anim na column (KP) na kukunin ng macro mula sa sheet Generator, kung saan nagpatupad kami ng random number generator (tingnan ang problema 4 sa itaas).
• Sa column Q, binibilang namin ang bilang ng mga tugma sa pagitan ng mga nahulog na numero at ang nabuo gamit ang function SUMPRODUCT (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли, 50 приз.
• Sa huling column S, isinasaalang-alang namin ang kabuuang resulta ng buong laro bilang isang pinagsama-samang kabuuan upang makita ang dynamics sa proseso.

At upang muling buhayin ang buong istraktura, kailangan namin ng isang maliit na macro. Sa tab developer (Developer) pumili ng isang pangkat Visual Basic o gumamit ng keyboard shortcut Alt+F11. Pagkatapos ay magdagdag ng bagong walang laman na module sa pamamagitan ng menu Ipasok – Module at ipasok ang sumusunod na code doon:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets("Игра") = Set wsNumbers("Игра") = Worksheets Sets wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в 5 гта беро ти количества ("6:1048576"). Tanggalin ang 'очищаем старые данные Para sa t = 1 Para sa iGames Para sa b = 1 Sa iTickets 'копируем выигравшие номера с листа Ти2022жил листа Ти1жи ли листа Ти1жи 1 с люс. (10, 1). Kopyahin ang Destinasyon:=wsGame.Cells(i, 4) 'копируем и вставляем специальной вставкой значений сгенерированныто номера Грыванныто номера Гомера специальной вставкой значений сгенерированныто номера Гс. .PasteEspesyal na Paste:=xlPasteValues ​​i = i + 4 Susunod b Susunod t End Sub  

Nananatili itong ipasok ang nais na mga paunang parameter sa mga dilaw na selula at patakbuhin ang macro Nag-develop – Macros (Developer — Macros) o keyboard shortcut Alt+F8.

Para sa kalinawan, maaari ka ring bumuo ng diagram para sa huling column na may pinagsama-samang kabuuan, na nagpapakita ng pagbabago sa balanse ng pera sa panahon ng laro:

Paghahambing ng iba't ibang estratehiya

Ngayon, gamit ang ginawang simulator, maaari mong subukan ang anumang diskarte sa laro sa mga totoong draw sa 2022 at makita ang mga resultang idudulot nito. Kung maglalaro ka ng 1 tiket sa bawat draw, magiging ganito ang pangkalahatang larawan ng "plum":

Dito:

• Generator ay isang laro kung saan sa bawat draw ay pumipili kami ng mga random na numero na nilikha ng aming generator (na may parehong timbang).
• Paborito ay isang laro kung saan sa bawat draw ay gumagamit kami ng parehong mga numero – ang mga madalas na natalo sa mga draw sa nakalipas na dalawang taon (27, 32, 11, 14, 34, 40).
• Tagalabas – pareho, ngunit ginagamit namin ang pinakabihirang mga drop-down na numero (12, 18, 26, 10, 21, 6).
• Malamig – sa lahat ng mga draw ay gumagamit kami ng mga numero na hindi nahuhulog sa loob ng mahabang panahon (35, 5, 39, 11, 6, 29).

Tulad ng nakikita mo, walang malaking pagkakaiba, ngunit ang random number generator ay kumikilos nang kaunti kaysa sa iba pang "mga diskarte".

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

Paglalaro sa bawat draw na may isang tiket na may random na nabuong mga numero (na may parehong timbang):

Paglalaro ng 10 tiket sa bawat draw na may mga random na nabuong numero (na may parehong timbang):

Paglalaro ng 100 tiket sa bawat draw na may mga random na numero (na may parehong timbang):

Ang mga komento, gaya ng sinasabi nila, ay kalabisan – ang deposit drain ay hindi maiiwasan sa lahat ng kaso 🙂