Симулација лутрије у Екцел-у

Лутрија није лов на срећу,

то је лов на губитнике.

Са завидном редовношћу (и све чешће у последње време) људи ми пишу тражећи помоћ у разним прорачунима везаним за лутрију. Неко жели да имплементира свој тајни алгоритам за одабир добитних бројева у Екцелу, неко жели да пронађе обрасце у бројевима који су испали из прошлих извлачења, неко жели да ухвати организаторе лутрије у непоштеној игри.

В етој статье мне хотелось би ответить на часть етих вопросов. Благо, в Екцел дла решениа таких задач достаточно инструментов, многи из которих, кстати, могут пригодитьса и в более прозаических рабочих ситуациј.

Задатак 1. Вероватноћа победе

Узмимо за пример класичну лутрију Столото 6 од 45. Према правилима, само они који су погодили свих 10 бројева од 6 добијају супер награду (45 милиона рубаља или више ако је стање наградног фонда акумулирано из претходних извлачења). Ако сте погодили 5, добићете 150 хиљада рубаља, ако 4 – 1500 рубаља. , ако су 3 броја од 6, онда 150 рубаља, ако су 2 броја – вратићете 50 рубаља потрошених на карту. Погоди само један или ниједан – узми само ендорфине из процеса игре.

Математичка вероватноћа победе може се лако израчунати коришћењем стандардне функције НУМБЕРЦОМБ (КОМБИНЕЈ), који је доступан у Мицрософт Екцел-у за такав случај. Ова функција израчунава број комбинација Н бројева од М. Дакле, за нашу лутрију „6 од 45“ то би било:

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

… што је једнако 8, укупан број свих могућих комбинација у овој лутрији.

Ако желите да израчунате вероватноћу за делимичну победу (2-5 бројева од 6), онда ћете прво морати да израчунате број таквих опција, који је једнак производу броја комбинација погодних бројева од 6 по броју непогођених бројева од преосталих (45-6) = 39 бројева. Затим делимо укупан број свих могућих комбинација (8) са примљеним бројем победа за сваку опцију – и добијамо вероватноћу победе за сваки случај:

Иначе, вероватноћа да, на пример, погинете у авионској несрећи у Нашој земљи, процењује се на око 1 према милион. А вероватноћа да добијете у казину на рулету, кладећи све на један број је 1 до 37.

Ако вас све наведено није зауставило и још увек сте спремни да играте даље, наставите.

Задатак 2. Учесталост појављивања сваког броја

За почетак, хајде да одредимо са којом фреквенцијом испадају одређени бројеви. У идеалној лутрији, с обзиром на довољно велики временски интервал за анализу, све лоптице треба да имају исту вероватноћу да буду у победничком узорку. У стварности, карактеристике дизајна бубња за лутрију и облик тежине лоптица могу искривити ову слику, а за неке лоптице вероватноћа испадања може бити већа/мања него за друге. Хајде да проверимо ову хипотезу у пракси.

Узмимо, на пример, податке о свим 2020. од 21 извлачења лутрије који су се одиграли у 6-45 са сајта њиховог организатора Столото, дизајнираног у облику овако „паметне“ табеле, погодне за анализу, са називом табАрхива Циркулација. Розигриши проходат два раза в день (в 11 утра и у 11 вечера), т.е. в етој таблици у нас полтори тисачи тиражеј-строк — вполне достаточнаа дла начала виборка дла анализе:

Да бисте израчунали учесталост појављивања сваког броја, користите функцију ЦОУНТИФ (ЦОУНТИФ) и додајте му функцију ТЕКСТ (ТЕКСТ)да додате водеће нуле и звездице пре и после једноцифреним бројевима, тако да ЦОУНТИФ тражи појављивање броја било где у комбинацији у колони Б. Такође, ради веће јасноће, направићемо графикон према резултатима и сортирати фреквенције у опадајућем редоследу:

У просеку, било која лопта би требало да падне 1459 извлачења * 6 лопти / 45 бројева = 194,53 пута (управо тако се зове статистика математическим ожиданием), но хорошо видно, что нека числа (27, 32, 11…) пада приметно чешће (+18%), а неки (10, 21, 6…) наоборот заметно реже (-15%), због основне масе. Соответственно, можно попробовать использовать ету информациу дла стратегии виигриша, т.е. или ставить на те шари, что випадаут часе, или наоборот — делать ставку на редко випадаусие шари в надежде, что они морају нагнать отставание.

