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は元データにはないパラメータを当てはめた結果です
