엑셀 정규 분포: NORM.INV, 평균, 표준편차
한마디로: 종 모양 곡선입니다. 대부분의 결과는 평균 가까이에 떨어지고, 평균에서 멀어질수록 드물어지며, 양쪽이 똑같습니다.
정규 분포는 평균을 중심으로 좌우 대칭으로 변하는 값을 나타내며, 퍼짐은 표준편차로 정해집니다. 엑셀에서는 =NORM.INV(RAND(), mean, sd)로 값 하나를 뽑습니다. 평균 1,000개, 표준편차 200개인 수요라면 P10은 744, P90은 1,256이고, 결과의 약 95%는 표준편차 2개 이내인 600에서 1,400 사이에 들어갑니다.
정규 분포는 무엇인가
정규 분포는 종 모양 곡선입니다. 대부분의 결과가 평균 근처에 있고, 멀어질수록 드물어지며, 양쪽이 거울처럼 대칭입니다. 숫자 두 개로 완전히 정해집니다. 평균은 중심을 정하며, 이 값이 중앙값이자 최빈값입니다. 표준편차는 폭을 정하며, 결과의 약 3분의 2는 평균에서 표준편차 1개 이내에 있습니다.
차트 읽는 법: 막대는 시뮬레이션한 10,000개 값 중 그 구간에 들어간 개수이고, 선은 이론 곡선입니다. 막대가 높을수록 그 값이 자주 나옵니다. 점선은 P10과 P90을 나타내며, 열 번 중 여덟 번은 그 사이에 떨어집니다.
| 구간 | 수요 | 결과 비중 |
|---|---|---|
| 평균 ± 표준편차 1개 | 800 – 1,200 | 68.3% |
| 평균 ± 표준편차 2개 | 600 – 1,400 | 95.4% |
| 평균 ± 표준편차 3개 | 400 – 1,600 | 99.7% |
백분위수도 같은 두 숫자에서 나옵니다. P90은 평균에서 표준편차의 1.2816배만큼 위에 있고 P10은 같은 거리만큼 아래에 있으므로, 결과의 80%가 744에서 1,256 사이에 들어갑니다.
엑셀의 정규 분포
NORM.INV(probability, mean, standard_deviation)는 주어진 비율의 결과가 그 아래에 놓이는 값을 돌려줍니다. 확률을 고정하면 백분위수가 나오고, 확률 자리에 RAND()를 쓰면 난수 추출값이 나오며, 시트를 다시 계산할 때마다 새 값으로 바뀝니다. 이 페이지의 엑셀 파일은 난수를 별도의 셀에 두어, 어떤 난수에서 어떤 추출값이 나왔는지 볼 수 있습니다. 셀 하나에 합치면 =NORM.INV(RAND(),D5,F5)입니다.
| 셀 | 수식 | 값 |
|---|---|---|
| 난수 u(I5) | =RAND() | |
| 추출값(J5) | =NORM.INV(I5, | |
| 셀 하나로 추출 | =NORM.INV(RAND(), | |
| 평균(K5) | =D5 | 1,000 |
| P10 (L5) | =NORM.INV(0.1, | 744 |
| P90 (M5) | =NORM.INV(0.9, | 1,256 |
수치 확인: 이론값과 엑셀, xellstorm의 10,000개 추출값 비교
| 이론값 | 엑셀, RAND() | xellstorm | |
|---|---|---|---|
| 평균 | 1,000 | 998 | 1,000 |
| 표준편차 | 200 | 199 | 200 |
| P10 | 744 | 743 | 744 |
| P50(중앙값) | 1,000 | 996 | 1,000 |
| P90 | 1,256 | 1,251 | 1,256 |
엑셀은 RAND()로 완전히 무작위하게 뽑고, xellstorm은 추출값을 범위 전체에 고르게 퍼뜨립니다(라틴 하이퍼큐브 샘플링). 그래서 통계값이 이론값에 더 가깝게 나옵니다.
반대로 수요가 어떤 수준 아래일 확률은 NORM.DIST(x, mean, standard_deviation, TRUE)로 구합니다. =NORM.DIST(700, 1000, 200, TRUE)의 결과는 6.7%입니다. 한 시즌의 수요가 700개 미만일 확률입니다. 예전 이름인 NORMINV와 NORMDIST도 그대로 쓸 수 있습니다. 추출값 하나는 일어날 수 있는 한 시즌일 뿐입니다. 불확실성이 이익이나 재고 수준에 어떤 영향을 주는지 보려면 모델에 이런 값이 수천 개 필요하며, 이를 해 주는 것이 몬테카를로 시뮬레이션입니다(엑셀 몬테카를로 시뮬레이션 참고).
정규 분포가 맞는 경우
- 목표값 주위의 측정값. 가공 치수, 충전 중량, 측정 오차는 공칭값 주위에서 대칭으로 변합니다. 공차 누적 예제가 그런 경우입니다.
- 독립적인 여러 부분의 합. 서로 영향을 주지 않는 작은 불확실성을 많이 더하면, 각 부분이 어떤 모양이든 합은 종 모양이 됩니다. 통계학에서는 이를 중심극한정리라고 합니다.
- 자리 잡은 제품의 수요는 표준편차가 평균에 비해 작습니다. 주문 수량 예제의 수요가 그렇습니다.
맞지 않는 경우
정규 분포에는 하한이 없어서 어느 정도 확률로 음수 값이 나옵니다. 이 페이지의 수요에서는 그 확률이 무시할 만하지만, 표준편차가 평균의 절반인 500일 때 추출값 중 음의 수요가 되는 비율은 2.3%입니다. 음수가 될 수 없고 폭넓게 퍼지는 값에는 로그정규 분포나 감마 분포를 쓰거나, 정규 분포를 0에서 절단하세요.
또 하나, 정규 분포는 좌우 대칭입니다. 비용과 기간은 대개 줄어드는 폭보다 초과하는 폭이 더 크며, 이런 치우침은 PERT 분포, 삼각 분포, 로그정규 분포가 반영합니다. 극단적인 값도 매우 드뭅니다. 평균에서 표준편차 3개 넘게 떨어진 값은 거의 나오지 않아서, 가격이나 수익률의 큰 움직임을 실제보다 작게 봅니다. 스튜던트 t 분포는 극단값이 더 자주 나옵니다.
xellstorm에서 정규 분포 입력하는 법
- 엑셀 파일을 열고, 확신이 없는 셀을 클릭한 뒤 입력으로 지정 버튼을 누릅니다.
- 분포 단계에서 정규 분포를 고르고 평균과 표준편차를 입력합니다. 실행하기 전에도 평균 열과 P10 – P90 열이 두 숫자가 나타내는 값을 보여 줍니다.
- 표준편차 대신 낮은 값, 대표값, 높은 값으로 생각하는 편이 편하다면 입력을 선택하고 사이드 패널의 “추정치로” 탭을 씁니다. P10, P50, P90을 입력하고 정규 분포를 고르세요. 과거 값이 있으면 “데이터로” 탭에서 그 값에 정규 분포와 여러 다른 모양을 적합하고 순위를 매깁니다.
- 추출값이 0 아래로 내려가지 않게 하려면 파라미터 아래의 “절단 / 이동 / 배율”을 열고 절단 최소를 0으로 설정합니다.
이미 =NORM.INV(RAND(), mean, sd)로 값을 뽑는 엑셀 파일은 고칠 필요가 없습니다. “엑셀 파일에서 가져오기”는 이런 셀을 정규 분포 입력으로 바꾸고, 평균과 표준편차는 해당 셀에 연결된 채로 둡니다. 애드인용으로 만든 모델도 RiskNormal, VoseNormal, PsiNormal에서 같은 방식으로 변환합니다.
자주 묻는 질문
엑셀에서 정규 분포를 따르는 난수는 어떻게 만드나요?
=NORM.INV(RAND(), mean, sd)를 씁니다. 평균 1,000, 표준편차 200이면 =NORM.INV(RAND(), 1000, 200)입니다. 다시 계산(F9)할 때마다 새 값이 나옵니다. 수식을 열 아래로 복사하면 값을 여러 개 뽑을 수 있고, 그 평균은 입력한 평균에, 표준편차는 입력한 표준편차에 가까워집니다.
NORM.INV와 NORM.DIST는 무엇이 다른가요?
두 함수는 반대되는 질문에 답합니다. NORM.DIST(x, mean, sd, TRUE)는 값을 받아 그 값 아래의 결과가 나올 확률을 돌려주고, NORM.INV(p, mean, sd)는 확률을 받아 그 비율의 결과가 아래에 놓이는 값을 돌려줍니다. 평균 1,000, 표준편차 200인 수요에서 NORM.INV(0.9, 1000, 200)의 결과는 1,256입니다. 이 값을 NORM.DIST에 넣으면 0.9가 나옵니다.
정규 분포의 P90은 어떻게 구하나요?
P90은 평균에 표준편차의 1.2816배를 더한 값이며, 엑셀에서는 =NORM.INV(0.9, mean, sd)입니다. 평균 1,000, 표준편차 200이면 1,256입니다. P10은 평균에서 같은 거리만큼 아래인 744이고, P95는 1,329입니다. 이것은 입력 하나의 백분위수입니다. 여러 입력을 더한 합계의 P90은 백분위수끼리 더할 수 없으므로 시뮬레이션이 필요합니다(P50, P80, P90 참고).
정규 분포에서 음수가 나올 수 있나요?
네. 정규 분포는 양쪽으로 한없이 뻗어 있어서, 언제나 어느 정도 확률로 음수가 나옵니다. 평균이 0보다 표준편차의 약 4배 넘게 위에 있으면 그 확률은 아주 작고, 표준편차가 평균의 절반이면 2.3%입니다. 음수가 될 수 없는 값에는 분포를 0에서 절단하거나 로그정규 분포나 감마 분포를 쓰세요.
관련 글
- 가이드
어떤 확률 분포를 써야 할까요? 몬테카를로 모델의 분포 선택 가이드
질문 두 개로 알맞은 확률 분포를 찾습니다. PERT, 삼각, 정규, 로그정규, 와이블, 푸아송, 이항, 베타 등 각 분포의 용도와 엑셀 수식을 설명합니다. - 가이드
엑셀 로그정규 분포: LOGNORM.INV, 인수, 사용 시점
로그정규 분포는 양수이며 오른쪽 꼬리가 깁니다. LOGNORM.INV의 인수가 어떻게 작동하는지, 평균과 표준편차에서 그 인수를 구하는 방법, 언제 쓰는지를 설명합니다. - 공차 누적
공차 누적 분석: 최악 케이스, RSS, 몬테카를로 중 무엇을 쓸까요?
하우징과 부품 다섯 개: 최악 케이스에서는 틈새가 0.120mm까지 줄어들 수 있습니다. 틈새가 0.4mm 미만인 비율은 RSS 예측 3.29%, 몬테카를로 20,000회 시행 결과 3.21%입니다.
xellstorm은 엑셀 모델을 브라우저에서 몬테카를로 시뮬레이션하는 도구입니다. 애드인은 필요 없고, 엑셀 파일은 내 컴퓨터 밖으로 나가지 않습니다.