Excel でモンテカルロ シミュレーションの概要

注:  最新のヘルプ コンテンツをできるだけ早く、お客様がお使いの言語で提供したいと考えております。 このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。私たちの目的は、このコンテンツがお客様の役に立つようにすることです。お客様にとって役立つ情報であったかどうかを、このページの下部でお知らせください。 簡単に参照できるように、こちらに 英語の記事 があります。

この記事では、Wayne の L Winston してから、 Microsoft Excel のデータ分析とビジネス モデリング対応しました。このドキュメントの詳細についてのMicrosoft ラーニングを参照してください

  • ユーザーのタイプ モンテカルロ シミュレーション

  • セル内で=RAND()を入力するときの動作

  • 個別の確率変数の値をシミュレートするにはどうことができますか。

  • 標準確率変数の値をシミュレートするにはどうことができますか。

  • グリーティング カードの会社を特定する方法を生成する名刺の数ですか。

ご不明のイベントを正確に評価されます。たとえばとは、新製品のキャッシュ フローは正の正味現在価値 (NPV) がある確率投資ポートフォリオのリスク係数とは何ですか。モンテカルロ シミュレーションでは、モデルのような状況不確定要素を表示し、時間の桁をコンピューター上再生することができます。

注: 名前モンテカルロ シミュレーションに由来コンピューター シミュレーション 1930 およびを正常に機能を備えた atom 爆弾爆破するために必要な連鎖反応する確率を推定 1940 中に実行します。この作業に関連する物理学者は、シミュレーションをモンテカルロコード名しましたが、ギャンブルをでした。

次の 5 つの章では、Excel を使用して、モンテカルロ シミュレーションを実行する方法の例が表示されます。

多くの企業は、意思決定プロセスの重要な部分としてモンテカルロ シミュレーションを使用します。いくつかの例を紹介します。

  • 一般的なモーター Proctor や記念日ファイザー、板紙 Myers Squibb、and、Eli ユリ平均値を返すと新製品のリスク係数の両方を評価するのにシミュレーションを使用します。GM でこの情報はどの製品が市場を使えることを確認する CEO によってに使用されます。

  • GM では、企業の純利益を予測、構造化され、購入のコストを予測するリスク (利率の変更と exchange レートの変動) などのさまざまな種類の脆弱性を判断するなどのアクティビティのシミュレーションが使用されます。

  • ユリは、各製品の最適な工場の容量を決定するのにシミュレーションを使用します。

  • Proctor と記念日をモデル化し、最適な状態で生垣外部 exchange リスク シミュレーションを使用します。

  • Sears では、シミュレーションを使用して、各製品ラインの個数を仕入先から順序必要があるかを決定: たとえば、今年に配置 Dockers trousers フィールドのペアの数。

  • 燃料と製品の企業は、展開、契約している、またはプロジェクトを延期するためのオプションの値などシミュレーション実数オプション、] の値を使用します。

  • 財務計画は、クライアントの退職後に最適な投資戦略を決定するのにモンテカルロ シミュレーションを使用します。

セルに数式=RAND()を入力するときに、0 ~ 1 の間の任意の値に等しくなります数値が表示されます。したがって、約 25% の時間、すれば、数値 0.25; 小さい約 10% を数値を表示する必要がありますは、少なくとも 0.90 ようにします。RAND 関数の動作を示すためには、Randdemo.xlsx、図 60-1 に示すように、ファイルを参照してください。

本の画像
RAND 関数を示す図 60-1

注: Randdemo.xlsx ファイルを開くときに、60-1 の図に示すように、同じランダムな番号は表示されません。RAND 関数は、ワークシートを開いたときに、または新しい情報がワークシートに入力されたときに生成される数字を常に自動的に再計算します。

