Excel'de piyango simülasyonu

Piyango şans avı değildir,

kaybedenler için bir avdır.

Kıskanılacak bir düzenlilikle (ve son zamanlarda daha sık olarak), insanlar bana piyangolarla ilgili çeşitli hesaplamalarda yardım istemek için yazıyorlar. Birisi Excel'de kazanan sayıları seçmek için gizli algoritmasını uygulamak istiyor, biri geçmiş çekilişlerden düşen sayılardaki kalıpları bulmak istiyor, biri dürüst olmayan bir oyunda piyango organizatörlerini yakalamak istiyor.

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

Görev 1. Kazanma olasılığı

Örnek olarak 6 piyangodan klasik Stoloto 45'yı ele alalım. Kurallara göre, yalnızca 10'dan 6 sayının tümünü tahmin edenler bir süper ödül alır (ödül fonunun bakiyesi önceki çekilişlerden birikmişse 45 milyon ruble veya daha fazla). 5 tahmin ettiyseniz, 150 – 4 ruble ise 1500 bin ruble alacaksınız. , eğer 3'dan 6 sayı ise 150 ruble, 2 sayı ise - bilete harcanan 50 ruble iade edeceksiniz. Yalnızca birini tahmin edin veya hiç birini tahmin etmeyin – oyun sürecinden yalnızca endorfin alın.

Kazanmanın matematiksel olasılığı, standart fonksiyon kullanılarak kolayca hesaplanabilir. NUMBERCOMB (KOMBİNE), böyle bir durum için Microsoft Excel'de mevcuttur. Bu fonksiyon, M'den N sayının kombinasyon sayısını hesaplar. Dolayısıyla, "6 üzerinden 45" piyangomuz için şöyle olur:

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

… 8'e eşittir, bu piyangodaki tüm olası kombinasyonların toplam sayısı.

Kısmi bir kazanma olasılığını hesaplamak istiyorsanız (2 üzerinden 5-6 sayı), o zaman önce bu tür seçeneklerin sayısını hesaplamanız gerekir; bu, tahmin edilen sayıların kombinasyonlarının sayısının çarpımına eşittir. 6 sayısı ile tahmin edilmeyen sayıların dışında kalan (45-6) = 39 sayıdır. Ardından, tüm olası kombinasyonların toplam sayısını (8) her seçenek için alınan kazanç sayısına böleriz ve her durum için kazanma olasılıklarını elde ederiz:

Bu arada, örneğin Ülkemizde bir uçak kazasında ölme olasılığı milyonda 1 olarak tahmin ediliyor. Ve bir kumarhanede rulette kazanma olasılığı, her şeyi tek bir sayıya yatırarak 1'den 37'ye kadardır.

Yukarıdakilerin tümü sizi durdurmadıysa ve hala oynamaya hazırsanız, devam edin.

Görev 2. Her bir sayının oluşma sıklığı

Başlamak için, belirli sayıların hangi sıklıkta düştüğünü belirleyelim. İdeal bir piyangoda, analiz için yeterince geniş bir zaman aralığı verildiğinde, tüm topların kazanan örnekte olma olasılığı aynı olmalıdır. Gerçekte, piyango davulunun tasarım özellikleri ve topların ağırlık şekli bu resmi bozabilir ve bazı toplar için düşme olasılığı diğerlerinden daha yüksek/düşük olabilir. Bu hipotezi pratikte test edelim.

Örneğin, 2020-21'te gerçekleşen 6 piyango çekilişinden 45'nin tamamına ilişkin verileri, organizatörleri Stoloto'nun web sitesinden, böyle bir “akıllı” tablo şeklinde tasarlanmış, adıyla analize uygun olarak alalım. tabArşiv Dolaşımı. Розыгрыши проходят два раза в день (11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Her sayının oluşma sıklığını hesaplamak için şu işlevi kullanın: COUNTIF (EĞERSAY) ve ona bir işlev ekleyin METİN (METİN)EĞERSAY, B sütunundaki kombinasyonun herhangi bir yerinde bir sayının oluşumunu aramak için tek basamaklı sayıların önüne ve arkasına sıfırlar ve yıldız işaretleri eklemek için. Ayrıca, daha fazla netlik için sonuçlara göre bir grafik oluşturacağız ve frekansları sıralayacağız. azalan sırayla:

