Jak zrobić symulację Lotto w Arkuszu Google?

Sprawa wydaje się banalna, ale tak całkiem banalna nie jest. Mamy do dyspozycji 3 funkcje losujące w Google Sheets, ale żadna samodzielnie nie przeprowadzi nam losowania. A co jeśli chcemy zrobić 10 000 losowań by sprawdzić, czy rzeczywiście tak ciężko trafić szóstkę?

Do zabawy z tym wpisem proponuję korzystać z arkusza roboczego.

Dostępne funkcje losujące

RAND() – generuje liczbę z zakresu 0 do 1 więc samodzielnie nie uciągnie. Nie musimy wpisywać żadnych parametrów. Dostajemy ułamek dziesiętny.

RANDBETWEEN(dół;góra) – podaje nam liczbę całkowitą z zakresu dół – góra, z liczbami granicznymi włącznie. RANDBETWEEN(0;9) da nam każdą liczbę jednocyfrową.

RANDARRAY(ile wierszy; ile kolumn) – tworzy siatkę liczb losowych o zadanych wymiarach. Liczby są z zakresu 0 do 1.

Losujemy pierwszą liczbę

Tu sytuacja jest prosta. Używamy RANDBETWEEN

Losujemy kolejne liczby uwzględniając te które już wyszły z puli

W przypadku drugiej liczby i kolejnych, nie możemy sobie pozwolić na to by pojawiły się duplikaty. Bo w “analogowym” totalizatorze liczby nie wracają do koszyka.

Pomysł jest taki: Stworzymy tabelę liczb od 1 do 49, wyciągniemy z niej liczbę wcześniej wylosowaną i przeprowadzimy losowanie z tej nowej, uszczuplonej tabeli.

sequence(49) buduje nam tabelę

filter(sequence(49);sequence(49)<>B16) – bierze tabelę i usuwa z niej wartosć z wcześniejszego losowania (5) (o funkcji FILTER poczytacie w osobnym wpisie. Pozostaje w niej 48 liczb.

Funkcja INDEX służy do wyławiania wartości z tabeli:

INDEX(tabela;nr pozycji)

Tabelę przed chwilą zdefiniowaliśmy, a teraz musimy tylko wylosować, który jej element weźmiemy. Do tego posłuży nam znów RANDBETWEEN, tym razem już tylko z 48 elementów – randbetween(1;48)

Analogicznie robimy z kolejnymi losowaniami. Za każdym razem pula jest coraz bardziej uszczuplana. W końcu wygląda tak:

Losujemy zestaw używając RANDARRAY

Drugi sposób przeprowadzenia losowania to stworzenie tabeli przy pomocy RANDARRAY, a następnie przekształcenie wartości, tak by były to liczby całkowite z zakresu 1 do 49 i żeby się nie powtarzały. Zaletą tego rozwiązania jest to, że łatwiej się będzie nam zmieścić z formułą w jednej komórce, przez co łatwiej też będzie od razu posortować wartości rosnąco (jak to zwykle podaje się w Lotto).

Zaczynam od tabeli liczb losowych:

W drugim etapie mnożę każdą komórkę tabeli *49 by uzyskać wartości z zakresu 0 – 49.

Następny etap to zaokrąglenie liczb tak by wyeliminować wartości zero. Dlatego do każdej wartości dodaję 0,5 (by uniknąć zaokrąglania w dół do zera) Do samego zaokrąglania używam funkcji ROUND.

Duplikatów pozbywam się funkcją UNIQUE, a tabelę przycinam funkcją ARRAY_CONSTRAIN o składni

ARRAY_CONSTRAIN(tabela;ile zostawić wierszy; ile zostawić kolumn)

Na koniec sortuję (funkcja SORT) go i układam w poziomie (funkcja TRANSPOSE).

Niewątpliwą zaletą drugiego rozwiązania jest krótszy zapis, natomiast istnieje niewielkie prawdopodobieństwo, że duplikatów będzie tak dużo, że po ich wyeliminowaniu pozostanie nam mniej niż 6 wartości.

Ile można zarobić w Lotto? Czyli przeprowadzamy losowanie tysiące razy.

Moim celem było od początku sprawdzenie ile razy będę musiał przeprowadzać losowanie, dopóki nie uda mi się trafić szóstki. W rozbudowanej wersji tego arkusza (więcej wierszy i powtarzanie losowania przy pomocy skryptu) zrobiłem około 175 000 prób, “wydałem” kilkaset tysięcy złotych na kupony, ale udało się złapać tylko trzy piątki.

Jak powstała symulacja 100 losowań lotto?

W żółtej tabeli tworzyłem wyniki losowania, a w różowej moje typowanie – też losowe. A następnie sprawdzałem ile elementów się zgadza.

Dla 100 losowań mój wynik jest dość mizerny. Jeśli każdy kupon kosztuję 3 zł i stosuje zasady z Lotto Plus to wygrywam zaledwie 10 zł.

Dla dociekliwych stworzyłem plik roboczy w którym jest 10 000 losowań. Uwaga! Każda modyfikacja sprawia, że arkusz przelicza się kilkanaście sekund (na wolniejszych komputerach może być dłużej).

Przykładowe podsumowanie wygląda tak:

Podsumowanie symulacji Lotto

Lotto jest fajną zabawą i emocjonującym spędzaniem czasu. Podobnie jak pisanie tego bloga, raczej nie przyniesie sensownych pieniędzy. Chyba, że trafi się legendarne szczęście.

Piękny, prawda?

Przy okazji warto spojrzeć na stronę Lotto, gdzie piszą wprost, że rozmawiamy o bardzo małych prawdopodobieństwach – mnie więcej takich jak to że rodzice dadzą Ci imię DHRUMIL:

Źródło: Lotto.pl