最初に、セル C3 にコピー C4:C402 法=RAND()します。C3:C402データ範囲に名前を。次に、F] 列で 400 ランダムな番号 (セル F2) の平均値を追跡して 0 と 0.25、0.25 と 0.50、0.50 と 0.75 と 0.75 と 1 の間で分数を決定するのには、COUNTIF 関数を使用します。F9 キーを押すと、ランダムな番号が再計算されます。400 の数値の平均値が常に約、0.5 と約 25% の結果が 0.25」との間隔で確認します。これらの結果がランダムな番号の定義と一致します。複数のセルに rand 関数によって生成された値が独立したにも注意してください。たとえばでランダムな番号が生成される場合は、セル C3 (たとえば、0.99) の数が多いに把握することが何も、他の乱数の値

予定表の要求は、次の個別の確率変数によって制御されますとします。

必要に応じて

確率

10,000

0.10

20,000

0.35

40,000

0.3

60,000

0.25

Excel を再生する、またはシミュレートの予定表この必要に応じて何度方法あることができますか。考えられる要求の予定表に、RAND 関数のそれぞれの値を関連付けることをお勧めします。次の割り当ては 10,000 の要求が、時間の 10% に発生してがようにします。

必要に応じて

ランダムな番号が割り当てられています。

10,000

0.10 未満

20,000

大きいまたは 0.10、かつより小さい 0.45 で数量

40,000

0.45 で数量を超えるで、および 0.75 より小さい

60,000

0.75 以上

要求のシミュレーションを示すためには、Discretesim.xlsx、次のページで図 60-2 に示すように、ファイルを確認します。

本の画像
図 60-2 確率変数を個別のシミュレーション

テーブルの範囲 (検索という名前) F2:G5 から検索を開始するランダムな番号を使用するが、シミュレーションに重要です。大きいまたは 0 と 0.10 未満の乱数を生成確認要求が 10,000 です。0.10 以上で、および 0.45 で数量未満の乱数を生成 20,000; の要求0.45 で数量を超えるで、および 0.75 未満の乱数を生成 40,000; の要求ランダムな番号を 0.75 以上の 60,000 の要求が生まれます。C4:C402 法RAND()に C3 からコピーして 400 ランダムな番号を生成します。400 試行回数、または B4:B402 に数式VLOOKUP(C3,lookup,2)B3 からコピーして予定表のオンデマンドの反復回数]、[生成します。この数式により、0.10 未満の乱数の 10,000 の要求を生成 0.10 と 0.45 で数量の間の乱数 20,000 の要求が生成されます。F8:F11 セル範囲には、COUNTIF 関数を使用して、各必要に応じて、400 の反復計算の一部を決定します。ここには、ランダムな番号を再計算 F9 キーを押して、シミュレートされた確率、確率を推定オンデマンドに近いされます。

数式NORMINV(rand(),mu,sigma)を任意のセルに入力すると、シミュレートされた値の標準の平均メモリ ユニットと標準偏差シグマを持つ確率変数が生成されます。Normalsim.xlsx、図 60-3 が表示される、ファイルには、この手順を示します。

本の画像
図 60-3 標準確率変数をシミュレートします。

たとえば、400 試行回数、または 40,000 の平均と標準偏差は 10,000 標準確率変数の反復回数] シミュレートしたいとします。(E1、E2 のセルにこれらの値を入力してこれらのセルの意味シグマの名前をそれぞれできます。)数式=RAND()を C4 から C5:C403 にコピーするには、400 のさまざまなランダムな番号が生成されます。B4 にコピー B5:B403 数式NORMINV(C4,mean,sigma) 40,000 の平均と標準偏差は 10,000 標準確率変数から 400 のさまざまな試用版の値が生成されます。ランダムな番号を再計算 F9 キーを押して平均 40,000 と 10,000 に近い標準偏差の近くに残ります。

基本的には、数式NORMINV(p,mu,sigma)ランダムな番号xの平均メモリ ユニットと標準偏差シグマ標準確率変数のp位が生成されます。たとえば、ランダムな番号をセル C4 に 0.77 (図 60-3 を参照してください) 出力セル B4 で約 77th 百分位の標準確率変数 40,000 の平均と標準偏差は 10,000 します。

このセクションでは、意思決定ツールとしてモンテカルロ シミュレーションを使用する方法がわかります。バレンタイン カードの要求は、次の個別の確率変数を管理します。

