【Excel】エクセルで回帰分析をするやり方と結果の見方（グラフ・係数・決定係数・p値・95信頼区間・残差分析・エラー）

エクセルの回帰分析は、売上と広告費、気温と来客数、勉強時間と得点のように、二つ以上の数値データにどのような関係があるかを調べる機能です。

分析ツールを使えば、回帰式、係数、決定係数、p値、95信頼区間、残差などを自動で出力できますが、表に多くの数値が並ぶため、最初はどこを見ればよいのか迷うかもしれません。

この記事では、サンプルデータを用いてエクセルで単回帰分析を行う手順と、結果の読み取り方、散布図や残差分析による確認、よくあるエラーの対処法を解説します。

数式の形だけではなく、データの質とグラフの形も確認することが、実務で使える回帰分析につながります。

 

エクセルで回帰分析を実行する手順

それではまず、エクセルの分析ツールを使って回帰分析を実行する手順について解説していきます。

A	B	C
1	広告費	売上
2	10	125
3	15	149
4	20	167
5	25	190

 

分析ツールアドインの有効化

エクセルで回帰分析を行うには、最初に分析ツールを利用できる状態にします。

リボンのファイルを選び、オプション、アドインへ進みます。

画面下部の管理でExcelアドインを選択し、設定をクリックしましょう。

表示された一覧で分析ツールにチェックを入れ、OKを押します。

データタブの右側にデータ分析が表示されれば準備完了です。

データ分析が見つからない場合は、アドインの有効化が完了していない可能性があります。

 

目的変数と説明変数の指定

回帰分析では、予測したい数値を目的変数、予測の材料にする数値を説明変数として設定します。

今回の例では、広告費を説明変数、売上を目的変数として扱います。

データタブのデータ分析をクリックし、一覧から回帰を選択してOKを押します。

入力Y範囲には売上の列を指定し、入力X範囲には広告費の列を指定します。

1行目に見出しがあるため、ラベルにチェックを入れます。

出力先は新規ワークシートを選ぶと、元データを残したまま結果を確認できて便利です。

 

回帰分析の出力設定

出力オプションでは、残差、標準残差、残差グラフ、正規確率グラフなどを選択できます。

初めて確認する場合は、残差と残差グラフにチェックを入れておくと、式の当てはまりを後から検証しやすくなります。

信頼度は通常95パーセントのままで問題ありません。

OKをクリックすると、回帰統計、分散分析表、係数表、残差出力が作成されます。

出力表の数値だけを見て判断せず、グラフと残差もセットで確認する姿勢が大切です。

 

回帰式と係数の読み取り方

続いては、出力された係数から回帰式を作り、数値の意味を確認していきます。

項目	係数	意味
切片	82.4	広告費が0のときの推定売上
広告費	4.25	広告費が1増えたときの推定増加量

 

回帰式の基本形

単回帰分析の回帰式は、目的変数をY、説明変数をXとして、Y＝a＋bXで表します。

aは切片、bは説明変数の係数です。

広告費が20の場合は、82.4＋4.25×20を計算し、推定売上は167.4となります。

セル上で予測値を求めるなら、D2に=$H$17+$H$18*B2のように入力し、下へオートフィルします。

ここでH17とH18は、出力された切片と広告費の係数があるセルを想定しています。

係数のセルは絶対参照にしておくと、数式をコピーしても参照先がずれません。

 

切片と傾きの意味

切片は、すべての説明変数が0のときに予測される目的変数の値です。

広告費が0という状態が実際にあり得るなら、切片は基礎売上として解釈できます。

ただし、観測した広告費が10から50の範囲だけである場合、0まで外挿した切片は参考値にとどめる必要があります。

一方で傾きは、広告費が1単位増えたときに売上が平均でどれだけ変化するかを表します。

係数が正なら増加傾向、負なら減少傾向です。

係数が大きくても、それだけで重要な要因とは断定できません。

統計的な有意性と、業務上の影響額の両方を確認しましょう。

 

複数の説明変数を使う場合

広告費に加えて、気温、営業日数、クーポン配布数なども売上に関係する場合は、重回帰分析を使います。