Ortalama olarak, herhangi bir top 1459 beraberlik * 6 top / 45 sayı = 194,53 kez düşmelidir (istatistiklerde tam olarak buna denir matematyческим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (%+18), а некоторые (10, 21, 6… Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадотевие станинары.

Görev 3. Uzun süredir hangi sayılar çekilmedi?

Başka bir strateji, yeterince büyük sayıda çekilişle, er ya da geç 1'den 45'e kadar mevcut olan her sayının düşmesi gerektiği fikrine dayanmaktadır. Bu nedenle, kazananlar arasında uzun süredir bazı sayılar görünmüyorsa (“soğuk toplar”), gelecekte bunlara bahis yapmayı denemek mantıklıdır. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей архив давно не выпадавшие номера, если отсортировать наш архив тиражей архив тиражей ьза 2020-21 гоыад паново DAHA MARUZ KALMIŞ (KİBRİT). Her numarayı aramak için yukarıdan aşağıya (yani yeniden eskiye) arama yapacak ve bu sayının en son bırakıldığı koşunun seri numarasını (yıl sonundan başlangıca kadar sayarak) verecektir:

Задача 4. Jeneratör случайных чисел

Başka bir oyun stratejisi, sayıları tahmin ederken psikolojik faktörü ortadan kaldırmaya dayanmaktadır. Bir oyuncu bahsini yaparak sayıları seçtiğinde, bunu tamamen rasyonel olarak değil, bilinçaltında yapar. İstatistiklere göre, örneğin, 1'den 31'e kadar olan sayılar diğerlerinden (favori tarihler) %70 daha sık seçilir, 13 daha az sıklıkla seçilir (lanet düzine), “şanslı” yediyi içeren sayılar daha sık seçilir, vb. Ama tüm sayıları aynı olan bir makineye (piyango davulu) karşı oynuyoruz, bu yüzden şansımızı eşitlemek için onları aynı matematiksel tarafsızlıkla seçmek mantıklı. Bunu yapmak için, Excel'de rastgele ve - en önemlisi - tekrarlanmayan sayılar üreteci oluşturmamız gerekiyor:

Bunu yapmak için:

1. adında bir “akıllı” tablo oluşturalım. tabloJeneratör, burada ilk sütun 1'den 45'e kadar olan sayılarımız olacaktır.
2. İkinci sütuna her sayının ağırlığını girin (biraz sonra buna ihtiyacımız olacak). Tüm sayılar bizim için eşit değerdeyse ve bunları eşit olasılıkla seçmek istiyorsak, ağırlık her yerde 1'e eşitlenebilir.
3. Üçüncü sütunda işlevi kullanıyoruz SLCHIS (RAND), Excel'de которая которая венерирует случайное дробное число от 0 до 1, добавив к нему вес из пребыдущего столло Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) her birinin ağırlığı dikkate alınarak yeni bir 45 rasgele sayı kümesi oluşturulacaktır.
4. Dördüncü bir sütun ekleyelim, burada işlev kullanılır RANK (RÜTBE) вычислим ранг (позицию в топе) для каждого из чисел.

Şimdi, işlevi kullanarak sıra 6'ya göre ilk altı sayının seçimini yapmak için kalır. DAHA MARUZ KALMIŞ (KİBRİT):

При нажатии на клавишу F9 Excel sayfasındaki formüller yeniden hesaplanacak ve her seferinde yeşil hücrelerde yeni bir 6 sayı seti alacağız. Ayrıca, B sütununda daha büyük bir ağırlığın ayarlandığı sayılar, orantılı olarak daha yüksek bir sıra alacak ve bu nedenle, rastgele örneklemimizin sonuçlarında daha sık görünecektir. Tüm sayıların ağırlığı aynı olarak ayarlanırsa, hepsi aynı olasılıkla seçilecektir. Bu şekilde, 6 üzerinden 45'lık adil ve tarafsız bir rastgele sayı üreteci elde ederiz, ancak gerekirse dağılımın rastgeleliğinde ayarlamalar yapma yeteneği ile.

Her çekilişte bir taneyle değil, örneğin, her birinde tekrarlanmayan sayıları seçeceğimiz iki biletle oynamaya karar verirsek, alttan yeşil aralığa ek satırlar ekleyebiliriz, sıralamaya 6, 12, 18 vb. ekleyerek. d. sırasıyla:

Görev 5. Excel'de Piyango Simülatörü

Tüm bu konunun bir özeti olarak, Excel'de herhangi bir stratejiyi deneyebileceğiniz ve sonuçları karşılaştırabileceğiniz tam teşekküllü bir piyango simülatörü oluşturalım (optimizasyon teorisinde buna benzer bir şeye Monte Carlo yöntemi de denir, ancak daha basit olacaktır) bizim için).

Her şeyi gerçeğe en yakın hale getirmek için, bir an için 1 Ocak 2022'nin olduğunu ve önümüzde oynamayı planladığımız bu yılın çekilişlerinin olduğunu hayal edin. Gerçek düşen sayıları tabloya girdim tablTiraži2022, sonraki hesaplamaların kolaylığı için ek olarak çizilen sayıları birbirinden ayrı sütunlara ayırarak:

Ayrı bir sayfada oyun adıyla “akıllı” bir tablo şeklinde modelleme için bir boşluk oluşturun tabIgra aşağıdaki form:

İşte:

• Yukarıdaki sarı hücrelerde 2022'de katılmak istediğimiz çekiliş sayısını (1-82) ve her çekilişte oynadığımız bilet sayısını makro için ayarlayacağız.
• İlk 11 sütunun (AJ) verileri makro tarafından 2022 çizim sayfasından kopyalanacaktır.
• Makronun sayfadan alacağı sonraki altı sütuna (KP) ilişkin veriler Jeneratör, burada rasgele sayı üreteci uyguladık (yukarıdaki sorun 4'e bakın).
• Q sütununda, bırakılan sayılar ile işlev kullanılarak oluşturulanlar arasındaki eşleşmelerin sayısını sayarız. SUMPRODUCT (TOPLAMÇARPIM).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если визоли 50)
• Son sütun S'de, süreçteki dinamikleri görmek için tüm oyunun genel sonucunu kümülatif bir toplam olarak ele alıyoruz.

Ve tüm bu yapıyı canlandırmak için küçük bir makroya ihtiyacımız var. sekmesinde geliştirici (Geliştirici) bir takım seç Visual Basic veya klavye kısayolunu kullanın Ara Toplam+F11. Ardından menü aracılığıyla yeni bir boş modül ekleyin Ekle – Modül ve şu kodu oraya girin:

Alt Piyango() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets Set() wsGame = Worksheet"s Set("sNr)s(" wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6:1048576").Sil 'очищаем старые данные için t = 1 için iGames İçin b = 1 için iTickets için 'копируем выигравшие номера с листа Таchiражим 2022 için, (1, 1).Kopya Hedefi:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированные нолменра с) wsgame. .PasteSpecial Paste:=xlPasteValues ​​i = i + 10 Sonraki b Sonraki t End Sub  

