Excel-də lotereya simulyasiyası

Lotereya bəxt üçün ovu deyil,

itirənlər üçün bir ovdur.

Həsəd aparan müntəzəmliklə (və daha tez-tez son vaxtlar) insanlar lotereyalarla bağlı müxtəlif hesablamalarda kömək istəmək üçün mənə yazırlar. Kimsə Excel-də uduşlu nömrələri seçmək üçün gizli alqoritmini həyata keçirmək istəyir, kimsə keçmiş tirajlardan düşmüş nömrələrdə nümunələr tapmaq, kimsə lotereya təşkilatçılarını vicdansız oyunda tutmaq istəyir.

В этой стье мне хотелось бы ответить на часть этих вопросов. Blago, Excel-də tənzimləmə üçün təqlid sənədləri, çoxlu alətlər, çox sayda kompüter, kstati, qəbul edə bilərsiniz və daha çox işləmə vəziyyəti.

Tapşırıq 1. Qazanma ehtimalı

Nümunə olaraq 6 lotereyadan klassik Stoloto 45-nı götürək. Qaydalara əsasən, yalnız 10 rəqəmdən 6 rəqəmin hamısını təxmin edənlər super mükafat alırlar (əgər uduş fondunun balansı əvvəlki tirajlardan yığılıbsa, 45 milyon rubl və ya daha çox). 5-i təxmin etsəniz, 150 min rubl, 4-1500 rubl alacaqsınız. , əgər 3 nömrədən 6-ü varsa, onda 150 rubl, 2 nömrə varsa - biletə xərclənən 50 rublu qaytaracaqsınız. Yalnız birini və ya heç birini təxmin edin - oyun prosesindən yalnız endorfinlər alın.

Qazanmanın riyazi ehtimalı standart funksiyadan istifadə etməklə asanlıqla hesablana bilər NÖMRƏK (Birləşin), belə bir hal üçün Microsoft Excel-də mövcuddur. Bu funksiya M-dən N ədədin birləşmələrinin sayını hesablayır. Beləliklə, bizim “6-dən 45-sı” lotereyamız üçün belə olardı:

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

… 8-ə bərabərdir, bu lotereyada bütün mümkün birləşmələrin ümumi sayı.

Qismən qazanma ehtimalını (2-dan 5-6 ədəd) hesablamaq istəyirsinizsə, əvvəlcə bu cür variantların sayını hesablamalı olacaqsınız, bu da təxmin edilən nömrələrin birləşmələrinin sayının hasilinə bərabərdir. Qalanlardan (6-45) təxmin edilməmiş ədədlərin sayına görə 6 = 39 ədəd. Sonra biz bütün mümkün kombinasiyaların ümumi sayını (8) hər bir seçim üçün alınan uduşların sayına bölürük və hər bir hal üçün uduş ehtimallarını alırıq:

Yeri gəlmişkən, məsələn, Ölkəmizdə təyyarə qəzasında ölmə ehtimalı milyonda 1-də qiymətləndirilir. Ruletdə kazinoda udmaq, hər şeyi bir nömrəyə mərc etmək ehtimalı 1-dən 37-ə qədərdir.

Yuxarıda göstərilənlərin hamısı sizə mane olmadısa və siz hələ də daha da oynamağa hazırsınızsa, davam edin.

Tapşırıq 2. Hər bir nömrənin baş vermə tezliyi

Başlamaq üçün müəyyən nömrələrin hansı tezlikdə düşdüyünü müəyyən edək. İdeal lotereyada təhlil üçün kifayət qədər böyük vaxt intervalı verildikdə, bütün topların uduş nümunəsində olma ehtimalı eyni olmalıdır. Əslində, lotereya barabanının dizayn xüsusiyyətləri və topların çəki forması bu mənzərəni təhrif edə bilər və bəzi toplar üçün düşmə ehtimalı digərlərinə nisbətən daha yüksək/aşağı ola bilər. Bu fərziyyəni praktikada yoxlayaq.

