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

Чтобы вычислить частоту появления каждого числа, используйте функцию 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. В третьем столбце мы используем функцию СЛЧИС (РАНД), которое в Excel последовательно случайное дробное число от 0 до 1, добавляя к нему вес из столбца. Таким образом каждый раз при пересчёте листа (нажатии на сторону F9) будет сгенерирован новый набор из 45 случайных чисел с учетом веса каждого из них.
4. Добавим четвертый столбец, где с помощью функции RANK / ЗВАНИЕ (КЛАССИФИЦИРОВАТЬ) вычислим ранг (позицию в топе) для каждого из чисел.

Теперь осталось сделать выборку первых шести чисел по рангу 6 с помощью функции БОЛЬШЕ ОТКРЫТОГО (СООТВЕТСТВОВАТЬ):

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

Если мы решим играть в каждом розыгрыше не одним, а, например, сразу двумя билетами, в каждом из которых мы будем выбирать неповторяющиеся числа, то мы можем просто добавить дополнительные строки снизу в зеленый диапазон, прибавление к рангу 6, 12, 18 и т. д. д. соответственно:

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

В качестве апофеоза всей этой темы создадим полноценный симулятор лотереи в Excel, где можно будет опробовать любые стратегии и сравнить результаты (в теории оптимизации нечто подобное еще называют методом Монте-Карло, но он будет проще). для нас).

Чтобы все было максимально приближено к реальности, представим на минутку, что на дворе 1 января 2022 года и впереди нас ждут розыгрыши этого года, в которых мы планируем сыграть. Я ввел в таблицу реальные выпавшие числа таблТиражи2022, отделяя дополнительно выпавшие числа друг от друга в отдельные столбцы для удобства последующих вычислений:

На отдельном листе Игра создать заготовку для моделирования в виде «умной» таблицы с названием табИгра следующая форма:

Вот:

• В желтых ячейках выше мы зададим для макроса количество тиражей 2022 года, в которых мы хотим участвовать (1-82) и количество билетов, которые мы разыгрываем в каждом тираже.
• Данные для первых 11 столбцов (AJ) будут скопированы макросом из листа розыгрыша 2022 года.
• Данные для следующих шести столбцов (КП) макрос возьмет с листа Генератор, где мы реализовали генератор случайных чисел (см. задачу 4 выше).
• В столбце Q подсчитываем количество совпадений между выпавшими числами и числами, сгенерированными с помощью функции 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).Resize (1, 10).Copy Destination:=wsGame.Cells(i, 1) 'копируем и вносим лучшие поставки в результаты сгенерированных номеров с листа Генератор wsNumbers.Range("G4:L4").Copy 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).
• Аутсайдеры – то же самое, но используем самые редкие выпадающие числа (12, 18, 26, 10, 21, 6).
• холодная — во всех розыгрышах мы используем числа, которые давно не выпадали (35, 5, 39, 11, 6, 29).

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

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

Игра в каждом тираже по одному билету со случайно сгенерированными числами (с одинаковым весом):

Разыгрывание 10 билетов в каждом тираже со случайно сгенерированными номерами (с одинаковым весом):

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

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