شبیه سازی قرعه کشی در اکسل

لاتاری شکار شانس نیست،

این یک شکار بازنده است.

با منظمی غبطه‌انگیز (و اغلب اخیراً)، مردم برای من نامه می‌نویسند و در محاسبات مختلف مربوط به قرعه‌کشی کمک می‌خواهند. کسی می‌خواهد الگوریتم مخفی خود را برای انتخاب اعداد برنده در اکسل پیاده‌سازی کند، کسی می‌خواهد الگوهایی را در اعدادی پیدا کند که از قرعه‌کشی‌های گذشته خارج شده‌اند، کسی می‌خواهد برگزارکنندگان قرعه‌کشی را در یک بازی نادرست دستگیر کند.

در этой статье мне хотелось бы ответить на часть этих вопросов. در اکسل برای ابزارهای مختلف، مقدار زیادی از ابزارها، اکسل، می‌توان از ابزارهای مختلف استفاده کرد.

وظیفه 1. احتمال برنده شدن

بیایید قرعه کشی کلاسیک Stoloto 6 از 45 را به عنوان مثال در نظر بگیریم. طبق قوانین، فقط کسانی که هر 10 عدد از 6 عدد را حدس زده اند، یک جایزه فوق العاده دریافت می کنند (اگر موجودی صندوق جایزه از قرعه کشی های قبلی جمع شده باشد، 45 میلیون روبل یا بیشتر). اگر 5 را حدس بزنید، 150 هزار روبل دریافت خواهید کرد، اگر 4 تا 1500 روبل باشد. ، اگر 3 عدد از 6 باشد، 150 روبل، اگر 2 عدد باشد - 50 روبل هزینه شده برای بلیط را برمی گردانید. فقط یکی را حدس بزنید یا هیچ کدام - فقط اندورفین را از روند بازی دریافت کنید.

با استفاده از تابع استاندارد می توان احتمال ریاضی برنده شدن را به راحتی محاسبه کرد NUMBERCOMB (ترکیب)، که در مایکروسافت اکسل برای چنین موردی موجود است. این تابع تعداد ترکیبات N عدد از M را محاسبه می کند. بنابراین برای قرعه کشی "6 از 45" ما به صورت زیر خواهد بود:

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

… که برابر با 8 است، تعداد کل تمامی ترکیب های ممکن در این قرعه کشی.

اگر می خواهید احتمال برد جزئی (2-5 عدد از 6) را محاسبه کنید، ابتدا باید تعداد چنین گزینه هایی را محاسبه کنید که برابر است با حاصل ضرب تعداد ترکیب اعداد حدس زده شده از 6 با تعداد اعداد نامشخص از بقیه (45-6) = 39 عدد. سپس تعداد کل ترکیب‌های ممکن (8) را بر تعداد بردهای دریافتی برای هر گزینه تقسیم می‌کنیم - و احتمال برنده شدن را برای هر مورد بدست می‌آوریم:

به هر حال، به عنوان مثال، احتمال مرگ در یک سانحه هوایی در کشور ما حدود 1 در یک میلیون تخمین زده می شود. و احتمال برنده شدن در کازینو در رولت، شرط بندی همه چیز روی یک عدد 1 تا 37 است.

اگر همه موارد بالا مانع شما نشد و همچنان آماده بازی بیشتر هستید، ادامه دهید.

وظیفه 2. فراوانی وقوع هر عدد

برای شروع، اجازه دهید تعیین کنیم که اعداد خاص با چه فرکانسی سقوط می کنند. در یک قرعه کشی ایده آل، با توجه به فاصله زمانی کافی برای تجزیه و تحلیل، همه توپ ها باید احتمال یکسانی برای قرار گرفتن در نمونه برنده را داشته باشند. در واقع، ویژگی‌های طراحی درام قرعه‌کشی و وزن شکل توپ‌ها می‌تواند این تصویر را مخدوش کند، و برای برخی از توپ‌ها احتمال افتادن آن‌ها ممکن است بیشتر یا کمتر از بقیه باشد. بیایید این فرضیه را در عمل آزمایش کنیم.

