Simulasi lotere di Excel

Lotre bukanlah perburuan keberuntungan,

itu berburu pecundang.

Dengan keteraturan yang patut ditiru (dan lebih sering akhir-akhir ini), orang-orang menulis kepada saya meminta bantuan dalam berbagai perhitungan yang berkaitan dengan lotere. Seseorang ingin menerapkan algoritme rahasia mereka untuk memilih nomor pemenang di Excel, seseorang ingin menemukan pola dalam angka yang keluar dari undian sebelumnya, seseorang ingin menangkap penyelenggara lotere dalam permainan yang tidak jujur.

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

Tugas 1. Probabilitas menang

Mari kita ambil Stoloto 6 klasik dari 45 lotere sebagai contoh. Menurut aturan, hanya mereka yang menebak 10 angka dari 6 yang menerima hadiah super (45 juta rubel atau lebih jika saldo dana hadiah terakumulasi dari undian sebelumnya). Jika Anda menebak 5, Anda akan menerima 150 ribu rubel, jika 4 – 1500 rubel. , jika 3 angka dari 6, maka 150 rubel, jika 2 angka – Anda akan mengembalikan 50 rubel yang dihabiskan untuk tiket. Tebak hanya satu atau tidak sama sekali – dapatkan hanya endorfin dari proses permainan.

Probabilitas matematis untuk menang dapat dengan mudah dihitung menggunakan fungsi standar NOMORCOMB (MENGGABUNGKAN), yang tersedia di Microsoft Excel untuk kasus seperti itu. Fungsi ini menghitung jumlah kombinasi angka N dari M. Jadi untuk lotere “6 dari 45” kami adalah:

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

… yang sama dengan 8, jumlah total semua kemungkinan kombinasi dalam lotere ini.

Jika Anda ingin menghitung probabilitas untuk kemenangan parsial (2-5 angka dari 6), maka Anda harus terlebih dahulu menghitung jumlah opsi tersebut, yang sama dengan produk dari jumlah kombinasi angka yang ditebak dari 6 dengan jumlah angka yang tidak terjawab dari sisa (45-6) = 39 angka. Kemudian kami membagi jumlah total semua kemungkinan kombinasi (8) dengan jumlah kemenangan yang diterima untuk setiap opsi – dan kami mendapatkan probabilitas kemenangan untuk setiap kasus:

Omong-omong, kemungkinan, misalnya, kematian dalam kecelakaan pesawat di Negara Kita diperkirakan sekitar 1 dalam sejuta. Dan kemungkinan menang di kasino di roulette, mempertaruhkan semuanya pada satu nomor adalah 1 hingga 37.

Jika semua hal di atas tidak menghentikan Anda dan Anda masih siap untuk bermain lebih lanjut, lanjutkan.

Tugas 2. Frekuensi kemunculan setiap angka

Untuk memulainya, mari kita tentukan dengan frekuensi berapa angka-angka tertentu jatuh. Dalam lotere yang ideal, dengan interval waktu yang cukup besar untuk analisis, semua bola harus memiliki probabilitas yang sama untuk menjadi sampel pemenang. Pada kenyataannya, fitur desain drum lotere dan bentuk berat bola dapat mendistorsi gambar ini, dan untuk beberapa bola kemungkinan jatuh mungkin lebih tinggi/rendah daripada yang lain. Mari kita uji hipotesis ini dalam praktik.

Mari kita ambil, misalnya, data semua tahun 2020 dari 21 undian lotere yang terjadi pada 6-45 dari situs web penyelenggara mereka Stoloto, dirancang dalam bentuk tabel "pintar", nyaman untuk dianalisis, dengan nama tabArsip Sirkulasi. озыгрыши оходят а аза ень (в 11 а 11 ечера), .е. ой аблице ас олторы ажей-строк — олне остаточная ачала орка анализа:

Untuk menghitung frekuensi kemunculan setiap angka, gunakan fungsi COUNTIF (HITUNGAN) dan tambahkan fungsi ke dalamnya TEKS (TEKS)untuk menambahkan nol di depan dan tanda bintang sebelum dan sesudah ke angka satu digit, sehingga COUNTIF mencari kemunculan angka di mana saja dalam kombinasi di kolom B. Selain itu, untuk kejelasan yang lebih besar, kami akan membuat bagan berdasarkan hasil dan mengurutkan frekuensi dalam urutan menurun:

