Excel 多項式近似でR2の値を取得する

ここのところ、Excelの多項式近似で悩んでいた件のメモ。

例えば、下記サンプルデータとグラフが有るとします。

グラフ上で右クリック　→　近似曲線の追加で下記のように設定すると

近似式とR²の値が表示されます。

このR²の値だけ（0.9947）欲しいですが、その都度Excel使うのも面倒、　　
そもそもグラフなので、そこから数値だけコピーするのも厄介です。

もっとシンプルにR²だけを計算する方法は無いか？、というのが今回のお題。

そもそもR²とは？

Rは相関係数で、その名の通り二つのデータ間の相関を示します。

相関係数 - Wikipedia

R²は相関係数の二乗で決定係数とも言い、データと回帰式（近似計算式）が、
いかに近いかを示す数値です、1.0に近いほど正確に近似出来ているらしい。

当初、ExcelのR²の計算方法を調べ、それと同じ計算式を実装しようと思いましたが、
実は決定係数には定義が8種類有り、計算方法も色々有ることが判明。（下記参照）

決定係数 - Wikipedia

よって今回はそのままExcelベースで、より簡単に R²を取得出来る方法を調査します。
（Excelがどの方法なのか調べるのも大変だし、それが目的でも無いので）

2017/9/4　追記

後日、Excelを使わずに計算する方法も検討しました、下記参照

mengineer.hatenablog.com

LINEST関数 線形近似

結論から言うと、EXCELのLINEST関数を使えば良いようです。

support.office.com

まずは（簡単な）線形近似で検証します、最初のサンプルデータだと下記。

近似式 で、傾きaは9.6、切片bが1の近似式、R²は0.9944となりました。
（Excelグラフ上）

これをLINEST関数から求める場合、元データが下記のようにA列、B列に有るとして、

傾き a=INDEX(LINEST(B2:B6, A2:A6), 1) 
切片 b=INDEX(LINEST(B2:B6, A2:A6), 2) 
決定係数 R2=INDEX(LINEST(B2:B6, A2:A6, TRUE, TRUE), 3, 1) 

で計算出来ます、LINEST関数自体の書式は下記

LINEST(既知の y, [既知の x], [定数], [補正])

グラフのY軸のデータが先なので気をつけて下さい、傾きと切片だけなら、
[定数]と[補正]は省略可能です、LINEST関数の戻り値は配列になっており、
一つ目が傾き、二つ目が切片なので、更にINDEX関数で個々の値を取得します。

R²を計算したい場合は、[定数]と[補正]ともに”TRUE"を指定します。

この場合、戻り値は補正情報が加わった二次元配列になります、
データの並びは下記の通り（上記のLINEST関数の説明ページに説明有り）

三行目の一列目がR²なので、INDEXで3,1を指定することで取得出来ます。

LINEST関数 多項式近似（二次）

今回欲しいのは多項式近似（二次）でのR²です、
この場合、LINEST関数に渡す[既知のx]に、 を追加します。

近似式 として

a=INDEX(LINEST(C2:C6, A2:B6), 1) 
b=INDEX(LINEST(C2:C6, A2:B6), 2) 
c=INDEX(LINEST(C2:C6, A2:B6), 3) 
R2=INDEX(LINEST(C2:C6, A2:B6, TRUE, TRUE), 3, 1) 

線形近似との違いは、[既知のX]でAとBの2列を指定していることだけです。

判ってしまえば簡単な話でしたが、実はここに気付くまで結構手こずりました....
三次の多項式なら、更に を追加すれば良いはずです。

実際のデータ

ここまでは説明用として、データ数が5個の簡単なサンプルを使用しましたが、
今回、実際に使用したいのは、下記のような4096個のデータです。

これまでのBLOGのネタからして、勘の良い方なら気付かれたかもしれませんが、
カメラの画像データのグラフですね。（水平方向、8bit）

次は、この画像データを取得する部分のソフトから上記のExcelの関数を呼んで、
一発でR²を取得出来るようにしますが、長くなりましたので次回へと続く。