به عنوان مثال، داده‌های مربوط به تمام قرعه‌کشی‌های 2020 از 21 قرعه‌کشی را که در 6-45 از وب‌سایت سازمان‌دهنده آنها Stoloto انجام شده است، در نظر بگیریم، که به شکل یک جدول "هوشمند"، مناسب برای تجزیه و تحلیل، با نام طراحی شده است. tabArchive Circulation. Розыгрыши проходят два раза в день (در 11 اوترا و در 11 شب)، т.е. در этой таблице у нас полторы тысячи тиражей-строк — تماماً ارائه دهنده برای تجزیه و تحلیل:

برای محاسبه فراوانی وقوع هر عدد از تابع استفاده کنید COUNTIF (COUNTIF) و یک تابع به آن اضافه کنید TEXT (متن)برای اضافه کردن صفرها و ستاره های قبل و بعد به اعداد تک رقمی، به طوری که COUNTIF به دنبال وقوع یک عدد در هر نقطه از ترکیب در ستون B باشد. همچنین، برای وضوح بیشتر، نموداری را بر اساس نتایج می سازیم و فرکانس ها را مرتب می کنیم. به ترتیب نزولی:

به طور متوسط، هر توپی باید بیفتد 1459 تساوی * 6 توپ / 45 عدد = 194,53،XNUMX بار (این دقیقاً همان چیزی است که در آمار نامیده می شود математическим ожиданем), اما choroşo viewno, chto nekotorыe chisla (27, 32, 11…) دارای وزن کم (+ 18%), a nekotorыe (10, 21, 6…) از مواد غذایی مناسب (-15%), чем اصلیя масса. Соответственно, ممکن است برای استفاده از این اطلاعات برای استراتژی های выигрыша, т.е. لیبو گذاشتن روی ته шары، chto vыпадают чаще، либо наоборот — رديف بندي در رده بندي چارы در اميد، chto они должны нагнать отставание.

وظیفه 3. چه اعدادی برای مدت طولانی ترسیم نشده اند؟

استراتژی دیگر مبتنی بر این ایده است که با تعداد قرعه کشی های به اندازه کافی، دیر یا زود هر عدد از 1 تا 45 در دسترس باید خارج شود. بنابراین اگر برخی از اعداد برای مدت طولانی در بین برندگان ظاهر نشده اند ("توپ های سرد")، منطقی است که در آینده روی آنها شرط بندی کنید. 

می توانید همه چیز را نادیده بگیریم، در صورتی که برای 2020-21 سال XNUMX-XNUMX استفاده کنید. بیشتر در معرض (همخوانی داشتن). برای جستجوی هر شماره از بالا به پایین (یعنی از اجراهای جدید به قدیمی) جستجو می‌کند و شماره سریال اجرا (با شمارش از پایان سال تا ابتدا) را که این شماره آخرین بار در آن جا انداخته شده است را ارائه می‌کند:

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

استراتژی دیگر بازی مبتنی بر حذف عامل روانی هنگام حدس زدن اعداد است. وقتی بازیکنی با شرط بندی اعداد را انتخاب می کند، ناخودآگاه این کار را نه کاملاً منطقی انجام می دهد. طبق آمار، به عنوان مثال، اعداد از 1 تا 31 70٪ بیشتر از بقیه انتخاب می شوند (تاریخ های مورد علاقه)، 13 کمتر انتخاب می شود (دوجین لعنتی)، اعداد حاوی هفت "خوش شانس" بیشتر انتخاب می شوند و غیره. اما ما در برابر یک ماشین (درام قرعه کشی) بازی می کنیم که همه اعداد برای آن یکسان هستند، بنابراین منطقی است که آنها را با همان بی طرفی ریاضی انتخاب کنیم تا شانس خود را برابر کنیم. برای انجام این کار، باید یک مولد اعداد تصادفی و مهمتر از همه غیر تکراری در اکسل ایجاد کنیم:

برای انجام این:

