Excelで「whatif」分析用のデータテーブルを作成する方法

Microsoft Excelは、「whatif」シナリオを分析するためのいくつかの機能を提供します。そのようなツールの1つがデータシートです。これを使用すると、さまざまな値を試してさまざまな結果を確認できます。

これは、さまざまな変数を使用して、ローン、投資、製品のマークアップなどを確認するのに便利な方法です。たとえば、ローンにいくつかの異なる利率を入力して、各利率での支払い額をすべて1か所で確認できます。

1つの変数でデータテーブルを作成します

Excelのデータテーブルには、1つまたは2つの異なる入力を含めることができます。それでは、1つの変数を持つデータテーブルから始めましょう。FV関数と年利、支払回数、支払額の定数を使用して、投資の将来価値を計算しました。

ここでは、さまざまな金利での投資の将来価値がどうなるかを見ていきます。

列または行に値を入力できますが、配置は数式を配置する場所に影響します。値が列にある場合は、数式を右上の1つのセルに配置します。値が連続している場合は、数式を1セル下の左側に配置します。

この例では、列指向のデータテーブルを使用しています。下のスクリーンショットでわかるように、数式を列Dに移動し、金利を列Cにリストしました。

数式、値、および期待される結果を含むセルを選択します。私たちにとって、それはC2からD6までです。

[データ]タブをクリックし、[仮定の分析]ドロップダウン矢印をクリックして、[データテーブル]を選択します。

開いた[データテーブル]ウィンドウで、設定に従って、変更する変数のセル参照を入力します。この例では、「列入力セル」フィールドに、変化する利率のセルB3への参照を入力しています。ここでも、列ベースのデータテーブルを使用しています。

[OK]をクリックすると、結果が表示されます。これで、入力された各金利での投資の将来価値が得られます。

2つの変数を使用してデータテーブルを作成します

より詳細な分析のために、データテーブルに2つの変数を追加できます。同じ例を使用して、さまざまな金利で投資の将来価値を取得するだけでなく、支払い回数を変更することでさまざまな将来価値を確認する必要があります。

2つの変数を使用しているため、列指向と行指向の両方のデータテーブルを使用します。したがって、以下に示すように、数式を値の列のすぐ上、値の行の左側に配置します。

次に、数式、値、期待される結果を含むすべてのセルを選択します。私たちにとって、それはC2からF6までです。

[データ]タブをクリックし、[仮定の分析]>[データテーブル]を選択します。ウィンドウが表示されたら、行と列の入力セル参照を入力します。

この例を使用して、金利用の列入力セルにB3を入力し、支払い金額用の行入力セルにB4を入力します。

[OK]をクリックすると、変数のすべての出力が表示されます。ここでは、さまざまな金利とさまざまな支払い回数を使用した投資の将来価値があり、すべてがきれいな表に表示されています。

次回、異なる数または量のワークシートのデータを分析する場合は、データシートの使用を検討してください。計算が行われるだけでなく、結果は簡単に分析できる構造化されたテーブルを提供します。