지정된 평균과 표준편차를 기반으로 Excel에서 난수 생성

특정한 평균과 표준편차를 가진 난수 집합을 생성하는 것은 통계적 시뮬레이션, 알고리즘 테스트 또는 금융, 공학, 교육 분야의 프로세스 모델링과 같은 영역에서 일반적으로 요구되는 작업입니다. 그러나 Excel에는 주어진 평균과 표준편차에 맞춘 난수 목록을 즉시 생성할 수 있는 직접적인 내장 함수가 제공되지 않습니다. 특정 통계적 특성을 가지는 무작위 테스트 데이터를 자주 만들어야 한다면 이를 달성하는 방법을 아는 것이 작업 효율성과 데이터 품질을 크게 향상시킬 수 있습니다.

이 튜토리얼에서는 지정된 평균과 표준편차를 기반으로 난수를 생성하는 실용적인 방법을 소개합니다. 이 과정은 단계별로 상세히 설명되며, 수식 매개변수에 대한 해설과 오류 방지 및 문제 해결을 위한 전문가 팁도 포함됩니다. 또한, 이 과정을 자동화하거나 대규모 데이터 세트를 효율적으로 생성해야 하는 사용자를 위해 VBA 매크로 솔루션도 제공합니다.

지정된 평균과 표준편차를 기반으로 난수 생성

VBA 코드 - 지정된 평균과 표준편차를 가진 난수 생성

---

지정된 평균과 표준편차를 기반으로 난수 생성

Excel에서 표준 함수들을 조합하여 원하는 평균과 표준편차에 맞는 난수 집합을 생성할 수 있습니다. 다음 단계를 따라 소규모에서 중간 규모의 데이터 세트 또는 간단한 임시 작업에 적합한 솔루션을 찾으세요:

1. 먼저, 목표로 하는 평균과 표준편차를 두 개의 빈 셀에 입력하세요. 명확하고 체계적으로 진행하기 위해 B1 셀에 필요한 평균을, B2 셀에 필요한 표준편차를 입력한다고 가정하겠습니다. 스크린샷 참조:

2. 초기 난수 데이터를 생성하려면 B3 셀로 이동하여 다음 수식을 입력하세요:

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

수식을 입력한 후, 채우기 핸들을 드래그하여 필요한 만큼 많은 행을 채우세요. 각 셀은 지정된 평균과 표준편차를 기반으로 값을 생성합니다.

팁: 수식 =NORMINV(RAND(),$B$1,$B$2) 내에서:

• RAND()는 워크시트가 다시 계산될 때마다 0과 1 사이의 다른 난수 확률을 생성합니다.
• $B$1은 지정한 평균값을 나타냅니다.
• $B$2는 원하는 표준편차를 나타냅니다.

최신 버전의 Excel(2010 이후)에서는 =NORM.INV(RAND(),$B$1,$B$2)사용하는 것을 고려하세요. 이는 기능적으로 동일하지만 업데이트된 함수 이름을 반영합니다.

3. 생성된 숫자들이 통계적으로 의도한 평균과 표준편차와 유사한지 확인하려면, 생성된 샘플의 실제 값을 계산하기 위한 다음 수식을 사용하세요. D1 셀에서 다음 수식으로 샘플 평균을 계산하세요:

=AVERAGE(B3:B16)

D2에서 다음 수식으로 샘플 표준편차를 계산하세요:

=STDEV.P(B3:B16)

팁:

• B3:B16은 예시 범위입니다. 2단계에서 생성한 난수의 개수에 맞게 조정하세요.
• 큰 난수 샘플은 큰 수의 법칙에 따라 실제 평균과 표준편차가 지정된 값에 더 가까워집니다.

4. 난수 시리즈를 정확히 의도한 평균과 표준편차에 맞추기 위해 초기 난수 값을 정규화하세요. D3 셀에 다음 수식을 입력하세요:

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

난수 개수만큼 많은 행까지 채우기 핸들을 드래그하세요. 이 수식은 초기 값을 표준화하고 B1과 B2에 있는 평균과 표준편차에 정확히 맞춥니다.

팁:

• B1은 요구한 평균입니다.
• B2는 요구한 표준편차입니다.
• B3은 원래의 난수 값입니다.
• D1은 원래 난수 값들의 평균입니다.
• D2는 원래 난수 값들의 표준편차입니다.

