Excel 蒙地卡羅模擬：有無增益集的做法
Excel 模型做蒙地卡羅模擬有三種做法：RAND() 公式搭配資料表、增益集，或在 Excel 之外執行 Excel 檔案的工具。三者都會把模型重新計算數千次，不確定的輸入從範圍中抽出，再把結果當成機率來看：超出預算的機率、80% 信心水準下不會超出的成本、影響最大的輸入。
為什麼要模擬，而不只用一個數字
試算表一組輸入只給一個答案。輸入是估計值時，這個答案看不出可能差多少，而且往往偏樂觀：把最可能成本加總，忽略了成本超支比節省容易。在專案成本範例裡，基準估算（各項最可能成本之和）有 94.2% 的模擬結果會超出。最佳、基準與最差情況也解決不了：它們給三個答案，卻沒說各自有多大的可能。
1. 一般的 Excel，不需增益集：RAND() 與資料表
Excel 沒有模擬指令，但小型模型靠函數就夠了：
- 把每個不確定的輸入換成亂數抽樣。
RAND()會傳回 0 到 1 之間的均勻亂數；再用反累積分配函數轉成所需範圍內的值（公式見下表）。 - 重複計算。在一欄填入試驗編號 1 到 5,000，旁邊的下一欄、第一個編號上方一列，放入輸出儲存格的參照。從該列起向下選取這兩欄，選擇「資料」›「模擬分析」›「資料表」，在「欄輸入儲存格」（Column input cell）填入同一張工作表上任何一個空白儲存格。每一列都會重新計算整個 Excel 檔案，所以每一列都抽到新的亂數。
- 彙整該欄。用
AVERAGE、PERCENTILE.INC與COUNTIF（再除以試驗次數），算出平均值、P80 與超出目標的機率；直方圖則呈現分配的形狀。
| 輸入 | 公式 |
|---|---|
| 常態分配（平均值、標準差） | =NORM.INV(RAND(), mean, sd) |
| PERT 分配（最小、最可能、最大） | =BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max) |
| 三角分配（最小、最可能、最大），亂數放在 U | =IF(U<(ml-min)/(max-min), min+SQRT(U*(max-min)*(ml-min)), max-SQRT((1-U)*(max-min)*(max-ml))) |
| 風險事件（機率 p、成本 c） | =IF(RAND()<p, c, 0) |
這種做法行得通，也很適合學習。但用在實際模型上，限制就看得出來：
- 資料表每做一次試驗，就重新計算整個 Excel 檔案，大型模型會變慢。把計算選項設為 Automatic Except for Data Tables（自動但資料表除外），其餘時間檔案才不會卡。
RAND()每次重新計算都會重抽，所以 Excel 檔案一重新計算，結果就變，除非先貼成值。- 相關輸入、敏感度分析與報告，公式和圖表都得自己做。
2. Excel 增益集
@RISK、Crystal Ball、Analytic Solver 與 ModelRisk 等增益集，會為 Excel 加上分配函數（例如直接輸入儲存格的 PERT 函數），代為執行試驗，並產生圖表、敏感度分析與報告。它們在 Excel 內執行，每位使用者都得裝 Excel 與增益集，在許多公司就得向 IT 部門提出申請。
3. 在 Excel 之外執行 Excel 檔案的工具
第三種是自帶計算引擎的工具，讀取 .xlsx 檔案後自己執行試驗。xellstorm 就是這樣，而且直接在瀏覽器執行：
- 開啟現有的 Excel 檔案；檔案在瀏覽器分頁計算，不會上傳。相容性檢查會比對公式與 Excel 儲存的值，標出引擎無法計算的儲存格。
- 在模型圖上點選不確定的輸入並設定範圍，再點選要追蹤的結果。若 Excel 檔案已用
NORM.INV(RAND(), …)，或 @RISK、ModelRisk、Analytic Solver 的常用分配函數，支援的公式會變成輸入，參數仍連結到原本的儲存格。上述 PERT 的BETA.INV公式也能轉換：以算術運算組成的形狀引數仍保持連結，每次執行前先計算（請參閱 Excel 的 PERT 分配）。 - 執行使用亂數種子，所以同一個模型每次得到的數字都一樣；大多數 Excel 檔案中，只會重新計算試驗裡有變動的儲存格。
三種做法並排比較
| 一般 Excel | Excel 增益集 | xellstorm | |
|---|---|---|---|
| 安裝 | 不需要 | Excel 中的增益集 | 不需安裝：直接在瀏覽器執行 |
| 分配 | 以公式自行建立 | 多種，以函數提供 | 48 種，含自訂的表格分配 |
| 重複試驗 | 資料表，重新計算整個 Excel 檔案 | 內建 | 內建，在大多數 Excel 檔案中只重新計算有變動的部分 |
| 下次得到相同數字 | 否，除非貼成值 | 固定亂數種子即可 | 使用亂數種子，與 CPU 核心數無關 |
| 相關性、敏感度、報告 | 自行建立公式與圖表 | 內建 | 內建 |
如何解讀結果
- 平均值
- 所有模擬結果的平均。輸入呈偏態時，會與最可能輸入算出的結果不同。
- 百分位數（P10、P50、P80、P90）
- 有 10%、50%、80% 或 90% 的結果不超過的值。P50 即中位數；P80 與 P90 是常用來設定預算的百分位數（進一步了解百分位數）。
- 目標機率
- 符合某項條件的結果所占的比例，例如總成本高於預算。
- S 曲線
- 以結果為橫軸、累積機率為縱軸的曲線：可讀出任何百分位數，或不超過某個值的機率。
- 龍捲風圖
- 每個輸入從低百分位數移到高百分位數、其餘維持在中位數時，結果會變動多少；最寬的長條就是影響最大的輸入（龍捲風圖）。
- CVaR（最差 5% 的平均值）
- 最差 5% 結果的平均值：不只看壞情況多常發生，也看有多糟（從最差的結果決定準備金規模）。
需要多少次試驗？
多到所報告的數字不再變動為止。模擬平均值的雜訊隨試驗次數的平方根縮小：試驗次數增為四倍，雜訊減半。數千次試驗是常見的起點；P95 這類極端百分位數需要的試驗次數比平均值更多。xellstorm 能持續執行，直到平均值與百分位數穩定在所選的容許誤差內。
常見問題
蒙地卡羅模擬與情境分析相同嗎？
不同。情境分析只計算少數選定的情況，例如最佳、基準與最差，沒說各自有多大的可能。模擬則從範圍抽出數千種組合，並告訴您各個結果的可能性。
估算應該使用哪種分配？
以最小、最可能與最大值給出的專家估計，用 PERT 或三角分配。不會低於零、右側拖得很長的量，用對數常態分配。事件發生與否，用機率搭配發生時的成本。有資料時，把分配配適到資料上。
需要修改 Excel 檔案嗎？
用 xellstorm 不必改：分配設定放在 Excel 檔案旁邊，不在檔案裡，也能直接讀取已有的儲存格（例如最小／最可能／最大欄）；公式維持原樣。一般 Excel 或增益集則把範圍放在檔案本身，寫成公式或增益集自己的定義。
如何在 Excel 中建立 PERT 分配？
Excel 沒有 PERT 函數，但 PERT 分配就是縮放成從最小延伸到最大的貝他分配，所以可用 BETA.INV 抽樣：=BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max)，其中最小、最可能（ml）與最大換成儲存格參照。平均值為（最小 + 4 × 最可能 + 最大）/ 6。Excel 的 PERT 分配另將它與三角分配做比較。
不用增益集，能在 Excel 中執行蒙地卡羅模擬嗎？
可以。用 =NORM.INV(RAND(), mean, sd) 之類的公式抽出每個不確定的輸入，用資料表重複執行模型，再用 AVERAGE、PERCENTILE.INC 與 COUNTIF 彙整結果。或在 xellstorm 開啟 Excel 檔案：試驗在瀏覽器執行，不需增益集，也不需要 Excel。
相關內容
- 指南
Excel 的 PERT 分配：公式、平均值與使用時機
PERT 是從最小值到最大值的貝他分配，平均值為 (最小 + 4 × 最可能 + 最大)/6。附 Excel 的 BETA.INV 公式，以及 PERT 與三角分配的比較。 - 指南
P50、P80、P90 是什麼意思？預算該採用哪一個
P80 是 80% 的模擬結果都不會超過的成本。營建專案 6 個成本項目的 P80 加總為 2,843，但這些項目加總後的 P80 是 2,756（單位：千美元）。 - 指南
龍捲風圖與敏感度分析詳解
龍捲風圖一次只移動一個輸入，xellstorm 預設從 P10 到 P90。以一份營建估算來說，有一項風險就讓成本變動 150（單位：千美元）。 - 專案成本與時程
營建專案需要多少應變準備金？
實作範例：營建估算中各項最可能成本，有 94.2% 的模擬結果會超出。用蒙地卡羅模擬估出營建成本應變準備金。
xellstorm 是在瀏覽器中執行的 Excel 模型蒙地卡羅模擬工具：免裝增益集，Excel 檔案也不會離開您的電腦。