Как составить таблицу розыгрышей

В первой части мы рассчитали необходимые параметры для заполнения таблицы. Теперь расскажу и покажу как её заполнять

(продолжение статьи «Как составить систему»).

Заполнение таблицы

Исходя из полученных результатов, чертим таблицу 21 х 21 на листе бумаги. Более продвинутые могут использовать программу Exsel Microsoft Office.
Очень удобно и есть возможность вписать формулы в ячейки. Посмотрите, как это сделано у меня. В ячейках справа и внизу таблицы подсчитывается количество номеров по строкам и столбцам.

Пояснения к таблице:

• по горизонтали: номера от 1 до 21;
• по вертикали: количество комбинаций — 21;
• справа: количество номеров в комбинации — должно быть 5 (до заполнения стоят нули);
• внизу: количество номеров в столбце — должно быть 5 (пока там нули).

Начинаем заполнять. На бумаге лучше писать простым карандашом, чтобы можно было исправлять неправильные записи. Те кто заполняет таблицу на компьютере удалят ошибки с помощью клавиши DEL.

Есть два варианта заполнения таблицы: по горизонтали и по вертикали. Будем заполнять по вертикали, сверху вниз.

Номер 1 повторяется 5 раз (из расчета) и мы его вписываем пять раз в первом столбце:

Всё. С первым номером разобрались. В сумме номеров первого столбца стоит цифра 5.

Номер 2 также повторяется пять раз и должен один раз быть в сочетании с номером 1 (зелёные ячейки).

Номер 3 должен по одному разу совпадать с номерами 1 и 2 и пять раз в столбце. Вписываем номер в таблицу, сверху вниз:

Отмечаем номер 4 таким же образом. Проверяем, чтобы сочетания червёрки были со всеми уже введёнными номерами (1-4, 2-4, 3-4 — отмечено зелёным):

Номера шаров в тираже редко выпадают с одинаковым шагом, например все номера через 4 или 5 пробелов. Чаще выпадают номера, которые идут подряд или через один пробел. Это можно проследить по таблице результатов тиражей лотереи 5 из 36. Учитывая сказанное, будем записывать номера в таблицу в хаотическом порядке с соблюдением расчетных данных.

Номер 5 вписываем не вторым по порядку, а в первую строчку. Он будет третим в первой комбинации (сумма в первой строчке равна 3). И с этого места нужно отмечать пустые ячейки, при заполнении которых уже есть сочетания в введённых номерах. И в эти ячейки не надо записывать номера, чтобы не было повторений.

В первой строке есть комбинация 2-5, поэтому в строках 6, 7, 8 и 9 не должно быть пятёрки.

В таблице такие ячейки закрашены в красный цвет, на бумаге отмечаем любым знаком (крестиком, минусом или косой чертой).

В последующем такие же метки нужно будет ставить в строчках, где сумма номеров равна пяти.

Проверяем наличие сочетаний 1-5, 2-5, 3-5, 4-5 (отмечено зелёным цветом):

Номера 6, 7 и 8 записываем в таком же порядке.

Не забывайте, что заполняем таблицу сверху вниз, отмечая красным цветом (или любым символом) нижние ячейки, при заполнении которых будут повторения комбинаций «двоек». Сочетание двух любых номеров в системе должно повторяться только один раз.

Не делаю комментариев по заполнению. Должно быть понятно из рисунков:

Продолжнение в статье Подробное заполнение таблицы.

При перепечатке текстов и использовании материалов сайта в различных видах надеюсь увидеть ссылку на мой сайт. Уважайте труд.

Яхин Ильшат

Лотерея — это не охота за удачей,
это охота за неудачниками.

С завидной регулярностью (а в последнее время — всё чаще) мне пишут люди с просьбами помочь в различных вычислениях, связанных с лотереями. Кто-то хочет реализовать в Excel свой секретный алгоритм подбора выигрышных чисел, кто-то — найти закономерности в выпавших номерах прошедших тиражей, кто-то — подловить организаторов лотереи на нечестной игре.

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

Задача 1. Вероятность выигрыша

Возьмем для примера классическую лотерею «Столото 6 из 45». По правилам суперприз (10 млн. рублей или больше, если накопился остаток призового фонда с прошлых тиражей) получают только те, кто угадал все 6 чисел из 45. Если вы угадали 5, то получите 150 тыс. рублей, если 4 — 1500 р., если 3 числа из 6, то 150 р., если 2 числа — вернете 50 р., потраченных на билет. Угадаете только одно или ни одного — получите только эндорфины от процесса игры.

Математическую вероятность выигрыша можно легко рассчитать с помощью стандартной функции ЧИСЛКОМБ (COMBIN), которая имеется в Microsoft Excel на такой случай. Эта функция вычисляет количество комбинаций N чисел из M. Так для нашей лотереи «6 из 45» это будет:

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

… что равно 8 145 060 — общее число всех возможных комбинаций в этой лотерее.

