Excelで三角分布の乱数を作る：数式、平均、PERT分布との違い
ひとことで言うと：低い値、最も可能性の高い値、高い値の3つを、確率に変える最も簡単な方法です。確率は最も可能性の高い値まで直線で上がり、高い値に向かって直線で下がります。
三角分布は、3点見積もりを、最小値から最頻値まで直線的に上がり、最大値まで直線的に下がる確率に変えます。平均は（最小＋最頻＋最大）/ 3で、コストを80、100、140（USD千）と見積もると106.7、P90は124.5になります。Excelに三角分布の関数はありませんが、RAND() と SQRT を使った1つの数式で値を引けます。
三角分布とは
3つの数値で決まります。これより下では何も起きない最小値、これより上では何も起きない最大値、そして密度が山になる最頻値です。3点の間は2本の直線で結ばれるので、専門家の低い値・最も可能性の高い値・高い値から結果の全範囲を作る、最も簡単な方法です。
数式：平均、標準偏差、中央値
| 量 | 数式と計算例 |
|---|---|
| 平均 | (min + ml + max) / 3 = 320 / 3 = 106.7 |
| 標準偏差 | √((min² + ml² + max² − min × ml − min × max − ml × max) / 18) = 12.5 |
| 最頻値を下回る確率 | (ml − min) / (max − min) = 20/60 = 33.3% |
| 中央値（P50） | 105.4 |
最頻値が最小値に近いので、長い側は上です。結果の3分の2が最も可能性の高いコストを上回り、平均の106.7は、それよりかなり上にあります。最も可能性の高い値を足し合わせた予算が、たいてい低すぎるのはこのためです。
Excelでの三角分布
Excelの RAND() は0〜1の乱数uを返し、数式がそれを三角分布の値に変えます。uが小さければ最小値に近い値、大きければ最大値に近い値になり、範囲のどの部分にも、公平な割合で引きが入るようになっています。uが、最頻値より下の部分の割合より小さいときは min + √(u × (max − min) × (ml − min))、そうでなければ max − √((1 − u) × (max − min) × (max − ml)) です。数式にuが2回出てくるので、uは別のセルに置く必要があります。RAND() を2か所に書くと別々の数値になり、分布が正しくなくなります。Excelファイルでは、uがI7、引いた値がJ7にあります。
| セル | 数式 | 値 |
|---|---|---|
| 乱数 u（I7） | =RAND() | |
| 引いた値（J7） | =IF(I7<(F7-D7)/(H7-D7), | |
| 平均（K7） | =(D7+F7+H7)/3 | 106.7 |
| P10 (L7) | =IF(0.1<(F7-D7)/(H7-D7), | 91.0 |
| P90 (M7) | =IF(0.9<(F7-D7)/(H7-D7), | 124.5 |
数値の確認：理論値と、Excelとxellstormがそれぞれ引いた10,000個の値の比較
| 理論値 | Excel（RAND()） | xellstorm | |
|---|---|---|---|
| 平均 | 106.7 | 106.7 | 106.7 |
| 標準偏差 | 12.5 | 12.6 | 12.5 |
| P10 | 91.0 | 90.6 | 91.0 |
| P50（中央値） | 105.4 | 105.6 | 105.4 |
| P90 | 124.5 | 124.7 | 124.5 |
Excelは RAND() で完全にランダムに引きます。xellstormは範囲全体に均等に散らして引く（ラテンハイパーキューブサンプリング）ので、統計値が理論値に近くなります。
uを固定した確率に置き換えると、シミュレーションなしでパーセンタイルが得られます。0.9ならP90の124.5です。表の2組の引きは、どちらも理論値を再現しています。
三角分布とPERT分布：同じ3つの数値でも、限界値への重みが違う
PERT分布も同じ最小値、最頻値、最大値を使いますが、その間になめらかな曲線を描きます。Excelファイルには両方があり、7行目と8行目です。
グラフの見方：線が高いほど、その下の値が起きやすくなります。どちらの形も、最小値から始まり、最頻値で山になり、最大値で終わります。三角分布は、両端の近くでも線が高いままです。
| 三角分布 | PERT | |
|---|---|---|
| 平均 | 106.7 | 103.3 |
| 標準偏差 | 12.5 | 11.1 |
| P10 | 91.0 | 89.2 |
| P90 | 124.5 | 118.7 |
| 134を超える確率 | 1.5% | 0.1% |
三角分布は、限界値の付近に重みが多く残ります。134を超えるコストになる確率は、三角分布で1.5%、PERT分布では0.1%です。平均は最頻値を4回ではなく1回しか数えないので、偏った見積もりでは高くなり（106.7、PERT分布は103.3）、P90も高くなります（124.5、PERT分布は118.7）。どちらが正しいということはなく、同じ3つの数値を違う見方で読んだものです。
- 三角分布を選ぶのは、限界値に近い値が現実的な場合や、同じ見積もりからより慎重な広がりを取りたい場合です。
- PERT分布を選ぶのは、最も可能性の高い値を最も信頼していて、限界値がまず届かない境界である場合です。
- どちらも選ばないのは、低い値と高い値が限界値ではなく、10回に1回のケース（P10とP90）の場合です。最小・最大として使うと、その外側の20%の結果が抜け落ちます。代わりにパーセンタイルに分布を当てはめるか、パーセンタイルによる三角分布を選びます。
xellstormで三角分布を入力する
- セルを入力にして、「分布」ステップで三角分布を選びます。最小、最頻、最大を入力すると、「平均」と「P10 – P90」の列が入力に合わせて更新されます。
- 低い値と高い値がP10とP90なら、代わりにパーセンタイルによる三角分布を選び、下端と上端に入力して、下端%を10、上端%を90にします。または、入力を選んで、サイドパネルの「見積もりから」で形を当てはめます。どのパーセンタイルの組も三角分布にぴったり合うとは限りません。最も近い分布がどれだけずれるかは、パネルに表示されます。
- 同じ見積もりの読み方を比べるには、入力をPERTに変えるシナリオを追加します。シナリオは基準ケースと同じ乱数で実行するので、差は分布の形だけから生まれます。
アドイン向けのモデルは、RiskTriang、VoseTriangle、PsiTriangular から変換できます。RiskTrigen は、パーセンタイルによる三角分布に変換します。
よくある質問
Excelで三角分布の乱数を作るには？
1つのセルに乱数 =RAND() を置き、別のセルで逆関数の式を使って値に変換します。乱数がI7、最小がD7、最頻値がF7、最大がH7にあるとき、数式は =IF(I7<(F7-D7)/(H7-D7),D7+SQRT(I7*(H7-D7)*(F7-D7)),H7-SQRT((1-I7)*(H7-D7)*(H7-F7))) です。このページのExcelファイルに入っているので、そのままコピーできます。
三角分布の平均はいくつですか？
平均は（最小＋最頻＋最大）/ 3です。80、100、140の見積もりでは、320 / 3 = 106.7になります。同じ数値のPERT分布は、平均が（最小＋4×最頻＋最大）/ 6 = 103.3です。
三角分布のP90はどう求めますか？
乱数の代わりに0.9を入れた、引きの数式を使います。=IF(0.9<(F7-D7)/(H7-D7),D7+SQRT(0.9*(H7-D7)*(F7-D7)),H7-SQRT((1-0.9)*(H7-D7)*(H7-F7))) は、80、100、140の見積もりで124.5になります。これは1つの項目のP90です。複数の項目の合計は、パーセンタイルが足し合わせられないので、シミュレーションで求めます。
三角分布とPERT分布は、どちらが保守的ですか？
同じ3つの数値なら、三角分布の方が広く散らばり、限界値の付近に多くの重みを置きます。右に歪んだ見積もりでは平均もP90も高くなるので、より慎重な読み方です。左右対称の見積もりでは平均は等しく、三角分布は両側とも広くなります。
関連ページ
- ガイド
確率分布の選び方：モンテカルロシミュレーションで使う分布はどれか？
2つの質問で、適した確率分布が見つかります。PERT分布、三角分布、正規分布、対数正規分布、ワイブル分布、ポアソン分布、二項分布、ベータ分布などについて、使いどころとExcelの数式を紹介します。 - ガイド
ExcelでPERT分布を使う：数式・平均・使いどころ
PERT分布は、最小から最大までのベータ分布で、平均は（最小＋4×最頻＋最大）/ 6です。ExcelのBETA.INV数式と、三角分布との違いも解説します。 - ガイド
Excelで一様分布の乱数を作る：2つの値の間の乱数
一様分布は、最小と最大の間のどの値も同じ確率で起こる分布です。Excelの数式、整数用のRANDBETWEEN、平均、範囲だけで足りる場面を解説します。
xellstormは、Excelモデル向けのブラウザー型モンテカルロシミュレーションツールです。アドインは不要で、Excelファイルがパソコンの外に出ることはありません。