Rata-rata, setiap bola harus jatuh 1459 seri * 6 bola / 45 angka = 194,53 kali (inilah yang disebut dalam statistik атематическим ожиданием), о орошо о, о екоторые а (27, 32, 11…) адали аметно аще (+18%), а екоторые (10, 21, 6…) аоборот осноевно %), оответственно, ожно опробовать ользовать ормацию атегии а, .е. о авить а е ары, о адают аще, о аоборот — елать авку а едко адающие ары адежде, о он ад .

Tugas 3. Nomor apa yang sudah lama tidak ditarik?

Strategi lain didasarkan pada gagasan bahwa dengan jumlah undian yang cukup besar, cepat atau lambat setiap nomor dari semua yang tersedia dari 1 hingga 45 akan rontok. Jadi jika beberapa nomor belum muncul di antara pemenang untuk waktu yang lama (“bola dingin”), maka masuk akal untuk mencoba bertaruh pada mereka di masa depan. 

ожно егко айти е авно е адавшие омера, если отсортировать аш архив ажей а 2020-21 од о анию аты ользовать LEBIH TERBUKTI (COCOK). Ini akan mencari dari atas ke bawah (yaitu dari yang baru ke yang lama) untuk mencari setiap nomor dan memberikan nomor seri dari run (dihitung dari akhir tahun ke awal) di mana nomor ini terakhir dijatuhkan:

адача 4. енератор айных ел

Strategi permainan lainnya didasarkan pada menghilangkan faktor psikologis saat menebak angka. Ketika seorang pemain memilih angka dengan membuat taruhannya, dia secara tidak sadar melakukan ini tidak sepenuhnya rasional. Menurut statistik, misalnya, angka dari 1 hingga 31 dipilih 70% lebih sering daripada yang lain (tanggal favorit), 13 dipilih lebih jarang (selusin), angka yang mengandung tujuh "keberuntungan" lebih sering dipilih, dll. Tapi kami bermain melawan mesin (drum lotere) yang semua angkanya sama, jadi masuk akal untuk memilihnya dengan ketidakberpihakan matematis yang sama untuk menyamakan peluang kami. Untuk melakukan ini, kita perlu membuat generator angka acak dan – yang paling penting – tidak berulang di Excel:

Untuk melakukan ini:

1. Mari kita buat tabel "pintar" bernama tabelGenerator, di mana kolom pertama akan menjadi nomor kami dari 1 hingga 45.
2. Di kolom kedua, masukkan bobot untuk setiap nomor (kita akan membutuhkannya nanti). Jika semua angka sama berharganya bagi kami dan kami ingin memilihnya dengan probabilitas yang sama, maka bobotnya dapat disetel sama dengan 1 di mana-mana.
3. Di kolom ketiga kami menggunakan fungsi SLCHIS (RAND), оторая Excel енерирует айное обное о от 0 о 1, обавив ему ес едыдущего олбца. аким образом аждый аз ересчёте а (нажатии а авишу F9) satu set baru dari 45 nomor acak akan dihasilkan, dengan mempertimbangkan bobot untuk masing-masingnya.
4. Mari tambahkan kolom keempat, di mana menggunakan fungsi RANK (PANGKAT) анг (позицию опе) аждого ел.

Sekarang tinggal memilih enam angka pertama dengan peringkat 6 menggunakan fungsi LEBIH TERBUKTI (COCOK):

ажатии а авишу F9 rumus pada lembar Excel akan dihitung ulang dan setiap kali kita akan mendapatkan set baru 6 angka dalam sel hijau. Selain itu, angka-angka yang bobotnya lebih besar ditetapkan di kolom B akan menerima peringkat yang lebih tinggi secara proporsional dan, dengan demikian, lebih sering muncul dalam hasil sampel acak kami. Jika bobot untuk semua angka disetel sama, maka semuanya akan dipilih dengan probabilitas yang sama. Dengan cara ini kita mendapatkan generator angka acak yang adil dan tidak bias dari 6 dari 45, tetapi dengan kemampuan untuk melakukan penyesuaian keacakan distribusi jika perlu.

Jika kami memutuskan untuk bermain di setiap undian bukan dengan satu, tetapi, misalnya, dengan dua tiket sekaligus, di mana masing-masing kami akan memilih nomor yang tidak berulang, maka kami cukup menambahkan garis tambahan dari bawah ke rentang hijau, menambahkan 6, 12, 18, dll ke peringkat. d. masing-masing:

Tugas 5. Simulator Lotere di Excel

