エクセルマニア

EXCEL 重回帰式(多変量解析)

TOP > EXCELで統計解析 > EXCELで重回帰式(多変量解析)
このエントリーをはてなブックマークに追加

EXCELで重回帰式(多変量解析)


 EXCELで多変量解析の仕方を解説します。多変量解析というのは例えば、 「タバコ1日何本吸う?1日の睡眠時間は?1日の摂取カロリーは?」 などから、ズバリ「あなたの寿命はxxx才です!」という事を統計的な手法から答える方法です。

このようなデータがあれば、
タバコ20本、睡眠3時間の場合は57.1歳。
タバコ0本、睡眠10時間の場合は77.6歳。
のようにデータにはない答えを導く事ができます。

EXCELで重回帰式を求める


計算の流れ

 計算の流れは、一番上に用意されたデータから順に計算していき、最終的には下にある「係数」と「定数」を求める事ができれば重回帰式を求める事ができます。 この重回帰式により、データにはない条件の答え「タバコ40本・睡眠時間5時間の場合の寿命は?」などを自由に求める事ができます。 また、このページでは、数学的な意味の解説は省き、数学的な意味は別ページで解説します。

サンプルデーターの平均値

①まず用意された3列のデータからはそれぞれの平均値「=AVERAGE(~)」を算出します。
(後で使用します。)

②平均値を求めました


分散共分散行列

用意された3列のデータの「分散共分散行列」を求めます。
アドイン「分析ツール」をインストールしておく必要があります。

③EXCEL2010の場合「データ」メニュー→「データ分析」→「データ分析」ダイアログ→「共分散」を選択してOKをクリックします。

④「入力元」は用意されたデータ「A2:C5」を選択し、「出力先」は適当な場所を選択(ここではA11:C13)します。
アドイン「分析ツール」をインストールしておく必要があります。

⑤「分散共分散行列」が求まりました

また、後で計算で使用するため、空白部分を以下のように埋めておきます。


逆行列

⑥先程求めた分散共分散行列の右下2行2列の逆行列を求めます。
A17:B18を選択した状態で⇒「=MINVERSE(C13:D14) 」⇒「Ctrl+Shift+Enter」

⑦逆行列を求めました


重回帰式の係数の算出

先程求めた逆行列(A17:B18)と分散共分散行列から(B13:B14)を掛け算する事で重回帰式の係数が求まります。

⑧A21:A22を選択した状態で⇒「=MMULT(A17:B18, B13:B14) 」⇒「Ctrl+Shift+Enter」


重回帰式の定数の算出

定数(=p)の算出に Y=ax1+bx2+p → p=y-ax1-bx2の計算を行っています。

⑨定数を算出します。⑧で求めた係数と、②で求めた平均値から求めます。「=A8-A21*B8-A22*C8」


重回帰式の係数と定数から結果を求める。

重回帰式の係数

係数と定数を求める事ができました。A21は「タバコの本数」の係数、A22は「睡眠時間」の係数になり、以下の式で求める事ができます。


寿命 = A21*タバコの本数 + A22*睡眠時間 + A25


この係数・定数を用いてF2:H5には元データのパラメータをあてはめた結果、F6:H8は元データにはないパラメータを当てはめた結果です

多変量解析の実行結果