Excel дээрх сугалааны симуляци

Сугалаа бол аз хайх биш,

энэ бол ялагдагчдыг хайх явдал юм.

Атаархмаар тогтмол байдлаар (мөн сүүлийн үед илүү олон удаа) хүмүүс сугалаатай холбоотой янз бүрийн тооцоололд тусламж хүсч над руу бичдэг. Хэн нэгэн Excel-д хожсон тоонуудыг сонгох нууц алгоритмаа хэрэгжүүлэхийг хүсч, хэн нэгэн өнгөрсөн сугалааны тохиролоос унасан тоонуудын хэв маягийг олохыг хүсч, хэн нэгэн сугалааны зохион байгуулагчдыг шударга бус тоглоомоор барьж авахыг хүсч байна.

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

Даалгавар 1. Ялах магадлал

6 сугалааны сонгодог Stoloto 45-г жишээ болгон авч үзье. Дүрэм журмын дагуу 10-аас 6-ыг нь таасан хүмүүс л супер шагнал авна (хэрэв өмнөх сугалаанаас шагналын сангийн үлдэгдэл хуримтлагдсан бол 45 сая рубль ба түүнээс дээш). Хэрэв та 5-ыг таасан бол 150 мянган рубль, 4 бол 1500 рубль авах болно. , хэрэв 3 дугаараас 6 дугаар байвал 150 рубль, 2 дугаар байвал тасалбарт зарцуулсан 50 рубль буцаана. Зөвхөн нэгийг нь тааг, эсвэл аль нь ч биш - тоглоомын үйл явцаас зөвхөн эндорфин авах.

Стандарт функцийг ашиглан хожих математик магадлалыг хялбархан тооцоолж болно NUMBER COMB (НОСЛОХ), ийм тохиолдолд Microsoft Excel дээр байдаг. Энэ функц нь M тооноос N тооны хослолын тоог тооцоолдог. Тиймээс манай "6-аас 45" сугалааны хувьд дараах байдалтай байна.

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

… энэ нь 8-тай тэнцүү бөгөөд энэ сугалааны бүх боломжит хослолын нийт тоо.

Хэрэв та хэсэгчлэн ялах магадлалыг (2-аас 5-6 тоо) тооцоолохыг хүсч байвал эхлээд ийм сонголтуудын тоог тооцоолох хэрэгтэй бөгөөд энэ нь таасан тоонуудын хослолын тооны үржвэртэй тэнцүү байна. Үлдсэн тооноос (6-45) таамаглаагүй тооны тоогоор 6 = 39 тоо. Дараа нь бид боломжит бүх хослолын нийт тоог (8) сонголт бүрийн ялалтын тоонд хувааж, тохиолдол бүрийн ялалтын магадлалыг авна.

Дашрамд дурдахад, жишээлбэл, манай улсад онгоцны ослоор нас барах магадлал саяд 1 орчим байдаг. Бүх зүйлийг нэг тоогоор бооцоо тавиад казинод рулет дээр хожих магадлал 1-ээс 37 байна.

Дээрх бүх зүйл таныг зогсоож чадаагүй бөгөөд та цааш тоглоход бэлэн хэвээр байгаа бол үргэлжлүүлээрэй.

Даалгавар 2. Тоо бүрийн тохиолдох давтамж

Эхлэхийн тулд тодорхой тоонууд ямар давтамжтайгаар унадаг болохыг тодорхойлъё. Тохиромжтой хонжворт сугалааны хувьд дүн шинжилгээ хийхэд хангалттай том хугацааны интервалтай бол бүх бөмбөг хожих түүвэрт байх магадлал ижил байх ёстой. Бодит байдал дээр сугалааны бөмбөрийн дизайны онцлог, бөмбөлгүүдийн жингийн хэлбэр нь энэ зургийг гажуудуулж болох бөгөөд зарим бөмбөгний хувьд унах магадлал бусадтай харьцуулахад өндөр/бага байж болно. Энэ таамаглалыг практик дээр туршиж үзье.

