Simulimi i lotarisë në Excel

Lotaria nuk është një gjueti për fat,

është një gjueti për humbësit.

Me një rregullsi të lakmueshme (dhe më shpesh kohët e fundit), njerëzit më shkruajnë duke kërkuar ndihmë në llogaritjet e ndryshme që lidhen me llotaritë. Dikush dëshiron të zbatojë algoritmin e tij sekret për zgjedhjen e numrave fitues në Excel, dikush dëshiron të gjejë modele në numrat që kanë rënë nga shortet e kaluara, dikush dëshiron të kapë organizatorët e lotarisë në një lojë të pandershme.

В этой статье мне хотелось бы ответить на часть этих вопросов. Përshtatshmëria, në Excel për këto instrumente, shumë nga kotorыh, kstati, mund të favorizojë dhe në të kundërtën e punës.

Detyra 1. Probabiliteti për të fituar

Le të marrim si shembull llotarinë klasike Stoloto 6 nga 45. Sipas rregullave, vetëm ata që kanë marrë me mend të 10 numrat nga 6 marrin një super çmim (45 milion rubla ose më shumë nëse bilanci i fondit të çmimeve është grumbulluar nga tërheqjet e mëparshme). Nëse keni marrë me mend 5, do të merrni 150 mijë rubla, nëse 4 - 1500 rubla. , nëse 3 numra nga 6, atëherë 150 rubla, nëse 2 numra - do të ktheni 50 rubla të shpenzuara në biletë. Mendoni vetëm një ose asnjë - merrni vetëm endorfina nga procesi i lojës.

Probabiliteti matematikor për të fituar mund të llogaritet lehtësisht duke përdorur funksionin standard NUMRI COMB (KOMBINIM), i cili është i disponueshëm në Microsoft Excel për një rast të tillë. Ky funksion llogarit numrin e kombinimeve të N numrave nga M. Pra, për lotarinë tonë "6 nga 45" do të ishte:

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

… që është e barabartë me 8, numri i përgjithshëm i të gjitha kombinimeve të mundshme në këtë llotari.

Nëse dëshironi të llogaritni probabilitetin për një fitore të pjesshme (2-5 numra nga 6), atëherë së pari do t'ju duhet të llogaritni numrin e opsioneve të tilla, i cili është i barabartë me produktin e numrit të kombinimeve të numrave të hamendësuar nga 6 nga numri i numrave të pamenduar nga pjesa e mbetur (45-6) = 39 numra. Pastaj e ndajmë numrin total të të gjitha kombinimeve të mundshme (8) me numrin e fituar të fituar për secilin opsion - dhe marrim probabilitetet e fitimit për secilin rast:

Nga rruga, probabiliteti, për shembull, për të vdekur në një aksident avioni në vendin tonë vlerësohet në rreth 1 në një milion. Dhe probabiliteti për të fituar në një kazino në ruletë, duke vënë bast gjithçka në një numër është 1 deri në 37.

Nëse të gjitha sa më sipër nuk ju ndaluan dhe jeni ende gati për të luajtur më tej, vazhdoni.

Detyra 2. Frekuenca e shfaqjes së secilit numër

Për të filluar, le të përcaktojmë se me çfarë frekuence bien numra të caktuar. Në një llotari ideale, duke pasur parasysh një interval kohor mjaft të madh për analizë, të gjithë topat duhet të kenë të njëjtën probabilitet për të qenë në kampionin fitues. Në realitet, tiparet e dizajnit të daulles së lotarisë dhe forma e peshës së topave mund ta shtrembërojnë këtë pamje, dhe për disa topa probabiliteti i rënies mund të jetë më i lartë/më i ulët se për të tjerët. Le ta testojmë këtë hipotezë në praktikë.

