Мадэляванне латарэі ў Excel

Латарэя - не паляванне на ўдачу,

гэта паляванне на няўдачнікаў.

З зайздроснай рэгулярнасцю (а апошнім часам усё часцей) мне пішуць людзі з просьбай дапамагчы ў розных разліках, звязаных з латарэямі. Хтосьці хоча рэалізаваць свой сакрэтны алгарытм выбару выйгрышных нумароў у Excel, хтосьці хоча знайсці заканамернасці ў ліках, якія выпалі з мінулых розыгрышаў, хтосьці хоча злавіць арганізатараў латарэі на несумленнай гульні.

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

Задача 1. Верагоднасць выйгрышу

У якасці прыкладу возьмем класічную латарэю «Сталата 6 з 45». Згодна з правіламі, толькі той, хто адгадаў усе 10 нумароў з 6, атрымлівае суперпрыз (45 мільёнаў рублёў і больш, калі астатак прызавога фонду назапашаны з папярэдніх розыгрышаў). Калі вы адгадалі 5, то атрымаеце 150 тысяч рублёў, калі 4 – 1500 рублёў. , калі 3 лічбы з 6, то 150 рублёў, калі 2 лічбы – вяртаюць 50 рублёў, патрачаных на квіток. Адгадайце толькі адну або ніводнай - атрымлівайце ад працэсу гульні толькі эндарфіны.

Матэматычную верагоднасць выйгрышу можна лёгка вылічыць з дапамогай стандартнай функцыі ЛІЧБАР (КАМБІНАВАЦЬ), які даступны ў Microsoft Excel для такога выпадку. Гэтая функцыя вылічвае колькасць камбінацый N лікаў з M. Такім чынам, для нашай латарэі «6 з 45» гэта будзе:

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

… што роўна 8, агульнай колькасці ўсіх магчымых камбінацый у гэтай латарэі.

Калі вы хочаце разлічыць верагоднасць частковага выйгрышу (2-5 лікаў з 6), то вам спачатку трэба будзе палічыць колькасць такіх варыянтаў, роўнае здабытку колькасці камбінацый адгаданых лікаў з 6 па колькасці неадгаданых лікаў з астатніх (45-6) = 39 лікаў. Затым дзелім агульную колькасць усіх магчымых камбінацый (8) на атрыманую колькасць выйгрышаў па кожнай опцыі – і атрымліваем верагоднасці выйгрышу для кожнага выпадку:

Дарэчы, верагоднасць, напрыклад, загінуць у авіякатастрофе ў нашай краіне ацэньваецца прыкладна ў 1 на мільён. А верагоднасць выйгрышу ў казіно ў рулетку, паставіўшы ўсё на адно лік, - 1 да 37.

Калі ўсё вышэйпералічанае вас не спыніла і вы ўсё яшчэ гатовыя гуляць далей, працягвайце.

Заданне 2. Частата сустракаемасці кожнай лічбы

Для пачатку вызначым, з якой частатой выпадаюць тыя ці іншыя лічбы. У ідэальнай латарэі з улікам дастаткова вялікага інтэрвалу часу для аналізу ўсе шары павінны мець аднолькавую верагоднасць апынуцца ў выйгрышнай выбарцы. У рэчаіснасці канструктыўныя асаблівасці латарэйнага барабана і вага-форма шароў могуць сказіць гэтую карціну, і для некаторых шароў верагоднасць выпадзення можа быць вышэй/менш, чым для іншых. Давайце праверым гэтую гіпотэзу на практыцы.

