Gerar número aleatório com uma média e desvio padrão específicos no Excel

Gerar um conjunto de números aleatórios com uma média e desvio padrão especificados é um requisito comum em áreas como simulação estatística, testes de algoritmos ou modelagem de processos em campos como finanças, engenharia e educação. No entanto, o Excel não oferece uma função interna direta para gerar instantaneamente essa lista de números aleatórios adaptada tanto a uma média quanto a um desvio padrão específico. Se você frequentemente precisa criar dados de teste randomizados que correspondam estatisticamente a determinadas características, saber como fazer isso pode melhorar significativamente a eficiência do seu fluxo de trabalho e a qualidade dos seus dados.

Neste tutorial, apresentaremos maneiras práticas de gerar números aleatórios com base na média e no desvio padrão que você especificar, com instruções detalhadas passo a passo, explicações dos parâmetros das fórmulas e dicas de especialistas para prevenção de erros e solução de problemas. Além disso, fornecemos uma solução de macro VBA para usuários que precisam automatizar esse processo ou gerar grandes conjuntos de dados de forma eficiente.

Gerar número aleatório com média e desvio padrão fornecidos

Código VBA - Gerar números aleatórios com média e desvio padrão especificados

---

Gerar número aleatório com média e desvio padrão fornecidos

No Excel, você pode criar um conjunto de números aleatórios que se ajustem à sua média e desvio padrão desejados aplicando uma combinação de funções padrão. Siga estas etapas para obter uma solução adequada para conjuntos de dados de pequeno a médio porte ou necessidades rápidas e ad-hoc:

1. Primeiro, insira sua média e desvio padrão alvo em duas células vazias separadas. Para clareza e organização, digamos que você use a célula B1 para a média necessária e a célula B2 para o desvio padrão necessário. Veja a captura de tela:

2. Para criar os dados aleatórios iniciais, vá para a célula B3 e insira a seguinte fórmula:

=NORMINV(RAND(),$B$1,$B$2)

Após inserir a fórmula, arraste a alça de preenchimento para baixo para preencher quantas linhas forem necessárias para o seu conjunto de dados aleatórios. Cada célula irá gerar um valor com base na média e no desvio padrão especificados.

Dica: Dentro da fórmula =NORMINV(RAND(),$B$1,$B$2):

• RAND() produz uma probabilidade aleatória diferente entre 0 e 1 sempre que a planilha recalcular.
• $B$1 refere-se ao valor médio que você especificou.
• $B$2 refere-se ao desvio padrão desejado.

Para versões modernas do Excel (2010 e posteriores), considere usar =NORM.INV(RAND(),$B$1,$B$2), que é funcionalmente o mesmo, mas reflete nomes de funções atualizadas.

3. Para verificar se seus números gerados estatisticamente se assemelham à sua média e desvio padrão pretendidos, use as seguintes fórmulas para calcular os valores reais da sua amostra gerada. Na célula D1, calcule a média da amostra com:

=AVERAGE(B3:B16)

Em D2, calcule o desvio padrão da amostra com:

=STDEV.P(B3:B16)

Dica:

• B3:B16 é apenas um intervalo de exemplo. Ajuste-o de acordo com quantos valores aleatórios você gerou na Etapa 2.
• Uma amostra aleatória maior resulta em uma média e um desvio padrão reais mais próximos dos seus valores especificados, devido à lei dos grandes números.

4. Para ajustar ainda mais sua série para que ela corresponda exatamente à sua média e desvio padrão pretendidos, normalize seus valores aleatórios iniciais. Na célula D3, insira a seguinte fórmula:

=$B$1+(B3-$D$1)*$B$2/$D$2

Arraste a alça de preenchimento para baixo por tantas linhas quantos números aleatórios você tiver. Esta fórmula padroniza seus valores iniciais e os dimensiona precisamente para atender à média e ao desvio padrão em B1 e B2.

Dica:

• B1 é a sua média necessária.
• B2 é o seu desvio padrão necessário.
• B3 é o valor aleatório original.
• D1 é a média desses valores aleatórios originais.
• D2 é o desvio padrão desses valores aleatórios originais.

Agora você pode confirmar que o conjunto final de valores atende aos seus requisitos recalculando sua média e desvio padrão para fins de garantia de qualidade e documentação.

5. Na célula D17, calcule a média do seu conjunto de números aleatórios finais com a seguinte fórmula:

=AVERAGE(D3:D16)

Depois, na célula D18, calcule o desvio padrão com a fórmula abaixo:

=STDEV.P(D3:D16)

Dica: D3:D16 refere-se ao intervalo dos seus números aleatórios finalizados.

Solução de problemas:

• Se você visualizar um erro #VALOR!, verifique novamente todos os intervalos de células referenciados e certifique-se de que nenhuma fórmula faça referência a células em branco ou inválidas.
• Se a fórmula continuar mudando toda vez que você recalcular, selecione os números aleatórios finais, copie-os e use Colar Especial > Valores para evitar novas atualizações.
• Lembre-se de que geradores aleatórios no Excel dependem de recálculo, então salvar resultados estáticos é necessário quando a consistência é crítica.

---

Código VBA - Gerar números aleatórios com média e desvio padrão especificados

Para cenários em que você precisa gerar rapidamente uma grande quantidade de dados aleatórios que correspondam a uma média e desvio padrão especificados — especialmente em casos repetitivos, automatizados ou de alto volume — uma macro VBA oferece uma solução que economiza tempo. Com apenas uma execução, você pode criar um conjunto completo de dados diretamente na sua pasta de trabalho, reduzindo a repetição manual e minimizando erros de cópia de fórmulas.

Essa abordagem é adequada para:

• Geração automática de conjuntos de dados aleatórios para simulações, testes de estresse ou demonstrações educacionais.
• Situações em que você deseja padronizar o formato de saída com intervenção manual mínima.
• Usuários confortáveis com o uso do Editor VBA no Excel.

Comparado aos métodos de fórmulas, o VBA também pode permitir ajustes dinâmicos ou integração com fluxos de trabalho mais complexos, mas tenha em mente que as macros devem estar habilitadas na sua pasta de trabalho e podem exigir salvamento explícito no formato .xlsm "habilitado para macro".

1. Na barra de ferramentas do Excel, clique em Ferramentas de Desenvolvedor (se não estiver visível, habilite-a via Arquivo > Opções > Personalizar Faixa de Opções), depois selecione Visual Basic. Na janela Visual Basic for Applications, clique em Inserir > Módulo e copie o código a seguir na janela de módulo vazio:

Sub GenerateRandomNumbersWithMeanStd()
    Dim outputRange As Range
    Dim meanValue As Double, stdDevValue As Double
    Dim numItems As Long, i As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set outputRange = Application.InputBox("Select the output range", xTitleId, Type:=8)
    meanValue = Application.InputBox("Enter the mean value", xTitleId, "", Type:=1)
    stdDevValue = Application.InputBox("Enter the standard deviation", xTitleId, "", Type:=1)
    
    If outputRange Is Nothing Or meanValue = 0 Or stdDevValue = 0 Then
        MsgBox "Please ensure you have specified all required parameters.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    numItems = outputRange.Count
    Randomize
    
    For i = 1 To numItems
        outputRange.Cells(i).Value = Application.WorksheetFunction.NormInv(Rnd, meanValue, stdDevValue)
    Next i
End Sub

2. Clique no botão Executar (ou pressione F5) para iniciar a macro. Uma caixa de diálogo solicitará que você selecione o intervalo onde deseja gerar os números aleatórios (por exemplo, selecione A1:A100 para 100 valores). Em seguida, será solicitado que você insira a média e o desvio padrão desejados. A macro preencherá o intervalo com números aleatórios que correspondem às suas especificações.

Dicas e Solução de Problemas:

• O VBA usa a função NormInv do Excel para gerar números distribuídos normalmente — sempre verifique se a sua versão suporta isso; para versões mais antigas do Excel, a função pode precisar ser NORMINV.
• A semente aleatória é definida com Randomize para resultados variados em cada execução.
• Se você quiser resultados reprodutíveis, comente ou remova a linha Randomize.
• A macro substituirá qualquer dado existente no intervalo de saída selecionado, então certifique-se de escolher uma área vazia, se necessário.
• Se você inserir valores inadequados (por exemplo, um desvio padrão negativo ou zero), a macro não prosseguirá e mostrará uma mensagem de aviso.

---

Artigos Relacionados:

• Gerar números aleatórios sem repetição no Excel
• Gerar números aleatórios positivos ou negativos no Excel
• Parar a mudança de números aleatórios no Excel
• Gerar sim ou não aleatoriamente no Excel