Если же хочется рассчитать вероятность для частичного выигрыша (2-5 чисел из 6), то придётся сначала вычислить количество таких вариантов, которое равно произведению числа комбинаций угаданных чисел из 6 на количество не угаданных чисел из оставшихся (45-6)=39 чисел. Затем общее количество всех возможных комбинаций (8 145 060) мы делим на полученное количество выигрышей по каждому варианту — и получим вероятности выигрыша для каждого случая:

К слову, вероятность, например, погибнуть в авиакатастрофе в России оценивается примерно как 1 к миллиону. А вероятность выиграть в казино в рулетку, поставив всё на один номер — 1 к 37.

Если всё вышеперечисленное вас не остановило и вы по-прежнему готовы играть дальше — продолжаем.

Задача 2. Частота выпадения каждого числа

Для начала давайте определим с какой частотой выпадают те или иные числа. В идеальной лотерее, если брать для анализа достаточно большой временной интервал, у всех шаров должна быть одинаковая вероятность попадания в победную выборку. В реальности же особенности конструкции лототрона и вес-форма шаров могут вносить искажения в эту картину и для каких-то шаров вероятность выпадения вполне может оказаться выше/ниже, чем для других. Давайте проверим эту гипотезу на практике.

Возьмём для примера данные по всем прошедшим в 2020-21 году тиражам лотереи 6 из 45 с сайта их организатора Столото, оформленные в виде вот такой удобной для анализа «умной» таблицы с именем таблАрхивТиражей. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Для подсчёта частоты выпадения каждого числа используем функцию СЧЁТЕСЛИ (COUNTIF) и дополнительно вложим в неё функцию ТЕКСТ (TEXT), чтобы добавить к одноразрядным числам начальные нули и звёздочки перед и после, чтобы СЧЁТЕСЛИ искала вхождение числа в любом месте комбинации в столбце В. Также для пущей наглядности построим диаграмму по результатам и отсортируем частоты по убыванию:

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

Задача 3. Какие числа давно не выпадали?

Ещё одна стратегия базируется на идее, что при достаточно большом количестве тиражей рано или поздно должно выпасть каждое число из всех имеющихся от 1 до 45. Так что если какие-то числа давно не появлялись среди выигравших («холодные шары»), то логично попробовать сделать на них ставку в будущем. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21 год по убыванию даты и использовать функцию ПОИСКПОЗ (MATCH). Она будет сверху-вниз (т.е. от новых к старым тиражам) искать каждое число и выдавать порядковый номер тиража (считая от конца года к началу), где последний раз это число выпало:

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

Ещё одна стратегия игры основана на том, чтобы исключить психологический фактор при угадывании номеров. Когда игрок выбирает числа, делая свою ставку, то подсознательно делает это не совсем рационально. По статистике, например, числа от 1 до 31 выбирают на 70 % чаще, чем остальные (любимые даты), реже выбирают 13 (чертова дюжина), чаще выбирают числа содержащие «счастливую» семерку и т.д. Но играем мы против машины (лототрона), для которой все числа одинаковы, так что имеет смысл выбирать их с такой же математической беспристрастностью, чтобы уравнять наши шансы. Для этого нам потребуется создать в Excel генератор случайных и — что особенно важно — неповторяющихся чисел:

Для этого:

1. Создадим «умную» таблицу с именем таблГенератор, где в первом столбце будут наши числа от 1 до 45.
2. Во втором столбце введём вес для каждого числа (он потребуется нам чуть позднее). Если все числа для нас одинаково ценны и мы хотим выбирать их с равной вероятностью, то вес везде можно поставить равным 1.
3. В третьем столбце используем функцию СЛЧИС (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) будет генерироваться новый набор из 45 случайных чисел с учётом веса для каждого из них.
4. Добавим четвертый столбец, где с помощью функции РАНГ (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Теперь останется сделать выборку первых шести по рангу 6 чисел с помощью функции ПОИСКПОЗ (MATCH):

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

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

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

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

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

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

Здесь:

• В желтых ячейках сверху будем задавать для макроса количество тиражей 2022 года, в которых мы хотим участвовать (1-82) и количество билетов, которыми мы играем в каждом тираже.
• Данные для первых 11 столбцов (A-J) макрос будет копировать с листа тиражей 2022 года.
• Данные для следующих шести столбцов (K-P) макрос будет брать с листа Генератор, где мы реализовали генератор случайных чисел (см. задачу 4 выше).
• В столбце Q мы считаем количество совпадений выпавших чисел и сгенерированных с помощью функции СУММПРОИЗВ (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли, то приз — 50 р. за билет)
• В последнем столбце S считаем общий результат всей игры нарастающим итогом, чтобы видеть динамику в процессе.

И чтобы оживить всю эту конструкцию нам потребуется небольшой макрос. На вкладке Разработчик (Developer) выберем команду Visual Basic или воспользуемся сочетанием клавиш Alt+F11. Затем добавим новый пустой модуль через меню Insert — Module и введем туда следующий код:

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").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").Copy
            wsGame.Cells(i, 11).PasteSpecial Paste:=xlPasteValues
            
            i = i + 1
        Next b
    Next t

End Sub

Останется ввести желаемые исходные параметры в жёлтые ячейки и запустить макрос через Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8.

