Simulação de loteria no Excel

A loteria não é uma caça à sorte,

é uma caça aos perdedores.

Com uma regularidade invejável (e com mais frequência ultimamente), as pessoas me escrevem pedindo ajuda em vários cálculos relacionados a loterias. Alguém quer implementar seu algoritmo secreto para selecionar números vencedores no Excel, alguém quer encontrar padrões nos números que caíram de sorteios anteriores, alguém quer pegar os organizadores da loteria em um jogo desonesto.

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

Tarefa 1. Probabilidade de ganhar

Vamos pegar a clássica loteria Stoloto 6 de 45 como exemplo. De acordo com as regras, apenas aqueles que adivinharam todos os 10 números de 6 recebem um super prêmio (45 milhões de rublos ou mais se o saldo do fundo de prêmios tiver acumulado em sorteios anteriores). Se você adivinhou 5, receberá 150 mil rublos, se 4 – 1500 rublos. , se 3 números de 6, então 150 rublos, se 2 números – você devolverá 50 rublos gastos no bilhete. Adivinhe apenas um ou nenhum - obtenha apenas endorfinas no processo do jogo.

A probabilidade matemática de ganhar pode ser facilmente calculada usando a função padrão NÚMERO COMB (COMBINAR), que está disponível no Microsoft Excel para esse caso. Esta função calcula o número de combinações de N números de M. Então, para nossa loteria “6 de 45” seria:

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

… que é igual a 8, o número total de todas as combinações possíveis nesta loteria.

Se você quiser calcular a probabilidade de uma vitória parcial (2-5 números de 6), primeiro terá que calcular o número de tais opções, que é igual ao produto do número de combinações de números adivinhados de 6 pelo número de números não adivinhados dos restantes (45-6) = 39 números. Em seguida, dividimos o número total de todas as combinações possíveis (8) pelo número de vitórias recebidas para cada opção – e obtemos as probabilidades de vitória para cada caso:

A propósito, a probabilidade, por exemplo, de morrer em um acidente de avião em Nosso País é estimada em cerca de 1 em um milhão. E a probabilidade de ganhar em um cassino na roleta, apostando tudo em um número é de 1 a 37.

Se todos os itens acima não o impediram e você ainda está pronto para continuar jogando, continue.

Tarefa 2. Frequência de ocorrência de cada número

Para começar, vamos determinar com que frequência certos números caem. Em uma loteria ideal, dado um intervalo de tempo suficientemente grande para análise, todas as bolas deveriam ter a mesma probabilidade de estar na amostra vencedora. Na realidade, as características de design do tambor de loteria e a forma do peso das bolas podem distorcer essa imagem e, para algumas bolas, a probabilidade de cair pode ser maior/menor do que para outras. Vamos testar essa hipótese na prática.

Tomemos, por exemplo, dados de todos os 2020 sorteios de loteria de 21 que ocorreram em 6-45 do site de seu organizador Stoloto, projetado na forma de uma tabela tão “inteligente”, conveniente para análise, com o nome tabArquivo Circulação. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала вализя дляя анализѺа дляя

Para calcular a frequência de ocorrência de cada número, use a função COUNTIF (CONT.SE) e adicione uma função a ele TEXTO (TEXTO)para adicionar zeros e asteriscos à esquerda antes e depois de números de um dígito, para que CONT.SE procure a ocorrência de um número em qualquer lugar da combinação na coluna B. Além disso, para maior clareza, vamos construir um gráfico por resultados e classificar as frequências por ordem decrescente:

Em média, qualquer bola deve cair 1459 empates * 6 bolas / 45 números = 194,53 vezes (isso é exatamente o que é chamado nas estatísticas математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная масса. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.

Tarefa 3. Que números não são sorteados há muito tempo?

Outra estratégia baseia-se na ideia de que com um número suficientemente grande de sorteios, mais cedo ou mais tarde cada número disponível de 1 a 45 deve cair. Portanto, se alguns números não aparecem entre os vencedores há muito tempo (“bolas frias”), é lógico tentar apostar neles no futuro. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21 год по у быванию даты и использовать функцию MAIS EXPOSTOS (COMBINE). Ele pesquisará de cima para baixo (ou seja, de corridas novas para antigas) para procurar cada número e fornecerá o número de série da corrida (contando do final do ano até o início) onde esse número foi descartado pela última vez:

Passo 4. Gerador de energia

Outra estratégia de jogo baseia-se na eliminação do fator psicológico ao adivinhar os números. Quando um jogador escolhe números fazendo sua aposta, ele subconscientemente faz isso de forma não totalmente racional. De acordo com as estatísticas, por exemplo, os números de 1 a 31 são escolhidos 70% mais vezes do que o resto (datas favoritas), 13 é escolhido com menos frequência (maldita dúzia), números contendo os sete “sortudos” são escolhidos com mais frequência, etc. Mas estamos jogando contra uma máquina (tambor de loteria) para a qual todos os números são iguais, então faz sentido escolhê-los com a mesma imparcialidade matemática para igualar nossas chances. Para fazer isso, precisamos criar um gerador de números aleatórios e – o mais importante – não repetidos no Excel:

Para fazer isso:

1. Vamos criar uma tabela “inteligente” chamada gerador de mesa, onde a primeira coluna será nossos números de 1 a 45.
2. Na segunda coluna, insira o peso para cada número (precisaremos um pouco mais tarde). Se todos os números são igualmente valiosos para nós e queremos escolhê-los com igual probabilidade, então o peso pode ser igual a 1 em todos os lugares.
3. Na terceira coluna usamos a função SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столз предыдущего столго. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) será gerado um novo conjunto de 45 números aleatórios, levando em consideração o peso de cada um deles.
4. Vamos adicionar uma quarta coluna, onde usando a função RANK (CLASSIFICAÇÃO) вычислим ранг (позицию в топе) para каждого из чисел.

