【6つもあった!】Excelで単回帰分析の最小二乗法を解く解法をすべて実演

2021年9月8日

最小二乗法の中でも、単回帰分析は基本中の基本です。

単回帰分析とは1つの説明変数xで、目的変数yを予測するものです。

y=ax+bの中のabの2つのパラメータを最小二乗法で決めます。

その決め方はいろいろあり、Excelでやる方法だけで少なくとも6つはあります。

  1. 公式を使う方法
  2. ソルバーを使う方法
  3. 行列を使う方法
  4. SLOPE & INTERCEPT関数を使う方法
  5. LINEST関数を使う方法
  6. 散布図にトレンドラインを追加する方法

 

1つずつ紹介していきます。

 

今回の例題は次の通りです。

人数
x
処理数
y
17 173
4 60
6 80
10 127
5 63
9 111

このようなデータが得られた時に、人数xから処理数yを推定する回帰式を求めます。

 

公式から求める方法

これは一番正統派で、先生からも褒められる優等生の方法です。

【機械学習の基本】最小二乗法を安全在庫理論と関連付けてわかりやすく解説

でも触れたように、単回帰の最小二乗法は、aとbの2変数の二次関数の最小値を求めることによって解が得られます。

これを式変形で解析的に解くには平方完成をするか、偏微分した後に連立方程式を解かないといけないのですが、答えが簡単に得られる公式が知られています。

それが、

a=(xとyの共分散)/(xの分散)

b=(yの平均)-(xの平均)×a

です。

つまり、この公式は解析的な解法から生まれたもので、近似も何もない正統派の解法です。

 

また、共分散分散も平均もExcel関数にありますので、次のように簡単に求めることができます。

 

回帰式は

y=9.04x+25.50

になります。

 

ソルバーを使う方法

Excelには最適値を計算してくれる「ソルバー」がついています。

最小二乗法には正にこの機能が使えます。

まず、予測値残差残差2残差平方和を求めるために、次のようなシートを作成します。

 

求めるaとbには適当な値を設定しておきます。

後でソルバーが少しずつ変化させながら最適な値を探索してくれます。

ここでは仮にa=b=1としておきましょう。

 

次にデータ >> ソルバーをクリックします。

 

すると設定画面が現れます。

設定する項目は3つです。

まず、目的セルを設定します。

最小二乗法では残差平方和を最小化したいので、残差平方和を選びます。

また目的セルを最小化したいので、目標値は「最小値」をクリックします。

 

またaとbを変えながら目的セルを最小化したいので、変数セルにはabを設定します。

 

その後、「解決」ボタンを押すと、一瞬で次のように解決してくれます。

 

行列を使う方法

この方法が一番難しいかもしれません。

次に出てくる3つはスーパー簡単な方法ですので、行列が苦手な人は次へ飛んで下さい。

 

連立方程式は次のように行列で表すことができます。

そして、両辺の左からXの逆行列を掛けると

X-1XA=X-1Y

ですので、

A=X-1Y

でA、つまりaとbが計算できます。

 

同様に今回の例を見てみましょう。

最小二乗法ではxから予測値f(x)を求める式を作って、yになるべく近づけようとします。

ですので、f(x)とyは完全に一致するわけではないのですが、ほぼ同じです。

従ってニアリーイコールの記号を使って、次のような行列で表すことができます。

 

先ほどのXA=Yと同じような式になったので、両辺に逆行列を掛けてAを求めたいのですが、Xは6×2の行列で正方行列でないため、逆行列が求められません。

このような時にはXの転置行列を掛ければ、XTXは正方行列になります。

XTXA⋍XTY

そして、左からXTXの逆行列を掛ければAを求めることができます。

(XTX)-1XTXA=A⋍(XTX)-1XTY

そして、このAは最小二乗法の解になることが知られています。

従って、この式に従って行列演算をしていけばaとbが求まるはずです。

やってみましょう。

 

Excelで行列の演算をやる方法については、

【行列を使って計算!】多次元正規分布のグラフをエクセルで作成する方法|サンプルファイル付き

で解説してます。

それを使うと、次のように計算できます。

 

この方法でも、

a=9.04

b=25.5

となり、同じ結果が得られました。

 

忙しい人のための時短方法

以上は、ある程度数学を知っていないとできない方法でしたが、もっと簡単に誰でもできる方法もあります。

「始めに言ってくれよ。」

と思うくらい簡単です。

 

SLOPE & INTERCEPT関数を使う方法

傾きと切片が、それぞれSLOPE関数INTERCEPT関数で簡単に求められる方法です。

 

傾き=SLOPE(yの範囲、xの範囲)

切片=INERCEPT(yの範囲、xの範囲)

 

実際には次のように入力します。

 

同じ

y=9.04x+25.50

の回帰式が得られました。

 

LINEST関数を使う方法

LINEST関数だけで、傾きと切片の両方を一発で求める方法です。

 

複数のセルの計算結果を一遍に求める配列数式を使いますので、まずは答えを出したい左右隣り合うセルを選択します。

 

次に、

= LINEST(yの範囲、xの範囲)

を入力します。

 

最後にCtrl+Shift+Enterを押すと、数式が{}で囲まれ、結果が2つのセルに一遍に書き込まれます。

 

同じ

y=9.04x+25.50

の回帰式が得られました。

この方法が一番簡単だと思います。

 

散布図にトレンドラインを追加する方法

これはグラフも一緒に作りたい人には一番手っ取り早い方法です。

まずは次のようにして散布図を作成します。

 

散布図ができたら、グラフの中のどれか1つの点を右クリックして、「近似曲線の追加」を選択します。

 

すると下記のような画面が現われますので、「グラフ数式を表示する」をチェックします。

 

同じ

y=9.0396x+25.496

の回帰式が得られました。

 

以上が会社や学校で必要になるレポート用の解法ですが、機械学習で計算する場合のオンライン用のアルゴリズムは別です。

興味のある方はこちらもどうぞ。

>> 【Excelでわかりやすく】勾配降下法で最小値が見つかる理由を視覚的に理解する

>> 【例題をExcelでわかりやすく】最急降下法で単回帰の最小二乗法を解いてみる