POINT
- Excelの近似曲線の式を関数を使って計算する方法と，任意の点の値を求める方法について．
- ①曲線の係数を得る関数と，②曲線上の値を得る関数がある．
- 標準誤差や決定係数（$R^{2}$）を取得することも可能．
Excelの関数を使って，最小二乗法による回帰分析を行う方法 （グラフの「近似曲線」を関数で求める方法）を紹介します．
関数を使うことによって
- グラフで近似曲線とその数式を表示する（上図）
- 係数をコピペする
- 任意の点の値を求める
という一連の作業が不要になります．
【関連記事】
多項式の場合
次の2つの関数で求めることができます：- LINEST関数：各項の係数を返す関数．
- TREND関数：回帰曲線上の値を求める関数．
多変数多項式
以下の形式のデータがあるとき，| $y$ | $x_{1}$ | $\cdots$ | $x_{N}$ |
|---|---|---|---|
| $\vdots$ | $\vdots$ | $\vdots$ | $\vdots$ |
多項式
\begin{aligned}
y&=f(x_{1}, ... , x_{N})\\
&=\sum_{i=1}^N m_{i} x_{i} + m_{0}
\end{aligned}
を最小二乗法で決定することを考えます．このとき，
y&=f(x_{1}, ... , x_{N})\\
&=\sum_{i=1}^N m_{i} x_{i} + m_{0}
\end{aligned}
- 係数$\{m_{i}\}_{i=0}^{N}$
- $f(x_{1}, x_{2} ,..., x_{N})$の値
は，以下で求めることができます：
多項式の場合
\begin{aligned}
m_i&=\text{INDEX$\bigl($ LINEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列),$\, 1$,$\, N-i+1\bigr)$}\\
f(x_{1}, x_{2}, ..., x_{N})&=\text{TREND$\bigl(\,y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,(x_{1}, x_{2}, ..., x_{N})\,\bigr)$}
\end{aligned}
m_i&=\text{INDEX$\bigl($ LINEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列),$\, 1$,$\, N-i+1\bigr)$}\\
f(x_{1}, x_{2}, ..., x_{N})&=\text{TREND$\bigl(\,y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,(x_{1}, x_{2}, ..., x_{N})\,\bigr)$}
\end{aligned}
例を作成しました：
1変数多項式
上で$x_{i}=x^{i}$の場合には，用意するデータを簡略化することができます．即ち，以下のようなデータ系列を用意しておけば，
| $y$ | $x$ |
|---|---|
| $\vdots$ | $\vdots$ |
\begin{aligned}
y&=f(x)\\
&=\sum_{i=0}^{N} m_{i} x^{i}
\end{aligned}
に関する計算は，以下の式で行うことができます：y&=f(x)\\
&=\sum_{i=0}^{N} m_{i} x^{i}
\end{aligned}
1変数多項式の場合
\begin{aligned}
m_{i}
&=\text{INDEX}
(\,\text{LINEST}(y\text{のデータ列}, \textcolor{red}{x\text{のデータ列\textasciicircum} \{1,2,\cdots,N\}} ), 1 , N-i+1) \\
f(x)&=\text{TREND} \bigl(y\text{のデータ列}, \textcolor{red}{x\text{のデータ列\textasciicircum} \{1,2,\cdots,N\}} ,\textcolor{blue}{x\text{\textasciicircum} \{1,2,\cdots,N\} } \,\bigr)
\end{aligned}
m_{i}
&=\text{INDEX}
(\,\text{LINEST}(y\text{のデータ列}, \textcolor{red}{x\text{のデータ列\textasciicircum} \{1,2,\cdots,N\}} ), 1 , N-i+1) \\
f(x)&=\text{TREND} \bigl(y\text{のデータ列}, \textcolor{red}{x\text{のデータ列\textasciicircum} \{1,2,\cdots,N\}} ,\textcolor{blue}{x\text{\textasciicircum} \{1,2,\cdots,N\} } \,\bigr)
\end{aligned}
例えば，$N=2$の場合は下図の2次近似式を求めることに相当します：
指数関数の場合
指数関数\begin{aligned}
y&=f(x_{1}, ..., x_{N})\\
&=m_0\cdot \prod_{i=1}^{N} m_{i}^{x_i}
\end{aligned}
を最小二乗法で決定することを考えます．このとき，
y&=f(x_{1}, ..., x_{N})\\
&=m_0\cdot \prod_{i=1}^{N} m_{i}^{x_i}
\end{aligned}
- 係数$\{m_{i}\}_{i=0}^N$
- $f(x_{1}, x_{2}, ..., x_{N})$の値
は，以下で求めることができます：
指数関数の場合
\begin{aligned}
m_i&=\text{INDEX$\bigl($ LOGEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列),$\, 1$,$\, N-i+1\bigr)$}\\
f(x_{1}, x_{2}, ..., x_{N})&=\text{GROWTH$\bigl(\,y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,(x_{1}, x_{2}, ..., x_{N})\,\bigr)$}
\end{aligned}
m_i&=\text{INDEX$\bigl($ LOGEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列),$\, 1$,$\, N-i+1\bigr)$}\\
f(x_{1}, x_{2}, ..., x_{N})&=\text{GROWTH$\bigl(\,y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,(x_{1}, x_{2}, ..., x_{N})\,\bigr)$}
\end{aligned}
標準誤差や決定係数を取得する方法
上で紹介した関数は，配列を返します．この配列の中には標準誤差や決定係数も含まれており，INDEX関数を使うことでこれらの値を取得することができます．関数
- LINEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,$[定数],$\,$[補正])
- LOGEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,$[定数],$\,$[補正])
※ [定数]をFALSEにすると，LINEST関数では$m_0=0$, LOGEST関数では$m_0=1$となる．省略はTRUE扱い．
※ [補正]をTRUEにすると，追加情報（下の2行目以降）を返す．省略はFALSE扱い．
| $m_{N}$ | $m_{N-1}$ | $\cdots$ | $m_{1}$ | $m_{0}$ |
| $se_{N}$ | $se_{N-1}$ | $\cdots$ | $se_{1}$ | $se_{0}$ |
| $R^{2}$ | $se_y$ | |||
| $F$ | $d_f$ | |||
| $ss_{reg}$ | $ss_{resid}$ |