Le të marrim, për shembull, të dhënat për të gjitha shortet e 2020 nga 21 shortet që u zhvilluan në 6-45 nga faqja e internetit e organizatorit të tyre Stoloto, e krijuar në formën e një tabele kaq "të zgjuar", të përshtatshme për analizë, me emrin tabArkivi Qarkullimi. Розыгрыши проходят два раза во ditë (në 11 utra dhe në 11 të mbrëmjes), т.е. në этой таблице у нас полторы тысячи тиражей-строк — plotësisht të vlefshme për fillimin e zgjedhjes për analizën:

Për të llogaritur shpeshtësinë e shfaqjes së secilit numër, përdorni funksionin countif (COUNTIF) dhe shtoni një funksion në të TEXT (TEKSTI)për të shtuar zerat kryesore dhe yjet para dhe pas numrave njëshifrorë, në mënyrë që COUNTIF të kërkojë shfaqjen e një numri kudo në kombinim në kolonën B. Gjithashtu, për qartësi më të madhe, ne do të ndërtojmë një grafik sipas rezultateve dhe do të renditim frekuencat në rend zbritës:

Mesatarisht, çdo top duhet të bjerë 1459 barazime * 6 topa / 45 numra = 194,53 herë (kjo është pikërisht ajo që quhet në statistika математическим ожиданием), por хорошо видно, что некоторые числа (27, 32, 11…) nuk ishte shumë i dobët (+18%), një i pakët (10, 21, 6…) i cili nuk ishte më i rëndësishëm (-15%), për shkak të masës bazë. Соответственно, mund të provoni të përdorni informacione për strategjitë выигрыша, т.е. либо ставил на те шары, что падают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, што они должны нагнать отставание.

Detyra 3. Cilët numra nuk janë tërhequr për një kohë të gjatë?

Një strategji tjetër bazohet në idenë se me një numër mjaft të madh të barazimeve, herët a vonë çdo numër nga të gjithë të disponueshëm nga 1 në 45 duhet të bjerë jashtë. Pra, nëse disa numra nuk janë shfaqur në mesin e fituesve për një kohë të gjatë ("topa të ftohtë"), atëherë është logjike të provoni bast mbi to në të ardhmen. 

Mund të lexoni të gjitha vlerat që nuk janë të pafavorshme, përndryshe nuk mund të lexoni për vitin 2020-21 për vitin XNUMX-XNUMX në ditët e fundit dhe për të shfrytëzuar funksionin ME TE EKSPOZUAR (ndeshje). Do të kërkojë nga lart poshtë (d.m.th. nga versionet e reja tek të vjetrat) për të kërkuar çdo numër dhe do të japë numrin serial të programit (duke llogaritur nga fundi i vitit deri në fillim) ku ky numër është hedhur për herë të fundit:

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

Një strategji tjetër e lojës bazohet në eliminimin e faktorit psikologjik kur hamendësoni numrat. Kur një lojtar zgjedh numrat duke vënë bastin e tij, ai në mënyrë të pandërgjegjshme e bën këtë jo plotësisht racionalisht. Sipas statistikave, për shembull, numrat nga 1 deri në 31 zgjidhen 70% më shpesh se pjesa tjetër (datat e preferuara), 13 zgjidhet më rrallë (dreq duzina), numrat që përmbajnë shtatë "fat" zgjidhen më shpesh, etj. Por ne po luajmë kundër një makinerie (daulle llotarie) për të cilën të gjithë numrat janë të njëjtë, kështu që ka kuptim t'i zgjedhim me të njëjtën paanësi matematikore për të barazuar shanset tona. Për ta bërë këtë, ne duhet të krijojmë një gjenerator të numrave të rastësishëm dhe - më e rëndësishmja - jo-përsëritëse në Excel:

Për ta bërë këtë:

