こんにちは。

タイトルの件についてです。
業務で複数の(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≦x≦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のときのyを求める。

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

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

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

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

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

このQ&Aに関連する最新のQ&A

A 回答 (2件)

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


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

まず、xの値によってa,bを求めるためのパラメータシートを作成します。
【parameterシート】
  A列 B列 C列
1)0  1  3
2)1  2  3
3)1.5 3  3
4)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
    • good
    • 0

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


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

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


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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q直線補間出来る関数

直線補間できる関数はエクセルに有るのでしょうか?
直線補間とはxy座標において、点A(x1,y1),点B(x2,y2)が有ったときに、x1≦x≦x2における任意のxに対するyの値を点AB間が直線だと仮定して求めるすることです。

今は、VLOOKUP,match,Index関数を使ってやってますが、関数がある非常に楽です。

Aベストアンサー

 2点だけでしたら使うまでも無い様な気がしますが、FORECAST関数というものがあります。

【参考URL】
 FORECAST 関数 - Excel - Office.com
  http://office.microsoft.com/ja-jp/excel-help/HP010342532.aspx

 初心者のエクセル(Excel)学習・入門 > エクセル関数の使い方 > 統計関数 > FORECAST関数とは
  http://excel.onushi.com/function/forecast.htm

QEXCELにてローパスフィルタを作成する

実験の測定データをEXCELでデータ整理しようと考えております。データ整理のためローパスフィルタをかけたいのですが、具体的にどういった式、もしくはEXCELの機能を使用したらいいのでしょうか?デジタルフィルタが良く分からないのでよろしくお願いします。
ちなみにローパスフィルタは1000Hzをかけたいです。

Aベストアンサー

時系列データの処理ならば

OutputData(n+1) = OutputData(n) + (InputData(n+1) - OutputData(n)) * dt / T

dt:データのサンプリング間隔
T:フィルタの時定数 1/2πf
f:カットオフ周波数
n,n+1:それぞれn個目,n+1個目のデータをしめす。

でいけると思いますが、一次のパッシブなんで効果が薄いかも。(普通はベッセルかけるんでしょうけど、そこまではわからない)

QExcel 指定の文字列を含むとカウントするようにしたい場合は?

ただいまエクセルと格闘中の初心者です。
関数等はオートSUMくらいしか使ったことがなく、必要時にいちいち調べているような状態です。
今回は文字列を含むとカウントするところで躓いております。

表の中から特定の文字列を含むとカウントするようにしたい時COUNTIFを使うと思うのですが、この場合正確にその言葉じゃなければカウントされませんよね?

例えば「ボールペン」「サインペン」「シャープペン」のようなペン類をまとめてカウントしたい時、ペンだけで認識してくれるようにすることは可能なのでしょうか?
COUNTIF関連で探してみているのですが、中々思うような検索ができていません(もしくはわからずに見逃しているのかも?)

エクセルにお詳しい方、アドバイスを頂ければと思います。
よろしくお願い致します。

Aベストアンサー

検索条件に「*ペン*」としてみてはいかがですか。

ちなみに上記は「ペン」がどこかにあるものを対象とする。
「*ペン」は文字列がペンで終わるものを対象にする。
「ペン*」は文字列がペンで始まるものを対象とする。

Q数値微分の近似公式について

一定間隔hで計測した位置座標データから、各計測時の速度を近似公式を使った数値微分で解く方法を調べています。
3点近似公式は(f(X0+h)-f(X0-h))/2h
5点近似公式は(f(X0-2h)-8f(X0-h)+8f(X0+h)-f(X0+2h))/12h
というところまでは分かったのですが、7点、9点とより高次の近似公式について、どなたか教えてください。

Aベストアンサー

5点近似公式がどうやって導かれたかを理解すれば,
以下何点近似でも導けます.

Taylor 展開
(1)  f(x0±h) = f(x0) ± f'(x0) h + f''(x0) h^2 / 2!
         ± f^(3)(x0) h^3 / 3! + ・・・
(2)  f(x0±2h) = f(x0) ± f'(x0) 2h + f''(x0) (2h)^2 / 2!
         ± f^(3)(x0) (2h)^3 / 3! + ・・・