Sarı hücrelere istenen başlangıç ​​parametrelerini girmek ve makroyu çalıştırmak için kalır. Geliştirici – Makrolar (Geliştirici — Makrolar) veya klavye kısayolu Ara Toplam+F8.

Netlik sağlamak için, oyun sırasında para dengesindeki değişikliği yansıtan kümülatif bir toplam ile son sütun için bir diyagram da oluşturabilirsiniz:

Farklı stratejilerin karşılaştırılması

Şimdi, oluşturulan simülatörü kullanarak, herhangi bir oyun stratejisini 2022'de gerçek çekilişler üzerinde test edebilir ve getireceği sonuçları görebilirsiniz. Her çekilişte 1 bilet oynarsanız, "erik" in genel resmi şuna benzer:

İşte:

• Jeneratör her çekilişte jeneratörümüz tarafından oluşturulan rastgele sayıları (aynı ağırlıkta) seçtiğimiz bir oyundur.
• Favoriler her çekilişte aynı sayıları kullandığımız bir oyundur - son iki yılda beraberelerde en sık düşenler (27, 32, 11, 14, 34, 40).
• Yabancılar – aynı, ancak en nadir açılan sayıları kullanıyoruz (12, 18, 26, 10, 21, 6).
• Soğuk – tüm çekilişlerde uzun süredir düşmeyen sayılar kullanıyoruz (35, 5, 39, 11, 6, 29).

Gördüğünüz gibi, büyük bir fark yok, ancak rastgele sayı üreteci diğer “stratejilerden” biraz daha iyi davranıyor.

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

Her çekilişte rastgele oluşturulmuş sayılarla (aynı ağırlıkta) bir biletle oynamak:

Her çekilişte rastgele oluşturulmuş sayılarla (aynı ağırlıkta) 10 bilet oynamak:

Her çekilişte rastgele sayılarla (aynı ağırlıkta) 100 bilet oynamak:

Yorumlar, dedikleri gibi gereksizdir - her durumda depozito tahliyesi kaçınılmazdır 🙂