Excel 卜瓦松分配：計數事件，以及何時改用負二項分配
一句話說：一週、一個月或一年內，某件事發生幾次；事件一件一件發生，平均速率已知。
事件各自獨立、以已知的平均速率發生時，卜瓦松分配給出固定期間內的事件數，例如一週的客訴件數或一個月的故障次數。只要一個數字就能決定它，就是平均值。平均每週 4 件客訴時，一週完全沒有客訴的機率是 1.8%，一週有 8 件以上的機率是 5.1%。Excel 有 POISSON.DIST，但沒有反函數；隨機計數要靠一張小小的累積機率表。計數的變動比卜瓦松分配所容許的更大時，改用負二項分配。
卜瓦松分配是什麼
想像事件以穩定的平均速率，一件接一件隨機出現：來電、瑕疵、理賠、大型設備群的故障。一週內出現的次數是整數，0、1、2……，它的分配只取決於平均次數。平均值也決定各週的起伏有多大：標準差是平均值的平方根，每週 4 件客訴的標準差是 2。這類事件之間的時間是指數分配：這裡平均 1.75 天一件。
圖怎麼看：每根長條是 10,000 個模擬週中，客訴件數剛好是那個數字的占比；每個圓點是理論機率。長條和圓點吻合。
最可能出現的週，是 3 或 4 件客訴（各 19.5%）。P10 是 2，P90 是 7：十週中至少有八週落在兩者之間。
Excel 中的卜瓦松分配
POISSON.DIST(k, mean, FALSE) 得到剛好 k 件事件的機率，用 TRUE 則是至多 k 件的機率：=1-POISSON.DIST(7, 4, TRUE) 就是 8 件以上的機率 5.1%。沒有 POISSON.INV，所以隨機計數要多一個步驟。檔案在 Tables 工作表列出 0 到 99 件事件的累積機率，再計算其中有幾個低於亂數 u：這個個數，就是累積機率達到 u 的最小 k，正是一次卜瓦松抽樣。在單一儲存格裡是 =COUNTIF(Tables!B$3:B$102,"<"&RAND())。
| 儲存格 | 公式 | 值 |
|---|---|---|
| 亂數 u (I12) | =RAND() | |
| 抽樣值 (J12) | =COUNTIF(Tables!B$3:B$102, | |
| 單一儲存格的抽樣 | =COUNTIF(Tables!B$3:B$102, | |
| 平均值 (K12) | =D12 | 4 |
| P10 (L12) | =COUNTIF(Tables!B$3:B$102, | 2 |
| P90 (M12) | =COUNTIF(Tables!B$3:B$102, | 7 |
核對數字：理論值，對照 Excel 與 xellstorm 各 10,000 個抽樣值
| 理論值 | Excel, RAND() | xellstorm | |
|---|---|---|---|
| 平均值 | 4 | 3.99 | 4.00 |
| 標準差 | 2 | 1.98 | 2.00 |
| P10 | 2 | 2 | 2 |
| P50（中位數） | 4 | 4 | 4 |
| P90 | 7 | 7 | 7 |
Excel 用 RAND() 完全隨機抽樣；xellstorm 把抽樣值平均分散在整個範圍（拉丁超立方抽樣），所以統計量更貼近理論值。
負二項分配：計數的變動比卜瓦松分配更大時
實際的計數常比卜瓦松分配更不平均：需求有忙月與閒月，瑕疵成批出現，壞天氣時理賠增加。看一下歷史資料：若計數的起伏比卜瓦松分配所容許的大得多（變異數，也就是標準差的平方，明顯大於平均值），卜瓦松分配就低估了閒的期間與忙的期間。負二項分配多一個參數，專門處理這份額外的分散。平均值同樣是 4，但變異數為 8 時：
圖怎麼看：每條線連起一週內發生 0、1、2 件以上事件的機率。負二項分配在兩端都比較高：沒有事件的週更多，事件很多的週也更多。
| 卜瓦松分配 | 負二項分配 | |
|---|---|---|
| 變異數 | 4 | 8 |
| 完全沒有的機率 | 1.8% | 6.3% |
| 8 件以上的機率 | 5.1% | 11.3% |
| P10 – P90 | 2 – 7 | 1 – 8 |
| 儲存格 | 公式 | 值 |
|---|---|---|
| 亂數 u (I13) | =RAND() | |
| 抽樣值 (J13) | =COUNTIF(Tables!C$3:C$102, | |
| 單一儲存格的抽樣 | =COUNTIF(Tables!C$3:C$102, | |
| 平均值 (K13) | =D13*(1-F13)/F13 | 4 |
| P10 (L13) | =COUNTIF(Tables!C$3:C$102, | 1 |
| P90 (M13) | =COUNTIF(Tables!C$3:C$102, | 8 |
在 Excel 中，NEGBINOM.DIST(k, n, p, TRUE) 得到它的累積機率，同樣的查表做法也能抽樣：=COUNTIF(Tables!C$3:C$102,"<"&RAND())。它的參數是 n 與 p（教科書的定義是第 n 次成功之前的失敗次數）。由平均值與變異數換算：p = 平均值 / 變異數，n = 平均值² / (變異數 − 平均值)，這裡 p = 0.5，n = 4。再訂購點範例就因為這個原因，從負二項分配抽取每月需求。
何時用卜瓦松、負二項或二項分配
- 卜瓦松分配用於以穩定速率發生的獨立事件計數，實務上沒有上限：到達、來電、瑕疵、大量機件的故障。
- 負二項分配用於同類計數，但歷史資料的變動大於平均值：需求、理賠、成群出現的瑕疵。
- 二項分配用於機會次數固定的情形：一批 50 件中的不良品、200 位客戶中接受報價的人數。
在 xellstorm 設定卜瓦松分配
- 把儲存格設為輸入，在「分配」步驟選卜瓦松分配，輸入平均值；或選負二項分配，輸入 n 與 p。
- 有過往計數時，選取該輸入，用側邊面板的「由資料」：整數資料會把卜瓦松與負二項分配，連同連續分配的形狀一併配適並排名。計數的變動不超過平均值時，會略過負二項分配，並建議用卜瓦松分配。
增益集模型可轉換 RiskPoisson、VosePoisson 與 PsiPoisson，以及 RiskNegbin、VoseNegBin 與 PsiNegBinomial。
常見問題
Excel 怎麼產生卜瓦松分配的亂數？
Excel 沒有卜瓦松反函數。在一欄列出 k = 0、1、2……，旁邊放 POISSON.DIST(k, mean, TRUE)，再計算這些累積機率中有幾個低於 RAND()：表在 B3:B102 時，公式是 =COUNTIF(Tables!B$3:B$102,"<"&RAND())。表要夠長，讓最後一個機率在顯示的精度下是 1（平均值 4 的話，30 列就夠）。
POISSON.DIST 用 TRUE 與 FALSE 有什麼不同？
用 FALSE 回傳剛好 k 件事件的機率；用 TRUE 則是 k 件以下的機率。平均值為 4 時：剛好 4 件的機率是 19.5%，8 件以上的機率是 1 減去 7 件以下的累積機率，即 5.1%。
該用卜瓦松還是負二項分配？
把計數的變異數與平均值比一比。大致相等：卜瓦松分配。明顯較大：負二項分配，多出來的參數能反映這份分散。平均值為 4 時，變異數 8 的負二項分配，有 8 件以上的機率是 11.3%，卜瓦松分配則是 5.1%。
卜瓦松分配的平均值可以是小數嗎？
可以。平均值是平均速率，例如每月 0.3 次故障；產生的計數則一定是整數。平均值小於 1 時，大多是 0，偶爾出現 1 或 2。
相關內容
- 指南
機率分配怎麼選？蒙地卡羅模型的分配選擇指南
兩個問題找出合適的機率分配：PERT、三角、常態、對數常態、韋伯、卜瓦松、二項、貝他等分配，並附各自的用途與 Excel 公式。 - 指南
Excel 二項分配與白努利分配：發生與否，以及 n 次中發生幾次
白努利分配是單一個發生與否的事件，例如風險會不會發生；二項分配則計算 n 次嘗試中「是」的次數。附 Excel 公式、BINOM.DIST、BINOM.INV 與風險登記冊。 - 庫存政策
哪個再訂購點能在成本與缺貨之間取得平衡？
把再訂購點從 180 件提高到 250 件，缺貨年度的機率從 28.0% 降到 5.1%，平均年成本 2,133 則增加 345。
xellstorm 是在瀏覽器中執行的 Excel 模型蒙地卡羅模擬工具：免裝增益集，Excel 檔案也不會離開您的電腦。