Excelでの最小二乗法・グラフ作成がめんどくさい方へ

Excelでのグラフ作成、めんどくさくないですか

物理学実験や化学実験のレポートを書くとき、必ずと言っていいほど求められるのが**「最小二乗法を用いたグラフの作成」**です。多くの学生はMicrosoft Excelでデータ処理を行いますが、次のような経験はないでしょうか。

散布図を出して、近似曲線を追加して、数式を表示して…と操作手順が多すぎる
各データ点にX軸・Y軸のエラーバー（誤差棒）を個別につけるのが面倒
有効数字を揃えたり、グラフの体裁をレポートの規定（白黒・グリッドなし等）に合わせる設定が細かい
そもそも傾きの誤差（標準誤差）の出し方が分からない

最後の項目が特に厄介です。近似曲線の機能では傾きと切片は表示できても、その不確かさは出てきません。

この記事では、まずExcelできちんとやる方法を説明し、そのうえで毎回の手間を省く方法を紹介します。

Excelで最小二乗法を行う3つの方法

方法1：グラフの「近似曲線」機能

散布図を作り、データ点を右クリックして「近似曲線の追加」→「線形近似」を選び、「グラフに数式を表示する」「グラフにR-2乗値を表示する」にチェックを入れます。

手軽ですが、表示される桁数が制御しにくく、標準誤差も出ません。レポートに貼るには物足りない方法です。

方法2：関数で個別に求める

セルに直接入力する方法です。 $x$ のデータが A2:A6、 $y$ のデータが B2:B6 にあるとします。

求めたいもの	Excelの関数
傾き $a$	`=SLOPE(B2:B6, A2:A6)`
切片 $b$	`=INTERCEPT(B2:B6, A2:A6)`
決定係数 $R^2$	`=RSQ(B2:B6, A2:A6)`
相関係数 $r$	`=CORREL(B2:B6, A2:A6)`
推定値の標準誤差	`=STEYX(B2:B6, A2:A6)`

引数の順番が「 $y$ が先、 $x$ が後」である点に注意してください。逆にすると別の直線が出てしまいますが、エラーにはならないので気づきにくい間違いです。

方法3：LINEST関数で標準誤差まで一度に求める

傾き・切片の標準誤差まで出したいなら、LINEST を使います。5行2列の範囲を選択してから次を入力し、配列数式として確定します（Excelのバージョンによっては Ctrl+Shift+Enter）。

=LINEST(B2:B6, A2:A6, TRUE, TRUE)

返ってくる値の配置は次のとおりです。

	左の列	右の列
1行目	傾き $a$	切片 $b$
2行目	$a$ の標準誤差	$b$ の標準誤差
3行目	決定係数 $R^2$	推定値の標準誤差
4行目	$F$ 値	自由度
5行目	回帰平方和	残差平方和

2行目が、レポートで「 $a = 1.990 \pm 0.060$ 」と書くときの $\pm$ の値です。第4引数の TRUE を忘れると1行目しか返ってこないので注意してください。

そもそも最小二乗法とは（原理）

最小二乗法は、測定した点 $(x_i, y_i)$ に対して、各点と直線との縦方向のずれ（残差）の二乗和が最小になるように、直線 $y = ax + b$ の傾き $a$ と切片 $b$ を決める方法です。

$S = \sum_{i=1}^{n} \left( y_i - (a x_i + b) \right)^2$

この $S$ を最小にする条件 $\dfrac{\partial S}{\partial a} = 0$ 、 $\dfrac{\partial S}{\partial b} = 0$ を解くと、次の式が得られます。

$a = \frac{n \sum x_i y_i - \sum x_i \sum y_i}{n \sum x_i^2 - \left(\sum x_i\right)^2}, \quad b = \frac{\sum y_i - a \sum x_i}{n}$

なぜ「絶対値の和」ではなく「二乗の和」なのかというと、二乗にすることで微分が可能になり、正負のずれが打ち消し合わず、大きく外れた点にペナルティを与えられるためです。

また、この式は縦方向のずれだけを最小化している点に注意してください。 $x$ にも誤差がある場合は厳密には別の手法（直交回帰など）が必要ですが、学生実験では $x$ の誤差が十分小さいとみなして通常の最小二乗法を使うのが一般的です。

手計算で追える計算例

次の5点で実際に計算してみます。

$x$	1.0	2.0	3.0	4.0	5.0
$y$	2.1	3.9	6.2	7.8	10.1

必要な和を求めます。

$n = 5$
$\sum x_i = 15$ 、 $\sum y_i = 30.1$
$\sum x_i y_i = 110.2$ 、 $\sum x_i^2 = 55$

傾きは、

$a = \frac{5 \times 110.2 - 15 \times 30.1}{5 \times 55 - 15^2} = \frac{551 - 451.5}{275 - 225} = \frac{99.5}{50} = 1.99$

切片は、

$b = \frac{30.1 - 1.99 \times 15}{5} = \frac{30.1 - 29.85}{5} = 0.05$

標準誤差まで求める

残差 $y_i - \hat{y}_i$ を並べると $0.06,\ -0.13,\ 0.18,\ -0.21,\ 0.10$ で、その二乗和は $0.107$ です。残差の分散は

$\sigma^2 = \frac{1}{n-2}\sum (y_i - \hat{y}_i)^2 = \frac{0.107}{3} = 0.0357$