1. بیایید یک جدول "هوشمند" به نام ایجاد کنیم جدول مولد، که در آن ستون اول اعداد ما از 1 تا 45 خواهد بود.
2. در ستون دوم، وزن هر عدد را وارد کنید (کمی بعد به آن نیاز خواهیم داشت). اگر همه اعداد برای ما به یک اندازه ارزش داشته باشند و بخواهیم آنها را با احتمال مساوی انتخاب کنیم، آنگاه وزن را می توان در همه جا برابر با 1 قرار داد.
3. در ستون سوم از تابع استفاده می کنیم SLCHIS (رند), کدورا در اکسل تولید می‌کند از 0 تا 1 خرده‌ریزی را تولید می‌کند. Таким образом каждый раз при пересчёте فهرست F9) مجموعه جدیدی از 45 عدد تصادفی با در نظر گرفتن وزن هر یک از آنها تولید می شود.
4. بیایید یک ستون چهارم اضافه کنیم، جایی که از تابع استفاده می کنیم رتبه (رتبه) вычислим ранг (پوزيشن در топе) для каждого из чисел.

اکنون باقی مانده است که با استفاده از تابع، شش عدد اول را با رتبه 6 انتخاب کنید بیشتر در معرض (همخوانی داشتن):

При наготии на клавишу F9 فرمول های موجود در برگه اکسل مجدداً محاسبه می شوند و هر بار یک مجموعه جدید از 6 عدد در سلول های سبز دریافت می کنیم. علاوه بر این، اعدادی که وزن بیشتری برای آنها در ستون B تعیین شده است، به نسبت بالاتری رتبه دریافت می کنند و بنابراین، بیشتر در نتایج نمونه تصادفی ما ظاهر می شوند. اگر وزن همه اعداد یکسان باشد، همه آنها با احتمال یکسان انتخاب می شوند. به این ترتیب ما یک مولد اعداد تصادفی منصفانه و بی‌طرفانه 6 از 45 را دریافت می‌کنیم، اما در صورت لزوم می‌توانیم تنظیمات تصادفی توزیع را انجام دهیم.

اگر تصمیم بگیریم در هر قرعه کشی نه با یک، بلکه مثلاً با دو بلیط به طور همزمان بازی کنیم که در هر کدام از آنها اعداد غیر تکراری را انتخاب می کنیم، می توانیم به سادگی خطوط اضافی را از پایین به محدوده سبز اضافه کنیم. اضافه کردن 6، 12، 18 و غیره به رتبه. د به ترتیب:

وظیفه 5. شبیه ساز قرعه کشی در اکسل

به عنوان نقطه پایانی از کل این موضوع، بیایید یک شبیه ساز قرعه کشی تمام عیار در اکسل ایجاد کنیم، که در آن می توانید هر استراتژی را امتحان کنید و نتایج را با هم مقایسه کنید (در نظریه بهینه سازی، چیزی مشابه روش مونت کارلو نیز نامیده می شود، اما ساده تر خواهد بود. برای ما).

برای اینکه همه چیز تا حد امکان به واقعیت نزدیک شود، برای لحظه ای تصور کنید که اول ژانویه 1 است و قرعه کشی های امسال را پیش رو داریم که قصد داریم در آن بازی کنیم. اعداد کاهش یافته واقعی را در جدول وارد کردم tablTiraži2022، جدا کردن اعداد کشیده شده اضافی از یکدیگر در ستون های جداگانه برای راحتی محاسبات بعدی:

در یک برگه جداگانه بازی یک جای خالی برای مدل سازی در قالب یک جدول "هوشمند" با نام ایجاد کنید tabIgra فرم زیر:

اینجا:

• در سلول‌های زرد بالا، تعداد قرعه‌کشی‌های سال 2022 که می‌خواهیم در آن شرکت کنیم (1-82) و تعداد بلیط‌هایی که در هر قرعه‌کشی بازی می‌کنیم را برای کلان تعیین می‌کنیم.
• داده های 11 ستون اول (AJ) توسط ماکرو از برگه قرعه کشی 2022 کپی می شود.
• داده های شش ستون بعدی (KP) که ماکرو از برگه می گیرد مولد، جایی که ما یک مولد اعداد تصادفی را پیاده سازی کرده ایم (مشکل 4 را در بالا ببینید).
• در ستون Q، تعداد تطابقات بین اعداد حذف شده و اعداد ایجاد شده با استفاده از تابع را می شماریم SUMPRODUCT (مجموع محصول).
• در ستونце R вычисляем финансовый результат (если не выиграли, تا مینوس 50 روبل برای بلیط, если выиграли, то приз — 50 р. برای بلیط)
• در آخرین ستون S، نتیجه کلی کل بازی را به عنوان یک مجموع تجمعی در نظر می گیریم تا پویایی در روند را مشاهده کنیم.

