Notes_JP

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

f:id:IsThisAPen:20171127202454p:plain:w300


  • Excel関数で回帰分析を行う方法の紹介.
  • 係数を求める関数と,回帰曲線上の値を求める関数がある.
  • 標準誤差や決定係数 ($R^2$) を取得することも可能.



この記事では,

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

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

参考の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関数で取得することができます.

プライバシーポリシー