Məsələn, 2020-21-də baş tutan 6 lotereya tirajından bütün 45-ci illərə dair məlumatları onların təşkilatçısı Stoloto-nun veb saytından, təhlil üçün əlverişli, adı ilə belə bir "ağıllı" masa şəklində hazırlanmışdır. tabArxiv Tiraj. Розыгрыши проходят два раза в день (11 утра və 11 vechera), s. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Hər bir nömrənin baş vermə tezliyini hesablamaq üçün funksiyadan istifadə edin COUNTIF (COUNTIF) və ona funksiya əlavə edin TEXT (MƏTN)birrəqəmli ədədlərə əvvəl və sonra aparıcı sıfırları və ulduzları əlavə etmək, beləliklə, COUNTIF B sütununda birləşmənin istənilən yerində ədədin baş verməsini axtarsın. Həmçinin, daha aydınlıq üçün biz nəticələrə görə qrafik quracağıq və tezlikləri çeşidləyəcəyik. azalan qaydada:

Orta hesabla, hər hansı bir top düşməlidir 1459 heç-heçə * 6 top / 45 ədəd = 194,53 dəfə (statistikada məhz belə deyilir) matematiceskim ожиданием), lakin хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), a nekotorыe (10, 21, 6…) naoborot заметно реже (-15%), чем основная масса. Sootvetstvenno, mümkündür istifadə edə bilərsiniz bu informasiya strategiyaları üçün strategiyalar, s. либо ставить на те шары, что выпадют чаще, либо наоборот — делать ставку на редко выпадющие шары в надежде, что они должны нагнать отставание.

Tapşırıq 3. Hansı nömrələr uzun müddətdir çəkilməyib?

Başqa bir strategiya, kifayət qədər çox sayda tirajla gec-tez 1-dən 45-ə qədər mövcud olan hər nömrənin düşəcəyi fikrinə əsaslanır. Beləliklə, uzun müddət qaliblər arasında bəzi nömrələr görünmürsə ("soyuq toplar"), gələcəkdə onlara mərc etməyə cəhd etmək məntiqlidir. 

Можно легко найти все давно не выпадавшие номера, наш arxiv tirajey üçün 2020-21-ci il üçün silinir və funksiyasını istifadə edin DAHA FAZLA (MAÇ). O, hər bir nömrəni axtarmaq üçün yuxarıdan aşağıya (yəni yenidən köhnə qaçışlara) axtaracaq və bu nömrənin sonuncu buraxıldığı qaçışın seriya nömrəsini (ilin sonundan əvvəlinə kimi) verəcək:

Задача 4. Generator sluchaynıx chisel

Başqa bir oyun strategiyası rəqəmləri təxmin edərkən psixoloji faktorun aradan qaldırılmasına əsaslanır. Oyunçu mərc edərək nömrələri seçdikdə, o, şüuraltı olaraq bunu tamamilə rasional olaraq etmir. Statistikaya görə, məsələn, 1-dən 31-ə qədər olan rəqəmlər qalanlardan 70% daha çox seçilir (sevimli tarixlər), 13 daha az seçilir (lənətə gəlmiş onlarla), “şanslı” yeddi olan nömrələr daha çox seçilir və s. Amma biz bütün nömrələri eyni olan maşına (lotereya nağarasına) qarşı oynayırıq, ona görə də şanslarımızı bərabərləşdirmək üçün onları eyni riyazi qərəzsizliklə seçmək məntiqlidir. Bunun üçün Excel-də təsadüfi və ən əsası təkrarlanmayan ədədlər generatoru yaratmalıyıq:

Bunu etmək:

1. adlı “ağıllı” cədvəl yaradaq tableGenerator, burada birinci sütun 1-dən 45-ə qədər olan nömrələrimiz olacaq.
2. İkinci sütunda hər nömrənin çəkisini daxil edin (bir az sonra bizə lazım olacaq). Əgər bütün rəqəmlər bizim üçün eyni dərəcədə dəyərlidirsə və biz onları bərabər ehtimalla seçmək istəyiriksə, onda çəki hər yerdə 1-ə bərabər təyin oluna bilər.
3. Üçüncü sütunda biz funksiyadan istifadə edirik SLCHIS (RAND), Excel-də genereriruet sluchaynoe drobnoe cislo ot 0 to 1, dobaviv k nemu ves from predyduschego stolbca. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) hər birinin çəkisi nəzərə alınmaqla 45 təsadüfi ədəddən ibarət yeni dəst yaradılacaq.
4. Funksiyadan istifadə etdiyimiz dördüncü sütunu əlavə edək RANK (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

İndi funksiyadan istifadə edərək 6-cı dərəcəyə görə ilk altı rəqəmin seçimini etmək qalır DAHA FAZLA (MAÇ):

При нажатии на клавишу F9 Excel vərəqindəki düsturlar yenidən hesablanacaq və hər dəfə yaşıl xanalarda yeni 6 rəqəm dəsti alacağıq. Üstəlik, B sütununda daha böyük çəki təyin edilmiş nömrələr mütənasib olaraq daha yüksək dərəcə alacaq və beləliklə, təsadüfi seçməmizin nəticələrində daha tez-tez görünəcəkdir. Əgər bütün nömrələr üçün çəki eyni təyin edilibsə, onda onların hamısı eyni ehtimalla seçiləcək. Bu yolla 6-dən 45-dan ədalətli və qərəzsiz təsadüfi ədəd generatoru əldə edirik, lakin lazım gələrsə, paylanmanın təsadüfiliyinə düzəlişlər etmək imkanı ilə.

Hər tirajda bir ilə deyil, məsələn, eyni anda iki biletlə oynamaq qərarına gəlsək, hər birində təkrarlanmayan nömrələr seçəcəyik, onda sadəcə altdan yaşıl diapazona əlavə xətlər əlavə edə bilərik, rütbəyə 6, 12, 18 və s. əlavə etmək. d. müvafiq olaraq:

Tapşırıq 5. Excel-də lotereya simulyatoru

Bütün bu mövzunun apotheozu olaraq, Excel-də hər hansı bir strategiyanı sınaya və nəticələri müqayisə edə biləcəyiniz tam hüquqlu bir lotereya simulyatoru yaradaq (optimallaşdırma nəzəriyyəsində oxşar bir şey Monte Karlo metodu da adlanır, lakin daha sadə olacaq. bizim üçün).

Hər şeyi mümkün qədər reallığa yaxınlaşdırmaq üçün bir anlıq təsəvvür edin ki, 1-ci il yanvarın 2022-dir və qarşıda bizi oynamağı planlaşdırdığımız builki püşkatmalar gözləyir. Cədvəldə həqiqi düşmüş rəqəmləri daxil etdim tablTiraži2022, sonrakı hesablamaların rahatlığı üçün əlavə olaraq çəkilmiş nömrələri bir-birindən ayrı sütunlara ayırmaq:

Ayrı bir vərəqdə Oyun adı ilə "ağıllı" cədvəl şəklində modelləşdirmə üçün boşluq yaradın tabIgra aşağıdakı forma:

Burada:

• Yuxarıdakı sarı xanalarda makro üçün 2022-ci ildə iştirak etmək istədiyimiz tirajların sayını (1-82) və hər tirajda oynadığımız biletlərin sayını təyin edəcəyik.
• İlk 11 sütun (AJ) üçün məlumatlar 2022-ci il tiraj vərəqindən makro ilə kopyalanacaq.
• Növbəti altı sütun üçün məlumatlar (KP) makro vərəqdən götürəcəkdir Generator, burada biz təsadüfi ədədlər generatorunu tətbiq etdik (yuxarıda 4-cü məsələyə baxın).
• Q sütununda biz atılan nömrələr ilə funksiyadan istifadə edərək yaradılanlar arasında uyğunluqların sayını hesablayırıq SUMPRODUCT (MƏHSUL).
• V stolbce R maliyyə nəticələrinə görə pul qazanır (bilet üçün minus 50 rubl, bilet üçün isə 50 rubl)
• Sonuncu S sütununda, prosesdəki dinamikanı görmək üçün bütün oyunun ümumi nəticəsini məcmu cəmi hesab edirik.

Və bütün bu strukturu canlandırmaq üçün bizə kiçik bir makro lazımdır. Nişanda geliştirici (İnkişaf etdirici) komanda seçin Visual Basic və ya klaviatura qısa yolundan istifadə edin Dayandırmaq+F11. Sonra menyu vasitəsilə yeni boş modul əlavə edin Daxil et - Modul və oraya aşağıdakı kodu daxil edin:

Sub Lottery() Dim iGames kimi, iTickets As Integer, i As Long, t As Integer, b As As Integer 'WsGame = Worksheets Set ("İqra") Set wsNumbers = Worksheets(")") wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'Road tiraje və kajdom tiraje i = 5 'Tiracy Tabraws-da. ("6:1048576"). Silin 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тиражи 2022 и вставляем на листов Игра wsArchive(ReCells1e). (1, 1).Təyinatı Kopyala:=wsGame.Cells(i, 10) 'Copyruem and in spesialnoy instavkoy zonachenis sgenerirovannıe nömrələr siyahısında Generator wsNumbers.Range("G1:L4").Copy wsGame,C4s. .PasteSpecial Paste:=xlPasteValues ​​i = i + 11 Next b Next t End Sub  

İstədiyiniz ilkin parametrləri sarı xanalara daxil etmək və makronuzu keçmək qalır İnkişaf etdirici - Makroslar (Tərtibatçı — Makroslar) və ya klaviatura qısa yolu Dayandırmaq+F8.

Aydınlıq üçün son sütun üçün oyun zamanı pul balansındakı dəyişikliyi əks etdirən məcmu cəmi ilə diaqram da qura bilərsiniz:

Müxtəlif strategiyaların müqayisəsi

İndi yaradılmış simulyatordan istifadə edərək 2022-ci ildə real tirajlarda istənilən oyun strategiyasını sınaqdan keçirə və onun gətirəcəyi nəticələri görə bilərsiniz. Hər tirajda 1 bilet oynayırsınızsa, "gavalı" nın ümumi şəkli belə görünür:

Burada:

• Generator hər tirajda generatorumuz tərəfindən yaradılmış təsadüfi nömrələri seçdiyimiz bir oyundur (eyni çəki ilə).
• Seçilmişlərə hər tirajda eyni nömrələrdən istifadə etdiyimiz bir oyundur - son iki ildə ən çox heç-heçə edənlərdən (27, 32, 11, 14, 34, 40).
• kənar – eyni, lakin biz ən nadir açılan nömrələrdən istifadə edirik (12, 18, 26, 10, 21, 6).
• Soyuq – bütün tirajlarda çoxdan düşməyən nömrələrdən istifadə edirik (35, 5, 39, 11, 6, 29).

Gördüyünüz kimi, böyük fərq yoxdur, lakin təsadüfi ədədlər generatoru digər "strategiyalardan" bir az daha yaxşı davranır.

Eyni zamanda, çoxlu pullu biletlər və tirajlarda istifadə edə bilərsiniz, daha çox seçim edə bilərsiniz (qrupda belə bir şey yoxdur).

Hər tirajda təsadüfi yaradılan nömrələri olan bir biletlə oynamaq (eyni çəki ilə):

Hər tirajda təsadüfi yaradılmış nömrələrlə (eyni çəki ilə) 10 bilet oynamaq:

Təsadüfi nömrələrlə hər tirajda 100 bilet oynamaq (eyni çəki ilə):

Şərhlər, necə deyərlər, artıqdır - bütün hallarda əmanətin boşaldılması qaçılmazdır 🙂