Симулација на лотарија во Excel

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

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

Со завидна регуларност (и се почесто во последно време) луѓето ми пишуваат и бараат помош во разни пресметки поврзани со лотарии. Некој сака да го имплементира својот таен алгоритам за избор на добитни броеви во Excel, некој сака да најде шаблони во бројките што испаднале од минатите извлекувања, некој сака да ги фати организаторите на лотаријата во нечесна игра.

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

Задача 1. Веројатност за победа

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

Математичката веројатност за победа може лесно да се пресмета со помош на стандардната функција NUMBERCOMB (КОМБИНИРАЈ), кој е достапен во 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 од веб-страницата на нивниот организатор Столото, дизајнирана во форма на таква „паметна“ табела, погодна за анализа, со име tabАрхива Тираж. Розыгрыши проходят два раза во день (во 11 утра и во 11 вечера), т.е. в этой таблице во нас полторы тысячи тиражей-строк — целосно достапен за почеток на избор за анализа:

За да ја пресметате фреквенцијата на појавување на секој број, користете ја функцијата COUNTIF (COUNTIF) и додадете функција на неа ТЕКСТОТ (ТЕКСТ)да се додадат водечките нули и ѕвездички пред и потоа на едноцифрени броеви, така што COUNTIF бара појава на број каде било во комбинацијата во колоната Б. Исто така, за поголема јасност, ќе изградиме графикон по резултати и ќе ги сортираме фреквенциите по опаѓачки редослед:

Во просек, секоја топка треба да падне 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, добавив к нему вес од предыдущего столбца. Таким образом каждый раз при пересчёте листа (нагата на клавишу F9) ќе се генерира нов сет од 45 случајни броеви, земајќи ја предвид тежината за секој од нив.
4. Ајде да додадеме четврта колона, каде што се користи функцијата РАНГ (РАНГ) вычислим ранг (позицию в топе) для каждого из чисел.

Сега останува да се направи избор на првите шест броеви по ранг 6 користејќи ја функцијата ПОИЗЛОЖЕНИ (натпревар):

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

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

Задача 5. Симулатор за лотарија во Excel

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

За се да биде што поблиску до реалноста, замислете за момент дека е 1 јануари 2022 година и ни претстојат овогодинешни ждрепки во кои планираме да играме. Ги внесов вистинските испуштени бројки во табелата tablTiraži2022, одвојувајќи ги дополнително извлечените броеви едни од други во посебни колони за практичноста на последователните пресметки:

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

Тука:

• Во жолтите ќелии погоре, за макрото ќе го поставиме бројот на извлекувања во 2022 година во кои сакаме да учествуваме (1-82) и бројот на тикети што ги играме при секое извлекување.
• Податоците за првите 11 колони (AJ) ќе бидат копирани со макрото од листот за извлекување во 2022 година.
• Податоци за следните шест колони (KP) макрото ќе ги земе од листот Генератор, каде што имплементиравме генератор на случаен број (видете го проблемот 4 погоре).
• Во колоната Q, го броиме бројот на совпаѓања помеѓу испуштените броеви и оние генерирани со помош на функцијата СУМПРОДУКТ (SumProduct).
• Во столбце R вычисляем финансовый результат (если не се играл, тоа минус 50 рубљи за билет, если выиграли, тоа приз — 50 р. за билет)
• Во последната колона S, го сметаме вкупниот резултат од целата игра како кумулативен збир за да ја видиме динамиката во процесот.

И за да ја оживееме целата оваа структура, ни треба мало макро. На јазичето инвеститорот (програмер) изберете тим Visual Basic или користете кратенка на тастатурата Alt+F11. Потоа додадете нов празен модул преку менито Вметни – Модул и внесете го следниов код таму:

Под лотарија() Затемнети iGames како цел број, iTickets како цел број, i додека, t како цел број, b како цел број 'объявляем переменные для ссылки на листы Поставете wsGame = Работни листови("Игра") Поставете wsБроеви" wsArchive = Работни листови("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов во каждом тираже i = 5 'перваблиаGawsка ("6:1048576").Избриши 'очищаем старые данные За t = 1 За iGames За b = 1 за iTickets 'копируем выигравшие номера со листа Тиражи 2022 и ставил на листот Игра wsArchive.Resells +Cells(1). (1, 1).Копирај дестинација:=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированные номера со листа Генератор wsNumbers.Range("G1:L4").Копирај wss4,Cell. .PasteSpecial Paste:=xlPasteValues ​​i = i + 11 Следно b Следно t Крај Под  

Останува да ги внесете саканите почетни параметри во жолтите ќелии и да го извршите макрото низ Програмер - Макроа (Програмер - макроа) или кратенка на тастатура Alt+F8.

За јасност, можете исто така да изградите дијаграм за последната колона со кумулативен збир, што ја одразува промената во салдото на парите за време на играта:

Споредба на различни стратегии

Сега, користејќи го креираниот симулатор, можете да тестирате која било стратегија за игра на вистински ждрепка во 2022 година и да ги видите резултатите што таа би ги донела. Ако играте по 1 тикет во секое извлекување, тогаш целокупната слика на „сливата“ изгледа отприлика вака:

Тука:

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

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

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

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

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

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

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