エクセルで一次線形補間する方法

こんにちは。

タイトルの件についてです。
業務で複数の(x,y)のサンプルから、任意のxについてのyを求めています。
具体的には、(1.5 , 3), (2.4 , 5.6) , (7.4 , 6.8).......
のようなサンプルから、x=6.5のときy=?を求めています。

このxyについて、全体を直線の一次式と捉えて、
任意のxについて当てはまる、y=ax+bの式を作成し、
任意のxに対応するyの値を近似的に求める……としているなら、簡単なのですが、
実務上はそうしていません。

上記の例でいえば、例えばx=1.8のときの値を求めるに際して、
①x=1.8は、採取されたサンプルのうち、1.5≦ｘ≦2.4の区間に属することを確認。
②1.5≦x≦2.4の区間については、（5.6-3）/(2.4-1.5)が一次式の傾き。
③3+(1.8-1.5)*(5.6-3)/(2.4-1.5)でx=1.8のときのｙを求める。

これが、x=4であれば、2.4≦ｘ≦7.4の区間について、同様のことをしています。

ざっくり言えば、サンプルの値を活かして、折線での線形補間を行っています。
（一次スプライン補間という言い方をするのでしょうか？）

現在、「任意のx」について、それがどの区間に属するかを確認し、
上記の計算を、計算式で行っています。
非常に煩雑です。

これを、xとyのサンプルデータだけ並べたら、
自動的に線形補間された折線のグラフを描画してくれ、
なおかつそのグラフをもとに任意のｘについてのｙを求めてくれるような、
エクセル関数はないでしょうか？

詳しい方、教えていただければ有りがたいです。

No.2 回答者： ママチャリ 回答日時： 2016/04/24 09:06

y=ax+b とした場合、xの値によってa,bを任意に決定して、yを求めたいということですようね？

このような場合、ユーザ定義関数を作成すると便利です。
次の例では、=y(x値のセル)　のように、普通の関数と同様に使用することができます。　
具体的な実装方法は、以下の通りです。

まず、xの値によってa,bを求めるためのパラメータシートを作成します。
【parameterシート】
　　A列　B列　C列
１）0　　1　　3
２）1　　2　　3
３）1.5　3　　3
４）2.5　4　　3

A列がxに対する範囲となります。
上記の例では･･･
0≦x＜1→1行目が採用
1≦x＜1.5→2行目が採用
1.5≦x＜2.5→3行目が採用
2.5≦x→4行目が採用
･･･といった具合です。（該当行の値以上、次の行の値未満となります）

B列→aの値、C列→bの値です。

ユーザ定義関数は、以下の通りです。
実際は、a,bの他にも必要なパラメータがありそうですが、それは任意に追加してください。

Function y(x As Double) As Double
Dim a As Variant
Dim b As Variant
Dim I As Long

'xの値で、パラメータシートのA列を検索し、採用すべき行を取得します。
I = WorksheetFunction.Match(x, Sheets("parameter").Range("A:A"), 1)

a = Sheets("parameter").Cells(I, "B")
b = Sheets("parameter").Cells(I, "C")

y = a * x + b

End Function

No.1 回答者： 銀鱗 回答日時： 2016/04/19 13:45

自分は「散布図」に「近似曲線」を描かせてその「補完式」を表示させ、

その補完式を別のセルに入力してｘに対するｙの値を求めます。

面倒ですけど、自分にはそのやり方しか分かりません。


・・・
以前、実測した値にばらつきがあることが分かっているデータを元に検量線を近似曲線で示して、
それを元に補正値を決めるなんていう、ちょっと面倒な校正業務をやっていました。