[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『感度分析について』（にとりん）
感度分析についての質問になります。
行・列それぞれ代入セルの数字を仮に「標準値」とします。
シミュレーションの表は、標準値を中心として5段階に分かれております。
まず行は、仮に50%が標準値だとすると、40%・45%・50%・55%・60%という具合に50％（標準値）を中心として5%ずつ段階があるという感じです。
もう一方の列も同様に、1,200を標準値にした場合、1,000・1,100・1,200・1,300・1,400という感じです。
ここまででも良く伝わらないと思いますが、以下のようなイメージです。
A B C D E F G H I
1 行代入 50%
2 列代入 1,100
3 結果 5%
4
5 ( ) 40% 45% 50% 55% 60%
6 1,000
7 1,100
8 1,200
9 1,300
10 1,400
A5( )内を「=B3」とした後、「A5：F10」を範囲指定して、データ⇒予測⇒What-If⇒データテーブルから「行の代入セル」に「=$B$1」、「列の代入セル」に「=$B$2」を選択すれば、「B6」〜「F10」にシミュレーション値が出ると思うのですが、間違いなかったでしょうか？
Q1.何故か「B6」〜「B10」までの数字が同じ結果で出てきてしまい、困っております。
Q2.例えば「D5」のセルの数字を数値入力による方法ではなく「=B1」として、「B5」を「=B1-10%」、「B6」を「=B1-5%」というように、行代入の数字が変動するたび（列も同様）標準値による結果を中心としてシミュレーションの値が変動するような仕組みは作れますでしょうか？
（上の例だと「50%かつ1,200」の場合における結果「=D8」5%を中心としてシミュレーションが求められてますが、行代入・列代入の数字が代わっても「=D8」の標準値結果を中心としてシミュレーションが求められる仕組みです。）
非常に難しいご質問で、かつ拙い説明で申し訳ありませんが、何卒宜しくお願い致します。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
肝心のB3セルにはどんな数式が入っているんですか？
（半平太） 2021/02/24(水) 11:42
B3のセルは、別のシートで計算された結果になります。
実は、このシートは「エネルギー設備規模（kW）=B2」と「設備の稼働割合（稼働○%）=B1」、及びここには示しておりませんが「設備投資額」から算出される、「内部収益率（IRR○%）=B3」を別のシートで計算していて、「B6」〜「F10」にはIRRの結果がシミュレーションされるようにしたいと考えているところです。
よって、「B3」セルには別シートで算出したIRRの結果を持ってくることになります。
（にとりん） 2021/02/24(水) 12:06
すみません。
ちょっと先程の説明に誤りがありましたので、訂正致します。
「エネルギー設備規模（kW）」は説明の誤りで、正しくは「エネルギー設備規模／設備投資額（事業単価：円／kW）」になります。
なので、単価が低いほどIRRの結果は良くなり、単価が高いほどIRRの結果は悪くなりますので、サンプルですと「F6」が高パフォーマンスかつ低コストであるため最も高いIRR値が算定され、逆に「B10」は低パフォーマンスかつ高コストのため最も低いIRR値になるシミュレーション値が算定される様にしたいと思っております。
（にとりん） 2021/02/24(水) 12:31
＞Q1.何故か「B6」〜「B10」までの数字が同じ結果で出てきてしまい、困っております。
どんな数式か知らされない側としては、「同じ結果」おかしいのかどうか判断できません。 D7セルが5%にならなかったらおかしいとは言えると思います。(実際D7は5%ではないのですか？）
＞Q2.例えば「D5」のセルの数字を数値入力による方法ではなく ＞「=B1」として、「B5」を「=B1-10%」、「B6」を「=B1-5%」というように、 ＞行代入の数字が変動するたび（列も同様）標準値による結果を中心として ＞シミュレーションの値が変動するような仕組みは作れますでしょうか？
やってみれば分かると思いますが、 B5の40%をシミュレートしようとすると、B1に40%が適用され、 ブーメランとなってB5は30%でしかなくなり、何がなんだか分からない結果が得られます。 その部分を便利にしたいのであれば、 B5:F5とA6:A10の数値はマクロで生データ化してから、シミュレーションを開始すればいいでしょう。
＞サンプルですと「F6」が高パフォーマンスかつ低コストであるため最も高いIRR値が算定され、 ＞逆に「B10」は低パフォーマンスかつ高コストのため最も低いIRR値になる ＞シミュレーション値が算定される様にしたいと思っております。
「エネルギー設備規模／設備投資額（事業単価：円／kW）」のカッコ書きが、 全体の単価を意味するものなのか、分母にだけに掛かっているのかこちらでは分かりません。
いずれにしても、IRR値を算出している数式が正しく作られていれば、意図通りの結果が得られるハズです。
（半平太） 2021/02/24(水) 13:15
ご回答ありがとうございます。
Q1-A.D8セルは5%になりますが、D6〜D10がすべて5%というシミュレーション結果が出てきてしまいます。実際の数字とは違いますが、現在以下のような事になっています。
A B C D E F G H I
1 行代入 50%
2 列代入 1,100
3 結果 5%
4
5 ( ) 40% 45% 50% 55% 60%
6 1,000 1% 3% 5% 7% 9% ←列の結果がなぜか反映されていない
7 1,100 1% 3% 5% 7% 9%
8 1,200 1% 3% 5% 7% 9%
9 1,300 1% 3% 5% 7% 9%
10 1,400 1% 3% 5% 7% 9%
Q2-A.説明不足と誤りがありました。事業単価を意味しているので、設備投資額（円）／エネルギー設備規模（kW）＝事業単価（円／kW）という意味でした。申し訳ございません。
Q3-A.IRR値の計算は問題ないです。なので、シミュレーション表の50%と1,200の交差するD8セルの値は正常です。
Q4.B1・B2の数字が変動しても、常にD5およびA8の数字はB1・B2の数字となり、D8のの結果を基準値としてそれぞれのシミュレーション値を算定したいという考えです。マクロは不慣れなためそのために必要なマクロがあるのでしたら教えてもらえれば有り難いです。
宜しくお願い致します。
（にとりん） 2021/02/24(水) 13:43
＞Q3-A.IRR値の計算は問題ないです。
私は、そこに疑念があると言っている訳で、その一言で処理されては先に進めないです。
一つ、実験して頂けませんか。 B1セルに60%、B2セルに1400として、B3セルになんと出ますか？
（半平太） 2021/02/24(水) 14:05
度々失礼します。
おっしゃる通り、D5の50%の数字を「手入力でやった場合」と「=B1」とした場合で、シミュレーション値が違くなってしまいました。手入力だと正しく、=B1だとダメな結果でした。
（同じ50%という数字をもってきているものだと思っていたもので…）
半平太様の疑念の通り、表上のD8セルのIRR値も含め、結果としてめちゃくちゃな内容となりました。
（にとりん） 2021/02/24(水) 14:10
D5を=B1とした場合
A B C D E F G H I
1 行代入 50%
2 列代入 1,200
3 結果 3.5% ←この結果値は正常です
4
5 ( ) 40% 45% 50% 55% 60%
6 1,000 1.3% 0.0% 0.0% 1.3% 3.5%
7 1,100 1.3% 0.0% 0.0% 1.3% 3.5%
8 1,200 1.3% 0.0% 0.0% 1.3% 3.5%
9 1,300 1.3% 0.0% 0.0% 1.3% 3.5%
10 1,400 1.3% 0.0% 0.0% 1.3% 3.5%
D5も含めA列・5行の数字を手入力でやった場合
A B C D E F G H I
1 行代入 50%
2 列代入 1,200
3 結果 3.5% ←この結果値は正常です
4
5 ( ) 40% 45% 50% 55% 60%
6 1,000 1.3% 2.3% 3.5% 4.5% 5.7% ←相変わらず列の結果がなぜか反映されていない
7 1,100 1.3% 2.3% 3.5% 4.5% 5.7%
8 1,200 1.3% 2.3% 3.5% 4.5% 5.7%
9 1,300 1.3% 2.3% 3.5% 4.5% 5.7%
10 1,400 1.3% 2.3% 3.5% 4.5% 5.7%
（にとりん） 2021/02/24(水) 14:21
申し訳ないですが、この展開のしかたではストレスが溜まるだけなので、私は下ります。
他の回答者のレスをお待ちください。
（半平太） 2021/02/24(水) 14:32
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.