Microsoft Excel で What-If 分析ツールを使用する方法

Microsoft Excel でデータを分析するとき、「オプション B の代わりにオプション A を選択したらどうなるでしょうか?」などの比較を行うことができます。Excel に組み込まれている What-If 分析ツールを使用すると、数値と金額をより簡単に比較できます。たとえば、仕事の給与、ローンのオプション、収益と支出のシナリオを評価することができます。

Excel の What-If 分析ツールには、シナリオ マネージャー、ゴール シーク、データ テーブルが含まれます。これらのツールの目的をわかりやすく説明するために、それぞれの例を見てみましょう。

シナリオマネージャー

シナリオ マネージャーを使用して、さまざまな結果を確認するために変更できる値を入力します。おまけとして、金額や数値を並べて比較するためのシナリオ概要レポートを作成します。

たとえば、イベントを計画していて、コストが異なるいくつかのテーマの中から選択することにしたとします。各テーマの価格を設定して、それらを比較するためにどれくらいの費用がかかるかを確認します。

意思決定に役立つように、さまざまな状況に応じたさまざまなシナリオを作成します。

シナリオマネージャーの使用方法

上記の例のように、さまざまな状況を比較する準備ができている場合は、次の手順に従って Excel のシナリオ マネージャーを使用します。

• 最初のシナリオのデータをシートに入力します。前の例を使用して、イベントのテーマ費用を比較し、ビーチ テーマの費用をセル A2 ～ A6 に入力し、その費用をセル B2 ～ B6 に入力します。セル B7 で価格を合計して、合計コストを確認します。

• これらの詳細をシナリオ マネージャーに追加するには、リボンの [データ] タブと [予測] セクションに移動します。「What-If 分析」ドロップダウン メニューを開き、「シナリオ マネージャー」を選択します。

• 「追加」をクリックします。

• シナリオに名前を付けて (ここでは「Beach Theme」を使用します)、「Changing Cells」フィールドに調整するセルを入力します。あるいは、シート内のセル内でカーソルをドラッグして、そのフィールドに値を入力します。必要に応じて、デフォルト以外のコメントを入力し、「OK」をクリックします。

• 「セルの変更」フィールドの値はシート上の値と一致する必要がありますが、ここで調整できます。「OK」をクリックして続行します。

• 最初のシナリオを追加したので、それがシナリオ マネージャーにリストされているのが表示されます。「追加」を選択して次のシナリオを設定します。

• 最初のシナリオと同様に、2 番目のシナリオの詳細を入力します。名前、変更するセル、およびオプションのコメントを入力し、「OK」をクリックします。この例では、インプレース比較を簡単に確認できるように、「Vegas Theme」と同じセル範囲 B2 ～ B6 を入力しています。

• 「シナリオ値」ウィンドウに 2 番目のシナリオの値を入力します。最初のセルと同じセルを使用している場合は、それらのセルが表示されます。使用するものを入力し、「OK」をクリックします。

• シナリオマネージャーウィンドウのリストから表示したいシナリオを選択し、「表示」をクリックします。

• シート内の値が更新され、選択したシナリオが表示されます。

• 引き続きシナリオを追加して表示し、シート内の更新された値を表示します。シートに残しておきたいシナリオを見つけたら、「閉じる」を選択してシナリオ マネージャーを終了します。

シナリオの概要を表示する

シナリオの概要を表示して、すべてのシナリオを一度に確認し、並べて比較します。

• 「データ -> What-If 分析 -> シナリオ マネージャー」に戻り、「概要」をクリックします。

• 表示するレポートの種類を選択します。「シナリオの概要」または「シナリオピボットテーブルレポート」のいずれかです。必要に応じて、結果を表示したい場合は、その結果を含むセルを入力し、「OK」をクリックします。

この例では、「シナリオの概要」を選択し、レポートを新しいシート タブに配置します。また、オプションでレポートの特定の部分を非表示にするセルのグループ化をレポートに含めることができることにも注目してください。

シナリオ マネージャーで詳細を調整しても、レポートは自動的に更新されないため、新しいレポートを生成する必要があることに注意してください。

ゴールを目指す

ゴール シーク ツールは、シナリオ マネージャーとはやや逆に機能します。このツールを使用すると、既知の結果が得られ、さまざまな変数を入力して、その結果にどのように到達できるかを確認できます。

たとえば、製品を販売していて、年間の利益目標があるとします。目標を達成するには、何個販売する必要があるか、またはいくらの価格で販売する必要があるかを知りたいと考えています。ゴール シークは答えを見つけるための理想的なツールです。

ゴール シークでは、1 つの変数または入力値のみを使用できます。残りの値を事前に用意しているシナリオでは、これを使用します。

ゴールシークの使い方

Goal Seek ツールの例では、1,500 個の製品を販売し、52,000 ドルの利益を得たいと考えています。私たちはゴールシークを使用して、その目標を達成するために製品をどの価格で販売すべきかを決定しています。

• まず、シナリオに従ってシートに値と数式を入力します。この例を使用すると、現在の数量をセル B2 に、推定価格をセル B3 に、利益の式をセル B4 に入力します=B2*B3。

• [データ] タブに移動し、[What-If 分析] ドロップダウン メニューを開き、[ゴール シーク] を選択します。

