Excel의 복권 시뮬레이션

복권은 행운을 노리는 것이 아니라,

그것은 패배자를 위한 사냥이다.

부러운 규칙으로 (그리고 최근에는 더 자주) 사람들은 복권과 관련된 다양한 계산에 도움을 요청하는 편지를 씁니다. 누군가는 Excel에서 당첨 번호를 선택하기 위한 비밀 알고리즘을 구현하고 싶고, 누군가는 과거 추첨에서 빠진 숫자에서 패턴을 찾고, 누군가는 부정직한 게임에서 복권 주최자를 잡으려고 합니다.

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

과제 1. 당첨 확률

6개의 복권 중 고전적인 Stoloto 45을 예로 들어 보겠습니다. 규칙에 따르면 10개 중 6개 숫자를 모두 맞힌 사람에게만 슈퍼 상품(이전 추첨에서 상금 잔액이 누적된 경우 45만 루블 이상)이 주어집니다. 5를 추측하면 150-4 루블이면 1500 루블을 받게됩니다. , 3개 중 6개 숫자이면 150루블, 2개 숫자이면 티켓에 사용한 50루블을 반환합니다. 하나만 추측하거나 전혀 추측하지 마십시오. 게임 과정에서 엔돌핀만 얻으십시오.

수학적 승리 확률은 표준 함수를 사용하여 쉽게 계산할 수 있습니다. 숫자콤 (결합시키다), 이러한 경우 Microsoft Excel에서 사용할 수 있습니다. 이 함수는 M에서 N개의 숫자 조합의 수를 계산합니다. 따라서 "6/45" 복권의 경우 다음과 같습니다.

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

... 이것은 이 복권에서 가능한 모든 조합의 총 수인 8과 같습니다.

부분 승리 확률(2개 중 5-6개)을 계산하려면 먼저 이러한 옵션의 수를 계산해야 합니다. 나머지(6-45) 중 추측되지 않은 숫자의 수로 6 = 39개. 그런 다음 가능한 모든 조합의 총 수(8)를 각 옵션에 대해 받은 승수로 나누고 각 경우에 대해 승률을 얻습니다.

그건 그렇고, 예를 들어 우리나라에서 비행기 추락 사고로 사망할 확률은 약 1만 분의 1로 추산됩니다. 그리고 룰렛에서 카지노에서 이길 확률은 하나의 숫자에 모든 것을 걸 때 37에서 XNUMX 사이입니다.

위의 모든 사항이 중단되지 않고 계속 플레이할 준비가 되었으면 계속하십시오.

Task 2. 각 번호의 발생빈도

먼저 특정 숫자가 어떤 빈도로 떨어지는지 알아보겠습니다. 이상적인 복권에서는 분석을 위한 충분한 시간 간격이 주어지면 모든 공이 당첨된 샘플에 포함될 확률이 동일해야 합니다. 실제로 복권의 디자인 특징과 공의 무게 모양이 이 그림을 왜곡할 수 있으며 일부 공의 경우 떨어지는 확률이 다른 공에 비해 높거나 낮을 수 있습니다. 이 가설을 실제로 테스트해 봅시다.

예를 들어, 주최자 Stoloto의 웹 사이트에서 2020-21에 발생한 6개의 복권 추첨 중 45년 전체에 대한 데이터를 살펴보겠습니다. 탭아카이브 순환. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для ан:

각 숫자의 발생 빈도를 계산하려면 다음 함수를 사용하십시오. 카운티 (카운티프) 그리고 거기에 기능을 추가 TEXT (본문)한 자리 숫자 앞뒤에 선행 XNUMX과 별표를 추가하여 COUNTIF가 B열의 조합에서 숫자의 출현을 찾도록 합니다. 또한 명확성을 위해 결과별로 차트를 작성하고 빈도를 정렬합니다. 내림차순:

평균적으로 모든 공은 1459 무승부 * 6 공 / 45 숫자 = 194,53 번 떨어질 것입니다 (이것은 정확히 통계에서 호출되는 것입니다 물질적 지식), неко оорошо видно, что некотор ые числа (27, 32, 11…) выпадали заметно ч r (+18%), аекототото 울거리 (10, 21, 6…) набо 울거리입니다. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающи

작업 3. 오랫동안 그려지지 않은 숫자는 무엇입니까?

또 다른 전략은 충분히 많은 수의 무승부에서 조만간 1에서 45까지의 모든 숫자에서 탈락해야 한다는 아이디어에 기반을 두고 있습니다. 따라서 오랜 기간 동안 승자 사이에 일부 숫자가 나타나지 않은 경우("콜드 볼") 미래에 베팅을 시도하는 것이 논리적입니다. 

Можно легко найти все давно не выпадавшие номера, если отсортировать нбаш нархив тиражей за потудо2020 더 많이 노출 (성냥). 각 번호를 검색하고 이 번호가 마지막으로 삭제된 실행의 일련 번호(연말부터 시작까지 계산)를 제공하기 위해 위에서 아래로(즉, 새 실행에서 이전 실행으로) 검색합니다.

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

또 다른 게임 전략은 숫자를 추측할 때 심리적 요인을 제거하는 것입니다. 플레이어가 내기를 하여 숫자를 선택할 때 그는 무의식적으로 완전히 합리적이지 않습니다. 예를 들어, 통계에 따르면 1에서 31 사이의 숫자는 나머지(가장 좋아하는 날짜)보다 70% 더 자주 선택되고, 13은 덜 자주 선택되며(젠장) "행운" XNUMX이 포함된 숫자는 더 자주 선택됩니다. 그러나 우리는 모든 숫자가 동일한 기계(복권 드럼)와 게임을 하고 있으므로 기회를 균등하게 하기 위해 동일한 수학적 공정성으로 숫자를 선택하는 것이 합리적입니다. 이렇게 하려면 Excel에서 난수 생성기를 만들고 가장 중요하게는 반복되지 않는 숫자를 만들어야 합니다.

이것을하기 위해:

1. 이름이 '스마트'인 테이블을 만들어 보겠습니다. 테이블 생성기, 여기서 첫 번째 열은 1에서 45까지의 숫자입니다.
2. 두 번째 열에 각 숫자의 가중치를 입력합니다(나중에 필요합니다). 모든 숫자가 우리에게 동등하게 가치가 있고 동일한 확률로 선택하려는 경우 가중치는 모든 곳에서 1로 설정할 수 있습니다.
3. 세 번째 열에서는 함수를 사용합니다. 슬치스 (랜드), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего ст Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) 각각의 가중치를 고려하여 새로운 45개의 난수 세트가 생성됩니다.
4. 함수를 사용하는 네 번째 열을 추가해 보겠습니다. 계급 (계급) вычислим rang (позицию в топе) для каждого из чисел.

이제 함수를 사용하여 순위 6으로 처음 XNUMX개의 숫자를 선택해야 합니다. 더 많이 노출 (성냥):

При нажатии на клавишу F9 Excel 시트의 수식이 다시 계산되고 매번 녹색 셀에 6개의 새로운 숫자 세트가 표시됩니다. 또한 B열에 더 큰 가중치가 설정된 숫자는 그에 비례하여 더 높은 순위를 받으므로 무작위 표본의 결과에 더 자주 나타납니다. 모든 숫자에 대한 가중치를 동일하게 설정하면 모든 숫자가 동일한 확률로 선택됩니다. 이렇게 하면 6개 중 45개의 공정하고 편향되지 않은 난수 생성기를 얻을 수 있지만 필요한 경우 분포의 임의성을 조정할 수 있습니다.

각 무승부에서 하나가 아니라 예를 들어 한 번에 두 개의 티켓으로 플레이하기로 결정한 경우 각 티켓에서 반복되지 않는 번호를 선택하면 맨 아래에서 녹색 범위까지 추가 라인을 추가할 수 있습니다. 순위에 6, 12, 18 등을 추가합니다. 디. 각기:

작업 5. Excel의 복권 시뮬레이터

이 전체 주제의 신조로 Excel에서 본격적인 복권 시뮬레이터를 만들어보고 모든 전략을 시도하고 결과를 비교할 수 있습니다 (최적화 이론에서는 비슷한 것을 Monte Carlo 방법이라고도하지만 더 간단합니다. 우리를 위해).

