Symulacja loterii w Excelu

Loteria to nie polowanie na szczęście,

to polowanie na przegranych.

Z godną pozazdroszczenia regularnością (a ostatnio coraz częściej) ludzie piszą do mnie prosząc o pomoc w różnych obliczeniach związanych z loteriami. Ktoś chce zaimplementować swój tajny algorytm wybierania zwycięskich liczb w Excelu, ktoś chce znaleźć wzorce w liczbach, które wypadły z poprzednich losowań, ktoś chce przyłapać organizatorów loterii w nieuczciwej grze.

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

Zadanie 1. Prawdopodobieństwo wygranej

Weźmy jako przykład klasyczny Stoloto 6 z 45 loterii. Zgodnie z zasadami tylko ci, którzy odgadli wszystkie 10 liczb z 6, otrzymują super nagrodę (45 milionów rubli lub więcej, jeśli saldo funduszu nagród zgromadziło się z poprzednich losowań). Jeśli odgadłeś 5, otrzymasz 150 tysięcy rubli, jeśli 4 – 1500 rubli. , jeśli 3 liczby z 6, to 150 rubli, jeśli 2 liczby – zwrócisz 50 rubli wydanych na bilet. Zgadnij tylko jeden lub żaden – uzyskaj tylko endorfiny z procesu gry.

Matematyczne prawdopodobieństwo wygranej można łatwo obliczyć za pomocą standardowej funkcji LICZBA GRZEBIENI (POŁĄCZYĆ), który w takim przypadku jest dostępny w programie Microsoft Excel. Ta funkcja oblicza liczbę kombinacji liczb N z M. Tak więc dla naszej loterii „6 z 45” byłoby to:

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

… co równa się 8, czyli łącznej liczbie wszystkich możliwych kombinacji w tej loterii.

Jeśli chcesz obliczyć prawdopodobieństwo częściowej wygranej (2-5 liczb z 6), to najpierw musisz obliczyć liczbę takich opcji, która jest równa iloczynowi liczby kombinacji odgadniętych liczb z 6 przez liczbę niezgadniętych liczb z pozostałych (45-6) = 39 liczb. Następnie dzielimy łączną liczbę wszystkich możliwych kombinacji (8) przez otrzymaną liczbę wygranych dla każdej opcji – i otrzymujemy prawdopodobieństwa wygranej dla każdego przypadku:

Nawiasem mówiąc, prawdopodobieństwo np. śmierci w katastrofie lotniczej w naszym kraju szacuje się na około 1 do miliona. A prawdopodobieństwo wygranej w kasynie w ruletce, obstawianie wszystkiego na jeden numer wynosi od 1 do 37.

Jeśli wszystko to cię nie powstrzymało i nadal jesteś gotowy do dalszej gry, kontynuuj.

Zadanie 2. Częstotliwość występowania każdej liczby

Na początek ustalmy, z jaką częstotliwością wypadają określone liczby. W idealnej loterii, przy wystarczająco dużym przedziale czasu na analizę, wszystkie kule powinny mieć takie samo prawdopodobieństwo znalezienia się w zwycięskiej próbie. W rzeczywistości cechy konstrukcyjne bębna loterii i ciężar kulek mogą zniekształcić ten obraz, a w przypadku niektórych piłek prawdopodobieństwo wypadnięcia może być wyższe/mniejsze niż w przypadku innych. Przetestujmy tę hipotezę w praktyce.

Weźmy dla przykładu dane o wszystkich 2020 z 21 losowań loterii, które odbyły się w godz. tabObieg archiwum. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Aby obliczyć częstotliwość występowania każdej liczby, użyj funkcji COUNTIF (LICZ.JEŻELI) i dodaj do niego funkcję TEKST (TEKST)aby dodać wiodące zera i gwiazdki przed i po liczbach jednocyfrowych, aby LICZ.JEŻELI szukało wystąpienia liczby w dowolnym miejscu kombinacji w kolumnie B. Ponadto, dla większej przejrzystości, zbudujemy wykres według wyników i posortujemy częstotliwości w kolejności malejącej:

