엑셀 로그정규 분포: LOGNORM.INV, 인수, 사용 시점
한마디로: 보통은 적당한 수준이지만 가끔 몇 배나 크게 나오는 값입니다. 수리 비용이 그런 예이고, 0 아래로는 내려가지 않습니다.
로그정규 분포는 0 아래로 내려갈 수 없고 위쪽 꼬리가 긴 값, 즉 비용, 손실, 기간 같은 값을 나타냅니다. 이 값의 로그가 정규 분포를 따릅니다. 엑셀에서는 =LOGNORM.INV(RAND(), LN(median), s)로 값을 뽑습니다. 중앙값이 2,000(USD)이고 s = 0.8인 보험금 청구 1건의 수리비는 평균이 2,754이고 중앙값보다 높습니다. P90은 5,576이며 중앙값의 2.8배입니다.
로그정규 분포는 무엇인가
정규 분포는 작고 독립적인 효과를 더하지만, 로그정규 분포는 곱합니다. 수리가 예상보다 20% 더 복잡하고, 필요한 부품이 30% 더 비싸고, 작업이 10% 더 오래 걸리는 식입니다. 값은 항상 양수이고, 대부분 낮은 쪽에 모여 있으며, 가느다란 꼬리가 오른쪽으로 멀리 뻗습니다. 일부 청구는 일반적인 청구의 몇 배나 되는 비용이 듭니다.
차트 읽는 법: 막대는 시뮬레이션한 10,000개 값 중 그 구간에 들어간 개수이고, 선은 이론 곡선입니다. 막대가 높을수록 그 값이 자주 나옵니다. 오른쪽의 길고 낮은 부분이 드물게 나오는 비싼 청구입니다.
숫자 두 개로 정해집니다. 중앙값은 대푯값으로, 결과의 절반이 그 아래에 있습니다. 퍼짐 s는 꼬리가 얼마나 멀리 뻗는지를 정합니다. 0.25 정도면 값이 중앙값 가까이에 머물고, 1 정도면 일부는 몇 배로 커집니다. (엄밀히는 s는 값의 로그의 표준편차입니다.) 평균, 봉우리, 백분위수는 모두 이 두 숫자에서 나옵니다.
수식: 평균, 표준편차, 봉우리, 백분위수
| 구하는 값 | 수식과 예시 |
|---|---|
| 평균 | 중앙값 × es²/2 = 2,000 × e0.32 = 2,754 |
| 표준편차 | 평균 × √(es² − 1) = 2,608 |
| 봉우리(최빈값) | 중앙값 × e−s² = 1,055 |
| P10과 P90 | 중앙값 × e∓1.2816 s = 717, 5,576 |
봉우리 < 중앙값 < 평균의 순서는 오른쪽으로 치우친 값의 전형적인 모습입니다. 평균은 꼬리에 끌려 올라가서, 시뮬레이션한 청구 중 평균 청구액보다 비용이 큰 것은 34.5%뿐입니다. 중앙값이나 최빈 비용으로 짠 예산은 평균에 못 미칩니다.
엑셀의 로그정규 분포
LOGNORM.INV(probability, mean, standard_dev)는 잘못 읽기 쉽습니다. 인수로 넣는 평균과 표준편차는 값 자체가 아니라 값의 로그의 평균과 표준편차입니다. 위 청구 사례에서는 LN(2,000) = 7.60이고 s = 0.8입니다. 비용 자체의 평균과 표준편차를 넣는 것이 이 함수에서 가장 흔한 실수이며, 결과가 아무 의미도 없게 됩니다. 그래서 이 엑셀 파일은 중앙값을 셀에 두고 LN(median)을 넘깁니다. 셀 하나에 합치면 =LOGNORM.INV(RAND(),LN(D6),F6)입니다.
| 셀 | 수식 | 값 |
|---|---|---|
| 난수 u(I6) | =RAND() | |
| 추출값(J6) | =LOGNORM.INV(I6, | |
| 셀 하나로 추출 | =LOGNORM.INV(RAND(), | |
| 평균(K6) | =D6*EXP(F6^2/2) | 2,754 |
| P10 (L6) | =LOGNORM.INV(0.1, | 717 |
| P90 (M6) | =LOGNORM.INV(0.9, | 5,576 |
수치 확인: 이론값과 엑셀, xellstorm의 10,000개 추출값 비교
| 이론값 | 엑셀, RAND() | xellstorm | |
|---|---|---|---|
| 평균 | 2,754 | 2,797 | 2,754 |
| 표준편차 | 2,608 | 2,659 | 2,603 |
| P10 | 717 | 734 | 717 |
| P50(중앙값) | 2,000 | 2,020 | 2,000 |
| P90 | 5,576 | 5,642 | 5,575 |
엑셀은 RAND()로 완전히 무작위하게 뽑고, xellstorm은 추출값을 범위 전체에 고르게 퍼뜨립니다(라틴 하이퍼큐브 샘플링). 그래서 통계값이 이론값에 더 가깝게 나옵니다.
대신 값의 평균과 표준편차를 알고 있다면 먼저 변환합니다. s = √(ln(1 + (표준편차 / 평균)²)), 중앙값 = 평균 / √(1 + (표준편차 / 평균)²)입니다. 평균 3,000, 표준편차 1,500이면 s = 0.4724, 중앙값은 2,683입니다. 엑셀에서는 =SQRT(LN(1+(sd/mean)^2))와 =mean/SQRT(1+(sd/mean)^2)입니다. LOGNORM.DIST(x, LN(median), s, TRUE)는 x 미만일 확률을 돌려주고, 예전 이름인 LOGINV는 LOGNORM.INV와 똑같이 작동합니다.
로그정규 분포를 쓰는 경우
- 비용, 손실, 청구 금액은 음수가 될 수 없고 가끔 대푯값의 몇 배까지 커집니다.
- 기간과 리드 타임은 보통보다 훨씬 빨리 끝나는 일은 드물지만 훨씬 오래 걸릴 수는 있습니다. 과거 배송에 로그정규 분포를 적합하는 배송 리드 타임 예제가 그런 경우입니다.
- 가격과 성장 계수는 퍼센트 단위로 변합니다. 여러 기간에 걸쳐 무작위 퍼센트만큼 오르내리는 가격은 결국 로그정규 분포를 따릅니다.
다른 분포와 비교하면 이렇습니다. 정규 분포는 대칭이고 음수가 나올 수 있습니다. PERT 분포나 삼각 분포에는 확실한 최댓값이 있어서 전문가가 정한 범위에는 맞지만, 몇 배로 초과할 수 있는 값에는 맞지 않습니다. 감마 분포는 모양이 같은 종류이면서 꼬리가 더 얇습니다. 데이터가 있으면 로그정규 분포와 감마 분포를 둘 다 적합해 비교하세요.
xellstorm에서 로그정규 분포 입력하는 법
- 셀을 입력으로 만들고 분포 단계에서 로그정규 분포를 고릅니다.
- 필드는 scipy를 따릅니다. s는 로그의 퍼짐(형상 모수), scale은 중앙값, loc은 이동으로, 값이 0보다 위에서 시작하는 경우가 아니면 0입니다. 앞의 청구 사례에서는 s 0.8, scale 2000, loc 0입니다. 평균 열과 P10 – P90 열에서 입력한 값이 어떤 결과가 되는지 확인합니다.
- 백분위수로 생각한다면 입력을 선택하고 사이드 패널의 “추정치로” 탭을 씁니다. P10, P50, P90을 입력하고 로그정규 분포를 고르면 s와 중앙값을 구해 줍니다. 과거 값이 있으면 “데이터로” 탭에서 loc 0인 로그정규 분포를 적합하고 다른 모양과 비교합니다.
“엑셀 파일에서 가져오기”는 =LOGNORM.INV(RAND(), mu, sigma) 셀과 RiskLognorm(값의 평균과 표준편차), RiskLognorm2(값의 로그의 평균과 표준편차), VoseLognormal, PsiLogNormal 같은 애드인 함수를 로그정규 분포 입력으로 변환합니다.
자주 묻는 질문
엑셀에서 로그정규 난수는 어떻게 만드나요?
=LOGNORM.INV(RAND(), LN(median), s)를 씁니다. median은 대푯값이고 s는 그 로그의 표준편차입니다. 중앙값 2,000, s = 0.8이면 =LOGNORM.INV(RAND(), LN(2000), 0.8)입니다. 다시 계산할 때마다 새 값이 나옵니다.
LOGNORM.INV의 평균과 표준편차는 무엇인가요?
값 자체가 아니라 ln(x)의 평균과 표준편차이며, 흔히 μ와 σ로 씁니다. μ는 중앙값의 로그로, LN(2,000) = 7.60입니다. 값의 평균과 표준편차에서 시작한다면 먼저 변환합니다. σ = √(ln(1 + (표준편차 / 평균)²)), μ = ln(평균) − σ² / 2입니다.
로그정규 분포의 평균이 중앙값보다 높은 이유는 무엇인가요?
꼬리가 오른쪽으로 멀리 뻗는데 값은 0 아래로 내려갈 수 없기 때문입니다. 가끔 나오는 아주 큰 값이 평균을 끌어올립니다. 평균은 중앙값 × es²/2이므로 퍼짐이 클수록 차이가 커집니다. s = 0.8이면 중앙값이 2,000일 때 평균은 2,754입니다.
로그정규 분포와 정규 분포 중 무엇을 써야 하나요?
목표값 주위에서 대칭으로 변하고 0에서 멀리 떨어진 값에는 정규 분포를 씁니다. 위쪽이 아래쪽보다 긴 양수(비용, 손실, 기간, 가격)에는 로그정규 분포를 씁니다. 표준편차가 평균에 비해 작으면(대략 평균의 10분의 1 미만) 두 분포는 거의 같습니다.
관련 글
- 가이드
어떤 확률 분포를 써야 할까요? 몬테카를로 모델의 분포 선택 가이드
질문 두 개로 알맞은 확률 분포를 찾습니다. PERT, 삼각, 정규, 로그정규, 와이블, 푸아송, 이항, 베타 등 각 분포의 용도와 엑셀 수식을 설명합니다. - 가이드
엑셀 감마 분포: 대기 시간, 합계, GAMMA.INV
감마 분포는 여러 단계의 합계 시간처럼 양수이고 오른쪽으로 치우친 값을 나타냅니다. 형상 모수와 척도 모수, 엑셀의 GAMMA.INV와 GAMMA.DIST, 감마 분포와 로그정규 분포의 비교를 설명합니다. - 배송 리드 타임
배송이 약속한 시간 안에 도착할 확률은 얼마일까요?
설명을 위해 만든 소규모 가상 배송 이력에 분포를 적합하고 정시 배송 확률을 비교합니다. 시뮬레이션 시행을 늘려도 소표본의 한계가 해결되지 않는 이유를 살펴봅니다. 엑셀 파일을 포함합니다.
xellstorm은 엑셀 모델을 브라우저에서 몬테카를로 시뮬레이션하는 도구입니다. 애드인은 필요 없고, 엑셀 파일은 내 컴퓨터 밖으로 나가지 않습니다.