【エクセル回帰分析結果の見方】出力される４つの表をすべて検証してみた。
回帰分析はエクセルで一瞬にして計算され、素晴らしい結果表が出力されますが、理解するのが難しい項目も含まれていますね。
「観測された分散比」や「合計」などは英語を日本語に翻訳する段階で意味が分かりにくくなっています。
また「変動」や「残差」などは定義がはっきりしません。
こういう時には、同じ結果になるように自分で計算してみれば意味がはっきりするでしょう。
そこで、すべての項目を自分で計算して確かめてみました。
同じような疑問を持っている人も多いと思いますので、結果を公開します。
理解の足しにして下さい。
エクセル分析ツールで回帰分析の結果表を出力する
物流センターにおけるピッキング作業への投入人数と、ピッキング処理数のデータを使いました。
12日間の結果は次の通りです。
| 投入人数 | 処理数 |
| 69 | 1,324 |
| 57 | 1,110 |
| 51 | 866 |
| 48 | 854 |
| 48 | 773 |
| 61 | 937 |
| 41 | 773 |
| 41 | 629 |
| 53 | 987 |
| 45 | 831 |
| 56 | 971 |
| 48 | 740 |
散布図はこのようになります。
比較的、相関は高そうです。
そこで、投入人数から処理数を推測する回帰式を求めることを考えます。
まずは、回帰分析の結果を出してみましょう。
データ＞データ分析をクリックします。
続いて出てくるポップアップ画面で「回帰」を選択した後、OKをクリックします。
続いて出てくるポップアップ画面ではデータの範囲を選択します。
投入人数から処理数を推測したいので、投入人数が説明変数x、処理数が目的変数yになります。
それぞれを選択した後、「残差」も選択してOKをクリックします。
すると、次のような結果表ができますので、これについて一つひとつ計算して意味を理解していきます。
基本統計量を求めておく
基本統計量
まず元データについて平均、標準偏差、分散、偏差平方和、平方和の基本統計量を、下図のように求めておきましょう。
続いてy偏差、x偏差、積和、残差の二乗も、それぞれのデータにつき求めておきます。
回帰の統計量
そして、これらの統計量を元に回帰の統計量を計算します。
これを【図1】とします。
後で出てきます。
傾きと切片
これは前回の記事
相関｜回帰｜分散｜共分散｜標準偏差の関係をまとめて直観的に理解する
で解説したように、次の式で計算できます。
傾き＝積和／xの偏差平方和＝Σ(x-x̄)(y-ȳ)／Σ(x-x̄)2
切片＝ȳ－傾き×x̄
x̄とȳは、それぞれxとyの平均です。
残差分散
回帰式とはxからyを推測する式です。
あくまで推測ですので、観測されたyとは誤差があります。
これを残差と言います。
残差の分散とは、全観測データ（この場合12個）についての残差の二乗を足して自由度で割ったものです。
回帰分析での自由度はN-k-1になります。
Nは観測データの数でこの例では12、kは説明変数の数でこの例ではxだけなので1です。
ですので自由度は10になり、残差の二乗を10で割っています。
従って残差分散は次式で計算できます。
σ2= Σ(y-(βx+α))2 ／(N-k-1)
傾きと切片の標準誤差
回帰直線の傾きと切片は先ほどの式で計算できますが、この式は一次関数の式のように唯一無二のものではありません。
観測されたデータから最も当てはまりが良さそうな式を選んだに過ぎません。
一次関数のように100%の確信がある式なのか、あまり相関関係はないのだけれども仕方なしに作った式なのか、式を見ただけでは見分けが付きませんね。
そこで、この確信度を表すために、標準誤差を使います。
回帰直線の傾きを求めるには、次式のように積和をxの偏差平方和で割りました。
傾き＝積和／xの偏差平方和＝Σ(x-x̄)(y-ȳ)／Σ(x-x̄)2
積和はx方向、y方向の偏差を合成した情報を持っているので、それをx方向の偏差で割るとy方向の偏差が出てきます。
それはつまりxが平均から少し動くと、それにつれてyがどれくらい動くかを表すので傾きになります。
回帰式で推測した結果と観測値との差は先に出てきた残差分散で計算できました。
この残差分散は、xとyの残差の情報を集約していると考えられます。
従って、傾きと同じようにxの偏差平方和で割ると、y方向の残差が求まります。
この平方根が傾きの標準誤差で、次式で計算できます。
傾きの標準誤差＝√（残差分散／xの偏差平方和）＝√(Σ(y-(βx+α))2 ／Σ(x-x̄)2)
また切片の標準偏差は次式で計算できることが知られています。
切片の標準誤差＝√（残差分散／データ数×xの平方和／xの偏差平方和）
＝√(Σ(y-(βx+α))2 ／(N-k-1)×Σx2/Σ(x-x̄)2)
回帰分析の４つの結果表を検証する
それでは前準備ができましたので、先ほど出力した結果表の項目を一つずつ検証していきましょう。
３番目の表
まず、4つある表のうちの3番目、「傾き」と「切片」の表から見ていきます。
傾きと切片の係数とは、回帰式
ŷ=βx+α
におけるβとαのことです。
ŷは回帰式でxの値から推定したyの値のことで、yハットと読みます。
これは先ほど求めた傾きと切片のことです。
また傾きと切片の標準誤差も、先ほど求めた値と一致していることが分かります。
【図1】と見比べてみて下さい。
「t」とはt値のことで、傾き、切片ともに係数÷標準誤差になっています。
これは係数とゼロが標準誤差何個分離れているかを意味します。
例えば傾きの係数βは約20で標準誤差は約3なので、標準誤差約7個分離れていることが分かります。
t値についてもっと知りたい方はこちらも参考にしてみて下さい。
>> 【対応がある2郡】改善効果をt検定で検証する方法を具体例でわかりやすく解説します。
「P値」とはt値に対応する確率のことです。
Pは確率Probabilityの頭文字です。
P値とt値の対応はt分布表を見れば分かりますが、エクセル関数でも計算できます。
＝T.DIST（t値、自由度、TRUE）
を使って上記のように計算できます。
今回の例では傾きのP値は0.0056%なので、とても小さいですね。
これは、傾きが0である確率は極めて小さいことを意味します。
つまり、傾きは0ではなくて20.27570093と考えてほぼ間違いないということです。
通常は5%より小さければ有意差あり、つまり傾きの係数には意味があると判断します。
「下限95%」と「上限95%」は95%信頼区間の下限と上限のことです。
下限は
係数－標準誤差×下側2.5%点
上限は
係数＋標準誤差×上側2.5%点
で求まります。
信頼度95%ということは両側2.5%を除くということなので、下側、上側ともに2.5%点になります。
これもエクセルにあるt分布の逆関数で求められますが、自由度がN-k-1＝10になることに注意です。
＝T.INV（確率、自由度）
傾きの係数βは20.27570093と計算されていますが、これは100%の確信がある値ではなく、
13.49979111から27.05161076の間であることが95%の確信度でいえるということです。
４番目の表
次に、一番下に表示される「残差出力」の表を見てみましょう。
赤で囲んだ箇所が自動で出力された表で、それ以外は管理人が付け加えた箇所です。
表の中の「予測y」は、回帰式ŷ=βx+αから計算されるŷのことです。
「残差」は観測値yとŷの差のことで、y-ŷで計算できます。
「標準誤差」は各残差を残差の標準偏差で割った値で、残差が標準偏差何個分かを表します。
「回帰」は回帰式で推測値ŷとŷの平均値ŷ̄との差のことです。
ŷ̄はȳと等しくなります。
「合計」は観測値yとyの平均ȳとの差のことで、次式が成り立ちます。
残差＋回帰＝y-ŷ + ŷ-ŷ̄＝y-ŷ + ŷ-ȳ＝y-ȳ＝合計
これは何を言っているかというと、各観測値yと平均値ȳの差（合計）は、回帰式による誤差（回帰）と回帰式に関係ない残差（残差）の和になるということです。
１番目の表
次に一番上の表、概要を見てみましょう。
「重相関R」とは相関係数のことです。
相関係数＝共分散／(xの標準偏差×yの標準偏差)
で計算できます。
先のシートに基本統計量がありますので、そのシートで計算しましょう。
共分散は√（積和÷自由度）ですが、xとyの標準偏差は今回は不偏標準偏差で計算していますので、自由度を一つ落としたN-1、つまり11で計算していることに注意です。
不偏標準偏差について曖昧な方はこちらも読んでみて下さい。
>> 標準偏差はnで割るの？n-1で割るの？【誰もが一度は疑問を持つ】
「重決定R2」は決定係数のことで、次式で定義されます。
決定係数＝１－（残差の平方和／合計の平方和）
先ほど
残差＋回帰＝合計
であることを説明しましたが、決定係数は全体のばらつきのうち回帰のばらつきがどれくらいの割合を占めるかを表す指標です。
この指標の値が大きいほど、回帰式の精度が高いことを意味します。
また、
決定係数＝相関係数2
という性質があります。
R2という名称はここから来ています。
「補正R2」は自由度を調整した決定係数です。
どういうことかというと、決定係数は回帰式の精度を見るための指標ですが、説明変数の数が増えるだけでも大きな値になるという性質があります。
これでは公平な指標になりませんので、自由度を使って調整します。
自由度調整済み決定係数は次式で定義されます。
自由度調整済み決定係数＝１－{残差の平方和／(N-k-1)}／(合計の平方和／N-1)
「標準誤差」は残差の平方和を自由度N-k-1で割って平方根を取った値です。
２番目の表
最後に分散分析表を見てみましょう。
「回帰」、「残差」、「合計」それぞれの「変動」とは、それぞれの平方和のことです。
そして「分散」はそれぞれの平方和を自由度で割った値です。
「観測された分散比」とは、回帰による分散と残差の分散との比のことです。
回帰式によって推測されるyの範囲と、計算式では推定しようがない残差の範囲を比較しているので、前者が後者に対して十分に大きくないと回帰式の意味があまりないことになります。
これを判定するために、両者の差がないという仮説（帰無仮説）を置いたF検定を行います。
F検定についてもっと知りたい方はこちらも読んでみて下さい。
>> 【具体例でわかりやすく解説！】F検定は何に使えるの？｜商品分類の仕方で実演
観測された分散比とは、F検定におけるF値のことです。
「有意F」とはF値に対する確率、すなわちP値のことです。
これはエクセルにあるF分布の関数F.DISTで求められます。
＝F.DIST（F値、分子の自由度、分母の自由度、TRUE）
この例ではP値が0.0056%ですので、回帰式によって推定されるyの範囲と、計算式では推定しようがない残差の範囲が同じである確率は極めて低い、つまり誤差は十分に小さく、回帰式の精度は高いという意味になります。
以上、結果表の項目をすべて検証できました。
Udemyの関連講座
いちばん理解できる統計学ベーシック講座その２【相関分析・回帰分析】
統計学の基礎を学べるベーシック講座「その２」です。統計学を用いたデータ分析の基礎となる「相関分析・回帰分析」について豊富な図を用いて説明していきます。相関分析や回帰分析という一生モノのスキルをぜひこの機会に一緒に身につけましょう！
【データ分析入門（最初の3日）】文系の方や統計・データサイエンスにつまずいた方も実務で使える（考え方とExcel活用）
データ分析の流れ（課題を捉え、分析し、結果を伝える）を通して理解します。演習では架空の会社を想定したデータで、エクセルを利用してグラフ作成・記述統計・回帰分析を行い最終報告までのスキルを身につけます。社内データ分析者向けの入門講座です。
【ゼロから始めるデータ分析】 ビジネスケースで学ぶPythonデータサイエンス入門
分析コンペティションに参加しながら回帰分析による売上予測、機械学習での顧客ターゲティングなど実践的なビジネス課題でデータ分析の一連の流れを身に着けよう。 プログラミング初心者にもおすすめ。