から
(3)  {f(x0+2h) - f(x0-2h)} + a{f(x0+h) - f(x0-h)}
を作ってみると,hの偶数乗の項は消えて,奇数乗の項は
(4)  h の係数  4+2a
(5)  h^3 の係数 (8/3) + (1/3)a
になります.
したがって,(5)がゼロになるように,すなわち a=-8 と選べばよく,
質問の5点近似公式が直ちに導けます.
補正が h^5 f^(5)(x0) のオーダーであることも明らかでしょう
(偶数次の項は消えるから).

7点近似なら,
(6)  f(x0±h) = f(x0) ± f'(x0) h + f''(x0) h^2 / 2!
         ± f^(3)(x0) h^3 / 3! + f^(4)(x0) h^4 / 4!
         ± f^(5)(x0) h^5 / 5! + ・・・
(7)  f(x0±2h) = ・・・
(8)  f(x0±3h) = ・・・
から
(9)  {f(x0+3h) - f(x0-3h)} + b{f(x0+2h) - f(x0-2h)}
     + c{f(x0+h) - f(x0-h)}
を作って,h^3 と h^5 の係数がゼロになるように b,c を決めればよいわけです.

式の上からは近似の点数を上げるほど近似は良くなりますが,
実際の測定データは誤差を含んでいますし,桁落ちもありますから,
近似の点数を上げれば良いというものでもありません.

5点近似公式がどうやって導かれたかを理解すれば,
以下何点近似でも導けます.

Taylor 展開
(1)  f(x0±h) = f(x0) ± f'(x0) h + f''(x0) h^2 / 2!
         ± f^(3)(x0) h^3 / 3! + ・・・
(2)  f(x0±2h) = f(x0) ± f'(x0) 2h + f''(x0) (2h)^2 / 2!
         ± f^(3)(x0) (2h)^3 / 3! + ・・・
から
(3)  {f(x0+2h) - f(x0-2h)} + a{f(x0+h) - f(x0-h)}
を作ってみると,hの偶数乗の項は消えて,奇数乗の項は
(4)  h の係数  4+2a
(5)  h^3 の係数 (8/3) + (1/3)...続きを読む

Qエクセル STDEVとSTDEVPの違い

エクセルの統計関数で標準偏差を求める時、STDEVとSTDEVPがあります。両者の違いが良くわかりません。
宜しかったら、恐縮ですが、以下の具体例で、『噛み砕いて』教えて下さい。
(例)
セルA1~A13に1~13の数字を入力、平均値=7、STDEVでは3.89444、STDEVPでは3.741657となります。
また、平均値7と各数字の差を取り、それを2乗し、総和を取る(182)、これをデータの個数13で割る(14)、この平方根を取ると3.741657となります。
では、STDEVとSTDEVPの違いは何なのでしょうか?統計のことは疎く、お手数ですが、サルにもわかるようご教授頂きたく、お願い致します。

Aベストアンサー

データが母集団そのものからとったか、標本データかで違います。また母集団そのものだったとしても(例えばクラス全員というような)、その背景にさらならる母集団(例えば学年全体)を想定して比較するような時もありますので、その場合は標本となります。
で標本データの時はSTDEVを使って、母集団の時はSTDEVPをつかうことになります。
公式の違いは分母がn-1(STDEV)かn(STDEVP)かの違いしかありません。まぁ感覚的に理解するなら、分母がn-1になるということはそれだけ結果が大きくなるわけで、つまりそれだけのりしろを多くもって推測に当たるというようなことになります。
AとBの違いがあるかないかという推測をする時、通常は標本同士の検証になるわけですので、偏差を余裕をもってわざとちょっと大きめに見るということで、それだけ確証の度合いを上げるというわけです。

Qエクセルで計算すると2.43E-19などと表示される。Eとは何ですか?

よろしくお願いします。
エクセルの回帰分析をすると有意水準で2.43E-19などと表示されますが
Eとは何でしょうか?

また、回帰分析の数字の意味が良く分からないのですが、
皆さんは独学されましたか?それとも講座などをうけたのでしょうか?

回帰分析でR2(決定係数)しかみていないのですが
どうすれば回帰分析が分かるようになるのでしょうか?
本を読んだのですがいまいち難しくて分かりません。
教えてください。
よろしくお願いします。