و برای احیای کل این ساختار، به یک کلان کوچک نیاز داریم. روی زبانه توسعه دهنده (توسعه دهنده) یک تیم انتخاب کنید ویژوال بیسیک یا از میانبر صفحه کلید استفاده کنید دگرساز+F11. سپس یک ماژول خالی جدید از طریق منو اضافه کنید درج - ماژول و کد زیر را در آنجا وارد کنید:

Sub Lottery() کم iGames به عنوان عدد صحیح، iTickets به عنوان عدد صحیح، i تا زمانی که، t به عنوان عدد صحیح، b به عنوان عدد صحیح wsArchive = Worksheets ("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов во каждом тираже i = 5 'перваблиа тиражей. ("6:1048576").حذف 'очищаем старые данные برای t = 1 به iGames برای b = 1 به iTickets 'копируем выигравшие номера со فهرست Тиражи 2022 и вставляем на لیست بازی wsArchive.Resells,1(t). (1, 1).Copy Destination:=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированные номера со فهرست Генератор wsNumbers.Range("G1:L4"). wsGame4,C را کپی کنید. PasteSpecial Paste:=xlPasteValues ​​i = i + 11 بعدی b بعدی t پایان فرعی  

باقی مانده است که پارامترهای اولیه مورد نظر را در سلول های زرد وارد کنید و ماکرو را اجرا کنید توسعه دهنده - ماکروها (توسعه دهنده - ماکروها) یا میانبر صفحه کلید دگرساز+F8.

برای وضوح، همچنین می توانید نموداری برای آخرین ستون با مجموع تجمعی ایجاد کنید که منعکس کننده تغییر موجودی پول در طول بازی است:

مقایسه استراتژی های مختلف

اکنون، با استفاده از شبیه ساز ایجاد شده، می توانید هر استراتژی بازی را در قرعه کشی های واقعی در سال 2022 آزمایش کنید و نتایجی را که به همراه خواهد داشت را مشاهده کنید. اگر در هر قرعه کشی 1 بلیط بازی کنید، تصویر کلی "آلو" چیزی شبیه به این است:

اینجا:

• مولد یک بازی است که در هر قرعه کشی اعداد تصادفی ایجاد شده توسط مولد ما (با همان وزن) را انتخاب می کنیم.
• مورد علاقه های شما بازی‌ای است که در هر قرعه‌کشی از اعداد یکسانی استفاده می‌کنیم - اعدادی که در دو سال گذشته اغلب در تساوی‌ها شکست خورده‌اند (27، 32، 11، 14، 34، 40).
• خارجی ها - یکسان است، اما ما از کمیاب ترین اعداد کشویی (12، 18، 26، 10، 21، 6) استفاده می کنیم.
• سرد - در تمام قرعه‌ها از اعدادی استفاده می‌کنیم که برای مدت طولانی افت نکرده‌اند (35، 5، 39، 11، 6، 29).

همانطور که می بینید، تفاوت زیادی وجود ندارد، اما مولد اعداد تصادفی کمی بهتر از سایر "استراتژی ها" رفتار می کند.

Можно также попробовать играть большим количеством بلیت های در هر گروهی، chtobы perekrыtь bolьшее количество вариантов (иногда для этого несколько игроков объединяются در گروه).

بازی در هر قرعه کشی با یک بلیط با اعداد تصادفی تولید شده (با همان وزن):

پخش 10 بلیط در هر قرعه کشی با اعداد تصادفی تولید شده (با همان وزن):

پخش 100 بلیط در هر قرعه کشی با اعداد تصادفی (با همان وزن):

نظرات، همانطور که می گویند، اضافی هستند - تخلیه سپرده در همه موارد اجتناب ناپذیر است 🙂