Simulasi loteri dalam Excel

Loteri bukan memburu nasib,

ia adalah memburu orang yang kalah.

Dengan keteraturan yang dicemburui (dan lebih kerap kebelakangan ini), orang menulis kepada saya meminta bantuan dalam pelbagai pengiraan yang berkaitan dengan loteri. Seseorang mahu melaksanakan algoritma rahsia mereka untuk memilih nombor yang menang dalam Excel, seseorang ingin mencari corak dalam nombor yang telah gagal daripada cabutan lepas, seseorang ingin menangkap penganjur loteri dalam permainan yang tidak jujur.

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

Tugasan 1. Kebarangkalian menang

Mari kita ambil Stoloto 6 klasik daripada 45 loteri sebagai contoh. Mengikut peraturan, hanya mereka yang meneka semua 10 nombor daripada 6 menerima hadiah super (45 juta rubel atau lebih jika baki dana hadiah telah terkumpul daripada cabutan sebelumnya). Jika anda meneka 5, anda akan menerima 150 ribu rubel, jika 4 – 1500 rubel. , jika 3 nombor daripada 6, maka 150 rubel, jika 2 nombor – anda akan mengembalikan 50 rubel yang dibelanjakan untuk tiket. Teka hanya satu atau tiada - dapatkan hanya endorfin daripada proses permainan.

Kebarangkalian matematik untuk menang boleh dikira dengan mudah menggunakan fungsi standard NUMBERCOMB (GABUNG), yang tersedia dalam Microsoft Excel untuk kes sedemikian. Fungsi ini mengira bilangan kombinasi nombor N daripada M. Jadi untuk loteri "6 daripada 45" kami ialah:

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

… yang bersamaan dengan 8, jumlah bilangan semua kombinasi yang mungkin dalam loteri ini.

Jika anda ingin mengira kebarangkalian untuk kemenangan separa (2-5 nombor daripada 6), maka anda perlu terlebih dahulu mengira bilangan pilihan tersebut, yang sama dengan hasil darab bilangan kombinasi nombor yang diteka daripada 6 dengan bilangan nombor yang tidak disentuh daripada baki (45-6) = 39 nombor. Kemudian kami membahagikan jumlah bilangan semua kemungkinan kombinasi (8) dengan bilangan kemenangan yang diterima untuk setiap pilihan – dan kami mendapat kebarangkalian menang untuk setiap kes:

Dengan cara ini, kebarangkalian, sebagai contoh, untuk mati dalam nahas kapal terbang di Negara Kita dianggarkan kira-kira 1 dalam sejuta. Dan kebarangkalian untuk menang dalam kasino di rolet, mempertaruhkan segala-galanya pada satu nombor ialah 1 hingga 37.

Jika semua perkara di atas tidak menghalang anda dan anda masih bersedia untuk bermain lebih jauh, teruskan.

Tugasan 2. Kekerapan kejadian setiap nombor

Sebagai permulaan, mari tentukan dengan kekerapan nombor tertentu jatuh. Dalam loteri yang ideal, diberikan selang masa yang cukup besar untuk analisis, semua bola harus mempunyai kebarangkalian yang sama untuk berada dalam sampel yang menang. Pada hakikatnya, ciri reka bentuk dram loteri dan bentuk berat bola boleh memesongkan gambar ini, dan bagi sesetengah bola kebarangkalian untuk terjatuh mungkin lebih tinggi/lebih rendah berbanding bola yang lain. Mari kita uji hipotesis ini dalam amalan.

