엑셀 이항 분포와 베르누이 분포: 예·아니요 사건과 n번 중 횟수
한마디로: 베르누이 분포는 “일어날까?”에 1 또는 0으로 답하고, 이항 분포는 “n번 중 몇 번일까?”에 답합니다.
베르누이 분포는 예·아니요로 갈리는 사건 하나입니다. 리스크가 확률 p로 일어나거나(1) 일어나지 않는(0) 경우이며, 엑셀에서는 =IF(RAND() < p, 1, 0)으로 뽑습니다. 이항 분포는 같은 확률로 독립적으로 n번 시도했을 때 ‘예’의 횟수를 세며, =BINOM.INV(n, p, RAND())로 뽑습니다. 불량률이 4%인 50개 묶음에서는 불량품이 평균 2개이고, 불량이 하나도 없는 묶음의 확률은 13.0%, 5개 이상인 묶음의 확률은 4.9%입니다.
베르누이 분포: 일어날까요, 아닐까요?
모델의 불확실성 중에는 금액이 아니라 사건인 것이 많습니다. 허가가 지연되는지 아닌지, 공급업체가 실패하는지 아닌지, 관리대장의 리스크가 발생하는지 아닌지입니다. 베르누이 분포는 확률 p로 1이고 아니면 0입니다. 여기에 영향액을 곱하면 시행마다의 리스크 비용이 됩니다. =IF(RAND() < 0.3, 1, 0) * 500은 시행의 약 30%에서 500이고 나머지에서는 0입니다.
| 셀 | 수식 | 값 |
|---|---|---|
| 난수 u(I14) | =RAND() | |
| 추출값(J14) | =IF(I14<D14, | |
| 셀 하나로 추출 | =IF(RAND()<D14, | |
| 평균(K14) | =D14 | 0.3 |
| P10 (L14) | =BINOM.INV(1, | 0 |
| P90 (M14) | =BINOM.INV(1, | 1 |
평균은 p이므로 그 리스크의 기대 비용은 0.3 × 500 = 150입니다. 그러나 150의 비용이 드는 시행은 하나도 없습니다. 각 시행의 비용은 0이거나 500입니다. 확률 × 영향액을 고정된 숫자로 모델에 넣으면 바로 이 점이 감춰지며, 그래서 이런 리스크가 모인 관리대장의 예비비를 정하려면 시뮬레이션이 필요합니다(리스크 관리대장 예제 참고). 이 행의 xellstorm 10,000회 추출에서 리스크는 시행의 30.0%에서 발생했습니다.
이항 분포: n번 중 몇 번일까요?
같은 ‘예·아니요’ 확률을 n번 독립적으로 반복하고 ‘예’의 수를 세면 그 횟수가 이항 분포를 따릅니다. 한 묶음의 불량품 수, 200명 중 제안을 수락하는 고객 수, 20일 중 비가 오는 날 수가 그런 예입니다. 평균은 n × p, 표준편차는 √(n × p × (1 − p))입니다. p = 0.04인 50개 묶음에서는 평균이 2, 표준편차가 1.39입니다. 베르누이 분포는 n = 1인 이항 분포입니다.
차트 읽는 법: 막대는 시뮬레이션한 10,000개 묶음 중 불량품이 그 개수인 묶음의 비중이고, 점은 이론 확률입니다.
| 셀 | 수식 | 값 |
|---|---|---|
| 난수 u(I15) | =RAND() | |
| 추출값(J15) | =BINOM.INV(D15, | |
| 셀 하나로 추출 | =BINOM.INV(D15, | |
| 평균(K15) | =D15*F15 | 2 |
| P10 (L15) | =BINOM.INV(D15, | 0 |
| P90 (M15) | =BINOM.INV(D15, | 4 |
수치 확인: 이론값과 엑셀, xellstorm의 10,000개 추출값 비교
| 이론값 | 엑셀, RAND() | xellstorm | |
|---|---|---|---|
| 평균 | 2 | 2.01 | 2.00 |
| 표준편차 | 1.39 | 1.40 | 1.39 |
| P10 | 0 | 0 | 0 |
| P50(중앙값) | 2 | 2 | 2 |
| P90 | 4 | 4 | 4 |
엑셀은 RAND()로 완전히 무작위하게 뽑고, xellstorm은 추출값을 범위 전체에 고르게 퍼뜨립니다(라틴 하이퍼큐브 샘플링). 그래서 통계값이 이론값에 더 가깝게 나옵니다.
엑셀의 이항 분포
BINOM.DIST(k, n, p, FALSE)는 정확히 k개일 확률입니다. 묶음에서 불량품이 2개일 확률은 27.6%입니다.TRUE를 쓰면 k개 이하일 확률이므로, 5개 이상일 확률은=1-BINOM.DIST(4, 50, 0.04, TRUE)이고 값은 4.9%입니다.BINOM.INV(n, p, probability)는 반대로 묻습니다. 그 횟수 이하가 나올 확률이 주어진 확률 이상이 되는 가장 작은 횟수, 즉 백분위수를 구합니다.RAND()를 쓰면 난수 횟수를 뽑습니다:=BINOM.INV(D15,F15,RAND()). 인수 순서에 주의하세요.NORM.INV와 달리 확률이 마지막에 옵니다. 예전 이름은CRITBINOM입니다.
시도 횟수가 많고 확률이 작으면 이항 분포는 평균이 같은 푸아송 분포에 가까워집니다. 불량이 하나도 없는 묶음의 확률은 이항 분포에서 13.0%, 평균 2인 푸아송 분포에서 13.5%입니다.
xellstorm에서 베르누이 분포와 이항 분포 입력하는 법
- 셀을 입력으로 만들고 분포 단계에서 베르누이 분포를 고른 뒤 p를 입력하거나, 이항 분포를 고르고 n과 p를 입력합니다.
- 리스크 이벤트라면 영향액을 별도의 셀에 두고(고정된 숫자나 PERT 같은 다른 입력) 엑셀 파일에서 곱합니다. 그러면 리스크의 발생과 크기가 분리되고, 민감도 분석이 둘 중 어느 쪽이 결과를 좌우하는지 보여 줍니다.
- 과거의 0/1 결과가 있으면 사이드 패널의 “데이터로” 탭에서 베르누이 분포를 적합합니다. n을 아는 과거 횟수가 있으면 “시도 횟수를 아는 이항 분포” 아래에 n을 입력하고 다시 적합합니다.
“엑셀 파일에서 가져오기”는 =IF(RAND() < p, 1, 0)을 베르누이 분포 입력으로, =BINOM.INV(n, p, RAND())를 이항 분포 입력으로 변환합니다. 애드인 모델은 RiskBernoulli, VoseBernoulli, PsiBernoulli, RiskBinomial, VoseBinomial, PsiBinomial에서 변환합니다.
자주 묻는 질문
엑셀에서 리스크가 발생하는지는 어떻게 시뮬레이션하나요?
=IF(RAND() < p, 1, 0)을 씁니다. 확률 p로 1을 돌려주며, 여기에 리스크의 영향액을 곱합니다. 다시 계산을 많이 반복하면 1의 비중이 p에 가까워집니다. 리스크마다 별도의 RAND()를 써야 리스크가 서로 독립적으로 발생합니다.
베르누이 분포와 이항 분포는 무엇이 다른가요?
베르누이 분포는 시도 한 번이고 결과는 0 또는 1입니다. 이항 분포는 같은 확률로 독립적인 시도를 n번 하고, 결과는 그중 1이 나온 횟수로 0부터 n까지입니다. 베르누이 분포는 n = 1인 이항 분포입니다.
엑셀에서 이항 난수는 어떻게 만드나요?
=BINOM.INV(n, p, RAND())를 씁니다. 불량률이 4%인 50개 묶음이라면 =BINOM.INV(50, 0.04, RAND())입니다. 다시 계산할 때마다 0부터 n까지의 새 정수가 나오며, 그 평균은 n × p = 2에 가까워집니다.
이항 분포와 푸아송 분포 중 무엇을 써야 하나요?
기회의 횟수 n이 정해져 있고 기회마다 확률이 같으면 이항 분포를 씁니다. 횟수는 n을 넘을 수 없습니다. 사건이 한 기간에 평균 비율로 몇 번이든 일어날 수 있으면 푸아송 분포를 씁니다. n이 크고 p가 작으면 두 분포의 답은 거의 같습니다.
관련 글
- 가이드
어떤 확률 분포를 써야 할까요? 몬테카를로 모델의 분포 선택 가이드
질문 두 개로 알맞은 확률 분포를 찾습니다. PERT, 삼각, 정규, 로그정규, 와이블, 푸아송, 이항, 베타 등 각 분포의 용도와 엑셀 수식을 설명합니다. - 리스크 관리대장
리스크 관리대장에는 예비비가 얼마나 필요할까요?
확률 × 최빈 영향의 합계 291, 시뮬레이션 평균 손실 359, 최악 5% 평균 1,436(천 달러 단위). 예비비는 최악의 경우를 기준으로 정합니다. - 가이드
엑셀 푸아송 분포: 사건 횟수와 음이항 분포를 쓰는 경우
푸아송(포아송) 분포는 알려진 평균 비율로 일어나는 사건을 한 기간 동안 세는 분포입니다. POISSON.DIST, 역함수 없이 엑셀에서 푸아송 난수 뽑기, 횟수가 더 들쭉날쭉할 때 쓰는 음이항 분포를 설명합니다.
xellstorm은 엑셀 모델을 브라우저에서 몬테카를로 시뮬레이션하는 도구입니다. 애드인은 필요 없고, 엑셀 파일은 내 컴퓨터 밖으로 나가지 않습니다.