• 次の値を入力し、「OK」をクリックします。
  • Set cell : 目的の結果を得るために変更する値のセル参照 (数式を含む)。この例では、これはセル B4 です。
  • To value : 希望する結果の値。私たちにとって、これは 52000 です。
  • cell : 結果を得るために変更するセル参照を変更します。価格を変更したいのでセルB3を使用しています。

• [OK] をクリックすると、[ゴール シーク ステータス] ボックスが更新されて解決策が表示され、シートが調整された値を含むように変更されます。この例では、52,000 ドルの目標を達成するには、製品を 35 ドルで販売する必要があります。「OK」を選択して、シートに新しい値を保持します。

ご存知ですか: Microsoft Excel では、スパークラインやミニ チャートの挿入など、さまざまなことができます。

データ表

Excel のデータ テーブルを使用して、考えられるさまざまな数値状況を表示します。

理想的な例として、ローンのオプションを検討しているとします。さまざまな金利を入力すると、それぞれの金利での月々の支払額がわかります。これは、どの金利で買い物をするかを決定したり、貸し手と話し合ったりするのに役立ちます。

データ テーブルでは、変数は 2 つまでしか使用できません。さらに必要な場合は、シナリオ マネージャーを使用してください。

データテーブルの使用方法

3 番目の What-If 分析ツールであるデータ テーブルを使用するには、次の手順に従います。データ設定に注意してください。

たとえば、データ テーブルを使用して、次のデータを通じてさまざまな金利でローンの支払いがいくらになるかを確認します。

• セル B3 ～ B5 には金利、支払回数、融資額が表示されます。
• セル C3 から C5 で調査する金利を含む Rate 列。
• セル D2 に現在の支払いの式が含まれる支払い列。
• [支払い] 列の数式の下にある結果セル。データ テーブル ツールを使用して自動的に入力されます。これは、金利ごとの支払額を示しています。

シートにデータと数式を入力するときは、次の点に注意してください。

• 行または列指向のレイアウトを使用します。それによって数式の配置が決まります。
• 行指向のレイアウトの場合、初期値の 1 列左側のセルと、値が含まれる行の 1 つ下のセルに数式を配置します。
• 列指向のレイアウトの場合、値を含む列の 1 行上のセルと 1 つ右側のセルに数式を配置します。

この例では、列指向のレイアウトで単一変数 (金利) を使用しています。セル D2 (値の 1 つ上の行、1 つ右のセル) に数式が配置されていることに注目してください。

• 独自のデータを入力し、数式、値、結果セルを含むセルを選択します。この例では、セル C2 ～ D5 を選択しています。

• [データ] タブに移動し、[What-If 分析] ドロップダウン メニューを開き、[データ テーブル] を選択します。

• データの変化する変数を含むセルを [データ テーブル] ボックスに入力します。行指向のレイアウトの場合は「行入力セル」を使用し、列指向のレイアウトの場合は「列入力セル」を使用します。この例では後者を使用し、金利を含むセルである「B3」を入力します。

• [データ テーブル] ボックスで [OK] をクリックすると、結果のセルに期待したデータが入力されていることがわかります。この例には、異なる金利ごとの支払い額が含まれています。

データ テーブルで 1 つではなく 2 つの変数を使用したり、行指向のレイアウトを試したり、この機能に関する Microsoft のサポート ページでこの What-If 分析ツールの詳細と制限を確認したりできることに注意してください。

よくある質問

Excel で既存のシナリオを編集するにはどうすればよいですか?

シナリオ マネージャーを使用して、シナリオの名前と値を変更できます。「データ -> What-If 分析 -> シナリオ マネージャー」を選択してツールを開きます。リストからシナリオを選択し、右側の「編集」をクリックします。変更を加え、「OK」を選択して変更を保存します。

最初にシナリオ概要レポートを作成した場合は、更新された詳細を確認するためにレポートを再生成する必要があります。

Excel によるデータ テーブルの再計算を停止できますか?

ブックにデータ テーブルが含まれている場合、変更がない場合でも、Excel はそのデータ テーブルを自動的に再計算します。ただし、必要に応じてこのオプションをオフにすることもできます。

[数式] タブに移動し、[計算] グループの [計算オプション] ドロップダウン メニューを開き、[データ テーブルを除いて自動] を選択します。

データ テーブルを手動で再計算するには、式を選択して を押しますF9。

Excel が提供する他の分析ツールは何ですか?

Excel には、必要に応じてさまざまな種類のデータ分析ツールが用意されています。いくつか例を挙げると、条件付き書式を使用して特定のデータを強調表示したり、クイック分析を使用して書式設定、グラフ、表を使用したり、Power Query を使用して堅牢なデータ分析を行うことができます。

また、データを絞り込むためのフィルター、表やグラフをフィルター処理するためのスライサー、データに関する質問への回答を取得するためのデータ分析ツールなどの基本的な Excel 機能を使用することもできます。

これらの機能などに関する追加のサポートが必要な場合は、Windows の Excel の [ヘルプ] タブに移動するか、Mac の Excel の [Tell me] メニュー オプションを使用してください。

画像クレジット: Pixabay。すべてのスクリーンショットは Sandy Writtenhouse によるものです。