Średnio każda piłka powinna spaść 1459 remisów * 6 piłek / 45 liczb = 194,53 razy (dokładnie tak się nazywa w statystykach matematycy ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно аще (+15%), а некоторые (XNUMX, XNUMX, XNUMX…) наоборот замесет Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те ары, что выпадают чаще, либо наоборот — делать ставку на редко выпададат аще, либо наоборот — делать ставку на редко выпададат ары выпадат ае, делать ставку на редко выпададат ае, делать ставку

Zadanie 3. Jakie liczby nie były losowane od dawna?

Inna strategia opiera się na założeniu, że przy odpowiednio dużej liczbie losowań prędzej czy później każda liczba ze wszystkich dostępnych od 1 do 45 powinna wypaść. Jeśli więc niektóre liczby od dawna nie pojawiły się wśród zwycięzców („zimne kule”), logiczne jest, aby spróbować obstawiać je w przyszłości. 

Можно легко найти все давно не выпадавшие номера, przede wszystkim отсортировать наш архив тиражей z dnia 2020-21 grudnia XNUMX r. WIĘCEJ EKSPOZYCYJNE (MECZ). Będzie przeszukiwać od góry do dołu (tj. od nowych do starych przebiegów), aby wyszukać każdy numer i podać numer seryjny przebiegu (licząc od końca roku do początku), w którym ten numer został ostatnio usunięty:

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

Inna strategia gry polega na wyeliminowaniu czynnika psychologicznego podczas zgadywania liczb. Kiedy gracz wybiera liczby, obstawiając swój zakład, podświadomie robi to nie do końca racjonalnie. Według statystyk np. liczby od 1 do 31 są wybierane 70% częściej niż pozostałe (ulubione daty), 13 rzadziej (cholera tuzin), częściej wybierane są liczby zawierające „szczęśliwą” siódemkę itp. Ale gramy z maszyną (bęben loteryjny), dla której wszystkie liczby są takie same, więc warto wybrać je z taką samą matematyczną bezstronnością, aby wyrównać nasze szanse. W tym celu musimy stworzyć generator liczb losowych i – co najważniejsze – niepowtarzalnych liczb w Excelu:

Aby to zrobić:

1. Stwórzmy „inteligentną” tabelę o nazwie TabelaGenerator, gdzie w pierwszej kolumnie będą nasze liczby od 1 do 45.
2. W drugiej kolumnie wpisz wagę dla każdej liczby (będziemy jej potrzebować nieco później). Jeśli wszystkie liczby są dla nas jednakowo cenne i chcemy je wybrać z równym prawdopodobieństwem, to wagę można ustawić wszędzie na 1.
3. W trzeciej kolumnie używamy funkcji SLCHIS (SKRAJ), которая w Excel генерирует случайное дробное исло от 0 do 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) zostanie wygenerowany nowy zestaw 45 liczb losowych z uwzględnieniem wagi dla każdej z nich.
4. Dodajmy czwartą kolumnę, w której używamy funkcji RANGA (RANGA) вычислим ранг (позицию в топе) для каждого из чисел.

Teraz pozostaje dokonać wyboru pierwszych sześciu liczb według rangi 6 za pomocą funkcji WIĘCEJ EKSPOZYCYJNE (MECZ):

При нажатии на клавишу F9 formuły w arkuszu Excela zostaną przeliczone i za każdym razem otrzymamy nowy zestaw 6 liczb w zielonych komórkach. Ponadto liczby, dla których w kolumnie B ustawiono większą wagę, otrzymają proporcjonalnie wyższą rangę i tym samym częściej będą pojawiać się w wynikach naszej próby losowej. Jeśli waga dla wszystkich liczb jest taka sama, to wszystkie zostaną wybrane z takim samym prawdopodobieństwem. W ten sposób otrzymujemy sprawiedliwy i bezstronny generator liczb losowych 6 z 45, ale z możliwością dostosowania losowości rozkładu, jeśli to konieczne.

Jeśli zdecydujemy się grać w każdym losowaniu nie jednym, ale np. dwoma losami na raz, w każdym z których wybierzemy nie powtarzające się liczby, to możemy po prostu dodać dodatkowe linie od dołu do zielonego zakresu, dodanie 6, 12, 18 itd. do rangi. d. odpowiednio:

Zadanie 5. Symulator loterii w Excelu

Jako apoteozę tego całego tematu stwórzmy pełnoprawny symulator loterii w Excelu, w którym można wypróbować dowolne strategie i porównać wyniki (w teorii optymalizacji coś podobnego nazywa się też metodą Monte Carlo, ale będzie prostsze dla nas).