Mari kita ambil, sebagai contoh, data pada semua 2020 daripada 21 cabutan loteri yang berlaku dalam 6-45 dari laman web penganjur mereka Stoloto, direka dalam bentuk jadual "pintar" sedemikian, mudah untuk dianalisis, dengan nama tabArkib Peredaran. Розыгрыши проходят два раза в день (dalam 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Untuk mengira kekerapan kejadian setiap nombor, gunakan fungsi COUNTIF (COUNTIF) dan tambahkan fungsi padanya TEXT (TEKS)untuk menambah sifar dan asterisk pendahulu sebelum dan selepas kepada nombor satu digit, supaya COUNTIF mencari kejadian nombor di mana-mana dalam gabungan dalam lajur B. Selain itu, untuk lebih jelas, kami akan membina carta mengikut keputusan dan mengisih frekuensi dalam susunan menurun:

Secara purata, mana-mana bola harus jatuh 1459 seri * 6 bola / 45 nombor = 194,53 kali (ini betul-betul dipanggil dalam statistik математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые числа (10, 21, 6…) выпадали заметно чаще (+15%), а некоторые числа (XNUMX, XNUMX, мятно чаще (+XNUMX%), а некоторые (XNUMX, XNUMX, XNUMX tahun) Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шарто в начоборот .

Tugasan 3. Apakah nombor yang sudah lama tidak dilukis?

Strategi lain adalah berdasarkan idea bahawa dengan jumlah cabutan yang cukup besar, lambat laun setiap nombor daripada semua yang tersedia dari 1 hingga 45 akan gugur. Jadi jika beberapa nombor tidak muncul di kalangan pemenang untuk masa yang lama ("bola sejuk"), maka adalah logik untuk mencuba bertaruh pada mereka pada masa akan datang. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей untuk 2020-21 годзывать по удица LEBIH TERDEDAH (PERLAWANAN). Ia akan mencari dari atas ke bawah (iaitu dari larian baru ke larian lama) untuk mencari setiap nombor dan memberikan nombor siri larian (mengira dari akhir tahun hingga awal) di mana nombor ini terakhir digugurkan:

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

Satu lagi strategi permainan adalah berdasarkan menghapuskan faktor psikologi apabila meneka nombor. Apabila pemain memilih nombor dengan membuat pertaruhannya, dia secara tidak sedar melakukan ini tidak sepenuhnya secara rasional. Mengikut statistik, sebagai contoh, nombor dari 1 hingga 31 dipilih 70% lebih kerap daripada yang lain (tarikh kegemaran), 13 dipilih kurang kerap (sedozen sialan), nombor yang mengandungi tujuh "bertuah" lebih kerap dipilih, dsb. Tetapi kami bermain menentang mesin (dram loteri) yang semua nombornya adalah sama, jadi masuk akal untuk memilihnya dengan kesaksamaan matematik yang sama untuk menyamakan peluang kami. Untuk melakukan ini, kita perlu mencipta penjana nombor rawak dan - yang paling penting - tidak berulang dalam Excel:

Untuk membuat ini:

1. Mari buat jadual "pintar" bernama tableGenerator, di mana lajur pertama ialah nombor kami dari 1 hingga 45.
2. Dalam lajur kedua, masukkan berat untuk setiap nombor (kami akan memerlukannya sedikit kemudian). Jika semua nombor adalah sama berharga kepada kami dan kami ingin memilihnya dengan kebarangkalian yang sama, maka berat boleh ditetapkan sama dengan 1 di mana-mana.
3. Dalam lajur ketiga kami menggunakan fungsi SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) set baharu 45 nombor rawak akan dijana, dengan mengambil kira berat bagi setiap nombor tersebut.
4. Mari tambah lajur keempat, di mana menggunakan fungsi Rank (KEDUDUKAN) вычислим ранг (позицию в топе) для каждого из чисел.

Kini tinggal membuat pemilihan enam nombor pertama mengikut peringkat 6 menggunakan fungsi tersebut LEBIH TERDEDAH (PERLAWANAN):

При нажатии на клавишу F9 formula pada helaian Excel akan dikira semula dan setiap kali kita akan mendapat set baharu 6 nombor dalam sel hijau. Selain itu, nombor yang beratnya lebih besar ditetapkan dalam lajur B akan menerima kedudukan yang lebih tinggi secara berkadar dan, dengan itu, muncul lebih kerap dalam keputusan sampel rawak kami. Jika berat untuk semua nombor ditetapkan kepada sama, maka kesemuanya akan dipilih dengan kebarangkalian yang sama. Dengan cara ini kita mendapat penjana nombor rawak yang adil dan tidak berat sebelah sebanyak 6 daripada 45, tetapi dengan keupayaan untuk membuat pelarasan kepada rawak pengedaran jika perlu.

Jika kita memutuskan untuk bermain dalam setiap cabutan bukan dengan satu, tetapi, sebagai contoh, dengan dua tiket sekaligus, di mana setiap satu kita akan memilih nombor yang tidak berulang, maka kita hanya boleh menambah garisan tambahan dari bawah ke julat hijau, menambah 6, 12, 18, dsb. pada pangkat. d. masing-masing:

Tugasan 5. Simulator Loteri dalam Excel

Sebagai apotheosis bagi keseluruhan topik ini, mari buat simulator loteri lengkap dalam Excel, di mana anda boleh mencuba sebarang strategi dan membandingkan hasilnya (dalam teori pengoptimuman, sesuatu yang serupa juga dipanggil kaedah Monte Carlo, tetapi ia akan menjadi lebih mudah untuk kami).

Untuk menjadikan segala-galanya hampir dengan realiti yang mungkin, bayangkan sejenak bahawa ia adalah 1 Januari 2022 dan kami mempunyai cabutan tahun ini di hadapan kami, di mana kami merancang untuk bermain. Saya memasukkan nombor yang jatuh sebenar di dalam jadual tablTiraži2022, memisahkan nombor yang dilukis tambahan antara satu sama lain ke dalam lajur yang berasingan untuk kemudahan pengiraan seterusnya:

Pada helaian berasingan Permainan buat tempat kosong untuk pemodelan dalam bentuk jadual "pintar" dengan nama tabIgra borang berikut:

Di sini:

• Dalam sel kuning di atas, kami akan menetapkan untuk makro bilangan cabutan pada 2022 yang kami mahu sertai (1-82) dan bilangan tiket yang kami mainkan dalam setiap cabutan.
• Data untuk 11 lajur (AJ) pertama akan disalin oleh makro daripada helaian cabutan 2022.
• Data untuk enam lajur (KP) seterusnya yang akan diambil oleh makro daripada helaian Generator, di mana kami telah melaksanakan penjana nombor rawak (lihat masalah 4 di atas).
• Dalam lajur Q, kami mengira bilangan padanan antara nombor yang digugurkan dan yang dijana menggunakan fungsi tersebut SUMPRODUCT (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли, 50 приз.)
• Dalam lajur S terakhir, kami menganggap keputusan keseluruhan keseluruhan permainan sebagai jumlah kumulatif untuk melihat dinamik dalam proses.

Dan untuk memulihkan keseluruhan struktur ini, kita memerlukan makro kecil. Pada tab pemaju (Pembangun) memilih pasukan Visual Basic atau gunakan pintasan papan kekunci Alt+F11. Kemudian tambah modul kosong baharu melalui menu Sisipan – Modul dan masukkan kod berikut di sana:

Sub Loteri() Malapkan iGames Sebagai Integer, iTickets Sebagai Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets("Игра") = Set wsNumbers "Игра") = Set wsNumbers wsArchive = Lembaran Kerja("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в 5 гтабер ти количество тиражей iTickets = wsGame.Range("C6") 'количество билетов в 1048576 гта беро ти ли количество тиражей ("1:1").Padamkan 'очищаем старые данные Untuk t = 2022 Kepada iGames Untuk b = 1 Kepada iTickets 'копируем выигравшие номера с листа листа Ти1ражи листа Ти1вги листа +10сталя листа +1стаги 4 листа копирующие копируем. (4, 11). Destinasi Salin:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированныто номера Грунные номера Гомера специальной вставкой значений сгенерированныто номера Грыныто номера XNUMX, XNUMX, XNUMX).(XNUMX, XNUMX). .Tampal Tampal Khas:=xlTampalValues ​​i = i + XNUMX Seterusnya b Seterusnya t Sub Akhir  

Ia kekal untuk memasukkan parameter awal yang dikehendaki dalam sel kuning dan menjalankan makro melalui Pembangun – Makro (Pembangun — Makro) atau pintasan papan kekunci Alt+F8.

Untuk kejelasan, anda juga boleh membina rajah untuk lajur terakhir dengan jumlah terkumpul, mencerminkan perubahan dalam baki wang semasa permainan:

Perbandingan strategi yang berbeza

Kini, menggunakan simulator yang dicipta, anda boleh menguji mana-mana strategi permainan pada cabutan sebenar pada tahun 2022 dan melihat keputusan yang akan dibawanya. Jika anda bermain 1 tiket dalam setiap cabutan, maka gambaran keseluruhan "plum" kelihatan seperti ini:

Di sini:

• Generator ialah permainan di mana dalam setiap cabutan kami memilih nombor rawak yang dicipta oleh penjana kami (dengan berat yang sama).
• Kegemaran ialah permainan di mana dalam setiap cabutan kita menggunakan nombor yang sama – nombor yang paling kerap kalah dalam cabutan sejak dua tahun lalu (27, 32, 11, 14, 34, 40).
• Outsiders – sama, tetapi kami menggunakan nombor lungsur turun yang paling jarang berlaku (12, 18, 26, 10, 21, 6).
• Dingin – dalam semua cabutan kita menggunakan nombor yang tidak jatuh untuk masa yang lama (35, 5, 39, 11, 6, 29).

Seperti yang anda lihat, tidak ada perbezaan yang besar, tetapi penjana nombor rawak berkelakuan lebih baik sedikit daripada "strategi" yang lain.

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

Bermain dalam setiap cabutan dengan satu tiket dengan nombor yang dijana secara rawak (dengan berat yang sama):

Memainkan 10 tiket dalam setiap cabutan dengan nombor yang dijana secara rawak (dengan berat yang sama):

Memainkan 100 tiket dalam setiap cabutan dengan nombor rawak (dengan berat yang sama):

Komen, seperti yang mereka katakan, adalah berlebihan – longkang deposit tidak dapat dielakkan dalam semua kes 🙂