入力X範囲に複数列を指定するだけで、エクセルは各説明変数の係数を個別に出力します。

重回帰式は、Y＝a＋b1X1＋b2X2＋b3X3のような形です。

各係数は、ほかの説明変数の影響を一定としたうえでの関係を示します。

説明変数同士が似た動きをする場合は、係数が不安定になる多重共線性にも注意が必要です。

 

決定係数と補正決定係数の確認

続いては、モデル全体の説明力を表す決定係数と補正決定係数を確認していきます。

回帰統計	値
重相関R	0.965
重決定R2	0.931
補正R2	0.908

 

決定係数の意味

決定係数はR2とも呼ばれ、回帰式が目的変数のばらつきをどの程度説明できているかを示す値です。

値は原則として0から1の範囲で、1に近いほどデータへの当てはまりがよいと考えられます。

R2が0.931なら、売上の変動の約93.1パーセントを広告費によって説明できているという読み方になります。

ただし、決定係数が高いことは因果関係を証明するものではありません。

季節、価格改定、競合状況など、表に入れていない要因が同時に影響している可能性もあります。

 

補正決定係数の役割

説明変数を増やすと、通常の決定係数は不要な変数を追加しても下がりにくい性質があります。

補正決定係数は、説明変数の数とデータ件数を考慮して補正した指標です。

重回帰分析でモデルを比較する際は、通常のR2より補正R2を優先して確認するとよいでしょう。

変数を追加したのに補正R2が下がるなら、その変数はモデル改善に十分寄与していない可能性があります。

 

散布図と近似直線の活用

数値表だけで判断する前に、広告費と売上の散布図を作ると関係性を直感的に把握できます。

二列のデータ範囲を選択し、挿入タブから散布図を選びます。

グラフ上のデータ系列を右クリックし、近似曲線の追加を選択します。

線形を選び、グラフに数式を表示する、グラフにR-2乗値を表示するにチェックを入れましょう。

点が一直線の近くに集まるほど、線形の回帰式がデータを表しやすい状態です。

曲線状に並ぶ、特定の点だけ離れているといった場合は、単純な直線モデルが適切ではないかもしれません。

 

p値と95信頼区間による有意性判断

続いては、係数が偶然のばらつきではないかを判断するp値と95信頼区間を確認していきます。

変数	P-値	下限95%	上限95%
切片	0.012	42.1	122.7
広告費	0.004	2.9	5.6

 

p値の基本的な見方

p値は、その係数が実際には0であると仮定した場合に、今回のような結果が偶然に得られる確率の目安です。

一般的にはp値が0.05未満なら、5パーセント水準で統計的に有意と判断します。

広告費のp値が0.004なら、広告費の係数は0ではないと考える根拠が比較的強いといえます。

p値が小さいことは、効果の大きさを意味するわけではありません。

データ数が非常に多いと、小さな違いでもp値が小さくなることがあります。

 

95信頼区間の読み方

95信頼区間は、係数のもっともらしい範囲を示す指標です。

広告費の係数の下限95パーセントが2.9、上限95パーセントが5.6なら、真の係数はこの範囲にあると推定します。

信頼区間が0をまたがない場合は、通常、係数のp値も0.05未満になります。

範囲が広いときは、データ数が少ない、ばらつきが大きい、説明変数が偏っているなどの原因が考えられます。

 

分散分析表の有意F

エクセルの分散分析表には、有意Fという項目があります。

これは回帰モデル全体に説明力があるかを検定するためのp値です。

単回帰分析では、説明変数のp値と有意Fは同じ結論になりやすいものです。

重回帰分析では、有意Fが小さくても、個別の説明変数にはp値が大きいものが混在する場合があります。

モデル全体の有意性と、各係数の有意性は分けて読む必要があります。

 

残差分析と予測値の検証

続いては、予測値と実測値の差である残差を用いて、回帰式が妥当かを検証していきます。

広告費	実測売上	予測売上	残差
10	125	124.9	0.1
15	149	146.2	2.8
20	167	167.4	-0.4

 

残差の計算方法

残差は、実測値から予測値を引いた値です。

