Excel 2019 (パート 28): What-if 分析 (If-Then 分析)

Excel には、What-if 分析などの複雑な数学的計算を実行するための強力なツールが含まれています。この機能は、データが不完全であっても、データを実験して質問に答えるのに役立ちます。この記事では、ゴール シークと呼ばれる分析ツールの使用方法を学びます。

ゴールを目指す

Excel で数式や関数を作成するときは、常にさまざまな部分を組み合わせて結果を計算する必要があります。ゴール シークは逆に機能します。つまり、目的の結果から開始でき、エンジンはその結果をもたらす入力値を計算します。この記事では、いくつかの例を使用して、ゴール シークの使用方法を説明します。

ゴールシークの使い方例1

あなたがクラスに登録しているとします。現在の得点は 65 点で、この科目に合格するには少なくとも 70 点を獲得する必要があります。幸いなことに、GPA を上げるのに役立つ最後の課題が 1 つあります。ゴール シークを使用すると、科目に合格するために最終演習で必要なスコアを知ることができます。

下の画像では、最初の 4 つの演習のスコアが 58、70、72、および 60 であることがわかります。5 番目の演習のスコアがどのようになるかは不明ですが、式または関数を記述して計算することができます。必要な最終スコア。この場合、各割り当ては均等に重み付けされるため、=AVERAGE(B2:B6) と入力して 5 つのスコアすべてを平均するだけです。ゴール シークを使用する場合、セル B6 には、その最終課題に必要な最低スコアが表示されます。

1. 値を変更するセルを選択します。ゴール シークを使用するときは常に、数式または関数がすでに含まれているセルを選択する必要があります。たとえば、セル B7 には数式=AVERAGE(B2:B6) が含まれているため、セル B7 を選択します。

2. [データ]タブ で、[What-If 分析]コマンド をクリックし、ドロップダウン メニューから[ゴール シーク]を選択します。

3. 3 つのフィールドを含むダイアログ ボックスが表示されます。最初のフィールドSet cell: には、目的の結果が含まれます。この例では、セル B7 が選択されています。

2 番目のフィールド [ To value: ] は、望ましい結果です。たとえば、クラスに合格するには少なくともその数値を獲得する必要があるため、70と入力します。

3 番目のフィールド [セルの変更:]は、ゴール シークが回答を配置するセルです。たとえば、最後の演習で達成する必要があるスコアを決定するため、セルB6を選択します。

4. 完了したら、「OK」をクリックします。

5. ダイアログ ボックスには、ゴール シークが答えを見つけることができたかどうかが表示されます。「OK」をクリックします。

6. 結果が指定したセルに表示されます。この例では、ゴール シークは、合格するには最終課題で少なくとも 90 点のスコアが必要であると計算しました。

ゴールシーク使用例2

イベントを計画していて、500 ドルの予算を超えない範囲でできるだけ多くの人を招待したいとします。ゴール シークを使用して、招待する人数を確認できます。以下の例では、セル B5 に、予約の合計費用と 1 人あたりの費用を計算する式=B2+B3*B4が含まれています。

1. 値を変更するセルを選択します。たとえば、セル B5 を選択します。

2. [データ]タブ で、[What-If 分析]コマンド をクリックし、ドロップダウン メニューから[ゴール シーク]を選択します。

3. 3 つのフィールドを含むダイアログ ボックスが表示されます。最初のフィールドSet cell: には、目的の結果が含まれます。この例では、セル B5 が選択されています。

2 番目のフィールド [ To value: ] は、望ましい結果です。たとえば、500 ドルだけ使いたいので、「500」と入力します。

3 番目のフィールド [セルの変更:]は、ゴール シークが回答を配置するセルです。たとえば、500 ドル以上を費やすことなく何人のゲストを招待できるかを知りたいので、セル B4 を選択します。

4. 完了したら、「OK」をクリックします。

5. ダイアログ ボックスには、ゴール シークが答えを見つけることができたかどうかが表示されます。「OK」をクリックします。

6. 結果が指定したセルに表示されます。この例では、ゴール シークは答えを約18.62 と計算しました。この場合、最終的な答えは整数である必要があるため、答えを切り上げるか切り捨てる必要があります。切り上げを行うと予算を超えるため、例ではゲスト18名を切り捨てます。

上の例でわかるように、状況によっては整数の答えが必要になります。ゴール シークで小数が表示された場合は、状況に応じて切り上げまたは切り捨てを行う必要があります。

他の種類の what-if 分析

より高度なプロジェクトの場合は、他のタイプの what-if 分析 (シナリオとデータ テーブル) を検討することもできます。ゴール シークのように、目的の結果から開始して逆方向に作業するのではなく、これらのオプションを使用すると、複数の値をテストし、結果がどのように変化するかを確認できます。

スクリプトを使用すると、複数のセル (最大 32) の値を一度に置換できます。好きなだけシナリオを作成し、値を手動で変更せずに比較できます。以下の例では、シナリオを使用して、今後のイベントのさまざまな場所を比較しています。

データ テーブルを使用すると、数式で 1 ​​つまたは 2 つの変数を取得し、それらを好きなだけ異なる値に置き換えて、結果をテーブルに表示できます。このオプションは、シナリオやゴール シークとは異なり、複数の結果を一度に表示するため、特に強力です。以下の例では、自動車ローンに関して 24 の可能な結果が表示されます。