mengineer's blog

ニッチなネタばかりですが。

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

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

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

f:id:mengineer:20160530200437p:plain

グラフ上で右クリック → 近似曲線の追加で下記のように設定すると f:id:mengineer:20160530200600p:plain

近似式とR2の値が表示されます。 f:id:mengineer:20160530201329p:plain

このR2の値(0.9947)が欲しいのですが、その都度Excelを立ち上げて
グラフを作るのも面倒臭いし、図なので計算結果のコピーなども出来ません。

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

そもそもR2とは?

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

相関係数 - Wikipedia

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

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

決定係数 - Wikipedia

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

LINEST関数 線形近似

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

support.office.com

まずは(簡単な)線形近似で検証します、最初のサンプルデータだと下記。 f:id:mengineer:20160530220725p:plain

近似式 { y = ax + b} で、傾きaは9.6、切片bが1の近似式、R2は0.9944となりました、
LINEST関数を使う場合は、元データが下記のようにA列、B列に有るとして、
f:id:mengineer:20160530222257p:plain

傾き 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関数で個々の値を取得します。

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

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

f:id:mengineer:20160530225710p:plain

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

LINEST関数 多項式近似(二次)

今回欲しいのは多項式近似(二次)でのR2です、
この場合、LINEST関数に渡す[既知のx]に、 { x^2} を追加します。

f:id:mengineer:20160530231609p:plain

近似式 { y = ax^2 + bx + c} として

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列を指定していることだけです。

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

実際のデータ

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

f:id:mengineer:20160531085551p:plain

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

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