エクセルの出力オプションで残差にチェックを入れると、自動計算された表を確認できます。

自分で作る場合は、予測値の右隣のセルに=C2-D2のような数式を入力し、下方向へコピーします。

残差が正なら実測値が予測値より高く、負なら実測値が予測値より低い状態です。

 

残差グラフの確認

残差グラフでは、横軸に説明変数または予測値、縦軸に残差を配置します。

理想的には、残差が0の上下にランダムに散らばります。

右上がりや右下がりの規則的な傾向がある場合は、直線では表せない関係を見落としている可能性があります。

説明変数が大きくなるほど残差の幅が広がる扇形のパターンは、分散が一定でない状態のサインです。

残差に規則性がある場合は、対数変換、二次項の追加、別の説明変数の検討が候補になります。

 

外れ値と影響の大きいデータ

ほかの点から大きく離れたデータは外れ値と呼ばれます。

入力ミス、集計漏れ、特殊なキャンペーン、臨時休業など、通常と異なる事情が隠れていることがあります。

外れ値を見つけたら、すぐに削除するのではなく、まず元データと発生理由を確認しましょう。

説明変数の端にあり、回帰直線の傾きを大きく動かす点は、影響の大きいデータである可能性があります。

分析対象として残す場合と、別ケースとして扱う場合で、結論が変わることもあります。

 

分析ツールのエラーと対処方法

続いては、回帰分析で表示されやすいエラーと、データ準備の確認方法を解説していきます。

症状	主な原因
入力範囲にエラー	XとYの件数が異なる、文字列が混在している
出力範囲にエラー	出力先に既存データがある
計算できない	データ数不足、説明変数に変化がない

 

入力範囲とラベルの不一致

もっとも多い原因は、入力Y範囲と入力X範囲の行数が異なることです。

たとえばY範囲がC1:C20で、X範囲がB2:B20では、片方だけ見出しを含むため件数が一致しません。

1行目がヘッダーなら、両方とも1行目を含めてラベルにチェックを入れるか、両方とも2行目から選択します。

空白セル、文字列、ハイフン、エラー値が途中に混ざっていないかも確認しましょう。

見た目が数字でも、文字列として保存されていると分析できない場合があります。

 

データ数不足と変数の固定

回帰分析には、説明変数の数より十分に多い観測データが必要です。

説明変数が一つでも、データが数件しかなければ推定は不安定になります。

また、広告費の列がすべて同じ値のように、説明変数に変化がない場合は傾きを計算できません。

重回帰分析では、説明変数を増やし過ぎると自由度が不足し、結果を信頼しにくくなります。

 

出力先と参照エラーの確認

出力範囲を指定した場合、その範囲に既存のデータや結合セルがあると、分析結果を出力できません。

新規ワークシートを選ぶか、十分に空いているセルを出力先として指定しましょう。

数式で予測値を作る場合には、係数の参照セルを固定しないために計算結果がずれることがあります。

F4キーで絶対参照に切り替え、$記号が付いた参照になっているかを確認します。

エラーが起きたときは、データ、範囲、ラベル、出力先の順番で確認すると原因を絞り込みやすいです。

 

まとめ エクセルで回帰分析をするやり方と結果の見方

エクセルの回帰分析では、データ分析の回帰機能を使うことで、説明変数と目的変数の関係を数値で確認できます。

まずは分析ツールを有効化し、1行目にヘッダーを置いたデータ範囲を正しく指定しましょう。

係数から回帰式を作れば、説明変数をもとに目的変数の予測値を計算できます。

決定係数と補正決定係数は当てはまりの目安であり、p値と95信頼区間は係数の確かさを確認する材料です。

残差グラフを確認すれば、直線モデルで問題ないか、外れ値や偏りがないかを判断しやすくなります。

回帰分析は出力表の数字を読むだけで終わらせず、散布図、残差、業務上の意味を合わせて検討することが重要です。

入力範囲のずれ、ラベル設定、空白や文字列の混在、出力先の重複といったエラーも、事前のデータ確認で多くを防げます。

小さなサンプルからでも実際に操作し、予測と実測の差を確かめながら、エクセルの回帰分析を業務に活用していきましょう。