1. Le të krijojmë një tabelë "të zgjuar" me emrin Gjeneratori i tabelave, ku kolona e parë do të jenë numrat tanë nga 1 në 45.
2. Në kolonën e dytë, vendosni peshën për secilin numër (do të na duhet pak më vonë). Nëse të gjithë numrat janë njësoj të vlefshëm për ne dhe duam t'i zgjedhim me probabilitet të barabartë, atëherë pesha mund të vendoset e barabartë me 1 kudo.
3. Në kolonën e tretë përdorim funksionin SLCHIS (RAND), kotoraja në Excel gjeneron 0 deri në 1, do të thotë se nuk do të jetë e mundur nga XNUMX deri në XNUMX. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) do të gjenerohet një grup i ri prej 45 numrash të rastësishëm, duke marrë parasysh peshën për secilin prej tyre.
4. Le të shtojmë një kolonë të katërt, ku përdoret funksioni RADHA (RANK) вычислим ранг (pozicioni në top) для каждого из чисел.

Tani mbetet të bëjmë një përzgjedhje të gjashtë numrave të parë sipas renditjes 6 duke përdorur funksionin ME TE EKSPOZUAR (ndeshje):

При наготии на клавишу F9 formulat në fletën e Excel do të rillogariten dhe çdo herë do të marrim një grup të ri prej 6 numrash në qelizat jeshile. Për më tepër, numrat për të cilët është vendosur një peshë më e madhe në kolonën B do të marrin një renditje proporcionalisht më të lartë dhe, kështu, do të shfaqen më shpesh në rezultatet e kampionit tonë të rastësishëm. Nëse pesha për të gjithë numrat vendoset në të njëjtën, atëherë të gjithë ata do të zgjidhen me të njëjtën probabilitet. Në këtë mënyrë marrim një gjenerues të drejtë dhe të paanshëm numrash të rastësishëm prej 6 nga 45, por me aftësinë për të bërë rregullime në rastësinë e shpërndarjes nëse është e nevojshme.

Nëse vendosim të luajmë në çdo barazim jo me një, por, për shembull, me dy bileta njëherësh, në secilën prej të cilave do të zgjedhim numra që nuk përsëriten, atëherë thjesht mund të shtojmë vija shtesë nga fundi në diapazonin e gjelbër, duke shtuar 6, 12, 18, etj. d. përkatësisht:

Detyra 5. Simulatori i Lotarisë në Excel

Si një apoteozë e gjithë kësaj teme, le të krijojmë një imitues të plotë të lotarisë në Excel, ku mund të provoni çdo strategji dhe të krahasoni rezultatet (në teorinë e optimizimit, diçka e ngjashme quhet edhe metoda Monte Carlo, por do të jetë më e thjeshtë për NE).

Për ta bërë gjithçka sa më afër realitetit, imagjinoni për një moment se është 1 janari 2022 dhe kemi përpara shortet e këtij viti, në të cilat planifikojmë të luajmë. Kam futur numrat e vërtetë të rënë në tabelë tablTiraži2022, duke i ndarë numrat e tërhequr shtesë nga njëri-tjetri në kolona të veçanta për lehtësinë e llogaritjeve të mëvonshme:

Në një fletë të veçantë Lojë krijoni një bosh për modelim në formën e një tabele "të zgjuar" me emrin tabIgra formën e mëposhtme:

këtu:

• Në qelizat e verdha të mësipërme, ne do të vendosim për makro numrin e barazimeve në vitin 2022 në të cilat duam të marrim pjesë (1-82) dhe numrin e biletave që luajmë në çdo short.
• Të dhënat për 11 kolonat e para (AJ) do të kopjohen nga makro nga fleta e tërheqjes 2022.
• Të dhënat për gjashtë kolonat e ardhshme (KP) makro do të marrë nga fleta Gjenerator, ku kemi implementuar një gjenerues numrash të rastësishëm (shih problemin 4 më lart).
• Në kolonën Q, ne numërojmë numrin e ndeshjeve midis numrave të rënë dhe atyre të gjeneruar duke përdorur funksionin SUMPRODUCT (SUMPRODUCT).
• Në shtyllën R вычисляем финансовый результат (esli nuk vыigrani, deri në minus 50 rubla për biletën, если vыиграли, то приз — 50 р. për biletën)
• Në kolonën e fundit S, ne e konsiderojmë rezultatin e përgjithshëm të të gjithë lojës si një total kumulativ për të parë dinamikën në proces.

