Моделювання лотереї в 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 роках, з сайту їх організатора Столото, оформленого у вигляді такої собі зручної для аналізу «розумної» таблиці з назвою вкладка Архів Тираж. Розигриші проходять два рази в день (в 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. У третьому стовпчику використовуємо функцію СЛЧИС (RAND), яка в 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, відокремивши додатково витягнуті числа один від одного в окремі стовпці для зручності наступних розрахунків:

На окремому аркуші Game створити заготовку для моделювання у вигляді «розумної» таблиці з назвою tabIgra наступна форма:

тут:

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

І щоб оживити всю цю структуру, нам потрібен маленький макрос. На вкладці розробник (розробник) вибрати команду Візуальний Бейсік або скористайтеся комбінацією клавіш інший+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("Генератор") Set wsArchive = Worksheets("Тиражі 2022") iGames = wsGame.Range("C1") 'кількість тиражів iTickets = wsGame.Range("C2") 'кількість квитків у кожному тиражі i = 5 'первая строка в таблиці таблІгра wsGame.Rows ("6:1048576").Видалити 'очищаем старі дані For t = 1 To iGames For b = 1 To iTickets 'копіюємо вигравшіе номери зі списку Тиражі 2022 і вставляємо на лист Гра wsArchive.Cells(t + 1, 1).Змінити розмір (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 квиток, то загальна картина «сливи» виглядає приблизно так:

тут:

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

Як бачите, великої різниці немає, але генератор випадкових чисел поводиться трохи краще, ніж інші «стратегії».

Можна також спробувати зіграти велику кількість квитків у кожному тиражі, щоб перекрити більшу кількість варіантів (іногда для цього кілька гравців об’єднані в групу).

Гра в кожному тиражі з одним квитком із випадково згенерованими числами (з однаковою вагою):

Розіграш 10 квитків у кожному тиражі з випадково згенерованими числами (з однаковою вагою):

Розіграш 100 білетів у кожному тиражі з випадковими числами (з однаковою вагою):

Коментарі, як кажуть, зайві – злив депозиту неминучий у всіх випадках 🙂