【6つの方法を実演！】Excelで最小二乗法を計算する方法をわかりやすく
Excelで最小二乗法を計算する方法は６通りもある！
最小二乗法の中でも、単回帰分析は基本中の基本です。
単回帰分析とは1つの説明変数xで、目的変数yを予測するものです。
y＝ax＋bの中のaとbの2つのパラメータを最小二乗法で決めます。
その決め方はいろいろあり、Excelでやる方法だけで少なくとも6つはあります。
- 公式を使う方法
- ソルバーを使う方法
- 行列を使う方法
- SLOPE & INTERCEPT関数を使う方法
- LINEST関数を使う方法
- 散布図にトレンドラインを追加する方法
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を変えながら目的セルを最小化したいので、変数セルにはaとbを設定します。
その後、「解決」ボタンを押すと、一瞬で次のように解決してくれます。
行列を使う方法
この方法が一番難しいかもしれません。
次に出てくる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でわかりやすく】最急降下法で単回帰の最小二乗法を解いてみる