エクセル・クックブック

第14章. もしも分析

この作品はAIを使って翻訳されている。ご意見、ご感想をお待ちしている：[e-mail]

What-if分析では、さまざまな値を試してその効果を調べる。 例えば、金利の変更がローン返済にどのような影響を与えるかを発見したり、従業員のスケジュールを立てるのに最も効率的な方法を見つけたりすることができる。

この章のレシピは、エクセルで利用できる4種類のwhat-if分析ツールをカバーしている：データテーブル、シナリオ、ゴールシーク、ソルバーである。カバーする分野は以下の通りである：

• 1つまたは2つの変数が異なる値を仮定した場合の影響を示す結果表をプロデューサが作成する。

• 値のセットをシナリオとして保存し、それらを切り替える

• 目標を達成し、具体的な結果を返すために必要なセルの値を発見する。

• 制約条件の下での利益の最大化、労働力のスケジューリング、タスクの割り当て、ルート計画などの最適化問題の解決

14.1 1変数データテーブルの作成

問題

1つのセルに異なる値を代入すると、1つまたは複数の数式の結果がどのように変わるかを示す表を作成したい。

解決策

セル B1 にローン金額、B2 にローン期間（月単位）、B3 に年率、セル B4 に PMT 関数（レシピ 10.1 を参照）を使って月々の支払額を計算するワークシートがあるとする。ローン期間を変更することで、月々の支払額と支払総額がどのように変わるかを確認したい。

1つのセルに異なる値を代入すると、1つまたは複数の数式の結果がどのように変わるかを示すセル範囲である。つまり、この例では、異なるローン期間について、毎月の支払額と支払総額を比較する1変数データテーブルを作成することができる。

、データテーブルを作成する：

1. 入力セルを特定する：異なる値を代入して、その効果を確認したいセルである。この例では、入力セルはB2（月単位のローン期間）である。

2. 入力セルに使用したい値を1列に入力し、両側の行と列を少し空ける。この例では、E3:E6の範囲に12、24、36、48の数字を入力する。

3. ステップ 2 で入力した値の 1 行上、1 列右のセルに、結果を表示したい数式（ ）を入力する。つまり、月払いの計算式の結果を見るには、セル F2 に次のように入力する。 =B4とセル F2 に入力する。

4. 他の計算式の結果を見たい場合は、最初の計算式の右側のセルに入力する。つまり、各ローン期間の支払合計額を追加表示するには、セルG2に次のように入力する。 =B4*B2とセルG2に入力する。

5. 入力値と数式を含むセル範囲（この例ではE2:G6）を選択する。次に、データ⇒予測⇒What-If分析⇒データ・テーブルを選択して、データ・テーブル・ダイアログ・ボックスを開く。

6. 入力セルの値は列にあるので、入力セルへのセル参照を列入力セル・ボックスに入れる。つまり、この例では $B$2と入力する。

7. OKをクリックしてデータ・テーブルを挿入する。

8. オプションとして、データ表の目的を明確にするために見出しを追加し、形式を整える。

図14-1に完成したデータテーブルを示す。

図14-1. 列指向の1変数データ・テーブル