Aベストアンサー

★回答
・最初に『回帰分析』をここで説明するのは少し大変なので『E』のみ説明します。
・回答者 No.1 ~ No.3 さんと同じく『指数表記』の『Exponent』ですよ。
・『指数』って分かりますか?
・10→1.0E+1(1.0×10の1乗)→×10倍
・100→1.0E+2(1.0×10の2乗)→×100倍
・1000→1.0E+3(1.0×10の3乗)→×1000倍
・0.1→1.0E-1(1.0×1/10の1乗)→×1/10倍→÷10
・0.01→1.0E-2(1.0×1/10の2乗)→×1/100倍→÷100
・0.001→1.0E-3(1.0×1/10の3乗)→×1/1000倍→÷1000
・になります。ようするに 10 を n 乗すると元の数字になるための指数表記のことですよ。
・よって、『2.43E-19』とは?
 2.43×1/(10の19乗)で、
 2.43×1/10000000000000000000となり、
 2.43×0.0000000000000000001だから、
 0.000000000000000000243という数値を意味します。

補足:
・E+数値は 10、100、1000 という大きい数を表します。
・E-数値は 0.1、0.01、0.001 という小さい数を表します。
・数学では『2.43×10』の次に、小さい数字で上に『19』と表示します。→http://ja.wikipedia.org/wiki/%E6%8C%87%E6%95%B0%E8%A1%A8%E8%A8%98
・最後に『回帰分析』とは何?下の『参考URL』をどうぞ。→『数学』カテゴリで質問してみては?

参考URL:http://ja.wikipedia.org/wiki/%E5%9B%9E%E5%B8%B0%E5%88%86%E6%9E%90

★回答
・最初に『回帰分析』をここで説明するのは少し大変なので『E』のみ説明します。
・回答者 No.1 ~ No.3 さんと同じく『指数表記』の『Exponent』ですよ。
・『指数』って分かりますか?
・10→1.0E+1(1.0×10の1乗)→×10倍
・100→1.0E+2(1.0×10の2乗)→×100倍
・1000→1.0E+3(1.0×10の3乗)→×1000倍
・0.1→1.0E-1(1.0×1/10の1乗)→×1/10倍→÷10
・0.01→1.0E-2(1.0×1/10の2乗)→×1/100倍→÷100
・0.001→1.0E-3(1.0×1/10の3乗)→×1/1000倍→÷1000
・になります。ようするに 10 を n 乗すると元の数字になるた...続きを読む

QExcelで最大値の入っている数値の右側にある項目名を拾いたい。

Excelで最大値の入っている数値の右側にある項目名を拾いたい。

下のようなデータがあります。(A列は年、Bは何かの生産量・・とします)

1 2000 7
2 2001 11
3 2002 14
4 2003 10
5 2004 9
6 2005 8

生産量(B列)の最大値を求めるのは=MAX(B1:B7)で"14"が得られると思いますが、求めているのは"14"の入っている年の"2002"という数字を隣のC1に入れたいと思います。
どのような関数が必要でしょうか?

Aベストアンサー

こんばんは!
一例です。

↓の画像でC2セルに表示するようになりますが・・・

C2セルの数式は
=INDEX($A$2:$A$1000,MATCH(MAX(B2:B1000),B2:B1000,0))
にしています。
(エラー処理はしていません)
A列の1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば幸いです。m(__)m

Qエクセルで多数のシートをまとめる方法

エクセルで10以上あるシートを一発でひとつにまとめる方法はあるでしょうか?

コピー&ペーストを繰り返すしかないのでしょうか…

仕事で明日やらなければならないので、
お分かりになる方、是非教えてください!

Aベストアンサー

まぁ既出回答にもあるように,10数回程度コピー貼り付けすることで,どうという作業ではないとは思います。

さておき。
ご利用のエクセルのバージョンが不明ですが,ご利用のOfficeのバージョンによっては

1.ワードを起動する
2.挿入のファイルからエクセルブックを指定する
3.添付図のようなダイアログが表示されたらラッキー成功です,ブック全体を指定して挿入する
4.Ctrl+Aで全体を選び,コピーして,エクセルの新しいシートに貼り付ける

と一発でできます。
添付図のようにならなかったときは,出来ません。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報