【エクセル】線形補間と近似直線はどう違う? 同じ6点で比べてみた

こんにちは、ヒガシです。

前回は、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 です。

同じ6点に対して、青緑の実線は x=4 と 6 の2点を結び、オレンジの破線は全点に当てはめた近似直線。x=5 では前者が13.40、後者が14.20。
左が6点の全体像、右が x = 5 付近の拡大図。青緑の実線が前後2点の補間、オレンジの破線が全6点の近似直線です。

左の図で x が 4 と 6 の黒い点に注目してみてください。青緑の線は両方の点をぴったり通ります。一方、オレンジの線はどちらの点の上も通らず、少し上を通っています。右の拡大図で2本の線を読むと、x が 5 のところで 0.80 離れています。

自分のデータなら、どちらを使う?

たとえば、センサーの校正表をエクセルに入れていて、表にない入力値に対応する出力を読み取りたいとします。表に載っている値をそのまま生かし、その間は直線で変化すると考えるなら、使うのは知りたい入力値を挟む2点です。今回は4と6ですが、調べたい入力値が変われば、選ぶ2点も変わります。

一方、少しずつばらつく測定値から「入力が1増えたら、全体として出力がどれだけ変わるか」を知りたいこともあります。直線で傾向を表せると判断したら、こちらは全点で近似直線を引く場面です。

今回の 0.80 は、計算方法を変えたことで生じた差です。x が 5 のときに実際はいくつなのか、という測定値は表にありません。どちらの見積もりが実物に近いかまで確かめたくなったら、次に測りたいのはまさに x = 5 の点ですね。

それではまた!

コメント

タイトルとURLをコピーしました