Жишээлбэл, 2020-21-нд болсон 6 сугалааны тохирлоос 45 оны бүх мэдээллийг зохион байгуулагч Столотогийн вэбсайтаас авсан, дүн шинжилгээ хийхэд тохиромжтой, ийм "ухаалаг" хүснэгт хэлбэрээр бүтээгдсэнийг авч үзье. tabArchive Circulation. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Тоо бүрийн давтамжийг тооцоолохын тулд функцийг ашиглана уу COUNTIF (COUNTIF) мөн түүнд функц нэмнэ Текст (Текст)Нэг оронтой тоонуудын өмнө болон дараа нь тэргүүлэгч тэг болон одыг нэмэхийн тулд COUNTIF нь В баганын хослолын аль ч хэсэгт байгаа тоог хайж олох болно. Мөн илүү тодорхой болгохын тулд бид үр дүнгээр график байгуулж, давтамжийг эрэмбэлэх болно. буурах дарааллаар:

Дунджаар ямар ч бөмбөг унах ёстой 1459 сугалаа * 6 бөмбөг / 45 тоо = 194,53 удаа (үүнийг статистикт яг ингэж нэрлэдэг) математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная масса. Соответственно, боломжтой стратеги выигрыша, т.е. либо ставить на те шары, что выпадют чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.

Даалгавар 3. Удаан хугацаанд ямар тоо зураагүй вэ?

Өөр нэг стратеги нь хангалттай олон тооны сугалаагаар эрт орой хэзээ нэгэн цагт 1-ээс 45 хүртэлх тоо бүр унах ёстой гэсэн санаан дээр суурилдаг. Тиймээс хэрэв зарим тоо ялагчдын дунд удаан хугацаанд гарч ирээгүй бол ("хүйтэн бөмбөг") ирээдүйд тэдэнтэй бооцоо тавихыг оролдох нь логик юм. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21 год по убыванию даты и использовать функцию ИЛҮҮ ИЛҮҮ (ТОГЛОЛТ). Энэ нь тоо бүрийг хайж олохын тулд дээрээс доош (жишээ нь шинэээс хуучин гүйлт хүртэл) хайлт хийх бөгөөд энэ дугаарыг хамгийн сүүлд орхисон гүйлтийн серийн дугаарыг (жилийн эцсээс эхэн хүртэл тоолох) өгнө.

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

Тоглоомын өөр нэг стратеги нь тоог таах үед сэтгэл зүйн хүчин зүйлийг арилгахад суурилдаг. Тоглогч бооцоо тавих замаар тоо сонгохдоо ухамсартайгаар үүнийг огт оновчтой биш хийдэг. Статистикийн мэдээгээр, жишээлбэл, 1-ээс 31 хүртэлх тоог бусад хүмүүсээс 70% илүү (дуртай огноо), 13-ыг цөөн (хараал идсэн хэдэн арван), "азтай" долоог агуулсан тоог илүү олон удаа сонгодог гэх мэт. Гэхдээ бид бүх тоо нь адилхан машинтай (сугалааны бөмбөр) тоглож байгаа тул боломжоо тэнцүүлэхийн тулд тэдгээрийг ижил математикийн шударга байдлаар сонгох нь утга учиртай юм. Үүнийг хийхийн тулд бид Excel дээр санамсаргүй, хамгийн чухал нь давтагдахгүй тоонуудын генератор үүсгэх хэрэгтэй.

Үүнийг хийхийн тулд:

1. нэртэй "ухаалаг" хүснэгт үүсгэцгээе tableGenerator, эхний баганад 1-ээс 45 хүртэлх тоонууд байх болно.
2. Хоёр дахь баганад тоо бүрийн жинг оруулна уу (бидэнд энэ нь бага зэрэг хэрэгтэй болно). Хэрэв бүх тоо бидний хувьд адил үнэ цэнэтэй бөгөөд бид тэдгээрийг ижил магадлалтайгаар сонгохыг хүсч байвал жинг хаа сайгүй 1-тэй тэнцүү болгож болно.
3. Гурав дахь баганад бид функцийг ашигладаг SLCHIS (RAND), которая в Excel genereriruet случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) тус бүрийн жинг харгалзан 45 санамсаргүй тооны шинэ багц үүсгэнэ.
4. Функцийг ашиглаж байгаа дөрөв дэх баганыг нэмье Ранк (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Одоо функцийг ашиглан эхний зургаан тоог 6-р зэрэглэлээр сонгох хэрэгтэй ИЛҮҮ ИЛҮҮ (ТОГЛОЛТ):

При нажатии на клавишу F9 Excel хуудсан дээрх томьёог дахин тооцоолох бөгөөд бид ногоон нүдэнд 6 тооны шинэ багц авах болно. Түүнээс гадна В баганад илүү том жин тавьсан тоонууд пропорциональ өндөр зэрэглэлийг хүлээн авах бөгөөд ингэснээр бидний санамсаргүй түүврийн үр дүнд илүү олон удаа харагдах болно. Хэрэв бүх тооны жинг ижил гэж тохируулсан бол бүгдийг нь ижил магадлалтайгаар сонгоно. Ингэснээр бид 6-аас 45-н шударга, шударга санамсаргүй тоо үүсгэгчийг авах боловч шаардлагатай бол тархалтын санамсаргүй байдалд тохируулга хийх чадвартай.

Хэрэв бид сугалаа бүрт нэг биш, жишээлбэл, нэг дор хоёр тасалбараар тоглохоор шийдсэн бол тус бүрт нь давтагддаггүй тоонуудыг сонгох юм бол доороос ногоон хүрээ рүү нэмэлт мөрүүдийг нэмж болно. зэрэглэлд 6, 12, 18 гэх мэтийг нэмэх. г. тус тус:

Даалгавар 5. Excel-ийн сугалааны симулятор

Энэ бүхэл бүтэн сэдвийг илтгэх үүднээс Excel-д бүрэн хэмжээний сугалааны симулятор бүтээцгээе, тэнд та ямар ч стратеги туршиж, үр дүнг харьцуулах боломжтой (оновчлолын онолын хувьд үүнтэй төстэй зүйлийг Монте Карло арга гэж нэрлэдэг, гэхдээ энэ нь илүү хялбар байх болно. Бидний хувьд).

Бүх зүйлийг аль болох бодит байдалд ойртуулахын тулд 1 оны 2022-р сарын XNUMX-ний өдөр гэж төсөөлөөд үз дээ, энэ жилийн сугалаа бидний өмнө байгаа бөгөөд бид тоглохоор төлөвлөж байна. Би хүснэгтэд жинхэнэ буурсан тоонуудыг оруулав tablTiraži2022, дараагийн тооцоо хийхэд хялбар байх үүднээс нэмэлт зурсан тоонуудыг бие биенээсээ тусдаа багана болгон тусгаарлана.

Тусдаа хуудсан дээр Тоглоом нэртэй "ухаалаг" хүснэгт хэлбэрээр загварчлах хоосон зайг үүсгэ табИгра дараах хэлбэр:

Энд:

• Дээрх шар нүднүүдэд бид 2022 онд оролцохыг хүссэн сугалааны тоо (1-82) болон сугалаа бүрт тоглох тасалбарын тоог макронд тохируулна.
• Эхний 11 баганын (AJ) өгөгдлийг 2022 оны сугалааны хуудаснаас макрогоор хуулна.
• Макро нь хүснэгтээс авах дараагийн зургаан баганын өгөгдөл (KP). Генератор, бид санамсаргүй тоо үүсгэгчийг хэрэгжүүлсэн (дээрх 4-р асуудлыг үзнэ үү).
• Q баганад бид хасагдсан тоо болон функцийг ашиглан үүсгэсэн тоонуудын хоорондох таарч байгаа тоог тоолно SUMPRODUCTION (ХҮРЭЭНИЙ БҮТЭЭГДЭХҮҮН).
• В столбце R вычисляем финансовый результат (эсли биш, билетийн хувьд минус 50 рубль, харин үнэ нь 50 р. за билет)
• Сүүлийн баганад S, бид үйл явцын динамикийг харахын тулд бүх тоглоомын нийт үр дүнг хуримтлагдсан нийлбэр гэж үздэг.

Мөн энэ бүтцийг бүхэлд нь сэргээхийн тулд бидэнд жижиг макро хэрэгтэй. Таб дээр Developer (Хөгжүүлэгч) баг сонгох Visual Basic эсвэл гарын товчлолыг ашиглана уу Alt+F11. Дараа нь цэсээр дамжуулан шинэ хоосон модулийг нэмнэ үү Оруулах - Модуль тэнд дараах кодыг оруулна уу:

Sub Lottery() Dim iGames-г бүхэл тоогоор, iTickets-ийг бүхэл тоогоор, i As Long-оор, t As Integer-ээр, b As Integer-ээр 'obъявляем переменные для ссылки на листы Set wsGame = Worksheets("Игра") Set wsNumbers = Worksheets"(") wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество тиражей i Root таблиудад i = 5 табл. ("6:1048576"). Устгах 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тиражи 2022 и вставляем на лист Игра wsArchive1. (1, 1).Хуулбарлах Destination:=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор wsNumbers.Range("G1:L4").Copy wsGame,i,C4s. .PasteSpecial Paste:=xlPasteValues ​​i = i + 11 Next b Next t End Sub  

Шар нүднүүдэд хүссэн анхны параметрүүдийг оруулаад макрог ажиллуулахад л үлддэг Хөгжүүлэгч - Макро (Хөгжүүлэгч — Макро) эсвэл гарын товчлол Alt+F8.

Ойлгомжтой болгохын тулд та тоглолтын үеэр мөнгөний үлдэгдлийн өөрчлөлтийг тусгасан хуримтлагдсан нийлбэр бүхий сүүлийн баганад диаграммыг үүсгэж болно.

Янз бүрийн стратегиудын харьцуулалт

Одоо та бүтээсэн симуляторыг ашигласнаар 2022 онд бодит сугалаагаар ямар ч тоглоомын стратегийг туршиж үзээд ямар үр дүнд хүрэхийг харах боломжтой. Хэрэв та сугалаа бүрт 1 тасалбар тогловол "чавга" -ын ерөнхий зураг дараах байдалтай байна.

Энд:

• Генератор Энэ нь сугалаа бүрт генераторын үүсгэсэн (ижил жинтэй) санамсаргүй тоог сонгодог тоглоом юм.
• дуртай Энэ нь сугалаа бүрт ижил тоонуудыг ашигладаг тоглоом бөгөөд сүүлийн хоёр жилийн хугацаанд сугалаанд хамгийн олон удаа унасан (27, 32, 11, 14, 34, 40).
• Гадны хүмүүс - адилхан, гэхдээ бид хамгийн ховор унадаг тоонуудыг ашигладаг (12, 18, 26, 10, 21, 6).
• Хүйтэн - бүх сугалаанд бид удаан хугацаанд унасан тоонуудыг ашигладаг (35, 5, 39, 11, 6, 29).

Таны харж байгаагаар тийм ч их ялгаа байхгүй, гэхдээ санамсаргүй тоо үүсгэгч нь бусад "стратеги"-ээс арай илүү ажилладаг.

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

Сугалаа бүрт санамсаргүй байдлаар үүсгэсэн тоо бүхий нэг тасалбараар тоглох (ижил жинтэй):

Сугалаа бүрт санамсаргүй байдлаар үүсгэсэн тоогоор 10 тасалбар тоглох (ижил жинтэй):

Сугалаа бүрт санамсаргүй тоогоор 100 тасалбар тоглох (ижил жинтэй):

Тэдний хэлснээр сэтгэгдэл нь илүүц юм - бүх тохиолдолд ордыг зайлуулах нь зайлшгүй юм 🙂