Sebagai pendewaan dari seluruh topik ini, mari buat simulator lotere lengkap di Excel, di mana Anda dapat mencoba strategi apa pun dan membandingkan hasilnya (dalam teori pengoptimalan, sesuatu yang serupa juga disebut metode Monte Carlo, tetapi akan lebih sederhana untuk kita).

Untuk membuat segalanya sedekat mungkin dengan kenyataan, bayangkan sejenak bahwa ini adalah 1 Januari 2022 dan kami memiliki undian tahun ini di depan kami, di mana kami berencana untuk bermain. Saya memasukkan angka nyata yang dijatuhkan di tabel tablTiraži2022, memisahkan nomor yang ditarik tambahan dari satu sama lain ke dalam kolom terpisah untuk kenyamanan perhitungan selanjutnya:

Di lembar terpisah Permainan buat kosong untuk pemodelan dalam bentuk tabel "pintar" dengan nama tabIgra bentuk berikut:

Sini:

• Di sel kuning di atas, kami akan mengatur makro jumlah undian pada tahun 2022 di mana kami ingin berpartisipasi (1-82) dan jumlah tiket yang kami mainkan di setiap undian.
• Data untuk 11 kolom pertama (AJ) akan disalin oleh makro dari drawing tahun 2022.
• Data untuk enam kolom (KP) berikutnya yang akan diambil makro dari lembar Generator, di mana kami telah menerapkan generator nomor acak (lihat masalah 4 di atas).
• Di kolom Q, kami menghitung jumlah kecocokan antara angka yang dijatuhkan dan yang dihasilkan menggunakan fungsi SUMPRODUK (SUMPRODUK).
• олбце R вычисляем ансовый результат (если не али, о 50 ей а ет, если али, о — 50 . а ет)
• Pada kolom terakhir S, kami menganggap hasil keseluruhan dari keseluruhan permainan sebagai total kumulatif untuk melihat dinamika dalam prosesnya.

Dan untuk menghidupkan kembali seluruh struktur ini, kita membutuhkan makro kecil. Di tab pembangun (Pengembang) pilih tim Visual Basic atau gunakan pintasan keyboard lain+F11. Kemudian tambahkan modul kosong baru melalui menu Sisipkan – Modul dan masukkan kode berikut di sana:

Sub Lottery() Redupkan 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 ' ("6:1048576").Hapus 'очищаем арые анные For t = 1 To iGames For b = 1 To iTickets 'копируем авшие омера а ажи 2022 авляем а а archive + 1Cа ws. (1, 1).Salin Tujuan:=wsGame.Cells(i, 10) .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Next b Next t End Sub  

Tetap memasukkan parameter awal yang diinginkan dalam sel kuning dan menjalankan makro melalui Pengembang – Makro (Pengembang — Makro) atau pintasan keyboard lain+F8.

Untuk kejelasan, Anda juga dapat membuat diagram untuk kolom terakhir dengan total kumulatif, yang mencerminkan perubahan saldo uang selama permainan:

Perbandingan strategi yang berbeda

Sekarang, dengan menggunakan simulator yang dibuat, Anda dapat menguji strategi permainan apa pun pada undian nyata pada tahun 2022 dan melihat hasil yang akan dihasilkannya. Jika Anda memainkan 1 tiket di setiap undian, maka gambaran keseluruhan "plum" terlihat seperti ini:

Sini:

• Generator adalah permainan di mana dalam setiap undian kami memilih nomor acak yang dibuat oleh generator kami (dengan bobot yang sama).
• Favorit adalah permainan di mana dalam setiap undian kami menggunakan angka yang sama – angka yang paling sering kalah dalam undian selama dua tahun terakhir (27, 32, 11, 14, 34, 40).
• Outsiders – sama, tetapi kami menggunakan nomor drop-down paling langka (12, 18, 26, 10, 21, 6).
• Dingin – dalam semua undian kami menggunakan angka yang sudah lama tidak keluar (35, 5, 39, 11, 6, 29).

Seperti yang Anda lihat, tidak ada perbedaan besar, tetapi generator angka acak berperilaku sedikit lebih baik daripada "strategi" lainnya.

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

Bermain di setiap undian dengan satu tiket dengan nomor yang dibuat secara acak (dengan bobot yang sama):

Memainkan 10 tiket di setiap undian dengan nomor yang dibuat secara acak (dengan bobot yang sama):

Memainkan 100 tiket di setiap undian dengan nomor acak (dengan bobot yang sama):

Komentar, seperti yang mereka katakan, berlebihan – pengurasan deposit tidak dapat dihindari dalam semua kasus