【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でわかりやすく】最急降下法で単回帰の最小二乗法を解いてみる
Udemyの関連講座
いちばん理解できる統計学ベーシック講座その２【相関分析・回帰分析】
統計学の基礎を学べるベーシック講座「その２」です。統計学を用いたデータ分析の基礎となる「相関分析・回帰分析」について豊富な図を用いて説明していきます。相関分析や回帰分析という一生モノのスキルをぜひこの機会に一緒に身につけましょう！
【データ分析入門（最初の3日）】文系の方や統計・データサイエンスにつまずいた方も実務で使える（考え方とExcel活用）
データ分析の流れ（課題を捉え、分析し、結果を伝える）を通して理解します。演習では架空の会社を想定したデータで、エクセルを利用してグラフ作成・記述統計・回帰分析を行い最終報告までのスキルを身につけます。社内データ分析者向けの入門講座です。
【ゼロから始めるデータ分析】 ビジネスケースで学ぶPythonデータサイエンス入門
分析コンペティションに参加しながら回帰分析による売上予測、機械学習での顧客ターゲティングなど実践的なビジネス課題でデータ分析の一連の流れを身に着けよう。 プログラミング初心者にもおすすめ。