【Excel】エクセルで重回帰分析を行うやり方
エクセルの重回帰分析は、売上や成績などの結果に対して、複数の要因がどの程度関係しているのかを数値で確かめる方法です。
たとえば売上を目的変数として、広告費、来店者数、販売価格を説明変数に設定すると、それぞれの要素が売上へ与える影響を同時に分析できます。
関数だけで計算する方法もありますが、基本となる操作は分析ツールの回帰分析を使う方法です。
重回帰分析では、予測したい結果を目的変数、結果に影響すると考える複数の項目を説明変数として扱います。
まずはデータの並び方を整え、分析ツールを有効化してから、回帰分析の画面で入力範囲を指定しましょう。
この記事では、サンプルデータを使いながら、エクセルで重回帰分析を行うやり方、結果表の読み方、数式を使った予測値の求め方まで解説します。
エクセルで重回帰分析を行う方法1【分析ツールの有効化】
それではまず、重回帰分析を実行するために必要な分析ツールの有効化について解説していきます。
| A | B | C | D |
|---|---|---|---|
| 売上 | 広告費 | 来店者数 | 価格 |
| 520 | 80 | 310 | 1200 |
| 610 | 95 | 350 | 1180 |
分析ツールアドインの追加手順
重回帰分析は、通常のリボンに常に表示されている機能ではないため、はじめに分析ツールアドインを有効にします。
エクセル画面の左上にあるファイルを選び、画面下部のオプションをクリックします。
表示されたExcelのオプション画面では、左側のメニューからアドインを選択しましょう。
画面下部の管理でExcelアドインを選び、設定ボタンを押します。
一覧にある分析ツールへチェックを入れてOKを選択すると、有効化は完了です。
分析ツールは一度有効にすれば、通常は次回以降もデータタブから利用できます。
会社のPCで設定変更ができない場合は、管理者による制限が設定されている可能性もあります。
【操作のポイント】アドイン一覧に分析ツールが見当たらないときは、管理の選択欄がExcelアドインになっているか確認します。
データタブから分析を開く確認方法
続いて、分析ツールが正しく追加されたかを確認していきます。
リボンのデータタブを開くと、右端付近の分析グループにデータ分析が表示されます。
データ分析をクリックすると、ヒストグラム、移動平均、回帰などの分析手法が並んだダイアログが開きます。
この一覧の回帰が、単回帰分析と重回帰分析の両方に使う項目です。
説明変数が一つなら単回帰、二つ以上なら重回帰になりますが、操作画面は同じです。
複数の説明変数を同時に指定することが、重回帰分析の重要な条件です。
【操作のポイント】データ分析が表示されない場合は、エクセルを一度閉じずに、アドイン設定へ戻ってチェック状態を確認します。
重回帰分析に向くデータの準備
重回帰分析では、各行を一つの観測データとしてそろえる必要があります。
たとえば月別売上を分析するなら、1行目を見出しにして、2行目以降へ各月の売上、広告費、来店者数、価格を入力します。
途中に空白行、結合セル、文字列だけの行があると、入力範囲の判定が不安定になるため注意が必要です。
目的変数は予測したい数値です。
説明変数は目的変数に影響すると仮定する数値です。
同じ行には、同じ月、同じ店舗、同じ顧客など、対応する条件の値を配置します。
売上が千円単位なら、広告費や価格も単位を明確にした見出しにすると、係数を読んだときの解釈がしやすくなります。
【操作のポイント】1行目には売上や広告費などのヘッダーを置き、2行目以降を欠損値のない連続した数値データにします。
重回帰分析用データの入力範囲
続いては、目的変数と説明変数を正しく指定するためのデータ配置を確認していきます。
| 行 | A列 | B列 | C列 | D列 |
|---|---|---|---|---|
| 1 | 売上 | 広告費 | 来店者数 | 価格 |
| 2 | 520 | 80 | 310 | 1200 |
| 3 | 610 | 95 | 350 | 1180 |
目的変数と説明変数の選び方
目的変数には、予測したい、または変動理由を調べたい数値を一列で指定します。
売上を分析したい場合は、A列の売上が目的変数です。
広告費、来店者数、価格のように売上へ影響すると考えられる数値を、説明変数としてB列からD列へ並べます。
目的変数を二列以上にすると回帰分析は実行できないため、分析ごとに結果となる列を一つに絞ります。
一方で説明変数は連続した複数列にできます。
広告費をB列、価格をD列だけにしてC列を空けるより、必要な列を隣接させておくほうが範囲指定は簡単です。
【操作のポイント】売上のような結果を目的変数に置き、原因候補の項目を説明変数として右側にまとめます。
ヘッダーを含める入力範囲の指定
回帰のダイアログでは、入力Y範囲へ目的変数の列を、入力X範囲へ説明変数の列を指定します。
サンプルデータが1行目にヘッダーを持つ場合、入力Y範囲はA1からA13、入力X範囲はB1からD13のように指定します。
その後でラベルにチェックを入れると、エクセルは1行目を計算対象ではなく見出しとして扱います。
ラベルへチェックを入れ忘れると、売上や広告費という文字を数値として処理しようとしてエラーになることがあります。
入力Y範囲は目的変数の列です。
入力X範囲は説明変数の列です。
1行目にヘッダーがあるデータでは、範囲にヘッダーを含めてラベルへチェックを入れる方法が分かりやすいでしょう。
【操作のポイント】Y範囲とX範囲は、必ず同じ行数になるように指定します。
データ数と説明変数数のバランス
説明変数を増やすほど、多くのデータ行が必要になります。
3個の説明変数を使う場合でも、数行だけで計算すると偶然のばらつきに結果が左右されやすくなります。
実務では、説明変数の数に対して十分な観測数を集め、季節性や特別なキャンペーンなども必要に応じて検討します。
重回帰分析は計算ができることと、信頼できる判断ができることが同じではありません。
また、広告費と来店者数のように似た動きをする説明変数が強く関連していると、多重共線性によって係数が不安定になる場合があります。
説明変数同士の意味と相関関係も確認しておくと、分析結果を業務で使いやすくなります。
【操作のポイント】少ないデータに多くの説明変数を詰め込まず、目的に必要な項目へ絞り込みます。
回帰分析ダイアログの設定
続いては、回帰分析ダイアログで範囲を指定し、結果を出力する手順を確認していきます。
| 項目 | 指定例 | 役割 |
|---|---|---|
| 入力Y範囲 | $A$1:$A$13 | 売上 |
| 入力X範囲 | $B$1:$D$13 | 広告費、来店者数、価格 |
| 出力先 | 新規ワークシート | 分析結果表 |
回帰分析の選択と範囲入力
データタブのデータ分析をクリックし、分析ツール一覧から回帰を選んでOKを押します。
回帰ダイアログが表示されたら、入力Y範囲の右端にある範囲選択ボタンを使ってA1からA13を選択します。
入力X範囲にはB1からD13を指定します。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 売上 | 広告費 | 来店者数 | 価格 |
| 2 | 520 | 80 | 310 | 1200 |
| 3 | 610 | 95 | 350 | 1180 |
入力X範囲は、複数の説明変数を含む長方形の範囲として選択します。
【操作のポイント】範囲を直接入力するときは、絶対参照のドル記号が付いていても問題ありません。
ラベルと信頼水準の設定
入力範囲に1行目のヘッダーを含めた場合は、ラベルのチェックボックスをオンにします。
信頼水準は通常95パーセントのままで利用できます。
これは、係数の推定結果にどの程度の幅を持たせて判断するかに関わる設定です。
信頼水準95パーセントは、統計的な判断でよく用いられる基準です。
特殊な調査設計でなければ、最初は既定値を変更せずに結果を確認する方法で問題ありません。
残差にチェックを入れると、実績値と予測値との差を出力できます。
予測値が実務でどの程度使えるかを見たい場合は、残差出力も選択すると便利でしょう。
【操作のポイント】ヘッダーを含めた範囲ではラベルをオンにし、必要に応じて残差出力を追加します。
出力先の選択と実行
出力オプションでは、新規ワークシートへ出力を選ぶと、元データを変更せずに結果を確認できます。
同じシートへ出力する場合は、元データや図表と重ならない空白セルを出力先範囲として指定しましょう。
OKを押すと、回帰統計、分散分析表、係数表が作成されます。
分析結果は値の大きさだけで判断せず、決定係数、有意確率、係数の符号を順に確認します。
分析をやり直す場合は、新しい出力先を選ぶか、既存の結果を残したまま別シートで比較するのがおすすめです。
【操作のポイント】初回は新規ワークシートを選び、元データと分析結果を分けて管理します。
重回帰分析結果の読み方
続いては、エクセルが出力する回帰分析結果の読み方を確認していきます。
| 確認項目 | 見る内容 | 判断の例 |
|---|---|---|
| 重決定 R2 | モデルの説明力 | 0.72 |
| P値 | 係数の有意性 | 0.05未満 |
| 係数 | 影響の方向と大きさ | 広告費が正 |
重決定係数と補正 R2
重決定 R2は、説明変数全体で目的変数の変動をどの程度説明できているかを示す指標です。
たとえば重決定 R2が0.72なら、売上の変動のおよそ72パーセントを、広告費、来店者数、価格で説明できているという見方ができます。
R2は1に近いほど当てはまりがよい指標ですが、高い数値だけでモデルの良し悪しを決めるものではありません。
説明変数を増やすとR2は下がりにくいため、複数の項目を比較するときは補正 R2も確認します。
補正 R2は、説明変数を増やすことによる見かけ上の改善を考慮した数値です。
R2はモデル全体の当てはまりを示します。
補正 R2は説明変数の数を考慮した比較に向きます。
【操作のポイント】説明変数の組み合わせを比べるときは、R2だけでなく補正 R2も並べて確認します。
係数と切片の意味
係数表の切片は、すべての説明変数を0としたときの予測値です。
広告費の係数が1.8なら、他の条件が同じ場合、広告費が1単位増えると売上は平均で1.8単位増える関係を表します。
価格の係数が負なら、価格が上がるほど売上が下がる傾向を示します。
予測売上 ＝ 切片 ＋ 広告費の係数 × 広告費 ＋ 来店者数の係数 × 来店者数 ＋ 価格の係数 × 価格
係数は、ほかの説明変数の影響を一定とみなした場合の変化量として読みます。
単純な相関とは異なり、複数要因を同時に考慮できる点が重回帰分析の利点です。
【操作のポイント】係数のプラスとマイナスを確認し、単位を踏まえて業務上の変化量へ置き換えます。
P値と有意 Fの確認
係数表にあるP値は、その説明変数の影響が偶然に見えている可能性を判断する目安です。
一般にはP値が0.05未満なら、統計的に有意と判断する場面が多くなります。
P値が小さいことは因果関係を証明する意味ではなく、データ上で一定の関係が確認された目安です。
分散分析表にある有意 Fは、モデル全体に説明力があるかを確かめる指標です。
有意 Fが0.05未満で、個別の係数でもP値が小さい項目は、優先的に検討する候補になります。
【操作のポイント】有意 Fでモデル全体を確認してから、各説明変数のP値と係数を確認します。
予測値と残差の計算方法
続いては、分析結果の係数を利用して予測値と残差を計算する方法を確認していきます。
| A列 | B列 | C列 | D列 | E列 |
|---|---|---|---|---|
| 売上 | 広告費 | 来店者数 | 価格 | 予測売上 |
| 520 | 80 | 310 | 1200 | 515.4 |
係数を使う予測式の入力
分析結果で切片が50、広告費の係数が1.8、来店者数の係数が0.9、価格の係数がマイナス0.12だったとします。
サンプルデータの2行目に対する予測値をE2へ計算する数式は、次の形になります。
=50+B2*1.8+C2*0.9-D2*0.12
この式では、B2の広告費、C2の来店者数、D2の価格をそれぞれの係数へ掛け、切片を加えます。
数式内の係数は、回帰分析結果に表示された数値をそのまま参照します。
係数を別セルへ貼り付けておく場合は、係数セルを絶対参照にすると、数式をコピーしたときに参照位置がずれません。
【操作のポイント】予測式は切片から始め、各説明変数に対応する係数を掛けて加減算します。
オートフィルによる予測値の展開
E2へ予測式を入力した後、セル右下のフィルハンドルを下方向へドラッグすると、各行の予測値をまとめて計算できます。
セル参照はB2、C2、D2から、次の行ではB3、C3、D3へ自動的に変化します。
係数を別シートのB2からE2へ保存している場合は、係数セルの参照をドル記号で固定しましょう。
= $B$2+B2*$C$2+C2*$D$2+D2*$E$2
上の式では、左側のドル記号付きセルが係数、右側の行番号が変化するセルが各行の実績データです。
結果を見やすくするため、予測値は小数点以下の表示桁数をそろえるとよいでしょう。
【操作のポイント】先頭セルに数式を入力してからフィルハンドルを使い、下の行へオートフィルします。
残差と予測精度の確認
残差は、実績値から予測値を引いた差です。
売上がA列で予測売上がE列なら、F2へ=A2-E2と入力すると、予測がどれだけ外れたかを確認できます。
残差がプラスなら実績が予測より高く、マイナスなら実績が予測より低かったことを示します。
残差が特定の月だけ大きい場合は、セール、天候、競合の出店など、説明変数に含めていない要因が影響した可能性があります。
予測値と残差を表やグラフで確認すると、モデルを改善するヒントが見つかるかもしれません。
【操作のポイント】残差は実績値マイナス予測値で計算し、極端に大きい行の背景を個別に確認します。
重回帰分析で起こりやすいエラー
続いては、エクセルで重回帰分析を行うときに起こりやすいエラーと注意点を確認していきます。
| 状況 | 主な原因 | 確認事項 |
|---|---|---|
| 回帰を実行できない | 範囲の行数が異なる | Y範囲とX範囲 |
| 結果が不安定 | 説明変数が似すぎている | 相関と項目内容 |
入力範囲エラーの原因
回帰分析でエラーが出る場合、最初にY範囲とX範囲の開始行と終了行を確認します。
たとえばY範囲がA1からA13で、X範囲がB1からD12では、行数が一致しないため分析できません。
空白セル、エラー値、文字列が混ざっている場合も、計算が止まる原因になります。
見出しを範囲に含めたときはラベルをオンにし、含めないときはラベルをオフにする対応が必要です。
範囲選択をやり直す前に、元データのフィルターや非表示行も含めて確認すると安心です。
【操作のポイント】範囲の行数、空白セル、ラベル設定の三点を順番に見直します。
多重共線性と説明変数の整理
説明変数同士が非常に似た情報を持つと、多重共線性が起こり、係数やP値が安定しにくくなります。
たとえば広告表示回数と広告費は強く連動することがあり、両方を入れることで個別の影響を分けにくくなる場合があります。
このときは、業務上の意味が明確な方を残す、別々の期間のデータを増やすなどの検討が必要です。
説明変数は多いほどよいのではなく、目的変数との関係を説明でき、互いに役割が異なる項目を選ぶことが大切です。
係数の符号が常識と大きく異なる場合も、多重共線性やデータ入力ミスを疑うきっかけになります。
【操作のポイント】似た内容の説明変数を同時に入れすぎず、各項目を採用する理由を整理します。
分析結果を利用するときの注意点
重回帰分析で得られる結果は、過去データに見られた関係を数値化したものです。
将来の市場環境が大きく変わる場合や、入力範囲を超える広告費や価格を予測式へ入れる場合は、精度が下がる可能性があります。
統計結果は意思決定の材料として使い、現場の事情やデータ収集時の条件と組み合わせて判断しましょう。
特に個人情報、医療、採用評価などに関わるデータでは、分析目的、偏り、利用範囲を慎重に確認する必要があります。
定期的に新しいデータで再分析し、予測値と実績値の差を確認する運用が実務的です。
【操作のポイント】分析結果を固定的な結論にせず、新しい実績データでモデルを定期的に見直します。
まとめ エクセルで重回帰分析を行うやり方
エクセルで重回帰分析を行うには、まず分析ツールを有効にし、目的変数と複数の説明変数を見出し付きの表として準備します。
データ分析から回帰を選び、入力Y範囲へ目的変数、入力X範囲へ説明変数を指定し、1行目にヘッダーがある場合はラベルへチェックを入れましょう。
出力された結果では、重決定 R2でモデル全体の当てはまりを確認し、係数、P値、有意 Fを組み合わせて読み取ることが重要です。
係数を使えば、切片と各説明変数の値から予測値を計算でき、実績値との差である残差からモデル改善の手がかりも得られます。
分析ツールを有効化します。
目的変数を一列、説明変数を複数列で準備します。
回帰ダイアログでY範囲、X範囲、ラベル、出力先を設定します。
R2、係数、P値、残差を確認して実務で活用します。
データの件数、欠損値、説明変数同士の関係にも注意しながら、エクセルの重回帰分析を売上予測や要因分析へ役立てていきましょう。