Для наглядности можно ещё построить диаграмму по последнему столбцу с нарастающим итогом, отражающую изменение денежного баланса в процессе игры:

Сравнение разных стратегий

Теперь, используя созданный симулятор, можно протестировать на реальных тиражах 2022 года любую стратегию игры и посмотреть на результаты, которые бы она принесла. Если играть 1 билетом в каждом тираже, то общая картина «слива» выглядит примерно так:

Здесь:

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

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

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

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

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

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

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

Спрос

Компания часто организует некоторые внутренние конкурсы по продвижению, чтобы мотивировать продажи, чтобы создать больше потока. Лотерея обычно используется с небольшими и большими средствами, такими как следующая схема

В течение двойного одиннадцати 2020 года, то есть в 24:00 с 1 по 11 ноября.ЛотереяПосле окончания двойного одиннадцати, 12 ноября, будет проведен лотерея iPhone12. В то время количество ваучеров Draw определит вероятность завоевания приза на продажу.

Например, компания имеет 3 продажи Xiaohong, Xiao Ming, Xiao Zhang.

Когда мы делаем модель, мы обычно получаем число из базы данных, которая образует сводную таблицу

Тем не менее, во время лотереи мы должны генерировать рекордное количество каждого розыгрыша. Поскольку система лотереи является более ошибкой, мы можем нарисовать лотерею только в соответствии с количеством строк, поэтому мы должны генерироватьИмя сотрудника*Записи о многих строках, то есть 100 строк данных. Следующие два метода реализованы двумя методами, не были отделены от среды Excel.

Реализация PowerQuery

Шаг 1 загружает таблицу в PowerQuery

Нажмите на таблицу данных, затем нажмите на заказданные–>Из формыЗагрузить данныеPowerQuery

Шаг 2 добавляет пользовательские столбцы в PowerQuery

Нажмите по очередиДобавить столбец–>Пользовательский столбец,существуетИндивидуальная формула столбцаВведите следующий контент и определите

={1..[Количество ваучеров.]}

Результаты будут добавлены

Шаг 3 Нажмите на стрелку, продлитесь к новой линии, завершите

Нажмите на двухюю стрелку в правой стороне имени поля, выберитеРасширить до Синсинга

Затем вышел результат

ЩелкнулДомашняя страница–>Закрыть и загрузитьВы можете отобразить его на листе Excel

Реализация VBA

Реализовать логику

VBAЛогика реализации такая

VBA код

Код реализации выглядит следующим образом. Чтобы облегчить расширение, код, вероятно, будет переменной

Sub repeatRow()
    Dim Arr
    Dim i%, j%, k%, wRow%
    Dim Sht As Worksheet,OriSht as Worksheet
    Dim t!
    
    Const ORINAME$ =  "источник данных"              'Имя источника данных
    Const SHTNAME$ =  "Список розыгрыша"		«Имя таблицы, чтобы сгенерировать список лотереи
    Const LASTCOL As Byte = 10	    Количество столбцов, где расположено количество ваучеров Draw, вы можете использовать `= column ()` для просмотра номера столбца, или вы можете изменить стиль R1C1 на просмотр
    Const STARTROW As Byte = 2      'Номер строки первой строки данных начинается с заголовка. Так что это также номер строки, где находится заголовок
    
    t = Timer	'Таймер
    set OriSht = Sheets(ORINAME)
    
    'Напишите источники данных в память
    With OriSht
        Arr = .Range(.Cells(STARTROW, 1), .Cells(.Cells(.Rows.Count, 1).End(3).Row, LASTCOL))
    End With
    
    'Предварительная обработка: если есть файл с тем же именем, сообщается об ошибке, поэтому сначала удалите его
    Application.DisplayAlerts = 0
    For Each Sht In Worksheets
        If Sht.Name = SHTNAME Then
            Sht.Delete
            Exit For
        End If
    Next
    Application.DisplayAlerts = 1

    'Новые таблицы и измените имя таблицы, чтобы указать имя
    Set Sht = Worksheets.Add(after:=OriSht)
    Sht.Name = SHTNAME
    
    With Sht
        'Название первой строки
        wRow = 1
        For j = LBound(Arr, 2) To UBound(Arr, 2)
            .Cells(wRow, j).Value = Arr(wRow, j)
        Next

        'Начните писать контент во второй строке
        wRow = 2
        For i = LBound(Arr) + 1 To UBound(Arr)
            If Arr(i, 1) <>  "Резюме" Then         'Определите, является ли первый столбец с резюме два слова, чтобы определить, есть ли резюме
                For k = 1 To Arr(i, LASTCOL)
                    For j = LBound(Arr, 2) To UBound(Arr, 2)
                        .Cells(wRow, j).Value = Arr(i, j)
                    Next
                    wRow = wRow + 1
                Next
            End If
        Next
    End With
         Msgbox "трудоемкий:" & Format(Timer - t, "0.0s"), vbInformation, SHTNAME & "Генерировать комплекс "
End Sub

Приложение

Я не знаю, как бежатьVBAКод? См. Следующую статью

Как запустить макрос