Aby wszystko było jak najbardziej zbliżone do rzeczywistości, wyobraźmy sobie przez chwilę, że jest 1 stycznia 2022 roku i przed nami tegoroczne losowania, w których planujemy zagrać. Wpisałem w tabelce rzeczywiste upuszczone liczby tablTiraži2022, rozdzielając dodatkowo wylosowane liczby od siebie na osobne kolumny dla wygody późniejszych obliczeń:

Na osobnym arkuszu gra stwórz blankiet do modelowania w postaci „inteligentnej” tabeli z nazwą zakładkaIgra następujący formularz:

Tutaj:

• W żółtych komórkach powyżej ustawimy dla makra liczbę losowań w 2022 roku, w których chcemy wziąć udział (1-82) oraz liczbę losów, które gramy w każdym losowaniu.
• Dane dla pierwszych 11 kolumn (AJ) zostaną skopiowane przez makro z arkusza losowania 2022.
• Dane dla kolejnych sześciu kolumn (KP), które makro pobierze z arkusza generator, gdzie zaimplementowaliśmy generator liczb losowych (patrz problem 4 powyżej).
• W kolumnie Q liczymy liczbę dopasowań między liczbami odrzuconymi a tymi wygenerowanymi za pomocą funkcji SUMPRODUCT (SUMA ILOCZYN).
• В столбце R вычисляем финансовый результат (если не выиграли, do min.
• W ostatniej kolumnie S bierzemy pod uwagę ogólny wynik całej gry jako skumulowaną sumę, aby zobaczyć dynamikę procesu.

A żeby ożywić całą tę strukturę, potrzebujemy małego makra. Na karcie wywoływacz (Deweloper) wybierz drużynę Visual Basic lub użyj skrótu klawiaturowego inny+F11. Następnie dodaj nowy pusty moduł za pomocą menu Wstaw – moduł i wpisz tam następujący kod:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов w цажражебрата = w („5:6”) (1048576, 1).Copy Destination:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листавляем специальной вставкой значений сгенерированные номера с листавляем специальной вставкой значений сгенерированные номера с листавляем специальной вставкой значений сгенерированные номера с листа. .PasteSpecial Paste:=xlPasteValues ​​i = i + 2022 Dalej b Dalej t Koniec Sub  

Pozostaje wprowadzić żądane parametry początkowe w żółtych komórkach i uruchomić makro Deweloper – Makra (Deweloper — Makra) lub skrót klawiaturowy inny+F8.

Dla jasności możesz również zbudować diagram dla ostatniej kolumny ze skumulowaną sumą, odzwierciedlającą zmianę salda pieniężnego podczas gry:

Porównanie różnych strategii

Teraz, korzystając ze stworzonego symulatora, możesz przetestować dowolną strategię gry na prawdziwych losowaniach w 2022 roku i zobaczyć, jakie wyniki przyniesie. Jeśli zagrasz 1 los w każdym losowaniu, ogólny obraz „śliwki” wygląda mniej więcej tak:

Tutaj:

• generator to gra, w której w każdym losowaniu wybieramy losowe liczby stworzone przez nasz generator (o tej samej wadze).
• Ulubione to gra, w której w każdym losowaniu używamy tych samych liczb – tych, które najczęściej wypadały w losowaniach w ciągu ostatnich dwóch lat (27, 32, 11, 14, 34, 40).
• Outsiders – to samo, ale używamy najrzadszych liczb z listy rozwijanej (12, 18, 26, 10, 21, 6).
• Zimno – we wszystkich losowaniach używamy numerów, które od dawna nie wypadały (35, 5, 39, 11, 6, 29).

Jak widać, nie ma dużej różnicy, ale generator liczb losowych zachowuje się trochę lepiej niż inne „strategie”.

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

Granie w każdym losowaniu jednym losem z losowo wygenerowanymi liczbami (o tej samej wadze):

Rozegranie 10 losów w każdym losowaniu z losowo wygenerowanymi liczbami (o tej samej wadze):

Rozegranie 100 losów w każdym losowaniu z losowymi liczbami (o tej samej wadze):

Komentarze, jak mówią, są zbyteczne – odpływ osadów jest w każdym przypadku nieunikniony 🙂