分母が $n-2$ なのは、直線を引くのに2つのパラメータ（ $a$ と $b$ ）を使う分だけ自由度が減るためです。したがってデータ点が2点以下だと誤差を定義できません。2点なら必ず直線がぴったり通ってしまい、ばらつきの情報が残らないことを考えると納得しやすいと思います。

$S_{xx} = \sum x_i^2 - \dfrac{(\sum x_i)^2}{n} = 55 - 45 = 10$ を使って、

$\Delta a = \frac{\sigma}{\sqrt{S_{xx}}} = \frac{0.189}{\sqrt{10}} = 0.060, \qquad \Delta b = \sigma\sqrt{\frac{\sum x_i^2}{n S_{xx}}} = 0.198$

結果をレポートに書く形にまとめると、

$y = (1.990 \pm 0.060)x + (0.05 \pm 0.20)$

となります。誤差の桁は主値の末位に揃えるのがルールです（ $\pm 0.06$ ではなく $\pm 0.060$ ）。詳しくは有効数字の扱い方を参照してください。

$\Delta a = \sigma/\sqrt{S_{xx}}$ という形からわかる重要な事実があります。 $x$ の範囲を広く取るほど $S_{xx}$ が大きくなり、傾きの誤差は小さくなります。測定点を狭い範囲に密集させるより、広い範囲に散らした方が精度が上がるということです。実験計画を立てるときに効いてくる話です。

決定係数 $R^2$ の正しい読み方

$R^2$ は「全体のばらつきのうち、直線で説明できた割合」を表します。上の例では

$R^2 = 1 - \frac{0.107}{39.708} = 0.9973$

となり、99.7%が直線で説明できたことになります。

ただし、 $R^2$ が高いことは「直線で正しい」ことを意味しません。よくある誤解なので、次の点を押さえておいてください。

本当は緩やかな曲線でも、狭い範囲だけを見れば $R^2$ は高く出ます
点数が少ないと $R^2$ は不当に高くなりがちです（2点なら必ず $1.000$ ）
必ず残差をプロットして確認してください。残差が正・負・正のように系統的に並んでいたら、それは直線モデルが不適切だという信号です

$R^2$ を報告するときは、桁を盛らないことも大切です。測定データが3桁なのに $R^2 = 0.997312$ と書く必要はありません。

原点を通すべきか

「理論上は原点を通るはずだから、切片を0に固定したい」という場面があります。Excelでは近似曲線の設定で「切片＝0」を指定できます。

しかし、安易に固定するのは危険です。切片を自由に動かして求めた結果、 $b = 0.05 \pm 0.20$ のように「誤差の範囲で0と矛盾しない」と言えることこそが、原点を通るという主張の根拠になります。最初から0に固定してしまうと、その検証ができません。

まずは自由に当てはめ、切片が0と有意に違わないことを確認してから、必要に応じて固定するという順序が安全です。

曲線のデータを直線にする

最小二乗法は直線にしか使えませんが、変数を取り替えれば多くの関係を直線に持ち込めます。

元の関係	変換	直線の形
$y = A e^{kx}$	片対数（ $\ln y$ 対 $x$ ）	$\ln y = kx + \ln A$
$y = A x^{n}$	両対数（ $\ln y$ 対 $\ln x$ ）	$\ln y = n \ln x + \ln A$

片対数プロットの傾きが $k$ 、両対数プロットの傾きがべき指数 $n$ になります。「グラフが両対数で直線に乗ったので、べき乗則に従う」という考察は、この変換に基づいています。

なお、対数変換すると誤差の重みが変わるため、厳密には重み付き最小二乗法が必要になります。学生実験では通常そこまで求められませんが、知っておくと理解が深まります。どうしても直線化できない場合は曲線近似ツールで多項式・指数・対数のフィットを直接試せます。

ツールならコピペするだけ

ここまでの計算を毎週手作業でやるのは大変です。当サイトの最小二乗法＆グラフ作成ツールを使えば、次の手順で終わります。

Excelやスプレッドシートの測定データをそのままコピー＆ペーストする
傾き・切片・標準誤差・決定係数が瞬時に算出される
エラーバー付きのグラフが自動で描画される

タブ区切り・カンマ区切り・スペース区切りを自動判別し、1行目が文字列なら系列名として認識します。有効数字の桁数も指定でき、誤差の桁は主値に自動で揃います。

グラフの一部を拡大したい場合はマウスでドラッグするだけで表示範囲が切り替わり、近似線は常に表示範囲の端から端まで自動的に描画されるため、軸の最小値・最大値を設定し直す必要はありません。

できあがったグラフはPNG・SVG・PDFで書き出せます。SVGとPDFはベクター形式なので、拡大しても文字や線が粗くなりません。印刷されるレポートではこちらを推奨します。

さらに、入力したデータをLaTeX形式の表（tabular）コードとして出力する機能もあります。LaTeXでレポートを書いている場合はPGFPlotsによるグラフ作成もあわせてご覧ください。

まとめ

Excelなら LINEST(y, x, TRUE, TRUE) で標準誤差まで一度に求まる。SLOPE / INTERCEPT だけでは誤差が出ない
引数は「 $y$ が先、 $x$ が後」。逆でもエラーにならないので要注意
標準誤差の分母は $n-2$ 。3点以上ないと誤差が定義できない
$x$ の範囲を広く取るほど傾きの精度が上がる
$R^2$ が高い＝直線で正しい、ではない。残差の並びを必ず確認する
切片は最初から0に固定せず、自由に求めて0と矛盾しないことを確かめる

計算そのものは最小二乗法ツールに任せて、考察に時間を使ってください。