本稿では、Excelでn次関数の係数を計算する方法について、解説する。
n次関数の係数の計算方法
n次関数の係数は、LINEST関数とINDEX関数とを組み合わせて計算する。
1次関数から3次関数、n次関数について、以下に示す。
■1次関数
1次関数 \(y=c_1x+c_2x\)の回帰曲線の係数は以下の式で求める。
\(\quad INDEX(LINEST(Y,X\)^\(\){\(1\)}\(),1,m)\)
\(\quad X: 既知のx\)のデータの組
\(\quad Y: 既知のy\)のデータの組
\(\quad m: \)1,2で係数\(c_1,c_2\)にそれぞれ対応。
1次関数については、SLOPE関数とINTERCEPT関数を用いて、求めることもできる。
\(\quad 傾きSLOPE(Y,X)\)
\(\quad 切片INTERCEPT(Y,X)\)
\(\quad X: 既知のx\)のデータの組
\(\quad Y: 既知のy\)のデータの組
1次関数は、こちらの方が簡単ですね。
■2次関数
2次関数 \(y=c_1x^2+c_2x+c_3\)の回帰曲線の係数は以下の式で求める。
\(\quad INDEX(LINEST(Y,X\)^\(\){\(1,2\)}\(),1,m)\)
\(\quad X: 既知のx\)のデータの組
\(\quad Y: 既知のy\)のデータの組
\(\quad m: \)1,2,3で係数\(c_1,c_2,c_3\)にそれぞれ対応。
■3次関数
3次関数 \(y=c_1x^3+c_2x^2+c_3x+c_4\)の回帰曲線の係数は以下の式で求める。
\(\quad INDEX(LINEST(Y,X\)^\(\){\(1,2,3\)}\(),1,m)\)
\(\quad X: 既知のx\)のデータの組
\(\quad Y: 既知のy\)のデータの組
\(\quad m: \)1,2,3,4で係数\(c_1,c_2,c_3,c_4\)にそれぞれ対応。
ここで、Worksheet関数のの意味は以下になる。
\(LINEST(Y,X\)^\(\){\(1,2,3\)}\()\)は、3次関数の係数を{\(c_1,c_2,c_3,c_4\)}の配列として返す。
\(INDEX(範囲(配列),行番号,列番号)\)は、配列の各要素を取り出す。
■n次関数
n次関数 \(y=c_1x^n+c_2x^{n-1}+…+c_{n+1}\)の回帰曲線の係数は以下の式で求める。
\(\quad INDEX(LINEST(Y,X\)^{\(1,2,…,n\)}\(),1,m)\)
\(\quad X: 既知のx\)のデータの組
\(\quad Y: 既知のy\)のデータの組
\(\quad m: 1,2,…,n+1\)で係数\(c_1,c_2,…,c_{n+1}\)にそれぞれ対応。
1～3次関数の計算および既知のデータとの相関
既知の\(x\)のデータ、既知の\(y\)のデータに対して、1～3次関数の係数を求めた。
さらに、1～3次関数の\(y\)を計算し、既知の\(y\)との相関係数をそれぞれ計算して、データとグラフを以下に示した。
なお、相関係数は以下の式で求めた。
\(\quad CORREL(Y,Y_n)\)
\(\quad Y: 既知のy\)のデータの組
\(\quad Y_n: n次関数のy\)のデータの組
グラフと相関係数から、1次から3次へ次数が大きくなるほど、既知の値と相関性が高くなることが分かる。
