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, teste de algoritmos ou modelagem de processos em campos como finanças, engenharia e educação. No entanto, o Excel não fornece uma função interna direta para gerar instantaneamente uma lista de números aleatórios personalizados tanto para uma média quanto para um desvio padrão dados. Se você frequentemente precisa criar dados de teste randomizados que correspondam a características estatísticas específicas, saber como realizar isso pode melhorar significativamente a eficiência do seu fluxo de trabalho e a qualidade dos 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 da fórmula 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 uma média e desvio padrão específicos

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

---

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

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 uma solução adequada para conjuntos de dados de pequeno a médio porte ou para 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 randomizados 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 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 cada vez que a planilha recalcula.
• $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 posterior), considere usar =NORM.INV(RAND(),$B$1,$B$2), que é funcionalmente o mesmo, mas reflete nomes de funções atualizadas.

3. Para verificar que 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 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 desvio padrão reais mais próximos dos 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. Essa 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 é sua média necessária.
• B2 é 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 final de números aleatórios 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ê vir 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 atualizações adicionais.
• 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 onde você precisa produzir 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 uma única execução, você pode criar um conjunto de dados completo 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 onde você deseja padronizar o formato de saída com mínima intervenção manual.
• 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 macros".

1. Na faixa de opções 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 do 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 Executar botão (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, você será solicitado a inserir 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 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á quaisquer dados existentes 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 de alterar números aleatórios no Excel
• Gerar aleatoriamente 'sim' ou 'não' no Excel