Задатак 3. Који бројеви одавно нису извучени?

Друга стратегија се заснива на идеји да са довољно великим бројем извлачења, пре или касније сваки број од свих доступних од 1 до 45 треба да испадне. Дакле, ако се неки бројеви дуго нису појављивали међу победницима („хладне лопте”), онда је логично да се у будућности покушате кладити на њих. 

Можете лако најти све давно не випадавшие номера, ако сортирате наш архив тиражеј за 2020-21 год по убиваниу дати и использујте функцију ИЗЛОЖЕНИЈИ (УТАКМИЦА). Он ће претраживати од врха до дна (тј. од новог до старог) да би потражио сваки број и дао серијски број серије (рачунајући од краја године до почетка) где је овај број последњи пут испуштен:

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

Друга стратегија игре заснива се на елиминисању психолошког фактора приликом погађања бројева. Када играч бира бројеве тако што се клади, он то подсвесно чини не сасвим рационално. Према статистици, на пример, бројеви од 1 до 31 бирају се 70% чешће од осталих (омиљени датуми), 13 се бира ређе (проклета туцета), чешће се бирају бројеви који садрже „срећних“ седам итд. Али ми играмо против машине (лутријског бубња) за коју су сви бројеви исти, па је логично да их бирамо са истом математичком непристрасношћу како бисмо изједначили своје шансе. Да бисмо то урадили, морамо да направимо генератор случајних и – што је најважније – бројева који се не понављају у Екцелу:

Да уради ово:

1. Хајде да направимо „паметну“ табелу под називом таблеГенератор, где ће прва колона бити наши бројеви од 1 до 45.
2. У другу колону унесите тежину за сваки број (требаће нам мало касније). Ако су нам сви бројеви подједнако вредни и желимо да их изаберемо са једнаком вероватноћом, онда се тежина свуда може поставити једнака 1.
3. У трећој колони користимо функцију СЛЦХИС (РАНД), котораа в Екцел генерирует случајное дробное число от 0 до 1, добавить к њему вес из предидусего столбца. Таким образом каждиј раз при пересчете листа (нажатии на клавишу F9) биће генерисан нови скуп од 45 случајних бројева, узимајући у обзир тежину сваког од њих.
4. Хајде да додамо четврту колону, где користимо функцију РАНГ (РАНГ) вичислим ранг (позицију в топе) дла каждого из чисел.

Сада остаје да извршимо избор првих шест бројева по рангу 6 користећи функцију ИЗЛОЖЕНИЈИ (УТАКМИЦА):

При нажатии на клавишу F9 формуле на Екцел листу ће бити поново израчунате и сваки пут ћемо добити нови скуп од 6 бројева у зеленим ћелијама. Штавише, бројеви за које је постављена већа тежина у колони Б добиће пропорционално виши ранг и стога ће се чешће појављивати у резултатима нашег случајног узорка. Ако је тежина за све бројеве подешена на исту, онда ће сви бити изабрани са истом вероватноћом. На овај начин добијамо правичан и непристрасан генератор случајних бројева од 6 од 45, али са могућношћу прилагођавања случајности дистрибуције ако је потребно.

Ако одлучимо да играмо у сваком извлачењу не са једним, већ, на пример, са два тикета одједном, у сваком од којих ћемо изабрати бројеве који се не понављају, онда можемо једноставно додати додатне линије од дна до зеленог опсега, додајући рангу 6, 12, 18 итд. д. редом:

Задатак 5. Симулатор лутрије у Екцел-у

Као апотеоза целе ове теме, хајде да направимо пуноправни симулатор лутрије у Екцелу, где можете испробати било коју стратегију и упоредити резултате (у теорији оптимизације, нешто слично се назива и Монте Карло метода, али ће бити једноставније за нас).

Да све буде што ближе стварности, замислите на тренутак да је 1. јануар 2022. године и да су пред нама овогодишњи жреби у којима планирамо да играмо. Унео сам стварне испуштене бројеве у табелу таблТиражи2022, одвајајући додатно извучене бројеве један од другог у засебне колоне ради погодности наредних израчунавања:

На посебном листу игра креирајте празан простор за моделирање у облику „паметне“ табеле са именом табИгра следећи образац:

