【Excel】エクセルで回帰分析を行うやり方（単回帰分析・最小二乗法）
Excelの回帰分析は、売上と広告費、気温と販売数、学習時間と得点のように、二つの数値の関係を調べたいときに役立つ機能です。
単回帰分析では、一つの説明変数から目的変数を予測する直線を求めます。
Excelでは分析ツールを使う方法と、関数で計算する方法の両方を選べます。
この記事で扱うサンプルでは、A列を広告費、B列を売上とします。
1行目は見出しとし、実際のデータは2行目から入力する前提です。
回帰式は、売上の予測値を切片＋傾き×広告費として表します。
最小二乗法の考え方、分析ツールの操作、係数の読み方、予測式の作り方まで順番に確認していきましょう。
エクセルで単回帰分析を実行する方法
それではまず、Excelの分析ツールで単回帰分析を実行する方法について解説していきます。
| 行 | A列 広告費 | B列 売上 |
|---|---|---|
| 1 | 広告費 | 売上 |
| 2 | 10 | 82 |
| 3 | 20 | 105 |
| 4 | 30 | 126 |
| 5 | 40 | 151 |
分析ツールの有効化
回帰分析のボタンが表示されていない場合は、最初にアドインを有効化します。
Excelのファイルを選び、オプション、アドインの順に開きます。
画面下部の管理でExcelアドインを選び、設定をクリックしましょう。
分析ツールにチェックを付けてOKを押すと、データタブの分析グループにデータ分析が追加されます。
この設定は通常一度行えば、次回以降も利用できます。
会社の端末で設定変更が制限されている場合は、管理者にアドイン利用の可否を確認する必要があるかもしれません。
回帰分析ダイアログの設定
続いては、回帰分析ダイアログの入力内容を確認していきます。
データタブのデータ分析をクリックし、一覧から回帰分析を選択してOKを押します。
入力Y範囲には結果として予測したい売上の列を指定し、入力X範囲には原因側として扱う広告費の列を指定します。
今回なら入力Y範囲はB1からB5、入力X範囲はA1からA5です。
1行目を範囲に含めた場合は、ラベルにチェックを入れましょう。
YとXを逆に指定すると、求めたい予測式とは異なる分析になるため注意が必要です。
出力先は新規ワークシートを選ぶと、元データを崩さず結果を見比べやすくなります。
出力結果の確認
続いては、分析後に表示される表の見方を確認していきます。
OKをクリックすると、回帰統計、分散分析表、係数表が新しいシートに出力されます。
まず係数表の切片とX値を見つけます。
切片は広告費がゼロのときの推定売上であり、X値の係数は広告費が一単位増えたときの売上変化の目安です。
たとえば切片が60、X値の係数が2.3なら、予測式は売上＝60＋2.3×広告費となります。
出力された係数は、必ずしも因果関係を証明する数値ではありません。
広告費以外に季節、商品価格、在庫、競合施策などが売上へ影響する可能性も考えましょう。
【操作のポイント】入力Y範囲は予測したい結果、入力X範囲は説明に使う数値として指定します。
最小二乗法と回帰直線の仕組み
続いては、単回帰分析で回帰直線が求められる仕組みについて確認していきます。
| 広告費 x | 実績売上 y | 予測売上 ŷ |
|---|---|---|
| 10 | 82 | 83.0 |
| 20 | 105 | 106.0 |
| 30 | 126 | 129.0 |
回帰式の基本形
それではまず、単回帰式の形について解説していきます。
予測値 ŷ ＝ a ＋ bx
aは切片、bは傾き、xは説明変数、ŷは予測値です。
広告費をx、売上をyとするなら、式は広告費から売上を推定する直線になります。
傾きが正なら広告費が大きいほど売上も増える傾向、負なら反対方向の傾向を示します。
ただし、傾きが正であっても、常に同じ割合で売上が増えるとは限りません。
残差と平方和
続いては、最小二乗法の中心となる残差について確認していきます。
実際の売上と回帰式で求めた予測売上の差を残差と呼びます。
ある行の実績が105で予測が106なら、残差はマイナス1です。
残差をそのまま足すと正負が打ち消し合うため、最小二乗法では残差を二乗して合計します。
残差平方和 ＝ Σ 実績値－予測値 の二乗
残差平方和が最も小さくなる直線を選ぶ考え方が最小二乗法です。
Excelの回帰分析は、この計算を自動で行って係数を出力します。
散布図による関係の確認
続いては、数値を読む前に散布図で傾向を確認していきます。
広告費と売上の2列を選択し、挿入タブから散布図を作成します。
点が右上がりに並ぶなら正の関係、右下がりなら負の関係が疑われます。
一部の点だけが極端に離れている場合は、外れ値が回帰式へ強く影響することがあります。
入力ミス、特別なキャンペーン、欠品などの背景を確認してから除外の可否を判断しましょう。
【操作のポイント】回帰分析の前に散布図を作ると、直線で説明しやすいデータかを直感的に見極められます。
分析結果の係数と決定係数の読み方
続いては、回帰分析結果の係数と決定係数を読む方法について確認していきます。
| 項目 | 例 | 意味 |
|---|---|---|
| 重決定 R2 | 0.84 | 当てはまりの目安 |
| 切片 | 60.0 | xがゼロの推定値 |
| X値 | 2.30 | xが一増える変化量 |
決定係数 R2
それではまず、決定係数R2について解説していきます。
R2は、目的変数の変動を回帰式がどの程度説明できているかを見る指標です。
0から1の範囲で示され、1に近いほどデータは回帰直線に沿いやすい傾向です。
R2が高いことは、予測が必ず正しいことや因果関係が確定したことを意味しません。
特にデータ件数が少ない場合は、偶然の並びによって高く見える可能性があります。
有意FとP値
続いては、有意FとP値の確認方法について解説していきます。
有意Fは、回帰モデル全体に統計的な意味があるかを確認するための数値です。
係数表にあるP値は、各係数が偶然にゼロとなっている可能性を検討する材料になります。
一般的にはP値が小さいほど、偶然だけでは説明しにくい結果と考えられます。
ただし、基準値だけで機械的に結論を出さず、業務上の意味とデータの質を合わせて判断することが重要です。
Excel画面での確認手順
続いては、出力シートで重要な欄を見つける手順を確認していきます。
出力表の回帰統計の中にある重決定R2を確認します。
係数表では切片とX値の行を探し、予測式に転記する数値を確認しましょう。
【操作のポイント】R2、有意F、X値のP値は、同じ出力シートで続けて確認できます。
関数を使った回帰式と予測値の作成
続いては、分析結果を使ってシート上に予測値を作成する方法について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 広告費 | 実績売上 | 予測売上 |
| 10 | 82 | 83.0 |
| 20 | 105 | 106.0 |
SLOPE関数とINTERCEPT関数
それではまず、傾きと切片を関数で求める方法について解説していきます。
=SLOPE(B2:B5,A2:A5)
=INTERCEPT(B2:B5,A2:A5)
SLOPE関数では既知のyを先に指定し、既知のxを次に指定します。
INTERCEPT関数も同じ順序で範囲を指定すると、回帰直線の切片を返します。
範囲の行数をそろえることが、正しい係数を得る基本です。
FORECAST.LINEAR関数
続いては、新しい広告費から売上を予測する関数を確認していきます。
=FORECAST.LINEAR(A6,$B$2:$B$5,$A$2:$A$5)
この式ではA6の広告費をもとに、B2からB5の売上データとA2からA5の広告費データから予測値を返します。
既知のデータ範囲には絶対参照を付けると、数式を下へコピーしても範囲がずれません。
FORECAST.LINEAR関数は、過去データの傾向を直線として延長した予測です。
予測誤差の計算
続いては、実績と予測の差を見える化する方法について確認していきます。
C2に予測売上を表示した場合、D2には=B2-C2と入力すると残差を求められます。
残差の絶対値を見たいときは、=ABS(B2-C2)を使います。
大きな誤差が連続するなら、説明変数が不足している、データの期間が混ざっている、直線関係が弱いといった可能性があります。
【操作のポイント】予測値だけで終わらせず、残差列を作って外れ値や偏りを確認しましょう。
回帰分析で注意したいデータの扱い
続いては、回帰分析の精度を落としやすいデータの扱いについて解説していきます。
| 確認項目 | 確認内容 |
|---|---|
| 欠損値 | 空白や文字列が混ざっていないか |
| 外れ値 | 特別な事情の値ではないか |
| 期間 | 同じ条件の期間を比較しているか |
欠損値と文字列
それではまず、入力データの空白と文字列について解説していきます。
数値列に空白、ハイフン、未入力、単位付きの文字列が含まれると、分析結果が期待どおりにならない場合があります。
広告費が10千円のような入力になっているなら、数値の10だけを別列に用意する方法が安全です。
分析対象の列は、単位をそろえた純粋な数値だけで構成することが重要です。
外れ値の判断
続いては、外れ値を扱う際の考え方について確認していきます。
極端に高い売上があったからといって、すぐに削除してはいけません。
大型受注、期間限定の値引き、祝日、障害発生など、業務上の理由を確認する必要があります。
明らかな入力誤りであれば修正し、正しい特別値なら通常データと分けて分析する選択肢もあります。
因果関係と予測範囲
続いては、分析結果を実務で使うときの注意点を確認していきます。
相関がある二つの数値でも、一方が他方の原因とは限りません。
また、広告費10から40のデータで作った式を、広告費1000の予測へそのまま使うのは危険です。
観測した範囲から大きく離れた予測は、外挿と呼ばれ不確実性が高くなります。
【操作のポイント】データの意味、収集条件、予測したい範囲をそろえてから回帰式を利用します。
複数の要因を扱う重回帰分析の基礎
続いては、広告費以外の要因も考慮したいときの重回帰分析について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 広告費 | 気温 | 売上 |
| 10 | 18 | 82 |
説明変数の追加
それではまず、説明変数を追加する場面について解説していきます。
売上が広告費だけで決まらない場合は、気温、来店数、価格、曜日などを説明変数として追加できます。
回帰分析ダイアログでは、入力X範囲に隣接する複数列をまとめて指定します。
目的変数の売上列は入力Y範囲に一列で指定する点は単回帰分析と同じです。
説明変数の重複
続いては、似た説明変数を同時に入れる注意点について確認していきます。
広告費と広告表示回数のように強く似た動きをする変数を同時に入れると、係数が不安定になることがあります。
これは多重共線性と呼ばれる問題です。
変数は多ければよいわけではなく、目的に対して意味のある項目を選ぶ必要があります。
単回帰分析との使い分け
続いては、単回帰分析と重回帰分析の使い分けについて確認していきます。
まずは一つの要因との関係を理解したい場合、単回帰分析は結果を説明しやすい方法です。
複数の要因を同時に調整しながら予測精度を高めたい場合は、重回帰分析が候補になります。
データ件数が少ないうちは、説明変数を増やしすぎず、単回帰分析から始めると理解しやすいでしょう。
【操作のポイント】説明変数を増やす前に、各列が何を表し、なぜ売上に関係すると考えるのかを整理します。
まとめ エクセルで回帰分析を行うやり方
Excelで単回帰分析を行うと、二つの数値の関係を回帰直線として整理し、予測へ活用できます。
最初はデータタブのデータ分析から回帰分析を選び、入力Y範囲に結果、入力X範囲に説明変数を指定しましょう。
係数表の切片とX値から予測式を作り、重決定R2とP値で結果の妥当性を確認する流れが基本です。
最小二乗法は、実績値と予測値の差である残差の二乗和が最も小さくなる直線を求める考え方です。
SLOPE関数、INTERCEPT関数、FORECAST.LINEAR関数を使えば、分析ツールを使わずに係数や予測値をシートへ表示することもできます。
ただし、回帰分析は因果関係を自動で証明する機能ではありません。
欠損値、外れ値、データ期間、予測範囲を確認し、業務上の背景と合わせて解釈することが大切です。
まずは身近な二列のデータで散布図と単回帰分析を試し、数値の関係を読み取る習慣を作っていきましょう。
