Notes_JP

あまり知られていないこと

【Excel】最小二乗法による回帰分析 (近似曲線の求め方)

Excelの関数を使って,最小二乗法による回帰分析を行う方法を紹介します.

【最小二乗法に馴染みのない人へ】
・Excelのグラフの「近似曲線」を,関数を使って求める方法です.

多項式の場合

以下の形式のデータがあるとき,

$y$ $x_1$ $\cdots$ $x_N$
$\vdots$ $\vdots$ $\vdots$ $\vdots$

多項式
\begin{align}
y&=f(x_1,...,x_N)\\
&=\sum_{i=1}^N m_i x_i+m_0
\end{align}
を最小二乗法で決定することを考えます.このとき,

  • 係数$\{m_i\}_{i=0}^N$
  • $f(x_1,x_2,...,x_N)$の値

は,以下で求めることができます:

多項式の場合
\begin{align} 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{align}

1変数多項式の場合

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

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

\begin{align}
y&=f(x)\\
&=\sum_{i=0}^N m_i x^i
\end{align}
に関する計算は,以下の式で行うことができます:

1変数多項式の場合
\begin{align} m_i&=\text{INDEX( LINEST($y$のデータ列,$\color{red}{\text{$\,x$のデータ列^{$1,2,\cdots,N$}}}$),$\, 1$,$\, N-i+1$)}\\ f(x)&=\text{TREND$\bigl(\,y$のデータ列,$\color{red}{\text{$\,x$のデータ列^{$1,2,\cdots,N$}}}$,$\color{red}{\text{$\,x$^{$1,2,\cdots,N$}}}\,\bigr)$} \end{align}

指数関数の場合

指数関数
\begin{align}
y&=f(x_1,...,x_N)\\
&=m_0\cdot \prod_{i=1}^N m_i^{x_i}
\end{align}
を最小二乗法で決定することを考えます.このとき,

  • 係数$\{m_i\}_{i=0}^N$
  • $f(x_1,x_2,...,x_N)$の値

は,以下で求めることができます:

指数関数の場合
\begin{align} 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{align}

関数の詳細:標準誤差や$R^2$値を取得する方法

LINEST関数/LOGEST関数

参考のMicrosoftのサイトにあるように,


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}$

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