이제 최종 값 집합이 요구사항을 충족하는지 확인하기 위해 평균과 표준편차를 재계산하여 품질 보증 및 문서화를 수행할 수 있습니다.

5. D17 셀에서 다음 수식으로 최종 난수 세트의 평균을 계산하세요:

=AVERAGE(D3:D16)

그런 다음 D18 셀에서 아래 수식으로 표준편차를 계산하세요:

=STDEV.P(D3:D16)

팁: D3:D16은 최종 난수 범위를 나타냅니다.

문제 해결:

• #VALUE! 오류가 발생하면, 모든 참조된 셀 범위를 다시 확인하고 공백이나 잘못된 셀을 참조하지 않는지 확인하세요.
• 수식이 다시 계산할 때마다 계속 변경된다면, 최종 난수를 선택해 복사하고 '선택하여 붙여넣기 > 값'을 사용하여 추가 업데이트를 방지하세요.
• Excel의 난수 생성기는 다시 계산에 의존하므로 일관성이 중요한 경우 정적 결과를 저장해야 합니다.

---

VBA 코드 - 지정된 평균과 표준편차를 가진 난수 생성

지정된 평균과 표준편차를 갖는 다량의 난수 데이터를 빠르게 생성해야 하는 경우 — 특히 반복적이거나 자동화된 고용량 작업의 경우 — VBA 매크로는 시간을 절약해 줍니다. 한 번 실행으로 바로 워크북에 전체 데이터 세트를 생성하여 수작업 반복을 줄이고 수식 복사 오류를 최소화할 수 있습니다.

이 접근법은 다음 경우에 적합합니다:

• 시뮬레이션, 스트레스 테스트 또는 교육 시연을 위한 난수 데이터 세트를 자동으로 생성하는 경우
• 최소한의 수작업으로 출력 형식을 표준화하려는 경우
• Excel에서 VBA 편집기를 사용하는 데 익숙한 사용자

수식 방법과 비교했을 때 VBA는 동적 조정이나 복잡한 워크플로와의 통합을 가능하게 하지만, 매크로는 워크북에서 활성화되어야 하며 "매크로 지원" .xlsm 형식으로 명시적으로 저장해야 할 수도 있습니다.

1. Excel 리본에서 개발 도구를 클릭하세요 (보이지 않으면 파일 > 옵션 > 리본 사용자 정의에서 활성화). 그런 다음 Visual Basic을 선택하세요. Visual Basic for Applications 창에서 삽입 > 모듈을 클릭하고 다음 코드를 비어 있는 모듈 창에 복사하세요:

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다음을 클릭하세요 실행 버튼 (또는 F5키를 눌러) 매크로를 시작합니다. 대화 상자가 나타나서 난수를 출력할 범위를 선택하라고 묻습니다 (예: A1:A100을 선택하여 100개의 값을 생성). 다음으로 원하는 평균과 표준편차를 입력하라는 메시지가 표시됩니다. 매크로는 해당 범위에 지정된 조건에 맞는 난수로 채웁니다.

팁 및 문제 해결:

• VBA는 정규 분포된 숫자를 생성하기 위해 Excel의 NormInv 함수를 사용합니다 — 항상 버전이 이를 지원하는지 다시 확인하세요. 이전 Excel 버전에서는 NORMINV 함수를 사용해야 할 수도 있습니다.
• Randomize를 통해 각 실행마다 다양한 결과를 얻도록 난수 시드가 설정됩니다.
• 재현 가능한 결과를 원한다면 Randomize 줄을 주석 처리하거나 제거하세요.
• 매크로는 선택한 출력 범위에 있는 기존 데이터를 덮어쓰므로 필요하다면 빈 영역을 선택해야 합니다.
• 부적절한 값을 입력한 경우 (예: 음수 또는 0인 표준편차), 매크로는 진행되지 않고 경고 메시지가 표시됩니다.

---

관련 기사:

• Excel에서 중복 없이 난수 생성
• Excel에서 양수 또는 음수 난수 생성
• Excel에서 변동하는 난수 멈추기
• Excel에서 무작위로 예/아니오 생성