必要に応じて

確率

10,000

0.10

20,000

0.35

40,000

0.3

60,000

0.25

グリーティング カードの販売 400 円、それぞれのカードの可変コストは ¥ 1.50 します。$0.20 カードあたりのコストの残りのカードを破棄する必要があります。名刺の数を印刷する必要がありますか。

基本的には、何度 (たとえば、1000 反復回数]) (10,000、20,000、40,000、または 60,000) は、各可能な生産数量をシミュレートします。指定する注文数量 1000 反復で最大平均利益を得られます。図 60-4 が表示される、ファイル、Valentine.xlsx では、このセクションのデータが表示されます。セル C1:C11 B1:B11 のセルの範囲の名前を割り当てます。セル範囲 G3:H6 には、名前の参照が割り当てられます。セル C4:C6 では、販売価格と原価パラメーターが入力されます。

本の画像
図 60-4 バレンタイン カード シミュレーション

セル C1 に試用版の製造数量 (この例では 40,000) を入力することができます。次に、数式=RAND()とセル C2 のランダムな番号を作成します。前述のよう法VLOOKUP(rand,lookup,2)では、セル C3 のカードの要求をシミュレートします。(VLOOKUP 数式でrand 関数は、セル名をセル C3、RAND 関数ではありませんに割り当てられている)。

販売数の数はより少ない場合は、生産数量とオンデマンドです。数式で売上を計算するセル C8、 MIN (作成された場合は、必要に応じて) * unit_priceします。数式の合計製造コストを計算するセル C9 で生成 * unit_prod_costします。

単位数が残ってオンデマンド マイナス等しい運用オンデマンドより多くのカードを作成している場合それ以外の場合単位が残っていません。数式でセル C10 で、処理のコストを計算unit_disp_cost * IF (生成 > 必要に応じて、生成される – 必要に応じて、0)します。最後に、セルに C11、収益 – total_var_cost-total_disposing_costとしての利益を計算します。

各生産数量多くの場合 (たとえば、1000) f9 キーを押すし、期待する各数量の利益をカウントする効率的な方法と思います。このような場合は、にとっての救世主双方向のデータ テーブルが呼び出されます。(データ テーブルの詳細について 15 章「感度分析とデータ テーブル、」を参照してください)。この例で使用するデータ テーブルには、60-5 に図が表示されます。

本の画像
グリーティング カードのシミュレーション図 60-5 双方向のデータ テーブル

セル範囲 A16:A1015 の番号 1 – 1000 (、1000 試行回数に対応する) を入力します。これらの値を作成するのには、1 つの簡単な方法では、セル A16 に1を入力して起動します。セルを選択し、[ホーム] タブの [編集] グループの [塗りつぶし] をクリックします [系列] ダイアログ ボックスを表示する系列を選択します。図 60-6 では、表示される [系列] ダイアログ ボックスで、1 の増分値] および [停止値 1000 を入力します。系列の領域で、[] オプションを選択し、[し、[ OK] をクリックします。数値 1 – 1000 セル A16 に開始] 列に入力します。

本の画像
図 60-6 1000 を通じて試用版の番号 1 を入力する系列] ダイアログ ボックスを使用します。

次にでセル B15:E15 当社可能な生産数量 (10,000、20,000、40,000 60,000) を入力します。試用版の番号 (1 ~ 1000) ごとの利益を計算して、各生産数量します。利益 (で計算されるセル C11)、データ テーブル (A15) の左上隅のセルに数式をC11 =を入力して呼びます。