모든 것을 가능한 한 현실에 가깝게 만들기 위해, 1년 2022월 XNUMX일이고 우리가 플레이할 올해의 추첨이 우리 앞에 있다고 잠시 상상해 보십시오. 나는 테이블에 실제 떨어진 숫자를 입력했습니다. tablTiraži2022, 후속 계산의 편의를 위해 추가로 그려진 숫자를 서로 별도의 열로 분리합니다.

별도의 시트에 게임 이름이있는 "스마트"테이블 형태로 모델링을 위해 공백을 만듭니다. 탭이그라 다음 형식:

이리:

• 위의 노란색 셀에서 매크로에 대해 2022년 참여를 원하는 무승부 수(1-82)와 각 무승부에서 플레이하는 티켓 수를 설정합니다.
• 처음 11개 열(AJ)의 데이터는 2022년 드로시트의 매크로에 의해 복사됩니다.
• 다음 XNUMX개 열(KP)에 대한 데이터는 매크로가 시트에서 가져옵니다. 발전기, 여기서 난수 생성기를 구현했습니다(위의 문제 4 참조).
• Q 열에서 삭제된 숫자와 함수를 사용하여 생성된 숫자 간의 일치 수를 계산합니다. SUMPRODUCT (합산).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, игра зел)
• 마지막 열 S에서는 전체 게임의 전체 결과를 누적 합계로 간주하여 프로세스의 역학을 확인합니다.

그리고 이 전체 구조를 되살리려면 작은 매크로가 필요합니다. 탭에서 개발자 (개발자) 팀을 선택 Visual Basic을 또는 키보드 단축키를 사용 다른+F11. 그런 다음 메뉴를 통해 새 빈 모듈을 추가합니다. 삽입 – 모듈 거기에 다음 코드를 입력하십시오.

Set Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets(") = Worksheets("Игр 설정) wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ( "6 : 1048576"). 삭제 '· 끄는'очищаем старые данн 목표는 t = 1 to b = 1 ~ itickets 'wozamets'견없는 worcathecull (to rathiver) + соте ciigul (Âwiscull). (2022, 1).Copy Destination:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор wsNumbers.Range("G1:L10").Copy wsGame.Cells(i, 1) .PasteSpecial Paste:=xlPasteValues ​​i = i + 4 Next b Next t End Sub  

노란색 셀에 원하는 초기 매개변수를 입력하고 매크로를 실행해야 합니다. 개발자 – 매크로 (개발자 — 매크로) 또는 키보드 단축키 다른+F8.

명확성을 위해 게임 중 잔액의 변화를 반영하여 누적 합계가 있는 마지막 열에 대한 다이어그램을 작성할 수도 있습니다.

다른 전략의 비교

이제 생성된 시뮬레이터를 사용하여 2022년 실제 무승부에서 모든 게임 전략을 테스트하고 가져올 결과를 확인할 수 있습니다. 각 추첨에서 1장의 티켓을 사용하는 경우 "자두"의 전체 그림은 다음과 같습니다.

이리:

• 발전기 는 각 추첨에서 생성기에 의해 생성된 임의의 숫자(같은 가중치로)를 선택하는 게임입니다.
• 즐겨 찾기 는 각 무승부에서 지난 27년 동안 무승부에서 가장 자주 탈락한 숫자(32, 11, 14, 34, 40, XNUMX)를 사용하는 게임입니다.
• 외부인 – 동일하지만 가장 드문 드롭다운 번호(12, 18, 26, 10, 21, 6)를 사용합니다.
• 감기 – 모든 추첨에서 오랫동안 떨어지지 않은 숫자(35, 5, 39, 11, 6, 29)를 사용합니다.

보시다시피 큰 차이는 없지만 난수 생성기가 다른 "전략"보다 조금 더 잘 작동합니다.

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

무작위로 생성된 번호(같은 가중치)가 있는 하나의 티켓으로 각 무승부에서 플레이:

무작위로 생성된 숫자(같은 가중치)로 각 추첨에서 10장의 티켓을 재생합니다.

각 추첨에서 무작위 숫자로 100장의 티켓을 재생합니다(같은 무게):

의견은 불필요합니다. 모든 경우에 보증금 배수가 불가피합니다 🙂