Лотарийна симулация в 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 от уебсайта на техния организатор Столото, проектиран под формата на такава „умна“ таблица, удобна за анализ, с името tabArchive Тираж. Розигрыши преминават два раза в ден (в 11 утра и в 11 вечерта), т.е. в тази таблица у нас няколко хиляди тиражей-строк — достатъчна за началото на избора за анализ:

За да изчислите честотата на срещане на всяко число, използвайте функцията COUNTIF (COUNTIF) и добавете функция към него ТЕКСТ (ТЕКСТ)за добавяне на водещи нули и звездички преди и след към едноцифрени числа, така че 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 и т.н. към ранга. д. съответно:

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

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

За да бъде всичко възможно най-близко до реалността, за момент си представете, че е 1 януари 2022 г. и ни предстоят тазгодишните тиражи, в които планираме да играем. Въведох реално изпуснатите числа в таблицата tablTiraži2022, разделяйки допълнително изтеглените числа едно от друго в отделни колони за удобство при последващи изчисления:

На отделен лист игра създайте заготовка за моделиране под формата на „умна“ таблица с името tabIgra следната форма:

Тук:

• В жълтите клетки по-горе ще зададем за макроса броя тегления през 2022 г., в които искаме да участваме (1-82) и броя билети, които играем във всяко теглене.
• Данните за първите 11 колони (AJ) ще бъдат копирани от макроса от листа за теглене за 2022 г.
• Данни за следващите шест колони (KP), които макросът ще вземе от листа Генератор, където сме внедрили генератор на случайни числа (вижте проблем 4 по-горе).
• В колона Q отчитаме броя на съвпаденията между изпуснатите числа и тези, генерирани с помощта на функцията 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 'объявяваме переменни за връзки на листи Set wsGame = Worksheets("Игра") Set wsNumbers = Worksheets("Generator") Set wsArchive = Worksheets("Тиражи 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 Next b Next t End Sub  

Остава да въведете желаните начални параметри в жълтите клетки и да прекарате макроса Разработчик – макроси (Програмист — Макроси) или клавишна комбинация Друг+F8.

За по-голяма яснота можете също да изградите диаграма за последната колона с кумулативна сума, отразяваща промяната в паричния баланс по време на играта:

Сравнение на различни стратегии

Сега, използвайки създадения симулатор, можете да тествате всяка стратегия за игра на реални тегления през 2022 г. и да видите резултатите, които ще донесе. Ако играете по 1 билет във всяко теглене, тогава общата картина на „сливата“ изглежда така:

Тук:

• Генератор е игра, при която при всяко теглене избираме произволни числа, създадени от нашия генератор (с еднакво тегло).
• Любими е игра, в която във всяко теглене използваме едни и същи числа – тези, които най-често са се падали в тегления през последните две години (27, 32, 11, 14, 34, 40).
• Outsiders – същото, но използваме най-редките падащи числа (12, 18, 26, 10, 21, 6).
• Студ – във всички тегления използваме числа, които не са падали отдавна (35, 5, 39, 11, 6, 29).

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

Можете също така да пробвате да играете по-голямо количество билети във всеки тираж, за да отворите по-голям брой варианти (иногда за това няколко играчи са обединени в групата).

Игра във всяко теглене с един билет с произволно генерирани числа (с еднакво тегло):

Разиграване на 10 билета във всяко теглене с произволно генерирани числа (с еднакво тегло):

Разиграване на 100 билета във всяко теглене с произволни числа (с еднакво тегло):

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