Excel Tips #01「Excelで将来を予測する」



横軸に「時間」、縦軸に何らかの「量」で表されるグラフにおいて、 そのデータの推移から将来を予測することは、TVニュースや新聞などでよく目にしますね。 例えば、「このペースで進むと3年後には○○○ぐらいになるでしょう」のような。 Excelでは、このようなデータの推定を、「近似曲線の追加」という機能を使うことによって、 比較的簡単に計算することができます。

上の表1で示されるデータの場合を例にとって、その手順を説明しましょう。

【1】データ入力・グラフの作成

グラフウィザードの使い方など、「Excel基礎」の部分はここでは省きますが、 今回のような「近似・推定」を行う場合、「折れ線グラフ」や「棒グラフ」よりも 「散布図」(上の図1)が、後の作業(「近似曲線の追加」)を考えれば、 ベストだと言えます。

【2】近似曲線の追加

作成したグラフをクリックして選択します。 そうすると、メニューバーに「グラフ」というメニューが(右から3つめに) 現れましたね。 その中から「近似曲線の追加...」というメニューを実行します。 この例の場合なら、[種類]から「線形近似」を選べばいいでしょう。 さらに、[オプション]から「グラフに数式を表示する」をクリックしてチェックを入れることによって、 散布図の中に近似曲線(線形近似)の方程式(1次式)が自動的に追加されます (下の「完成画面」参照)。

【3】データの推定

散布図に追加された方程式を利用すれば、データの推定が可能です。 以下の「完成画面」を見てください。

ここで、セル「C18」に調べたい年度を入力すれば、セル「F18」に推定値が出力されます。 そのためには、セル「F18」に

=19.661*C18-39128

と入力することによって、セル「C18」の値が変わっても(再入力されても)、 その都度、対応する推定値が再計算され、セル「F18」に表示されます。 この方程式の逆関数("x=..."の形に変形)を考えれば、 縦軸の数値から、それに対応する年度(横軸の値)が求められます。 各自、19行目に表示できるよう、考えてみましょう (中学校程度の数学知識があれば、できるはずなので...)。

ここで用いた例題のExcelファイルは、下記からダウンロードすることができます。

解凍後ファイル名:xltips_1.xls(17KB)

(LHa自動解凍形式なので、↑ダウンロード完了後↑ダブルクリックすればOK)