こんにちは、ヒガシです。
前回は、2つの点から、その間にある値をエクセルの FORECAST.LINEAR 関数で求めました。今回は、その途中でもう何点か測っていたらどうなるかを考えてみます。前回の記事「FORECAST.LINEARで2点間の値を求める」。
知りたいのは前回と同じ、x が 5 のときの y です。5 の前後にある2点だけを関数に渡すのか、せっかくあるのだから全部の点を渡すのか。どちらを選ぶかで、同じ表から違う答えが出ます。
前回の2点に、途中の4点を追加する
前回使ったのは (1, 12) と (10, 19) の2点です。この2点だけなら、どちらの方法でも同じ直線になり、x が 5 のときの y は 15.111… です。下の表では両端をそのまま残し、説明用に架空の4点を加えました。
| A列(x) | B列(y) |
|---|---|
| 1 | 12.0 |
| 2 | 12.2 |
| 4 | 12.8 |
| 6 | 14.0 |
| 7 | 15.2 |
| 10 | 19.0 |
1行目が見出し、2~7行目がデータです。求めたい x の値 5 を D2 に入力しておきます。
同じ関数で、範囲だけを変えてみる
まずは5を挟む直前・直後の2点です。x が 4 のときに 12.8、6 のときに 14.0。5 はちょうど真ん中ですから、直線で結ぶと答えも真ん中の 13.40 になります。エクセルでは次の式です。
=FORECAST.LINEAR(D2,B4:B5,A4:A5)
次に、同じ関数に6点すべてを渡してみます。
=FORECAST.LINEAR(D2,B2:B7,A2:A7)
今度は 14.20 になりました。エクセルは指定された6点について、各点の y と直線が予測する y との差の二乗和が最も小さくなるように、1本の直線を引きます。今回の式は y = 0.75x + 10.45 です。

左の図で x が 4 と 6 の黒い点に注目してみてください。青緑の線は両方の点をぴったり通ります。一方、オレンジの線はどちらの点の上も通らず、少し上を通っています。右の拡大図で2本の線を読むと、x が 5 のところで 0.80 離れています。
自分のデータなら、どちらを使う?
たとえば、センサーの校正表をエクセルに入れていて、表にない入力値に対応する出力を読み取りたいとします。表に載っている値をそのまま生かし、その間は直線で変化すると考えるなら、使うのは知りたい入力値を挟む2点です。今回は4と6ですが、調べたい入力値が変われば、選ぶ2点も変わります。
一方、少しずつばらつく測定値から「入力が1増えたら、全体として出力がどれだけ変わるか」を知りたいこともあります。直線で傾向を表せると判断したら、こちらは全点で近似直線を引く場面です。
今回の 0.80 は、計算方法を変えたことで生じた差です。x が 5 のときに実際はいくつなのか、という測定値は表にありません。どちらの見積もりが実物に近いかまで確かめたくなったら、次に測りたいのはまさに x = 5 の点ですね。
それではまた!


コメント