Овде:

• У жутим ћелијама изнад, за макро ћемо поставити број извлачења у 2022. у којима желимо да учествујемо (1-82) и број тикета које играмо у сваком извлачењу.
• Макро ће копирати податке за првих 11 колона (АЈ) са листа за извлачење 2022.
• Податке за наредних шест колона (КП) макро ће преузети са листа Генератор, где смо имплементирали генератор случајних бројева (погледајте проблем 4 изнад).
• У колони К, бројимо број подударања између испуштених бројева и оних генерисаних помоћу функције СУМПРОДУЦТ (СУМПРОИЗВОД).
• В столбце Р вичислаем финансовиј результат (если не вииграли, то минус 50 рублеј за билет, ако вииграли, то приз — 50 р. за билет)
• У последњој колони С, укупан резултат целе утакмице сматрамо кумулативним укупним да бисмо видели динамику у процесу.

А да бисмо оживели целу ову структуру, потребан нам је мали макро. На картици развијач (Програмер) изабрати тим Висуал Басиц или користите пречицу на тастатури други+ФКСНУМКС. Затим додајте нови празан модул преко менија Инсерт – Модул и тамо унесите следећи код:

Суб Лоттери() Дим иГамес Ас Интегер, иТицкетс Ас Интегер, и Ас Лонг, т Ас Интегер, б Ас Интегер 'объавлаем переменние дла ссилки на листове Сет всГаме = Ворксхеетс("Игра") Сет всНумберс = Ворксхеетс("Генератор") Сет всАрцхиве = Ворксхеетс("Тиражи 2022") иГамес = всГаме.Ранге("Ц1") 'количество тиражеј иТицкетс = всГаме.Ранге("Ц2") 'количество билетов в каждом тираже и = 5 'перваа строка в таблици таблице.РовсГ таблме. ("6:1048576").Делете 'очисаем старие данние Фор т = 1 То иГамес Фор б = 1 То иТицкетс 'копируем виигравшие номера са листе Тиражи 2022 и вставлаем на лист Игра всАрцхиве.Целлс(т + 1, 1).Ресизе (1, 10).Цопи Дестинатион:=всГаме.Целлс(и, 1) 'копируем и вставлаем специальнуу вставку сгенерированних номера са листе Генератор всНумберс.Ранге("Г4:Л4").Цопи всГаме.Целлс(и, 11) .ПастеСпециал Пасте:=клПастеВалуес и = и + 1 Следећи б Следећи т Крај под  

Остаје да унесете жељене почетне параметре у жуте ћелије и прођете кроз макро Програмер – Макрои (Програмер — Макрои) или пречица на тастатури други+F8.

Ради јасноће, такође можете да направите дијаграм за последњу колону са кумулативним укупним износом, који одражава промену биланса новца током игре:

Поређење различитих стратегија

Сада, користећи креирани симулатор, можете тестирати било коју стратегију игре на реалним извлачењима 2022. године и видети резултате које би то донело. Ако играте 1 тикет у сваком извлачењу, онда укупна слика "шљиве" изгледа отприлике овако:

Овде:

• Генератор је игра у којој у сваком извлачењу бирамо насумичне бројеве које креира наш генератор (са истом тежином).
• Изабрано је игра у којој у сваком извлачењу користимо исте бројеве – оне који су најчешће испадали у реми у последње две године (27, 32, 11, 14, 34, 40).
• Аутсајдери – исто, али користимо најређе падајуће бројеве (12, 18, 26, 10, 21, 6).
• Хладан – у свим извлачењима користимо бројеве који дуго нису испали (35, 5, 39, 11, 6, 29).

Као што видите, нема велике разлике, али генератор случајних бројева се понаша мало боље од осталих „стратегија“.

Такође можете пробати да играте велики број улазница у сваком тирању, да бисте прекрили већи број варијанти (иногда за ово неколико играча обједињује се у групи).

Играње у сваком извлачењу са једним тикетом са насумично генерисаним бројевима (са истом тежином):

Играње 10 тикета у сваком извлачењу са насумично генерисаним бројевима (са истом тежином):

Играње 100 тикета у сваком извлачењу са насумичним бројевима (са истом тежином):

Коментари су, како кажу, сувишни – одлив депозита је у сваком случају неизбежан 🙂