準備が整いましたトリック Excel にシミュレート各生産数量の要求の 1000 反復回数] にします。(A15:E1014)、テーブルの範囲を選択する [データ] タブで [データ ツール] グループでし、[な分析、データのテーブルを選択します。双方向のデータ テーブルを設定するには、行の代入セルとして、生産数量 (セル C1) を選択し、列の代入セルとして (I14 のセルを選択した) 空白のセルを選択します。[Ok] をクリックすると、Excel は、各注文数量の 1000 オンデマンドの値をシミュレートします。

なぜこの動作を理解するのには、C16:C1015 セル範囲のデータ テーブルに配置されている値を検討してください。これらのセルのそれぞれについて、Excel は、セル C1 で 20,000 の値を使用します。C16] では、1 の列の代入セルの値が空のセルとセル C2 が再計算にランダムな番号に配置されます。対応する利益はセル C16 に記録されます。2 の列のセルの入力値を空のセルに配置し、C2 にランダムな番号をもう一度再計算します。対応する利益が C17 のセルに入力されます。

C13:E13 AVERAGE(B16:B1015)数式にセル B13 からコピーすることによって、各製品の数量のシミュレートされた利益の平均を計算します。C14:E14 STDEV(B16:B1015)数式にセル B14 からコピーすることによって、シミュレートされた各注文数量利益の標準偏差を計算します。F9 キーを押すキーを押して毎回のオンデマンド 1000 反復回数] は各注文数量のシミュレートします。40,000 カードを常に作成するには、最大予想される利益が得られます。このため、40,000 カードの作成が適切な決定が表示されます。

上の意思決定のリスクの影響    40,000 カードではなく 20,000 を生成して、期待する利益削除約 22% が (利益の標準偏差を測定する) には、リスクがほとんど 73% を削除します。このため場合は、リスクを非常に注力 20,000 カードを作成することがあります、右上の決定します。10,000 カードを作成している場合は、常に販売すべて残ってせずにそのために、0 カードの標準偏差がところでが常に 10,000 カードを作成します。

注: このブックで、計算方法のオプションは、テーブル以外自動に設定されています。(数式] タブの [計算方法] で [計算] コマンドを使用)。この設定によりするデータ テーブルが再計算 F9 キーを押して、ため大量のデータ テーブルが遅く作業場合は、ワークシートに入力するたびに再計算を理解するのに参照される場合を除き。メモは、この例では、f9 キーを押すと平均利益が変更されます。これは、f9 キーを押すたびにランダムな番号を 1000 のさまざまな順序は、各注文数量の要求の作成に使用されるために発生します。

に対する信頼区間利益の平均    このような状況で自然な質問には、どのような間隔がお 95% 真の平均利益に入ることを確認してよいですか。この間隔は、利益の平均に対する信頼区間を 95%と呼ばれます。シミュレーション出力の平均値の 95% 信頼区間は、次の式で計算されます。

本の画像

J11 のセルに数式D13–1.96*D14/SQRT(1000)された 40,000 の予定表と利益の平均値の 95% 信頼区間の最小の値を計算します。J12 のセルに数式D13+1.96*D14/SQRT(1000)の 95% の信頼区間の上限を計算します。これらの計算には、60-7 に図が表示されます。

本の画像
平均利益 40,000 の予定表を並べるときの 95% 信頼区間を図 60 ~ 7

しましたが、95 %40,000 の予定表を並べるときは、平均利益が $56,687 と $62,589 間であることを確認します。

  1. GMC の販売店では、2005 エンボイの要求を通常の方法で 200 の意味と 30 の標準偏差の分散と考えています。以前を受信すると、そのコストは ¥ 25,000、以前の ¥ 40,000 彼販売する.30,000 ドルの完全な価格ですべてのエンボイ半分販売できます。200、220、240、260、280、または 300 エンボイの順序を検討彼はします。数順序必要がありますがよいですか。

  2. 小さなスーパー マーケットがユーザー雑誌毎週を注文する必要があるのコピーの数を確認しようとしています。考えるのためには、その要求は、次の個別の確率変数によって制御されます。

    必要に応じて

    確率

    15

    0.10

    [エンタープライズ フラグ 20

    0.20

    25

    0.30

    [エンタープライズ日付 30

    0.25

    35

    0.15

  3. スーパー マーケットでは、ユーザーの各コピーの $1.00 を支払うし、$1.95 の販売します。0.50 ドルの各 unsold のコピーが返されます。ユーザーの数のコピーがストア順序ですか?

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×