Fórmula genérica
=MATCH(RAND(),cumulative_probability)
Resumo
Para gerar um número aleatório, ponderado com uma determinada probabilidade, você pode usar uma tabela auxiliar junto com uma fórmula baseada nas funções RAND e MATCH.
No exemplo mostrado, a fórmula em F5 é:
=MATCH(RAND(),D$5:D$10)
Explicação
Esta fórmula se baseia na tabela auxiliar visível no intervalo B4: D10. A coluna B contém os seis números que queremos como resultado final. A coluna C contém o peso da probabilidade atribuído a cada número, inserido como uma porcentagem. A coluna D contém a probabilidade cumulativa, criada com esta fórmula em D5, copiada para baixo:
=SUM(D4,C4)
Observe, estamos intencionalmente deslocando a probabilidade cumulativa para baixo uma linha, de modo que o valor em D5 seja zero. Isso é para garantir que MATCH seja capaz de encontrar uma posição para todos os valores até zero, conforme explicado abaixo.
Para gerar um valor aleatório, usando a probabilidade ponderada na tabela auxiliar, F5 contém esta fórmula, copiada:
=MATCH(RAND(),D$5:D$10)
Dentro de MATCH, o valor de pesquisa é fornecido pela função RAND. RAND gera um valor aleatório entre zero e 1. O array de pesquisa está no intervalo D5: D10, bloqueado para que não mude conforme a fórmula é copiada na coluna.
O terceiro argumento para MATCH, tipo de correspondência, é omitido. Quando o tipo de correspondência é omitido, MATCH retornará a posição do maior valor menor ou igual ao valor de pesquisa *. Em termos práticos, isso significa que a função MATCH viaja ao longo dos valores em D5: D10 até que um valor maior seja encontrado e, em seguida, "volta" para a posição anterior. Quando MATCH encontra um valor maior do que o último maior valor em D5: D10 (.7 no exemplo), ele retorna a última posição (6 no exemplo). Conforme mencionado acima, o primeiro valor em D5: D10 é deliberadamente zero para garantir que os valores abaixo de .1 sejam "capturados" pela tabela de pesquisa e retornem uma posição de 1.
* Os valores no intervalo de pesquisa devem ser classificados em ordem crescente.
Valor de texto aleatório ponderado
Para retornar um valor de texto ponderado aleatório (ou seja, um valor não numérico), você pode inserir valores de texto no intervalo B5: B10 e, em seguida, adicionar INDEX para retornar um valor nesse intervalo, com base na posição retornada por MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Notas
- Encontrei essa abordagem em uma postagem do fórum em mrexcel.com
- RAND é uma função volátil e irá recalcular a cada alteração da planilha
- Depois de ter os valores aleatórios, use colar valores especiais> para substituir a fórmula, se necessário
