Excelで最小二乗法(近似曲線の求め方)

Excelのグラフと近似曲線
Excelのグラフと近似曲線
POINT

  • Excelの近似曲線の式を関数で求め,任意の点の値を計算する方法.
  • ①曲線の係数を得る関数と,②曲線上の値を得る関数がある.
  • 標準誤差や決定係数($R^{2}$)を取得することも可能.

Excelの関数を使って,最小二乗法による回帰分析を行う方法 (グラフの「近似曲線」を関数で求める方法)を紹介します.

関数を使うことによって

  1. グラフで近似曲線とその数式を表示する(上図)
  2. 係数をコピペする
  3. 任意の点の値を求める

という一連の作業が不要になります.

【関連記事】

多項式の場合

次の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}
を最小二乗法で決定することを考えます.このとき,

  • 係数$\{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}

1変数多項式

上で$x_{i}=x^{i}$の場合には,用意するデータを簡略化することができます.
即ち,以下のようなデータ系列を用意しておけば,

$y$ $x$
$\vdots$ $\vdots$

\begin{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}
(注:色付きの部分が上で紹介した,多項式の場合の式と異なっています.)

例えば,$N=2$の場合は下図の2次近似式を求めることに相当します:

多項式近似
多項式近似の例

指数関数の場合

指数関数
\begin{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}

標準誤差や決定係数を取得する方法

上で紹介した関数は,配列を返します.この配列の中には標準誤差や決定係数も含まれており,INDEX関数を使うことでこれらの値を取得することができます.

関数

  • LINEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,$[定数],$\,$[補正])
  • LOGEST($y$のデータ列,$\,x_{1}\sim x_{N}$のデータ列,$\,$[定数],$\,$[補正])
は以下の配列を返します(詳しくは,参考記事にあるMicrosoftのページを参照してください).

※ [定数]を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}$

従って,他の値も同様に,INDEX関数で取得することができます.

参考記事