Dhe për të ringjallur gjithë këtë strukturë, ne kemi nevojë për një makro të vogël. Në skedën zhvillues (Zhvilluesi) zgjidhni një ekip Visual Basic ose përdorni shkurtoren e tastierës Ndalet+F11. Pastaj shtoni një modul të ri bosh përmes menysë Insert – Moduli dhe shkruani kodin e mëposhtëm atje:

Nën Lottery() Dim iGames si numër i plotë, iTickets si numër i plotë, i për aq kohë sa, t sa numër i plotë, b si numër i plotë 'объявляем переменные за ссылки на листы Set wsGame = Fletët e punës ("Игра") Set wsNumbers" (Генера) = wsArchive = Fletët e punës ("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов во каждом тираже i = 5 'перваблиaGawska ("6:1048576"). Fshi 'очищаем старые данные Për t = 1 Tek iGames Për b = 1 Tek iTickets 'kopiruem выигравшие номера со listën Тиражи 2022 и vendos në listën e lojës wsArchive.Resells,1,(). (1, 1). Kopjo Destinacionin:=wsGame.Cells(i, 10) 'копируем и вставляем специјалной вставкой значений сгенерированные номера со листа Генератор wsNumbers.Range("G1:L4"). Kopjo wssGame,Cell. .PasteSpecial Paste:=xlPasteValues ​​i = i + 4 Tjetra b Tjetra t Fundi Nën  

Mbetet për të futur parametrat fillestarë të dëshiruar në qelizat e verdha dhe për të drejtuar makro Zhvilluesi - Makro (Zhvilluesi - Makro) ose shkurtore të tastierës Ndalet+F8.

Për qartësi, mund të ndërtoni gjithashtu një diagram për kolonën e fundit me një total kumulativ, duke pasqyruar ndryshimin në bilancin e parave gjatë lojës:

Krahasimi i strategjive të ndryshme

Tani, duke përdorur simulatorin e krijuar, mund të testoni çdo strategji lojë në barazime reale në vitin 2022 dhe të shihni rezultatet që do të sillte. Nëse luani 1 biletë në çdo barazim, atëherë fotografia e përgjithshme e "kumbullës" duket diçka si kjo:

këtu:

• Gjenerator është një lojë ku në çdo short zgjedhim numra të rastësishëm të krijuar nga gjeneratori ynë (me të njëjtën peshë).
• Favoritet është një lojë ku në çdo barazim përdorim të njëjtët numra – ata që kanë rënë më shpesh në barazim gjatë dy viteve të fundit (27, 32, 11, 14, 34, 40).
• Të huajt – e njëjta gjë, por ne përdorim numrat më të rrallë me rënie (12, 18, 26, 10, 21, 6).
• Të ftohtë – në të gjitha barazimet përdorim numra që nuk kanë rënë jashtë për një kohë të gjatë (35, 5, 39, 11, 6, 29).

Siç mund ta shihni, nuk ka ndonjë ndryshim të madh, por gjeneruesi i numrave të rastësishëm sillet pak më mirë se "strategjitë" e tjera.

Можно также попробовать играть мнооогу количеством bileta në çdo kohë, për të bërë më shumë koliçevë variante (inogda dlя этого несколько игроков объединяются во grup).

Duke luajtur në çdo barazim me një biletë me numra të krijuar rastësisht (me të njëjtën peshë):

Luajtja e 10 biletave në çdo short me numra të krijuar rastësisht (me të njëjtën peshë):

Luajtja e 100 biletave në çdo short me numra të rastësishëm (me të njëjtën peshë):

Komentet, siç thonë ata, janë të tepërta - kullimi i depozitave është i pashmangshëm në të gjitha rastet 🙂