Agora resta fazer uma seleção dos seis primeiros números pelo posto 6 usando a função MAIS EXPOSTOS (COMBINE):

Por que estou na clave F9 as fórmulas na planilha do Excel serão recalculadas e cada vez obteremos um novo conjunto de 6 números nas células verdes. Além disso, os números para os quais um peso maior foi definido na coluna B receberão uma classificação proporcionalmente maior e, portanto, aparecerão com mais frequência nos resultados de nossa amostra aleatória. Se o peso de todos os números for igual, todos eles serão selecionados com a mesma probabilidade. Dessa forma, obtemos um gerador de números aleatórios justo e imparcial de 6 em 45, mas com a capacidade de fazer ajustes na aleatoriedade da distribuição, se necessário.

Se decidirmos jogar em cada sorteio não com um, mas, por exemplo, com dois bilhetes de uma só vez, em cada um dos quais escolheremos números não repetidos, podemos simplesmente adicionar linhas adicionais da parte inferior ao intervalo verde, adicionando 6, 12, 18, etc. à classificação. d. respectivamente:

Tarefa 5. Simulador de loteria no Excel

Como apoteose de todo este tópico, vamos criar um simulador de loteria completo no Excel, onde você pode experimentar qualquer estratégia e comparar os resultados (na teoria da otimização, algo semelhante também é chamado de método de Monte Carlo, mas será mais simples para nós).

Para tornar tudo o mais próximo possível da realidade, imagine por um momento que é 1º de janeiro de 2022 e temos os sorteios deste ano pela frente, nos quais planejamos jogar. Eu digitei os números reais perdidos na tabela tablTiraži2022, separando os números adicionalmente sorteados uns dos outros em colunas separadas para a conveniência dos cálculos subsequentes:

Em uma folha separada Games crie um espaço em branco para modelagem na forma de uma tabela “inteligente” com o nome tabIgra o seguinte formulário:

Aqui:

• Nas células amarelas acima, definiremos para a macro o número de sorteios em 2022 em que queremos participar (1-82) e o número de ingressos que jogamos em cada sorteio.
• Os dados das primeiras 11 colunas (AJ) serão copiados pela macro da planilha de sorteio de 2022.
• Dados para as próximas seis colunas (KP) que a macro retirará da planilha Gerador, onde implementamos um gerador de números aleatórios (veja o problema 4 acima).
• Na coluna Q, contamos o número de correspondências entre os números descartados e os gerados usando a função SUMPRODUCT (SUMPRODUTO).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли , то приз — 50 р. за билет)
• Na última coluna S, consideramos o resultado geral de todo o jogo como um total cumulativo para ver a dinâmica do processo.

E para reviver toda essa estrutura, precisamos de uma pequena macro. Na guia revelador (Desenvolvedor) escolha um time Visual Basic ou use o atalho de teclado outro+F11. Em seguida, adicione um novo módulo vazio através do menu Inserir - Módulo e digite o seguinte código lá:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets("Играратор") Sets = Worksheets("Играратор") wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Exclua 'ччищаем старые даныы para t = 1 a igames para b = 1 a к ил ил ил ил ил ил ил ил ил ил ил ил ил ил и и и и и и с с с с м с ы ы ы ы ы ы a 2022 с с с м м м м м м ы ы ы ы ы ыoames'. (1, 1) .copy Destination: = wsgame.Cells (i, 1) 'кии Este и и и в danina и и в в в в в в в в вameiM (sin и и в вameiM (sin и и в в в в вameiM (sin и и в в в вameiM (sin и и в в в вameiM (sin и и в в в вameiM (sin и и в в в в вameiM (sin и и в вameiM (sina) wbers (sina) e sin и в в вameiM (sin и и в вameS, e sin и в в вameS, e sin и в в вameS (s). .PasteSpecial Paste:=xlPasteValues ​​i = i + 10 Next b Next t End Sub  

Resta inserir os parâmetros iniciais desejados nas células amarelas e executar a macro Desenvolvedor – Macros (Desenvolvedor — Macros) ou atalho de teclado outro+F8.

Para maior clareza, você também pode construir um diagrama para a última coluna com um total cumulativo, refletindo a mudança no saldo de dinheiro durante o jogo:

Comparação de diferentes estratégias

Agora, usando o simulador criado, você pode testar qualquer estratégia de jogo em empates reais em 2022 e ver os resultados que isso traria. Se você jogar 1 bilhete em cada sorteio, a imagem geral da “ameixa” será mais ou menos assim:

Aqui:

• Gerador é um jogo onde em cada sorteio escolhemos números aleatórios criados pelo nosso gerador (com o mesmo peso).
• Favoritos é um jogo onde em cada sorteio usamos os mesmos números – aqueles que mais caíram nos sorteios nos últimos dois anos (27, 32, 11, 14, 34, 40).
• Outsiders – o mesmo, mas usamos os números suspensos mais raros (12, 18, 26, 10, 21, 6).
• Frio – em todos os sorteios usamos números que não saem há muito tempo (35, 5, 39, 11, 6, 29).

Como você pode ver, não há grande diferença, mas o gerador de números aleatórios se comporta um pouco melhor que as outras “estratégias”.

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

Jogando em cada sorteio com um bilhete com números gerados aleatoriamente (com o mesmo peso):

Jogando 10 bilhetes em cada sorteio com números gerados aleatoriamente (com o mesmo peso):

Jogando 100 bilhetes em cada sorteio com números aleatórios (com o mesmo peso):

Comentários, como se costuma dizer, são supérfluos – um dreno de depósito é inevitável em todos os casos 🙂