이 글에서는 Excel에서 데이터를 정규 분포(Normal Distribution)로 변환하는 방법을 소개합니다. 통계학에서 정규 분포 개념은 단일 데이터 또는 여러 데이터가 데이터셋 내에서 어떤 위치에 있는지 이해하는 데 매우 중요합니다. 정규 분포 값을 일일이 수동으로 계산하는 것은 시간도 많이 걸리고 번거로운 작업입니다. 다행히 Excel에는 이 문제를 순식간에 해결해 주는 함수가 있는데, 바로 NORM.DIST 함수입니다. 자세한 내용을 알아보겠습니다.
정규 분포란 무엇인가?
정규 분포는 가우시안 분포(Gaussian Distribution)라고도 불리며, 표본 공간의 확률 분포를 의미합니다. 정규 분포에서 데이터의 분포는 평균을 중심으로 대칭을 이루며, 평균에 가까운 데이터가 평균에서 먼 데이터보다 더 많이 나타나는 정도를 측정한 것입니다. 특히 정규 분포에서는 평균(Mean)이 0, 표준 편차(Standard Deviation)가 1이라는 점이 중요합니다. 실생활에서 정규 분포는 한 집단 내에서 각 데이터가 갖는 의미와 중요성을 파악하는 데 큰 도움이 됩니다.
데이터셋에서 정규 분포를 구하는 공식은 다음과 같습니다.
여기서,
σ = 표준 편차 (Standard Deviation)
x = 표본 데이터 (Sample Data)
μ = 평균 (Average/Mean)
Excel에서 데이터를 정규 분포로 변환하는 2가지 방법
예제 데이터셋에는 시험에서 학생들의 성적 정보가 들어 있습니다. Excel을 사용해 학생들의 점수를 정규 분포로 변환하여 누가 뛰어난 성적을 냈는지 확인해 보겠습니다.
1. Excel 함수를 활용한 정규 분포 변환
데이터를 정규 분포로 변환하는 가장 좋은 방법은 Excel의 NORM.DIST 함수를 사용하는 것입니다. 이 함수는 앞서 설명한 수식을 기반으로 정규 분포 값을 자동으로 계산해 줍니다. 아래 과정을 따라 해 보세요.
단계:
- 먼저 정규 분포 값을 저장할 열을 만들고, 평균(Mean)과 표준 편차(Standard Deviation)를 저장할 두 개의 셀을 선택합니다.
- 평균을 계산하기 위해 다음 수식을 입력한 후 Enter 키를 누릅니다. 그러면 학생들 점수의 평균이 표시됩니다.
=AVERAGE(C5:C11)
여기서 AVERAGE 함수는 학생들 점수의 평균값을 반환합니다.
- 다음으로, 표준 편차를 계산하기 위해 아래 수식을 입력합니다.
=STDEV.P(C5:C11)
STDEV.P 함수는 해당 데이터의 표준 편차를 반환합니다.
- 그다음, 셀 D5에 다음 수식을 입력합니다.
=NORM.DIST(C5,$C$13,$C$14,FALSE)
- Enter 키를 누르면 셀 C5에 있는 점수의 정규 분포 값이 표시됩니다.
- 이후 채우기 핸들(Fill Handle)을 사용해 아래쪽 셀까지 자동 채우기(AutoFill) 합니다.
이렇게 하면 데이터셋의 모든 데이터를 Excel에서 정규 분포로 변환할 수 있습니다.
- 데이터를 정규 분포 그래프로 확인하고 싶다면 범위 C4:D11을 선택합니다.
- 그다음 삽입(Insert) >> 분산형 곡선이 있는 꺾은선형(Scatter with Smooth Lines) 차트를 선택합니다.
이제 데이터가 정규 분포 그래프로 표시됩니다.
참고로 전체 데이터 중 약 68%는 평균 ± 표준 편차 범위 안에, 95%는 평균 ± 2×표준 편차 범위 안에, 99.7%는 평균 ± 3×표준 편차 범위 안에 속합니다. 즉, 학생들의 68%는 74.57 ± 10.66, 즉 63.91점부터 85.23점 사이의 점수를 받았다는 뜻입니다. 같은 방식으로 95%와 99.7%에 해당하는 구간도 구할 수 있습니다.
2. 수학적 수식을 적용한 정규 분포 변환
통계에 익숙하고 기본 제공되는 NORM.DIST 함수를 사용하지 않고 싶다면, 정규 분포의 수학적 수식을 직접 활용할 수도 있습니다. 아래 과정을 따라 해 보세요.
단계:
- 먼저 정규 분포 값을 저장할 열을 만들고, 평균과 표준 편차를 저장할 두 개의 셀을 선택합니다.
- 1번 방법에서 설명한 과정대로 평균과 표준 편차를 계산합니다.
- 그다음, 셀 D5에 다음 수식을 입력합니다.
=EXP(-0.5*((C5-$C$13)/$C$14)^2)/($C$14*SQRT(2*PI()))
여기서 이 수식은 EXP 함수와 SQRT 함수를 사용해 정규 분포 변환의 수학적 수식을 구현합니다. 입력한 데이터의 정규 분포 값이 반환됩니다.
- Enter 키를 누르면 셀 C5에 있는 점수의 정규 분포 값이 표시됩니다.
- 이후 채우기 핸들(Fill Handle)을 사용해 아래쪽 셀까지 자동 채우기 합니다.
이렇게 하면 수학적 수식을 사용해 데이터셋의 모든 데이터를 정규 분포로 변환할 수 있습니다.
- 데이터를 정규 분포 그래프로 확인하고 싶다면 1번 방법의 그래프 생성 과정을 참고하세요.
연습하기
아래는 이 글에서 사용한 데이터셋입니다. 직접 다운로드하여 위에서 소개한 방법들을 연습해 보세요.
마무리
지금까지 Excel에서 데이터를 정규 분포로 변환하는 기본적인 방법 두 가지를 살펴보았습니다. NORM.DIST 함수를 활용하는 방법과 수학적 수식을 직접 적용하는 방법 모두 상황에 따라 유용하게 사용할 수 있으니, 본인에게 맞는 방법을 선택하시기 바랍니다. 이 글에 대해 더 나은 방법이나 질문, 피드백이 있다면 댓글로 공유해 주세요. 더 궁금한 점이 있다면 ExcelDemy 웹사이트를 방문해 보세요.