エクセルで売上や成績、費用などの変化を分析するとき、原因になりそうな項目が一つとは限りません。
広告費と価格、気温と来店者数のように、複数の説明変数が一つの結果にどの程度関係しているかを調べたい場面では、重回帰分析が役立ちます。
重回帰分析は難しそうに見えますが、Excelの分析ツールやLINEST関数を使えば、統計ソフトがなくても実施できます。
重回帰分析では、予測したい数値を目的変数、影響を調べる数値を説明変数として扱います。
複数の説明変数を同時に入れるため、一つの要因だけを見る単回帰分析よりも、実務に近い予測と比較がしやすくなります。
この記事では、1行目に見出しがあるサンプルデータを用いて、エクセルで重回帰分析を行う操作、関数を使う方法、グラフの考え方、分析結果の読み方を解説します。
数値の意味を確認しながら進めれば、予測式の作成や改善施策の検討にも活用できるでしょう。
エクセルで重回帰分析を実行する方法【分析ツールの回帰機能】
それではまず、Excelの分析ツールにある回帰機能で重回帰分析を実行する方法について解説していきます。
| 月 | 売上 | 広告費 | 平均気温 | 来店者数 |
|---|---|---|---|---|
| 4月 | 520 | 42 | 18 | 310 |
| 5月 | 610 | 50 | 22 | 355 |
| 6月 | 680 | 58 | 25 | 390 |
この例では売上を目的変数、広告費、平均気温、来店者数を説明変数として設定します。
分析ツールの追加設定
重回帰分析を使う前に、リボンに分析ツールが表示される状態かを確認しましょう。
「データ」タブの右側に「データ分析」が見当たらない場合は、「ファイル」から「オプション」を開きます。
左側の「アドイン」を選び、画面下部の管理欄で「Excel アドイン」を選択して「設定」をクリックします。
一覧から「分析ツール」にチェックを入れ、「OK」を選択すれば準備完了です。
分析ツールは一度有効にすると、通常は次回以降もデータタブから利用できます。
分析ツールが表示されない原因は、アドインが未登録であるケースがほとんどです。
会社のPCで設定項目が変更できない場合は、管理者の権限設定が影響している可能性があります。
目的変数と説明変数の範囲指定
データタブの「データ分析」をクリックし、一覧から「回帰」を選択して「OK」をクリックします。
入力Y範囲には、予測したい売上の列を指定します。
今回の表であれば、1行目の見出しを含めてB1からB13のように売上列を選択します。
入力X範囲には、広告費、平均気温、来店者数の3列をまとめて指定します。
たとえばC1からE13を指定し、1行目に列名を含めた場合は「ラベル」にチェックを入れましょう。
目的変数をY、説明変数をXとして指定する考え方です。
Yは一列であることが基本ですが、Xには隣り合う複数列を指定できます。
説明変数の列は途中に空白列を入れず、連続した範囲にまとめると指定ミスを防げます。
出力先は「出力先」を選択し、空いているセルを指定するか、「新規ワークシート」を選択すると見やすく整理できます。
必要に応じて「残差」にチェックを入れると、予測値と実績値との差も同時に出力されます。
回帰ダイアログの実行操作
範囲指定を終えたら、「OK」をクリックして分析を実行します。
新しいワークシートまたは指定した場所に、回帰統計、分散分析表、係数表が出力されます。
表の数値が一度に多く表示されても、まずは重決定R2、補正R2、有意F、係数、P値の順に確認すると理解しやすくなります。
重回帰分析の出力では、係数表だけを見て判断せず、モデル全体の有意性も確認することが大切です。
【操作のポイント】入力Y範囲は売上などの結果、入力X範囲は原因候補となる複数列です。
見出しを選択範囲に含めた場合だけ、ラベルのチェックを入れましょう。
重回帰分析に使うデータの整え方
続いては、重回帰分析に使うデータの整え方を確認していきます。
| 売上 | 広告費 | 平均気温 | 来店者数 |
|---|---|---|---|
| 520 | 42 | 18 | 310 |
| 610 | 50 | 22 | 355 |
| 680 | 58 | 25 | 390 |
1行が一つの観測対象になるように、各列の意味をそろえることが基本です。
1行目のヘッダーと数値形式
サンプルデータでは1行目をヘッダーとし、2行目以降に各月の実績を入力します。
見出しには「売上」「広告費」のように、列の内容が分かる名前を付けましょう。
金額、人数、温度などは、見た目だけではなくセル内部でも数値として認識されている必要があります。
「520千円」のように単位を直接入力すると文字列になる場合があるため、数値は520だけを入力し、単位はヘッダーや表示形式で管理する方法が安全です。
重回帰分析の対象範囲に文字列やエラー値が混ざると、正しい計算結果が得られません。
売上を千円単位で入力するなら、すべての行を千円単位でそろえます。
説明変数の単位が異なっていても計算はできますが、係数を比較するときは単位の違いに注意が必要です。
欠損値と外れ値の確認
空白セルがある月は、回帰分析の前に理由を確認します。
単純に入力漏れなら元データを補い、実績が存在しないなら分析対象から除外する判断も必要です。
ゼロは空白とは異なり、実際に広告費がゼロだったという意味を持ちます。
空白をゼロに置き換えると、分析結果が大きく変わるかもしれません。
また、桁を一つ誤入力した値や、特別なキャンペーンによる異常に大きい売上は外れ値になることがあります。
外れ値を消す前には、計算ミスなのか、実際に起きた重要な出来事なのかを区別しましょう。
実務上の出来事による値であれば、削除するよりも、その月に何があったかを別途記録して解釈に反映するほうが有益な場合もあります。
説明変数の重複と相関関係
説明変数は多ければよいわけではありません。
たとえば「来店者数」と「レジ通過数」のように非常に似た指標を同時に入れると、どちらが売上に影響しているのかを分けにくくなります。
この状態は多重共線性と呼ばれ、係数の符号や大きさが不安定になる原因です。
相関係数を確認するには、CORREL関数を利用できます。
=CORREL(C2:C13,D2:D13)
この式は、広告費がC列、平均気温がD列にある場合の相関係数を返します。
相関係数が1またはマイナス1に近いほど、二つの数値は強く連動している傾向です。
【操作のポイント】説明変数は、目的変数との関係だけでなく、説明変数同士の似通い方も確認します。
意味が重なる列をむやみに増やさないことが、読みやすい分析結果につながります。
LINEST関数による重回帰式の作成
続いては、LINEST関数による重回帰式の作成を確認していきます。
| 売上 | 広告費 | 気温 | 来店者数 |
|---|---|---|---|
| 520 | 42 | 18 | 310 |
| 610 | 50 | 22 | 355 |
LINEST関数を使うと、セル上で回帰係数を求め、別の計算式に利用しやすくなります。
LINEST関数の基本構文
LINEST関数は、既知の目的変数と説明変数から、最小二乗法による回帰直線または回帰式の係数を返す関数です。
複数の説明変数を指定すれば、重回帰分析の係数を配列として取得できます。
=LINEST(B2:B13,C2:E13,TRUE,TRUE)
この式ではB2からB13が売上、C2からE13が広告費、気温、来店者数です。
3番目のTRUEは切片を計算する指定、4番目のTRUEは追加の統計情報も返す指定です。
Microsoft 365やExcel 2021以降では、LINEST関数の結果が複数セルに自動展開されます。
古いExcelでは、結果を表示したい範囲を先に選択してから数式を入力し、配列数式として確定する操作が必要になることがあります。
係数の並び順と予測式
LINEST関数の1行目には係数が表示されますが、説明変数の係数は指定範囲の右端の列から逆順に返される点に注意が必要です。
C列からE列を指定した場合、結果の左側にはE列の来店者数の係数、その次にD列の気温の係数、さらにC列の広告費の係数、最後に切片が並びます。
回帰式は、一般に次の形で表せます。
予測売上 ＝ 切片 ＋ 広告費の係数 × 広告費 ＋ 気温の係数 × 気温 ＋ 来店者数の係数 × 来店者数
仮に切片が80、広告費の係数が2.5、気温の係数が1.8、来店者数の係数が0.9なら、広告費が50、気温が22、来店者数が355の予測売上は式に各値を代入して求められます。
係数が正なら、その説明変数が大きくなるほど目的変数も増える方向です。
係数が負なら、ほかの条件を同じとした場合に目的変数を下げる方向の関係を示します。
係数は因果関係を証明する数値ではなく、データ上で確認された関連の強さを表す値です。
予測値を求める数式
作成した係数を利用して、各行の予測売上を計算できます。
たとえばH2に切片、H3に広告費の係数、H4に気温の係数、H5に来店者数の係数を置いたとします。
= $H$2+$H$3*C2+$H$4*D2+$H$5*E2
この数式をF2に入力すると、2行目の広告費、気温、来店者数から予測売上が算出されます。
絶対参照のドル記号を係数セルに付けることで、下方向へコピーしても係数の参照先が固定されます。
F2の右下にあるフィルハンドルを下へドラッグすれば、F3以降にも同じ計算を反映できます。
サンプルデータの1行目をヘッダーにした場合、数式は2行目から入力してオートフィルする流れです。
【操作のポイント】LINEST関数の係数は右端の説明変数から逆順に出るため、列名と係数を対応させて記録します。
予測式では係数セルを絶対参照にして、オートフィル時のずれを防ぎましょう。
散布図と予測値グラフによる確認
続いては、散布図と予測値グラフによる確認を解説していきます。
| 月 | 実績売上 | 予測売上 | 残差 |
|---|---|---|---|
| 4月 | 520 | 508 | 12 |
| 5月 | 610 | 622 | -12 |
| 6月 | 680 | 671 | 9 |
重回帰分析は表の数値だけでなく、グラフにすると実績と予測のずれを直感的に確認できます。
実績値と予測値の折れ線グラフ
月、実績売上、予測売上の3列を選択し、「挿入」タブから「折れ線」または「散布図」を選択します。
時系列で比較したい場合は折れ線グラフ、実績値と予測値がどの程度一致しているかを確認したい場合は散布図が向いています。
予測値が実績値の動きに近いほど、モデルはデータの傾向を一定程度説明できていると考えられます。
一方で、特定の月だけ大きく離れている場合は、モデルに入れていない要因があった可能性を検討しましょう。
グラフは係数の正しさを証明するものではありませんが、入力ミスや予測の偏りに気付きやすい確認手段です。
Excel操作画面のイメージ
数式バーに表示された予測値の式を確認し、実績売上の隣に予測売上を並べると比較しやすくなります。
赤枠のセルを選択してフィルハンドルを下へ伸ばせば、月ごとの予測値を連続して求められます。
残差グラフとモデルの偏り
残差は、実績値から予測値を引いた差です。
=B2-F2
この式を残差列に入れ、下方向へオートフィルします。
残差がゼロの周辺にランダムに散らばるなら、一定の偏りは比較的小さいと判断できます。
一方で、月が進むにつれて残差が大きくなる、常にプラス側に偏るなどの傾向があれば、季節性や新規施策のような要因を説明変数に追加する余地があります。
予測が外れた理由を調べることは、重回帰分析を次の改善につなげる重要な工程です。
【操作のポイント】実績値、予測値、残差を同じ表に置くと、計算結果とグラフの確認を往復しやすくなります。
大きな残差が出た行は、数値だけでなく当時の業務上の事情も確認しましょう。
重回帰分析の結果の見方
続いては、重回帰分析の結果の見方を確認していきます。
| 指標 | 例 | 確認する内容 |
|---|---|---|
| 重決定R2 | 0.82 | モデルの説明力 |
| 有意F | 0.01 | モデル全体の有意性 |
| P値 | 0.03 | 各説明変数の有意性 |
回帰分析の出力表は専門用語が多いため、数値の役割を分けて読むことが大切です。
重決定R2と補正R2
重決定R2は、目的変数の変動を回帰式でどの程度説明できているかを示す指標です。
たとえばR2が0.82なら、売上の変動のうち約82パーセントを説明変数の組み合わせで説明できているという見方になります。
ただし、説明変数を増やすとR2は基本的に下がりにくい性質があります。
そこで、説明変数の数を考慮した補正R2も合わせて確認します。
候補となるモデルを比較するときは、R2だけでなく補正R2を優先して見ると、不要な変数を増やしすぎる判断を避けやすくなります。
R2が高くても、データ件数が少ない場合や、説明変数が重複している場合は過信できません。
係数とP値の判断
係数は、ほかの説明変数を同じとしたときに、その変数が1単位増えた場合の目的変数の変化量を表します。
広告費の係数が2.5なら、広告費が1単位増えたとき、売上は平均して2.5単位増える関係がデータ上で示されます。
P値は、その係数が偶然に得られた可能性を判断する材料です。
一般にはP値が0.05未満であれば、統計的に有意な関係と判断する目安が使われます。
P値が大きい変数は必ず削除するものではなく、業務上の重要性やデータ数も踏まえて検討します。
気温のP値が高くても、季節商品の販売では重要な要因であるかもしれません。
統計結果と現場の知識を組み合わせる姿勢が必要です。
有意Fと信頼区間の確認
有意Fは、説明変数をまとめた回帰モデル全体が意味を持つかを確認する指標です。
有意Fが0.05未満であれば、少なくとも一つ以上の説明変数が目的変数と関係している可能性が高いと考えます。
係数表の下限95パーセントと上限95パーセントは、係数の信頼区間です。
この範囲にゼロが含まれる場合は、係数の方向が明確ではない可能性があります。
係数の符号、P値、信頼区間、有意Fを一緒に見ることで、一つの数値だけに依存しない判断ができます。
分析結果は将来を保証する予言ではありません。
過去のデータに基づく推定であるため、価格改定、競合の出現、制度変更などの大きな環境変化があれば、再分析が必要になります。
【操作のポイント】R2は説明力、補正R2は変数数を考慮した説明力、P値は各変数の確からしさを見る目安です。
結果を報告するときは、数値の良し悪しだけでなく、分析対象期間とデータ件数も併記しましょう。
まとめ エクセルで重回帰分析をするやり方（結果の見方・グラフ・関数・複数の説明変数）
エクセルで重回帰分析を行うと、売上のような目的変数に対して、広告費、気温、来店者数など複数の説明変数が持つ関係を一度に確認できます。
まずは1行目にヘッダーを置き、2行目以降の各行で同じ条件のデータをそろえることが出発点です。
分析ツールの「回帰」では、入力Y範囲に目的変数、入力X範囲に複数の説明変数を指定します。
分析ツールで全体の結果を読み、LINEST関数で係数をセルに取り出すと、予測表の作成までスムーズに進められます。
予測値は、切片に各説明変数の係数と値の積を加える形で計算できます。
数式を先頭セルに入力し、係数を絶対参照にしてオートフィルすれば、複数行の予測値を効率よく作成できます。
実績値、予測値、残差をグラフで比較すると、特定の月だけ予測が大きく外れていないかを見つけやすくなります。
結果を見る際は、重決定R2と補正R2でモデルの説明力を確認し、有意Fでモデル全体、P値と信頼区間で各説明変数の妥当性を確認しましょう。
重回帰分析は数式を出すことが目的ではなく、どの要因を改善すれば結果が変わりそうかを考えるための材料です。
データの入力ミス、欠損値、説明変数同士の強い相関に注意しながら、定期的にデータを更新して分析を見直していきましょう。
