Excel 對數常態分配：LOGNORM.INV、參數與使用時機
一句話說：通常不大不小，偶爾卻會變成好幾倍的量，例如一次維修的費用。它不會低於零。
對數常態分配描述不可能低於零、上側有長尾的量，例如成本、損失或工期；取對數後，它就是常態分配。在 Excel 中，=LOGNORM.INV(RAND(), LN(median), s) 可以從中抽樣。單件理賠的修理費用，中位數 2,000（美元）、s = 0.8 時，平均值為 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% 的費用高於平均理賠。以中位數或最可能成本編的預算，會低於平均值。
Excel 中的對數常態分配
LOGNORM.INV(probability, mean, standard_dev) 很容易看錯：它的 mean 與 standard_dev，是數值取對數後的平均值與標準差，不是數值本身的。上面的理賠例子，兩者分別是 LN(2,000) = 7.60 與 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 |
核對數字：理論值，對照 Excel 與 xellstorm 各 10,000 個抽樣值
| 理論值 | Excel, 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 |
Excel 用 RAND() 完全隨機抽樣；xellstorm 把抽樣值平均分散在整個範圍（拉丁超立方抽樣），所以統計量更貼近理論值。
若已知的是數值本身的平均值與標準差，要先換算：s = √(ln(1 + (sd / mean)²))，中位數 = mean / √(1 + (sd / mean)²)。平均值 3,000、標準差 1,500 時，s = 0.4724，中位數為 2,683。在 Excel 裡是 =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，選對數常態分配，xellstorm 會算出 s 與中位數。有過往數值時，「由資料」會以 loc 0 配適對數常態分配，並和其他形狀比較。
「由檔案匯入」會把 =LOGNORM.INV(RAND(), mu, sigma) 的儲存格轉成對數常態輸入，也會轉換增益集的函數，如 RiskLognorm（數值本身的平均值與標準差）、RiskLognorm2（取對數後的平均值與標準差）、VoseLognormal 與 PsiLogNormal。
常見問題
Excel 怎麼產生對數常態分配的亂數？
用 =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 + (sd / mean)²))，μ = ln(mean) − σ² / 2。
為什麼對數常態分配的平均值高於中位數？
因為長尾伸向右側，而值又不可能低於零：偶爾出現的極大值，會把平均拉高。平均值是中位數 × es²/2，所以 s 愈大，差距愈大：s = 0.8、中位數 2,000 時，平均值是 2,754。
該用對數常態還是常態分配？
圍繞目標值對稱變動、又離零很遠的量，用常態分配。上側比下側長的正值，如成本、損失、工期、價格，用對數常態分配。標準差相對於平均值很小時（約小於十分之一），兩者幾乎一樣。
相關內容
- 指南
機率分配怎麼選？蒙地卡羅模型的分配選擇指南
兩個問題找出合適的機率分配：PERT、三角、常態、對數常態、韋伯、卜瓦松、二項、貝他等分配，並附各自的用途與 Excel 公式。 - 指南
Excel 伽瑪分配：等待時間、總和與 GAMMA.INV
伽瑪分配描述恆為正、右偏的量，例如數個步驟的總時間。說明形狀參數與尺度參數、Excel 的 GAMMA.INV 與 GAMMA.DIST，以及伽瑪與對數常態分配的比較。 - 配送前置時間
配送準時到貨的機率是多少？
用少量合成配送紀錄配適機率分配，比較準時到貨機率，並了解為什麼增加模擬次數也無法解決樣本太少的問題。附 Excel 檔案。
xellstorm 是在瀏覽器中執行的 Excel 模型蒙地卡羅模擬工具：免裝增益集，Excel 檔案也不會離開您的電腦。