Excelによる回帰分析

例えば，このようなデータを分析してみましょう．これは男女100人の身長，体重のデータ（仮想）です．以下の様にExcelの表の上にデータが並んでいたとします．
※データはこの後にもならんでいます．

uni-0.gif

ここでは，女性の身長が，自身の体重と父親の身長とどのような 関係にあるかを重回帰分析してみます．

【手順】

  1. メニューバーの「ツール」
  2. 「分析メニュー」
  3. 「回帰分析」

すると，次のダイアログが現れます．データ範囲，出力範囲を設定し，「OK」ボタンをクリックします．

reg-1.gif

図中の「入力Ｙ範囲」には，従属変数（被説明変数），「入力Ｘ範囲」には 独立変数（説明変数）を示す範囲を入力します．

残差や正規確率などのオプションを選択し，出力先を指定したら，「ＯＫ」 ボタンをクリックして，分析を行ないます．

※注意１
上記の図中に「定数に0を使用」という項目がありますが，これを指定して回帰した場合，相関係数や決定係数が正しく計算されません（MS側のバグらしいです．群馬大学の青木先生のホームページで知りました）．もっとも回帰係数は合っているようです．

【追加情報1999/12/30】上記の現象はExcel97での現象でしたが，最新のExcel2000でも同様の現象が確認されています（TSPで確認）．なんとかならないものか＞MS社様

Excelによる回帰分析（分析結果）

実行すると，Excelは以下の分析結果を出力します．

reg-2.gif

モデルの当てはまりの具合をしめす決定係数やそれぞれの変数について推定されたパラメータを見ることができます．

※注意２
「分析ツール」による回帰分析では，説明変数（X範囲）には16個までしか用いることができないことに注意してください．なぜなの？＞MS社様
まぁ16変数で構わないけど，17個以上を指定した場合にExcelで表示されるメッセージもおかしい．『〜16以上の変数は使用できません』←Excel2002(XP)ですよ？
「以上」を使うのなら17以上としなくては…

残差

推定された回帰式が適正なものであるかどうかを確認するものとして，決定係数や係数の有意性をチェックしますが，「残差」も確認する必要があります．回帰式（単回帰）は，

ｙ＝ａ＋ｂｘ

ですが，説明変数（ｘ）を上式に代入して求めた被説明変数（ｙ）と実際のデータから読み取れるｙには誤差が存在しています．これを残差とよび，残差ｕは，

ｕ＝ｙ−ａ−ｂｘ

で求められます．回帰分析（最小２乗法）ではこのｕに関して以下の仮定を置いています．

  1. ｕの平均は０
  2. ｕの分散は一定
  3. ｕはｘと無相関
  4. ｕは互いに無相関

残差を確認するには，これを散布図で視覚化すると良いでしょう．「回帰分析」ツールの中に残差を計算する箇所があるので，この「残差」と「残差グラフの作成」チェックしてみます．実行すると，回帰結果の他に各残差の値，残差のグラフが表示されます．

zansa.gif

例では残差はランダムに現れている（すなわち，良好な推定ができた）ように見えます．ｕがｘと共に増加（減少）している，ｕが規則性を持っているように観察される，などの傾向が観察されるのなら，推定がうまく行っていない，あるいは他に有力な説明変数がある，など回帰式そのものを見直す必要があります．

Excelの関数で算出する回帰分析

上記のように「分析ツール」を使っても良いのですが，算出した統計量をさらに次の分析に移したいときや，マクロを書くときなどには「分析ツール」よりも関数を用いたほうが便利です．関数では単回帰と重回帰で用いる関数が異なります．単回帰では係数，決定係数などはそれぞれ専用の１つの関数で求めます．重回帰では１つの関数で可能ですが，少々操作が難しいと思います．

単回帰の項目関数
X値の係数slope(Y範囲,X範囲)
切片intercept(Y範囲,X範囲)
決定係数rsq(Y範囲,X範囲)
重回帰関数
係数，切片などlinest(Y範囲,X範囲,定数オプション,補正オプション)

単回帰はその方法は他の関数と操作が同じなので説明は省略します．

【手順】

  1. 出力範囲をドラッグする．重回帰の場合，上記の例のように説明変数が2つの場合，切片を入れて3つの係数が計算されるので，3列分範囲を選択します．行は1〜5行選択します．

  2. 範囲は選択したまま，関数を入力する．以下の図のようになっているはずです．

    linest-1.gif

  3. 定数オプション,補正オプションにはそれぞれ true，falseのいずれかを入力します．

    • 定数オプション「true」→切片を算出
      定数オプション「false」→切片を０として計算

    • 補正オプション「true」→係数の標準誤差を算出
      補正オプション「false」→算出しない

  4. 「Ctrl」キー「Shift」キーを押しながら「Enter」キーを押す．普通は「Enter」キーだけですが，この関数では3つのキーを同時に押します．

  5. 出力結果は以下の図のようになります．

    linest-2.gif

    日本語による説明は阿部がつけたものです．分析ツールによる結果と見比べてください．通常の感覚では左から，切片，X値１，X値２となるでしょうが，linest関数はそれらとは逆になることに注意してください．また，出力範囲選択のところで，行数を4以下にすると，その分だけ（例えば上から4行分だけ）が表示されます．

  6. 係数の検定のためのt値を得たい場合，定義から，係数÷標準誤差の計算で得ることができます．上の例で変数「X値2」のt値は，おおよそ0.332÷0.079=4.203となります．

※連続処理などで出力の一部のセルのみを出力したい（例えば，各係数の標準誤差だけを出したい）という場合は，linest関数をindex関数で囲って用います．

例　index(linest(YYY,XXX,true,true),行番号，列番号)
行，列番号の定義はlinest関数の出力範囲の左上が1行1列目になります．

トップに戻る

１つ戻る