Возьмем, напрыклад, дадзеныя аб усіх 2020 з 21 розыгрышаў латарэі, якія адбыліся ў 6-45 з сайта іх арганізатара Столото, аформленага ў выглядзе такой зручнай для аналізу «разумнай» табліцы з назвай укладкаАрхіў Тыраж. Розыгрышы праходзяць два раза ў дзень (у 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. Давайце створым «разумную» табліцу з імем TableGenerator, дзе ў першым слупку будуць нашы лічбы ад 1 да 45.
2. У другім слупку ўпісваем вага кожнай лічбы (ён спатрэбіцца нам крыху пазней). Калі ўсе лікі аднолькава важныя для нас і мы хочам выбраць іх з аднолькавай верагоднасцю, то вага можа быць усталявана роўным 1 усюды.
3. У трэцім слупку мы выкарыстоўваем функцыю СЛЧЫС (РАНД), якая ў Excel генеруе выпадковае дробнае лік ад 0 да 1, дадаўшы да яго вес з папярэдняга столбца. Такім чынам кожны раз пры перасчытванні ліста (нажаці на клавішу F9) будзе згенераваны новы набор з 45 выпадковых лікаў з улікам вагі кожнага з іх.
4. Давайце дадамо чацвёрты слупок, дзе будзе выкарыстоўвацца функцыя RANK (РАНГ) вычислим ранг (пазіцыю ў топе) для кожнага з чысла.

Цяпер засталося зрабіць выбар першых шасці лікаў па рангу 6 з дапамогай функцыі БОЛЬШ ВЫКЛЮЧАНЫ (МАТЧ):

При нажатии на клавишу F9 формулы на лісце Excel будуць пералічаны, і кожны раз мы будзем атрымліваць новы набор з 6 лікаў у зялёных клетках. Больш за тое, лічбы, для якіх у слупку B быў усталяваны большы вага, атрымаюць прапарцыйна большы ранг і, такім чынам, часцей з'яўляюцца ў выніках нашай выпадковай выбаркі. Калі вага для ўсіх нумароў усталяваны аднолькавы, то ўсе яны будуць выбраны з аднолькавай верагоднасцю. Такім чынам мы атрымліваем справядлівы і бесстаронні генератар выпадковых лікаў 6 з 45, але з магчымасцю карэкціраваць выпадковасць размеркавання, калі неабходна.

Калі мы вырашылі гуляць у кожным розыгрышы не адным, а, напрыклад, адразу двума білетамі, у кожным з якіх будзем выбіраць нумары, якія не паўтараюцца, то можна проста дадаць дадатковыя лініі знізу ў зялёны дыяпазон, дабаўленне да рангу 6, 12, 18 і г.д. d. адпаведна:

Заданне 5. Сімулятар латарэі ў Excel

У якасці апафеозу ўсёй гэтай тэмы давайце створым паўнавартасны сімулятар латарэі ў Excel, дзе можна будзе апрабаваць любыя стратэгіі і параўнаць вынікі (у тэорыі аптымізацыі нешта падобнае таксама называецца метадам Монтэ-Карла, але так будзе прасцей для нас).

Каб усё было максімальна набліжана да рэальнасці, уявім на імгненне, што цяпер 1 студзеня 2022 года і наперадзе нас чакаюць розыгрышы гэтага года, у якіх мы плануем згуляць. Я ўвёў у табліцу рэальныя выпушчаныя лічбы tablTiraži2022, аддзяліўшы дадаткова выцягнутыя лічбы адзін ад аднаго ў асобныя слупкі для зручнасці наступных разлікаў:

На асобным аркушы Гульня стварыць нарыхтоўку для мадэлявання ў выглядзе «разумнай» табліцы з назвай ўкладка Ігра наступная форма:

Тут:

• У жоўтых клетках вышэй мы ўсталюем для макраса колькасць розыгрышаў у 2022 г., у якіх мы хочам прыняць удзел (1-82), і колькасць білетаў, якія мы разыгрываем у кожным розыгрышы.
• Даныя для першых 11 слупкоў (AJ) будуць скапіяваны макрасам з табліцы розыгрышу 2022 года.
• Дадзеныя для наступных шасці слупкоў (КП) макрас будзе браць з ліста Генератар, дзе мы рэалізавалі генератар выпадковых лікаў (гл. праблему 4 вышэй).
• У слупку Q мы падлічваем колькасць супадзенняў паміж выпушчанымі лікамі і лікамі, створанымі з дапамогай функцыі SUMPRODUCT (SUMPRODUCT).
• В столбце R вылічаем фінансавы вынік (если не выиграли, то минус 50 рублей за билет, если выиграли, то приз — 50 р. за билет)
• У апошнім слупку S мы разглядаем агульны вынік усёй гульні як кумулятыўны вынік, каб бачыць дынаміку ў працэсе.

І каб ажывіць усю гэтую структуру, патрэбны невялікі макрас. На ўкладцы распрацоўшчык (Распрацоўшчык) выбраць каманду Візуальны Бейсик або скарыстайцеся спалучэннем клавіш Alt+F11. Затым дадайце новы пусты модуль праз меню Устаўка – модуль і ўвядзіце там наступны код:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'аб'яўляем пераменныя для спасылак на спісы Set wsGame = Worksheets("Ігра") Set wsNumbers = Worksheets("Generator") Set wsArchive = Працоўныя аркушы("Тыражы 2022") iGames = wsGame.Range("C1") 'колькасць тыражоў iTickets = wsGame.Range("C2") 'колькасць білетаў у кожным тираже i = 5 'першая радок у табліцы таблІгра wsGame.Rows ("6:1048576").Delete 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'капіруем выигравшие нумары са спісу Тыражы 2022 і ўстаўляем на ліст Гульня wsArchive.Cells(t + 1, 1).Resize (1, 10).Copy Destination:=wsGame.Cells(i, 1) 'капіруем і ўстаўляем спецыяльнай устаўкай значэння генераваных нумароў са спісам Генератар wsNumbers.Range("G4:L4").Капіруем wsGame.Cells(i, 11) .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Далей b Наступны t Канец Sub  

Засталося ў жоўтыя вочкі ўвесці патрэбныя пачатковыя параметры і прапусціць макрас Распрацоўшчык - Макрасы (Распрацоўшчык — Макрасы) або спалучэнне клавіш Alt+F8.

Для нагляднасці вы таксама можаце пабудаваць дыяграму для апошняга слупка з нарастаючай сумай, якая адлюстроўвае змяненне грашовага балансу падчас гульні:

Параўнанне розных стратэгій

Цяпер, выкарыстоўваючы створаны сімулятар, вы можаце праверыць любую гульнявую стратэгію на рэальных розыгрышах у 2022 годзе і ўбачыць вынікі, якія гэта прынясе. Калі гуляць па 1 білету ў кожным розыгрышы, то агульная карціна «слівы» выглядае прыкладна так:

Тут:

• Генератар гэта гульня, дзе ў кожным розыгрышы мы выбіраем выпадковыя лічбы, створаныя нашым генератарам (з аднолькавай вагой).
• Выбраныя гэта гульня, дзе ў кожным розыгрышы мы выкарыстоўваем адны і тыя ж лічбы – тыя, якія часцей за ўсё выпадалі ў розыгрышах за апошнія два гады (27, 32, 11, 14, 34, 40).
• Аўтсайдэры – тое ж самае, але мы выкарыстоўваем самыя рэдкія выпадальныя лічбы (12, 18, 26, 10, 21, 6).
• Халодны – ва ўсіх розыгрышах мы выкарыстоўваем лічбы, якія даўно не выпадалі (35, 5, 39, 11, 6, 29).

Як бачыце, вялікай розніцы няма, але генератар выпадковых лікаў паводзіць сябе крыху лепш, чым іншыя «стратэгіі».

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

Гуляючы ў кожным розыгрышы з адным білетам з выпадковым чынам згенераванымі нумарамі (з аднолькавым вагой):

Гуляючы ў 10 білетаў у кожным розыгрышы з выпадковым чынам згенераванымі нумарамі (з аднолькавай вагой):

Разыгрыванне 100 білетаў у кожным розыгрышы са выпадковымі лікамі (з аднолькавай вагой):

Каментарыі, як кажуць, залішнія - зліў дэпазіту непазбежны ва ўсіх выпадках 🙂